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

1、第3章关系数据库标准语言SQL,3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7嵌入式SQL3.8小结,关系数据库标准语言SQL(续),SQL语言(StructuredQueryLanguage)1974年由Boyce和Chamberlin提出1975年1979年IBM公司在SystemR原型系统上实现是关系数据库的标准语言，是数据库领域中一个主流语言,关系数据库标准语言SQL(续),SQL标准SQL-86第一个SQL标准由美国国家标准局（AmericanNationalStandardInstitute，简称ANSI）公布1987年国际标准化组织（Inte

2、rnationalOrganizationforStandardization，简称ISO）通过SQL-89SQL-92SQL3,第3章关系数据库标准语言SQL,3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7嵌入式SQL3.8小结,3.1SQL概述,3.1.1SQL的特点3.1.2SQL语言的基本概念,3.1SQL概述,3.1.1SQL的特点3.1.2SQL语言的基本概念,3.1.1SQL的特点,综合统一2.高度非过程化3.面向集合的操作方式4.同一种语法结构提供两种使用方式5.语言简捷，易学易用,综合统一,SQL语言集数据定义语言DDL、数据操纵语言DM

3、L、数据控制语言DCL的功能于一体非关系模型的数据语言模式数据定义语言（模式DDL）外模式数据定义语言（外模式DDL或子模式DDL）与数据存储有关的描述语言（DSDL）数据操纵语言（DML）,2.高度非过程化,用户只需提出“做什么”，而不必指明“怎么做”存取路径的选择以及SQL语句的操作过程由系统自动完成。大大减轻了用户负担，而且有利于提高数据独立性。,3.面向集合的操作方式,SQL语言采用集合操作方式操作对象、查找结果可以是元组的集合一次插入、删除、更新操作的对象可以是元组的集合非关系数据模型采用的是面向记录的操作方式，操作对象是一条记录。,4.同一种语法结构提供两种使用方式,自含式语言能够

4、独立地用于联机交互的使用方式嵌入式语言能够嵌入到高级语言（例如C，COBOL，FORTRAN，PL/1）程序中，供程序员设计程序时使用。两种不同使用方式下，SQL语言的语法结构基本一致,5.语言简捷，易学易用,3.1SQL概述,3.1.1SQL的特点3.1.2SQL语言的基本概念,3.1.2SQL语言的基本概念,SQL语言的基本概念（续）,用户用SQL语言对基本表和视图进行操作基本表本身独立存在的表，一个关系对应一个表一个（或多个）基本表对应一个存储文件一个表可以带若干索引，索引也存放在存储文件中存储文件存储文件的逻辑结构组成了关系数据库的内模式存储文件的物理结构是任意的，对用户是透明的视图从

5、一个或几个基本表或视图导出的表是虚表，只存放视图的定义而不存放对应数据,第3章关系数据库标准语言SQL,3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.6数据控制3.7嵌入式SQL3.8小结,数据定义概述,SQL的数据定义功能定义表(模式)创建表删除表修改表定义定义视图(外模式)创建视图删除视图间接修改视图定义：删除+创建,数据定义概述(续),SQL的数据定义功能（续）定义索引(内模式)创建索引删除索引间接修改索引定义：删除+创建,数据定义概述(续),3.2数据定义,3.2.1定义、删除与修改基本表3.2.2建立与删除索引,3.2数据定义,3.2.1定义、删除与修改基本表3

6、.2.2建立与删除索引,3.2.1定义、删除与修改基本表,一、定义基本表二、修改基本表三、删除基本表,一、定义基本表,关系名（表名）属性名（列名）完整性约束,定义基本表（续）,语句格式CREATETABLE（，）；：所要定义的基本表的名字：组成该表的各个属性（列）：涉及相应属性列的完整性约束条件：涉及一个或多个属性列的完整性约束条件,定义基本表（续）,表级完整性约束与列级完整性约束常用完整性约束主码约束：PRIMARYKEY参照完整性约束唯一性约束：UNIQUE非空值约束：NOTNULL取值约束：CHECK,定义基本表（续）,数据类型不同的数据库系统支持的数据类型不完全相同IBMDB2SQL支

