MySQL入門（二），mysql入門

MySQL入門（二），mysql入門

本學習筆記參考《MySQL必知必會》和官方手冊MySQL 5.6 Reference Manual

MySQL入門（一）
MySQL入門（三）
本文內容：
- MySQL連接表
- MySQL視圖

四、MySQL連接表

    假設要儲存貨物的資訊，建立一個 products 表，每種貨物的資訊佔一行，包括產品名字，產地以及供應商的資訊（供應商名字，電話等）。同一個供應商可能有多種產品，那麼這時屬於同一供應商的不同產品可能要重複儲存供應商的資訊，這會造成儲存空間和時間的浪費，改動的不方便等。解決這個問題的方法是建立兩個表，products 和 vendors ，vendors 表中以供應商ID（vend_id）作為主鍵（primary key），products 表中以 vend_id 作為外鍵(foreign key)，這樣兩個表之間就建立了聯絡。
    如果資料存放區在多個表中可以使用連接來查詢，連接是一種機制，用來在一條 SELECT 語句中關聯表。

一個連接的例子（利用 WHERE 實現）：

mysql> SELECT vend_name, prod_name, prod_price    -> FROM vendors, products    -> WHERE vendors.vend_id = products.vend_id    -> ORDER BY vend_name, prod_name;

由沒有連接條件的表關係返回的結果為笛卡爾積，檢索出的行數目是第一個表中的行數乘以第二個表中的行數。

4.1 內部連接

    目前為止所用的連接稱為等值連接(equijoin)，它基於兩個表之間的相等測試，這種連接也成為內部連接。下面更為規範和首選的方法和上面的寫法返回相同的結果。

mysql> SELECT vend_name, prod_name, prod_price    -> FROM vendors INNER JOIN products    -> ON vendors.vend_id = products.vend_id;

4.2 連接多個表

mysql> SELECT prod_name, vend_name, prod_price, quantity    -> FROM orderitems, products, vendors    -> WHERE products.vend_id = vendors.vend_id    -> AND orderitems.prod_id = products.prod_id    -> AND order_num = 20005;

為簡便起見也可以使用表別名來進行查詢，表別名和列別名不一樣，表別名不返回到客戶機。下面的語句使用表別名，和上面的語句返回相同的結果。

mysql> SELECT prod_name, vend_name, prod_price, quantity    -> FROM orderitems AS o, products AS p, vendors AS v    -> WHERE p.vend_id = v.vend_id    -> AND o.prod_id = p.prod_id    -> AND o.order_num = 20005;

4.3 自連接

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

用子查詢的方式：

mysql> SELECT vend_id, prod_id, prod_name    -> FROM products    -> WHERE vend_id IN ( SELECT vend_id                       -> FROM products                       -> WHERE prod_id = 'DTNTR' );

用自連接的方式：

mysql> SELECT p1.vend_id, p1.prod_id, p1.prod_name    -> FROM products AS p1, products AS p2    -> WHERE p1.vend_id = p2.vend_id    -> AND p2.prod_id = 'DTNTR';

4.4 外部連接

    customers 表格儲存體所有客戶的資訊，每個客戶有唯一的ID（cust_id），cust_id 為 customers 的主鍵。orders 表格儲存體客戶訂單（但不是訂單細節），每個訂單有唯一的訂單號(order_num)，訂單用 cust_id 列與 customers 表關聯。

查詢存在訂單的客戶及其訂單號（可以用內連接）：

mysql> SELECT customers.cust_id, cust_name, orders.order_num    -> FROM customers INNER JOIN orders    -> ON customers.cust_id = orders.cust_id;

查詢所有客戶及其訂單（包括沒有訂單的客戶）：

mysql> SELECT customers.cust_id, cust_name, orders.order_num    -> FROM customers LEFT OUTER JOIN orders    -> ON customers.cust_id = orders.cust_id;

