数据查询 单表查询 连接查询 嵌套查询 集合查询.

Presentation on theme: "数据查询 单表查询 连接查询 嵌套查询 集合查询."— Presentation transcript:

1 数据查询 单表查询 连接查询 嵌套查询 集合查询

2 嵌套查询概述 一个SELECT-FROM-WHERE语句称为一个查询块
将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询

3 嵌套查询 SELECT Sname /*外层查询/父查询*/ FROM Student WHERE Sno IN
（SELECT Sno /*内层查询/子查询*/ FROM SC WHERE Cno= ' 2 '）；

4 嵌套查询 子查询的限制 不能使用ORDER BY子句 层层嵌套方式反映了SQL语言的结构化 有些嵌套查询可以用连接运算替代

5 嵌套查询分类 不相关子查询 子查询的查询条件不依赖于父查询 相关子查询 子查询的查询条件依赖于父查询

6 嵌套查询求解方法 不相关子查询 是由里向外逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。

7 嵌套查询求解方法(续) 相关子查询 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表； 然后再取外层表的下一个元组； 重复这一过程，直至外层表全部检查完为止。

8 嵌套查询 一、带有IN谓词的子查询 二、 带有比较运算符的子查询 三、 带有ANY或ALL谓词的子查询 四、 带有EXISTS谓词的子查询

9 一、带有IN谓词的子查询 此查询要求可以分步来完成 [例43] 查询与“刘晨”在同一个系学习的学生。 ① 确定“刘晨”所在系名
[例43] 查询与“刘晨”在同一个系学习的学生。 此查询要求可以分步来完成 ① 确定“刘晨”所在系名 SELECT Sdept FROM Student WHERE Sname= ' 刘晨 '； 结果为： ‘CS’

10 带有IN谓词的子查询（续） ② 查找所有在CS系学习的学生。 SELECT Sno，Sname，Sdept FROM Student
WHERE Sdept= ' CS '； 结果为： Sno Sname Sdept 李勇 CS 刘晨

11 带有IN谓词的子查询（续） 将第一步查询嵌入到第二步查询的条件中 SELECT Sno，Sname，Sdept FROM Student
WHERE Sdept IN (SELECT Sdept WHERE Sname= ‘ 刘晨 ’)； 此查询为不相关子查询。

12 带有IN谓词的子查询（续） 用自身连接完成[例43]查询要求 SELECT S1.Sno，S1.Sname，S1.Sdept
FROM Student S1，Student S2 WHERE S1.Sdept = S2.Sdept AND S2.Sname = '刘晨'；

13 带有IN谓词的子查询（续） [例44]查询选修了课程名为“信息系统”的学生学号和姓名
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= ‘信息系统’ ) ); 注意一致性

14 带有IN谓词的子查询（续） 用连接查询实现[例44] SELECT Sno，Sname FROM Student，SC，Course
WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno AND Course.Cname=‘信息系统’；

15 嵌套查询 一、带有IN谓词的子查询 二、 带有比较运算符的子查询 三、 带有ANY或ALL谓词的子查询 四、 带有EXISTS谓词的子查询

16 二、带有比较运算符的子查询 当能确切知道内层查询返回单值时，可用比较运算符（>，<，=，>=，<=，!=或< >）。 与ANY或ALL谓词配合使用

17 带有比较运算符的子查询（续） 例：假设一个学生只可能在一个系学习，并且必须属于一个系，则在[例43]可以用 = 代替IN ：
SELECT Sno，Sname，Sdept FROM Student WHERE Sdept = (SELECT Sdept WHERE Sname= ‘刘晨’)；

18 带有比较运算符的子查询（续） 子查询一定要跟在比较符之后 错误的例子： SELECT Sno，Sname，Sdept
FROM Student WHERE ( SELECT Sdept FROM Student WHERE Sname= ‘ 刘晨 ’ ) = Sdept；

