MySQL数据库11——子查询语句

嵌入一个SELECT的语句称为子查询语句。虽然内连接也可以代替子查询，但是内连接效率不高。而且我个人觉得子查询更好理解。

---

单值子查询

果子查询返回单值，则可以使用关系运算符，例如，等于（=）、不等于（<>）等，将其与主查询结合起来。下面通过例题举例说明。

 例 查询所有学生“心理学”的考试成绩，并以考试成绩降序进行排序。

分析：考试成绩在score表中，而该表中没有课名只有课号，所以首先必须从course表中查询“心理学”的课号，然后再从score表中根据查到的课号查询考试成绩。下面列出使用子查询完成查询任务的语句。

SELECT  s_id AS 学号, result1 AS 考试成绩 FROM    score
WHERE   c_id=(SELECT c_id  FROM course  WHERE course='心理学')
ORDER BY result1 DESC;

本例也可以使用内连接查询编写查询语句，具体语句如下所示。

SELECT  s_id AS 学号, result1 AS 考试成绩 FROM    score AS s,course AS c
WHERE   c.course='心理学'  AND s.c_id=c.ID
ORDER BY result1 DESC;

SELECT  s_id AS 学号, result1 AS 考试成绩
FROM    score AS s INNER JOIN course AS c ON s.c_id=c.ID
WHERE   c.course='心理学' 
ORDER BY result1 DESC;

 

---

子查询与聚合函数的配合使用

查询和聚合函数配合使用，其实是当前子查询的最大用途。因为，聚合函数通常都在SELECT子句字段列表处出现，而WHERE子句中又不能包含聚合函数，所以，通常是使用子查询获得聚合函数的返回值，然后将该返回值放到主查询中，最后再执行结合好后的查询语句。

例 查询出生日期最小（年龄最大）的学生的所有信息。

SELECT  * FROM   student
WHERE  birthday=(SELECT MIN(birthday) FROM student);

 

 

例 查询“心理学”考试成绩大于其考试成绩平均分的所有学生的学号、平时成绩和考试成绩。

SELECT s_id AS 学号, result2 AS 平时成绩, result1 AS 考试成绩 FROM   score
WHERE c_id=(SELECT ID FROM course WHERE course='心理学')
AND   result1>(SELECT AVG(result1) FROM  score WHERE c_id=(SELECT ID FROM course WHERE course='心理学'))
ORDER BY result1 DESC;

 将学生姓名也加上, 此时便需要将student表连接进来，

SELECT st.ID AS 学号, st.name AS 姓名,result2 AS 平时成绩, result1 AS 考试成绩
FROM  student AS st, score AS s, (SELECT ID FROM course  WHERE course='心理学') AS a
WHERE result1>(SELECT AVG(result1) FROM  score WHERE c_id=a.ID)
AND s.c_id=a.ID AND st.ID=s.s_id
ORDER BY result1 DESC;

 

---

列值子查询

查询所有课程类型为“必修”的学生的考试成绩，并按照课号进行升序排序。

如果使用子查询，本例的查询任务可以仅用一条语句完成，下面列出其具体语句。

SELECT  * FROM    score
WHERE  c_id IN (SELECT  ID FROM   course WHERE type='必修')
ORDER BY c_id, s_id;

 例 查询没有参加任何考试的学生的学号、姓名和所属院

SELECT  ID AS 学号, name AS 姓名, institute AS 所属院系 FROM    student
WHERE   ID NOT IN (SELECT DISTINCT s_id FROM     score)
ORDER BY ID;

 

---

相关子查询

相关子查询在子查询语句中调用了主查询用到的表字段。下面通过举例说明相关子查询的用法和其执行原理。

 例 从student表中，查询计科系所有学生的学号、姓名和考试总成绩，并按照考试总成绩降序排序。

SELECT ID AS 学号, name AS 姓名,(SELECT SUM(result1)FROM   score AS s WHERE s.s_id=st.ID) AS 考试总成绩
FROM   student AS st WHERE  institute='计算机学院'
ORDER BY 考试总成绩 DESC;

 如果使用表连接查询编写本例的查询语句，则其具体语句如下所示。

SELECT st.ID AS 学号,st.name AS 姓名,SUM(s.result1) AS 考试总成绩
FROM   student AS st,score AS s
WHERE  institute='计算机学院' AND    s.s_id=st.ID
GROUP BY st.ID, st.name
ORDER BY 考试总成绩 DESC;

运行结果同上。

若某个学生没有考试信息，那么就可能查不到。所以如果想查询所有计科系学生的考试信息，则应当使用左外连接，则其语句如下所示。

SELECT st.ID AS 学号,st.name AS 姓名,SUM(s.result1) AS 考试总成绩
FROM   student AS st LEFT JOIN score AS s ON s.s_id=st.ID
WHERE  institute='计算机学院'
GROUP BY st.ID, st.name
ORDER BY 考试总成绩 DESC;

运行结果同上。