以下例子均基于 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)