MySQL必知應會-第14章-使用子查詢

標籤：sni   目的   編號   版本   tps   注意   增加   客戶   資料庫   

第14章-使用子查詢

使用子查詢本章介紹什麼是子查詢以及如何使用它們。

14.1 子查詢

版本要求 MySQL 4.1引入了對子查詢的支援，所以要想使用本章描述的SQL，必須使用MySQL 4.1或更進階的版本。SELECT語句是SQL的查詢。迄今為止我們所看到的所有SELECT語句都是簡單查詢，即從單個資料庫表中檢索資料的單條語句。
查詢（query） 任何SQL語句都是查詢。但此術語一般指SELECT語句。
SQL還允許建立子查詢（ subquery） ，即嵌套在其他查詢中的查詢。為什麼要這樣做呢？理解這個概念的最好方法是考察幾個例子。

14.2 利用子查詢進行過濾

本書所有章中使用的資料庫表都是關係表（關於每個表及關係的描述，請參閱附錄B）。訂單儲存在兩個表中。對於包含訂單號、客戶ID、訂單日期的每個訂單， orders表格儲存體一行。各訂單的物品儲存在相關的orderitems表中。 orders表不儲存客戶資訊。它只儲存客戶的ID。實際的客戶資訊儲存在customers表中。現在，假如需要列出訂購物品TNT2的所有客戶，應該怎樣檢索？下面列出具體的步驟。

• (1) 檢索包含物品TNT2的所有訂單的編號。
• (2) 檢索具有前一步驟列出的訂單編號的所有客戶的ID。
• (3) 檢索前一步驟返回的所有客戶ID的客戶資訊。
  上述每個步驟都可以單獨作為一個查詢來執行。可以把一條SELECT語句返回的結果用於另一條SELECT語句的WHERE子句。也可以使用子查詢來把3個查詢組合成一條語句。第一條SELECT語句的含義很明確，對於prod_id為TNT2的所有訂單物品，它檢索其order_num列。輸出資料行出兩個包含此物品的訂單：
  

下一步，查詢具有訂單20005和20007的客戶ID。利用第7章介紹的IN子句，編寫如下的SELECT語句：

現在，把第一個查詢（返回訂單號的那一個）變為子查詢組合兩個查詢。請看下面的SELECT語句：

在SELECT語句中，子查詢總是從內向外處理。在處理上面的SELECT語句時， MySQL實際上執行了兩個操作。首先，它執行下面的查詢：
SELECT order_num FROM orderitems WHERE prod_id=’TNT2’
此查詢返回兩個訂單號： 20005和20007。然後，這兩個值以IN操作符要求的逗號分隔的格式傳遞給外部查詢的WHERE子句。外部查詢變成：
SELECT cust_id FROM orders WHERE order_num IN (20005,20007)
可以看到，輸出是正確的並且與前面寫入程式碼WHERE子句所返回的值相同。

格式化SQL 包含子查詢的SELECT語句難以閱讀和調試，特別是它們較為複雜時更是如此。如上所示把子查詢分解為多行並且適當地進行縮排，能極大地簡化子查詢的使用。現在得到了訂購物品TNT2的所有客戶的ID。下一步是檢索這些客戶ID的客戶資訊。檢索兩列的SQL語句為：

可以把其中的WHERE子句轉換為子查詢而不是寫入程式碼這些客戶ID：

為了執行上述SELECT語句， MySQL實際上必須執行3條SELECT語句。最裡邊的子查詢返回訂單號列表，此列表用於其外面的子查詢的WHERE子句。外面的子查詢返回客戶ID列表，此客戶ID列表用於最外層查詢的WHERE子句。最外層查詢確實返回所需的資料。可見，在WHERE子句中使用子查詢能夠編寫出功能很強並且很靈活的SQL語句。對於能嵌套的子查詢的數目沒有限制，不過在實際使用時由於效能的限制，不能嵌套太多的子查詢。
列必須匹配 在WHERE子句中使用子查詢（如這裡所示），應該保證SELECT語句具有與WHERE子句中相同數目的列。通常，子查詢將返回單個列並且與單個列匹配，但如果需要也可以使用多個列。
雖然子查詢一般與IN操作符結合使用，但也可以用於測試等於（=）、不等於（<>）等。
子查詢和效能 這裡給出的代碼有效並獲得所需的結果。但是，使用子查詢並不總是執行這種類型的資料檢索的最有效方法。更多的論述，請參閱第15章，其中將再次給出這個例子。

