一、高頻關鍵字與子句
| 關鍵字 | 作用 | 範例 |
|---|
| DISTINCT | 去重 | SELECT DISTINCT dept FROM emp; |
| COUNT(*) | 行數統計 | SELECT COUNT(*) FROM orders WHERE status=1; |
| LIMIT | 分頁/取前 N | SELECT * FROM logs ORDER BY id DESC LIMIT 10; |
| LIKE | 模糊查詢 | SELECT * FROM products WHERE name LIKE '%手機%'; |
| BETWEEN ... AND | 範圍查詢 | SELECT * FROM orders WHERE amount BETWEEN 100 AND 500; |
| IN | 集合包含 | SELECT * FROM users WHERE id IN (1,5,10); |
| ORDER BY | 排序 | SELECT * FROM products ORDER BY price DESC; |
| GROUP BY | 分組聚合 | SELECT dept, AVG(salary) FROM emp GROUP BY dept; |
| HAVING | 分組後過濾 | SELECT dept, COUNT(*) c FROM emp GROUP BY dept HAVING c>5; |
二、多表連接查詢（JOIN）
| 類型 | 語法 | 結果集 | 圖示 |
|---|
| INNER JOIN | A JOIN B ON A.id=B.a_id | 交集（雙方都有匹配） | 🔴🔵 重疊 |
| LEFT JOIN | A LEFT JOIN B ON A.id=B.a_id | A 全集 + B 匹配（無則 NULL） | 🔴 全 + 🔵 部分 |
| RIGHT JOIN | A RIGHT JOIN B ON A.id=B.a_id | B 全集 + A 匹配 | 🔵 全 + 🔴 部分 |
| FULL JOIN | A FULL JOIN B ON A.id=B.a_id | 並集（MySQL 不直接支持，用 UNION 模擬） | 🔴🔵 全 |
FULL JOIN 模擬
SELECT * FROM A LEFT JOIN B ON A.id=B.a_id
UNION
SELECT * FROM A RIGHT JOIN B ON A.id=B.a_id WHERE A.id IS NULL;
三、分頁優化模式
-- 分組去重保留最新
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY create_time DESC) rn
FROM logs
) t WHERE rn = 1;
-- 批量插入去重
INSERT IGNORE INTO users (email, name) VALUES ('a@b.com','A'), ('c@d.com','C');
-- 間隙鎖死鎖排查
SHOW ENGINE INNODB STATUS\G -- 看 LATEST DETECTED DEADLOCK
-- 查看表大小
SELECT table_name,
ROUND(data_length/1024/1024,2) data_mb,
ROUND(index_length/1024/1024,2) idx_mb
FROM information_schema.tables
WHERE table_schema='db_name' ORDER BY data_mb DESC;
