SELECT 基本語法結構
SELECT [DISTINCT] select_list
FROM table_references
[WHERE where_condition]
[GROUP BY col_list]
[HAVING having_condition]
[ORDER BY col_list [ASC|DESC]]
[LIMIT [offset,] row_count]
核心子句實戰
1. 投影:選擇欄位
-- 全欄位 (生產禁用)
SELECT * FROM users;
-- 指定欄位 + 別名 + 運算
SELECT id, username, email, YEAR(created_at) AS reg_year
FROM users;
2. 過濾:WHERE 條件
-- 比較運算子
WHERE age > 18 AND status = 'active'
-- 範圍
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'
-- 集合
WHERE city IN ('Taipei', 'Taichung', 'Kaohsiung')
-- 模糊 (索引失效風險)
WHERE name LIKE '張%' -- 前綴可用索引
WHERE name LIKE '%偉' -- 後綴/中間 無法用索引
-- 空值
WHERE phone IS NULL
WHERE phone IS NOT NULL
3. 排序:ORDER BY
-- 多欄位排序
ORDER BY created_at DESC, id ASC
-- 運算式排序
ORDER BY LENGTH(content) DESC
-- NULL 排序控制 (8.0+)
ORDER BY col ASC NULLS FIRST / NULLS LAST
4. 限制:LIMIT 分頁
-- 前 10 筆
LIMIT 10
-- 或
LIMIT 0, 10
-- 第 3 頁,每頁 20 筆
LIMIT 40, 20
-- 或 8.0+
LIMIT 20 OFFSET 40
-- 大偏移量效能問題:配合覆蓋索引或游標分頁
SELECT id FROM t WHERE ... ORDER BY id LIMIT 1000000, 10; -- 慢
-- 優化:記住上一頁最大 id
SELECT * FROM t WHERE id > 1000000 ORDER BY id LIMIT 10;
5. 分組聚合:GROUP BY + HAVING
-- 每個城市用戶數
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 100
ORDER BY user_count DESC;
-- 注意:SELECT 列必須在 GROUP BY 或聚合函數中 (ONLY_FULL_GROUP_BY 模式)
6. 多表連接:JOIN
-- 內連接:雙方都有匹配
SELECT u.name, o.order_no
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- 左連接:保留左表所有行
SELECT u.name, o.order_no
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL; -- 找無訂單用戶
進階技巧
- CTE (8.0+):
WITH cte AS (...) SELECT ... 簡化複雜查詢
- 窗口函數 (8.0+):
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)
- JSON 查詢:
JSON_EXTRACT(data, '$.key'), ->> 操作符
- EXPLAIN 分析:
EXPLAIN FORMAT=JSON SELECT ... 看執行計劃
常見錯誤避坑
WHERE col = NULL → 永無結果,用 IS NULL
NOT IN (subquery) 子查詢含 NULL → 全空結果,改 NOT EXISTS
- 隱式類型轉換:
WHERE varchar_col = 123 導致索引失效
SELECT * 生產環境禁用:網路傳輸、解析、緩存皆受影響