MySQL入门，第八部分，多表查询（二）

嵌套查询

嵌套查询是指一个SELECT—FROM—WHERE查询块嵌入在另一个SELECT—FROM—WHERE查询块的WHERE子句中的查询

注意：

只有当连接查询投影列的属性来自于一个关系表时才能用嵌套查询等效实现。若连接查询投影列的属性来自于多个关系表，则不能用嵌套查询实现

一、简单子查询（非相关子查询）

简单子查询：

简单子查询独立于外部查询的子查询,子查询总共执行一次,执行完毕后将值传递给外部查询，效率相对更高

当子查询返回的结果是一个集合时，外查询可能需要用到以下的一些关键字

表达式	含义
> ANY	只要大于其中一个即可
> ALL	必须大于所有结果
< ANY	只要小于其中一个即可
< ALL	必须小于所有结果
>= ANY	只要大于或等于其中一个即可
>= ALL	必须大于或等于所有结果
<= ANY	只要小于或等于其中一个即可
<= ALL	必须小于或等于所有结果
= ANY	只要等于其中一个即可
<> ANY	只要于其中一个不等即可
<> ALL	必须于所有结果都不等

 

查询成绩及格的学生的学号

SELECT sname AS 姓名
FROM student
WHERE sno IN
(
	SELECT sno
	FROM study
	WHERE grade >= 60
)

查询成绩最高分学生的学号

SELECT sno AS 学号
FROM study
WHERE grade >= ALL
(
	SELECT grade
	FROM study
)

查询选修了C语言课程的学生的姓名

子查询实现方式：

SELECT sname
FROM student
WHERE sno IN
(
	SELECT sno
	FROM study
	WHERE cno IN
	(
		SELECT cno
		FROM course
		WHERE cname='C语言'
	)
)


笛卡尔积实现方式：

SELECT sname
FROM student, study, course
WHERE student.sno = study.sno AND study.cno = course.cno AND course.cname = 'C语言'

二、相关子查询

相关子查询：

相关子查询的执行依赖于外部查询的数据,外部查询执行一行,子查询就执行一次，效率相对较低

用到相关子查询时可能用到以下两个关键字；注意，相关子查询不一定用到它们，即它们并不是相关子查询的标志

关键字	含义
EXISTS	依次取出外查询得到的每一个元组，代入子查询中计算。若该元组满足子查询的条件，则子查询返回一个布尔值 TRUE，并将该元组作为结果集的一个成员；否则返回布尔值 FALSE ，不作为结果集的一员
NOT EXISTS	依次取出外查询得到的每一个元组，代入子查询中计算。若该元组不满足子查询的条件，则子查询返回一个布尔值 TRUE，并将该元组作为结果集的一个成员；否则返回布尔值 FALSE ，不作为结果集的一员

将选修 操作系统 并且得分 超过85分 的学生的姓名显示出来

select sname
FROM student
WHERE EXISTS
(
	SELECT *
	FROM study
	WHERE student.sno = study.sno AND grade >= 85 AND EXISTS
	(
		SELECT *
		FROM course
		WHERE study.cno = course.cno AND cname = '操作系统'
	)
)

将没有选修操作系统的学生姓名显示出来

=======================================================================================================

分析：没有选修操作系统的学生 ：（TRUE（学生（没有选择操作系统））） ===等价于===> （FALSE（学生（选修了操作系统）））

（TRUE（学生（没有选择操作系统）））虽然从思维上很好理解，但是如何证明一个学生没有选择操作系统？这似乎不是一件简单的事情，因为元组是一条一条验证的，假如一个学生选修了操作系统和C语言，当遍历该学生选修了操作系统时确实可以排除该学生的姓名，但是当遍历到该学生选修了C语言的这条记录时该何去何从呢？在记录中，该记录中该学生确实没有选修操作系统！因此代码上第一种方法是极其难以实现的，因此我们选择第二种

=======================================================================================================

select sname
FROM student
WHERE NOT EXISTS
(
	SELECT *
	FROM study
	WHERE student.sno = study.sno AND EXISTS
	(
		SELECT *
		FROM course
		WHERE study.cno = course.cno AND cname = '操作系统'
	)
)

将选修了全部课程的学生的姓名显示出来

=======================================================================================================

（TRUE（学生（选修了全部课程）））===等价于===>（学生（FALSE（课程（该学生没有选修））））

用自然语言描述即为：

对于任意一门课程，该学生都选修了 ===等价于===>不存在这样一门课程，这个课程没有被该学生选修

存在于这样一个学生，他选修的课包含了学校开的所有课 ===等价于===>不存在这样一个学生，学校开的所有课不被他选修的课所包含

=======================================================================================================

SELECT sname
FROM student
WHERE NOT EXISTS
(
	SELECT *
	FROM course
	WHERE NOT EXISTS
	(
		SELECT *
		FROM study
		WHERE sno = student.sno AND cno = course.cno
	)
)

或

SELECT sname
FROM student
WHERE NOT EXISTS
(
	SELECT *
	FROM course
	WHERE cno NOT IN
	(
		SELECT cno
		FROM study
		WHERE student.sno = study.sno
	)
)

查询一个学生y，该学生至少选修了另外一个学生x选修的全部课程

=======================================================================================================

分析：

不存在这样的一门课程，该课程被x选修了，而没有被y选修

=======================================================================================================

SELECT DISTINCT sno
FROM study X
WHERE NOT EXISTS
(
	SELECT *
	FROM study Y
	WHERE y.sno = '03061' AND NOT EXISTS
	(
		SELECT *
		FROM study z
		WHERE z.sno = x.sno AND z.cno = y.cno
	)
)