數據庫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)

作用：提高查詢效率