7、持的数据类型SMALLINT半字长二进制整数。INTEGER或INT全字长二进制整数。DECIMAL(p,q)压缩十进制数，共p位，其中小数或DEC(p,q)点后有q位。0qp15，q=0时可以省略不写。FLOAT双字长浮点数。,定义基本表（续）,数据类型（续）CHARTER(n)长度为n的定长字符串。或CHAR(n)VARCHAR(n)最大长度为n的变长字符串。GRAPHIC(n)长度为n的定长图形字符串。VARGRAPHIC(n)最大长度为n的变长图形字符串。DATE日期型，格式为YYYY-MM-DD。TIME时间型，格式为HH.MM.SS。TIMESTAMP日期加时间。,定义基本表（续）

8、,数据类型（续）ORACLESQL支持的数据类型VARCHAR2(n)CHAR(n)NUMBER(p,q)DATELONGRAW或LONGRAW,定义基本表（续）,数据类型（续）KingBaseISQL支持的数据类型字符串类型CHARACTER(长度)最大长度为256个字符CHAR(长度)CHARACTIC的同义词CHARACTERVARYING(n)VARCHAR(n)TEXT大文本,定义基本表（续）,数据类型（续）KingBaseISQL支持的数据类型精确数值类型NUMERIC(精度,标度)标度值应小于精度值标度缺省为0,精度缺省为10DECIMAL(精度,标度)DEC(精度,标度)DEC

9、IMAL的同义词INTEGERINTINTEGER的同义词SMALLINT,定义基本表（续）,数据类型（续）KingBaseISQL支持的数据类型近似数值类型FLOAT(精度)可选精度浮点数REAL单精度浮点数DOUBLEPRECISION双精度浮点数DOUBLE同义词,定义基本表（续）,数据类型（续）KingBaseISQL支持的数据类型日期时间类型DATE日期TIMEWITHTIMEZONE时间TIMETZTIMEWITHOUTTIMEZONETIMETIMESTAMPWITHTIMEZONE日期和时间TIMESTAMPTZTIMESTAMPWITHOUTTIMEZONETIMESTAMP

