结构化查询语言SQL

第4章关系数据库语言SQL•教学内容SQL概述；SQL的数据定义；SQL的数据查询；SQL的数据更新；嵌入式SQL•教学要求了解SQL语言特点和基本概念，重点掌握SQL语言基本功能，会用SQL语言对数据库进行查询，更新；会用SQL语言创建视图，表和其他高级功能。•教学重点掌握SQL语言基本功能。•教学难点SQL的数据查询。4.1SQL语言概述4.1.1SQL的产生和发展1.1970年，提出关系模型；2.1972年，SQUARE语言；3.1974年，SEQUEL；4.1986年，美国提出了《数据库SQL语言》，1987年，国际化标准化组织采纳为国际标准，称为“SQL86”；5.1989年，SQL89；6.1992年，SQL2；7.1999年，SQL3。4.1.2SQL的特点1.一体化2.高度非过程化3.两种使用方式4.用户性能好5.功能强大6.是所有数据库管理系统的公共语言4.1.3SQL语言的基本组成SQL语言支持关系数据库的三级模式结构，如图6.1所示。其中：(1)外模式对应于视图和部分基本表；(2)模式对应于基本表，内模式对应于存储文件；(3)在SQL中，表被分为两种：一种叫基表(basetable)，其数据显式地存储在数据库中；另一种叫视图(view)，它是一种虚表，具有逻辑定义，是从一个或几个基表(或视图)导出的表。视图本身并不作为一个表显式地存储在数据库中。每个基本表对应一个存储文件，一个表可以带有若干索引，索引也存放在存储文件中，存储文件及索引组成了关系数据库的内模式。主要由以下三个部分组成：1.数据定义语言(DataDefinitionLanguage简称DDL)用于定义、撤销和修改数据模式。2.数据操纵语言(DataManipulationLanguage简称DML)，主要包括两部分：(1)数据查询语言(QueryLanguage简称QL)，用于进行数据查询；(2)数据更新语言，用于增加、删除和修改数据。3.数据控制语言(DataControlLanguage简称DCL)，用于对数据访问权限的控制完整性描述事务控制等。4.1.4SQL语言的基本数据类型类型名类型说明INTEGER数值型长整数(也可写成INT)SMALLINT数值型短整数REAL数值型浮点数DOUBLEPRECISION数值型双精度浮点数FLOAT(n)数值型浮点数，精度至少为n位数字NUMERIC(p,d)或DECIMAL(p,d)数值型定点数，有p位数字(不包括符号、小数点)，小数点后面有d位数字。CHAR(n)字符串型长度为n的定长字符串VARCHAR(n)字符串型具有最大长度为n的变长字符串BIT(n)位串型长度为n的定长二进制位串BITVARYING(n)位串型最大长度为n的变长二进制位串DATE时间型日期，年-月-日，形如YYYY-MM-DDTIME时间型时间，时：分：秒，形如HH：MM：SS4.2数据定义4.2.1基本表的操作1.定义基本表定义基本表的语句格式如下：CREATETABLE表名(列名1数据类型[列级完整性的约束]…………[,列名n数据类型[列级完整性的约束]][,表级完整性的约束]);说明:(1)表名是所要定义的基本表的名字，它可以由一个或多个属性(列)组成。(2)列级完整性的约束主要有：NOTNULL，就是规定一个属性的值不允许为空值，省略则表示可以允许该属性的值为空值；(3)完整性约束主要有以下三种子句：①主键子句(PRIMARYKEY)；②检查子句(CHECK)；③外键子句(FOREIGNKEY)。例4-1：建立一个“图书”表,它由图书编号B#、书名Title、作者Author、图书分类号T#、出版社编号P#、单价Price、复本数量CopyNumber、库存量Stocks、日罚金Fine、是否新书IsNew十个属性组成。其中图书编号、出版社编号不能为空，且图书编号的值是唯一的。CREATETABLEBook(B#CHAR(6)NOTNULL,TitleCHAR(60),AuthorCHAR(30),BookTypeCHAR(2),P#CHAR(2)NOTNULL,PriceNUMERIC(9),CopyNumberSMALLINT,StocksSMALLINT,FineNUMERIC(9),IsnewBIT,PRIMARYKEY(B#))2.修改基本表修改基本表的语句格式如下：ALTERTABLE表名[ADD新列名数据类型[完整性约束]][DROP完整性约束名][MODIFY列名数据类型]；说明:(1)表名是要修改的基本表；(2)ADD子句用于增加新列和新的完整性约束条件；(3)DROP子句用于删除指定的完整性约束条件；(4)MODIFY子句用于修改原有的列定义，包括修改列名和数据类型。例4-2：将图书表Book的“复本数量”列的数据类型由短整型改为长整型。ALTERTABLEBookMODIFY(ALTERCOLUMN)CopyNumberINTCopyNumberINT注：要用ALTERCOLUMN3.删除基本表删除基本表的语句格式如下：DROPTABLE表名说明:一个基本表一旦被删除后，这个表的所有数据都将被删除掉。因此，执行删除基本表的操作一定要格外小心。例4-3：删除图书表Book。DROPTABLEBook4.2.2索引的建立与删除1.建立索引CREATE[UNIQUE][CLUSTER]INDEX索引名ON表名(列名1[次序1][，列名2[次序2]]…)；说明：(1)表名是要建索引的基本表的名字；(2)索引可以建立在该表的一列或多列上，各列名之间用逗号隔开；(3)次序规定了索引值的排列次序，可为升序(ASC)或降序(DESC)，缺省值为ASC；(4)UNIQUE表示要建立的有一个索引值只对应唯一的数据记录，所以，如选择此项，则相应的列一定是主关键字；(5)CLUSTER表示要建立的索引是聚簇索引。例4-4：为图书表Book、借阅人表Reader建立索引。其中Book表按图书编号升序建唯一索引，Reader表按读者编号降序建唯一索引。CREATEUNIQUEINDEXBook_IndexONBook(B#)CREATEUNIQUEINDEXReader_IndexONReader(R#DESC)2.删除索引删除索引的语句格式如下：DROPINDEX索引名；例4-5：删除Book表的Book_Index索引。DROPINDEXBook_Index4.3数据查询4.3.1SELECT语句的一般格式SELECT[DISTINCT]目标列表达式1[,目标列表达式2]…FROM基本表名1或视图名1[,基本表名2或视图名2]…[WHERE条件表达式1][GROUPBY列名1[HAVING条件表达式2]][ORDERBY列名2[ASC|DESC]]说明：1.SELECT子句指定了查询的列，目标列表达式可以是：(1)*；(2)表中的列名；(3)表达式；2.FROM子句指定了被查询的基本表或视图；3.WHERE子句说明查询的条件，条件表达式1可使用下列操作符：(1)算术比较运算符：，=，，=，＝，；(2)逻辑运算符：AND，OR，NOT；(3)集合运算符：UNION(并)，INTERSECT(交)，EXCEPT(差)；(4)集合成员资格运算符：IN，NOTIN；(5)谓词：EXISTS(存在量词)，ALL，ANY，UNIQUE；(6)集合函数：COUNT(计数)，SUM(求和)，AVG(平均值)，MAX(最大值)，MIN(最小值)；(7)条件表达式1还可以是另外一个SELECT语句(即SELECT语句可以嵌套)。4.SELECT和FROM子句是每个SQL语句所必需的，其它子句是任选的，整个语句的含义是：根据WHERE子句的条件表达式，从FROM子句指定的基本表或视图中找出满足条件的元组，再按SELECT子句中的目标列表达式，选出元组中的属性值形成结果表；5.若有GROUP子句，则结果按指定的列名1分组产生结果表中的一个元组，通常在每组中还可以通过HAVING作用库函数，当查询结果分组输出时，只输出满足条件的组；6.若有ORDER子句，则结果要根据指定的列名2按升序或降序排序。下面以图书-借阅人数据库为例来说明SELECT语句的各种用法。图书-借阅人数据库包括六个表。(1).图书表Book(B#,Title,Author,BookType,P#,Price,CopyNumber,Stocks,Fine,IsNew)Book由图书编号(B#)、书名(Title)、作者(Author)、图书分类(BookType)、出版社(P#)、单价（元）(Price)、复本数量(CopyNumber)、库存量(Stocks)、日罚金（元）(Fine)、是否新书(IsNew)十个属性组成，其中B#为主键,P#为外键。(2).借阅卡表Card(C#,R#)Card由借阅卡编号(C#)、读者编号(R#)两个属性组成，其中C#为主键,R#为外键。(3).借阅人表Reader(R#,Name,IsStudent,Password,Borrowed,Email,PhoneNumber)Reader由学生编号(R#)、姓名(Name)、是否学生(IsStudent)、密码(Password)、已借数量(Borrowed)、Email地址(Email)、电话号码(PhoneNumber)七个属性组成，其中R#为主键。(4).借阅表Lending(L#,R#,B#,BorrowingDate,IsRenewed,RenewingDate,IsReturned,ReturningDate)Lending由流水号(L#)、借阅人编号(R#)、图书编号(B#)、借阅日期(BorrowingDate)、是否续借(IsRenewed)、续借日期(RenewingDate)、是否已归还(IsReturned)归还日期(ReturningDate)八个属性组成，其中L#、R#、B#为联合主键。(5).出版社表Publisher(P#,Publisher)Publisher由编号(P#)、出版社名(Publisher)两个属性组成，其中P#为主键。(6).图书分类表Type(T#,Type)Type由编号(T#)、分类号(Type)两个属性组成，其中T#为主键。4.3.2单表查询1.查询全部列把表中的所有属性列都选出来，可以有两种方法：(1)就在SELECT关键字后面列出所有属性列名；(2)可以在SELECT关键字后面用*，此时属性列名的显示顺序与其在基本表中的顺序相同。例4-6：查询图书馆中所有图书的详细记录。SELECT*FROMBook等价于：SELECTB#,Title,Author,BookType,P#,Price,CopyNumber,Stocks,Fine,IsNewFROMBook2.查询部分列可以通过在SELECT关键后面列出用户要查询的属性列后，属性列名的显示顺序可以根据用户需要来指定。例4-7：查询图书馆中所有图书的书号与书名。SELECTB#,TitleFROMBook3.查询某些列的不同元组两个本来不完全相同的元组，投影到指定的某些列上后，可能变成相同的行。为了去掉结果表中相同的行，可以在SELECT关键字后加DISTINCT关键字。例4-8：查询借了图书的借阅者编号。SELECTR#FROMLending假设查询的结果为：R#00010001000100020003该查询结果中包含了很多重复的行，用下面的查询语句可以去掉重复的行。SELECTDISTINCTR#FROMLending4.查询满足条件的元组可以通过WHERE子句实现，WHERE子句常用的查询条件如右表所示。查询条件谓词比较=,,=,,=,确定范围BETWEENAND,NOTBETWEENAND确