1. 引入

语法：

select 语句 where 条件 (select ... from 表)

1. 外面的查询称为夫查询，括号中的查询称为子查询
2. 子查询为父查询提供查询条件

1.1 例题

1. 查找笔试80分的学生信息

mysql> mysqlt * from stuinfo where stuno=(select stuno from stumarks where writtenexam=80);
+--------+-----------+--------+--------+---------+------------+
| stuNo  | stuName   | stuSex | stuAge | stuSeat | stuAddress |
+--------+-----------+--------+--------+---------+------------+
| s25303 | 李斯文    | 女     |     22 |       2 | 北京       |
+--------+-----------+--------+--------+---------+------------+

2. 查找笔试最高分的学生信息

方法一：
mysql> select * from stuinfo where stuno=(select stuno from stumarks order by writtenexam desc limit 1);
+--------+-----------+--------+--------+---------+------------+
| stuNo  | stuName   | stuSex | stuAge | stuSeat | stuAddress |
+--------+-----------+--------+--------+---------+------------+
| s25303 | 李斯文    | 女     |     22 |       2 | 北京       |
+--------+-----------+--------+--------+---------+------------+
1 row in set (0.00 sec)

方法二：
mysql> select * from stuinfo where stuno=(select stuno from stumarks where writtenexam=(select max(writtenexam) from stumarks));
+--------+-----------+--------+--------+---------+------------+
| stuNo  | stuName   | stuSex | stuAge | stuSeat | stuAddress |
+--------+-----------+--------+--------+---------+------------+
| s25303 | 李斯文    | 女     |     22 |       2 | 北京       |
+--------+-----------+--------+--------+---------+------------+
1 row in set (0.01 sec)

2. 子查询【in | not in】

用于子查询的返回结果有多个值时。

1. 查找笔试成绩及格的同学

mysql> select * from stuinfo where stuno in (select stuno from stumarks where writtenexam>=60);
+--------+--------------+--------+--------+---------+------------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress |
+--------+--------------+--------+--------+---------+------------+
| s25303 | 李斯文       | 女     |     22 |       2 | 北京       |
| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       |
| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       |
+--------+--------------+--------+--------+---------+------------+

2. 查询不及格的同学(不包括成绩为NULL的同学)

mysql> select * from stuinfo where stuno in (select stuno from stumarks where writtenexam<60);
+--------+--------------+--------+--------+---------+------------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress |
+--------+--------------+--------+--------+---------+------------+
| s25302 | 李文才       | 男     |     31 |       3 | 上海       |
| s25318 | 争青小子     | 男     |     26 |       6 | 天津       |
+--------+--------------+--------+--------+---------+------------+
2 rows in set (0.01 sec)

3. 查询没有通过的学生(不及格和缺考的同学)

mysql> select * from stuinfo where stuno not in (select stuno from stumarks where writtenexam>=60);
+--------+--------------+--------+--------+---------+------------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress |
+--------+--------------+--------+--------+---------+------------+
| s25302 | 李文才       | 男     |     31 |       3 | 上海       |
| s25305 | 诸葛丽丽     | 女     |     23 |       7 | 河南       |
| s25318 | 争青小子     | 男     |     26 |       6 | 天津       |
| s25319 | 梅超风       | 女     |     23 |       5 | 河北       |
+--------+--------------+--------+--------+---------+------------+

3. exists 和 not exists

1. 如果有人笔试超过80分就显示所有学生

mysql> select * from stuinfo where exists (select * from stumarks where writtenexam>=80);                          +--------+--------------+--------+--------+---------+------------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress |
+--------+--------------+--------+--------+---------+------------+
| s25301 | 张秋丽       | 男     |     18 |       1 | 北京       |
| s25302 | 李文才       | 男     |     31 |       3 | 上海       |
| s25303 | 李斯文       | 女     |     22 |       2 | 北京       |
| s25304 | 欧阳俊雄     | 男     |     28 |       4 | 天津       |
| s25305 | 诸葛丽丽     | 女     |     23 |       7 | 河南       |
| s25318 | 争青小子     | 男     |     26 |       6 | 天津       |
| s25319 | 梅超风       | 女     |     23 |       5 | 河北       |
+--------+--------------+--------+--------+---------+------------+

2. 如果没有人超过80分就显示所有学生

mysql> select * from stuinfo where not exists (select * from stumarks where writtenexam>=80);
Empty set (0.00 sec)

4. 子查询分类

1. 标量子查询：子查询返回结果就一个（使用=）

2. 列子查询：子查询返回结果是一个列表（使用in | not in）

3. 行子查询：子查询返回的结果是一行（使用in | not in）
   例题：查询语文成绩最高的男生和女生

mysql> select * from stu where (stusex,ch) in (select stusex,max(ch) from stu group by stusex);
+--------+--------------+--------+--------+---------+------------+------+------+
| stuNo  | stuName      | stuSex | stuAge | stuSeat | stuAddress | ch   | math |
+--------+--------------+--------+--------+---------+------------+------+------+
| s25318 | 争青小子     | 男     |     26 |       6 | 天津       |   86 |   92 |
| s25321 | Tabm         | 女     |     23 |       9 | 河北       |   88 |   77 |
+--------+--------------+--------+--------+---------+------------+------+------+

4. 表子查询：子查询返回的结果是一个表
   例题：查询语文成绩最高的男生和女生

mysql> select stuname,stusex,ch from (select * from stu order by ch desc) as t group by stusex;
+----------+--------+------+
| stuname  | stusex | ch   |
+----------+--------+------+
| Tabm     | 女      |   88 |
| 争青小子        | 男       |   86 |
+----------+--------+------+

脚下留心：

1. from后面是一个表，如果子查询的结果当成表来看，必须将子查询的结果取别名。
2. 如果开启了sql_mode=only_full_group_by模式，查询字段不是分组字段+聚合函数会报错。