MySQL 查詢語句完全指南：GROUP BY、UNION、子查詢、JOIN 類型與優化技巧
系統梳理 MySQL 查詢核心語法：分組排序、UNION 合並、子查詢分類與用法、各類 JOIN 含義與陷阱、自然連接、外連接細節，附實測圖示與優化清單。
一、基礎查詢子句順序 (GHOL)
SELECT [DISTINCT] col_list
FROM tbl
[WHERE cond]
[GROUP BY col_list [WITH ROLLUP]]
[HAVING group_cond]
[ORDER BY col_list [ASC|DESC]]
[LIMIT offset, row_count]
DISTINCT：全行去重，SELECT DISTINCT * 幾乎無意義（全列相同才算重複）。GROUP BY：非聚合列必須出現在 GROUP BY 中 (ONLY_FULL_GROUP_BY 模式)，否則結果不確定。HAVING：針對分組結果過濾，可用聚合函數；WHERE 不能用聚合函數。
二、UNION 合並結果集
(SELECT id, name, english FROM student ORDER BY english DESC LIMIT 1)
UNION
(SELECT id, name, math FROM student ORDER BY math DESC LIMIT 1);
- 預設
UNION = UNION DISTINCT，去重；UNION ALL 保留重複，效能更高。
- 子查詢
ORDER BY 需配合 LIMIT 才生效，否則被優化器忽略。
- 全局排序只需在最後一個 SELECT 後加
ORDER BY。
- 列數、類型、順序必須一致；結果列名以第一個 SELECT 為準。
三、子查詢分類與用法
| 分類 | 返回 | 位置 | 典型運算符 | 示例 |
|---|
| 標量 | 單值 | WHERE/SELECT | =, >, <, <=, >=, != | WHERE english = (SELECT MAX(english) FROM student) |
| 列子查詢 | 單列多行 | WHERE | IN, NOT IN, ANY, ALL, SOME | WHERE class IN (SELECT class FROM student WHERE english > 90) |
| 行子查詢 | 單行多列 | WHERE | =, !=, IN (需加括號) | WHERE (class, math) = (SELECT class, math FROM student WHERE id=8) |
| 表子查詢 | 多行多列 | FROM | 衍生表 (必須別名) | SELECT * FROM (SELECT * FROM student WHERE class='php103') t WHERE english < 60 |
| EXISTS | BOOL | WHERE | EXISTS / NOT EXISTS | SELECT * FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id) |
ANY/ALL/SOME 進階
= ANY ≡ IN；!= ALL ≡ NOT IN。> ALL (sub)：大於子查詢最大值；> ANY (sub)：大於子查詢最小值。- 含 NULL 時三值邏輯需謹慎，生產建議改寫為
IN/EXISTS。
四、JOIN 連接查詢詳解
1. 內連接 (INNER JOIN)
- 預設
JOIN = INNER JOIN。
ON 放連接條件，WHERE 放過濾條件；內連接兩者等價，但語義不同。USING(col)：要求雙表同名列，結果自動去重該列並置首。
2. 外連接
| 類型 | 保留邊 | 語法 | 關鍵限制 |
|---|
| 左外連接 | 左表 | LEFT [OUTER] JOIN | ON 必須寫連接條件，不可用 WHERE 連接 |
| 右外連接 | 右表 | RIGHT [OUTER] JOIN | 同左 |
| 全外連接 | 雙邊 | 不支援 | LEFT JOIN UNION RIGHT JOIN 模擬 |
-- 左連接：教師表保留所有，無排課記錄也顯示
SELECT t.name, tc.class_id
FROM teacher t
LEFT JOIN teacher_class tc ON t.id = tc.teacher_id;
3. 自然連接
NATURAL JOIN：自動匹配所有同名列，不可控，易因新增同名列導致語義變更，生產嚴禁。- 同樣有
NATURAL LEFT/RIGHT JOIN。
4. 交叉連接 (CROSS JOIN)
- 笛卡爾積，
m × n 行，極度危險，僅用於測試生成組合數據。
- MySQL 中
CROSS JOIN ≡ INNER JOIN (無 ON 時)。
五、別名與列名最佳實踐
- 表別名：
t, u, p 短小清晰，有別名後全程必須用別名，原表名失效。
- 列別名：
col AS alias 或 col alias，ORDER BY/GROUP BY/HAVING 可直接用別名。
- 連接條件列名 建議加表前綴 (
t.id = u.t_id)，避免歧義。
六、優化清單
EXPLAIN 看 type (目標 ref/eq_ref)、rows、Extra (避免 Using temporary; Using filesort)。- 被驅動表連接列 必須有索引。
- 小表驅動大表：
SELECT ... FROM small JOIN big ON ...。
- 只查需要的列，禁用
SELECT *。
- 外連接必要時改寫為
NOT EXISTS 或 LEFT JOIN ... WHERE right.pk IS NULL (反模式查詢)。
- 衍生表 (FROM 子查詢) MySQL 5.7+ 支援
derived_merge 優化，8.0 更強；舊版本考慮物化臨時表。