10、INTERVAL通用的时间间隔,定义基本表（续）,数据类型（续）KingBaseISQL支持的数据类型位串BIT(n)定长位串BITVARYING(n)变长位串BINARYLARGEOBJECT二进制大对象BLOBBINARYLARGEOBJECT的同义词BYTEA二进制位串布尔型BOOLEAN逻辑布尔量BOOLBOOLEAN的同义词,例题,例1建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。,例题（续）,CREATETABLEStudent(SnoCHAR(5)NOT

11、NULLUNIQUE，SnameCHAR(20)UNIQUE，SsexCHAR(1)，SageINT，SdeptCHAR(15)；,例题（续）,例2建立一个“学生选课”表SC，它由学号Sno、课程号Cno，修课成绩Grade组成，其中(Sno,Cno)为主码。CREATETABLESC(SnoCHAR(5),CnoCHAR(3),Gradeint,Primarykey(Sno,Cno);,二、修改基本表,语句格式ALTERTABLEADD完整性约束DROPMODIFY；：要修改的基本表ADD子句：增加新列和新的完整性约束条件DROP子句：删除指定的完整性约束条件MODIFY子句：用于修改列名和

12、数据类型,二、修改基本表,语句格式（续）只能间接删除属性列把表中要保留的列及其内容复制到一个新表中删除原表再将新表重命名为原表名不能修改完整性约束不能为已有列增加完整性约束,例题,例2向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDScomeDATE；不论基本表中原来是否已有数据，新增加的列一律为空值。如果基本表中原来已有数据，新增列不可有NOTNULL约束,例题,例3将年龄的数据类型改为半字长整数。ALTERTABLEStudentMODIFYSageSMALLINT；注：修改原有的列定义有可能会破坏已有数据。,例题,例4删除学生姓名必须取唯

13、一值的约束。ALTERTABLEStudentDROPUNIQUE(Sname)；,三、删除基本表,语句格式DROPTABLE;系统从数据字典中删去:该基本表的描述该基本表上的所有索引的描述系统从文件中删去表中的数据表上的视图往往仍然保留，但无法引用,例题,例5删除Student表。DROPTABLEStudent;,3.2数据定义,3.2.1定义、删除与修改基本表3.2.2建立与删除索引,3.2.2建立与删除索引,建立索引是加快查询速度的有效手段建立索引DBMS自动建立PRIMARYKEYUNIQUEDBA或表的属主（即建立表的人）根据需要建立维护索引DBMS自动完成使用索引DBMS自动选择

14、是否使用索引以及使用哪些索引,一、建立索引,语句格式CREATEUNIQUECLUSTERINDEXON(,)；用指定要建索引的基本表名字索引可以建立在该表的一列或多列上，各列名之间用逗号分隔用指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASCUNIQUE表明此索引的每一个索引值只对应唯一的数据记录CLUSTER表示要建立的索引是聚簇索引,建立索引（续）,唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束。,建立索引（续）,聚簇索引建立聚簇索引后，基

15、表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致。例：CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一个聚簇索引，而且Student表中的记录将按照Sname值的升序存放,建立索引（续）,在一个基本表上最多只能建立一个聚簇索引聚簇索引的用途：对于某些类型的查询，可以提高查询效率聚簇索引的适用范围很少对基表进行增删操作很少对其中的变长列进行修改操作,例题,例6为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建

16、唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；,二、删除索引,语句格式DROPINDEX；删除索引时，系统会从数据字典中删去有关该索引的描述。,例题,例7删除Student表的Stusname索引。DROPINDEXStusname；,第3章关系数据库标准语言SQL,3.1SQL概述3.2数据定义3.3查询3.4数据更新3

17、.5视图3.6数据控制3.7嵌入式SQL3.8小结,3.3查询,3.3.1单表查询3.3.2连接查询3.3.3嵌套查询3.3.4集合查询3.3.5小结,查询（续）,语句格式SELECTALL|DISTINCT，FROM，WHEREGROUPBY,HAVINGORDERBY,ASC|DESC；,语句格式,SELECT子句：指定要显示的属性列FROM子句：指定查询对象(基本表或视图)WHERE子句：指定查询条件GROUPBY子句：对查询结果按指定列的值分组，该属性列值相等的元组为一个组。通常会在每组中作用集函数。HAVING短语：筛选出满足指定条件的组ORDERBY子句：对查询结果表按指定列值的升

18、序或降序排序,示例数据库,学生-课程数据库学生表：Student(Sno，Sname，Ssex，Sage，Sdept)课程表：Course(Cno，Cname，Cpno，Ccredit)学生选课表：SC(Sno，Cno，Grade),3.3查询,3.3.1单表查询3.3.2连接查询3.3.3嵌套查询3.3.4集合查询3.3.5小结,3.3.1单表查询,单表查询查询仅涉及一个表，是一种最简单的查询操作选择表中的若干列选择表中的若干元组对查询结果排序使用集函数对查询结果分组,一、选择表中的若干列,属投影运算不消除重复行变化方式主要表现在SELECT子句的上查询指定列查询全部列查询经过计算的值,1.

19、查询指定列,方法在SELECT子句的中指定要查询的属性中各个列的先后顺序可以与表中的逻辑顺序不一致。即用户可以根据应用的需要改变列的显示顺序,例题,例1查询全体学生的学号与姓名。SELECTSno，SnameFROMStudent；例2查询全体学生的姓名、学号、所在系。SELECTSname，Sno，SdeptFROMStudent；,2.查询全部列,方法在SELECT关键字后面列出所有列名当列的显示顺序与其在基表中的顺序相同时，也可以简单地将指定为*,例题,例3查询全体学生的详细记录。SELECTSno，Sname，Ssex，Sage，SdeptFROMStudent；或SELECT*FRO

20、MStudent；,3.查询经过计算的值,方法SELECT子句的为表达式算术表达式字符串常量函数列别名等,例题,例4查全体学生的姓名及其出生年份。SELECTSname，2003-SageFROMStudent；输出结果：Sname2003-Sage-李勇1976刘晨1977王名1978张立1978,例题（续）,例5查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname，YearofBirth:，2002-Sage，ISLOWER(Sdept)FROMStudent；,例题（续）,输出结果：SnameYearofBirth:2002-SageISLOWER(S

21、dept)-李勇YearofBirth:1976cs刘晨YearofBirth:1977if王名YearofBirth:1978ma张立YearofBirth:1978if,例题（续）,例5.1使用列别名改变查询结果的列标题SELECTSnameNAME，YearofBirth:BIRTH，2002-SageBIRTHDAY，ISLOWER(Sdept)DEPARTMENTFROMStudent；输出结果：NAMEBIRTHBIRTHDAYDEPARTMENT-李勇YearofBirth:1976cs刘晨YearofBirth:1977if王名YearofBirth:1978ma张立Yearo

22、fBirth:1978if,二、选择表中的若干元组,消除取值重复的行查询满足条件的元组,1.消除取值重复的行,方法在SELECT子句中使用DISTINCT短语,例题,假设SC表中有下列数据SnoCnoGrade-9500119295001285950013889500229095002380,例题（续）,例6查询选修了课程的学生学号。(1)SELECTSnoFROMSC;或SELECTALLSnoFROMSC;结果：Sno-9500195001950019500295002,例题（续）,(2)SELECTDISTINCTSnoFROMSC;结果：Sno-9500195002,例题（续）,注意D

23、ISTINCT短语的作用范围是所有目标列例：查询选修课程的各种成绩错误的写法SELECTDISTINCTCno，DISTINCTGradeFROMSC;正确的写法SELECTDISTINCTCno，GradeFROMSC;,2.查询满足条件的元组,属选择运算通过WHERE子句实现比较大小确定范围确定集合字符串匹配涉及空值的查询多重条件查询,查询满足条件的元组（续）,WHERE子句常用的查询条件,(1)比较大小,方法在WHERE子句的中使用比较运算符=，=，!，!，逻辑运算符NOT+含上述比较运算符的表达式,例题,例7查询计算机系全体学生的名单。SELECTSnameFROMStudentWHE

24、RESdept=CS；,例题,例8查询所有年龄在20岁以下的学生姓名及其年龄。SELECTSname，SageFROMStudentWHERESage=20；,例题（续）,例9查询考试成绩有不及格的学生的学号。SELECTDISTINCTSnoFROMSCWHEREGrade60；,(2)确定范围,方法使用谓词BETWEENANDNOTBETWEENANDBETWEEN后：范围的下限（即低值）AND后：范围的上限（即高值）用多重条件查询实现,例题,例10查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWH

25、ERESageBETWEEN20AND23；,例题（续）,例11查询年龄不在2023岁之间的学生姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageNOTBETWEEN20AND23；,(3)确定集合,方法使用谓词INNOTIN：用逗号分隔的一组取值用多重条件查询实现,例题,例12查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN(IS，MA，CS);,例题,例13查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。SELECTSn

26、ame，SsexFROMStudentWHERESdeptNOTIN(IS，MA，CS);,(4)字符串匹配,方法使用谓词LIKE或NOTLIKENOTLIKEESCAPE：指定匹配模板匹配模板：固定字符串或含通配符的字符串当匹配模板为固定字符串时，可以用=运算符取代LIKE谓词，用!=或运算符取代NOTLIKE谓词,字符串匹配(续),例题：匹配模板为固定字符串例14查询学号为95001的学生的详细情况。SELECT*FROMStudentWHERESnoLIKE95001；等价于：SELECT*FROMStudentWHERESno=95001；,字符串匹配(续),通配符%(百分号)代表任意

27、长度（长度可以为0）的字符串。例：a%b表示以a开头，以b结尾的任意长度的字符串。如acb，addgb，ab等都满足该匹配串。_(下横线)代表任意单个字符。例：a_b表示以a开头，以b结尾的长度为3的任意字符串。如acb，afb等都满足该匹配串。,字符串匹配(续),例题：匹配模板为含通配符的字符串例15查询所有姓刘学生的姓名、学号和性别。SELECTSname，Sno，SsexFROMStudentWHERESnameLIKE刘%；,字符串匹配(续),匹配模板为含通配符的字符串（续）例16查询姓欧阳且全名为三个汉字的学生的姓名。SELECTSnameFROMStudentWHERESnameL

28、IKE欧阳_；,字符串匹配(续),匹配模板为含通配符的字符串（续）例17查询名字中第2个字为阳字的学生的姓名和学号。SELECTSname，SnoFROMStudentWHERESnameLIKE_阳%；,字符串匹配(续),匹配模板为含通配符的字符串（续）例18查询所有不姓刘的学生姓名。SELECTSname，Sno，SsexFROMStudentWHERESnameNOTLIKE刘%；,字符串匹配(续),ESCAPE短语：当用户要查询的字符串本身就含有%或_时，要使用ESCAPE短语对通配符进行转义。,字符串匹配(续),例题：使用换码字符将通配符转义为普通字符例19查询DB_Design课程

29、的课程号和学分。SELECTCno，CcreditFROMCourseWHERECnameLIKEDB_Design,字符串匹配(续),使用换码字符将通配符转义为普通字符(续)例19(续)SELECTCno，CcreditFROMCourseWHERECnameLIKEDB_DesignESCAPE,字符串匹配(续),使用换码字符将通配符转义为普通字符(续)例20查询以DB_开头，且倒数第3个字符为i的课程的详细情况。SELECT*FROMCourseWHERECnameLIKEDB_%i_ESCAPE；,(5)涉及空值的查询,方法使用谓词ISNULL或ISNOTNULL“ISNULL”不能用

30、“=NULL”代替,例题,例21某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。SELECTSno，CnoFROMSCWHEREGradeISNULL；,例题(续),例22查所有有成绩的学生学号和课程号。SELECTSno，CnoFROMSCWHEREGradeISNOTNULL；,(6)多重条件查询,方法用逻辑运算符AND和OR来联结多个查询条件AND的优先级高于OR可以用括号改变优先级可用来实现多种其他谓词NOTINNOTBETWEENAND,例题,例23查询计算机系年龄在20岁以下的学生姓名。SELECTSnameFROMStude

31、ntWHERESdept=CSANDSage20；,例题（续）,改写例12例12查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。SELECTSname，SsexFROMStudentWHERESdeptIN(IS，MA，CS)可改写为：SELECTSname，SsexFROMStudentWHERESdept=ISORSdept=MAORSdept=CS；,例题（续）,改写例10例10查询年龄在2023岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。SELECTSname，Sdept，SageFROMStudentWHERESageBETWEEN20AND23；

32、可改写为：SELECTSname，Sdept，SageFROMStudentWHERESage=20ANDSage=23；,三、对查询结果排序,方法使用ORDERBY子句可以按一个或多个属性列排序升序：ASC；降序：DESC；缺省值为升序当排序列含空值时ASC：排序列为空值的元组最后显示DESC：排序列为空值的元组最先显示,对查询结果排序（续）,例题例24查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。SELECTSno，GradeFROMSCWHERECno=3ORDERBYGradeDESC；,对查询结果排序（续）,结果SnoGrade-950109502495007929

33、5003829501082950097595014619500255,对查询结果排序（续）,例25查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。SELECT*FROMStudentORDERBYSdept，SageDESC；,四、使用集函数,方法5类主要集函数计数COUNT（DISTINCT|ALL*）COUNT（DISTINCT|ALL）计算总和SUM（DISTINCT|ALL）计算平均值AVG（DISTINCT|ALL）,使用集函数（续）,5类主要集函数(续)求最大值MAX（DISTINCT|ALL）求最小值MIN（DISTINCT|ALL）DISTINC

34、T短语：在计算时要取消指定列中的重复值ALL短语：不取消重复值ALL为缺省值,使用集函数（续）,例题例26查询学生总人数。SELECTCOUNT(*)FROMStudent；例27查询选修了课程的学生人数。SELECTCOUNT(DISTINCTSno)FROMSC；注：用DISTINCT以避免重复计算学生人数,使用集函数（续）,例28计算1号课程的学生平均成绩。SELECTAVG(Grade)FROMSCWHERECno=1；例29查询选修1号课程的学生最高分数。SELECTMAX(Grade)FROMSCWHERCno=1；,五、对查询结果分组,用途细化集函数的作用对象未对查询结果分组，集

35、函数将作用于整个查询结果对查询结果分组后，集函数将分别作用于每个组,对查询结果分组（续）,方法（参照后面例题）使用GROUPBY子句分组分组方法：按指定的一列或多列值分组，值相等的为一组使用GROUPBY子句后，SELECT子句的列名列表中只能出现分组属性和集函数GROUPBY子句的作用对象是查询的中间结果表,例题,例30求各个课程号及相应的选课人数。SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；结果CnoCOUNT(Sno)122234344433548,例题,例31求各个课程号及相应的课程成绩在90分以上的学生人数。SELECTCno，COUNT(Sno)FRO

36、MSCWHEREGrade=90GROUPBYCno；结果CnoCOUNT(Sno)113274358,对查询结果分组（续）,方法(续)使用HAVING短语筛选最终输出结果只有满足HAVING短语指定条件的组才输出HAVING短语与WHERE子句的区别：作用对象不同WHERE子句作用于基表或视图，从中选择满足条件的元组。HAVING短语作用于组，从中选择满足条件的组。,例题,例32查询选修了3门以上课程的学生学号。SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)3；,例题,例33查询有3门以上课程在90分以上的学生的学号及90分以上的课程数。SELECTSno,C

37、OUNT(*)FROMSCWHEREGrade=90GROUPBYSnoHAVINGCOUNT(*)=3;,3.3查询,3.3.1单表查询3.3.2连接查询3.3.3嵌套查询3.3.4集合查询3.3.5小结,3.3.2连接查询,概述同时涉及多个表的查询称为连接查询连接条件用来连接两个表的条件称为连接条件或连接谓词常用格式.比较运算符：=、=、.BETWEEN.AND.,连接查询（续）,连接字段连接谓词中的列名称为连接字段连接条件中的各连接字段类型必须是可比的，但不必是相同的,连接查询（续）,连接操作的执行过程嵌套循环法(NESTED-LOOP)首先在表1中找到第一个元组，然后从头开始扫描表2，

38、逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。重复上述操作，直到表1中的全部元组都处理完毕为止。,连接查询（续）,排序合并法(SORT-MERGE)：常用于=连接首先按连接属性对表1和表2排序对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续

39、,连接查询（续）,排序合并法(续)找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续重复上述操作，直到表1或表2中的全部元组都处理完毕为止,连接查询（续）,索引连接(INDEX-JOIN)对表2按连接字段建立索引对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组,连接查询（续）,SQL中连接查询的主要类型广义笛卡尔积等值连接(含自然连接)

40、非等值连接查询自身连接查询外连接查询复合条件连接查询,连接查询（续）,一、广义笛卡尔积二、等值与非等值连接查询三、自身连接查询四、外连接查询五、复合条件连接查询,一、广义笛卡尔积,不带连接谓词的连接很少使用例：SELECTStudent.*,SC.*FROMStudent,SC,二、等值与非等值连接查询,等值连接自然连接非等值连接,二、等值与非等值连接查询,等值连接连接运算符为=的连接操作.=.任何子句中引用表1和表2中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。,等值与非等值连接查询（续）,例32查询每个学生及其选修课程的情况。SELECTStudent.*，SC

41、.*FROMStudent，SCWHEREStudent.Sno=SC.Sno；,等值与非等值连接查询（续）,结果假设Student表、SC表分别有下列数据：Student表,等值与非等值连接查询（续）,SC表,等值与非等值连接查询（续）,结果表Student.SnoSnameSsexSageSdeptSC.SnoCnoGrade95001李勇男20CS9500119295001李勇男20CS9500128595001李勇男20CS9500138895002刘晨女19IS9500229095002刘晨女19IS95002380,等值与非等值连接查询（续）,自然连接等值连接的一种特殊情况，把目标

42、列中重复的属性列去掉。.=.SELECT语句不能直接实现自然连接例33对例32用自然连接完成。SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno；,等值与非等值连接查询（续）,非等值连接连接运算符不为=的连接操作.比较运算符：、=、.BETWEEN.AND.,三、自身连接,一个表与其自己进行连接，称为表的自身连接表示方法需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀,自身连接（续）,例34查询每一门课的间接先修课（即先修课的先修课）。SELECTFI

43、RST.Cno，SECOND.CpnoFROMCourseFIRST，CourseSECONDWHEREFIRST.Cpno=SECOND.Cno；,自身连接（续）,结果FIRST表（Course表）,自身连接（续）,SECOND表（Course表）,自身连接（续）,查询结果173556,四、外连接（OuterJoin）,外连接与普通连接的区别普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出,外连接（续）,外连接操作在表名后面加外连接操作符(*)或(+)指定非主体表非主体表有一“万能”的虚行，该行全部由空值组成虚行可以和主体表中所有不满足

44、连接条件的元组进行连接由于虚行各列全部是空值，因此与虚行连接的结果中，来自非主体表的属性值全部是空值,外连接（续）,外连接操作的种类左外连接外连接符出现在连接条件的左边右外连接外连接符出现在连接条件的右边,外连接（续）,例：用外连接操作改写例33SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno(*)；,外连接（续）,结果：Student.SnoSnameSsexSageSdeptCnoGrade95001李勇男20CS19295001李勇男20CS28595001李勇男2

45、0CS38895002刘晨女19IS29095002刘晨女19IS38095003王敏女18MA95004张立男19IS,五、复合条件连接,WHERE子句中含多个连接条件时，称为复合条件连接复合条件连接的类型两表按多个属性连接自身按多个属性连接多表连接,复合条件连接（续）,例35假设学校中性别相同的学生不会重名。现如下设计学生表和选修表：Std(Sname,Ssex,Sage,Sdept)StdC(Sname,Ssex,Cno,Grade)查询选修2号课程且成绩在90分以上的所有学生的姓名，性别及所在系。SELECTStd.Sname,Std.Ssex,SdeptFROMStd，StdCWHE

46、REStd.Sname=StdC.Sname/*连接谓词*/ANDStd.Ssex=StdC.Ssex/*连接谓词*/ANDStdC.Cno=2/*其他限定条件*/ANDStdC.Grade90；/*其他限定条件*/,复合条件连接（续）,例36查询每个学生的学号、姓名、选修的课程名及成绩。SELECTStudent.Sno，Sname，Cname，GradeFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoandSC.Cno=Course.Cno；结果：Student.SnoSnameCnameGrade95001李勇数据库9295001李勇数学85950

47、01李勇信息系统8895002刘晨数学9095002刘晨信息系统80,3.3查询,3.3.1单表查询3.3.2连接查询3.3.3嵌套查询3.3.4集合查询3.3.5小结,3.3.3嵌套查询,嵌套查询概述嵌套查询分类嵌套查询求解方法引出子查询的谓词,嵌套查询(续),嵌套查询概述一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询,嵌套查询(续),例SELECTSname外层查询/父查询FROMStudentWHERESnoIN（SELECTSno子内层查询/子查询FROMSCWHERECno=2）；,嵌

48、套查询(续),子查询的限制不能使用ORDERBY子句层层嵌套方式反映了SQL语言的结构化有些嵌套查询可以用连接运算替代,嵌套查询(续),嵌套查询分类不相关子查询子查询的查询条件不依赖于父查询相关子查询子查询的查询条件依赖于父查询,嵌套查询(续),嵌套查询求解方法不相关子查询是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。,嵌套查询(续),嵌套查询求解方法（续）相关子查询首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全

49、部检查完为止。,嵌套查询(续),引出子查询的谓词带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询,一、带有IN谓词的子查询,例37查询与“刘晨”在同一个系学习的学生。此查询要求可以分步来完成确定“刘晨”所在系名SELECTSdeptFROMStudentWHERESname=刘晨；结果为：SdeptIS,带有IN谓词的子查询（续）,查找所有在IS系学习的学生。SELECTSno，Sname，SdeptFROMStudentWHERESdept=IS；结果为：SnoSnameSdept95001刘晨IS95004张立IS,带有IN谓词的子查询（

50、续）,构造嵌套查询将第一步查询嵌入到第二步查询的条件中SELECTSno，Sname，SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname=刘晨)；此查询为不相关子查询。DBMS求解该查询时也是分步去做的。,带有IN谓词的子查询（续）,用自身连接完成本查询要求SELECTSno，Sname，SdeptFROMStudentS1，StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname=刘晨；,带有IN谓词的子查询（续）,父查询和子查询中的表均可以定义别名SELECTSno，Sname，SdeptF

