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的那些客户名称。

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

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

具有`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 |
+------------+