MySQL子查詢
在本教學中，您將學習如何使用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子查詢與比較運算子
可以使用比較運算子，例如=，>，<等將子查詢返回的單個值與WHERE子句中的表示式進行比較。
例如，以下查詢返回最大付款額的客戶。
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子查詢和相關子查詢來構建更複雜的查詢。