第3章 关系数据库标准语言SQL

《第3章 关系数据库标准语言SQL》由会员分享，可在线阅读，更多相关《第3章 关系数据库标准语言SQL（145页珍藏版）》请在金锄头文库上搜索。

1、上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL第三章第三章关系数据库的标准语言关系数据库的标准语言SQLSQL3.1SQL概述概述3.2架构（架构（SCHEMA）3.3表表3.4索引索引3.5数据查询数据查询3.6数据更新数据更新3.7视图视图3.8Transact-SQL语言语言上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.1SQLSQL概述概述SQL语言及其标准语言及其标准20世纪世纪70年代中期，年代中期，IBM公

2、司在研制公司在研制System-RRDBMS的过程的过程中，开发了世界上最早的中，开发了世界上最早的SQL语言，后来在许多数据库系统语言，后来在许多数据库系统中被使用，由于其广泛的使用，出现标准化需求，形成中被使用，由于其广泛的使用，出现标准化需求，形成SQL标准。标准。SQL-86SQL-89SQL-92(SQL2)SQL:1999(SQL3)SQL2003上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQLSQLSQL语言特点语言特点SQL的特点的特点综合统一综合统一高度非过程化高度非过程化面向集合的操作方式面

3、向集合的操作方式以同一种语法结构提供两种使用方法以同一种语法结构提供两种使用方法语言简洁，易学易用语言简洁，易学易用上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL5.语言简捷，易学易用语言简捷，易学易用上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.2架构（架构（SCHEMASCHEMA）CREATESCHEMAAUTHORIZATIONCREATESCHEMESTAUTHORIZATIONWANGDROPSCHEMEDR

4、OPSCHEMEZHANGCSCADE上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQLSQL Server2005SQL Server2005中的架构中的架构架构（架构（Schema）：数据库架构是一个独立于数）：数据库架构是一个独立于数据库用户的非重复据库用户的非重复命名空间命名空间，您可以将架构视，您可以将架构视为对象的容器为对象的容器-MSDN命名空间名其实就是文件夹名命名空间名其实就是文件夹名一个对象只能属于一个架构，就像一个文件只能存一个对象只能属于一个架构，就像一个文件只能存放于一个文件夹中一样。与

5、文件夹不同的是，架构放于一个文件夹中一样。与文件夹不同的是，架构是不能嵌套的。因此，我们要访问一个数据库对象是不能嵌套的。因此，我们要访问一个数据库对象的时候，通常应该是引用它的全名的时候，通常应该是引用它的全名-架构名架构名. 对象对象名名。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL默认架构默认架构每个登陆用户都有一个每个登陆用户都有一个defaultschema，当数据库对象，当数据库对象无前缀时，无前缀时，sqlserver自动加上默认架构。自动加上默认架构。如果引用的数据库对象不属于如果引用的数据

6、库对象不属于defaultschema时，将会时，将会提示数据库对象无效。提示数据库对象无效。可以修改用户的默认架构：可以修改用户的默认架构：ALTERUSERdboWITHDEFAULT_SCHEMA=emdbuser；可以改变此表的可以改变此表的schema,相当于把这个表放到另一个文相当于把这个表放到另一个文件夹件夹alterschemadboTRANSFERemdbuser.Borrower上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL架构与表架构与表要为要为每一个基本表指定所属模式，可通过以下每一个

7、基本表指定所属模式，可通过以下方式：方式：在表名中显式地给出模式名；在表名中显式地给出模式名；在创建模式时创建表；在创建模式时创建表；设置所属的模式设置所属的模式上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.3基本表的定义、删除和修改基本表的定义、删除和修改CREATETABLE（，）；）；：所要定义的基本表的名字：所要定义的基本表的名字：组成该表的各个属性（列）：组成该表的各个属性（列）：仅仅涉涉及及单单列列的的完完整整性性约约束束条件条件：涉及一个或多个属性列的：涉及一个或多个属性列的完整性约束条件完

8、整性约束条件上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL定义基本表（续）定义基本表（续）常用完整性约束常用完整性约束主码约束主码约束：PRIMARY KEYPRIMARY KEY唯一性约束：唯一性约束：UNIQUEUNIQUE非空值约束：非空值约束：NOT NULLNOT NULL检查约束：检查约束：CHECKCHECK参照完整性约束参照完整性约束:FOREIGN KEYFOREIGN KEY(属性名属性名)REFERENCESREFERENCES表名表名(属性名属性名)PRIMARY KEYPRIMARY

9、 KEY与与 UNIQUEUNIQUE的区别？的区别？上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQLSQL Server2005SQL Server2005数据类型数据类型二进制数据类型二进制数据类型字符数据类型字符数据类型Unicode数据类型数据类型日期和时间数据类型日期和时间数据类型数值数据类型数值数据类型货币数据类型货币数据类型特殊数据类型特殊数据类型上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL二进制数据类型二进制

10、数据类型Binary(N)是是n（1到到8000）位固定的二进制数位固定的二进制数据。据。Varbinary(N)是是n（1到到8000）位变长度的二进）位变长度的二进制数据。制数据。Image数据类型中存储的数据是以位字符串存储的，数据类型中存储的数据是以位字符串存储的，不是由不是由SQLServer解释的，必须由应用程序来解解释的，必须由应用程序来解释。释。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL字符型数据类型字符型数据类型Char(n)定长字符数据，其长度最多为定长字符数据，其长度最多为8KB。V

11、arChar(n)是变长字符数据，其长度最多为是变长字符数据，其长度最多为8KB。Text数据类型，可用来存储超过数据类型，可用来存储超过8KB的的ASCLL数据。数据。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQLUnicodeUnicode数据类型数据类型它为每种语言中的每个字符设定了统一并且唯一的二它为每种语言中的每个字符设定了统一并且唯一的二进制编码，以满足跨语言、跨平台进行文本转换、处进制编码，以满足跨语言、跨平台进行文本转换、处理的要求。理的要求。Unicode数据类型包括数据类型包括Nchar,

12、Nvarchar和和Ntext。使用使用Unicode数据类型，所占用的存储空间是使用非数据类型，所占用的存储空间是使用非Unicode数据类型所占用空间的两倍。数据类型所占用空间的两倍。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL日期和时间数据类型日期和时间数据类型Datetime所存储的日期范围是从所存储的日期范围是从1753年年1月月1日日开始，到开始，到9999年年12月月31日结束，每一个值要求日结束，每一个值要求8个个存储字节。存储字节。Smalldatetime所存储的日期范围是所存储的日期范

13、围是1900年年1月月1日开日开始，到始，到2079年年12月月31日结束，每一个值要求日结束，每一个值要求4个存储字节。个存储字节。日期格式包括日期格式包括MDY、DMY、YMD、YDM、MYD和和DYM。在默认情况下，日期格式为。在默认情况下，日期格式为MDY，可通，可通过过SetDateFormat改变。改变。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL数值数据类型数值数据类型整数数据类型：整数数据类型：int、smallint、tinyint分别用分别用4、2、1个字节存储整数。个字节存储整数。精确

14、小数精确小数Decimal（n,m)和和Numberic(n,m),占用存占用存储空间由数据位数确定。储空间由数据位数确定。近似小数近似小数real和和float上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL货币数据类型货币数据类型Money数据类型要求数据类型要求8个存储字节个存储字节Smallmoney数据类型要求数据类型要求4个存储字节个存储字节两者都带有四位小数两者都带有四位小数货币数据不需要用单引号货币数据不需要用单引号()引起来。虽然可以指引起来。虽然可以指定前面带有货币符号的货币值，但定前面带有

15、货币符号的货币值，但SQLServer不不存储任何与符号关联的货币信息，它只存储数值。存储任何与符号关联的货币信息，它只存储数值。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL特殊数据类型特殊数据类型Timestamp用于表示用于表示SQLServer活动的先后顺序，与活动的先后顺序，与插入数据或者日期和时间没有关系。插入数据或者日期和时间没有关系。uniqueidentifier此类型数据存储二进制值，其作用与此类型数据存储二进制值，其作用与全局唯一标识符全局唯一标识符(GUID)一样。一样。GUID主要用

16、于在有多主要用于在有多个节点、多台计算机的网络中，分配必须具有唯一性个节点、多台计算机的网络中，分配必须具有唯一性的标识符。占用的标识符。占用16bytes存储空间。存储空间。Bit由由1或者或者0组成。当表示真或者假、组成。当表示真或者假、ON或者或者OFF时，使用时，使用Bit数据类型。数据类型。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL 例例1 1 建建立立一一个个“学学生生”表表StudentStudent，它它由由学学号号SnoSno、姓姓名名SnameSname、性性别别SsexSsex、年年

