掌握 SELECT 語句是操作 MySQL 的基石。本文以學生表為例，系統講解從基礎查詢到多條件組合、模糊匹配、排序去重的完整語法與實戰技巧。
CREATE TABLE student (
id INT PRIMARY KEY NOT NULL,
name VARCHAR(30),
sex VARCHAR(4),
class VARCHAR(10)
);
INSERT INTO student (id, name, sex, class) VALUES
(01, '張三', '男', '班級1'),
(02, '李四', '男', '班級2'),
(03, '王五', '女', '班級3');| 場景 | 語法示例 | 說明 |
|---|---|---|
| 查詢所有列 | SELECT * FROM student; | 生產環境建議顯式列出列名 |
| 指定列順序 | SELECT name, id, class, sex FROM student; | 結果按 SQL 指定順序返回 |
| 條件篩選 | SELECT id, name FROM student WHERE class='班級1'; | 等值匹配 |
| IN 列表匹配 | SELECT * FROM student WHERE id IN (1,2); | 等同多個 OR |
| 範圍查詢 | SELECT * FROM student WHERE id BETWEEN 1 AND 3; | 包含邊界值 |
| 模糊匹配 % | SELECT * FROM student WHERE class LIKE '班%'; | % 匹配任意長度(含0) |
| 模糊匹配 _ | SELECT * FROM student WHERE class LIKE '_級_'; | _ 匹配單個字符 |
| 空值判斷 | SELECT name FROM student WHERE id IS NULL; | 不可用 = NULL |
| 非空判斷 | SELECT name FROM student WHERE id IS NOT NULL; | |
| AND 多條件 | SELECT * FROM student WHERE sex='男' AND class='班級1'; | 同時滿足 |
| OR 多條件 | SELECT * FROM student WHERE sex='女' OR id=1; | 滿足任一 |
| 去重 | SELECT DISTINCT sex FROM student; | 結果集唯一 |
| 單列排序 | SELECT * FROM student ORDER BY id DESC; | DESC 降序，默認 ASC |
| 多列排序 | SELECT * FROM student ORDER BY id, class; | 先按 id，相同再按 class |
AND 優先於 OR，複雜條件建議用括號明確邏輯：
SELECT * FROM student
WHERE (sex='男' OR sex='女') AND class IN ('班級1','班級2');SELECT * FROM student ORDER BY id LIMIT 0, 10; -- 第一頁 10 條SELECT name AS student_name, CONCAT(class, '-', sex) AS info FROM student;WHERE 條件列加索引可大幅提速。SELECT *，減少網絡傳輸與解析開銷。LIKE '%keyword%' 導致全表掃描，全文搜索請用全文索引或 Elasticsearch。
