查詢基礎：SELECT 語句核心語法
SELECT [DISTINCT] select_list
FROM table_list
[WHERE condition]
[GROUP BY group_list]
[HAVING condition]
[ORDER BY order_list]
[LIMIT offset, count]
數據準備：學生成績表示例
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32),
subject VARCHAR(20),
score INT
);
INSERT INTO student (name, subject, score) VALUES
('Alice', 'Math', 95), ('Alice', 'English', 88),
('Bob', 'Math', 72), ('Bob', 'English', 65),
('Charlie', 'Math', 85), ('Charlie', 'English', 92),
('David', 'PE', 80), ('David', 'PE', 75);
核心查詢場景實戰
1. 基礎投影與過濾
-- 全列查詢
SELECT * FROM student;
-- 指定列
SELECT name, subject, score FROM student;
-- 條件過濾 WHERE
SELECT * FROM student WHERE subject = 'Math';
SELECT * FROM student WHERE score > 80;
SELECT * FROM student WHERE subject = 'PE' AND score > 80;
-- 模糊匹配 LIKE
SELECT * FROM student WHERE name LIKE 'A%'; -- 以 A 開頭
SELECT * FROM student WHERE name LIKE '%li%'; -- 包含 li
SELECT * FROM student WHERE name LIKE '_o%'; -- 第二個字母是 o
2. 排序與分頁
-- 單列排序
SELECT * FROM student ORDER BY score ASC; -- 升序
SELECT * FROM student ORDER BY score DESC; -- 降序
-- 多列排序
SELECT * FROM student ORDER BY subject ASC, score DESC;
-- 分頁 LIMIT (MySQL/PostgreSQL/SQLite)
SELECT * FROM student ORDER BY score DESC LIMIT 5; -- 前 5 名
SELECT * FROM student ORDER BY score DESC LIMIT 5, 5; -- 第 6-10 名 (offset, count)
SELECT * FROM student ORDER BY score DESC LIMIT 5 OFFSET 5; -- 標準語法
3. 集合操作與去重
-- IN 集合查詢
SELECT * FROM student WHERE id IN (1, 2, 3);
-- 範圍查詢 BETWEEN
SELECT * FROM student WHERE score BETWEEN 80 AND 90;
-- 去重 DISTINCT
SELECT DISTINCT name FROM student; -- 唯一姓名
SELECT DISTINCT name, score FROM student; -- 唯一姓名+分數組合
4. 分組聚合
-- 單列分組計數
SELECT subject, COUNT(*) AS cnt FROM student GROUP BY subject;
-- 多維分組
SELECT name, subject, AVG(score) AS avg_score
FROM student
GROUP BY name, subject;
-- 分組過濾 HAVING (聚合後過濾)
SELECT name, AVG(score) AS avg_score
FROM student
GROUP BY name
HAVING AVG(score) > 80;
-- 多聚合函數
SELECT
COUNT(*) AS total_rows,
COUNT(score) AS score_count, -- 忽略 NULL
SUM(score) AS total_score,
AVG(score) AS avg_score,
MIN(score) AS min_score,
MAX(score) AS max_score
FROM student;
5. 進階：條件聚合與窗口函數 (8.0+)
-- 條件聚合：各科及格/不及格人數
SELECT
subject,
SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) AS pass_cnt,
SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS fail_cnt
FROM student
GROUP BY subject;
-- 窗口函數：排名
SELECT
name, subject, score,
ROW_NUMBER() OVER (PARTITION BY subject ORDER BY score DESC) AS rn,
RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS rk,
DENSE_RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS drk
FROM student;
常見聚合函數速查表
| 函數 | 說明 | 忽略 NULL | 示例 |
|---|
| COUNT(*) | 行數 | 否 | COUNT(*) |
| COUNT(col) | 非空值個數 | 是 | COUNT(email) |
| SUM(col) | 求和 | 是 | SUM(score) |
| AVG(col) | 平均值 | 是 | AVG(score) |
| MIN(col) | 最小值 | 是 | MIN(score) |
| MAX(col) | 最大值 | 是 | MAX(score) |
| GROUP_CONCAT | 分組拼接字符串 | 是 | GROUP_CONCAT(name ORDER BY score DESC SEPARATOR ',') |
| STDDEV / VARIANCE | 標準差/方差 | 是 | STDDEV(score) |
實戰避坑指南
| 錯誤寫法 | 問題 | 正確寫法 |
|---|
| SELECT name, COUNT(*) FROM student | 非聚合列未出現在 GROUP BY | SELECT name, COUNT(*) FROM student GROUP BY name |
| WHERE AVG(score) > 80 | 聚合函數不能用在 WHERE | HAVING AVG(score) > 80 |
| LIMIT 10, 5 在 SQL Server | 語法不兼容 | OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY |
| SELECT DISTINCT name, subject FROM student ORDER BY score | ORDER BY 列未在 SELECT | SELECT DISTINCT name, subject, score ... 或子查詢 |
| COUNT(*) 在大表全表掃描 | InnoDB 無計數器 | 近似值用 EXPLAIN rows、精確需覆蓋索引或計數表 |
效能優化提示
- WHERE 條件列加索引：
EXPLAIN 確認 type=ref/range
- ORDER BY/LIMIT 利用索引排序：避免 filesort
- 覆蓋索引：SELECT 列全部在索引中，Extra=Using index
- 避免 SELECT *：增加 IO、網路、緩存壓力
- 大分頁優化：
WHERE id > last_id LIMIT 20 替換 LIMIT 1000000, 20
總結
MySQL 查詢從基礎的 SELECT/WHERE 到進階的 GROUP BY/HAVING/窗口函數，構建了數據分析的完整工具箱。掌握 投影、過濾、排序、分頁、去重、聚合、分組 七大核心操作，配合 EXPLAIN 驗證執行計劃，能解決 95% 的業務查詢需求。建議從簡單查詢入手，逐步掌握聚合與窗口函數，實戰中多觀察執行計劃，養成索引意識。
