SELECT（嵌套查询续、集合查询、基于派生表的查询）

接上一篇的嵌套查询

带有EXISTS谓词的子查询

EXISTS谓词，存在量词 ∃

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

★若内层查询结果非空，则外层的WHERE子句返回真值
✦若内层查询结果为空，则外层的WHERE子句返回假值

由EXISTS引出的子查询，其目标列表达式通常都用 * ，因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。

NOT EXISTS谓词
★若内层查询结果非空，则外层的WHERE子句返回假值
✦若内层查询结果为空，则外层的WHERE子句返回真值

[例 3.60]查询所有选修了1号课程的学生姓名。
思路分析：
本查询涉及Student和SC关系
在Student中依次取每个元组的Sno值，用此值去检查SC表
若SC中存在这样的元组，其Sno值等于此Student.Sno值，并且其Cno= ‘1’，则取此Student.Sname送入结果表

 SELECT Sname
 FROM Student
 WHERE EXISTS
               (SELECT *
                FROM SC
                WHERE Sno=Student.Sno AND Cno= '1');

[例 3.61] 查询没有选修1号课程的学生姓名。

 SELECT Sname
 FROM     Student
 WHERE NOT EXISTS
               (SELECT *
                FROM SC
                WHERE Sno = Student.Sno AND Cno='1');

不同形式的查询间的替换
一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
所有带IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换
[例 3.55]查询与“刘晨”在同一个系学习的学生。
可以用带EXISTS谓词的子查询替换：

 SELECT Sno,Sname,Sdept
 FROM Student S1
  WHERE EXISTS
         　   (SELECT *
                 FROM Student S2
                 WHERE S2.Sdept = S1.Sdept AND
                               S2.Sname = '刘晨');

用EXISTS/NOT EXISTS实现全称量词

SQL语言中没有全称量词∀

可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：

[例 3.62] 查询选修了全部课程的学生姓名。

SELECT Sname
FROM Student
WHERE NOT EXISTS
                  (SELECT *
                    FROM Course
                    WHERE NOT EXISTS
                                  (SELECT *
                                   FROM SC
                                   WHERE Sno= Student.Sno
                                         AND Cno= Course.Cno
                                  )
                   );

执行过程
进入第一层：取Student表中的一个Sno值‘201215121’
进入第二层：取Course表中的一个Cno值‘1’
进入第三层：判断SC表中Sno是否有等于‘201215121’ 并且Cno=‘1’的元组，经过NOT EXISTS的取反，存在F，不存在就是T—— 结果F
返回第二层：取第二个Cno值‘2’
进入第三层：判断Cno=‘2’是否存在，结果取反 —— 结果F
重复以上操作
到第二层的结果是：

返回第一层的结果：T（NOT EXISTS再次取反）
结果为T，所以将Sno='201215121’学生的学生姓名取出放入结果表中。
进入第一层：取第二个Sno值‘201215122’
再次重复以上的操作。

用EXISTS/NOT EXISTS实现逻辑蕴涵
★SQL语言中没有蕴涵逻辑运算
★可以利用谓词演算将逻辑蕴涵谓词等价转换为：

[例 3.63]查询至少选修了学生201215122选修的全部课程的学生号码。
解题思路：
用逻辑蕴涵表达：查询学号为x的学生，对所有的课程y，只要201215122学生选修了课程y，则x也选修了y。
形式化表示：
1.用P表示谓词 “学生201215122选修了课程y”
2.用q表示谓词 “学生x选修了课程y”
3.则上述查询为
:
用上述式子等价转换

4.变换后语义：不存在这样的课程y，学生201215122选修了y，而学生x没有选。
用NOT EXISTS谓词表示：

SELECT DISTINCT Sno
FROM SC SCX
WHERE NOT EXISTS
                 (SELECT *
                  FROM SC SCY
                  WHERE SCY.Sno = ' 201215122 '  AND
                                NOT EXISTS
                                (SELECT *
                                 FROM SC SCZ
                                 WHERE SCZ.Sno=SCX.Sno AND
                                               SCZ.Cno=SCY.Cno));

集合查询

集合操作的种类

★并-UNION
★交-INTERSECT
★差-EXCEPT

参加集合操作的各查询结果的列数必须相同
对应项的数据类型必须相同

[例 3.64] 查询计算机科学系的学生及年龄不大于19岁的学生。

SELECT *
FROM Student
WHERE Sdept= 'CS'
UNION
SELECT *
FROM Student
WHERE Sage<=19;

UNION：将多个查询结果合并起来时，系统自动去掉重复元组
UNION ALL：将多个查询结果合并起来时，保留重复元组

[例 3.65] 查询选修了课程1或者选修了课程2的学生。

SELECT Sno
FROM SC
WHERE Cno='1'
UNION
SELECT Sno
FROM SC
WHERE Cno= '2';

[例3.66] 查询计算机科学系的学生与年龄不大于19岁的学生的交集。

SELECT *
FROM Student
WHERE Sdept='CS' 
INTERSECT
SELECT *
FROM Student
WHERE Sage<=19 

实际上就是查询计算机科学系中年龄不大于19岁的学生。

还可以用连接方式解决

SELECT *
FROM Student
WHERE Sdept= 'CS' AND  Sage<=19;

[例 3.67]查询既选修了课程1又选修了课程2的学生。

SELECT Sno
FROM SC
WHERE Cno='1' 
INTERSECT
SELECT Sno
FROM SC
WHERE Cno='2';

也可以使用嵌套查询

SELECT Sno
FROM    SC
 WHERE Cno=' 1 ' AND Sno IN
                            (SELECT Sno
                             FROM SC
                             WHERE Cno='2');

[例 3.68] 查询计算机科学系的学生与年龄不大于19岁的学生的差集。

SELECT *
FROM Student
WHERE Sdept='CS'
EXCEPT
SELECT  *
FROM Student
WHERE Sage <=19;

实际就是大于19岁的学生
用连接查询

SELECT *
FROM Student
WHERE Sdept= 'CS' AND  Sage>19;

基于派生表的查询

子查询不仅可以出现在WHERE子句中，
还可以出现在FROM子句中，
这时子查询生成的临时派生表成为主查询的查询对象。

[例3.57]找出每个学生超过他自己选修课程平均成绩的课程号

SELECT Sno, Cno
FROM SC, (SELECT  Sno, Avg(Grade) 
                    FROM   SC 
             GROUP BY   Sno)
                    AS   Avg_sc(avg_sno,avg_grade)
WHERE SC.Sno = Avg_sc.avg_sno
                    and SC.Grade >=Avg_sc.avg_grade

如果子查询中没有聚集函数，派生表可以不指定属性列，子查询SELECT子句后面的列名为其缺省属性。

[例]查询所有选修了1号课程的学生姓名，可以用如下查询完成：

SELECT Sname
FROM     Student,  
               (SELECT Sno FROM SC WHERE Cno='1') AS SC1
WHERE  Student.Sno=SC1.Sno;

---

疫情在家上网的不知道第几周。。。重点和难点还是在于嵌套查询，继续加油认真学习，一定可以掌握好的。