mysql高级查询 二_MySQL高级查询(二)

EXISTS 和NOT EXISTS子查询

EXISTS子查询

语法:

SELECT ……… FROM 表名 WHERE EXISTS (子查询);

例:

SELECT `studentNo` AS 学号,`studentResult` 成绩 FROM `result`

WHERE EXISTS(

/*查询LOgic Java最后一次考试成绩大于80的记录*/

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;

附注:EXISTS关键字后的参数是一个任意的子查询,如果该子查询有返回行,则EXISTS子查询         的结果为true ,此时再执行外层查询,反之查询结果没有返回行,则为false,外层查询将不         执行.

NOT EXISTS子查询

语法:

SELECT ……… FROM 表名 WHERE NOT EXISTS (子查询);

例:

SELECT AVG(studentResult)+5 AS 平均分 FROM result

WHERE NOT EXISTS(

/*查询Logic Java最后一个次考试成绩小于60 的记录*/

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 `sutdentResult`>60

) AND `examDate` = (

SELECT MAX(`examDate`) FROM `result` WHERE `subjectNo` = (

SELECT `subjectNo` FROM `subject` WHERE `subjectName` = `Logic Java`

)

)

附注:

NOT EXISTS 为EXISTS的取反操作,表示不存在.

当子查询没有返回行时,结果为true,反之亦然.

子查询注意事项

子查询语句可以嵌套在SQL语句中任何表达式出现的位置

嵌套在SELECT语句中的SELECT子句中的子查询如下:

语法: SELECT (子查询) FROM 表名;

3.嵌套在SELECT语句的FROM子句中的子查询语句如下:

语法: SELECT  *  FROM (子查询) AS 别名;

4 只要出现在子查询中而没有出现在父查询中的表不能包含在输出列.