数据库 / 27 SQL 子查询1

SQL 子查询1

1. 定义

慕课解释:子查询,又称嵌套查询,是一种嵌套在其它 SQL 查询的 Where 字句中的查询。

2. 前言

本小节,我们将一起学习 SQL 子查询

SQL 子查询是一种复杂的查询方式,一般子查询语句都可以被分为主查询部分和子查询部分。子查询部分为主查询部分服务,常用于为主查询返回其所需数据,或者进一步筛选主查询数据。

子查询的知识点其实不多,学习它的难点在于如何将 SQL 查询使用熟练,然后灵活搭配,本小节我们将聚焦在子查询的基本使用上,而把子查询中的两个特殊语法 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);

3. 语法

子查询的语法其实与普通查询的语法没有什么区别,只不过多了相应的子查询部分。

以 Select 为例,语法如下:

SELECT [col] FROM [table_name]
WHERE [col] [operator]
(
  SELECT [col] FROM [table_name];
  WHERE [col] [operator]
 );

其中table_name表示数据表名称,col表示字段名,operator表示字段操作。

子查询可灵活用于 Insert、Select、Update 和 Delete 指令中,我们没有列举出所有的语法,它们在子查询的部分其实是一致的,区别在于主操作部分。

子查询虽然很灵活,但是也有一定的限制,它必须满足以下几个规则:

  1. 子查询必须在括号()内。
  2. 子查询中不能使用 Order By,主查询可以使用。
  3. 子查询不能使用在聚合函数中。
  4. Between 指令不能与子查询一起使用,但可使用在子查询内部。
  5. 子查询若返回一条记录,则只能使用单值运算符,如 > ,若返回多条记录需使用多值运算符,如 In。
  6. 若子查询返回多条记录,且使用 ANY 或 ALL 特殊语法,则可使用单值比较符,我们将在下小节介绍。

4. Select 中的子查询

在 Select 中使用子查询是最为常见的一种子查询方式,子查询让我们可以更加灵活查询数据。

4.1 例1 Select 搭配子查询

请书写 SQL 语句,获取imooc_user表中小于最大年龄的用户名。

分析:

从题干中得出,需要获取小于最大年龄的用户名,所以第一步需要找到最大年龄,然后通过最大年龄比较得出年龄小于它的用户名;使用子查询可以快速的办到这一点,子查询得到最大年龄,主查询部分以最大年龄作为筛选条件从而得到结果。

语句:

整理可得语句如下:

SELECT username FROM imooc_user 
WHERE age <
(SELECT age FROM imooc_user ORDER BY age DESC LIMIT 1);

结果如下:

+----------+
| username |
+----------+
| peter    |
| pedro    |
| mike     |
| tom      |
+----------+

imooc_user 表中,jerry 年龄最大,28 岁,结果中显示了除他以外的所有人, 因此结果正确。

5. Insert 中的子查询

子查询还可以与 Insert 搭配使用,Insert 可将子查询得到的数据插入到其它表中。

5.1 例2 Insert 搭配子查询

请书写 SQL 语句,获取imooc_user表中小于最大年龄的用户名,并将用户名插入到 username_copy表中。

分析:

同上,另外的我们还需要新建 username_copy 表,并向其中插入数据。

语句:

整理可得语句如下:

CREATE TABLE username_copy(username varchar(20));
INSERT INTO username_copy SELECT username FROM imooc_user 
WHERE age <
(SELECT age FROM imooc_user ORDER BY age DESC LIMIT 1);

username_copy 表信息如下:

+----------+
| username |
+----------+
| peter    |
| pedro    |
| mike     |
| tom      |
+----------+

6. Update 中的子查询

子查询可搭配 Update,一次完成多条记录的更新,当然也可只更新一条记录。

6.1 例3 Update 搭配子查询

请书写 SQL 语句,将imooc_user表中年龄大于 25 岁的用户积分增加 100 。

分析:

由题干可知,我们可分两步完成,第一步从子查询中获取年龄大于 25 岁的用户 id,然后在主操作语句部分更新他们的积分。

语句:

整理可得语句如下:

UPDATE imooc_user SET score = score + 100 
WHERE id IN (SELECT id FROM imooc_user WHERE age > 25);

更新后,用户积分如下:

+----------+-------+
| username | score |
+----------+-------+
| peter    | 100   |
| pedro    | 200   |
| jerry    | 600   |
| mike     | 300   |
| tom      | 1100  |
+----------+-------+

如果你使用 MySQL,那么上面语句会无法执行,因为 MySQL 不支持在同一张表中查询又更新,因此我们可以使用如下的方式来改写 SQL,使 MySQL 来支持:

UPDATE imooc_user SET score = score + 100 
WHERE id IN ( 
SELECT a.id FROM
(SELECT id FROM imooc_user WHERE age > 25) as a
);

7. Delete 中的子查询

子查询可与 Delete 搭配使用来更加方便删除数据。

7.1 例4 Delete 搭配子查询

请书写 SQL 语句,删除imooc_user表中积分大于 500 的用户 。

分析:

我们仍然分两步完成,第一步子查询获取积分大于 500 的用户 id,然后在主操作删除他们。

语句:

整理可得语句如下:

DELETE FROM imooc_user 
WHERE id IN (SELECT id FROM imooc_user WHERE score > 500);

删除成功后,imooc_user 表信息如下:

+----------+-------+
| username | score |
+----------+-------+
| peter    | 100   |
| pedro    | 200   |
| mike     | 300   |
+----------+-------+

同样的,MySQL 不支持在一张表中同时删除和查询,因此我们改写一下:

DELETE FROM imooc_user 
WHERE id IN ( 
SELECT a.id FROM
(SELECT id FROM imooc_user WHERE score > 500) as a
);

8. 小结

  • 子查询的难点在于如何熟练搭配和使用查询,这是一个积少成多的过程,因此请务必多多练习。
  • 子查询也有诸多限制,而且性能不高,因此如果可以用一次查询解决的问题尽量不要使用子查询。