19 带有比较运算符的子查询（续） SELECT Sno， Cno ［例45］找出每个学生超过他选修课程平均成绩的课程号。 FROM SC x
WHERE Grade >=(SELECT AVG(Grade) FROM SC y WHERE y.Sno=x.Sno); 相关子查询

20 带有比较运算符的子查询（续） 可能的执行过程：
1. 从外层查询中取出SC的一个元组x，将元组x的Sno值（ ）传送给内层查询。 SELECT AVG(Grade) FROM SC y WHERE y.Sno=' '; 2. 执行内层查询，得到值88（近似值），用该值代替内层查询，得到外层查询： SELECT Sno， Cno FROM SC x WHERE Grade >=88；

21 带有比较运算符的子查询（续） 3. 执行这个查询，得到 （200615121，1）
4.外层查询取出下一个元组重复做上述1至3步骤，直到外层的SC元组全部处理完毕。结果为: （ ，2）

22 嵌套查询 一、带有IN谓词的子查询 二、 带有比较运算符的子查询 三、 带有ANY或ALL谓词的子查询 四、 带有EXISTS谓词的子查询

23 三、带有ANY或ALL谓词的子查询 谓词语义 ANY：任意一个值 ALL：所有值

24 带有ANY或ALL谓词的子查询 需要配合使用比较运算符 > ANY 大于子查询结果中的某个值

25 带有ANY或ALL谓词的子查询 [例46] 查询其他系中比计算机系某一学生年龄小的学生姓名和年龄 SELECT Sname，Sage
[例46] 查询其他系中比计算机系某一学生年龄小的学生姓名和年龄 SELECT Sname，Sage FROM Student WHERE Sage < ANY (SELECT Sage WHERE Sdept= ' CS ') AND Sdept <> ‘CS ' ; /*父查询块中的条件 */

26 带有ANY或ALL谓词的子查询 结果： 执行过程：
1.RDBMS执行此查询时，首先处理子查询，找出CS系中所有学生的年龄，构成一个集合(20，19) 2. 处理父查询，找所有不是CS系且年龄小于20或19的学生 Sname Sage 王敏 18 张立 19

