本章我们将介绍 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 连接重写以前的子查询。
相关子查询
相关子查询是使用WHERE
子句中外部查询的值的子查询。 对于外部查询处理的每一行,子查询都会被评估一次。
mysql> SELECT Name FROM Cars WHERE Cost <
-> (SELECT AVG(Cost) FROM Cars);
+------------+
| Name |
+------------+
| Audi |
| Mercedes |
| Skoda |
| Volvo |
| Citroen |
| Hummer |
| Volkswagen |
+------------+
在上面的相关子查询中,我们返回价格低于表中所有汽车平均价格的所有汽车。
具有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 |
+------------+
在此查询中,我们返回“预订”表中没有条目的所有客户。 这两个 SQL 查询都是相关查询。