第四章 MySQL高级查询(二)

时间:2022-11-14 15:11:00

第四章 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. 1.       子查询语句可以嵌套在SQL语句中任何表达式出现的位置。

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

A)如果子查询结果为单行单列,则可以不用指定列的别名。

B) 如果子查询结果为多行多列,必须为表指定别名。

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

常见错误: SELECT * FROM (SELECT *  FROM  ‘表名’);错误

这个子查询的错误在于主查询语句的FROM子句是一个子查询语句,返回的是大于单行单列的数据,应该为子查询结果集指定别名。正确的代码如下:

语法:      SELECT * FROM (SELECT * FROM `表名`) AS TEMP(别名);