MySQL必知應會-第15章-連接表

標籤：報表   特性   內容   代碼   from   類別   瞭解   良好的   查詢   

第15章-連接表

本章將介紹什麼是連接，為什麼要使用連接，如何編寫使用連接的SELECT語句。

15.1 連接

SQL最強大的功能之一就是能在資料檢索查詢的執行中連接（join）表。連接是利用SQL的SELECT能執行的最重要的操作，很好地理解連接及其文法是學習SQL的一個極為重要的組成部分。在能夠有效地使用連接前，必須瞭解關係表以及關聯式資料庫設計的一些基礎知識。下面的介紹並不是這個內容的全部知識，但作為入門已經足夠了。

15.1.1 關係表

理解關係表的最好方法是來看一個現實世界中的例子。假如有一個包含產品目錄的資料庫表，其中每種類別的物品佔一行。對於每種物品要儲存的資訊包括產品描述和價格，以及生產該產品的供應商資訊。現在，假如有由同一供應商生產的多種物品，那麼在何處儲存供應商資訊（如，供應商名、地址、聯絡方法等）呢？將這些資料與產品資訊分開儲存的理由如下。

• 因為同一供應商生產的每個產品的供應商資訊都是相同的，對每個產品重複此資訊既浪費時間又浪費儲存空間。
• 如果供應商資訊改變（例如，供應商搬家或電話號碼變動），只需改動一次即可。
• 如果有重複資料（即每種產品都儲存供應商資訊），很難保證每次輸入該資料的方式都相同。不一致的資料在報表中很難利用。

關鍵是，相同資料出現多次決不是一件好事，此因素是關聯式資料庫設計的基礎。關係表的設計就是要保證把資訊分解成多個表，一類資料一個表。各表通過某些常用的值（即關係設計中的關係（ relational） ）互相關聯。
在這個例子中，可建立兩個表，一個儲存供應商資訊，另一個儲存產品資訊。 vendors表包含所有供應商資訊，每個供應商佔一行，每個供應商具有唯一的標識。此標識稱為主鍵（ primary key） （在第1章中首次提到），可以是供應商ID或任何其他唯一值。products表只儲存產品資訊，它除了儲存供應商ID（vendors表的主鍵）外不儲存其他供應商資訊。vendors表的主鍵又叫作products的外鍵，它將vendors表與products表關聯，利用供應商ID能從vendors表中找出相應供應商的詳細資料。
外鍵（foreign key） 外鍵為某個表中的一列，它包含另一個表的主索引值，定義了兩個表之間的關係。這樣做的好處如下：

• 供應商資訊不重複，從而不浪費時間和空間；
• 如果供應商資訊變動，可以只更新vendors表中的單個記錄，相關表中的資料不用改動；
• 由於資料無重複，顯然資料是一致的，這使得處理資料更簡單。
  總之，關係資料可以有效地儲存和方便地處理。因此，關聯式資料庫的延展性遠比非關聯式資料庫要好。
  延展性（scale） 能夠適應不斷增加的工作量而不失敗。設計良好的資料庫或應用程式稱之為延展性好（ scale well） 。

15.1.2 為什麼要使用連接

正如所述，分解資料為多個表能更有效地儲存，更方便地處理，並且具有更大的延展性。但這些好處是有代價的。如果資料存放區在多個表中，怎樣用單條SELECT語句檢索出資料？答案是使用連接。簡單地說，連接是一種機制，用來在一條SELECT語句中關聯表，因此稱之為連接。使用特殊的文法，可以連接多個表返回一組輸出，連接在運行時關聯表中正確的行。

維護參考完整性 重要的是，要理解連接不是物理實體。換句話說，它在實際的資料庫表中不存在。連接由MySQL根據需要建立，它存在於查詢的執行當中。在使用關係表時，僅在關係列中插入合法的資料非常重要。回到這裡的例子，如果在products表中插入擁有非法供應商ID（即沒有在vendors表中出現）的供應商生產的產品，則這些產品是不可訪問的，因為它們沒有關聯到某個供應商。為防止這種情況發生，可指示MySQL只允許在products表的供應商ID列中出現合法值（即出現在vendors表中的供應商）。這就是維護參考完整性，它是通過在表的定義中指定主鍵和外鍵來實現的。（這將在第21章介紹。）

15.2 建立連接

