student(id, name, birth, department, address, sex)
| 分類 | 語法示例 | 說明 |
|---|---|---|
| 多列查詢 | SELECT id, name, birth FROM student; | 指定列，避免SELECT * |
| 全列查詢 | SELECT * FROM student; | 僅調試使用 |
| 精確匹配 | WHERE id=901 | 主鍵/唯一索引最快 |
| 範圍查詢 | WHERE id>=904 | 利用索引範圍掃描 |
| 集合查詢 | WHERE birth IN (1988,1990) | 等值多選，優化器轉為OR |
| 非集合查詢 | WHERE birth NOT IN (1990,1998) | 注意NULL值陷阱 |
| 區間查詢 | WHERE birth BETWEEN 1986 AND 1988 | 包含邊界，等價>= AND < |
| 字符模糊 | WHERE name LIKE '_三' / '張%' | _單字符 %多字符，前綴可用索引 |
| 非匹配 | WHERE name NOT LIKE '張%' | 全表掃描，慎用 |
| NULL判斷 | WHERE address IS NULL | 不可用 = NULL |
| 多條件AND | WHERE name LIKE '張%' AND birth>1985 | 索引合併/複合索引 |
| 多條件OR | WHERE id=905 OR birth=1988 | 通常全表掃描，考慮UNION ALL改寫 |
| 去重 | SELECT DISTINCT sex FROM student; | 單列/多列去重 |
| 排序 | ORDER BY birth DESC | ASC升序 DESC降序，利用索引避免filesort |
| 分組聚合 | SELECT sex, COUNT(*) FROM student GROUP BY sex; | 配合聚合函數 |
| 正則查詢 | WHERE birth REGEXP '1988|1990' | MySQL 8.0支持ICU正則，性能較差 |
| 分頁限制 | LIMIT 2 / LIMIT 1,3 | 偏移量,行數；深分頁優化用延遲關聯 |
SELECT id, name FROM student WHERE birth=1990若有INDEX(birth, id, name)則無回表SELECT * FROM t WHERE id > (SELECT id FROM t ORDER BY id LIMIT 100000, 1) LIMIT 20避免大偏移COALESCE(col, '默認值')、IFNULL(col, 0)基礎查詢是SQL的字母表，熟練組合拼湊出高性能業務查詢，需配合EXPLAIN持續驗證執行計劃。
