在本教程中，您将学习如何使用MySQL子查询编写复杂的查询语句并解释相关的子查询概念。
MySQL子查询是嵌套在另一个查询(如SELECT，INSERT，UPDATE或DELETE)中的查询。 另外，MySQL子查询可以嵌套在另一个子查询中。
MySQL子查询称为内部查询，而包含子查询的查询称为外部查询。 子查询可以在使用表达式的任何地方使用，并且必须在括号中关闭。
以下查询返回在位于美国(USA)的办公室工作的员工。
SELECT
lastName, firstName
FROM
employees
WHERE
officeCode IN (SELECT
officeCode
FROM
offices
WHERE
country = 'USA');
在这个例子中：
子查询返回位于美国的办公室的所有办公室代码。
外部查询选择在办公室代码在子查询返回的结果集中的办公室中工作的员工的姓氏和名字。
当查询执行时，首先执行子查询并返回一个结果集。然后，将此结果集作为外部查询的输入。
1. MySQL子查询在WHERE子句中
我们将使用示例数据(yiibaidb)库中的payments表进行演示。payments表的表结构如下 -
mysql> desc payments;
+----------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| customerNumber | int(11) | NO | PRI | NULL | |
| checkNumber | varchar(50) | NO | PRI | NULL | |
| paymentDate | date | NO | | NULL | |
| amount | decimal(10,2) | NO | | NULL | |
+----------------+---------------+------+-----+---------+-------+
4 rows in set
1.1 MySQL子查询与比较运算符
可以使用比较运算符，例如=，>，
例如，以下查询返回最大付款额的客户。
SELECT
customerNumber, checkNumber, amount
FROM
payments
WHERE
amount = (SELECT
MAX(amount)
FROM
payments);
执行上面查询语句，得到以下结果 -
+----------------+-------------+-----------+
| customerNumber | checkNumber | amount |
+----------------+-------------+-----------+
| 141 | JE105477 | 120166.58 |
+----------------+-------------+-----------+
除等式运算符之外，还可以使用大于(>)，小于(
例如，可以使用子查询找到其付款大于平均付款的客户。 首先，使用子查询来计算使用AVG聚合函数的平均付款。 然后，在外部查询中，查询大于子查询返回的平均付款的付款。参考以下查询语句的写法 -
SELECT
customerNumber, checkNumber, amount
FROM
payments
WHERE
amount > (SELECT
AVG(amount)
FROM
payments);
执行上面查询语句，得到以下结果 -
+----------------+-------------+-----------+
| customerNumber | checkNumber | amount |
+----------------+-------------+-----------+
| 112 | HQ55022 | 32641.98 |
| 112 | ND748579 | 33347.88 |
| 114 | GG31455 | 45864.03 |
| 114 | MA765515 | 82261.22 |
| 114 | NR27552 | 44894.74 |
| 119 | LN373447 | 47924.19 |
| 119 | NG94694 | 49523.67 |
| 省略部分数据 ......................... |
| 484 | JH546765 | 47513.19 |
| 486 | HS86661 | 45994.07 |
| 495 | BH167026 | 59265.14 |
| 496 | MN89921 | 52166 |
+----------------+-------------+-----------+
134 rows in set
1.2. 具有IN和NOT IN运算符的MySQL子查询
如果子查询返回多个值，则可以在WHERE子句中使用IN或NOT IN运算符等其他运算符。
查看以下客户和订单表的ER结构图 -
例如，可以使用带有NOT IN运算符的子查询来查找没有下过任何订单的客户，如下所示：
SELECT
customerName
FROM
customers
WHERE
customerNumber NOT IN (SELECT DISTINCT
customerNumber
FROM
orders);
执行上面查询，得到以下结果 -
+--------------------------------+
| customerName |
+--------------------------------+
| Havel & Zbyszek Co |
| American Souvenirs Inc |
| Porto Imports Co. |
| Asian Shopping Network, Co |
| Natrlich Autos |
| ANG Resellers |
| Messner Shopping Network |
| Franken Gifts, Co |
| BG&E Collectables |
| Schuyler Imports |
| Der Hund Imports |
| Cramer Spezialitten, Ltd |
| Asian Treasures, Inc. |
| SAR Distributors, Co |
| Kommission Auto |
| Lisboa Souveniers, Inc |
| Precious Collectables |
| Stuttgart Collectable Exchange |
| Feuer Online Stores, Inc |
| Warburg Exchange |
| Anton Designs, Ltd. |
| Mit Vergngen & Co. |
| Kremlin Collectables, Co. |
| Raanan Stores, Inc |
+--------------------------------+
24 rows in set
3. FROM子句中的MySQL子查询
在FROM子句中使用子查询时，从子查询返回的结果集将用作临时表。 该表称为派生表或物化子查询。
以下子查询将查找订单表中的最大，最小和平均数：
SELECT
MAX(items), MIN(items), FLOOR(AVG(items))
FROM
(SELECT
orderNumber, COUNT(orderNumber) AS items
FROM
orderdetails
GROUP BY orderNumber) AS lineitems;
执行上面查询，得到以下结果 -
+------------+------------+-------------------+
| MAX(items) | MIN(items) | FLOOR(AVG(items)) |
+------------+------------+-------------------+
| 18 | 1 | 9 |
+------------+------------+-------------------+
1 row in set
4. MySQL相关子查询
在前面的例子中，注意到一个子查询是独立的。 这意味着您可以将子查询作为独立查询执行，例如：
SELECT
orderNumber,
COUNT(orderNumber) AS items
FROM
orderdetails
GROUP BY orderNumber;
与独立子查询不同，相关子查询是使用外部查询中的数据的子查询。 换句话说，相关的子查询取决于外部查询。 对外部查询中的每一行对相关子查询进行一次评估。
在以下查询中，我们查询选择购买价格高于每个产品线中的产品的平均购买价格的产品。
SELECT
productname,
buyprice
FROM
products p1
WHERE
buyprice > (SELECT
AVG(buyprice)
FROM
products
WHERE
productline = p1.productline);
执行上面查询，得到以下结果 -
+-----------------------------------------+----------+
| productname | buyprice |
+-----------------------------------------+----------+
| 1952 Alpine Renault 1300 | 98.58 |
| 1996 Moto Guzzi 1100i | 68.99 |
| 2003 Harley-Davidson Eagle Drag Bike | 91.02 |
| 1972 Alfa Romeo GTA | 85.68 |
| 1962 LanciaA Delta 16V | 103.42 |
| 1968 Ford Mustang | 95.34 |
| 2001 Ferrari Enzo | 95.59 |
| ************ 此处省略了一大波数据 ****************** |
| American Airlines: B767-300 | 51.15 |
| America West Airlines B757-200 | 68.8 |
| ATA: B757-300 | 59.33 |
| F/A 18 Hornet 1/72 | 54.4 |
| The Titanic | 51.09 |
| The Queen Mary | 53.63 |
+-----------------------------------------+----------+
55 rows in set
对于变化的每一行产品线，每个产品线都会执行内部查询。 因此，平均购买价格也会改变。 外部查询仅筛选购买价格大于子查询中每个产品线的平均购买价格的产品。
5. MySQL子查询与EXISTS和NOT EXISTS
当子查询与EXISTS或NOT EXISTS运算符一起使用时，子查询返回一个布尔值为TRUE或FALSE的值。以下查询说明了与EXISTS运算符一起使用的子查询：
SELECT
*
FROM
table_name
WHERE
EXISTS( subquery );
在上面的查询中，如果子查询(subquery)有返回任何行，则EXISTS子查询返回TRUE，否则返回FALSE。
通常在相关子查询中使用EXISTS和NOT EXISTS。
下面我们来看看示例数据库(yiibaidb)中的orders和orderDetails表：
以下查询选择总额大于60000的销售订单。
SELECT
orderNumber,
SUM(priceEach * quantityOrdered) total
FROM
orderdetails
INNER JOIN
orders USING (orderNumber)
GROUP BY orderNumber
HAVING SUM(priceEach * quantityOrdered) > 60000;
执行上面查询，得到以下结果 -
+-------------+----------+
| orderNumber | total |
+-------------+----------+
| 10165 | 67392.85 |
| 10287 | 61402.00 |
| 10310 | 61234.67 |
+-------------+----------+
如上面所示，返回3行数据，这意味着有3个销售订单的总额大于60000。
可以使用上面的查询作为相关子查询，通过使用EXISTS运算符来查找至少有一个总额大于60000的销售订单的客户信息：
SELECT
customerNumber,
customerName
FROM
customers
WHERE
EXISTS( SELECT
orderNumber, SUM(priceEach * quantityOrdered)
FROM
orderdetails
INNER JOIN
orders USING (orderNumber)
WHERE
customerNumber = customers.customerNumber
GROUP BY orderNumber
HAVING SUM(priceEach * quantityOrdered) > 60000);
执行上面查询，得到以下结果 -
+----------------+-------------------------+
| customerNumber | customerName |
+----------------+-------------------------+
| 148 | Dragon Souveniers, Ltd. |
| 259 | Toms Spezialitten, Ltd |
| 298 | Vida Sport, Ltd |
+----------------+-------------------------+
3 rows in set
在本教程中，我们向您演示了如何使用MySQL子查询和相关子查询来构建更复杂的查询。
¥ 我要打赏
纠错/补充
收藏
加QQ群啦，易百教程官方技术学习群
注意：建议每个人选自己的技术方向加群，同一个QQ最多限加 3 个群。
扫一扫
4951
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
