SQL 子查询2
1. 前言
在上一小节中,我们一起学习了 SQL 子查询的语法,并介绍了 SQL 子查询是如何在 Insert、Select、Update 和 Delete 中使用的。
本小节,我们将继续学习子查询中的两个特殊关键字语法——ANY
和ALL
。
本小节测试数据如下,请先在数据库中执行:
DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
id int PRIMARY KEY,
username varchar(20),
age int,
score int
);
INSERT INTO imooc_user(id,username,age,score) VALUES (1,'peter', 18, 100),
(2,'pedro', 24, 200),(3,'jerry', 28, 500),
(4,'mike', 12, 300),(5,'tom', 27, 1000);
DROP TABLE IF EXISTS imooc_user_score_rank;
CREATE TABLE imooc_user_score_rank
(
id int PRIMARY KEY,
srank int,
score int
);
INSERT INTO imooc_user_score_rank(id,srank,score) VALUES (1,1,10000),(2,2,8000),(3,9,700);
2. ANY 和 ALL
ANY 和 ALL 主要解决,子查询返回多条记录而主查询需要使用单值比较符的问题。
在上一节中,我们谈到如果子查询返回多条记录,那么主查询部分只能使用多值比较符,如 In,对于单值比较符,如 > ,则无法工作。
有了 ANY 和 ALL,就可以在一定程度上解决这个问题,针对子查询返回的结果,ANY 和 ALL 的作用分别如下:
作用 | 说明 | |
---|---|---|
ANY | 子查询结果集任一条记录满足比较符即可 | 如 > 比较符,主查询部分大于任意一条子查询记录即可 |
ALL | 子查询结果集所有记录满足比较符才可 | 如 > 比较符,主查询部分须大于所有子查询记录才可 |
简单的理解,ALL 和 ANY 让单值比较符跟子查询结果集中的每一条记录都做比较,ALL 必须满足所有比较才为真,而 ANY 只需满足任意一个比较则为真。
3. 实践
我们还是以例子来更加详细的说明 ANY 和 ALL 的用法。
3.1 例1 子查询使用 ANY
请书写 SQL 语句,获取imooc_user
表中分数大于imooc_user_score_rank
表中任意一个分数的用户。
分析:
从题干中得出,imooc_user 表中的 score 对应 imooc_user_score_rank 中的 score,我们使用 ANY 子查询,第一步从子查询中获取 imooc_user_score_rank 表中的所有分数项,第二步在主查询中使用 ANY 与子查询结果比较。
语句:
整理可得语句如下:
SELECT username FROM imooc_user
WHERE score > ANY(SELECT score FROM imooc_user_score_rank);
结果如下:
+----------+
| username |
+----------+
| tom |
+----------+
tom 的分数为 1000, 大于 imooc_user_score_rank 表中的最小值 700,而其它的用户均小于 imooc_user_score_rank 表中任意分数。
3.2 例2 子查询使用 ALL
请书写 SQL 语句,获取imooc_user
表中分数大于imooc_user_score_rank
表中所有分数的用户。
分析:
同上,只需将 ANY 换成 ALL 即可。
语句:
整理可得语句如下:
SELECT username FROM imooc_user
WHERE score > ALL(SELECT score FROM imooc_user_score_rank);
结果如下:
+----------+
| username |
+----------+
在 imooc_user_score_rank 表中,最大的分数是 10000,而 imooc_user 表中用户分数均小于 10000,因此结果为空。
4. 小结
- ALL 和 ANY 的使用其实比较少,多加练习理解即可。
- 子查询重在积少成多和融汇贯通,请务必多加练习形成自己的知识网。
- 还没有人评论,欢迎说说您的想法!