MySQL SELECT 進階用法 10 例：從基礎到實戰技巧

MySQL SELECT 進階用法 10 例：從基礎到實戰技巧

SELECT 是 SQL 查詢核心，掌握其進階用法能寫出更簡潔、高效、可維護的查詢。本文通過 10 個實戰案例，覆蓋格式化、聚合、去重、分頁、多表、字符串處理等高頻場景。

1. 多行格式化：可讀性第一

-- 推薦：關鍵字大寫、換行、縮進
SELECT 
    u.id,
    u.username,
    o.order_no,
    o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
  AND o.created_at >= '2024-01-01'
ORDER BY o.amount DESC;

原則：一行一子句，列逐行列出，縮進對齊，利於 Code Review 與 Git Diff。

2. 批量查詢：IN 替代多個 OR

-- 低效
SELECT * FROM articles WHERE id = 1 OR id = 3 OR id = 5;

-- 高效：優化器可轉為範圍查詢
SELECT * FROM articles WHERE id IN (1, 3, 5);

-- 子查詢配合
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip_level >= 3);

3. 字符串拼接：CONCAT / CONCAT_WS

-- 基礎拼接
SELECT CONCAT(id, '-', title) AS display FROM articles WHERE id = 1;
-- 結果: "1-MySQL Tutorial"

-- 帶分隔符，自動跳過 NULL
SELECT CONCAT_WS(',', username, email, phone) AS contact FROM users;
-- NULL 值不產生額外分隔符

4. 字符串查找：LOCATE / INSTR

-- 返回首次出現位置（1-based），不存在返回 0
SELECT LOCATE('hello', 'hello world');  -- 1
SELECT LOCATE('world', 'hello world');  -- 7
SELECT LOCATE('mysql', 'hello world');  -- 0

-- 實戰：篩選包含關鍵詞的記錄
SELECT * FROM articles WHERE LOCATE('performance', content) > 0;

5. 分組聚合：GROUP BY + 聚合函數

-- 每城市用戶數
SELECT city, COUNT(*) AS user_cnt FROM customers GROUP BY city;

-- 多維分組
SELECT city, gender, COUNT(*), AVG(age) FROM customers GROUP BY city, gender;

-- 進階：條件聚合（單次掃描多指標）
SELECT 
    city,
    COUNT(*) AS total,
    SUM(CASE WHEN gender='M' THEN 1 ELSE 0 END) AS male_cnt,
    SUM(CASE WHEN age>=18 THEN 1 ELSE 0 END) AS adult_cnt
FROM customers GROUP BY city;

6. 分組過濾：HAVING vs WHERE

-- WHERE：行級過濾（分組前）
-- HAVING：組級過濾（分組後）

-- 查用戶數 > 100 的城市
SELECT city, COUNT(*) AS cnt FROM customers GROUP BY city HAVING COUNT(*) > 100;

-- 組合：先 WHERE 後 HAVING
SELECT city, COUNT(*) AS cnt 
FROM customers 
WHERE status = 'active' 
GROUP BY city 
HAVING COUNT(*) > 100;

7. 子句執行順序（邏輯順序 ≠ 寫作順序）

-- 邏輯執行順序：
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

-- 寫作順序：
SELECT ... FROM ... JOIN ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT ...

理解邏輯順序能解決「為什麼 SELECT 別名不能用在 WHERE/GROUP BY/HAVING」等疑惑。

8. 去重：DISTINCT 及性能

-- 單列去重
SELECT DISTINCT city FROM customers ORDER BY city;

-- 多列去重（組合唯一）
SELECT DISTINCT city, gender FROM customers;

-- 性能提示：大表 DISTINCT 需排序/哈希，慎用；優先考慮 GROUP BY 或 唯一索引

9. 分頁：LIMIT 偏移與優化

-- 前 10 條
SELECT * FROM articles LIMIT 10;
-- 等價
SELECT * FROM articles LIMIT 0, 10;

-- 第 3 頁（每頁 10 條）
SELECT * FROM articles LIMIT 20, 10;  -- offset=20, rows=10

-- 深分頁優化（百萬級 offset 慢）
-- 方案 1：覆蓋索引 + 子查詢
SELECT * FROM articles 
WHERE id >= (SELECT id FROM articles ORDER BY id LIMIT 100000, 1)
ORDER BY id LIMIT 10;

-- 方案 2：游標分頁（需唯一遞增鍵）
SELECT * FROM articles WHERE id > 100000 ORDER BY id LIMIT 10;

10. 多表查詢：別名、明確列、JOIN 類型

-- 必須使用表別名，消除歧義
SELECT 
    u.username,
    o.order_no,
    p.product_name
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN products p ON o.product_id = p.id
WHERE u.status = 'active';

-- 避免 SELECT *：網絡傳輸、解析、鎖範圍都受影響

附錄：常用技巧速查

熟練掌握這 10 大用法，覆蓋 90% 日常查詢場景，寫出專業級 SELECT 語句。