場景準備
-- 員工基本信息表
CREATE TABLE mytable (
name VARCHAR(20), sex CHAR(1), birth DATE, birthaddr VARCHAR(20)
);
INSERT INTO mytable VALUES ('abccs','f','1977-07-07','china'),('mary','f','1978-12-12','usa'),('tom','m','1970-09-02','usa');
-- 文章發布表
CREATE TABLE title (
writer VARCHAR(20), title VARCHAR(40), senddate DATE
);
INSERT INTO title VALUES ('abccs','a1','2000-01-23'),('mary','b1','1998-03-21'),('abccs','a2','2000-12-04'),('tom','c1','1992-05-16'),('tom','c2','1999-12-12');
多表查詢：內連接 (INNER JOIN)
-- 查詢 abccs 的姓名、性別、文章標題
SELECT m.name, m.sex, t.title
FROM mytable m
INNER JOIN title t ON m.name = t.writer
WHERE m.name = 'abccs';
-- 結果
+-------+------+-------+
| name | sex | title |
+-------+------+-------+
| abccs | f | a1 |
| abccs | f | a2 |
+-------+------+-------+
關鍵點：別名消除列名歧義
- 兩表均有 `name` 列時，**必須使用表別名限定**：`m.name`、`t.name`。
- 舊式逗號連接 (`FROM a, b WHERE a.id=b.id`) **不推薦**，語義不明、易漏寫條件導致笛卡爾積。
- **LEFT JOIN** 保留左表所有行 (無匹配右表列為 NULL)；**RIGHT JOIN** 同理。
多表 UPDATE 實戰
語法 (MySQL 特有)
UPDATE table1 t1
INNER JOIN table2 t2 ON t1.join_col = t2.join_col
SET t1.col1 = t2.col2, t1.col3 = 'value'
WHERE t1.filter = 'cond';
實例：同步文章表作者性別到員工表
UPDATE mytable m
INNER JOIN title t ON m.name = t.writer
SET m.sex = CASE t.title WHEN 'a1' THEN 'f' WHEN 'c1' THEN 'm' ELSE m.sex END
WHERE t.senddate > '1999-01-01';
多表 DELETE 實戰
語法
DELETE t1, t2 -- 刪除兩表匹配行
FROM table1 t1
INNER JOIN table2 t2 ON t1.join_col = t2.join_col
WHERE t1.filter = 'cond';
-- 或僅刪除主表行 (從表保留)
DELETE t1
FROM table1 t1
INNER JOIN table2 t2 ON t1.join_col = t2.join_col
WHERE t2.filter = 'cond';
實例：刪除 2000 年前發布文章的作者信息 (僅演示，慎用)
DELETE m
FROM mytable m
INNER JOIN title t ON m.name = t.writer
WHERE t.senddate < '2000-01-01';
性能優化核心原則
| 原則 | 說明 | 實踐 |
|---|
| 小表驅動大表 | 驅動表 (外層循環) 行數少，被驅動表走索引查找 | EXPLAIN 看 `type=ref`、`rows` 小 |
| 被驅動表關聯列建索引 | NLJ/BNL 核心前提 | `ON t1.id = t2.id` → `t2.id` 必須有索引 |
| 只選需要的列 | 避免 `SELECT *`，減少網絡/內存/回表 | 覆蓋索引可能性最大化 |
| WHERE 條件下推 | 過濾條件盡早應用，減少 JOIN 行數 | 優化器自動做，複雜查詢可手動改寫 |
| 避免 SELECT * 在多表 JOIN | 列名衝突、傳輸膨脹、應用層解析麻煩 | 顯式列出別名列 |
EXPLAIN 實戰分析
EXPLAIN SELECT m.name, m.sex, t.title
FROM mytable m INNER JOIN title t ON m.name = t.writer
WHERE m.name = 'abccs';
-- 關鍵輸出：
-- m: type=ref, key=idx_name, rows=1
-- t: type=ref, key=idx_writer, rows=2
-- Extra: Using where; Using index (覆蓋索引)
