MySQL 子查询

最后修改于 2023 年 1 月 10 日

在 MySQL 教程的这一部分，我们将介绍 MySQL 中的子查询。

一个子查询是一个查询中的查询。它也被称为内部查询或嵌套查询。子查询可以用于任何允许表达式的地方。它是一个用括号括起来的查询表达式。子查询可以与 SELECT, INSERT, UPDATE 或 DELETE 语句一起使用。

执行 SQL 任务的方法不止一种。许多子查询可以用 SQL 连接来代替。SQL 连接通常更快。

在本章中，我们将使用以下表格

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Cost   |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+

来自 Cars 表的数据。

mysql> SELECT * FROM Customers; SELECT * FROM Reservations;
+------------+-------------+
| CustomerId | Name        |
+------------+-------------+
|          1 | Paul Novak  |
|          2 | Terry Neils |
|          3 | Jack Fonda  |
|          4 | Tom Willis  |
+------------+-------------+
4 rows in set (0.00 sec)

+----+------------+------------+
| Id | CustomerId | Day        |
+----+------------+------------+
|  1 |          1 | 2009-11-22 |
|  2 |          2 | 2009-11-28 |
|  3 |          2 | 2009-11-29 |
|  4 |          1 | 2009-11-29 |
|  5 |          3 | 2009-12-02 |
+----+------------+------------+
5 rows in set (0.00 sec)

我们重述一下我们在 Customers 和 Reservations 表中拥有的内容。子查询通常在具有某种关系的表上执行。

带有 INSERT 语句的子查询

我们想要创建一个 Cars 表的副本。到另一个名为 Cars2 的表中。我们将为此创建一个子查询。

mysql> CREATE TABLE Cars2(Id INT NOT NULL PRIMARY KEY, 
    -> Name VARCHAR(50) NOT NULL, Cost INT NOT NULL);

我们创建一个新的 Cars2 表，该表具有与 Cars 表相同的列和数据类型。要了解表的创建方式，我们可以使用 SHOW CREATE TABLE 语句。

mysql> INSERT INTO Cars2 SELECT * FROM Cars;

这是一个简单的子查询。我们将 Cars 表中的所有行插入到 Cars2 表中。

mysql> SELECT * FROM Cars2;
+----+------------+--------+
| Id | Name       | Cost   |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+

数据被复制到一个新的 Cars2 表中。

标量子查询

标量子查询返回一个单一的值。

mysql> SELECT Name FROM Customers WHERE 
    -> CustomerId=(SELECT CustomerId FROM Reservations WHERE Id=5);
+------------+
| Name       |
+------------+
| Jack Fonda |
+------------+

括号中的查询是子查询。它返回一个单一的标量值。然后将返回的值用于外部查询。在这个标量子查询中，我们从 Customers 表中返回客户的姓名，其预订在 Reservations 表中的 Id 等于 5。

表子查询

表子查询返回一个零行或多行的结果表。

mysql> SELECT Name FROM Customers WHERE CustomerId IN    
    -> (SELECT DISTINCT CustomerId FROM Reservations);
+-------------+
| Name        |
+-------------+
| Paul Novak  |
| Terry Neils |
| Jack Fonda  |
+-------------+

上面的查询返回了进行了一些预订的客户的姓名。内部查询从 Reservations 表中返回客户 ID。我们使用 IN 谓词来选择那些客户的姓名，这些客户的 CustomerId 来自内部的 select 查询。

mysql> SELECT DISTINCT Name FROM Customers JOIN Reservations
    -> ON Customers.CustomerId=Reservations.CustomerId;
+-------------+
| Name        |
+-------------+
| Paul Novak  |
| Terry Neils |
| Jack Fonda  |
+-------------+

之前的子查询可以使用 SQL 连接重写。

关联子查询

关联子查询是一个在其 WHERE 子句中使用来自外部查询的值的子查询。该子查询会针对外部查询处理的每一行进行一次评估。

mysql> SELECT Name FROM Cars WHERE Cost <
    -> (SELECT AVG(Cost) FROM Cars);
+------------+
| Name       |
+------------+
| Audi       |
| Mercedes   |
| Skoda      |
| Volvo      |
| Citroen    |
| Hummer     |
| Volkswagen |
+------------+

在上面的关联子查询中，我们返回了所有价格低于表中所有汽车的平均价格的汽车。

带有 EXISTS, NOT EXISTS 的子查询

如果子查询返回任何值，则谓词 EXISTS 返回 TRUE，NOT EXISTS 返回 FALSE。

mysql> SELECT Name FROM Customers WHERE EXISTS
    -> (SELECT * FROM Reservations WHERE
    -> Customers.CustomerId=Reservations.CustomerId);
+-------------+
| Name        |
+-------------+
| Paul Novak  |
| Terry Neils |
| Jack Fonda  |
+-------------+

在上面的 SQL 语句中，我们选择了所有在 Reservations 表中有条目的客户的姓名。

mysql> SELECT Name FROM Customers WHERE NOT EXISTS    
    -> (SELECT * FROM Reservations WHERE 
    -> Customers.CustomerId=Reservations.CustomerId);
+------------+
| Name       |
+------------+
| Tom Willis |
+------------+

在此查询中，我们返回了所有在 Reservations 表中没有条目的客户。这两个 SQL 查询都是关联查询。