51、ROMStudentS1WHERES1.SdeptIN(SELECTSdeptFROMStudentS2WHERES2.Sname=刘晨)；,带有IN谓词的子查询（续）,例38查询选修了课程名为“信息系统”的学生学号和姓名嵌套查询SELECTSno，Sname最后在Student关系中FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno然后在SC关系中找出选FROMSC修了3号课程的学生学号WHERECnoIN(SELECTCno首先在Course关系中找出“信FROMCourse息系统”的课程号，结果为3号WHERECname=信息系统);,带有IN谓词的子查

52、询（续）,结果：SnoSname-95001李勇95002刘晨,带有IN谓词的子查询（续）,连接查询SELECTSno，SnameFROMStudent，SC，CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname=信息系统；,二、带有比较运算符的子查询,使用范围当能确切知道内层查询返回单值时，可用比较运算符（，=，）。与ANY或ALL谓词配合使用,带有比较运算符的子查询（续）,例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在例37可以用=代替IN：SELECTSno，Sname，SdeptFROMStude

53、ntWHERESdept=SELECTSdeptFROMStudentWHERESname=刘晨；,带有比较运算符的子查询（续）,子查询一定要跟在比较符之后错误的例子：SELECTSno，Sname，SdeptFROMStudentWHERE(SELECTSdeptFROMStudentWHERESname=刘晨)=Sdept；,三、带有ANY或ALL谓词的子查询,谓词语义ANY：任意一个值ALL：所有值,带有ANY或ALL谓词的子查询（续）,需要配合使用比较运算符ANY大于子查询结果中的某个值ALL大于子查询结果中的所有值=ANY大于等于子查询结果中的某个值=ALL大于等于子查询结果中的所有

