数据库MySQL-子查询

• 2020 年 3 月 27 日
• 筆記

1.3 子查询

语法：select * from 表1 where （子查询）

外面的查询称为父查询

子查询为父查询提供查询条件

1.3.1 标量子查询

特点：子查询返回的值是一个

-- 查找笔试成绩是80的学生  mysql> select * from stuinfo where stuno=(select stuno from stumarks where writtenexam=80);  +--------+---------+--------+--------+---------+------------+  | 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 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.00 sec)

1.3.2 列子查询

特点： 子查询返回的结果是一列

如果子查询的结果返回多条记录，不能使用等于，用in或not in

-- 查找及格的同学  mysql> select * from stuinfo where stuno in (select stuno from stumarks where writtenexam>=60);  +--------+----------+--------+--------+---------+------------+  | stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress |  +--------+----------+--------+--------+---------+------------+  | s25301 | 张秋丽         | 男       |     18 |       1 | 北京           |  | s25303 | 李斯文        | 女      |     22 |       2 | 北京           |  | s25304 | 欧阳俊雄        | 男       |     28 |       4 | 天津           |  +--------+----------+--------+--------+---------+------------+  3 rows in set (0.00 sec)    -- 查询不及格的同学  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.00 sec)    -- 查询需要补考的学生  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 | 河北          |  +--------+----------+--------+--------+---------+------------+  4 rows in set (0.00 sec)

1.3.3 行子查询

特点：子查询返回的结果是多个字段组成

-- 查找语文成绩最高的男生和女生    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 |  +--------+----------+--------+--------+---------+------------+------+------+  2 rows in set (0.00 sec)

1.3.4 表子查询

特点：将子查询的结果作为表

-- 查找语文成绩最高的男生和女生    mysql> select * from (select * from stu order by ch desc) t group by stusex;  +--------+----------+--------+--------+---------+------------+------+------+  | stuNo  | stuName  | stuSex | stuAge | stuSeat | stuAddress | ch   | math |  +--------+----------+--------+--------+---------+------------+------+------+  | s25321 | Tabm     | 女      |     23 |       9 | 河北          |   88 |   77 |  | s25318 | 争青小子        | 男       |     26 |       6 | 天津           |   86 |   92 |  +--------+----------+--------+--------+---------+------------+------+------+  2 rows in set (0.00 sec)

注意：from后面跟的是数据源，如果将子查询当成表来看， 必须给结果集取别名。

1.3.5 exists子查询

-- 如果笔试成绩有人超过80人，就显示所有学生信息  mysql> select * from stuinfo where exists (select * from stumarks where writtenexam>=80);    -- 没有超过80的学生，就显示所有学生信息  mysql> select * from stuinfo where not exists (select * from stumarks where writtenexam>=80);  Empty set (0.00 sec)

作用：提高查询效率