在本教程中，您将学习如何使用MySQL子查询编写复杂的查询语句并解释相关的子查询概念。
MySQL子查询是嵌套在另一个查询(如SELECT ，INSERT ，UPDATE 或DELETE )中的查询。 另外，MySQL子查询可以嵌套在另一个子查询中。
MySQL子查询称为内部查询，而包含子查询的查询称为外部查询。 子查询可以在使用表达式的任何地方使用，并且必须在括号中关闭。
以下查询返回在位于美国(USA)的办公室工作的员工。
Copy SELECT
lastName, firstName
FROM
employees
WHERE
officeCode IN ( SELECT
officeCode
FROM
offices
WHERE
country = 'USA' ); 在这个例子中：
外部查询选择在办公室代码在子查询返回的结果集中的办公室中工作的员工的姓氏和名字。
当查询执行时，首先执行子查询并返回一个结果集。然后，将此结果集作为外部查询的输入。
1. MySQL子查询在WHERE子句中
我们将使用示例数据(yiibaidb)库 中的payments表进行演示。payments表的表结构如下 -
Copy 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子查询与比较运算符
可以使用比较运算符，例如=，>，<等将子查询返回的单个值与WHERE 子句中的表达式进行比较。
例如，以下查询返回最大付款额的客户。
Copy SELECT
customerNumber, checkNumber, amount
FROM
payments
WHERE
amount = ( SELECT
MAX (amount)
FROM
payments); 执行上面查询语句，得到以下结果 -
Copy + ----------------+-------------+-----------+
| customerNumber | checkNumber | amount |
+ ----------------+-------------+-----------+
| 141 | JE105477 | 120166 . 58 |
+ ----------------+-------------+-----------+ 除等式运算符之外，还可以使用大于(>)，小于(<)等的其他比较运算符。
例如，可以使用子查询找到其付款大于平均付款的客户。 首先，使用子查询来计算使用AVG 聚合函数的平均付款。 然后，在外部查询中，查询大于子查询返回的平均付款的付款。参考以下查询语句的写法 -
Copy SELECT
customerNumber, checkNumber, amount
FROM
payments
WHERE
amount > ( SELECT
AVG (amount)
FROM
payments); 执行上面查询语句，得到以下结果 -
Copy + ----------------+-------------+-----------+
| 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运算符的子查询来查找没有下过任何订单的客户，如下所示：
Copy SELECT
customerName
FROM
customers
WHERE
customerNumber NOT IN ( SELECT DISTINCT
customerNumber
FROM
orders); 执行上面查询，得到以下结果 -
Copy + --------------------------------+
| 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子句中使用子查询时，从子查询返回的结果集将用作临时表 。 该表称为派生表 或物化子查询。
以下子查询将查找订单表中的最大 ，最小 和平均数 ：
Copy SELECT
MAX (items), MIN (items), FLOOR ( AVG (items))
FROM
( SELECT
orderNumber, COUNT (orderNumber) AS items
FROM
orderdetails
GROUP BY orderNumber) AS lineitems; 执行上面查询，得到以下结果 -
Copy + ------------+------------+-------------------+
| MAX (items) | MIN (items) | FLOOR ( AVG (items)) |
+ ------------+------------+-------------------+
| 18 | 1 | 9 |
+ ------------+------------+-------------------+
1 row in set 4. MySQL相关子查询
在前面的例子中，注意到一个子查询是独立的。 这意味着您可以将子查询作为独立查询执行，例如：
Copy SELECT
orderNumber,
COUNT (orderNumber) AS items
FROM
orderdetails
GROUP BY orderNumber; 与独立子查询不同，相关子查询是使用外部查询中的数据的子查询。 换句话说，相关的子查询取决于外部查询。 对外部查询中的每一行对相关子查询进行一次评估。
在以下查询中，我们查询选择购买价格高于每个产品线中的产品的平均购买价格的产品。
Copy SELECT
productname,
buyprice
FROM
products p1
WHERE
buyprice > ( SELECT
AVG (buyprice)
FROM
products
WHERE
productline = p1.productline); 执行上面查询，得到以下结果 -
Copy + -----------------------------------------+----------+
| 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运算符一起使用的子查询：
Copy SELECT
*
FROM
table_name
WHERE
EXISTS ( subquery ); 在上面的查询中，如果子查询(subquery)有返回任何行，则EXISTS子查询返回TRUE，否则返回FALSE。
通常在相关子查询中使用EXISTS和NOT EXISTS。
下面我们来看看示例数据库(yiibaidb)中的orders和orderDetails表：
以下查询选择总额大于60000的销售订单。
Copy SELECT
orderNumber,
SUM (priceEach * quantityOrdered) total
FROM
orderdetails
INNER JOIN
orders USING (orderNumber)
GROUP BY orderNumber
HAVING SUM (priceEach * quantityOrdered) > 60000 ; 执行上面查询，得到以下结果 -
Copy + -------------+----------+
| orderNumber | total |
+ -------------+----------+
| 10165 | 67392 . 85 |
| 10287 | 61402 . 00 |
| 10310 | 61234 . 67 |
+ -------------+----------+ 如上面所示，返回3行数据，这意味着有3个销售订单的总额大于60000。
可以使用上面的查询作为相关子查询，通过使用EXISTS运算符来查找至少有一个总额大于60000的销售订单的客户信息：
Copy 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 ); 执行上面查询，得到以下结果 -
Copy + ----------------+-------------------------+
| customerNumber | customerName |
+ ----------------+-------------------------+
| 148 | Dragon Souveniers, Ltd. |
| 259 | Toms Spezialitten, Ltd |
| 298 | Vida Sport, Ltd |
+ ----------------+-------------------------+
3 rows in set 在本教程中，我们向您演示了如何使用MySQL子查询和相关子查询来构建更复杂的查询。
