超越基礎 CRUD，掌握功能查詢（DML 封裝）、聯合/交叉/子查詢，能解決 80% 的業務報表與數據處理需求。本文以實戰場景為導向，系統講解四大進階查詢類型。
將 SELECT 篩選結果直接作為 UPDATE/DELETE/INSERT/CREATE TABLE 目標，實現批量操作。
| 類型 | 語法模式 | 場景 |
|---|---|---|
| 更新查詢 | UPDATE t SET col=expr WHERE condition; | 批量漲價、狀態流轉 |
| 刪除查詢 | DELETE FROM t WHERE condition; | 清理過期數據、軟刪除轉硬刪除 |
| 追加查詢 | INSERT INTO t(col1,col2) SELECT col1,col2 FROM src WHERE ...; | 數據歸檔、跨庫同步、報表落地 |
| 生成表查詢 | CREATE TABLE new_t AS SELECT ... FROM src ...; | 建立物化視圖、臨時分析表 |
實戰範例：將 2023 年已完成訂單歸檔至 orders_2023：
CREATE TABLE orders_2023 AS
SELECT * FROM orders
WHERE status=3 AND YEAR(created_at)=2023;
-- 驗證後再刪除主表數據
DELETE FROM orders WHERE status=3 AND YEAR(created_at)=2023;
規則：各 SELECT 列數、列順序、類型兼容必須一致。
-- 統計所有渠道用戶註冊量（去重手機號）
SELECT mobile, 'channel_a' AS src FROM channel_a_users
UNION ALL
SELECT mobile, 'channel_b' FROM channel_b_users
UNION ALL
SELECT mobile, 'channel_c' FROM channel_c_users;
MySQL 無原生 PIVOT，用 CASE WHEN + 聚合 模擬行轉列。
-- 統計各部門各季度銷售額
SELECT
dept_name,
SUM(CASE WHEN QUARTER(sale_date)=1 THEN amount ELSE 0 END) AS Q1,
SUM(CASE WHEN QUARTER(sale_date)=2 THEN amount ELSE 0 END) AS Q2,
SUM(CASE WHEN QUARTER(sale_date)=3 THEN amount ELSE 0 END) AS Q3,
SUM(CASE WHEN QUARTER(sale_date)=4 THEN amount ELSE 0 END) AS Q4,
SUM(amount) AS total
FROM sales
JOIN depts ON sales.dept_id = depts.id
WHERE YEAR(sale_date)=2023
GROUP BY dept_name
ORDER BY total DESC;
大數據量交叉查詢建議預計算落地寬表或接入 ClickHouse/Doris。
| 類型 | 位置 | 返回 | 典型運算符 |
|---|---|---|---|
| 標量子查詢 | SELECT/WHERE/HAVING | 單值(1行1列) | =, <>, >, <, >=, <= |
| 列子查詢 | WHERE/HAVING | 單列多行 | IN, NOT IN, ANY, ALL |
| 行子查詢 | WHERE | 單行多列 | =, <>, IN |
| 表子查詢 | FROM/JOIN | 多行多列 | 作為派生表 |
| 相關子查詢 | WHERE/SELECT | 引用外層列 | EXISTS, NOT EXISTS, IN |
實戰範例：
-- 1. 標量：查詢最高薪資員工
SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM employees);
-- 2. IN 列子查詢：有訂單的用戶
SELECT * FROM users WHERE id IN (SELECT DISTINCT user_id FROM orders);
-- 3. EXISTS 相關子查詢（通常比 IN 效率高，可用索引）
SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
-- 4. 派生表：各用戶最新訂單
SELECT u.*, o.amount AS latest_amount
FROM users u
JOIN (
SELECT user_id, amount, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
FROM orders
) o ON u.id = o.user_id AND o.rn = 1;
JOIN 通常有更好執行計劃。JOIN 或應用層組裝。optimizer_switch='derived_merge=on' (默認) 可將衍生表合併到外層查詢，避免物化臨時表。進階查詢的本質是「集合運算思維」：用 UNION 做加法、JOIN 做乘法、WHERE/HAVING 做過濾、GROUP BY 做聚合、子查詢 做邏輯封裝。
