SQL练习——嵌套查询/集合查询/基于派生表的查询

带有EXISTS谓词的子查询
EXITSTS谓词
（1）存在量词
（2）带有EXISTS谓词的子查询不返回任何数据，只产生逻辑的”true“和”false“

内层为非空--------返回TRUE
内层为空---------返回FALSE
(3)由EXISTS引出的子查询，其目标列表达式通常都用 *

因为带EXISTS的子查询只返回真值或假值，给出列名无实际意义。(我们关心的主要是TRUE 或者 FALSE)

NOT EXISTS谓词

内层为非空--------返回FALSE
内层为空---------返回TRUE(和EXISTS的结果相反)

【例 3.60】查询所有选修了1号课程的学生姓名。

思路分析：
（1）首先取出Student表中的每个Sno值，将Sno值送到SC表中
（2）如果SC表中存在此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';)

不同形式的查询替换

【例 3.55】查询与“刘晨”在同一个系学习的学生。

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

自我理解：该语句的执行顺序为：
先从Student表中选出一个元组，逐一与S2中的姓名为”李晨“的比较，如果系别相同，则为真，否则为假

难点来啦！！！
SQL中没有全称量词，所以需要用EXSITS/NOT EXISTS实现全称量词

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

感觉有点像 双重否定是肯定的感jio

【例 3.63】查询至少选修了学生201215122选修的全部课程的学生号码。

逻辑蕴含表达：查询学号为x的学生，对所有的课程y，只要201215122学生选修了课程y，则x也选修了y。

转换为存在量词：不存在这样的课程y，学生201215122选修了y，而学生x没有选。

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

集合查询的种类
(1) 并—UNION
(2) 交—INTERSECT
(3) 差—EXCEPT

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

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

分别查询计算机科学系的学生 和 年龄不大于19岁的学生，用并(UNION)连接即可！！

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

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

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

分别查询选修了课程1的学生 和 选修了课程2的学生，用并(UNION)连接即可！！

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;

用连接查询也能解决问题！！！

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;

用连接查询也能解决问题！！！

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)

【例3.60】查询所有选修了1号课程的学生姓名。

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

SELECT语句的一般格式

到这里，数据查询就结束啦！