54、值）ANY不等于子查询结果中的某个值!=（或）ALL不等于子查询结果中的任何一个值,带有ANY或ALL谓词的子查询（续）,例39查询其他系中比信息系某一学生年龄小的学生姓名和年龄SELECTSname，SageFROMStudentWHERESageIS;/*注意这是父查询块中的条件*/,带有ANY或ALL谓词的子查询（续）,结果SnameSage王敏18执行过程DBMS执行此查询时，首先处理子查询，找出IS系中所有学生的年龄，构成一个集合(19，18)。然后处理父查询，找所有不是IS系且年龄小于19或18的学生。,带有ANY或ALL谓词的子查询（续）,ANY和ALL谓词有时可以用集函数实现A

55、NY与ALL与集函数的对应关系,带有ANY或ALL谓词的子查询（续）,用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数,带有ANY或ALL谓词的子查询（续）,例39：用集函数实现例39SELECTSname，SageFROMStudentWHERESageIS;,带有ANY或ALL谓词的子查询（续）,例40查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词SELECTSname，SageFROMStudentWHERESageIS;查询结果为空表。,带有ANY或ALL谓词的子查询（续）,方法二：用集函数SELECTSname，Sag

56、eFROMStudentWHERESageIS;,四、带有EXISTS谓词的子查询,1.EXISTS谓词2.NOTEXISTS谓词3.不同形式的查询间的替换4.相关子查询的效率5.用EXISTS/NOTEXISTS实现全称量词6.用EXISTS/NOTEXISTS实现逻辑蕴函,带有EXISTS谓词的子查询(续),1.EXISTS谓词存在量词带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则返回真值若内层查询结果为空，则返回假值由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出

