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;

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

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;

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 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;

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> 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';

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

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;

mysql> SELECT * FROM productcustomers;

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;

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

mysql> SELECT * FROM customeremaillist;

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

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

mysql> DESC orderitemsexpanded;

mysql> SHOW CREATE VIEW orderitemsexpanded;

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