MySQL必知應會-第16章-建立進階連接

標籤：custom   要求   需要   github   含義   應該   方法   標準   完全   

第16章-建立進階連接

本章將講解另外一些連接類型（包括它們的含義和使用方法），介紹如何對被連接的表使用表別名和聚集合函式。

16.1 使用表別名

第10章中介紹了如何使用別名引用被檢索的表列。給列起別名的文法如下：

別名除了用於列名和計算欄位外， SQL還允許給表名起別名。這樣做有兩個主要理由：

• 縮短SQL語句；
• 允許在單條SELECT語句中多次使用相同的表。

請看下面的SELECT語句。它與前一章的例子中所用的語句基本相同，但改成了使用別名：

可以看到， FROM子句中3個表全都具有別名。 customers AS c建立c作為customers的別名，等等。這使得能使用省寫的c而不是全名customers。在此例子中，表別名只用於WHERE子句。但是，表別名不僅能用於WHERE子句，它還可以用於SELECT的列表、 ORDER BY子句以及語句的其他部分。應該注意，表別名只在查詢執行中使用。與列別名不一樣，表別名不返回到客戶機。

16.2 使用不同類型的連接

迄今為止，我們使用的只是稱為內部連接或等值連接（ equijoin） 的簡單連接。現在來看3種其他連接，它們分別是自連接、自然連接和外部連接。

16.2.1 自連接

如前所述，使用表別名的主要原因之一是能在單條SELECT語句中不止一次引用相同的表。下面舉一個例子。假如你發現某物品（其ID為DTNTR）存在問題，因此想知道生產該物品的供應商生產的其他物品是否也存在這些問題。此查詢要求首先找到生產ID為DTNTR的物品的供應商，然後找出這個供應商生產的其他物品。下面是解決此問題的一種方法：

這是第一種解決方案，它使用了子查詢。內部的SELECT語句做了一個簡單的檢索，返回生產ID為DTNTR的物品供應商的vend_id。該ID用於外部查詢的WHERE子句中，以便檢索出這個供應商生產的所有物品（第14章中講授了子查詢的所有內容。更多資訊請參閱該章）。現在來看使用連接的相同查詢：

此查詢中需要的兩個表實際上是相同的表，因此products表在FROM子句中出現了兩次。雖然這是完全合法的，但對products的引用具有二義性，因為MySQL不知道你引用的是products表中的哪個執行個體。為解決此問題，使用了表別名。 products的第一次出現為別名p1，第二次出現為別名p2。現在可以將這些別名用作表名。例如， SELECT語句使用p1首碼明確地給出所需列的全名。如果不這樣， MySQL將返回錯誤，因為分別存在兩個名為prod_id、 prod_name的列。 MySQL不知道想要的是哪一個列（即使它們事實上是同一個列）。 WHERE（通過匹配p1中的vend_id和p2中的vend_id）首先連接兩個表，然後按第二個表中的prod_id過濾資料，返回所需的資料。

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

16.2.2 自然連接

無論何時對錶進行連接，應該至少有一個列出現在不止一個表中（被連接的列）。標準的連接（前一章中介紹的內部連接）返回所有資料，甚至相同的列多次出現。 自然連接排除多次出現，使每個列只返回一次。怎樣完成這項工作呢？答案是，系統不完成這項工作，由你自己完成它。自然連接是這樣一種連接，其中你只能選擇那些唯一的列。這一般是通過對錶使用萬用字元（SELECT *），對所有其他表的列使用明確的子集來完成的。下面舉一個例子：

在這個例子中，萬用字元只對第一個表使用。所有其他列明確列出，所以沒有重複的列被檢索出來。事實上，迄今為止我們建立的每個內部連接都是自然連接，很可能我們永遠都不會用到不是自然連接的內部連接。

16.2.3 外部連接

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

• 對每個客戶下了多少訂單進行計數，包括那些至今尚未下訂單的客戶；
• 列出所有產品以及訂購數量，包括沒有人訂購的產品；
• 計算平均銷售規模，包括那些至今尚未下訂單的客戶。
  在上述例子中，連接包含了那些在相關表中沒有關聯線的行。這種類型的連接稱為外部連接。
  下面的SELECT語句給出一個簡單的內部連接。它檢索所有客戶及其訂單：
  

外部連接文法類似。為了檢索所有客戶，包括那些沒有訂單的客戶，可如下進行：

類似於上一章中所看到的內部連接，這條SELECT語句使用了關鍵字OUTER JOIN來指定連接的類型（而不是在WHERE子句中指定）。但是，與內部連接關聯兩個表中的行不同的是，外部連接還包括沒有關聯線的行。在使用OUTER JOIN文法時，必須使用RIGHT或LEFT關鍵字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右邊的表，而LEFT指出的是OUTER JOIN左邊的表）。上面的例子使用LEFT OUTER JOIN從FROM子句的左邊表（customers表）中選擇所有行。為了從右邊的表中選擇所有行，應該使用RIGHT OUTER JOIN，如下例所示：

沒有*=操作符 MySQL不支援簡化字元*=和=*的使用，這兩種操作符在其他DBMS中是很流行的。

外部連接的類型 存在兩種基本的外部連接形式：左外部連接和右外部連接。它們之間的唯一差別是所關聯的表的順序不同。換句話說，左外部連接可通過顛倒FROM或WHERE子句中表的順序轉換為右外部連接。因此，兩種類型的外部連接可互換使用，而究竟使用哪一種純粹是根據方便而定。

16.3 使用帶聚集合函式的連接

正如第12章所述，聚集合函式用來摘要資料。雖然至今為止聚集合函式的所有例子只是從單個表摘要資料，但這些函數也可以與連接一起使用。為說明這一點，請看一個例子。如果要檢索所有客戶及每個客戶所下的訂單數，下面使用了COUNT()函數的代碼可完成此工作：

此SELECT語句使用INNER JOIN將customers和orders表互相關聯。GROUP BY子句按客戶分組資料 ， 因 此 ， 函 數 調 用 COUNT(orders.order_num)對每個客戶的訂單計數，將它作為num_ord返回。聚集合函式也可以方便地與其他連接一起使用。請看下面的例子：

這個例子使用左外部連接來包含所有客戶，甚至包含那些沒有任何下訂單的客戶。結果顯示也包含了客戶Mouse House，它有0個訂單。

16.4 使用連接和連接條件

在總結關於連接的這兩章前，有必要匯總一下關於連接及其使用的某些要點。

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

16.5 小結

本章是上一章關於連接的繼續。本章從講授如何以及為什麼要使用別名開始，然後討論不同的連接類型及對每種類型的連接使用的各種文法形式。我們還介紹了如何與連接一起使用聚集合函式，以及在使用連接時應該注意的某些問題。

MySQL必知應會-第16章-建立進階連接