在使用 OUTER JOIN 文法時，必須使用 LEFT 或 RIGHT 關鍵字指定包括其所有行的表（RIGHT 指的是 OUTER JOIN 右邊的表，LEFT 指的是 OUTER JOIN 左邊的表）。

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

mysql> SELECT customers.cust_id, cust_name, orders.order_num,    -> COUNT(orders.order_num) AS num_ord    -> FROM customers INNER JOIN orders    -> ON customers.cust_id = orders.cust_id    -> GROUP BY customers.cust_id;

一篇介紹連接的文章

五、MySQL視圖5.1 什麼是視圖

    視圖是虛擬表。與包含資料的表不一樣，視圖只包含使用時動態檢索資料的查詢。
例子，下面的陳述式完成檢索訂購了某個特定產品的客戶的資訊。

mysql> SELECT cust_name, cust_contact    -> FROM customers, orders, orderitems    -> WHERE customers.cust_id = orders.cust_id    -> AND orders.order_num = orderitems.order_num    -> AND orderitems.prod_id = 'TNT2';

寫出這樣的語句必須知道表的結構和連接關係，現在如果把這個查詢封裝成一個名為 productcustomers 的虛擬表，則可以如下輕鬆的檢索出相同的資料：

mysql> SELECT cust_name, cust_contact     -> FROM productcustomers     -> WHERE prod_id = 'TNT2';

5.2 為什麼使用視圖

    重用SQL語句；簡化複雜的SQL操作；保護資料。但由於視圖不包含資料，每次使用時都要完成一個檢索，所以會影響效能。

5.3 使用視圖

用 CREATE VIEW 語句來建立視圖；
用 SHOW CREATE VIEW viewname; 語句來查看建立該視圖的語句；
用 DROP VIEW viewname; 來刪除視圖；
更新視圖時，可以先用 DROP 在用 CREATE。也可以直接用 CREATE OR REPLACE VIEW，如果視圖不存在則建立，如果存在則替換原視圖。

(1) 利用視圖簡化複雜的連接

mysql> CREATE VIEW productcustomers AS    -> SELECT cust_name, cust_contact, prod_id    -> FROM customers, orders, orderitems    -> WHERE customer.cust_id = orders.cust_id    -> AND orders.order_num = orderitems.order_num;

上面的語句建立了一個名為 productcustomers 的視圖，它連接三個表，以返回訂購了任意產品的所有客戶列表。

mysql> SELECT * FROM productcustomers;

(2) 用視圖重新格式化檢索出來的資料

mysql> CREATE VIEW vendorlocations AS    -> SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')    -> AS vend_title    -> FROM vendors ORDER BY vend_name;mysql> SELECT * FROM vendorlocations;

(3) 用視圖過濾不想要的資料

mysql> CREATE VIEW customeremaillist AS    -> SELECT cust_id, cust_name, cust_email    -> FROM customers    -> WHERE cust_email IS NOT NULL;

建立視圖 customeremaillist 過濾沒有郵箱地址的使用者。

mysql> SELECT * FROM customeremaillist;

(4) 使用視圖與計算欄位

mysql> CREATE VIEW orderitemsexpanded AS    -> SELECT order_num, prod_id, quantity, item_price,    -> quantity * item_price AS expanded_price    -> FROM orderitems;

建立一個視圖 orderitemsexpanded ，計算出所有訂單裡產品的總價格。

mysql> SELECT * FROM orderitemsexpanded    -> WHERE order_num = 20005;

5.4 查看視圖的資訊

利用 DESCRIBE 語句查看視圖的基本資料：

mysql> DESC orderitemsexpanded;

mysql> SHOW CREATE VIEW orderitemsexpanded;

查看所有已建立視圖的資訊：

mysql> USE information_schema;mysql> SELECT * FROM views;            // 該語句輸出資訊比較多mysql> SELECT TABLE_NAME, DEFINER FROM views;