目录

• 前言
• 带有IN谓词的子查询
• • 不相关子查询
  • 相关子查询
• 带有比较运算符的子查询
• 带有ANY（SOME）或ALL谓词的子查询
• 带有EXISTS谓词的子查询

前言

在SQL语言中，一个SELECT-FROM-WHERE称为一个查询块。将一个查询块嵌套在另一个查询块中的WHERE子句或HAVING短语的条件中的查询称为嵌套查询
其中上层的查询块称为外层查询或者父查询，下层的查询块称为内层查询或者子查询
例如：查询选择2号课程的学生的姓名

SELECT Sname 
FROM Student
WHERE Sno IN
(SELECT Sno
FROM SC 
WHERE Cno='2');
SELECT * FROM Student;
SELECT * FROM SC;

带有IN谓词的子查询

子查询的查询条件不依赖于父查询，这类子查询称为不相关子查询，
如果子查询的查询条件依赖于父查询，这类子查询称为相关子查询，整个查询语句称为相关嵌套查询语句。

不相关子查询

例3.55 查询与“刘晨”在同一个系学习的学生
① 确定“刘晨”所在的系名

SELECT Sdept
FROM Student
WHERE Sname='刘晨';

SELECT * FROM Student;

②查询所有在CS系学习的学生

SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept='CS';

SELECT * FROM Student;

然后对于这种形式的语句，可以采用嵌套结构：

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

SELECT * FROM Student;
SELECT * FROM SC;

此例也可以采用自身连接来进行：

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

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

SELECT Sno,Sname      --③最后在Student关系中取出Sno和Sname
FROM Student 
WHERE Sno IN
(SELECT Sno           --②然后在SC关系中找出选修了3号课程的学生学号
FROM SC
WHERE Cno IN
(SELECT Cno           --①首先在Course关系中找出“信息系统”的课程号，为3号
FROM Course
WHERE Cname='信息系统')
);

SELECT * FROM Student;
SELECT * FROM SC;
SELECT * FROM Course;

同样这个例子可以靠连接查询来实现：

SELECT Student.Sno,Sname
FROM Student,SC,Course
WHERE Student.Sno=SC.Sno AND
SC.Cno=Course.Cno AND
Course.Cname='信息系统';

SELECT * FROM Student;
SELECT * FROM SC;
SELECT * FROM Course;

相关子查询

例子为例3.57

带有比较运算符的子查询

例3.55 代换，使用=代替IN

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

SELECT * FROM Student;
SELECT * FROM SC;

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

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

--SELECT * FROM Student;
SELECT * FROM SC;
--SELECT * FROM Course;

x是SC的别名，又称为元组变量
语句分析：
①从外层查询中取出SC的一个元组x，将元组x的Sno值（201215121）传送给内层

SELECT AVG(Grade)
FROM SC y
WHERE y.Sno='201215121';

②执行内层查询，得到值88（近似值），用该值代替内层查询，得到外层查询

SELECT Sno,Cno
FROM SC x
WHERE Grade>=88;

③执行这个查询，得到：

（201215121，1）
（201215121，3）

④然后外层查询取出下一个元组重复上述①至③操作，直至外层的SC表元组处理完毕
得到上图结果。

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

子查询返回单值时可以使用比较运算符，返回多值时要使用**ANY（有的系统是SOME）**或这ALL谓词修饰符

>  ANY 大于子查询结果中的某个值

> ALL  大于子查询结果中的所有值

< ANY 小于子查询结果中的某个值

< ALL 小于子查询结果中的所有值

>=  ANY  大于等于子查询结果中的某个值

>= ALL  大于等于子查询结果中的所有值

<= ANY 小于等于子查询结果中的某个值

<= ALL 小于等于子查询结果中的所有值

= ANY 等于子查询结果中的某个值

= ALL 等于子查询结果中的所有值（通常没有实际意义）

!=(或<>) ANY 不等于子查询结果中的某个值

!=(或<>) ALL 不等于子查询结果中的任何一个值

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

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

本查询也可以用聚集函数来实现，首先用子查询找出所有CS系中最大年龄（20），然后在父查询中查所有非CS系中年龄小于20的学生

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

结果是一样的
例3.59 查询非计算机科学系中比计算机科学系所有学生年龄都小或相等的学生姓名和年龄

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

本例同样可以使用聚集函数：

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

=ANY等价于IN谓词，<ANY等价于<MAX，<>ALL等价于NOT IN谓词，<ALL等价于<MIN.

带有EXISTS谓词的子查询

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

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

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

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

利用EXISTS谓词对带有IN的例3.55进行查询

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

SELECT * FROM Student;

例3.62 查询选修了全部课程的学生姓名
SQL中没有全称量词（for all），但是可以把带有全称量词的谓词传华为等价的带有存在量词的谓词：

(∀x)P=┐(∃x(┐P))

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

SELECT * FROM Student;
SELECT * FROM SC;
SELECT * FROM Course ;

例3.63 查询至少选修了学生201215122选修的全部课程的学生号码
SQL语言中没有蕴含逻辑运算，但是可以利用谓词演算将一个逻辑蕴含的谓词等价转换为：

p→q＝┐pｖq

将该查询进行转换：

（∀y）p→q＝┐（∃y(┐(p→q)))=┐(∃y(┐(┐p V q)))=┐∃y(p V ┐q)

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

SELECT * FROM Student;
SELECT * FROM SC;
SELECT * FROM Course ;

总结：
注意！！！：
区分SQL题目中‘任意一个‘是和正常思维里的不一样，‘任意一个’表示的是从人中选择，只要有合适的即算符合算法
对于’都‘’全部‘则表示对于所有的全体都符合才算符合算法。