SQL数据查询——嵌套查询

语法格式

SELECT 列名表达式
FROM 表名
WHERE 表达式 运算符 (SELECT 子查询)
其中运算符包含IN、关系运算符、ANY、ALL、EXISTS五类
【注意】
1.SQL允许多层嵌套查询
2.子查询中不能使用ORDER BY子句（但可以使用GROUP BY等分组查询）

带有IN谓词的子查询

带有IN谓词的子查询指父查询与子查询之间用IN进行连接，判断某个属性列值是否在子查询的结果中

例：查询与“刘晨”在同一个系学习的学生
#step 1：确定“刘晨”所在系名
SELECT Sdept
FROM Student
WHERE Sname = '刘晨'
#step 2：查找所有在IS系学习的学生
SELECT Sno,Sname,Sdept
FROM Student
WHERE Sdept = 'IS'
#step 3：用子查询来实现将第一步查询嵌入到第二步查询中用以构造第二步查询的条件
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.Sno = S2.Sno AND S2.Sname = '刘晨'

#同时也可以像自身连接那样用别名将父查询和子查询的Student表区分开
SELECT Sno,Sname,Sdept
FROM Student S1
WHERE S1.Sdept IN
	(SELECT Sdept
	FROM Student S2
	WHERE S2.Sname = '刘晨')

IN谓词实现交运算

例：查询选修课程1的学生集合与选修课程2的学生集合的交集
SELECT Sno
FROM SC
WHERE Cno = '1' AND Sno IN
	(SELECT Sno
	FROM SC
	WHERE Cno = '2')

IN谓词实现差运算

例：查询选修课程1的学生集合与选修课程2的学生集合的差集
SELECT Sno
FROM SC
WHERE Cno = '1' AND Sno NOT IN
	(SELECT Sno
	FROM SC
	WHERE Cno = '2')

带有比较运算符的子查询

带有比较运算符的子查询指父查询与子查询之间用比较运算符进行连接，当用户能确切知道内层查询返回的是单值时可以用>、<、=、>=、<=、!=、<>等比较运算符
【注意】
子查询一定要跟在比较符之后

例：查询选修课程为“信息系统”的学生学号和姓名
SELECT Sno,Sname
FROM Student
WHERE Sno IN  --选修该课程的学生并非只有一个，所以要用IN谓词
	(SELECT Sno
	FROM SC
	WHERE Cno = --信息系统课程号唯一因此可以用=完成
		(SELECT Cno
		FROM Course
		WHERE Cname = '信息系统'))

