【2019-2020春学期】数据库作业8：SQL练习5 - SELECT（嵌套查询EXISTS、集合查询、基于派生表的查询）

4.带有EXISTS谓词的子查询

EXISTS谓词
（1）存在量词 $\exists$

（2）带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值 “true” 或逻辑假值 “false” 。
<1>若内层查询结果非空，则外层的WHERE子句返回真值
<2>若内层查询结果为空，则外层的WHERE子句返回假值

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

NOT EXISTS谓词（与EXISTS谓词相反）
（1）若内层查询结果非空，则外层的WHERE子句返回假值
（2）若内层查询结果为空，则外层的WHERE子句返回真值

例 3.60查询所有选修了1号课程的学生姓名。
思路分析：
（1）本查询涉及Student和SC关系
（2）在Student中依次取每个元组的Sno值，用此值去检查SC表
（3）若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');

不同形式的查询间的替换
（1）一些带EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换
（2）所有带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语言中没有全称量词 $\forall$（For all）
可以把带有全称量词的谓词转换为等价的带有存在量词的谓词：
$(\forall x)P\equiv \neg (\exists x(\neg P))$

例 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
                                      )
                       );

转义后的表达：没有一门课程是他不选修的
类似两个否定等于肯定

！！！ 用EXISTS/NOT EXISTS实现逻辑蕴涵（难点）

SQL语言中没有蕴涵逻辑运算
可以利用谓词演算将逻辑蕴涵谓词等价转换为：
$p\to q\equiv \neg p\vee q$
（参考离散数学）

例 3.63 查询至少选修了学生201215122选修的全部课程的学生号码。
解题思路：
(1)用逻辑蕴涵表达：查询学号为x的学生，对所有的课程y，只要201215122学生选修了课程y，则x也选修了y。
(2)形式化表示：
<1>用p表示谓词 “学生201215122选修了课程y”
<2>用q表示谓词 “学生x选修了课程y”
<3>则上述查询为: $（\forall y）p\to q$
(3)等价变换：$（\forall y）p\to q\equiv \neg \exists y(p\wedge \neg q)$
(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));

EXISTS的使用总结：
1、谓词EXISTS只会返回逻辑真值true或者逻辑假值false。
2、EXISTS若内层查询结果为非空，则外层WHERE子句返回真值；若为空，则返回假值；NOT EXISTS谓词相反。
3、一些EXISTS或NOT EXISTS谓词的子查询不能被其他形式的子查询等价替换，但所有带有IN谓词、比较运算符、ANY和ALL谓词的子查询都能用带EXISTS谓词的子查询等价替换。

大概步骤：先按照离散数学的方法将问题的形式化的表达出来，把带有全称量词的谓词转换为等价的带有存在量词的谓词，用谓词演算将逻辑蕴涵谓词等价转换（$(\forall x)P\equiv \neg (\exists x(\neg P))$，$p\to q\equiv \neg p\vee q$）），理解转换后的语义，一般情况下否定部分用NOT EXISTS谓词，肯定部分用EXISTS谓词，根据逻辑嵌套。

三、集合查询

集合操作的种类
1、并-UNION
2、交-INTERSECT
3、差-EXCEPT

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

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

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

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

若为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 

例 3.66 实际上就是查询计算机科学系中年龄不大于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;  

例3.68 实际上是查询计算机科学系中年龄大于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子句后面的列名为其缺省属性。

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

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

就是在FROM子句里进行子查询，形成一个派生表（临时表）。若子查询中有聚集函数，要指定属性列，反之，不指定（但都要指定表名）。后续与无派生表时类似。

两种方法对比，其实大体意思类似只是执行顺序不同，可根据具体情况选择，令语句更加简洁明了。

SELECT总结：

SELECT [ALL|DISTINCT]
<目标列表达式> [别名] [ ,<目标列表达式> [别名]] …
FROM <表名或视图名> [别名]
[ ,<表名或视图名> [别名]] …
|(<SELECT语句>)[AS]<别名>
[WHERE <条件表达式>]
[GROUP BY <列名1>[HAVING<条件表达式>]]
[ORDER BY <列名2> [ASC|DESC]];

分析题目意思，按此格式书写即可。

心得体会：
我是真的不太会写心得体会。。。总之，重在理解，多实验，自己打代码有助于记忆和理解。比较绕的部分是NOT EXISTS的嵌套查询，可以通过纸笔，画执行步骤会更清晰易懂。最后，千万别拖作业，好累（当然我还是没有偷工减料），就是如果早写能从容一点。