MySQL 核心技術精講：第 15-20 章深度解析（聯結、組合查詢、全文搜索與數據操作）

第 15 章：表聯結——關聯數據的核心技藝

SQL 最強大的功能之一，莫過於在數據檢索過程中實現表聯結。關係型資料庫的設計初衷，就是將資訊分解存儲於多張表中，每張表專注於一類數據，再透過外鍵建立關聯。外鍵是某張表中的一列，其值引用另一張表的主鍵，從而定義兩表間的邏輯關係。良好的資料庫設計還需具備可伸縮性，即能在不斷增長的工作負載下穩定運行。

15.1 聯結的基本概念

聯結是一種機制，允許在單條 SELECT 語句中關聯多張表。透過特定語法，MySQL 能在運行時正確匹配各表中的行，返回合併後的結果集。

15.2 創建基本聯結（隱式內聯結）

最傳統的寫法是在 FROM 子句列出多張表，並在 WHERE 子句指定連接條件：

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

關鍵提醒：務必包含 WHERE 子句，否則會產生笛卡爾積（交叉聯結），返回海量無意義數據。當列名在多表中重複時，必須使用「表名.列名」的完全限定名消除歧義。

15.3 內部聯結（標準語法）

基於相等測試的聯結稱為等值聯結，即內部聯結。ANSI SQL-92 標準推薦使用顯式 INNER JOIN ... ON 語法：

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

此寫法將聯結條件從 WHERE 移至 ON 子句，語義更清晰，也便於後續添加篩選條件。

15.4 多表聯結實戰

實際業務常需聯結 3 張以上的表。例如查詢訂購產品 'TNT2' 的客戶：

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

這相當於第 14 章介紹的子查詢功能，但聯結通常效能更優。

第 16 章：進階聯結技巧

16.1 表別名——簡化代碼、支援自聯結

為表指定簡短別名，不僅縮短 SQL，更是自聯結的前提：

SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'TNT2';

別名可用於 SELECT 列表、ORDER BY 等所有子句。

16.2 三種特殊聯結類型

自聯結

同一張表以不同別名出現兩次，解決「同表關聯」需求。例如找出與 DTNTR 同供應商的其他產品：

SELECT 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 *，本質上就是自然聯結。

外部聯結

內部聯結只返回匹配成功的行；外部聯結還會保留無匹配行。以 LEFT OUTER JOIN 為例：

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

結果會包含所有客戶，無訂單者的 order_num 顯示 NULL。RIGHT OUTER JOIN 則保留右表所有行。必須指定 LEFT 或 RIGHT 關鍵字。

16.3 聯結搭配聚集函數

統計每位客戶的訂單數：

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

內部聯結會自動排除無訂單客戶；若需保留零訂單客戶，改用 LEFT OUTER JOIN。

第 17 章：組合查詢（UNION）

組合查詢（Union/Compound Query）將多條 SELECT 結果垂直合併。適用場景：

1. 從不同表返回結構相同的數據；
2. 對同一表執行多次查詢並合併結果。

17.1 基本語法

SELECT vend_id, prod_id, prod_price FROM products WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price FROM products WHERE vend_id IN (1001,1002);

UNION 預設去重；若需保留重複行，使用 UNION ALL。

17.2 UNION 規則

• 由兩條或以上 SELECT 組成，以 UNION 分隔；
• 每查詢必須包含相同數量的列/表達式/聚集函數；
• 列數據類型需兼容（不必完全相同）。

17.3 結果排序

ORDER BY 僅能出現在最後一條SELECT 之後，對整個合併結果生效。

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('rabbit');

SELECT note_text, MATCH(note_text) AGAINST('rabbit') AS rank
FROM productnotes;

SELECT note_text
FROM productnotes
WHERE MATCH(note_text) AGAINST('anvils' WITH QUERY EXPANSION);

-- 必須同時包含 rabbit 和 bait
SELECT note_text FROM productnotes
WHERE MATCH(note_text) AGAINST('+rabbit +bait' IN BOOLEAN MODE);

-- 排除含 rope* 的行
SELECT note_text FROM productnotes
WHERE MATCH(note_text) AGAINST('heavy -rope*' IN BOOLEAN MODE);

INSERT INTO customers
(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES ('Pep E. Lapew', '100 main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, NULL);

INSERT INTO customers
(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
VALUES
('Pep E. Lapew', '100 main Street', 'Los Angeles', 'CA', '90046', 'USA'),
('M. Martian', '42 Galaxy Way', 'New York', 'NY', '11213', 'USA');

INSERT INTO customers
(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
SELECT cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country
FROM custnew;

UPDATE customers
SET cust_name = 'The Fudds', cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;