#以下是错误写法：
SELECT Sno,Sname
FROM Student
WHERE Sno IN  --选修该课程的学生并非只有一个，所以要用IN谓词
	(SELECT Sno
	FROM SC
	WHERE --子查询一定要跟在比较符之后
		(SELECT Cno
		FROM Course
		WHERE Cname = '信息系统' = Cno)

带有ANY谓词的子查询

子查询返回多值时可以使用关系运算符与ANY或ALL谓词的各种组合方式

例：查询其他系中比IS系某一学生年龄小的学生姓名
#方法1
SELECT Sname
FROM Student
WHERE Sage < ANY --年龄比IS系某位同学小
	(SELECT Sage
	FROM Student
	WHERE Sdept = 'IS') AND Sdept <> 'IS' --所在系不是IS

#方法2
SELECT Sname
FROM Student
WHERE Sage < 
	(SELECT MAX(Sage) --通过集函数实现
	FROM Student
	WHERE Sdept = 'IS') AND Sdept <> 'IS'

带有ALL谓词的子查询

例：查询其他系中比IS系所有学生年龄小的学生姓名
#方法1
SELECT Sname
FROM Student
WHERE Sage < ALL  --年龄小于所有IS系学生年龄
	(SELECT Sage
	FROM Student
	WHERE Sdept = 'IS') AND Sdept <> 'IS'

#方法2
SELECT Sname
FROM Student
WHERE Sage <
	(SELECT MIN(Sage) --集函数实现，找出IS系最小年龄
	FROM Student
	WHERE Sdept = 'IS') AND Sdept <> 'IS'

相关子查询

• 不相关子查询：子查询独立执行，且只执行一次
• 相关子查询：子查询的条件依赖于外部父查询中的某狗字段值，其不可单独执行，且要执行多次
  执行过程：
  （1）父查询每执行一次子查询都会被执行一次，并且每次父查询都将查询引用字段值传给子查询
  （2）如果子查询中任何元组与其匹配，父查询就返回元组
  （3）循环（1）（2）直至处理完父查询的每一个元组

引用子查询的值

将父查询中的列的值使用关系运算符与子查询中的值进行比较

例：查询成绩高于本人平均成绩的学号、课程号和成绩
SELECT Sno,Cno,Score
FROM SC X
WHERE Score > 
	(SELECT AVG(Score)
	FROM SC Y
	WHERE Y.Sno = X.Sno)

不引用子查询的值(EXISTS)

不引用子查询值的这类查询只是检查子查询是否返回了记录，需要使用EXISTS关键字，它只产生逻辑真值(TRUE)或逻辑假值(FALSE)，即子查询的结果中至少包含一个元组则EXISTS子查询返回TRUE；子查询结果集合为空则EXISTS子查询返回FALSE

例：查询选修了全部课程的学生姓名
#SQL语言没有全称量词，因此必须利用谓词演算将带有全称量词的谓词转换为等价带有存在量词的谓词
针对这道题，我们应该查询这样的学生，不存在有一门课程是其不选修的
SELECT Sname
FROM Student
WHERE NOT EXISTS
	(SELECT *  --由于返回的值是逻辑真值或逻辑假值而并非元组中某一列的值，因此直接写SELECT *即可
	FROM SC
	WHERE NOT EXISTS
		(SELECT *
		FROM Course
		WHERE Cno = SC.Cno AND Student.Sno = SC.Sno))

例：查询至少选修了学生95002选修的全部课程的学号
#SQL语言也没有蕴含逻辑运算因此也需要利用谓词演算将一个逻辑蕴涵的谓词转换为带有存在量词的谓词
这道题语义为：不存在有这样一门课程y，学生95002选修了y但是学生x没有选修y
SELECT Sno
FROM Student
WHERE NOT EXISTS
	(SELECT *
	FROM SC SCY
	WHERE SCY = '95002' AND NOT EXISTS
		(SELECT *
		FROM SC SCX
		WHERE Student.Sno = SCX.Sno AND SCX.Cno = SCY.Cno))

基于派生表的查询

子查询也可以出现在FROM子句中即基于子查询生成的临时派生表进行查询

例：查询选课门数唯一的学生学号（若只有S1学号的学生选了2门课则S1应为结果之一）
#step 1 查询学生学号及其修读的课程门数
SELECT Sno,COUNT(Cno)
FROM SC
GROUP BY Sno
#step 2 查询门数唯一的课程号
SELECT CT
FROM(SELECT Sno,COUNT(Cno) AS CT
	FROM SC
	GROUP BY Sno) AS T1(Sno,CT)
GROUP BY CT
HAVING COUNT(*) = 1
#step 3 输出学生学号
SELECT T3.Sno 
FROM(SELECT CT
	FROM(SELECT Sno,COUNT(Cno) AS CT
		FROM SC
		GROUP BY Sno) AS T1(Sno,CT)
	GROUP BY Sno) AS T2(CT), --门数唯一的课程号表T2(CT)
	(SELECT Sno,COUNT(Cno) AS CT FROM SC GROUP BY Sno) AS T3(Sno,CT) --学生及其选修课程门数表T3(Sno,CT)
WHERE T2.CT = T3.CT --将T2和T3做等值连接

本文到此就结束了，如有错误欢迎指出！