MySQL SQL 進階應用：排序、分組、連接、子查詢、DML 增強與內建函數實戰

核心查詢增強語法

ORDER BY 多欄位排序

SELECT * FROM play_list ORDER BY createtime;
SELECT * FROM play_list ORDER BY bookedcount DESC, createtime ASC;

DESC 僅作用於緊前欄位，多欄位需逐個指定排序方向。

DISTINCT 去重

SELECT DISTINCT userid FROM play_list;
SELECT DISTINCT userid, play_name FROM play_list; -- 組合去重

⚠️ 底層需排序，大表慎用，消耗 CPU。

GROUP BY + HAVING 分組過濾

SELECT userid, COUNT(*) AS play_num
FROM play_list
GROUP BY userid
HAVING COUNT(*) >= 2;

• 分組欄位必須出現在 SELECT 中
• 聚合函數配合使用（COUNT/SUM/AVG/MAX/MIN）
• HAVING 過濾分組結果，WHERE 過濾原始行

LIKE 模糊匹配

⚠️ %xxx% 導致全表掃描，大表禁用；xxx% 可用索引。

LIMIT 分頁

SELECT * FROM play_list
WHERE createtime BETWEEN 1427701323 AND 1430383307
LIMIT 10 OFFSET 6;

⚠️ OFFSET 過大需掃描前 N 行，深分頁效能差，建議游標式分頁（WHERE id > last_id LIMIT 10）。

CASE WHEN 條件輸出

SELECT play_name,
       CASE WHEN trackcount IS NULL THEN 0 ELSE trackcount END AS track_cnt
FROM play_list;

多表連接（JOIN）

內連接 vs 外連接

-- 內連接（僅匹配行）
SELECT f.userid
FROM play_fav f
INNER JOIN play_list l ON f.play_id = l.id
WHERE l.play_name = '老男孩';

-- 左連接（保留左表所有行）
SELECT l.play_name, f.userid
FROM play_list l
LEFT JOIN play_fav f ON l.id = f.play_id;

子查詢 vs JOIN 效能差異

-- 子查詢（優化器受限）
SELECT userid FROM play_fav
WHERE play_id = (SELECT id FROM play_list WHERE play_name = '老男孩');

-- JOIN（優化器可選驅動表）
SELECT f.userid FROM play_fav f
JOIN play_list l ON f.play_id = l.id
WHERE l.play_name = '老男孩';

原理：JOIN 採用 Nested Loop，優化器會自動選擇小表作驅動表；子查詢固定內外表順序，難以優化。

集合運算 UNION

SELECT userid FROM play_list
UNION
SELECT userid FROM play_fav; -- 預設去重

SELECT userid FROM play_list
UNION ALL
SELECT userid FROM play_fav; -- 保留重複

DML 進階語法

連表 UPDATE / DELETE

-- 連表更新：用 B 表 age 更新 A 表
UPDATE a, b SET a.age = b.age WHERE a.id = b.id;

-- 連表刪除：用 B 表條件刪 A 表
DELETE a FROM a, b WHERE a.id = b.id AND b.name = 'pw';

內建函數實戰

聚合函數

• COUNT/SUM/AVG/MAX/MIN：多對一
• GROUP_CONCAT(col)：分組內字符串拼接，預設 1024 字節上限（可調 group_concat_max_len）

-- 錯誤：非聚合欄位未分組
SELECT song_name, MAX(playcount) FROM song_list;

-- 正確：子查詢或排序取 TOP1
SELECT song_name, playcount FROM song_list ORDER BY playcount DESC LIMIT 1;

行列轉換實戰

SELECT album, GROUP_CONCAT(song_name) FROM song_list GROUP BY album;

預定義函數（單值處理，一對一）

• 字符串：CONCAT, SUBSTRING, REPLACE, TRIM, UPPER/LOWER, LENGTH
• 數值：ROUND, CEIL, FLOOR, ABS, MOD, RAND
• 日期時間：NOW, DATE, TIME, DATE_FORMAT, DATE_ADD/SUB, DATEDIFF, UNIX_TIMESTAMP, FROM_UNIXTIME
• 流程控制：IF, IFNULL, NULLIF, CASE WHEN

日期時間格式化符號

總結

• 掌握 ORDER BY/GROUP BY/HAVING/LIMIT 組合查詢
• 優先 JOIN 替代子查詢，利用優化器
• 善用 UNION/UNION ALL 整合異構數據
• DML 進階語法簡化業務邏輯（Upsert、批量、忽略）
• 連表 UPDATE/DELETE 處理跨表業務
• 聚合與預定義函數結合完成複雜統計