連接的建立非常簡單，規定要連接的所有表以及它們如何關聯即可。請看下面的例子：

我們來考察一下此代碼。 SELECT語句與前面所有語句一樣指定要檢索的列。這裡，最大的差別是所指定的兩個列（prod_name和prod_price）在一個表中，而另一個列（vend_name）在另一個表中。現在來看FROM子句。與以前的SELECT語句不一樣，這條語句的FROM子句列出了兩個表，分別是vendors和products。它們就是這條SELECT語句連接的兩個表的名字。這兩個表用WHERE子句正確連接， WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。可以看到要匹配的兩個列以vendors.vend_id 和 products.vend_id指定。這裡需要這種完全限定列名，因為如果只給出vend_id，則MySQL不知道指的是哪一個（它們有兩個，每個表中一個）。

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

15.2.1 WHERE子句的重要性

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

笛卡兒積（cartesian product） 由沒有連接條件的表關係返回的結果為笛卡兒積。檢索出的行的數目將是第一個表中的行數乘以第二個表中的行數。為理解這一點，請看下面的SELECT語句及其輸出：

從上面的輸出中可以看到，相應的笛卡兒積不是我們所想要的。這裡返回的資料用每個供應商匹配了每個產品，它包括了供應商不正確的產品。實際上有的供應商根本就沒有產品。

不要忘了WHERE子句 應該保證所有連接都有WHERE子句，否則MySQL將返回比想要的資料多得多的資料。同理，應該保證WHERE子句的正確性。不正確的過濾條件將導致MySQL返回不正確的資料。

叉連接 有時我們會聽到返回稱為叉連接（ cross join）的笛卡兒積的連接類型。

15.2.2 內部連接

目前為止所用的連接稱為等值連接（equijoin），它基於兩個表之間的相等測試。這種連接也稱為內部連接。其實，對於這種連接可以使用稍微不同的文法來明確指定連接的類型。下面的SELECT語句返回與前面例子完全相同的資料：

此語句中的SELECT與前面的SELECT語句相同，但FROM子句不同。這裡，兩個表之間的關係是FROM子句的組成部分，以INNER JOIN指定。在使用這種文法時，連接條件用特定的ON子句而不是WHERE子句給出。傳遞給ON的實際條件與傳遞給WHERE的相同。

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

15.2.3 連接多個表

SQL對一條SELECT語句中可以連接的表的數目沒有限制。建立連接的基本規則也相同。首先列出所有表，然後定義表之間的關係。例如：

此例子顯示編號為20005的訂單中的物品。訂單物品儲存在orderitems表中。每個產品按其產品ID儲存，它引用products表中的產品。這些產品通過供應商ID連接到vendors表中相應的供應商，供應商ID儲存在每個產品的記錄中。這裡的FROM子句列出了3個表，而WHERE子句定義了這兩個連接條件，而第三個連接條件用來過濾出訂單20005中的物品。

效能考慮 MySQL在運行時關聯指定的每個表以處理連接。這種處理可能是非常耗費資源的，因此應該仔細，不要連接不必要的表。連接的表越多，效能下降越厲害。
現在可以回顧一下第14章中的例子了。該例子如下所示，其SELECT語句返回訂購產品TNT2的客戶列表：

正如第14章所述，子查詢並不總是執行複雜SELECT操作的最有效方法，下面是使用連接的相同查詢：

正如第14章所述，這個查詢中返回資料需要使用3個表。但這裡我們沒有在嵌套子查詢中使用它們，而是使用了兩個連接。這裡有3個WHERE子句條件。前兩個關聯連接中的表，後一個過濾產品TNT2的資料。

多做實驗 正如所見，為執行任一給定的SQL操作，一般存在不止一種方法。很少有絕對正確或絕對錯誤的方法。效能可能會受操作類型、表中資料量、是否存在索引或鍵以及其他一些條件的影響。因此，有必要對不同的選擇機制進行實驗，以找出最適合具體情況的方法。

15.3 小結

連接是SQL中最重要最強大的特性，有效地使用連接需要對關聯式資料庫設計有基本的瞭解。本章隨著對連接的介紹講述了關聯式資料庫設計的一些基本知識，包括等值連接（也稱為內部連接）這種最經常使用的連接形式。下一章將介紹如何建立其他類型的連接。

MySQL必知應會-第15章-連接表