《SQL--嵌套查询集合查询.ppt》由会员分享，可在线阅读，更多相关《SQL--嵌套查询集合查询.ppt（42页珍藏版）》请在三一文库上搜索。
1、3.3.4 嵌套查询,嵌套查询概述嵌套查询分类嵌套查询求解方法引出子查询的谓词,嵌套查询概述,嵌套查询一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询,SELECT Sname外层查询/父查询 FROM Student WHERE Sno IN （SELECT Sno 内层查询/子查询 FROM SC WHERE Cno= 2 ）；,子查询的限制:不能使用ORDER BY子句层层嵌套方式反映了 SQL语言的结构化有些嵌套查询可以用连接运算替代,嵌套查询分类及求解方法,不相关子查询: 子查询的查询条
2、件不依赖于父查询是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。,相关子查询:子查询的查询条件依赖于父查询首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表；然后再取外层表的下一个元组；重复这一过程，直至外层表全部检查完为止。,引出子查询的谓词,带有IN谓词的子查询带有比较运算符的子查询带有ANY或ALL谓词的子查询带有EXISTS谓词的子查询,一、带有IN谓词的子查询,例37 查询与“刘晨”在同一个系学习的学生。 此查询要求可以分步来完成 确定“刘晨”所在系名 SELEC
3、T Sdept FROM Student WHERE Sname= 刘晨 ；结果为： Sdept IS, 查找所有在IS系学习的学生。 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept= IS ； 结果为：Sno Sname Sdept 95001 刘晨 IS 95004 张立 IS,构造嵌套查询,将第一步查询嵌入到第二步查询的条件中 SELECT Sno，Sname，Sdept FROM Student WHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname= 刘晨 )；此查询为不相关子查询。D
4、BMS求解该查询时也是分步去做的。,用自身连接完成本查询要求 SELECT S1.Sno，S1.Sname，S1.Sdept FROM Student S1，Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = 刘晨；,带有IN谓词的子查询（续）,父查询和子查询中的表均可以定义别名 SELECT Sno，Sname，Sdept FROM Student S1 WHERE S1.Sdept IN (SELECT Sdept FROM Student S2 WHERE S2.Sname= 刘晨 )；,带有IN谓词的子查询（续）,例38查询选修了课程
5、名为“信息系统”的学生学号和姓名 SELECT Sno，Sname 最后在Student关系中 FROM Student 取出Sno和Sname WHERE Sno IN (SELECT Sno 然后在SC关系中找出选 FROM SC 修了3号课程的学生学号 WHERE Cno IN (SELECT Cno 首先在Course关系中找出“信 FROM Course 息系统”的课程号，结果为3号 WHERE Cname= 信息系统);,结果： Sno Sname - - 95001 李勇 95002 刘晨,带有IN谓词的子查询（续）,用连接查询 SELECT Sno，Sname FROM Stu
6、dent，SC，Course WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname=信息系统；,二、带有比较运算符的子查询,当能确切知道内层查询返回单值时，可用比较运算符（，=，）。与ANY或ALL谓词配合使用,例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在例37可以用 = 代替IN ： SELECT Sno，Sname，Sdept FROM Student WHERE Sdept = SELECT Sdept FROM Student WHERE Sname= 刘晨 ；,带有比较运算符的子查询（续
7、）,子查询一定要跟在比较符之后 错误的例子： SELECT Sno，Sname，Sdept FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= 刘晨 ) = Sdept；,三、带有ANY或ALL谓词的子查询,谓词语义ANY：任意一个值ALL：所有值,需要配合使用比较运算符 ANY大于子查询结果中的某个值 ALL大于子查询结果中的所有值= ANY大于等于子查询结果中的某个值 = ALL大于等于子查询结果中的所有值）ANY不等于子查询结果中的某个值!=（或）ALL不等于子查询结果中的任何一个值,带有ANY或ALL谓词的子查询（续）
8、,例39 查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄 SELECT Sname，Sage FROM Student WHERE Sage IS ; /* 注意这是父查询块中的条件 */,带有ANY或ALL谓词的子查询（续）,结果 Sname Sage 王敏 18执行过程1.DBMS执行此查询时，首先处理子查询，找出 IS系中所有学生的年龄，构成一个集合(19，18)2. 处理父查询，找所有不是IS系且年龄小于19 或 18的学生,带有ANY或ALL谓词的子查询（续）,ANY和ALL谓词有时可以用集函数实现ANY与ALL与集函数的对应关系,用集函数实现子查询通常比直接用
9、ANY或ALL查询效率要高，因为前者通常能够减少比较次数,带有ANY或ALL谓词的子查询（续）,例39：用集函数实现例39查询其他系中比信息系任意一个(其中某一个)学生年龄小的学生姓名和年龄 SELECT Sname，Sage FROM Student WHERE Sage IS ;,带有ANY或ALL谓词的子查询（续）,例40 查询其他系中比信息系所有学生年龄都小的学生姓名及年龄。方法一：用ALL谓词 SELECT Sname，Sage FROM Student WHERE Sage IS ;查询结果为空表。,方法二：用集函数 SELECT Sname，Sage FROM Student W
10、HERE Sage IS ;,四、带有EXISTS谓词的子查询,1. EXISTS谓词2. NOT EXISTS谓词3. 不同形式的查询间的替换4. 相关子查询的效率5. 用EXISTS/NOT EXISTS实现全称量词6. 用EXISTS/NOT EXISTS实现逻辑蕴函,带有EXISTS谓词的子查询(续）,1. EXISTS谓词存在量词 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。若内层查询结果非空，则返回真值若内层查询结果为空，则返回假值由EXISTS引出的子查询，其目标列表达式通常都用* ，因为带EXISTS的子查询只返回真值或假值，
11、给出列名无实际意义2. NOT EXISTS谓词,带有EXISTS谓词的子查询(续）,例41 查询所有选修了1号课程的学生姓名。用嵌套查询 SELECT Sname FROM Student WHERE EXISTS (SELECT * FROM SC /*相关子查询*/ WHERE Sno=Student.Sno AND Cno= 1 )；,思路分析： 本查询涉及Student和SC关系。 在Student中依次取每个元组的Sno值，用此值去检查SC关系。 若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= 1，则取此Student.Sname送入结果关系。,带
12、有EXISTS谓词的子查询(续）,用连接运算SELECT SnameFROM Student, SCWHERE Student.Sno=SC.Sno AND SC.Cno= 1;,例42 查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=1)；此例用连接运算难于实现,带有EXISTS谓词的子查询(续）,3. 不同形式的查询间的替换一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换所有带IN谓词、
13、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。,例：例37查询与“刘晨”在同一个系学习的学生。可以用带EXISTS谓词的子查询替换： SELECT Sno，Sname，Sdept FROM Student S1 WHERE EXISTS SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = 刘晨 ；,带有EXISTS谓词的子查询(续）,5.用EXISTS/NOT EXISTS实现全称量词(难点)SQL语言中没有全称量词 （For all）可以把带有全称量词的谓词转换为等价的带有存在量
14、词的谓词： (x)P ( x( P),带有EXISTS谓词的子查询(续）,例43 查询选修了全部课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno）；,带有EXISTS谓词的子查询(续）,6. 用EXISTS/NOT EXISTS实现逻辑蕴函(难点)SQL语言中没有蕴函(Implication)逻辑运算可以利用谓词演算将逻辑蕴函谓词等价转换为：
15、p q pq,带有EXISTS谓词的子查询(续）,例44 查询至少选修了学生95002选修的全部课程的学生号码。解题思路：用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要95002学生选修了课程y，则x也选修了y。形式化表示：用P表示谓词 “学生95002选修了课程y”用q表示谓词 “学生x选修了课程y”则上述查询为: (y) p q,等价变换： (y)p q (y (p q ) (y ( p q) y(pq)变换后语义：不存在这样的课程y，学生95002选修了y，而学生x没有选。,带有EXISTS谓词的子查询(续）,用NOT EXISTS谓词表示： SELECT DISTINCT S
16、no 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)；,3.3 查 询,3.3.1 概述3.3.2 单表查询3.3.3 连接查询3.3.4 嵌套查询3.3.5 集合查询3.3.6 小结,3.3.5 集合查询,标准SQL直接支持的集合操作种类并操作(UNION)一般商用数据库支持的集合操作种类并操作(UNION)交操作(INTERSECT)差
17、操作(MINUS),1 并操作,形式 UNION 参加UNION操作的各结果表的列数必须相同；对应项的数据类型也必须相同,并操作（续）,例45 查询计算机科学系的学生及年龄不大于19岁的学生方法一： SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19；,方法二： SELECT DISTINCT * FROM Student WHERE Sdept= CS OR Sage=19；,并操作（续）,例46 查询选修了课程1或者选修了课程2的学生。方法一： SELECT Sno FROM SC
18、WHERE Cno= 1 UNION SELECT Sno FROM SC WHERE Cno= 2 ；,方法二： SELECT DISTINCT Sno FROM SC WHERE Cno= 1 OR Cno= 2 ；,并操作（续）,例47 设数据库中有一教师表Teacher(Tno, Tname,.)。查询学校中所有师生的姓名。 SELECT Sname FROM Student UNION SELECT Tname FROM Teacher;,new,2 交操作,标准SQL中没有提供集合交操作，但可用其他方法间接实现。,例48 查询计算机科学系的学生与年龄不大于19岁的学生的交集本例实际
19、上就是查询计算机科学系中年龄不大于19岁的学生 SELECT * FROM Student WHERE Sdept= CS AND Sage=19；,交操作（续）,例49 查询选修课程1的学生集合与选修课程2的学生集合的交集本例实际上是查询既选修了课程1又选修了课程2的学生 SELECT Sno FROM SC WHERE Cno= 1 AND Sno IN (SELECT Sno FROM SC WHERE Cno= 2 )；,交操作（续）,例50 查询学生姓名与教师姓名的交集本例实际上是查询学校中与教师同名的学生姓名 SELECT DISTINCT Sname FROM Student W
20、HERE Sname IN (SELECT Tname FROM Teacher);,new,3 差操作,标准SQL中没有提供集合差操作，但可用其他方法间接实现。,例51 查询计算机科学系的学生与年龄不大于19岁的学生的差集。 本例实际上是查询计算机科学系中年龄大于19岁的学生 SELECT * FROM Student WHERE Sdept= CS AND Sage19；,差操作（续）,例52 查询学生姓名与教师姓名的差集 本例实际上是查询学校中未与教师同名的学生姓名 SELECT DISTINCT Sname FROM Student WHERE Sname NOT IN (SELECT
21、 Tname FROM Teacher);,new,4. 对集合操作结果的排序,ORDER BY子句只能用于对最终查询结果排序，不能对中间结果排序任何情况下，ORDER BY子句只能出现在最后对集合操作结果排序时，ORDER BY子句中用数字指定排序属性,new,例53 错误写法 SELECT * FROM Student WHERE Sdept= CS ORDER BY Sno UNION SELECT * FROM Student WHERE Sage=19 ORDER BY Sno；,对集合操作结果的排序（续）,正确写法 SELECT * FROM Student WHERE Sdept= CS UNION SELECT * FROM Student WHERE Sage=19 ORDER BY 1；,new,3.3.6 SELECT语句的一般格式,SELECT ALL|DISTINCT 别名 ， 别名 FROM 别名 ， 别名 WHERE GROUP BY ， . HAVING ORDER BY ASC|DESC ， ASC|DESC ；,目标列表达式,目标列表达式格式(1) . *(2) .，. ：由属性列、作用于属性列的集函数和常量的任意算术运算（+，-，*，/）组成的运算公式。,