MySQL 子查询
简介：在本教程中，我们将向您展示如何使用MySQL子查询编写复杂查询并解释相关子查询概念。
一个MySQL子查询是嵌套在另一个查询内，如查询
SELECT，INSERT，UPDATE,DELETE 。此外，MySQL子查询可以嵌套在另一个子查询中。
MySQL子查询称为内部查询，而包含子查询的查询称为外部查询。子查询可以在任何使用表达式的地方使用，并且必须在括号中关闭。
以下查询返回在美国办事处工作的员工。
SELECT
lastName, firstName
FROM
employees
WHERE
officeCode IN (SELECT
officeCode
FROM
offices
WHERE
country = 'USA');
在这个例子中：
- 子查询返回位于美国的办事处的所有办公代码。
- 外部查询选择在办公室代码位于子查询返回的结果集中的办公室中工作的员工的姓氏和名字。
MySQL子查询在WHERE子句中
我们将使用示例数据库中的payments表进行演示。
+----------------+ | payments | +----------------+ | customerNumber | | checkNumber | | paymentDate | | amount | +----------------+ 4 rows in set (0.02 sec)
MySQL子查询带有比较运算符
您可以使用比较运算符（例如，=，>，<等等）将子查询返回的单个值与WHERE子句中的表达式进行比较。
例如，以下查询返回具有最高付款金额的客户。
SELECT
customerNumber, checkNumber, amount
FROM
payments
WHERE
amount = (SELECT
MAX(amount)
FROM
payments);
运行结果：
+----------------+-------------+-----------+ | customerNumber | checkNumber | amount | +----------------+-------------+-----------+ | 141 | JE105477 | 120166.58 | +----------------+-------------+-----------+ 1 row in set (0.02 sec)除了等于运算符，您还可以使用其他比较运算符，例如大于（
>），小于（<）等。
例如，您可以使用子查询查找付款大于平均付款的客户。首先，在子查询使用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 | ...
MySQL子查询带有IN和NOT IN运算符
如果子查询返回多个值，则可以在WHERE子句中使用其他运算符，例如IN或NOT IN运算符。
请参阅以下内容customers和orders表格：
+------------------------+ | customers | +------------------------+ | customerNumber | | customerName | | contactLastName | | contactFirstName | | phone | | addressLine1 | | addressLine2 | | city | | state | | postalCode | | country | | salesRepEmployeeNumber | | creditLimit | +------------------------+ 13 rows in set (0.00 sec) +----------------+ | orders | +----------------+ | orderNumber | | orderDate | | requiredDate | | shippedDate | | status | | comments | | customerNumber | +----------------+ 7 rows in set (0.00 sec)例如，您可以使用带有
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 | ...
MySQL子查询中的FROM子句
在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 (0.00 sec)
注意：
FLOOR()函数用于从项目的平均值中删除小数位。
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 | ...内部查询针对每个产品系列执行，因为每行都更改了产品系列。因此，平均购买价格也会发生变化。外部查询仅筛选购买价格高于子查询中每个产品系列的平均购买价格的产品。
MySQL子查询带有EXISTS和NOT EXISTS
当子查询与EXISTSor NOT EXISTS运算符一起使用时，子查询返回布尔值TRUEor FALSE。以下查询说明了与EXISTS运算符一起使用的子查询：
SELECT
*
FROM
table_name
WHERE
EXISTS( subquery );
在上面的查询中，如果子查询返回任何行，则EXISTS subquery返回TRUE，否则返回FALSE。
EXISTS和NOT EXISTS经常在用于相关子查询。
我们来看看示例数据库中的orders和orderDetails表：
+----------------+ | orders | +----------------+ | orderNumber | | orderDate | | requiredDate | | shippedDate | | status | | comments | | customerNumber | +----------------+ 7 rows in set (0.00 sec) +-----------------+ | orderdetails | +-----------------+ | orderNumber | | productCode | | quantityOrdered | | priceEach | | orderLineNumber | +-----------------+ 5 rows in set (0.01 sec)以下查询选择总值大于60K的销售订单。
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 rows in set (0.01 sec)它返回3行，这意味着有3个销售订单的总值大于60K。 可以使用上面的查询作为相关子查询来查找通过使用
EXISTS运算符放置至少一个总值大于60K的销售订单的客户：
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 (0.01 sec)在本教程中，我们向您展示了如何使用MySQL子查询和相关子查询来构造更复杂的查询。