17、龄龄SageSage、所所在在系系SdeptSdept五五个个属属性性组组成成。其其中中学学号号不不能能为为空空，值值是是唯唯一一的的，并并且且姓姓名名取值也唯一。取值也唯一。 CREATE TABLE StudentCREATE TABLE Student ( (SnoSno CHAR(5) NOT NULL UNIQUE CHAR(5) NOT NULL UNIQUE， SnameSname CHAR(20) UNIQUE CHAR(20) UNIQUE， SsexSsex CHAR(1) CHAR(1) ， Sage INTSage INT， SdeptSdept CHAR(15) CH

18、AR(15)；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL 例例2 2 建立一个建立一个“学生选课学生选课”表表SCSC，它由学号它由学号SnoSno、课程号课程号CnoCno，修课修课成绩成绩ScoreScore组成，其中组成，其中( (SnoSno, , CnoCno) )为主码为主码, ,SnoSno为外码参照为外码参照student.snostudent.sno, ,成绩在成绩在【0 0100100】。CREATE TABLE SC(CREATE TABLE SC( SnoSno CHAR(5) ,

19、 CHAR(5) , CnoCno CHAR(3) , CHAR(3) , Score Score intint, , CONSTRAINT CONSTRAINT grd_chkgrd_chk CHECK(ScoreCHECK(Score between 0 and 100) between 0 and 100) Primary key ( Primary key (SnoSno, , CnoCno) )， CONSTRAINT CONSTRAINT frk_Snofrk_Sno FOREIGN FOREIGN KEY(SnoKEY(Sno) ) REFERENCE REFERENCE stu

20、dent(Snostudent(Sno) ON DELETE CSCADE ON DELETE CSCADE ON UPDATE CSCADE; ON UPDATE CSCADE;上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL二、修改基本表二、修改基本表ALTER TABLE ALTER TABLE ADD ADD 完整性约束完整性约束 DROP DROP | | ALTER ALTER ； ：要修改的基本表：要修改的基本表ADDADD子句：增加新列和新的完整性约束条件子句：增加新列和新的完整性约束条件DRO

21、PDROP子句：删除指定的完整性约束条件或字段子句：删除指定的完整性约束条件或字段ALTERALTER子句：用于修改列名和数据类型子句：用于修改列名和数据类型上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL向向StudentStudent表表增增加加“入入学学时时间间”列列，其其数数据据类类型型为为日日期期型。型。ALTER TABLE Student ADD ALTER TABLE Student ADD ScomeScome DATE DATE；不不论论基基本本表表中中原原来来是是否否已已有有数数据据，新新

22、增增加加的的列列一一律律为为空值。空值。删除属性列删除属性列ALTER TABLE Student DROP COLUMN ALTER TABLE Student DROP COLUMN ScomeScome；将年龄的数据类型改为半字长整数。将年龄的数据类型改为半字长整数。ALTER TABLE Student ALTER Sage SMALLINTALTER TABLE Student ALTER Sage SMALLINT；删除学生姓名必须取唯一值的约束。删除学生姓名必须取唯一值的约束。ALTER TABLE Student DROP ALTER TABLE Student DROP UN

23、IQUE(SnameUNIQUE(Sname) )；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL三、删除基本表三、删除基本表DROPTABLE; 基基本本表表定定义义一一旦旦删删除除，表表中中的的数数据据、此此表表上上建建立立的的索索引引和和视视图图都都将将自自动动被被删删除除掉掉。但但是是有有的的系系统统，如如Oracle则则将将视视图图定定义义保保留留在在数数据据字字典典中。但是用户引用时就报错。中。但是用户引用时就报错。删除删除Student表表DROPTABLEStudent;上一页上一页下一下一页

24、页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.4索引索引建立索引是加快查询速度的有效手段建立索引是加快查询速度的有效手段建立索引建立索引DBA或表的属主（即建立表的人）根据需要建立或表的属主（即建立表的人）根据需要建立SQLSERVER自动建立以下列上的索引自动建立以下列上的索引PRIMARYKEYUNIQUE维护索引维护索引DBMS自动完成自动完成使用索引使用索引DBMS自动选择是否使用索引以及使用哪些索引自动选择是否使用索引以及使用哪些索引上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章

25、章关系数据库的标准语言关系数据库的标准语言SQL一、建立索引一、建立索引语句格式语句格式CREATE UNIQUE CLUSTER INDEX ON(,)；用用指定要建索引的基本表名字指定要建索引的基本表名字索索引引可可以以建建立立在在该该表表的的一一列列或或多多列列上上，各各列列名名之之间间用逗号分隔用逗号分隔用用指指定定索索引引值值的的排排列列次次序序，升升序序：ASC，降降序：序：DESC。缺省值：缺省值：ASCUNIQUE表表明明此此索索引引的的每每一一个个索索引引值值只只对对应应唯唯一一的的数据记录数据记录CLUSTER表示要建立的索引是聚簇索引表示要建立的索引是聚簇索引上一页上一页

26、下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL唯一值索引唯一值索引对于已含重复值的属性列不能建对于已含重复值的属性列不能建UNIQUE索引索引对对某某个个列列建建立立UNIQUE索索引引后后，插插入入新新记记录录时时DBMS会会自自动动检检查查新新记记录录在在该该列列上上是是否否取取了了重重复复值。这相当于增加了一个值。这相当于增加了一个UNIQUE约束约束上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL例题例题例例6为为学学生生-课课程

27、程数数据据库库中中的的Student，Course，SC三三个个表表建建立立索索引引。其其中中Student表表按按学学号号升升序序建建唯唯一一索索引引，Course表表按按课课程程号号升升序序建建唯唯一一索索引引，SC表表按按学学号号升升序序和课程号降序建唯一索引。和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原

28、理第第3章章关系数据库的标准语言关系数据库的标准语言SQL聚簇索引聚簇索引建建立立聚聚簇簇索索引引后后，基基表表中中数数据据也也需需要要按按指指定定的的聚聚簇簇属属性性值值的的升升序序或或降降序序存存放放。也也即即聚聚簇簇索索引引的的索索引项顺序与表中记录的物理顺序一致引项顺序与表中记录的物理顺序一致例：例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在在Student表表的的Sname（姓姓名名）列列上上建建立立一一个个聚聚簇簇索索引，而引，而且且Student表中的记录表中的记录将按照将按照Sname值的升序存放值的升序存放上一页上一页下一下一页页

29、返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL聚簇索引聚簇索引（续）（续）在一个基本表上最多只能建立一个聚簇索引在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询，可聚簇索引的用途：对于某些类型的查询，可以提高查询效率以提高查询效率聚簇索引的适用范围聚簇索引的适用范围很少对基表进行增删操作很少对基表进行增删操作很少对其中的变长列进行修改操作很少对其中的变长列进行修改操作上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL索引与聚簇索

30、引索引与聚簇索引索引相当于汉语字典里的索引。如果想在字典索引相当于汉语字典里的索引。如果想在字典里查一个字，一页一页的翻非常慢，但如果使里查一个字，一页一页的翻非常慢，但如果使用字典的索引（拼音、偏旁、笔画）就可以很用字典的索引（拼音、偏旁、笔画）就可以很快查到。快查到。字典里有几种类型的索引（拼音、偏旁、笔画）字典里有几种类型的索引（拼音、偏旁、笔画），字典本身也是有序的。一般按拼音排序，这，字典本身也是有序的。一般按拼音排序，这里拼音就是聚簇索引。也就是说聚簇索引的组里拼音就是聚簇索引。也就是说聚簇索引的组织顺序和数据本身的组织顺序是一致的。聚簇织顺序和数据本身的组织顺序是一致的。聚簇索引

31、也只有一个，因为数据只能按一种方式排索引也只有一个，因为数据只能按一种方式排序。序。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL聚簇索引与非聚簇索引聚簇索引与非聚簇索引聚簇索引的好处是执行查找一批数据比较快，因为数聚簇索引的好处是执行查找一批数据比较快，因为数据已经按聚簇索引排好序了，很少据已经按聚簇索引排好序了，很少io操作就可以从数据操作就可以从数据库中取出。例如需要查找从库中取出。例如需要查找从a到到c的汉字，只需查找的汉字，只需查找a的的开始页和开始页和c的结束页，不用一页页查找。的结束页，不用一页

32、页查找。非聚簇索引组织顺序与数据组织顺序不一致。当查找非聚簇索引组织顺序与数据组织顺序不一致。当查找一条数据时其与非聚簇索引的效率差别不大，但查找一条数据时其与非聚簇索引的效率差别不大，但查找一批数据时，非聚簇索引的一批数据时，非聚簇索引的IO可能是聚簇索引的可能是聚簇索引的n倍，倍，因为非聚簇索引需要一条一条查找。因为非聚簇索引需要一条一条查找。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL组合索引组合索引组合索引在索引的组合索引在索引的key中保存了所有组成该组中保存了所有组成该组合索引的字段，但只按第一

33、个字段进行排序。合索引的字段，但只按第一个字段进行排序。Key中保存的内容多，一般需要更大空间。中保存的内容多，一般需要更大空间。好处时如果查询所需要返回的数据字段都在组好处时如果查询所需要返回的数据字段都在组合索引的字段中，数据库将不需访问数据页，合索引的字段中，数据库将不需访问数据页，而直接返回索引中的字段值，可以加快查询速而直接返回索引中的字段值，可以加快查询速度。假如查询不满足这个条件，组合索引就没度。假如查询不满足这个条件，组合索引就没有意义，反而浪费了存储空间。有意义，反而浪费了存储空间。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据

34、库的标准语言关系数据库的标准语言SQL使用索引的原则使用索引的原则1、在需要经常搜索的列上创建索引、在需要经常搜索的列上创建索引2、经常用于连接的列上创建索引、经常用于连接的列上创建索引3、经常需要根据范围进行搜索的列上创建索引、经常需要根据范围进行搜索的列上创建索引4、经常需要排序的列上创建索引、经常需要排序的列上创建索引5、经常用于、经常用于where子句的列上创建索引子句的列上创建索引不创建索引的原则：不创建索引的原则：1、查询很少使用和参考的列不建索引、查询很少使用和参考的列不建索引2、对只有少数值的列不建索引、对只有少数值的列不建索引3、定义为、定义为text、image、bit的列

35、不建索引的列不建索引4、当需要、当需要update性能远远高于性能远远高于select性能时不应建索性能时不应建索上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL二、删除索引二、删除索引DROPINDEX；删除索引时，系统会从数据字典中删去有关该索引删除索引时，系统会从数据字典中删去有关该索引的描述。的描述。例例7删除删除Student表的表的Stusname索引。索引。DROPINDEXStusname；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言

36、关系数据库的标准语言SQL3.5查查询询语句格式语句格式SELECTALL|DISTINCT，FROM，WHEREGROUPBYHAVINGORDERBYASC|DESC；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL语句格式语句格式SELECT子句子句：指定要显示的属性列：指定要显示的属性列FROM子句子句：指定查询对象：指定查询对象(基本表或视图基本表或视图)WHERE子句子句：指定查询条件：指定查询条件GROUPBY子子句句：对对查查询询结结果果按按指指定定列列的的值值分分组组，该该属属性性列列值值相相

37、等等的的元元组组为为一一个个组组。通通常常会会在在每每组组中中作用集函数。作用集函数。HAVING短语短语：筛选出只有满足指定条件的组：筛选出只有满足指定条件的组ORDERBY子句子句：对查询结果表按指定列值的升序：对查询结果表按指定列值的升序或降序排序或降序排序上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.5.2单表查询单表查询查询仅涉及一个表，是一种最简单的查询操作查询仅涉及一个表，是一种最简单的查询操作一、选择表中的若干列一、选择表中的若干列二、选择表中的若干元组二、选择表中的若干元组三、对查询结果

38、排序三、对查询结果排序四、使用集函数四、使用集函数五、对查询结果分组五、对查询结果分组上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL查询指定列查询指定列例例1查询全体学生的学号与姓名。查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；例例2查询全体学生的姓名、学号、所在系。查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；例例3查询全体学生的详细记录。查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，Sd

39、eptFROMStudent；或或SELECT*FROMStudent；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.查询经过计算的值查询经过计算的值SELECT子句的子句的为表达式为表达式算术表达式算术表达式字符串常量字符串常量函数函数列别名列别名上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.查询经过计算的值查询经过计算的值 例例4 4 查全体学生的姓名及其出生年份。查全体学生的姓名及其出生年份。SELECT SE

40、LECT SnameSname，2010-Sage2010-SageFROM StudentFROM Student； 例例5 5 查查询询全全体体学学生生的的姓姓名名、出出生生年年份份和和所所有有系系，要要求求用用小小写字母表示所有系名。写字母表示所有系名。SELECT SELECT SnameSname，Year of Birth: Year of Birth: ，2000-Sage 2000-Sage ISLOWER(SdeptISLOWER(Sdept) )FROM StudentFROM Student； 使用列使用列别名别名改变查询结果的列标题改变查询结果的列标题 SELECT S

41、ELECT SnameSname NAME NAME，Year of Birth: Year of Birth: BIRTH BIRTH， 2010-Sage BIRTHDAY2010-Sage BIRTHDAY，ISLOWER(SdeptISLOWER(Sdept) DEPARTMENT) DEPARTMENT FROM Student FROM Student；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQLALL ALL 与与 DISTINCT DISTINCT例例6查询选修了课程的学生学号。查询选修了课

42、程的学生学号。(1)SELECTSnoFROMSC;或或(默认默认ALL)SELECTALLSnoFROMSC;(2)SELECTDISTINCTSnoFROMSC;上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL例题（续）例题（续）注意注意DISTINCT短语的作用范围是所有目标列短语的作用范围是所有目标列例：查询选修课程的各种成绩例：查询选修课程的各种成绩错误的写法错误的写法SELECTDISTINCTCno，DISTINCTScoreFROMSC;正确的写法正确的写法SELECTDISTINCTCno，S

43、coreFROMSC;上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL2.查询满足条件的元组查询满足条件的元组WHERE子句常用的查询条件子句常用的查询条件查询条件查询条件谓词谓词比较比较=，=,=,!=,!,!;NOT+上述比较运算符上述比较运算符确定范围确定范围BETWEENAND,NOTBETWEENAND确定集合确定集合IN,NOTIN字符匹配字符匹配LIKE,NOTLIKE空值空值ISNULL,ISNOTNULL多重条件多重条件AND,OR,NOT上一页上一页下一下一页页返回本章首页返回本章首页数据库

44、系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL(1)比较大小比较大小在在WHERE子句的子句的中使用比较运算符中使用比较运算符=，=，=，!=或或，!，!，逻辑运算符逻辑运算符NOT+比较运算符比较运算符例例8查询所有年龄在查询所有年龄在20岁以下的学生姓名及其年龄。岁以下的学生姓名及其年龄。SELECTSname，SageFROMStudentWHERESage=20；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL(2)确定范围确定范围使用谓词使用谓词BETWEENANDNO

45、TBETWEENAND例例10查询年龄在查询年龄在/不在不在2023岁（包括岁（包括20岁和岁和23岁）之岁）之间的学生的姓名、系别和年龄间的学生的姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL(3)确定集合确定集合使用谓词使用谓词IN,NOTIN：用逗号分隔的一组取值用逗号分隔的一组取值例例12查询信息系（查询信息系（IS）、）、数学系（数学系（MA）和计算机科学和计算机

46、科学系（系（CS）学生的姓名和性别。学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN(IS，MA，CS);上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL(4)字符串匹配字符串匹配NOTLIKEESCAPE：指定匹配模板：指定匹配模板匹配模板匹配模板：固定字符串或含通配符的字符串：固定字符串或含通配符的字符串当匹配模板为固定字符串时，当匹配模板为固定字符串时，可以用可以用=运算符取代运算符取代LIKE谓词谓词用用!=或或运算符取代运算符取代NOTLIKE谓词谓词上

47、一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL通配符通配符w%(百分号百分号)代表任意长度（长度可以为代表任意长度（长度可以为0）的字符串）的字符串例：例：a%b表示以表示以a开头，以开头，以b结尾的任意长度的字符结尾的任意长度的字符串。如串。如acb，addgb，ab等都满足该匹配串等都满足该匹配串_(下横线下横线)代表任意单个字符代表任意单个字符例：例：a_b表示以表示以a开头，以开头，以b结尾的长度为结尾的长度为3的任意字的任意字符串。如符串。如acb，afb等都满足该匹配串等都满足该匹配串当用户要查询的

48、字符串本身就含有当用户要查询的字符串本身就含有%或或_时，要使用时，要使用ESCAPE短语对通配符进行转义。短语对通配符进行转义。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL例题例题1)匹配模板为固定字符串匹配模板为固定字符串例例14查询学号为查询学号为95001的学生的详细情况。的学生的详细情况。SELECT*FROMStudentWHERESnoLIKE95001；等价于：等价于：SELECT*FROMStudentWHERESno=95001；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统

49、原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL匹配模板为含通配符的字符串匹配模板为含通配符的字符串例例15查询所有姓刘学生的姓名、学号和性别。查询所有姓刘学生的姓名、学号和性别。SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE刘刘%；例例16查询姓查询姓欧阳欧阳且全名为三个汉字的学生的姓名。且全名为三个汉字的学生的姓名。SELECTSnameFROMStudentWHERESnameLIKE欧阳欧阳_；例例17查询名字中第查询名字中第2个字为个字为阳阳字的学生的姓名和学号。字的学生的姓名和学号。SELECTSname，Sno

50、FROMStudentWHERESnameLIKE_阳阳%；例例18查询所有不姓刘的学生姓名。查询所有不姓刘的学生姓名。SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE刘刘%；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL使用转义字符将通配符转义为普通字符使用转义字符将通配符转义为普通字符例例19查询查询DB_Design课程的课程号和学分。课程的课程号和学分。SELECTCno，CcreditFROMCourseWHERECnameLIKEDB_DesignE

51、SCAPE例例20查询以查询以DB_开头，且倒数第开头，且倒数第3个字符为个字符为i的课程的的课程的详细情况。详细情况。SELECT*FROMCourseWHERECnameLIKEDB_%i_ESCAPE；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL(5)涉及空值的查询涉及空值的查询 使用谓词使用谓词 IS NULL IS NULL 或或 IS NOT NULLIS NOT NULL “IS NULL” “IS NULL” 不能用不能用 “ “= NULL” = NULL” 代替代替 例例21 21 某些

52、学生选修课程后没有参加考试，所以有选课记录，但某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。 SELECT Sno，Cno FROM SC WHERE Score IS NULL；例例22查所有有成绩的学生学号和课程号。查所有有成绩的学生学号和课程号。SELECTSno，CnoFROMSCWHEREScoreISNOTNULL；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL(6)多重条件查询多重条件

53、查询用逻辑运算符用逻辑运算符AND和和OR来联结多个查询条件来联结多个查询条件AND的优先级高于的优先级高于OR可以用括号改变优先级可以用括号改变优先级可用来实现多种其他谓词可用来实现多种其他谓词NOTINNOTBETWEENAND例例23查询计算机系年龄在查询计算机系年龄在20岁以下的学生姓名。岁以下的学生姓名。SELECTSnameFROMStudentWHERESdept=CSANDSage=20ANDSage=23；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL三、对查询结果排序三、对查询结果排序使用

54、使用ORDERBY子句子句可以按一个或多个属性列排序可以按一个或多个属性列排序升序：升序：ASC；降序：降序：DESC；缺省值为升序缺省值为升序当排序列含空值时当排序列含空值时ASC：排序列为空值的元组最后显示排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示排序列为空值的元组最先显示上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL排序实例排序实例例例24查查询询选选修修了了3号号课课程程的的学学生生的的学学号号及及其其成成绩绩，查查询询结结果果按分数降序排列。按分数降序排列。SELECTSno，S

55、coreFROMSCWHERECno=3ORDERBYScoreDESC；例例25查查询询全全体体学学生生情情况况，查查询询结结果果按按所所在在系系的的系系号号升升序序排排列，同一系中的学生按年龄降序排列。列，同一系中的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdept，SageDESC；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL四、使用集函数四、使用集函数计数计数COUNT（DISTINCT|ALL*）COUNT（DISTINCT|ALL）计算总和计算总和SUM（DISTI

56、NCT|ALL）计算平均值计算平均值AVG（DISTINCT|ALL）求最大值求最大值MAX（DISTINCT|ALL）求最小值求最小值MIN（DISTINCT|ALL）DISTINCT短语：在计算时要取消指定列中的重复值短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值短语：不取消重复值ALL为缺省值为缺省值上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL使用集函数实例使用集函数实例例例26查询学生总人数。查询学生总人数。SELECTCOUNT(*)FROMStudent；例例27查询选修了课程的学

57、生人数。查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSno)FROMSC；例例28计算计算1号课程的学生平均成绩。号课程的学生平均成绩。SELECTAVG(Score)FROMSCWHERECno=1；例例29查询选修查询选修1号课程的学生最高分数。号课程的学生最高分数。SELECTMAX(Score)FROMSCWHERCno=1；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL五、对查询结果分组五、对查询结果分组使用使用GROUPBY子句分组子句分组细化集函数的作用对象细化集函数的作用

58、对象未对查询结果分组，集函数将作用于整个查询结果未对查询结果分组，集函数将作用于整个查询结果对查询结果分组后，集函数将分别作用于每个组对查询结果分组后，集函数将分别作用于每个组GROUPBY子句的作用对象是查询的中间结果表子句的作用对象是查询的中间结果表分组方法：按指定的一列或多列值分组，值相等的为一组分组方法：按指定的一列或多列值分组，值相等的为一组使使用用GROUPBY子子句句后后，SELECT子子句句的的列列名名列列表表中中只只能能出出现现分组属性和集函数分组属性和集函数上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的

59、标准语言SQLGROUP BYGROUP BY子句示例子句示例例例30求各个课程号及相应的选课人数。求各个课程号及相应的选课人数。SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL使用使用HAVINGHAVING短语筛选最终输出结果短语筛选最终输出结果只有满足只有满足HAVING短语指定条件的组才输出短语指定条件的组才输出HAVING短语与短语与WHERE子句的区别：作用对象不同子句的区别：作用对象不同WHERE子子句句作作用用于于基基表表

60、或或视视图图，从从中中选选择择满满足足条条件的元组。件的元组。HAVING短语作用于组，从中选择满足条件的组短语作用于组，从中选择满足条件的组。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL使用使用HAVING短语筛选最终输出结果短语筛选最终输出结果例例31查询选修了查询选修了3门以上课程的学生学号。门以上课程的学生学号。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)3；例例32查查询询有有3门门以以上上课课程程是是90分分以以上上的的学学生生的的学学号号及及（90分分以以上上

61、的的）课程数课程数SELECTSno,COUNT(*)FROMSCWHEREScore=90GROUPBYSnoHAVINGCOUNT(*)=3;上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.3.3连接查询连接查询同时涉及多个表的查询称为连接查询同时涉及多个表的查询称为连接查询用来连接两个表的条件称为连接条件或连接谓词用来连接两个表的条件称为连接条件或连接谓词 一般格式：一般格式：. 1 .2 比较运算符：比较运算符：= =、 、 =、=、!=!=. 1 BETWEENBETWEEN . 2 ANDAND

62、 .3连接字段连接字段连接谓词中的列名称为连接字段连接谓词中的列名称为连接字段连接条件中的各连接字段类型必须是可比的，但不必是相连接条件中的各连接字段类型必须是可比的，但不必是相同的同的上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQLSQL中连接查询的主要类型中连接查询的主要类型广义笛卡尔积广义笛卡尔积 等值连接等值连接(含自然连接含自然连接) 非等值连接查询非等值连接查询 自身连接查询自身连接查询 外连接查询外连接查询 复合条件连接查询复合条件连接查询上一页上一页下一下一页页返回本章首页返回本章首页数据库系统

63、原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL一、广义笛卡尔积一、广义笛卡尔积不带连接谓词的连接不带连接谓词的连接很少使用很少使用例：例：SELECTStudent.*,SC.*FROMStudent,SC上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL二、等值与非等值连接查询二、等值与非等值连接查询等值连接等值连接连接运算符为连接运算符为=的连接操作的连接操作.=.任任何何子子句句中中引引用用表表1和和表表2中中同同名名属属性性时时，都都必必须须加加表表名名前前缀。引用唯一属性名缀

64、。引用唯一属性名时可以加也可以省略表名前缀。时可以加也可以省略表名前缀。SELECTStudent.*，SC.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL自然连接自然连接等等值值连连接接的的一一种种特特殊殊情情况况，把把目目标标列列中中重重复复的的属性列去掉。属性列去掉。例例33对对例例32用自然连接完成。用自然连接完成。SELECTStudent.Sno，Sname，Ssex，Sage， Sdept，Cno，ScoreFROMS

65、tudent，SCWHEREStudent.Sno=SC.Sno；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL非等值连接查询非等值连接查询连接运算符连接运算符不是不是=的连接操作的连接操作.比较运算符：比较运算符：、=、=、!=. BETWEEN .AND.上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL三、自身连接三、自身连接一个表与其自己进行连接，称为表的自身连接一个表与其自己进行连接，称为表的自身连接需要给表起别名以示

66、区别需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前由于所有属性名都是同名属性，因此必须使用别名前缀缀例例34查询至少选修两门课的同学学号查询至少选修两门课的同学学号SELECTSC1.SnoFROMSCsc1，SCsc2WHEREsc1.sno=sc2.snoANDo!=o；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL四、外连接四、外连接（OuterJoin）外连接与普通连接的区别外连接与普通连接的区别普通连接操作只输出满足连接条件的元组普通连接操作只输出满足连接条件的元组外连接操作以

67、指定表为连接主体，将主体表中不满外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出足连接条件的元组一并输出例例33查查询询每每个个学学生生及及其其选选修修课课程程的的情情况况包包括括没没有有选选修修课程的学生课程的学生-用外连接操作用外连接操作SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，ScoreFROMStudentLEFTOUTERJOINSCONStudent.Sno=SC.Sno；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL外连接（续外连

68、接（续）左外连接左外连接包括内连接和在左表中但内连接不会返回的那些行。包括内连接和在左表中但内连接不会返回的那些行。LEFTOUTERJOIN（或（或LEFTJOIN）右外连接右外连接包括内连接和在右表中但内连接不会返回的那些行。包括内连接和在右表中但内连接不会返回的那些行。RIGHTOUTERJOIN(或或RIGHTJOIN)全外连接全外连接包括内连接和在左表、右表中但内连接不会返回的那些行。包括内连接和在左表、右表中但内连接不会返回的那些行。FULLOUTERJOIN(或或FULLJOIN)上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标

69、准语言关系数据库的标准语言SQL五、复合条件连接五、复合条件连接WHERE子子句句中中含含多多个个连连接接条条件件时时，称称为为复复合合条条件件连接连接例例35查询选修查询选修2号课程且成绩在号课程且成绩在90分以上的所有分以上的所有学生的学号、姓名学生的学号、姓名SELECTStudent.Sno,student.SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=2ANDSC.Score90；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL多表连接多表连接

70、例例36查询每个学生的学号、姓名、选修的课程名及成绩。查询每个学生的学号、姓名、选修的课程名及成绩。SELECTStudent.Sno，Sname，Cname，ScoreFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cno；或：或：SELECTs.Sno,Sname,Cname,ScoreFROMStudentsINNERJOIN(SELECTSno,CName,ScoreFROMCoursecINNERJOINSCONc.Cno=SC.Cno)tONt.sno=s.sno上一页上一页下一下一页页返回本章首页返回本章首

71、页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL嵌套查询嵌套查询嵌套查询概述嵌套查询概述一个一个SELECT-FROM-WHERE语句称为一个查询块语句称为一个查询块将一个查询块嵌套在另一个查询块的将一个查询块嵌套在另一个查询块的WHERE子句或子句或HAVING短语的条件中的查询称为嵌套查询短语的条件中的查询称为嵌套查询SELECTSname外层查询外层查询/父查询父查询FROMStudentWHERESnoIN（SELECTSno内层查询内层查询/子查询子查询FROMSCWHERECno=2）；）；说明：子查询中不能使用说明：子查询中不能使用ORDER

72、BY子句子句层层嵌套方式反映了层层嵌套方式反映了SQL语言的结构化语言的结构化有些嵌套查询可以用连接运算替代有些嵌套查询可以用连接运算替代上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL嵌套查询的分类及求解过程嵌套查询的分类及求解过程不相关子查询：不相关子查询：子查询的查询条件不依赖于父查询子查询的查询条件不依赖于父查询是由里向外逐层处理。即每个子查询在上一级查询处是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找理之前求解，子查询的结果用于建立其父查询的查找条件。条件

73、。相关子查询：相关子查询：子查询的查询条件依赖于父查询子查询的查询条件依赖于父查询首先取外层查询中表的第一个元组，根据它与内层查首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若询相关的属性值处理内层查询，若WHERE子句返回子句返回值为真，则取此元组放入结果表；值为真，则取此元组放入结果表；然后再取外层表的下一个元组；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。重复这一过程，直至外层表全部检查完为止。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL一、带有一、

74、带有ININ谓词的子查询谓词的子查询例例37查询与查询与“刘晨刘晨”在同一个系学习的学生。在同一个系学习的学生。SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=刘晨刘晨)；此查询为不相关子查询，可以分步做，此查询为不相关子查询，可以分步做，DBMS求解该查询时也是分求解该查询时也是分步去做的。步去做的。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL带有带有ININ谓词的子查询（续）谓词的子查询（续）用自

75、身连接完成本查询要求用自身连接完成本查询要求SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1，StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname=刘晨刘晨；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL带有带有ININ谓词的子查询（续）谓词的子查询（续）父查询和子查询中的表均可以定义别名父查询和子查询中的表均可以定义别名SELECTSno，Sname，SdeptFROMStudentS1WHERES1.SdeptIN(SELECTSdep

76、tFROMStudentS2WHERES2.Sname=刘晨刘晨)；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL带有带有ININ谓词的子查询（续）谓词的子查询（续）例例38查询选修了课程名为查询选修了课程名为“信息系统信息系统”的学生学号和姓名的学生学号和姓名SELECTSno，Sname最后在最后在Student关系中取出关系中取出FROMStudentSno和和SnameWHERESnoIN(SELECTSno然后在然后在SC关系中找出选关系中找出选FROMSC修了修了3号课程的学生学号号课程的学生学号

77、WHERECnoIN(SELECTCno首先在首先在Course关系中找出关系中找出“信信FROMCourse息系统息系统”的课程号，结果为的课程号，结果为3号号WHERECname=信息系统信息系统);上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL带有带有IN谓词的子查询（续）谓词的子查询（续）用连接查询用连接查询SELECTSno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=信息系

78、统信息系统；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL二、带有比较运算符的子查询二、带有比较运算符的子查询当能确切知道内层查询当能确切知道内层查询返回单值时，可用比较运算符时，可用比较运算符（，=，=，!=或或）。）。例：假设一个学生只可能在一个系学习，并且必须属于一个系，则例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在在例例37可以可以用用=代替代替IN：SELECTSno，Sname，SdeptFROMStudentWHERESdept=（SELECTSdeptFROMStudentWH

79、ERESname=刘晨刘晨）；）；与与ANY或或ALL谓词配合使用谓词配合使用上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL带有比较运算符的子查询（续）带有比较运算符的子查询（续）子查询一定要跟在比较符之后子查询一定要跟在比较符之后错误错误的例子：的例子：SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=刘晨刘晨)=Sdept；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数

80、据库的标准语言关系数据库的标准语言SQL相关子查询的执行过程相关子查询的执行过程找出每个学生超过他选修课程平均成绩的课程号找出每个学生超过他选修课程平均成绩的课程号SELECT SELECT Sno,CnoSno,CnoFROM SC xFROM SC xWHERE Score =(SELECT WHERE Score =(SELECT AVG(ScoreAVG(Score) ) FROM SC y FROM SC y WHERE WHERE y.Snoy.Sno= =x.Snox.Sno) )上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标

81、准语言关系数据库的标准语言SQL三、带有三、带有ANY或或ALL谓词的子查询谓词的子查询ANY：任意一个值任意一个值ANY大于子查询结果中的某个值大于子查询结果中的某个值ALL大于子查询结果中的所有值大于子查询结果中的所有值ANY小于子查询结果中的某个值小于子查询结果中的某个值=ANY大于等于子查询结果中的某个值大于等于子查询结果中的某个值=ALL大于等于子查询结果中的所有值大于等于子查询结果中的所有值=ANY小于等于子查询结果中的某个值小于等于子查询结果中的某个值=ALL小于等于子查询结果中的所有值小于等于子查询结果中的所有值=ANY等于子查询结果中的某个值等于子查询结果中的某个值=ALL等

82、于子查询结果中的所有值（通常没有实际意义）等于子查询结果中的所有值（通常没有实际意义）!=（或（或）ANY不等于子查询结果中的某个值不等于子查询结果中的某个值!=（或（或）ALL不等于子查询结果中的任何一个值不等于子查询结果中的任何一个值ALL：所有值所有值上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL带有带有ANYANY或或ALLALL谓词的子查询（续谓词的子查询（续）例例39查询其他系中比信息系某查询其他系中比信息系某一一个学生年龄小的学生个学生年龄小的学生姓名和年龄姓名和年龄SELECTSname，Sa

83、geFROMStudentWHERESageANY(SELECTSageFROMStudentWHERESdept=IS)ANDSdeptIS;/*注意这是父查询块中的条件注意这是父查询块中的条件*/上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL带有带有ANYANY或或ALLALL谓词的子查询（续）谓词的子查询（续）结果结果SnameSage王敏王敏18执行过程执行过程1.DBMS执行此查询时，首先处理子查询，找出执行此查询时，首先处理子查询，找出IS系中所有学生的年龄，构成一个集合系中所有学生的年龄，构成一

84、个集合(19，18)2.处理父查询，找所有不是处理父查询，找所有不是IS系且年龄小于系且年龄小于19或或18的学生的学生上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL带有带有ANYANY或或ALLALL谓词的子查询（续）谓词的子查询（续）ANY和和ALL谓词有时可以用集函数实现谓词有时可以用集函数实现ANY与与ALL与集函数的对应关系与集函数的对应关系=或或!=ANYIN-MAXMIN=MINALL-NOTINMINMAX=MAX用集函数实现子查询通常比直接用用集函数实现子查询通常比直接用ANY或或ALL查询

85、效率要高，查询效率要高，因为前者通常能够减少比较次数因为前者通常能够减少比较次数上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL带有带有ANY或或ALL谓词的子查询（续）谓词的子查询（续）例例39：用集函数实现：用集函数实现例例39SELECTSname，SageFROMStudentWHERESage(SELECTMIN(Sage)FROMStudentWHERESdept=IS)ANDSdeptIS;上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关

86、系数据库的标准语言SQL四、带有四、带有EXISTSEXISTS谓词的子查询谓词的子查询1.EXISTS谓词谓词2.NOTEXISTS谓词谓词3.不同形式的查询间的替换不同形式的查询间的替换4.相关子查询的效率相关子查询的效率5.用用EXISTS/NOTEXISTS实现全称量词实现全称量词6.用用EXISTS/NOTEXISTS实现逻辑蕴函实现逻辑蕴函上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL带有带有EXISTSEXISTS谓词的子查询谓词的子查询(续）续）1. EXISTS1. EXISTS谓词谓词带有

87、带有EXISTSEXISTS谓词的子查询不返回任何数据，只产生逻辑真值谓词的子查询不返回任何数据，只产生逻辑真值“true”true”或逻辑假值或逻辑假值“false”false”。若内层查询结果非空，则返回真值若内层查询结果非空，则返回真值若内层查询结果为空，则返回假值若内层查询结果为空，则返回假值由由EXISTSEXISTS引出的子查询，其目标列表达式通常都用引出的子查询，其目标列表达式通常都用* * ，因为带，因为带EXISTSEXISTS的子查询只返回真值或假值，给出列名无实际意义的子查询只返回真值或假值，给出列名无实际意义2. NOT EXISTS2. NOT EXISTS谓词谓词3

88、. 3. 不同形式的查询间的替换不同形式的查询间的替换一些带一些带EXISTSEXISTS或或NOT EXISTSNOT EXISTS谓词的子查询不能被其他形式的子查谓词的子查询不能被其他形式的子查询等价替换询等价替换所有带所有带ININ谓词、比较运算符、谓词、比较运算符、ANYANY和和ALLALL谓词的子查询都能用带谓词的子查询都能用带EXISTSEXISTS谓词的子查询等价替换。谓词的子查询等价替换。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL带有带有EXISTSEXISTS谓词的子查询谓词的子查询(

89、续）续） 例例41 41 查询所有选修了查询所有选修了1 1号课程的学生姓名。号课程的学生姓名。用嵌套查询用嵌套查询 SELECT SELECT SnameSname FROM FROM StudentStudent WHERE EXISTS WHERE EXISTS (SELECT (SELECT * * FROM SC /* FROM SC /*相关子查询相关子查询* */ / WHERE WHERE SnoSno= =Student.SnoStudent.Sno AND AND CnoCno= 1 )= 1 )；用连接运算用连接运算SELECT SELECT SnameSnameFROM

90、 Student, SCFROM Student, SCWHERE Student.Sno=WHERE Student.Sno=SC.SnoSC.Sno AND AND SC.CnoSC.Cno= 1;= 1;上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL带有带有EXISTS谓词的子查询谓词的子查询(续）续） 例例42 42 查询没有选修查询没有选修1 1号课程的学生姓名。号课程的学生姓名。 SELECT SELECT SnameSname FROM Student FROM Student WHERE NO

91、T EXISTS WHERE NOT EXISTS (SELECT * (SELECT * FROM SC FROM SC WHERE WHERE SnoSno = Student.Sno = Student.Sno AND AND CnoCno=1)=1)；此例用连接运算难于实现此例用连接运算难于实现上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL带有带有EXISTSEXISTS谓词的子查询谓词的子查询(续）续） 例例43 43 查询选修了全部课程的学生姓名。查询选修了全部课程的学生姓名。SELECT SEL

92、ECT SnameSnameFROM StudentFROM StudentWHERE NOT EXISTSWHERE NOT EXISTS （SELECT *SELECT * FROM Course FROM Course WHERE NOT EXISTS WHERE NOT EXISTS (SELECT * (SELECT * FROM SC FROM SC WHERE WHERE SC.SnoSC.Sno= Student.Sno= Student.Sno AND AND SC.CnoSC.Cno= = Course.CnoCourse.Cno）上一页上一页下一下一页页返回本章首页返回本

93、章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL带有带有EXISTS谓词的子查询谓词的子查询(续）续） 例44 查询至少选修了学生95002选修的全部课程的学生号码。 SELECT DISTINCT Sno FROM SC SCX WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = 95002 AND NOT EXISTS (SELECT * FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno)；上一页上一页下一下一页页返回本章首页返回本章

94、首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.3.5 3.3.5 集合查询集合查询v一般商用数据库支持的集合操作种类一般商用数据库支持的集合操作种类并操作并操作( (UNION) )交操作交操作( (INTERSECT) )差操作差操作( (EXCEPT) )v语法语法UNION（INTERSECT、EXCEPT）v说明：参加运算的各结果表的列数必须相同；对应项的数据类型也必须相同上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL并操作并操作例例45查询计算机科学系的

95、学生及年龄不大于查询计算机科学系的学生及年龄不大于19岁的学岁的学生。生。方法一：方法一：SELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage=19；/WHERESdept=CSORSage=19；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL并操作并操作例例46查询选修了课程查询选修了课程1或者选修了课程或者选修了课程2的学生。的学生。方法一：方法一：SELECTSnoFROMSCWHERECno=1UNIONSELECTSnoFRO

96、MSCWHERECno=2；/WHERECno=1ORCno=2；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL并操作并操作例例47查询学校中所有师生的姓名。查询学校中所有师生的姓名。SELECTSnameFROMStudentUNIONSELECTTnameFROMTeacher;上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL例例48查询计算机科学系中年龄不大于查询计算机科学系中年龄不大于19岁的学生的姓名岁的学生的姓名S

97、ELECTSNameFROMStudentWHERESdept=CSINTERSECTSELECTSNameFROMStudentWHERESage=19交操作交操作上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3差操作差操作例例49查询没有选修查询没有选修1号课程的学生的学号号课程的学生的学号。SELECTSnoFROMStudentEXCEPTSELECTSnoFROMSCWHERECno=1上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据

98、库的标准语言SQL4.对集合操作结果的排序对集合操作结果的排序ORDERBY子句只能用于对最终查询结果排序，子句只能用于对最终查询结果排序，不能对中间结果排序不能对中间结果排序任何情况下，任何情况下，ORDERBY子句只能出现在最后子句只能出现在最后对集合操作结果排序时，对集合操作结果排序时，ORDERBY子句中用子句中用数字指定排序属性数字指定排序属性new上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL对集合操作结果的排序（续）对集合操作结果的排序（续）例例53错误写法错误写法SELECT*FROMStud

99、entWHERESdept=CSORDERBYSnoUNIONSELECT*FROMStudentWHERESage=19ORDERBYSno；new上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL对集合操作结果的排序（续对集合操作结果的排序（续）正确写法正确写法SELECT*FROMStudentWHERESdept=CSUNIONSELECT*FROMStudentWHERESage=19ORDERBY1；new上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库

100、的标准语言关系数据库的标准语言SQL3.3.6SELECTSELECT语句的一般格式语句的一般格式SELECTALL|DISTINCT别名别名FROM别名别名WHEREGROUPBY，.HAVINGORDERBYASC|DESC；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.4.1插入数据插入数据两种插入数据方式两种插入数据方式插入单个元组插入单个元组插入子查询结果插入子查询结果上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言S

101、QL1.插入单个元插入单个元组组语句格式语句格式INSERTINTO(，)VALUES(，)功能功能将新元组插入指定表中。将新元组插入指定表中。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL插入单个元组（续）插入单个元组（续）INTO子句子句指定要插入数据的表名及属性列指定要插入数据的表名及属性列属性列的顺序可与表定义中的顺序不一致属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组，且属性没有指定属性列：表示要插入的是一条完整的元组，且属性列属性与表定义中的顺序一致列属性与表定义

102、中的顺序一致指定部分属性列：插入的元组在其余属性列上取空值指定部分属性列：插入的元组在其余属性列上取空值VALUES子句子句提供的值必须与提供的值必须与INTO子句匹配子句匹配值的个数值的个数值的类型值的类型上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL插入单个元组（续）插入单个元组（续）例例1将一个新学生记录将一个新学生记录（学号：（学号：95020；姓名：陈冬；性别：男；所在系：；姓名：陈冬；性别：男；所在系：IS；年龄：年龄：18岁）插入到岁）插入到Student表中。表中。INSERTINTOStud

103、entVALUES(95020，陈冬陈冬，男男，IS，18)；例例2插入一条选课记录插入一条选课记录(95020，1)。INSERTINTOSC(Sno，Cno)VALUES(95020，1)；新插入的记录在新插入的记录在Score列上取空值列上取空值上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL2.插入子查询结果插入子查询结果语句格式语句格式INSERTINTO(，)子查询子查询；功能功能将子查询结果插入指定表中将子查询结果插入指定表中上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统

104、原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL插入子查询结果（续）插入子查询结果（续）例例3对每一个系，求学生的平均年龄，并把结果存入数据库。对每一个系，求学生的平均年龄，并把结果存入数据库。第一步：建表第一步：建表CREATETABLEDeptage(SdeptCHAR(15)，/*系名系名*/AvgageSMALLINT)；/*学生平均年龄学生平均年龄*/第二步：插入数据第二步：插入数据INSERTINTODeptage(Sdept，Avgage)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；上一页上一页下一下一页页返回本章首页返回

105、本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL插入子查询结果（续）插入子查询结果（续）INTO子句子句(与插入单条元组类似与插入单条元组类似)指定要插入数据的表名及属性列指定要插入数据的表名及属性列属性列的顺序可与表定义中的顺序不一致属性列的顺序可与表定义中的顺序不一致没有指定属性列：表示要插入的是一条完整的元组没有指定属性列：表示要插入的是一条完整的元组指定部分属性列：插入的元组在其余属性列上取空值指定部分属性列：插入的元组在其余属性列上取空值子查询子查询SELECT子句目标列必须与子句目标列必须与INTO子句匹配子句匹配值的个数值的个数值的类型

106、值的类型上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL插入子查询结果（续）插入子查询结果（续）DBMS在执行插入语句时会检查所插元组是在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则否破坏表上已定义的完整性规则实体完整性实体完整性参照完整性参照完整性用户定义的完整性用户定义的完整性对于有对于有NOTNULL约束的属性列是否提供了非空值约束的属性列是否提供了非空值对于有对于有UNIQUE约束的属性列是否提供了非重复值约束的属性列是否提供了非重复值对于有值域约束的属性列所提供的属性值是否在值域范围内对

107、于有值域约束的属性列所提供的属性值是否在值域范围内上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.4.2修改数据修改数据语句格式UPDATESET=，=WHERE；功能修改指定表中满足修改指定表中满足WHERE子句条件的元组子句条件的元组SET子句指定修改方式指定修改方式要修改的列要修改的列修改后取值修改后取值WHERE子句指定要修改的元组指定要修改的元组,缺省表示要修改表中的所有元组缺省表示要修改表中的所有元组上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库

108、的标准语言关系数据库的标准语言SQL例例4将学生将学生95001的年龄改为的年龄改为22岁。岁。UPDATEStudentSETSage=22WHERESno=95001；例例5将所有学生的年龄增加将所有学生的年龄增加1岁。岁。UPDATEStudentSETSage=Sage+1；例例6将信息系所有学生的年龄增加将信息系所有学生的年龄增加1岁。岁。UPDATEStudentSETSage=Sage+1WHERESdept=IS；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.带子查询的修改语句带子查询的修

109、改语句UPDATESCSETScore=0WHEREsnoin(SELETEsnoFROMStudentWHEREdept=cs)；例例7将计算机科学系全体学生的成绩置零。将计算机科学系全体学生的成绩置零。UPDATESCSETScore=0WHERECS=(SELETEsdeptFROMStudentWHEREsno=sc.sno)；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL修改数据（续）修改数据（续）DBMS在执行修改语句时会检查修改操作在执行修改语句时会检查修改操作是否破坏表上已定义的完整性规则是否

110、破坏表上已定义的完整性规则实体完整性实体完整性主码不允许修改主码不允许修改用户定义的完整性用户定义的完整性NOTNULL约束约束UNIQUE约束约束值域约束值域约束上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.4.3删除数据删除数据语法DELETEFROMWHERE；功能w删除指定表中满足删除指定表中满足WHERE子句条件的子句条件的元组元组WHERE子句w指定要删除的元组指定要删除的元组w缺省表示要修改表中的所有元组缺省表示要修改表中的所有元组三种删除方式删除某一个元组的值删除某一个元组的值删除多个元组

111、的值删除多个元组的值带子查询的删除语句带子查询的删除语句上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL1.删除一个删除一个/多个元组的值多个元组的值例例8删除学号为删除学号为95019的学生记录。的学生记录。DELETEFROMStudentWHERESno=95019；例例9删除删除2号课程的所有选课记录。号课程的所有选课记录。DELETEFROMSC；WHERECno=2；例例10删除所有的学生选课记录。删除所有的学生选课记录。DELETEFROMSC；上一页上一页下一下一页页返回本章首页返回本章首页数据

112、库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.带子查询的删除语句带子查询的删除语句例例11删除计算机科学系所有学生的选课记录。删除计算机科学系所有学生的选课记录。DELETEFROMSCWHERECS=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL删除数据删除数据(续续)DBMS在执行插入语句时会检查所插元组在执行插入语句时会检查所插元组是否破坏表上已定义的完整性规则是否破坏表

113、上已定义的完整性规则参照完整性参照完整性不允许删除不允许删除级联删除级联删除上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL更新数据与数据一致性更新数据与数据一致性DBMS在执行插入、删除、更新语句时必在执行插入、删除、更新语句时必须保证数据库一致性须保证数据库一致性l必须有事务的概念和原子性必须有事务的概念和原子性l完整性检查和保证完整性检查和保证上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.5视视图图视图的特点视图的特

114、点虚表，是从一个或几个基本表（或视图）导出虚表，是从一个或几个基本表（或视图）导出的表的表只存放视图的定义，不会出现数据冗余只存放视图的定义，不会出现数据冗余基表中的数据发生变化，从视图中查询出的数基表中的数据发生变化，从视图中查询出的数据也随之改变据也随之改变上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL数据库系统的三级模式结构数据库系统的三级模式结构 应用应用A A应用应用B B应用应用C C应用应用D D应用应用E E外模式外模式1 1外模式外模式2 2外模式外模式3 3外模式外模式/ /模式映象模式映

115、象模式模式模式模式/ /内模式映象内模式映象内模式内模式数据库数据库DB上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.5视视图图基于视图的操作基于视图的操作查询查询删除删除受限更新受限更新定义基于该视图的新视图定义基于该视图的新视图上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL1.建立视图建立视图语句格式语句格式CREATEVIEW(，)ASWITHCHECKOPTION；说明说明：DBMS执行执行CREATEVIEW语

116、句时只是把视图的定义存入数据字语句时只是把视图的定义存入数据字典，并不执行其中的典，并不执行其中的SELECT语句。语句。在对视图查询时，按视图的定义从基本表中将数据查出。在对视图查询时，按视图的定义从基本表中将数据查出。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL组成视图的属性列名组成视图的属性列名全部省略或全部指定全部省略或全部指定省略省略:由子查询中由子查询中SELECT目标列中的诸字段组成目标列中的诸字段组成以下情况需明确指定视图的所有列名以下情况需明确指定视图的所有列名:(1)某个目标列是集函数或

117、列表达式某个目标列是集函数或列表达式(2)多表连接时选出了几个同名列作为视图的字段多表连接时选出了几个同名列作为视图的字段(3)需要在视图中为某个列启用新的更合适的名字需要在视图中为某个列启用新的更合适的名字上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL行列子集视图行列子集视图例例1建立信息系学生的视图。建立信息系学生的视图。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept=IS；从单个基本表导出从单个基本表导出只是去掉了基本表的某

118、些行和某些列只是去掉了基本表的某些行和某些列保留了码保留了码上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQLWITHCHECKOPTION的视图的视图例例2建立信息系学生的视图，并要求透过该视图进行的建立信息系学生的视图，并要求透过该视图进行的更新操作只涉及信息系学生。更新操作只涉及信息系学生。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept=ISWITHCHECKOPTION；WITH CHECK OPTION表示对视图进行更新时，

119、必须满足视图表示对视图进行更新时，必须满足视图定义时的谓词条件定义时的谓词条件上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL基于多个基表的视图基于多个基表的视图例例4建立信息系选修了建立信息系选修了1号课程的学生视图。号课程的学生视图。CREATEVIEWIS_S1(Sno，Sname，Score)ASSELECTStudent.Sno，Sname，ScoreFROMStudent，SCWHERESdept=ISANDStudent.Sno=SC.SnoANDSC.Cno=1；上一页上一页下一下一页页返回本章

120、首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL基于视图的视图基于视图的视图例例5建立信息系选修了建立信息系选修了1号课程且成绩在号课程且成绩在90分分以上的学生的视图。以上的学生的视图。CREATEVIEWIS_S2ASSELECTSno，Sname，ScoreFROMIS_S1WHEREScore=90；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL带表达式的视图带表达式的视图例例6定义一个反映学生出生年份的视图。定义一个反映学生出生年份的视图。CREA

121、TEVIEWBT_S(Sno，Sname，Sbirth)ASSELECTSno，Sname，2000-SageFROMStudent设置一些派生属性列设置一些派生属性列,也称为虚拟列也称为虚拟列-Sbirth带表达式的视图必须明带表达式的视图必须明确定义组成视图的各个属性列名确定义组成视图的各个属性列名上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL建立分组视图建立分组视图例例7将学生的学号及他的平均成绩定义为一个视图将学生的学号及他的平均成绩定义为一个视图假设假设SC表中表中“成绩成绩”列列Score为数字型

122、为数字型CREATVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Score)FROMSCGROUPBYSno；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL建立视图（续建立视图（续）例例8将将Student表中所有女生记录定义为一个视图表中所有女生记录定义为一个视图CREATEVIEWF_Student1(stdnum，name，sex，age，dept)ASSELECT*FROMStudentWHERESsex=女女；缺点：修改基表缺点：修改基表Student的结构后，的结构后，Stud

123、ent表表与与F_Student1视图的映象关系被破坏，视图的映象关系被破坏，导致该视图不能正确工作。导致该视图不能正确工作。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL2.删除视图删除视图DROPVIEW；该语句从数据字典中删除指定的视图定义该语句从数据字典中删除指定的视图定义由该视图导出的其他视图定义仍在数据字典中，但由该视图导出的其他视图定义仍在数据字典中，但已不能使用，必须显式删除已不能使用，必须显式删除删除基表时，由该基表导出的所有视图定义都必须删除基表时，由该基表导出的所有视图定义都必须显式删除

124、显式删除例例9删除视图删除视图IS_S1DROPVIEWIS_S1；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.5.2查询视图查询视图从用户角度：查询视图与查询基本表相同从用户角度：查询视图与查询基本表相同DBMS实现实现视图查询的方法视图查询的方法实体化视图（实体化视图（ViewMaterialization）有效性检查：检查所查询的视图是否存在有效性检查：检查所查询的视图是否存在执行视图定义，将视图临时实体化，生成临时表执行视图定义，将视图临时实体化，生成临时表查询视图转换为查询临时表查询视图转换为

125、查询临时表查询完毕删除被实体化的视图查询完毕删除被实体化的视图(临时表临时表)上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL查询视图（续查询视图（续）视图消解法（视图消解法（ViewResolution）进行有效性检查，检查查询的表、视图等是否存在。如果进行有效性检查，检查查询的表、视图等是否存在。如果存在，则从数据字典中取出视图的定义存在，则从数据字典中取出视图的定义把视图定义中的子查询与用户的查询结合起来，转换成等把视图定义中的子查询与用户的查询结合起来，转换成等价的对基本表的查询价的对基本表的查询执行执

126、行修正修正后的查询后的查询上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL查询视图（续）查询视图（续）例例1在信息系学生的视图中找出年龄小于在信息系学生的视图中找出年龄小于20岁的学生。岁的学生。SELECTSno，SageFROMIS_StudentWHERESage20；IS_Student视图的定义视图的定义(视图定义例视图定义例1)：CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept=IS；上一页上一页下一下一页页返回本章首页返

127、回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL查询视图（续查询视图（续）视图实体化法视图实体化法视图消解法视图消解法转换后的查询语句为：转换后的查询语句为：SELECTSno，SageFROMStudentWHERESdept=ISANDSage20；视图消解法的局限视图消解法的局限有些情况下，视图消解法不能生成正确查询。采用视图有些情况下，视图消解法不能生成正确查询。采用视图消解法的消解法的DBMSDBMS会限制这类查询。会限制这类查询。上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关

128、系数据库的标准语言SQL3.5.3更新视图更新视图用户角度：更新视图与更新基本表相同用户角度：更新视图与更新基本表相同DBMS实现视图更新的方法实现视图更新的方法视图实体化法（视图实体化法（ViewMaterialization）视图消解法（视图消解法（ViewResolution）指定指定WITHCHECKOPTION子句后子句后DBMS在更新视图时会进行检查，防止用户通过视图对在更新视图时会进行检查，防止用户通过视图对不属于视不属于视图范围内图范围内的基本表数据进行更新的基本表数据进行更新上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准

129、语言关系数据库的标准语言SQL更新视图（续）更新视图（续）例例1将信息系学生视图将信息系学生视图IS_Student中学号中学号95002的学生姓名改为的学生姓名改为“刘辰刘辰”。UPDATEIS_StudentSETSname=刘辰刘辰WHERESno=95002；转换后的语句：转换后的语句：UPDATEStudentSETSname=刘辰刘辰WHERESno=95002ANDSdept=IS；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL更新视图（续）更新视图（续）例学生记录：例学生记录：95029，赵新

130、，赵新，20岁岁INSERTINTOIS_StudentVALUES(95029，赵新赵新，20)；转换为对基本表的更新：转换为对基本表的更新：INSERTINTOStudent(Sno，Sname，Sage，Sdept)VALUES(95029，赵新赵新，20，IS)；2向信息向信息系学生视图系学生视图IS_S中插入一个新的中插入一个新的上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL更新视图（续更新视图（续）例例3删除视图删除视图CS_S中学号为中学号为95029的记录的记录DELETEFROMIS_Stu

131、dentWHERESno=95029；转换为对基本表的更新：转换为对基本表的更新：DELETEFROMStudentWHERESno=95029ANDSdept=IS；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL更新视图的限制更新视图的限制一些视图是不可更新的，因为对这些视图的更一些视图是不可更新的，因为对这些视图的更新不能唯一地有意义地转换成对相应基本表的新不能唯一地有意义地转换成对相应基本表的更新更新(对两类方法均如此对两类方法均如此)例：视图例：视图S_G为不可更新视图。为不可更新视图。CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Score)FROMSCGROUPBYSno；上一页上一页下一下一页页返回本章首页返回本章首页数据库系统原理数据库系统原理第第3章章关系数据库的标准语言关系数据库的标准语言SQL3.5.4视图的作用视图的作用1.能够简化用户的操作2.使用户能够以多种角度看待同一数据3.对重构数据库提供了一定程度的逻辑独立性4.能够对机密数据提供安全保护