mysql中子查詢與連接表的示例分析

小編給大家分享一下mysql中子查詢與連接表的示例分析，希望大家閱讀完這篇文章之后都有所收獲，下面讓我們一起去探討吧！

1、什么是子查詢？

列出訂購物品TNT2的所有客戶：

select cust_id
from orders
where order_num IN (SELECT order_num

from orderitems
where prod_id = 'TNT2'
)

格式化SQL 包含子查詢的SELECT語句難以閱讀和調試，特別是它們較為復雜時更是如此。如上所示把子查詢分解為多行并且適當地進行縮進，能極大地簡化子查詢的使用。

對于能嵌套的子查詢的數目沒有限制，不過在實際使用時由于性能的限制，不能嵌套太多的子查詢。

注:

列必須匹配 在WHERE子句中使用子查詢（如這里所示），應該保證SELECT語句具有與WHERE子句中相同數目的列。通常，
子查詢將返回單個列并且與單個列匹配，但如果需要也可以使用多個列。

除了子查詢可以放在where 中，還可以放到select中去。

假如需要顯示customers表中每個客戶的訂單總數。

select cust_name, cust_state, (SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id) as orders
from customers
ORDER BY cust_name

mysql 的運行過程是先執行了customers 中查出來了cust_name，cust_state，cust_id，然后執行5次子查詢，查出來了結果。

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

下面介紹一下聯結:

SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE  vendors.vend_id=products.vend_id
ORDER BY vend_name, prod_name

注:

完全限定列名 在引用的列可能出現二義性時，必須使用完全限定列名（用一個點分隔的表名和列名）。如果引用一個沒有用表名限制的具有二義性的列名，MySQL將返回錯誤。

這里使用where 語句進行聯接的作用:

利用WHERE子句建立聯結關系似乎有點奇怪，但實際上，有一個很充分的理由。請記住，在一條SELECT語句中聯結幾個表時，相應的關系是在運行中構造的。在數據庫表的定義中不存在能指示MySQL如何對表進行聯結的東西。你必須自己做這件事情。在聯結兩個表時，你實際上做的是將第一個表中的每一行與第二個表中的每一行配對。WHERE子句作為過濾條件，它只包含那些匹配給定條件（這里是聯結條件）的行。沒有WHERE子句，第一個表中的每個行將與第二個表中的每個行配對，而不管它們邏輯上是否可以配在一起。

注:

笛卡兒積（cartesian product） 由沒有聯結條件的表關系返回的結果為笛卡兒積。檢索出的行的數目將是第一個表中的行數乘以第二個表中的行數。目前為止所用的聯結稱為等值聯結（equijoin），它基于兩個表之間的相等測試。這種聯結也稱為內部聯結。其實，對于這種聯結可以使用稍微不同的語法來明確指定聯結的類型。

下面的SELECT語句返回與前面例子完全相同的數據：

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products on vendors.vend_id = products.vend_id
ORDER BY vend_name, prod_name

使用哪種語法 ANSI SQL規范首選INNER JOIN語法。此外，盡管使用WHERE子句定義聯結的確比較簡單，但是使用明確的
聯結語法能夠確保不會忘記聯結條件，有時候這樣做也能影響性能。

性能考慮 MySQL在運行時關聯指定的每個表以處理聯結。這種處理可能是非常耗費資源的，因此應該仔細，不要聯結
不必要的表。聯結的表越多，性能下降越厲害。

多做實驗 正如所見，為執行任一給定的SQL操作，一般存在不止一種方法。很少有絕對正確或絕對錯誤的方法。性能可能
會受操作類型、表中數據量、是否存在索引或鍵以及其他一些條件的影響。因此，有必要對不同的選擇機制進行實驗，以找
出最適合具體情況的方法。我們同樣可以使用多張表的聯接，但是有一個問題，因為表名多個地方使用，故而表名很長，那么可以使用表的別名。

如：

下面介紹一下幾種特殊的連接。

2、自聯接

假如你發現某物品（其ID為DTNTR）存在問題，因此想知道生產該物品的供應商生產的其他物品是否也存在這些問題。此查詢要求首先找到生產ID為DTNTR的物品的供應商，然后找出這個供應商生產的其他物品。

下面是解決此問題的一種方法：

你可能使用子查詢，這樣做:

select prod_id,prod_name
from products
where vend_id = (SELECT vend_id from products WHERE prod_id ='DTNTR')

 同樣可以使用自聯接。

select t1.prod_id,t2.prod_name
from products t1, products t2
where t1.vend_id = t2.vend_id and t1.prod_id='DTNTR'

用自聯結而不用子查詢 自聯結通常作為外部語句用來替代從相同表中檢索數據時使用的子查詢語句。雖然最終的結果是
相同的，但有時候處理聯結遠比處理子查詢快得多。應該試一下兩種方法，以確定哪一種的性能更好。

3、自然聯接

無論何時對表進行聯結，應該至少有一個列出現在不止一個表中（被聯結的列）。標準的聯結（前一章中介紹的內部聯結）返回所有數據，甚至相同的列多次出現。自然聯結排除多次出現，使每個列只返回一次。

怎樣完成這項工作呢？答案是，系統不完成這項工作，由你自己完成它。自然聯結是這樣一種聯結，其中你只能選擇那些唯一的列。這一般是通過對表使用通配符（SELECT * ）對所有其他表的列使用明確的子集來完成的。

4、外部聯結

許多聯結將一個表中的行與另一個表中的行相關聯。但有時候會需要包含沒有關聯行的那些行。例如，可能需要使用聯結來完成以下工作：

比如：對每個客戶下了多少訂單進行計數，包括那些至今尚未下訂單的客戶；

SELECT customers.cust_id,order_num
from customers LEFT OUTER JOIN orders on customers.cust_id = orders.cust_id

這條SELECT語句使用了關鍵字OUTER JOIN來指定聯結的類型（而不是在WHERE子句中指定）。但是，與內部聯結關聯兩個表中的行不同的是，外部聯結還包括沒有關聯行的行。在使用OUTER JOIN語法時，必須使用RIGHT或LEFT關鍵字
指定包括其所有行的表（RIGHT指出的是OUTER JOIN右邊的表，而LEFT指出的是OUTER JOIN左邊的表）。

使用帶聚集函數的聯結:

要檢索所有客戶及每個客戶所下的訂單數:

SELECT customers.cust_id, COUNT(order_num) as num
from customers LEFT OUTER JOIN orders on customers.cust_id = orders.cust_id
GROUP BY cust_id

注意點:
1.注意所使用的聯結類型。一般我們使用內部聯結，但使用外部聯結也是有效的。
2.保證使用正確的聯結條件，否則將返回不正確的數據。
3.應該總是提供聯結條件，否則會得出笛卡兒積。
4.在一個聯結中可以包含多個表，甚至對于每個聯結可以采用不同的聯結類型。雖然這樣做是合法的，一般也很有用，但應該在一起測試它們前，分別測試每個聯結。這將使故障排除更為簡單。

看完了這篇文章，相信你對“mysql中子查詢與連接表的示例分析”有了一定的了解，如果想了解更多相關知識，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！