MySQL 查詢語句大全：從基礎 SELECT 到複雜連接、子查詢與性能優化實戰

一、基礎查詢核心語法

SELECT [ALL|DISTINCT|DISTINCTROW] 
    {* | table.* | col1 [AS alias1], col2 [AS alias2], ...}
FROM table_name [AS alias]
[WHERE condition]
[GROUP BY col_list [HAVING condition]]
[ORDER BY col_list [ASC|DESC]]
[LIMIT offset, row_count];

1. 選擇列表靈活控制

• SELECT *：全列（生產禁用，網絡/IO/解析開銷大）
• SELECT col1, col2：指定列，順序決定輸出順序
• SELECT col1 AS 別名, col2 別名：重命名，含特殊字符用反引號
• SELECT DISTINCT col1, col2：去重（多列組合去重）
• SELECT TOP n / LIMIT n：限制行數（MySQL 用 LIMIT）

2. WHERE 條件構建大全

3. 排序與分頁

ORDER BY create_time DESC, id ASC  -- 多鍵排序
LIMIT 10 OFFSET 20  -- 跳過 20 行取 10 行（第 3 頁，每頁 10 條）
LIMIT 20, 10        -- 等價寫法

深分頁優化：LIMIT 1000000, 10 極慢 → 改用延遲關聯或游標分頁：

SELECT * FROM t WHERE id > (SELECT id FROM t ORDER BY id LIMIT 1000000, 1) ORDER BY id LIMIT 10;

二、多表連接查詢（JOIN）完全圖解

連接類型對比

標準語法與最佳實踐

-- 內連接（推薦寫法）
SELECT u.name, o.order_no, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.status = 1;

-- 左連接（帶條件注意：ON vs WHERE 差異）
SELECT u.name, o.order_no
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 1  -- 條件在 ON 中：先過濾右表再連接
WHERE u.city = '北京';

-- 多表連接
SELECT u.name, o.order_no, p.product_name, od.quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_details od ON o.id = od.order_id
JOIN products p ON od.product_id = p.id;

ON vs WHERE 陷阱：LEFT JOIN 時，右表過濾條件放 ON 保留左表行，放 WHERE 會隱性轉為內連接。

三、集合運算：UNION 合併結果集

SELECT id, name FROM users_2022 WHERE city='北京'
UNION
SELECT id, name FROM users_2023 WHERE city='北京'
ORDER BY name;  -- 只能在最後 ORDER BY，按第一個 SELECT 列排序

• UNION：去重（預設），性能較差
• UNION ALL：保留重複，**大數據合併首選**，無去重開銷
• 所有 SELECT 列數、類型順序必須一致

四、子查詢：標量、行、表、EXISTS 四大形態

1. 標量子查詢（返回單值）

SELECT * FROM products 
WHERE price > (SELECT AVG(price) FROM products);

2. 行子查詢（返回一行多列）

SELECT * FROM users 
WHERE (city, district) = (SELECT city, district FROM branches WHERE id=1);

3. 表子查詢（返回多行多列，作派生表）

SELECT d.dept_name, e.avg_salary
FROM departments d
JOIN (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) e ON d.id = e.dept_id;

4. EXISTS / NOT EXISTS（相關子查詢，半連接優化）

-- 高性能：只要存在即返回，不掃描全表
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 1000
);

-- 等價 IN 但通常更優（特別是 NOT IN 有 NULL 陷阱）
SELECT * FROM users u
WHERE u.id IN (SELECT user_id FROM orders WHERE amount > 1000);

避坑：NOT IN (子查詢) 若子查詢結果含 NULL，整體返回空 → 改用 NOT EXISTS。

五、高級分析函數（MySQL 8.0+ 窗口函數）

-- 排名
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rk
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS drk

-- 偏移
LAG(salary)  OVER (PARTITION BY dept ORDER BY hire_date) AS prev_salary
LEAD(salary) OVER (PARTITION BY dept ORDER BY hire_date) AS next_salary

-- 聚合
SUM(salary) OVER (PARTITION BY dept) AS dept_total
AVG(salary) OVER (PARTITION BY dept ORDER BY hire_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_avg

六、性能優化核心清單

1. EXPLAIN 分析：關注 type（ALL/index/range/ref/eq_ref/const）、rows、Extra（Using index/Using where/Using temporary/Using filesort）
2. 索引策略：複合索引遵循最左前綴；覆蓋索引避免回表；IS NULL、<>、LIKE '%...' 不走索引
3. 避免 SELECT *：增加網絡、IO、內存、緩存失效
4. 大事務拆分：長事務阻塞鎖、undo log 膨脹
5. 分頁優化：延遲關聯、游標分頁、覆蓋索引
6. 統計信息：ANALYZE TABLE 定期更新，優化器依賴其決策

七、實戰範例：電商訂單統計儀表板查詢

WITH daily_stats AS (
    SELECT 
        DATE(create_time) AS stat_date,
        COUNT(*) AS order_count,
        SUM(amount) AS total_amount,
        AVG(amount) AS avg_amount,
        COUNT(DISTINCT user_id) AS buyer_count
    FROM orders
    WHERE create_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
      AND status IN (1,2,3)  -- 已付款/已發貨/已完成
    GROUP BY DATE(create_time)
),
user_rfm AS (
    SELECT 
        user_id,
        MAX(DATE(create_time)) AS last_order_date,
        COUNT(*) AS frequency,
        SUM(amount) AS monetary
    FROM orders
    WHERE status IN (2,3)
    GROUP BY user_id
)
SELECT 
    d.stat_date,
    d.order_count,
    d.total_amount,
    d.buyer_count,
    COUNT(CASE WHEN u.monetary > 1000 THEN 1 END) AS high_value_buyers
FROM daily_stats d
LEFT JOIN user_rfm u ON d.stat_date = u.last_order_date
GROUP BY d.stat_date
ORDER BY d.stat_date DESC;