数据库原理-嵌套查询

数据库原理及应用第三章关系数据库标准语言SQL3.1SQL概述3.2数据定义3.3查询3.4数据更新3.5视图3.3查询3.3.1概述3.3.2单表查询3.3.3连接查询3.3.4嵌套查询3.3.5集合查询3.3.6小结3.3.4嵌套查询3.3.4嵌套查询嵌套查询概述嵌套查询分类嵌套查询求解方法引出子查询的谓词3.3.4嵌套查询【例1】查询选修了2号课程的学生的姓名SELECTSnameFROMStudent,SCWHERE方法1：复合条件查询Student.Sno=SC.SnoandCno='2'3.3.4嵌套查询【例1】查询选修了2号课程的学生的姓名方法2：换个角度想问题思路：从Student表中查询学生的姓名，这个学生应该是选修了2号课程的学生。思路：从Student表中查询学生的姓名，这个学生应该在选修了2号课程的学生集合里。3.3.4嵌套查询【例1】查询选修了2号课程的学生的姓名方法2：换个角度想问题（SELECTSnoFROMSCWHERECno='2'）；外层查询/父查询内层查询/子查询SELECTSnameFROMStudentWHERESnoIN3.3.4嵌套查询一、嵌套查询概述–一个SELECT-FROM-WHERE语句称为一个查询块–将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询3.3.4嵌套查询–子查询的限制•不能使用ORDERBY子句–层层嵌套方式反映了SQL语言的结构化–有些嵌套查询可以用连接运算替代不相关子查询子查询的查询条件不依赖于父查询相关子查询子查询的查询条件依赖于父查询二、嵌套查询分类3.3.4嵌套查询三、嵌套查询求解方法不相关子查询是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。3.3.4嵌套查询相关子查询–首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；–然后再取外层表的下一个元组；–重复这一过程，直至外层表全部检查完为止。3.3.4嵌套查询三、嵌套查询求解方法（续）四、引出子查询的谓词带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询3.3.4嵌套查询（1）带有IN谓词的子查询（续）【例2】查询选修了课程名为“信息系统”的学生学号和姓名①首先在Course关系中找出“信息系统”的课程号，结果为3号②然后在SC关系中找出选修了3号课程的学生学号③最后在Student关系中取出Sno和Sname(SELECTCnoFROMCourseWHERECname=‘信息系统’));(SELECTSnoFROMSCWHERECnoINSELECTSno，SnameFROMStudentWHERESnoININ可由‘=’代替（2）带有比较运算符的子查询●当能确切知道内层查询返回单值时，可用比较运算符（，，=，=，=，!=或）。与ANY或ALL谓词配合使用某一个全部（3）ANY或ALL谓词的子查询【例3】查询其他系中比信息系任意一个(某一个)学生年龄小的学生姓名和年龄SELECTSname，SageFROMStudentWHERESageANY(SELECTSageFROMStudentWHERESdept='IS')ANDSdept'IS';/*注意这是父查询块中的条件*/（3）ANY或ALL谓词的子查询●ANY和ALL谓词有时可以用集函数实现–ANY与ALL与集函数的对应关系=或!===ANYIN--MAX=MAXMIN=MINALL--NOTINMIN=MINMAX=MAX–用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数IN（SELECTSnoFROMSCWHERECno='2'）；①SELECTSnameFROMStudentWHERESno②SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSC/*相关子查询*/WHERESno=Student.SnoANDCno='2')；1、什么是嵌套查询2、嵌套查询分类不相关子查询相关子查询3、嵌套查询执行方法3.4.3嵌套查询①②4、引出子查询的谓词（4）带有EXISTS谓词的子查询1.EXISTS谓词2.NOTEXISTS谓词3.不同形式的查询间的替换4.☆用EXISTS/NOTEXISTS实现全称量词5.☆用EXISTS/NOTEXISTS实现逻辑蕴函（4）带有EXISTS谓词的子查询●1.EXISTS谓词–存在量词–带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。●若内层查询结果非空，则返回真值●若内层查询结果为空，则返回假值–由EXISTS引出的子查询，其目标列表达式通常都用*，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义●2.NOTEXISTS谓词（4）带有EXISTS谓词的子查询【例1】查询所有选修了2号课程的学生姓名SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERE思路分析：查询Student中这样的学生姓名，只要在SC表中存在该学生选修1号课程的记录。Sno=Student.SnoANDCno='1')；（4）带有EXISTS谓词的子查询【例1】查询所有选修了2号课程的学生姓名SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno='1';等值连接实现：（4）带有EXISTS谓词的子查询SELECTSnameFROMStudentWHERE(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')；连接运算的实现?NOTEXISTS【例4】查询没有选修2号课程的学生姓名思路分析：查询Student中这样的学生姓名，只要在SC表中不存在该学生选修1号课程的记录。（4）带有EXISTS谓词的子查询SELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno'1';【例4】查询没有选修2号课程的学生姓名Student表CSCSMAIS20191819男女女男李勇刘晨王敏张立200215121200215122200215123200515125SdeptSageSsexSnameSnoSC表928588908012323200215121200215121200215121200215122200215122GradeCnoSnoSELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')；√结果表Sname刘晨王敏张立Student表SC表928588908012323200215121200215121200215121200215122200215122GradeCnoSnoSELECTSnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno'1';CSCSMAIS20191819男女女男李勇刘晨王敏张立200215121200215122200215123200515125SdeptSageSsexSnameSno结果表Sname李勇×√×注意：约束的是单个元组（4）带有EXISTS谓词的子查询3.不同形式的查询间的替换所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。一些带EXISTS或NOTEXISTS谓词的子查询不能被其他形式的子查询等价替换（4）带有EXISTS谓词的子查询4.用EXISTS（）/NOTEXISTS实现全称量词（）谓词：–P(x1,x2,…,xn)被称为n元谓词。其中P是谓词，代表一个确定的特征或关系(名)。x1,x2,…,xn称为谓词的参量，一般表示个体。Like（I，Music）例如：“我喜欢音乐”（4）带有EXISTS谓词的子查询4.用EXISTS（）/NOTEXISTS实现全称量词（）–如果谓词公式P(x)对于所有可能变量x都具有Ｔｒｕｅ值，则用(∀x)P(x)表示。–对于谓词公式P(x)，至少有一个变元X可使P(x)为Tｒｕｅ值，则用(∃x)P(x)表示。（4）带有EXISTS谓词的子查询(x)P≡(x(P))(x)P：例如：Ｐ表示谓词“学生ｘ选修了课程A”不存在这样的学生ｘ，ｘ没有选修课程A(x(P))全部学生都选修了课程A（4）带有EXISTS谓词的子查询【例5】检索全部学生都选修的课程号与课程名SELECTCno,CnameFROMCourseWHERENOTEXISTS（SELECT*FROMStudentWHERENOTEXISTS(SELECT*FROMSC查询这样的课程，没有一个学生是不选修的。(x)P≡(x(P))Sno=Student.SnoANDCno=Course.Cno）；WHEREStudent表SC表928588908012323200215121200215121200215121200215122200215122GradeCnoSnoCSCSMAIS20191819男女女男李勇刘晨王敏张立200215121200215122200215123200515125SdeptSageSsexSnameSno42421数据库数学信息系统123CcreditCpnoCnameCnoCourse表课堂练习：【例5】的执行过程？？课后练习P127345