MySQL必知必會讀書筆記-5(表的連接)

標籤：code   主鍵   color   表關聯   price   連接   自己的   line   pre   

連接（join）操作是SQL語言最強大的功能之一。這個操作是建立在關係表的基礎之上的

1--關係表

何謂關係表？

      假設有一個產品表，每一個產品都有自己的屬性和供應商，一個供應商可能有多種產品。

那麼在何處儲存供應商的資訊呢？如果直接在產品表裡面儲存，假設某個供應商有一千種產品，

在產品表裡，該供應商的資訊就會被儲存1000次，供應商要改個名字就要到表裡改1000次，

這樣不僅速度慢，還容易出錯。

      關鍵是，相同資料在表中多次出現不是什麼好事（至少很浪費空間）。這時，關係表的作用

就顯現出來了。我們可以建立兩個表，供應商表和產品表。每個供應商都有一個獨一無二的ID，

產品表每個產品裡面儲存一個供應商ID，兩個表就能通過一個ID聯絡起來，即每個產品都能通過

其供應商ID找到對應的供應商。

      供應商的ID就是主鍵，同時它也是產品表的外鍵，這個外鍵將產品表和供應商表關聯起來。

這樣做的好處有很多：不會產生重複的資訊，如果供應商資訊變動，只需要修改供應商表裡的一條

資訊即可。

2--連接(內連接)

     以上面的例子說，當我們需要檢索出一個產品的屬性和它的供應商的屬性時，需要兩個表的資訊

對應連接起來，這時候就用到了連接。

     建立連接

SELECT vend_name, prod_name, prod_priceFROM vendors, productsWHERE vendors.vend_id = products.vend_id

如果沒有where子句（即沒有連接條件），輸出的結果將是兩個表的笛卡爾積。輸出結果的數目將是第一個

表的行數乘以第二個表的行數。

 

內部連接

     上面例子使用等值連接，也稱為內部連接。

SELECT vend_name, prod_name, prod_priceFROM vendors INNER JOIN productsON vendors.vend_id = products.vend_id

 

 

3--參考完整性

 

 

4--連接多個表

       顯示編號為20005的訂單中的物品的基本資料和供應商資訊

SELECT prod_name, prod_price, vend_name, quantityFROM orderitems, products, vendorsWHERE order_num = 20005       AND orderitems.prod_id = products.prod_id      AND products.vend_id = vendors.vend_id

 

       返回訂購了TNT2的客戶的資訊

SELECT customers.cust_name, customers.cust_contact, orderitems.quantityFROM customers, orders, orderitemsWHERE orderitems.prod_id = ‘TNT2‘      AND orders.order_num = orderitems.order_num      AND customers.cust_id = orders.cust_id

 

5--使用表的別名

     對上一個例子使用別名，可以縮短代碼長度，結果相同

SELECT c.cust_name, c.cust_contact, oi.quantityFROM customers AS c, orders AS o, orderitems AS oiWHERE oi.prod_id = ‘TNT2‘      AND o.order_num = oi.order_num      AND c.cust_id = o.cust_id

 6--自連接

     找出某個產品的供應商生產的其他產品

1.使用子查詢

SELECT prod_id, prod_name FROM productsWHERE vend_id = (                 SELECT vend_id                 FROM products                 WHERE prod_id = ‘DTNTR‘                )

2.使用自連接

SELECT p1.prod_id, p1.prod_nameFROM products AS p1, products AS p2WHERE p1.vend_id = p2.vend_id  AND p2.prod_id = ‘DTNTR‘

 

7--外部連接

上述例子中，連接包含了那些在相關表中沒有關聯線的行，這種類型的連接稱為外部連接。

      檢索所有客戶及其訂單

1.使用內連接

SELECT cust_name, order_numFROM customers AS c INNER JOIN orders AS oON c.cust_id = o.cust_id

未列出沒有訂單的顧客

2.使用外連接

SELECT cust_name, order_numFROM customers AS c LEFT OUTER JOIN orders AS oON c.cust_id = o.cust_id

 

列出了沒有訂單的顧客

使用OUTER JOIN文法時，必須指定RIGHT或LEFT關鍵字

這裡LEFT表示列出左邊customer表中的所有行

 

8--使用帶聚集合函式的連接

檢索所有客戶和每個客戶所下的訂單數。

SELECT c.cust_name, c.cust_id, COUNT(o.order_num) AS num_ordFROM customers AS c LEFT OUTER JOIN orders AS oON c.cust_id = o.cust_idGROUP BY c.cust_id

 

 9--總結

 

 

MySQL必知必會讀書筆記-5(表的連接)