27 带有ANY或ALL谓词的子查询 用聚集函数实现[例46] SELECT Sname，Sage FROM Student
WHERE Sage < (SELECT MAX(Sage) FROM Student WHERE Sdept= ‘CS ') AND Sdept <> ' CS ’; 用集函数实现子查询通常比直接用ANY或ALL查询效率要高，因为前者通常能够减少比较次数

28 带有ANY或ALL谓词的子查询 [例47] 查询其他系中比计算机系所有学生年龄都小的学生姓名及年龄。 方法一：用ALL谓词
[例47] 查询其他系中比计算机系所有学生年龄都小的学生姓名及年龄。 方法一：用ALL谓词 SELECT Sname，Sage FROM Student WHERE Sage < ALL (SELECT Sage WHERE Sdept= ' CS ') AND Sdept <> ' CS ’;

29 带有ANY或ALL谓词的子查询 方法二：用聚集函数 SELECT Sname，Sage FROM Student
WHERE Sage < (SELECT MIN(Sage) WHERE Sdept= ' CS ') AND Sdept <>' CS ’;

30 ANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系
带有ANY或ALL谓词的子查询 ANY（或SOME），ALL谓词与聚集函数、IN谓词的等价转换关系 = <>或!= < <= > >= ANY IN -- <MAX <=MAX >MIN >= MIN ALL NOT IN <MIN <= MIN >MAX >= MAX

31 嵌套查询 一、带有IN谓词的子查询 二、 带有比较运算符的子查询 三、 带有ANY（SOME）或ALL谓词的子查询
四、 带有EXISTS谓词的子查询

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

33 带有EXISTS谓词的子查询(续） [例48]查询所有选修了1号课程的学生姓名。 用嵌套查询 SELECT Sname
FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno AND Cno= ‘ 1 ’)；

34 带有EXISTS谓词的子查询(续） 思路分析： 本查询涉及Student和SC关系
在Student中依次取每个元组的Sno值，用此值去检查SC关系 若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= '1'，则取此Student.Sname送入结果关系

35 带有EXISTS谓词的子查询(续） 用连接运算 SELECT Sname FROM Student, SC
WHERE Student.Sno=SC.Sno AND SC.Cno= '1';

36 带有EXISTS谓词的子查询(续） [例49] 查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student
[例49] 查询没有选修1号课程的学生姓名。 SELECT Sname FROM Student WHERE NOT EXISTS (SELECT * FROM SC WHERE Sno = Student.Sno AND Cno=‘1’)； 此例用连接运算难于实现

37 带有EXISTS谓词的子查询(续) 3. 不同形式的查询间的替换
一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换 所有带IN谓词、比较运算符、ANY和ALL谓词的子查询 都能用带EXISTS谓词的子查询等价替换。

38 带有EXISTS谓词的子查询(续） 例[50]：[例43]查询与“刘晨”在同一个系学习的学生。 可以用带EXISTS谓词的子查询替换：
SELECT Sno，Sname，Sdept FROM Student S1 WHERE EXISTS 　 (SELECT * FROM Student S2 WHERE S2.Sdept = S1.Sdept AND S2.Sname = ‘刘晨’)；

39 带有EXISTS谓词的子查询(续） 4. 相关子查询的效率
例44：不相关子查询的效率高于相关子查询的效率

40 带有EXISTS谓词的子查询(续） 相关子查询的效率可能高于连接查询 例[51]：查询选修了课程的学生姓名 法一： SELECT Sname
FROM Student WHERE EXISTS (SELECT * FROM SC WHERE Sno=Student.Sno)；

41 带有EXISTS谓词的子查询(续） 法二： SELECT Sname FROM Student，SC
WHERE Student.Sno=SC.Sno；

42 带有EXISTS谓词的子查询(续） 法三： SELECT Sname FROM Student WHERE sno in
(SELECT distinct sno FROM SC)；

43 带有EXISTS谓词的子查询(续） (x)P ≡  ( x( P)) 5. 用EXISTS/NOT EXISTS实现全称量词(难点)
SQL语言中没有全称量词 （For all） 可以把带有全称量词的谓词转换为等价的带有存在量词的谓词： (x)P ≡  ( x( P))

44 带有EXISTS谓词的子查询(续） [例52]查询选修了全部课程的学生姓名。 FROM Student WHERE NOT EXISTS
SELECT Sname FROM Student WHERE NOT EXISTS （SELECT * FROM Course (SELECT * FROM SC WHERE Sno= Student.Sno AND Cno= Course.Cno))；

45 带有EXISTS谓词的子查询(续） 6. 用EXISTS/NOT EXISTS实现逻辑蕴函(难点) p  q ≡  p∨q
SQL语言中没有蕴函(Implication)逻辑运算 可以利用谓词演算将逻辑蕴函谓词等价转换为： p  q ≡  p∨q

46 带有EXISTS谓词的子查询(续） [例53]查询至少选修了学生200615122选修的全部课程的学生号码。 解题思路：
用逻辑蕴函表达：查询学号为x的学生，对所有的课程y，只要 学生选修了课程y，则x也选修了y。 形式化表示： 用p表示谓词 “学生 选修了课程y” 用q表示谓词 “学生x选修了课程y” 则上述查询为: (y) p  q

47 带有EXISTS谓词的子查询(续） 等价变换： (y)p  q ≡  (y ((p  q ))
变换后语义：不存在这样的课程y，学生 选修了y，而学生x没有选。

48 带有EXISTS谓词的子查询(续） SELECT DISTINCT Sno 用NOT EXISTS谓词表示： FROM SC SCX
WHERE NOT EXISTS (SELECT * FROM SC SCY WHERE SCY.Sno = ' ' AND NOT EXISTS FROM SC SCZ WHERE SCZ.Sno=SCX.Sno AND SCZ.Cno=SCY.Cno))；

49 思考题 查询选修了计算机系开设的全部课程的学生。