Msql5子查询

以下例子均基于 sql文件 GitHub 链接 表。

前言

子查询：无法直接从数据表中得到查询结果，需要从查询结果集中再次进行查询，才能得到想要的结果。这个就是子查询！根据子查询是否执行多此，可将子查询分为 关联子查询 和 非关联子查询。

我的理解是这样的，我们可以抽象嵌套子查询语句为： 主查询 + 子查询 ，以下是我认为的抽象算法。

• 非关联子查询，

获取子查询结果集；
for 记录  in 子查询结果集：
	执行主查询语句；

• 关联子查询：

获去主查询结果集：
for 记录 in 主查询结果集：
	执行子查询语句；

根据以上，我们可以了解到，

• 非关联子查询，可以利用主查询语句中的索引；
• 关联子查询，可以用到子查询中的索引；

非关联子查询

基础

题一：获取身高最高的球员信息

mysql> SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player);
+---------------+--------+
| player_name   | height |
+---------------+--------+
| 索恩-马克     |   2.16 |
+---------------+--------+
1 row in set (0.00 sec)

集合比较子查询

集合比较子查询的作用是与另一个查询结果集进行比较，我们可以在子查询中使用 IN、ANY、ALL 和 SOME 操作符，介绍如下：

• IN：判断是否在集合中
• ANY：和结果集中任何值比较
• ALL：和结果集中所有值比较。
• SOME：是ANY别名，一般用ANY。

题四：获取出场的球员有哪些，利用IN获取

mysql> SELECT player_id, team_id, player_name FROM player WHERE player_id in (SELECT player_id FROM player_score);

题五：获取球员表中，比team_id 为1002队中任何一个球员高的球员信息，利用ANY获取（比1002中，其中一个高就行）

mysql> SELECT player_id, player_name, height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002);

**题五：获取球员表中，比team_id 为1002队中所有球员高的球员信息 **，利用ALL获取

mysql> SELECT player_id, player_name, height FROM player WHERE height > ALL (SELECT height FROM player WHERE team_id = 1002);
+-----------+---------------+--------+
| player_id | player_name   | height |
+-----------+---------------+--------+
|     10004 | 索恩-马克     |   2.16 |
+-----------+---------------+--------+
1 row in set (0.00 sec)

关联子查询

基础

题二：每个球队中大于平均身高的球员有哪些

mysql> SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id);
+------------------------------------+--------+---------+
| player_name                        | height | team_id |
+------------------------------------+--------+---------+
| 安德烈-德拉蒙德                    |   2.11 |    1001 |
| 索恩-马克                          |   2.16 |    1001 |
| 扎扎-帕楚里亚                      |   2.11 |    1001 |
| 乔恩-洛伊尔                        |   2.08 |    1001 |
| 布雷克-格里芬                      |   2.08 |    1001 |
| 雷吉-巴洛克                        |   2.01 |    1001 |
| 斯坦利-约翰逊                      |   2.01 |    1001 |
| 亨利-埃伦森                        |   2.11 |    1001 |
| 斯维亚托斯拉夫-米凯卢克            |   2.03 |    1001 |
| 博扬-博格达诺维奇                  |   2.03 |    1002 |
| 多曼塔斯-萨博尼斯                  |   2.11 |    1002 |
| 迈尔斯-特纳                        |   2.11 |    1002 |
| 赛迪斯-杨                          |   2.03 |    1002 |
| 道格-迈克德莫特                    |   2.03 |    1002 |
| TJ-利夫                            |   2.08 |    1002 |
| 凯尔-奥奎因                        |   2.08 |    1002 |
| 阿利兹-约翰逊                      |   2.06 |    1002 |
| 伊凯·阿尼博古                      |   2.08 |    1002 |
+------------------------------------+--------+---------+
18 rows in set (0.00 sec)

EXISTS关联子查询

题三：获取出场的球员有哪些，利用EXISTS获取

mysql> SELECT player_id, team_id, player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id);
+-----------+---------+---------------------------+
| player_id | team_id | player_name               |
+-----------+---------+---------------------------+
|     10001 |    1001 | 韦恩-艾灵顿               |
|     10002 |    1001 | 雷吉-杰克逊               |
|     10003 |    1001 | 安德烈-德拉蒙德           |
|     10004 |    1001 | 索恩-马克                 |
|     10005 |    1001 | 布鲁斯-布朗               |
|     10006 |    1001 | 兰斯顿-加洛韦             |
|     10007 |    1001 | 格伦-罗宾逊三世           |
|     10008 |    1001 | 伊斯梅尔-史密斯           |
|     10009 |    1001 | 扎扎-帕楚里亚             |
|     10010 |    1001 | 乔恩-洛伊尔               |
|     10022 |    1002 | 博扬-博格达诺维奇         |
|     10025 |    1002 | 赛迪斯-杨                 |
|     10024 |    1002 | 迈尔斯-特纳               |
|     10028 |    1002 | 泰瑞克-埃文斯             |
|     10030 |    1002 | 科里-约瑟夫               |
|     10023 |    1002 | 多曼塔斯-萨博尼斯         |
|     10029 |    1002 | 道格-迈克德莫特           |
|     10031 |    1002 | 阿龙-霍勒迪               |
|     10032 |    1002 | TJ-利夫                   |
+-----------+---------+---------------------------+
19 rows in set (0.00 sec)

EXISTS与IN区别

题四：获取出场的球员有哪些，利用IN获取

mysql> SELECT player_id, team_id, player_name FROM player WHERE player_id in (SELECT player_id FROM player_score);

利用EXISTS获取

mysql> SELECT player_id, team_id, player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id);

模式抽象： 将IN和EXISTS两种写法抽象为以下：

SELECT * FROM A WHERE cc IN (SELECT cc FROM B)

SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)

A比B表小，EXISTS子查询效率会更高。

A比B表大，IN子查询效率会更高。

A，B大小相当，两者效率相当。

主要原因在于对索引的使用。任何情况，只要大表的索引被使用，就可以使效率更高。解释如下：

• 对于IN，我们首先执行SELECT cc FROM B获取子查询结果集，再遍历子查询结果集的每一条记录，执行主查询语句，因此这一步，可以用到A的索引，如果A比较大，那么查询效率更高。
• 对于EXISTS，是关联查询，我们首先执行主查询语句，然后将该结果集的每一条记录，判断EXISTS是否为true，可以用到B的索引，如果B比较大，那么查询效率更高。

关联子查询作计算字段

题六：查询每个球队的球员数

mysql> SELECT team_name, (SELECT count(*) FROM player WHERE player.team_id = team.team_id) AS player_num FROM team;
+-----------------------+------------+
| team_name             | player_num |
+-----------------------+------------+
| 底特律活塞            |         20 |
| 印第安纳步行者        |         17 |
| 亚特兰大老鹰          |          0 |
+-----------------------+------------+
3 rows in set (0.01 sec)

参考

1. 深入理解MYSQL子查询IN的执行和优化
2. 区分关联子查询和非关联子查询
3. 简单易懂教你学会SQL关联子查询
4. SQL必知必会从入门到数据实战