【Hbu数据库 】第五周-2 嵌套查询

顾名思义 嵌(qian,之前一直以为这次是xiang镶)套查询就是一层套一层的查询 层层套娃 实现一句话完成多条件的查询 子查询中不可以用ORDER BY 子句，该子句只可对最终结果来查排序使用。

带有IN谓词的子查询

嵌套查询里 子查询结果往往是一个集合，谓词IN是嵌套查询中最常使用的谓词

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

SELECT Sno, Sname, Sdept
    	FROM Student
   	WHERE Sdept  IN
                  (SELECT Sdept
                   FROM Student
                   WHERE Sname= '刘晨');
    

子查询的结果不依赖于父查询，所以该种查询可以被称为 不相关查询

这个例子也可以自身连接来实现

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

原理已在上篇里说过。

查询选修了课程名为“信息系统”的学生学号和姓名

SELECT Sno,Sname                
  	FROM    Student                        
 	WHERE Sno  IN
             (SELECT Sno                   
              FROM    SC                        
              WHERE  Cno IN
                     (SELECT Cno            
                       FROM Course           
                       WHERE Cname= '信息系统'                      
		                  )
              );

这里第一层是先找出信息系统的课程号，第二层是通过课程号找到对应的学号，最后通过学号找到学生的姓名。

带有比较运算符的子查询

第一个例子中，由于一个学生只可能在一个系学习，则可以用 = 代替IN

SELECT Sno, Sname, Sdept
    	FROM Student
   	WHERE Sdept  =
                  (SELECT Sdept
                   FROM Student
                   WHERE Sname= '刘晨');
    

找出每个学生超过他选修课程平均成绩的课程号。

SELECT Sno, Cno
    FROM    SC  x
    WHERE Grade >=(SELECT AVG(Grade) 
		                               FROM  SC y
                                   WHERE y.Sno=x.Sno);

值得一提的是，这里是一个相关子查询，因为这里内层查询也用到了外层的x.sno

带有ANY（SOME）或ALL谓词的子查询

使用ANY或ALL谓词时必须同时使用比较运算

查询非计算机科学系中比计算机科学系任意一个学生年龄小的学生姓名和年龄

值得说的是，这里的任意并非所有，而是只有一个就够了。

SELECT Sname,Sage
    FROM    Student
    WHERE Sage < ANY (SELECT  Sage
 FROM    Student
WHERE Sdept= 'CS ')
     AND Sdept <> 'CS ';//这个AND是父模块里的条件。

为了实现这一功能，也可以使用聚集函数 max

SELECT Sname,Sage
     FROM   Student
     WHERE Sage < 
     (SELECT MAX(Sage)
       FROM Student
       WHERE Sdept= 'CS ')
       AND Sdept <> ' CS ';

结果是一样的，意思也很好看明白。老师说嵌套查询是一种比较高级的查询方式，也是我们作为科班的学生要掌握的一种手段，因为系统的学习，我们才有面对不同困难时的灵活思考变通的能力。

查询非计算机科学系中比计算机科学系所有学生年龄都小的学生姓名及年龄。

 SELECT Sname,Sage
    FROM Student
    WHERE Sage < ALL
 (SELECT Sage
    FROM Student
     WHERE Sdept= 'CS ')
       AND Sdept <> ' CS ';

和上一个例子大同小异，同样 用聚集函数实现时使用min就好了。

带有EXISTS谓词的子查询

exists谓词代表 存在量词 E（倒着的那个 打不出来）
带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。
若内层查询结果非空，则外层的WHERE子句返回真值
若内层查询结果为空，则外层的WHERE子句返回假值
NOT EXISTS恰恰相反

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

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

将Student里的学号和SC表中的相对照，并且当课程号为1的时候 说明存在，此时取出该学生姓名。

查询没有选修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谓词的子查询 等价替换

用带EXISTS谓词的子查询替换IN：
还是例1 还是刘晨：

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

查询选修了全部课程的学生姓名。

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语言中没有蕴涵逻辑运算→
逻辑蕴涵谓词 等价转换为：

 查询至少选修了学生201215122选修的全部课程的学生号码。
 
用逻辑蕴涵表达：查询学号为x的学生，对所有的课程y，
只要201215122学生选修了课程y，则x也选修了y。
用P表示谓词 “学生201215122选修了课程y”
用q表示谓词 “学生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));

这一块内容让我感到抽象，主要是离散数学的知识和逻辑上的东西很多，需要多思考写写才能反应过来~