MySQL 入門進階（二）：多表聯結與視圖實戰

MySQL 入門進階（二）：多表聯結與視圖實戰

本文承接基礎入門，深入講解多表聯結（內連接、外連接、自連接、多表連接）與視圖的創建、使用、管理，所有示例基於經典樣本數據庫，含實際輸出驗證。

一、多表聯結核心概念

將數據按業務拆分到多表（如產品表、供應商表、訂單表），通過**外鍵**建立關聯，查詢時用 JOIN 重組。避免冗餘、保證一致性、節省空間。

二、內部聯結

1. 隱式內連接（WHERE 條件）

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

2. 顯式內連接（標準語法，推薦）

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

笛卡爾積警示：無連接條件時，結果行數 = 表1行數 × 表2行數，極其危險。

三、聯結多表

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;

使用表別名簡化

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;

別名不返回客戶端，僅簡化 SQL 撰寫。

四、自聯結

場景：查詢生產某產品（ID=DTNTR）的供應商的其他產品。

子查詢寫法

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

自聯結寫法（效能更優）

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

自聯結避免子查詢，優化器更易生成高效執行計劃。

五、外部聯結

左外連接：保留左表所有行

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

結果：無訂單客戶的 order_num 顯示 NULL。

右外連接：保留右表所有行

SELECT customers.cust_id, cust_name, orders.order_num
FROM customers RIGHT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

六、帶聚合函數的聯結

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

統計每客戶訂單數。

七、視圖

什麼是視圖

虛擬表，不儲存數據，僅儲存 SELECT 查詢定義。使用時動態執行底層查詢。

優勢

• 重用複雜 SQL
• 簡化業務邏輯（如聯結多表、計算欄位）
• 安全控制（僅暴露特定欄位/行）

代價：每次查詢視圖都執行底層 SQL，大數據量需注意效能。

視圖操作語法

-- 創建
CREATE VIEW view_name AS SELECT ...;

-- 查看定義
SHOW CREATE VIEW view_name;

-- 刪除
DROP VIEW view_name;

-- 更新（不存在則創建，存在則替換）
CREATE OR REPLACE VIEW view_name AS SELECT ...;

實戰示例

1. 簡化複雜聯結

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

-- 使用視圖
SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';

2. 重新格式化輸出

CREATE VIEW vendorlocations AS
SELECT CONCAT(RTRIM(vend_name), ' (', RTRIM(vend_country), ')') AS vend_title
FROM vendors ORDER BY vend_name;

SELECT * FROM vendorlocations;

3. 過濾不需要的數據

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

SELECT * FROM customeremaillist;

4. 計算欄位

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

SELECT * FROM orderitemsexpanded WHERE order_num = 20005;

查看視圖信息

DESC orderitemsexpanded;
SHOW CREATE VIEW orderitemsexpanded;

-- 查詢所有視圖
USE information_schema;
SELECT TABLE_NAME, DEFINER FROM VIEWS;

總結

• 內連接只返回匹配行，外連接保留單側所有行
• 自聯結解決同表多層級關聯
• 視圖封裝複雜邏輯，提升可讀性與安全性
• 大數據量視圖查詢需評估效能，必要時改用物化表

掌握聯結與視圖，是從寫單表 SQL 進階到處理真實業務數據模型的關鍵一步。