57、列名无实际意义2.NOTEXISTS谓词,带有EXISTS谓词的子查询(续),思路分析：本查询涉及Student和SC关系。在Student中依次取每个元组的Sno值，用此值去检查SC关系。若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno=1，则取此Student.Sname送入结果关系。,带有EXISTS谓词的子查询(续),例41查询所有选修了1号课程的学生姓名。用嵌套查询SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=1)；求解过程,带有EXISTS谓词的子查询

58、(续),用连接运算SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno=1;,带有EXISTS谓词的子查询(续),例42查询没有选修1号课程的学生姓名。SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno=1)；此例用连接运算难于实现,带有EXISTS谓词的子查询(续),3.不同形式的查询间的替换一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用

59、带EXISTS谓词的子查询等价替换。,带有EXISTS谓词的子查询(续),例：例37可以用带EXISTS谓词的子查询替换：SELECTSno，Sname，SdeptFROMStudentS1WHEREEXISTS(SELECT*FROMStudentS2WHERES2.Sdept=S1.SdeptANDS2.Sname=刘晨)；,带有EXISTS谓词的子查询(续),4.相关子查询的效率由于带EXISTS量词的相关子查询只关心内层查询是否有返回值，并不需要查具体值，因此其效率并不一定低于其他形式的查询。例37：不相关子查询的效率高于相关子查询的效率,带有EXISTS谓词的子查询(续),相关子查询的效率可能高于连接查询例：查询选修了课程的学生姓名法一：SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.Sno)；,带有EXISTS谓词的子查询(续),