数据库系统概论数据库系统概论An Introduction to Database System第三章第三章 关系数据库标准语言关系数据库标准语言SQL SQL (续续1）An Introduction to Database An Introduction to Database SystemSystem3.3查询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结An Introduction to Database An Introduction to Database SystemSystem3.3.3连接查询同时涉及多个表的查询称为连接查询用来用来连连接两个表的条件称接两个表的条件称为连为连接条件或接条件或连连接接谓词谓词 一般格式：一般格式： .1 .2比比较较运算符：运算符：= =、 、 =、=、!=!=.1BETWEENBETWEEN.2ANDAND.3An Introduction to Database An Introduction to Database SystemSystem连接查询（续）连连接字段接字段连连接接谓词谓词中的列名称中的列名称为连为连接字段接字段连连接条件中的各接条件中的各连连接字段接字段类类型必型必须须是可比的，是可比的，但不必是相同的但不必是相同的An Introduction to Database An Introduction to Database SystemSystem连接操作的执行过程嵌套循环法(NESTED-LOOP) 首首先先在在表表1 1中中找找到到第第一一个个元元组组，然然后后从从头头开开始始扫扫描描表表2 2，逐逐一一查查找找满满足足连连接接件件的的元元组组，找找到到后后就就将将表表1 1中中的的第第一个元一个元组组与与该该元元组组拼接起来，形成拼接起来，形成结结果表中一个元果表中一个元组组。 表表2 2全全部部查查找找完完后后，再再找找表表1 1中中第第二二个个元元组组，然然后后再再从从头头开开始始扫扫描描表表2 2，逐逐一一查查找找满满足足连连接接条条件件的的元元组组，找找到到后后就就将将表表1 1中中的的第第二二个个元元组组与与该该元元组组拼拼接接起起来来，形形成成结结果表中一个元果表中一个元组组。 重复上述操作，直到表重复上述操作，直到表1 1中的全部元中的全部元组组都都处处理完理完毕毕 An Introduction to Database An Introduction to Database SystemSystem排序合并法(SORT-MERGE)常用于常用于= =连连接接 首先按首先按连连接属性接属性对对表表1 1和表和表2 2排序排序 对对表表1 1的的第第一一个个元元组组，从从头头开开始始扫扫描描表表2 2，顺顺序序查查找找满满足足连连接接条条件件的的元元组组，找找到到后后就就将将表表1 1中中的的第第一一个个元元组组与与该该元元组组拼拼接接起起来来，形形成成结结果果表表中中一一个个元元组组。当当遇遇到到表表2 2中中第第一一条条大大于于表表1 1连连接接字字段段值值的的元元组组时时，对对表表2 2的的查询查询不再不再继续继续An Introduction to Database An Introduction to Database SystemSystem排序合并法 找找到到表表1 1的的第第二二条条元元组组，然然后后从从刚刚才才的的中中断断点点处处继继续续顺顺序序扫扫描描表表2 2，查查找找满满足足连连接接条条件件的的元元组组，找找到到后后就就将将表表1 1中中的的第第一一个个元元组组与与该该元元组组拼拼接接起起来来，形形成成结结果果表表中中一一个个元元组组。直直接接遇遇到到表表2 2中中大大于于表表1 1连连接接字段字段值值的元的元组时组时，对对表表2 2的的查询查询不再不再继续继续 重重复复上上述述操操作作，直直到到表表1 1或或表表2 2中中的的全全部部元元组组都都处处理理完完毕为毕为止止 An Introduction to Database An Introduction to Database SystemSystem索引连接(INDEX-JOIN)对对表表2 2按按连连接字段建立索引接字段建立索引对对表表1 1中中的的每每个个元元组组，依依次次根根据据其其连连接接字字段段值值查查询询表表2 2的的索索引引，从从中中找找到到满满足足条条件件的的元元组组，找找到到后后就就将将表表1 1中中的的第第一一个个元元组组与与该该元元组组拼接起来，形成拼接起来，形成结结果表果表中一个元中一个元组组 An Introduction to Database An Introduction to Database SystemSystem连接查询（续）SQLSQL中中连连接接查询查询的主要的主要类类型型 广广义义笛卡笛卡尔积尔积 等等值连值连接接( (含自然含自然连连接接) ) 非等非等值连值连接接查询查询 自身自身连连接接查询查询 外外连连接接查询查询 复合条件复合条件连连接接查询查询An Introduction to Database An Introduction to Database SystemSystem一、广义笛卡尔积不带连接谓词的连接很少使用例：SELECTStudent.*,SC.*SELECTStudent.*,SC.*FROMStudent,SCFROMStudent,SCAn Introduction to Database An Introduction to Database SystemSystem二、等值与非等值连接查询等值连接、自然连接、非等值连接例32查询每个学生及其选修课程的情况。SELECTStudent.*SELECTStudent.*，SC.*SC.*FROMStudentFROMStudent，SCSCWHEREWHEREStudent.SnoStudent.Sno=SC.SnoSC.Sno；An Introduction to Database An Introduction to Database SystemSystem等值连接连连接运算符接运算符为为 =的的连连接操作接操作 .=.2 任任何何子子句句中中引引用用表表1 1和和表表2 2中中同同名名属属性性时时，都都必必须须加加表表名名前前缀缀。引引用用唯唯一一属属性性名名时时可可以以加加也也可可以以省省略略表表名前名前缀缀。 An Introduction to Database An Introduction to Database SystemSystem等值连接假设Student表、SC表分别有下列数据：Student表Sno SnameSsexSageSdept95001 李勇李勇 男男 20 CS95002 刘晨刘晨 女女 19 IS95003 王敏王敏 女女 18 MA95004 张立张立 男男 19 ISAn Introduction to Database An Introduction to Database SystemSystem等值连接SC表Sno CnoGrade95001 1 9295001 2 85950019500295002 3 2 3 88 90 80An Introduction to Database An Introduction to Database SystemSystem等值连接结结果表果表 Student.SnoStudent.Sno SnameSnameSsexSsexSageSageSdeptSdeptSC.SnoSC.SnoCnoCnoGradeGrade9500195001李勇李勇男男2020CSCS95001192950011929500195001李勇李勇男男2020CSCS95001285950012859500195001李勇李勇男男2020CSCS95001388950013889500295002刘晨刘晨女女1919ISIS95002290950022909500295002刘晨刘晨女女1919ISIS9500238095002380 An Introduction to Database An Introduction to Database SystemSystem自然连接等值连接的一种特殊情况，把目标列中重复的属性列去掉。例33对例32用自然连接完成。SELECTSELECTStudent.SnoStudent.Sno，SnameSname，SsexSsex，SageSage， SdeptSdept，CnoCno，GradeGradeFROMStudentFROMStudent，SCSCWHEREWHEREStudent.SnoStudent.Sno=SC.SnoSC.Sno；An Introduction to Database An Introduction to Database SystemSystem非等值连接查询连接运算符不是=的连接操作.2比比较较运算符：运算符： 、 =、=、!=!= .1BETWEENBETWEEN.2ANDAND.3An Introduction to Database An Introduction to Database SystemSystem三、自身连接一个表与其自己一个表与其自己进进行行连连接，称接，称为为表的表的自身连接自身连接需要需要给给表起表起别别名以示区名以示区别别由于所有属性名都是同名属性，因此必由于所有属性名都是同名属性，因此必须须使用使用别别名前名前缀缀An Introduction to Database An Introduction to Database SystemSystem自身连接（续）例34查询每一门课的间接先修课（即先修课的先修课）SELECTSELECTFIRST.CnoFIRST.Cno，SECOND.CpnoSECOND.CpnoFROMCourseFROMCourseFIRSTFIRST，CourseCourseSECONDSECONDWHEREWHEREFIRST.CpnoFIRST.Cpno=SECOND.CnoSECOND.Cno； An Introduction to Database An Introduction to Database SystemSystem自身连接（续）FIRSTFIRST表（表（CourseCourse表）表）Cno CnameCpnoCcredit 1数据库数据库 5 4 2数学数学 2 3信息系统信息系统 1 4 4操作系统操作系统 6 3 5数据结构数据结构 7 4 6数据处理数据处理 2 7PASCAL语言语言 6 4An Introduction to Database An Introduction to Database SystemSystem自身连接（续）SECONDSECOND表（表（CourseCourse表）表）Cno CnameCpnoCcredit 1数据库数据库 5 4 2数学数学 2 3信息系统信息系统 1 4 4操作系统操作系统 6 3 5数据结构数据结构 7 4 6数据处理数据处理 2 7PASCAL语言语言 6 4An Introduction to Database An Introduction to Database SystemSystem自身连接（续）查询结果 173556cnocnocpnocpnoAn Introduction to Database An Introduction to Database SystemSystem四、外连接（OuterJoin）外连接与普通连接的区别普通普通连连接操作只接操作只输输出出满满足足连连接条件的元接条件的元组组外外连连接操作以指定表接操作以指定表为连为连接主体，将主体表中接主体，将主体表中不不满满足足连连接条件的元接条件的元组组一并一并输输出出An Introduction to Database An Introduction to Database SystemSystem外连接（续） 例例 3333查查询询每每个个学学生生及及其其选选修修课课程程的的情情况况包包括括没没有有选选修修课课程的学生程的学生-用外用外连连接操作接操作SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，GradeFROMStudent，SCWHEREStudent.Sno=SC.Sno(*)；An Introduction to Database An Introduction to Database SystemSystem外连接（续）结结果：果： Student.SnoStudent.Sno SnameSnameSsexSsexSageSageSdeptSdeptCnoCnoGradeGrade 9500195001李勇李勇男男20CS19220CS1929500195001李勇李勇男男20CS28520CS2859500195001李勇李勇男男20CS38820CS3889500295002刘晨刘晨女女19IS29019IS2909500295002刘晨刘晨女女19IS38019IS3809500395003王敏王敏女女18MA18MA9500495004张张立立男男19IS19ISAn Introduction to Database An Introduction to Database SystemSystem外连接（续）在在表表名名后后面面加加外外连连接接操操作作符符(*)(*)或或(+)(+)指指定定非非主主体表体表非非主主体体表表有有一一“ “万万能能” ”的的虚虚行行，该该行行全全部部由由空空值值组组成成虚虚行行可可以以和和主主体体表表中中所所有有不不满满足足连连接接条条件件的的元元组进组进行行连连接接由由于于虚虚行行各各列列全全部部是是空空值值，因因此此与与虚虚行行连连接接的的结结果果中中，来来自自非非主主体体表表的的属属性性值值全全部部是是空空值值 An Introduction to Database An Introduction to Database SystemSystem外连接（续）左外连接 外连接符出现在连接条件的左边右外连接 外连接符出现在连接条件的右边 An Introduction to Database An Introduction to Database SystemSystem五、复合条件连接WHEREWHERE子句中含多个子句中含多个连连接条件接条件时时，称，称为为复合条件复合条件连连接接 例例3535查询选查询选修修2 2号号课课程且成程且成绩绩在在9090分以上的所有学生的分以上的所有学生的学号、姓名学号、姓名SELECTSELECTStudent.SnoStudent.Sno, ,student.Snamestudent.SnameFROMStudent,SCFROMStudent,SCWHEREWHEREStudent.SnoStudent.Sno=SC.SnoSC.SnoANDAND/*/*连连接接谓词谓词* */ /SC.CnoSC.Cno=2AND/*=2AND/*其他限定条件其他限定条件*/ /SC.Grade90SC.Grade90；/*/*其他限定条件其他限定条件*/ /An Introduction to Database An Introduction to Database SystemSystem多表连接 例例3636 查查询询每每个个学学生生的的学学号号、姓姓名名、选选修修的的课课程程名名及及成成绩绩。SELECTSELECTStudent.SnoStudent.Sno，SnameSname，CnameCname，GradeGradeFROMStudentFROMStudent，SCSC，CourseCourseWHEREWHEREStudent.SnoStudent.Sno=SC.SnoSC.Sno andandSC.CnoSC.Cno=Course.CnoCourse.Cno； 结结果：果：Student.SnoStudent.SnoSnameSnameCnameCnameGradeGrade9500195001李勇李勇数据数据库库92929500195001李勇李勇数学数学85859500195001李勇李勇信息系信息系统统88889500295002刘晨刘晨数学数学90909500295002刘晨刘晨信息系信息系统统8080An Introduction to Database An Introduction to Database SystemSystem3.3查询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结An Introduction to Database An Introduction to Database SystemSystem3.3.4嵌套查询嵌套查询概述嵌套查询分类嵌套查询求解方法引出子查询的谓词An Introduction to Database An Introduction to Database SystemSystem嵌套查询(续)嵌套查询概述一个一个SELECT-FROM-WHERESELECT-FROM-WHERE语语句称句称为为一个一个查查询块询块将一个将一个查询块查询块嵌套在另一个嵌套在另一个查询块查询块的的WHEREWHERE子句或子句或HAVINGHAVING短短语语的条件中的的条件中的查询查询称称为为嵌套嵌套查询查询An Introduction to Database An Introduction to Database SystemSystem嵌套查询(续)SELECTSELECTSnameSname外外层查询层查询/ /父父查询查询FROMStudentFROMStudentWHEREWHERESnoSnoININ（SELECTSELECTSnoSno内内层查询层查询/ /子子查询查询FROMSCFROMSCWHEREWHERECnoCno=2=2）；）；An Introduction to Database An Introduction to Database SystemSystem嵌套查询(续)子子查询查询的限制的限制不能使用不能使用ORDERBYORDERBY子句子句层层层层嵌套方式反映了嵌套方式反映了 SQLSQL语语言的言的结结构化构化有些嵌套有些嵌套查询查询可以用可以用连连接运算替代接运算替代An Introduction to Database An Introduction to Database SystemSystem嵌套查询分类不相关子查询子子查询查询的的查询查询条件不依条件不依赖赖于父于父查询查询相关子查询子子查询查询的的查询查询条件依条件依赖赖于父于父查询查询An Introduction to Database An Introduction to Database SystemSystem嵌套查询求解方法不相关子查询是由里向外逐是由里向外逐层处层处理。即每个子理。即每个子查询查询在在上一上一级查询处级查询处理之前求解，子理之前求解，子查询查询的的结结果果用于建立其父用于建立其父查询查询的的查查找条件。找条件。An Introduction to Database An Introduction to Database SystemSystem嵌套查询求解方法（续）相关子查询首先取外首先取外层查询层查询中表的第一个元中表的第一个元组组，根据它与，根据它与内内层查询层查询相关的属性相关的属性值处值处理内理内层查询层查询，若，若WHEREWHERE子句返回子句返回值为值为真，真，则则取此元取此元组组放入放入结结果果表；表；然后再取外然后再取外层层表的下一个元表的下一个元组组；重复重复这这一一过过程，直至外程，直至外层层表全部表全部检查检查完完为为止。止。An Introduction to Database An Introduction to Database SystemSystem引出子查询的谓词带带有有ININ谓词谓词的子的子查询查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询An Introduction to Database An Introduction to Database SystemSystem一、带有IN谓词的子查询 例例3737查询查询与与“ “刘晨刘晨” ”在同一个系学在同一个系学习习的学生。的学生。此此查询查询要求可以分步来完成要求可以分步来完成 确定确定“ “刘晨刘晨” ”所在系名所在系名SELECTSELECTSdeptSdept FROMStudentFROMStudentWHEREWHERESnameSname=刘晨刘晨 ；结结果果为为： SdeptSdeptISISAn Introduction to Database An Introduction to Database SystemSystem带有IN谓词的子查询（续） 查查找所有在找所有在ISIS系学系学习习的学生。的学生。SELECTSELECTSnoSno，SnameSname，SdeptSdeptFROMStudentFROMStudentWHEREWHERESdeptSdept=IS=IS； 结结果果为为：SnoSnoSnameSnameSdeptSdept9500195001刘晨刘晨ISIS9500495004张张立立ISISAn Introduction to Database An Introduction to Database SystemSystem构造嵌套查询将第一步将第一步查询查询嵌入到第二步嵌入到第二步查询查询的条件中的条件中SELECTSELECTSnoSno，SnameSname，SdeptSdeptFROMStudentFROMStudentWHEREWHERESdeptSdeptININ(SELECT(SELECTSdeptSdeptFROMStudentFROMStudentWHEREWHERESnameSname=刘晨刘晨 ) )；此此查询为查询为不相关子不相关子查询查询。DBMSDBMS求解求解该查询时该查询时也是分步也是分步去做的。去做的。An Introduction to Database An Introduction to Database SystemSystem带有IN谓词的子查询（续）用自身连接完成本查询要求SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1，StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname=刘晨；An Introduction to Database An Introduction to Database SystemSystem带有IN谓词的子查询（续）父查询和子查询中的表均可以定义别名SELECTSELECTSnoSno，SnameSname，SdeptSdeptFROMStudentS1FROMStudentS1WHERES1.SdeptINWHERES1.SdeptIN(SELECT(SELECTSdeptSdeptFROMStudentS2FROMStudentS2WHERES2.Sname=WHERES2.Sname=刘晨刘晨 ) )；An Introduction to Database An Introduction to Database SystemSystem带有IN谓词的子查询（续） 例例3838查询选查询选修了修了课课程名程名为为“ “信息系信息系统统” ”的学生学号和姓名的学生学号和姓名 SELECTSELECTSnoSno，SnameSname 最后在最后在StudentStudent关系中关系中 FROMStudentFROMStudent取出取出SnoSno和和SnameSnameWHEREWHERESnoSnoININ(SELECT(SELECTSnoSno 然后在然后在SCSC关系中找出关系中找出选选FROMSCFROMSC修了修了3 3号号课课程的学生学号程的学生学号WHEREWHERECnoCnoININ(SELECT(SELECTCnoCno 首先在首先在CourseCourse关系中找出关系中找出“ “信信FROMCourseFROMCourse息系息系统统” ”的的课课程号，程号，结结果果为为3 3号号WHEREWHERECnameCname=信息系信息系统统 ););An Introduction to Database An Introduction to Database SystemSystem带有IN谓词的子查询（续）结果：Sno Sname - - 95001 李勇 95002 刘晨An Introduction to Database An Introduction to Database SystemSystem带有IN谓词的子查询（续）用连接查询SELECTSELECTSnoSno，SnameSnameFROMStudentFROMStudent，SCSC，CourseCourseWHEREWHEREStudent.SnoStudent.Sno=SC.SnoSC.SnoANDANDSC.CnoSC.Cno=Course.CnoCourse.CnoANDANDCourse.CnameCourse.Cname=信息系信息系统统 ；An Introduction to Database An Introduction to Database SystemSystem二、带有比较运算符的子查询当能确切知道内层查询返回单值时，可用比较运算符（，=，=，!=或）。与ANY或ALL谓词配合使用An Introduction to Database An Introduction to Database SystemSystem带有比较运算符的子查询（续）例：假例：假设设一个学生只可能在一个系学一个学生只可能在一个系学习习，并且必，并且必须须属于一个系，属于一个系，则则在在 例例3737可以可以用用 =代替代替ININ ：SELECTSELECTSnoSno，SnameSname，SdeptSdeptFROMStudentFROMStudentWHEREWHERESdeptSdept =SELECTSELECTSdeptSdeptFROMStudentFROMStudentWHEREWHERESnameSname=刘晨刘晨 ；An Introduction to Database An Introduction to Database SystemSystem带有比较运算符的子查询（续） 子子查询查询一定要跟在比一定要跟在比较较符之后符之后错误错误的例子：的例子：SELECTSELECTSnoSno，SnameSname，SdeptSdeptFROMStudentFROMStudentWHERE(SELECTWHERE(SELECTSdeptSdeptFROMStudentFROMStudentWHEREWHERESnameSname=刘晨刘晨) )=SdeptSdept；An Introduction to Database An Introduction to Database SystemSystem三、带有ANY或ALL谓词的子查询谓词语义ANYANY：任意一个任意一个值值ALLALL：所有所有值值An Introduction to Database An Introduction to Database SystemSystem带有ANY或ALL谓词的子查询（续）需要配合使用比需要配合使用比需要配合使用比需要配合使用比较较运算符运算符运算符运算符 ANY ANY大于子大于子大于子大于子查询结查询结果中的某个果中的某个果中的某个果中的某个值值 ALL ALL大于子大于子大于子大于子查询结查询结果中的所有果中的所有果中的所有果中的所有值值 ANY ANY小于子小于子小于子小于子查询结查询结果中的某个果中的某个果中的某个果中的某个值值 ALL= ANY= ANY大于等于子大于等于子大于等于子大于等于子查询结查询结果中的某个果中的某个果中的某个果中的某个值值 = ALL= ALL大于等于子大于等于子大于等于子大于等于子查询结查询结果中的所有果中的所有果中的所有果中的所有值值= ANY= ANY小于等于子小于等于子小于等于子小于等于子查询结查询结果中的某个果中的某个果中的某个果中的某个值值 = ALL= ALL小于等于子小于等于子小于等于子小于等于子查询结查询结果中的所有果中的所有果中的所有果中的所有值值= ANY= ANY等于子等于子等于子等于子查询结查询结果中的某个果中的某个果中的某个果中的某个值值 =ALL=ALL等于子等于子等于子等于子查询结查询结果中的所有果中的所有果中的所有果中的所有值值（通常没有（通常没有（通常没有（通常没有实际实际意意意意义义）!=!=（或（或（或（或）ANYANY不等于子不等于子不等于子不等于子查询结查询结果中的某个果中的某个果中的某个果中的某个值值!=!=（或（或（或（或）ALLALL不等于子不等于子不等于子不等于子查询结查询结果中的任何一个果中的任何一个果中的任何一个果中的任何一个值值An Introduction to Database An Introduction to Database SystemSystem带有ANY或ALL谓词的子查询（续） 例例3939查询查询其他系中比信息系任意其他系中比信息系任意一一个个( (其中某其中某一一个个) )学生年学生年龄龄小的学生姓名和年小的学生姓名和年龄龄SELECTSELECTSnameSname，SageSageFROMStudentFROMStudentWHERESageWHERESageANYANY(SELECTSage(SELECTSageFROMStudentFROMStudentWHEREWHERESdeptSdept=IS)=IS)ANDANDSdeptSdeptISIS;/*/*注意注意这这是父是父查询块查询块中的条件中的条件*/ /An Introduction to Database An Introduction to Database SystemSystem带有ANY或ALL谓词的子查询（续）结结果果SnameSnameSageSage王敏王敏1818执执行行过过程程1.DBMS1.DBMS执执行此行此查询时查询时，首先，首先处处理子理子查询查询，找出，找出 ISIS系中所有学生的年系中所有学生的年龄龄，构成一个集合，构成一个集合(19(19，18)18)2.2.处处理父理父查询查询，找所有不是，找所有不是ISIS系且年系且年龄龄小于小于 1919或或 1818的学生的学生An Introduction to Database An Introduction to Database SystemSystem带有ANY或ALL谓词的子查询（续）ANYANY和和ALLALL谓词谓词有有时时可以用集函数可以用集函数实现实现 ANYANY与与ALLALL与集函数的与集函数的对应对应关系关系 = 或或!= =ANY IN - MAXMIN= MINALL - NOT IN MINMAX= MAXAn Introduction to Database An Introduction to Database SystemSystem带有ANY或ALL谓词的子查询（续）用集函数用集函数实现实现子子查询查询通常比直接用通常比直接用ANYANY或或ALLALL查询查询效率要高，因效率要高，因为为前者通常能前者通常能够够减减少比少比较较次数次数An Introduction to Database An Introduction to Database SystemSystem带有ANY或ALL谓词的子查询（续） 例例3939：用集函数：用集函数实现实现 例例3939SELECTSELECTSnameSname，SageSageFROMStudentFROMStudentWHERESageWHERESage(SELECT(SELECTMAX(Sage)MAX(Sage)FROMStudentFROMStudentWHEREWHERESdeptSdept=IS)=IS)ANDANDSdeptSdeptIS;IS;An Introduction to Database An Introduction to Database SystemSystem带有ANY或ALL谓词的子查询（续） 例例4040查询查询其他系中比信息系其他系中比信息系所有所有学生年学生年龄龄都都小的学生姓名及年小的学生姓名及年龄龄。方法一：用方法一：用ALLALL谓词谓词SELECTSELECTSnameSname，SageSageFROMStudentFROMStudentWHERESageALLWHERESageALL(SELECTSage(SELECTSageFROMStudentFROMStudentWHEREWHERESdeptSdept=IS)=IS)ANDANDSdeptSdeptIS;IS;查询结查询结果果为为空表。空表。An Introduction to Database An Introduction to Database SystemSystem带有ANY或ALL谓词的子查询（续）方法二：用集函数方法二：用集函数SELECTSELECTSnameSname，SageSageFROMStudentFROMStudentWHERESageWHERESage(SELECT(SELECTMIN(Sage)MIN(Sage)FROMStudentFROMStudentWHEREWHERESdeptSdept=IS)=IS)ANDANDSdeptSdeptIS;IS;An Introduction to Database An Introduction to Database SystemSystem四、带有EXISTS谓词的子查询1.EXISTS谓词2.NOTEXISTS谓词3.不同形式的查询间的替换4.相关子查询的效率5.用EXISTS/NOTEXISTS实现全称量词6.用EXISTS/NOTEXISTS实现逻辑蕴函An Introduction to Database An Introduction to Database SystemSystem带有EXISTS谓词的子查询(续）1. EXISTS1. EXISTS谓词谓词存在量存在量词词 带带有有EXISTSEXISTS谓词谓词的子的子查询查询不返回任何数据，只不返回任何数据，只产产生生逻辑逻辑真真值值“true”true”或或逻辑逻辑假假值值“false”false”。若内若内层查询结层查询结果非空，果非空，则则返回真返回真值值若内若内层查询结层查询结果果为为空，空，则则返回假返回假值值由由EXISTSEXISTS引出的子引出的子查询查询，其目，其目标标列表达式通常都用列表达式通常都用* * ，因，因为带为带EXISTSEXISTS的子的子查询查询只返回真只返回真值值或假或假值值，给给出列名无出列名无实际实际意意义义2. NOT EXISTS2. NOT EXISTS谓词谓词An Introduction to Database An Introduction to Database SystemSystem带有EXISTS谓词的子查询(续） 思路分析：思路分析： 本本查询查询涉及涉及StudentStudent和和SCSC关系。关系。 在在StudentStudent中依次取每个元中依次取每个元组组的的SnoSno值值，用此，用此值值去去检查检查SCSC关系。关系。 若若SCSC中存在中存在这样这样的元的元组组，其，其SnoSno值值等于此等于此Student.SnoStudent.Sno值值，并且其，并且其CnoCno= 1= 1，则则取此取此Student.SnameStudent.Sname送入送入结结果关系。果关系。An Introduction to Database An Introduction to Database SystemSystem带有EXISTS谓词的子查询(续） 例例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 )； 求解求解过过程程 An Introduction to Database An Introduction to Database SystemSystem带有EXISTS谓词的子查询(续）用用连连接运算接运算SELECT SELECT SnameSnameFROM Student, SCFROM Student, SCWHERE WHERE Student.SnoStudent.Sno= =SC.SnoSC.Sno AND AND SC.CnoSC.Cno= 1;= 1;An Introduction to Database An Introduction to Database SystemSystem带有EXISTS谓词的子查询(续） 例例42 42 查询查询没有没有选选修修1 1号号课课程的学生姓名。程的学生姓名。 SELECT SELECT SnameSname FROM FROM StudentStudent WHERE NOT EXISTS WHERE NOT EXISTS (SELECT * (SELECT * FROM SC FROM SC WHERE WHERE SnoSno = = Student.Student.SnoSno AND AND CnoCno=1)=1)；此例用此例用连连接运算接运算难难于于实现实现 An Introduction to Database An Introduction to Database SystemSystem带有EXISTS谓词的子查询(续）3. 3. 不同形式的不同形式的查询间查询间的替的替换换一些一些带带EXISTSEXISTS或或NOT EXISTSNOT EXISTS谓词谓词的子的子查询查询不能被其他形不能被其他形式的子式的子查询查询等价替等价替换换所有所有带带ININ谓词谓词、比、比较较运算符、运算符、ANYANY和和ALLALL谓词谓词的子的子查询查询都都能用能用带带EXISTSEXISTS谓词谓词的子的子查询查询等价替等价替换换。 An Introduction to Database An Introduction to Database SystemSystem带有EXISTS谓词的子查询(续）例：例： 例例3737查询查询与与“ “刘晨刘晨” ”在同一个系学在同一个系学习习的学生。的学生。可可以用以用带带EXISTSEXISTS谓词谓词的子的子查询查询替替换换： SELECT SELECT SnoSno，SnameSname，SdeptSdept FROM Student S1 FROM Student S1 WHERE EXISTS WHERE EXISTS SELECT *SELECT * FROM Student S2 FROM Student S2 WHERE S2.Sdept = S1.Sdept AND WHERE S2.Sdept = S1.Sdept AND S2.Sname = S2.Sname = 刘晨刘晨 ；An Introduction to Database An Introduction to Database SystemSystem带有EXISTS谓词的子查询(续）5.5.用用EXISTS/NOT EXISTSEXISTS/NOT EXISTS实现实现全称量全称量词词( (难难点点) )SQLSQL语语言中没有全称量言中没有全称量词词 （For allFor all）可以把可以把带带有全称量有全称量词词的的谓词转换为谓词转换为等价的等价的带带有有存在量存在量词词的的谓词谓词： (x)P ( x( P) An Introduction to Database An Introduction to Database SystemSystem带有EXISTS谓词的子查询(续） 例例43 43 查询选查询选修了全部修了全部课课程的学生姓名。程的学生姓名。 SELECT SELECT SnameSname FROM Student FROM Student WHERE NOT EXISTS WHERE NOT EXISTS （SELECT *SELECT * FROM Course FROM Course WHERE NOT EXISTS WHERE NOT EXISTS (SELECT * (SELECT * FROM SC FROM SC WHERE WHERE SnoSno= = Student.SnoStudent.Sno AND AND CnoCno= = Course.CnoCourse.Cno）；）；An Introduction to Database An Introduction to Database SystemSystem带有EXISTS谓词的子查询(续）6.6.用用EXISTS/NOTEXISTSEXISTS/NOTEXISTS实现逻辑蕴实现逻辑蕴函函( (难难点点) )SQLSQL语语言中没有言中没有蕴蕴函函(Implication)(Implication)逻辑逻辑运算运算可以利用可以利用谓词谓词演算将演算将逻辑蕴逻辑蕴函函谓词谓词等价等价转换为转换为：pqpqAn Introduction to Database An Introduction to Database SystemSystem带有EXISTS谓词的子查询(续） 例例44 44 查询查询至少至少选选修了学生修了学生9500295002选选修的全部修的全部课课程程的学生号的学生号码码。解解题题思路：思路：用用逻辑蕴逻辑蕴函表达：函表达：查询查询学号学号为为x x的学生，的学生，对对所有的所有的课课程程y y，只要只要9500295002学生学生选选修了修了课课程程y y，则则x x也也选选修了修了y y。形式化表示：形式化表示：用用P P表示表示谓词谓词 “ “学生学生9500295002选选修了修了课课程程y”y”用用q q表示表示谓词谓词 “ “学生学生x x选选修了修了课课程程y”y”则则上述上述查询为查询为: (: ( y) p y) p q q An Introduction to Database An Introduction to Database SystemSystem带有EXISTS谓词的子查询(续）等价等价变换变换：( ( y)y)ppqq ( ( y(y( ( (ppqq) ( ( y(y( ( ( ppq)q) y(py(p q)q)变换变换后后语义语义：不存在：不存在这样这样的的课课程程y y，学生学生9500295002选选修了修了y y，而学生而学生x x没有没有选选。An Introduction to Database An Introduction to Database SystemSystem带有EXISTS谓词的子查询(续）用用NOTEXISTSNOTEXISTS谓词谓词表示表示：SELECTDISTINCTSELECTDISTINCTSnoSnoFROMSCFROMSCSCXSCXWHERENOTEXISTSWHERENOTEXISTS(SELECT*(SELECT*FROMSCFROMSCSCYSCYWHEREWHERESCY.SnoSCY.Sno=95002AND=95002ANDNOTEXISTSNOTEXISTS(SELECT*(SELECT*FROMSCSCZFROMSCSCZWHEREWHERESCZ.SnoSCZ.Sno= =SCXSCX.Sno.SnoANDANDSCZ.CnoSCZ.Cno= =SCYSCY.Cno.Cno)；An Introduction to Database An Introduction to Database SystemSystem3.3查询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结An Introduction to Database An Introduction to Database SystemSystem3.3.5 集合查询标准SQL直接支持的集合操作种类并操作(UNION)一般商用数据库支持的集合操作种类并操作(UNION)交操作(INTERSECT)差操作(MINUS)An Introduction to Database An Introduction to Database SystemSystem1并操作形式 UNIONUNION 参加参加UNIONUNION操作的各操作的各结结果表的列数必果表的列数必须须相同；相同；对应项对应项的数据的数据类类型也必型也必须须相同相同An Introduction to Database An Introduction to Database SystemSystem并操作（续） 例例4545查询计查询计算机科学系的学生及年算机科学系的学生及年龄龄不大于不大于1919岁岁的学生。的学生。方法一：方法一：SELECT*SELECT*FROMStudentFROMStudentWHEREWHERESdeptSdept=CS=CSUNIONUNIONSELECT*SELECT*FROMStudentFROMStudentWHERESage=19WHERESage=19；An Introduction to Database An Introduction to Database SystemSystem并操作（续）方法二：SELECTDISTINCT*SELECTDISTINCT*FROMStudentFROMStudentWHEREWHERESdeptSdept=CSORSage=19=CSORSage=19；An Introduction to Database An Introduction to Database SystemSystem并操作（续） 例例4646查询选查询选修了修了课课程程1 1或者或者选选修了修了课课程程2 2的学生。的学生。方法一：方法一：SELECTSELECTSnoSnoFROMSCFROMSCWHEREWHERECnoCno=1=1UNIONUNIONSELECTSELECTSnoSnoFROMSCFROMSCWHEREWHERECnoCno=2=2；An Introduction to Database An Introduction to Database SystemSystem并操作（续）方法二：SELECTDISTINCTSELECTDISTINCTSnoSnoFROMSCFROMSCWHEREWHERECnoCno=1OR=1ORCnoCno=2=2；An Introduction to Database An Introduction to Database SystemSystem并操作（续） 例例4747设设数据数据库库中有一教中有一教师师表表Teacher(TnoTeacher(Tno, ,TnameTname,.),.)。查询查询学校中所有学校中所有师师生的姓名。生的姓名。SELECTSELECTSnameSnameFROMStudentFROMStudentUNIONUNIONSELECTSELECTTnameTnameFROMTeacher;FROMTeacher;newAn Introduction to Database An Introduction to Database SystemSystem2交操作标准SQL中没有提供集合交操作，但可用其他方法间接实现。An Introduction to Database An Introduction to Database SystemSystem2交操作 例例4848查询计查询计算机科学系的学生与年算机科学系的学生与年龄龄不大于不大于1919岁岁的学生的交集的学生的交集本例本例实际实际上就是上就是查询计查询计算机科学系中年算机科学系中年龄龄不大于不大于1919岁岁的学生的学生SELECT*SELECT*FROMStudentFROMStudentWHEREWHERESdeptSdept=CSAND=CSANDSage=19Sage19Sage19；An Introduction to Database An Introduction to Database SystemSystem差操作（续） 例例5252查询查询学生姓名与教学生姓名与教师师姓名的差集姓名的差集本例本例实际实际上是上是查询查询学校中未与教学校中未与教师师同名的学生姓同名的学生姓名名SELECTDISTINCTSELECTDISTINCTSnameSnameFROMStudentFROMStudentWHEREWHERESnameSnameNOTINNOTIN(SELECT(SELECTTnameTnameFROMTeacher);FROMTeacher);newAn Introduction to Database An Introduction to Database SystemSystem4.对集合操作结果的排序ORDERBY子句只能用于对最终查询结果排序，不能对中间结果排序任何情况下，ORDERBY子句只能出现在最后对集合操作结果排序时，ORDERBY子句中用数字指定排序属性newAn Introduction to Database An Introduction to Database SystemSystem对集合操作结果的排序（续） 例例5353错误错误写法写法SELECT*SELECT*FROMStudentFROMStudentWHEREWHERESdeptSdept=CS=CSORDERBYORDERBYSnoSnoUNIONUNIONSELECT*SELECT*FROMStudentFROMStudentWHERESage=19WHERESage=19ORDERBYORDERBYSnoSno；newAn Introduction to Database An Introduction to Database SystemSystem对集合操作结果的排序（续）正确写法正确写法SELECT*SELECT*FROMStudentFROMStudentWHEREWHERESdeptSdept=CS=CSUNIONUNIONSELECT*SELECT*FROMStudentFROMStudentWHERESage=19WHERESage=19ORDERBY1ORDERBY1；newAn Introduction to Database An Introduction to Database SystemSystem3.3.6SELECT语句的一般格式SELECTSELECTALL|DISTINCTALL|DISTINCT别别名名， 别别名名FROMFROM别别名名 ， 别别名名 WHEREWHERE GROUPBYGROUPBY1， .1. HAVINGHAVING ORDER BYORDER BYASC|DESC2ASC|DESC， ASC|DESC2ASC|DESC；An Introduction to Database An Introduction to Database SystemSystem目标列表达式目目标标列表达式格式列表达式格式(1)(1).*.*(2)(2).，. ：由：由属性列属性列、作用于属性列的、作用于属性列的集函数集函数和和常量常量的任意算的任意算术术运算（运算（+ +，- -，* *，/ /）组组成的运算公式。成的运算公式。An Introduction to Database An Introduction to Database SystemSystem集函数格式COUNTCOUNTSUMSUMAVG(DISTINCT|ALLAVG(DISTINCT|ALL)MAXMAXMINMINCOUNT(DISTINCT|ALL*)COUNT(DISTINCT|ALL*)An Introduction to Database An Introduction to Database SystemSystem条件表达式格式（1） ANY|ALL(SELECTANY|ALL(SELECT语语句句) )An Introduction to Database An Introduction to Database SystemSystem条件表达式格式（2） NOTBETWEENNOTBETWEEN ANDAND (SELECT(SELECT(SELECT(SELECT语语句句) )语语句句) )An Introduction to Database An Introduction to Database SystemSystem条件表达式格式（3） (1， )2) NOTINNOTIN(SELECT语句)An Introduction to Database An Introduction to Database SystemSystem条件表达式格式(4)NOTLIKE(5)ISNOTNULL(6)NOTEXISTS(SELECT语句)An Introduction to Database An Introduction to Database SystemSystem条件表达式格式（7）ANDANDANDAND ORORORORAn Introduction to Database An Introduction to Database SystemSystem 下课了。休息一会儿。休息一会儿。An Introduction to Database An Introduction to Database SystemSystem