第四章 MySQL高级查询（二）
一.EXISTS子查询
在执行create 或drop语句之前，可以使用exists语句判断该数据库对像是否存在，返回值是true或false。除此之外，exists也可以作为where语句的子查询，语法如下：
SELECT ……FROM 表名 WHERE EXISTS(子查询)；
EXISTS关键字后面的参数是一个任意的子查询，如果该子查询没有返回行，则EXISTS子查询的结果为true,此时再执行外层查询语句。如果EXISTS子查询结果为false,此时外层语句不再执行查询。
例：检查Logic Java课程最近一次考试，如果成绩有80以上者，则显示分数排在前5位的学员和分数。
SELECT `studentNo` AS 学号，`studentResult` 成绩 FROM `result`
WHERE EXISTS(
SELECT * FROM `result` WHERE `subjectNo` = (
SELECT `subjectNo` FROM `subject` WHERE `subjectName`='Logic Java'
)AND `examDate`=(
SELECT MAX(`examDate`)FROM `result` WHERE `subjectNo`=(
SELECT `subjectNo` FROM `subject`
WHERE `subjectName` = 'Logic Java')
)AND `studentResult`>80)
AND `subjectNo` = (SELECT `subjectNo` FROM `subject`
WHERE `subjectName` = 'Logic Java')
ORDER BY `studentResult` DESC LIMIT 5;
二.NOT EXISTS子查询
EXISTS 和IN一样，同时允许添加NOT关键字实现取反操作，NOT EXISTS表示不存在
例：检查Logic Java课程最近的一次考试，如果全部没有通过考试（60分及格线），则认为考试偏难，计算该次考试的平均分+5.
分析：所有人都没有通过考试，则“不存在成绩大于60的学生”，可以采用NOT EXISTS检测。
SELECT AVG(studentresult)+5 AS 平均分 FROM resulf
WHERE NOT EXISTS(
SELECT * FROM `result` WHERE `subjectNo` = (
SELECT `subjectNo` FROM `subject` WHERE `subjectName` = 'Logic Java'
)AND `examDate` = (
SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo` = (
SELECT `subjectNo` FROM `subject`
WHERE `subjectName` = 'Logic Java')
)AND `studentResult`>60)
AND `subjectNo` = (SELECT `subjectNo` FROM `subject`
WHERE `subjectName` = 'Logic Java')
AND `examDate`=(
SELECT MAX(`examDate`)FROM `result` WHERE `subjectNo`=(
SELECT `subjectNo` FROM `subject`
WHERE `subjectName` = 'Logic Java')
)
三.子查询的注意事项
- 1. 子查询语句可以嵌套在SQL语句中任何表达式出现的位置。
语法： SELECT * FROM (子查询) AS 表的别名;
A）如果子查询结果为单行单列，则可以不用指定列的别名。
B） 如果子查询结果为多行多列，必须为表指定别名。
- 2. 只出现在子查询中而没有出现在父查询中的表不能包含在输出列中。
常见错误： SELECT * FROM (SELECT * FROM ‘表名’)；错误
这个子查询的错误在于主查询语句的FROM子句是一个子查询语句，返回的是大于单行单列的数据，应该为子查询结果集指定别名。正确的代码如下：
语法: SELECT * FROM (SELECT * FROM `表名`) AS TEMP(别名)；