14.3 作為計算欄位使用子查詢

使用子查詢的另一方法是建立計算欄位。假如需要顯示customers表中每個客戶的訂單總數。訂單與相應的客戶ID儲存在orders表中。為了執行這個操作，遵循下面的步驟。
(1) 從customers表中檢索客戶列表。
(2) 對於檢索出的每個客戶，統計其在orders表中的訂單數目。
正如前兩章所述，可使用SELECT COUNT(*)對錶中的行進行計數，並且通過提供一條WHERE子句來過濾某個特定的客戶ID， 可僅對該客戶的訂單進行計數。例如，下面的代碼對客戶10001的訂單進行計數：

為了對每個客戶執行COUNT(*)計算，應該將COUNT(*)作為一個子查詢。請看下面的代碼：

這 條 SELECT 語 句 對 customers 表 中 每 個 客 戶 返 回 3 列 ：cust_name、 cust_state和orders。 orders是一個計算欄位，它是由圓括弧中的子查詢建立的。該子查詢對檢索出的每個客戶執行一次。在此例子中，該子查詢執行了5次，因為檢索出了5個客戶。子查詢中的WHERE子句與前面使用的WHERE子句稍有不同，因為它使用了完全限定列名（在第4章中首次提到）。下面的語句告訴SQL比較orders表中的cust_id與當前正從customers表中檢索的cust_id：
WHERE orders.cust_id=customers.cust_id

相互關聯的子查詢（correlated subquery） 涉及外部查詢的子查詢。
這種類型的子查詢稱為相互關聯的子查詢。任何時候只要列名可能有多義性，就必須使用這種文法（表名和列名由一個句點分隔）。為什麼這樣？我們來看看如果不使用完整列名會發生什麼情況：

顯然，返回的結果不正確（請比較前面的結果），那麼，為什麼會這樣呢？有兩個cust_id列，一個在customers中，另一個在orders中，需要比較這兩個列以正確地把訂單與它們相應的顧客匹配。如果不完全限定列名， MySQL將假定你是對orders表中的cust_id進行自身比較。而SELECT COUNT(*) FROM orders WHERE cust_id = cust_id;總是返回orders表中的訂單總數（因為MySQL查看每個訂單的cust_id是否與本身匹配，當然，它們總是匹配的）。雖然子查詢在構造這種SELECT語句時極有用，但必須注意限制有歧義性的列名。

不止一種解決方案 正如本章前面所述，雖然這裡給出的範例代碼運行良好，但它並不是解決這種資料檢索的最有效方法。在後面的章節中我們還要遇到這個例子。

逐漸增加子查詢來建立查詢 用子查詢測試和調試查詢很有技巧性，特別是在這些語句的複雜性不斷增加的情況下更是如此。 用子查詢建立（和測試）查詢的最可靠的方法是逐漸進行，這與MySQL處理它們的方法非常相同。首先，建立和測試最內層的查詢。然後，用寫入程式碼資料建立和測試外層查詢，並且僅在確認它正常後才嵌入子查詢。這時，再次測試它。對於要增加的每個查詢，重複這些步驟。這樣做僅給構造查詢增加了一點點時間，但節省了以後（找出查詢為什麼不正常）的大量時間，並且極大地提高了查詢一開始就正常工作的可能性。

14.4 小結

本章學習了什麼是子查詢以及如何使用它們。子查詢最常見的使用是在WHERE子句的IN操作符中，以及用來填充計算資料行。我們舉了這兩種操作類型的例子。

MySQL必知應會-第14章-使用子查詢