MySQL 查詢大全：基礎查詢、連接查詢、子查詢、正則表達式完整教程

準備數據

CREATE TABLE student (
  stu_id INT PRIMARY KEY,
  stu_name CHAR(10) NOT NULL,
  stu_age SMALLINT UNSIGNED NOT NULL,
  stu_sex CHAR(2) NOT NULL
);

CREATE TABLE grade (
  stu_id INT NOT NULL,
  stu_score INT,
  FOREIGN KEY (stu_id) REFERENCES student(stu_id)
);

一、基礎查詢語法

SELECT 屬性列表
FROM 表名/視圖
[WHERE 條件]
[GROUP BY 分組列 [HAVING 分組條件]]
[ORDER BY 排序列 [ASC|DESC]]
[LIMIT 偏移, 行數]

1. 全列查詢

SELECT * FROM student;

2. 條件查詢運算符

3. 通配符詳解

• %：匹配任意長度字符（含 0 長度）
• _：匹配單個字符（中文佔 2-3 字節，需多個 _）

二、聚合函數與分組

五大聚合函數

HAVING 過濾分組

SELECT emp_dep, AVG(emp_salary), GROUP_CONCAT(emp_name)
FROM employees
GROUP BY emp_dep
HAVING AVG(emp_salary) >= 6000;

WHERE vs HAVING：WHERE 過濾行（聚合前），HAVING 過濾組（聚合後）。

三、多表連接查詢

1. 內連接

-- 隱式
SELECT s.stu_id, s.stu_name, g.stu_score
FROM student s, grade g
WHERE s.stu_id = g.stu_id AND g.stu_score >= 90;

-- 顯式 (推薦)
SELECT s.stu_id, s.stu_name, g.stu_score
FROM student s
INNER JOIN grade g ON s.stu_id = g.stu_id
WHERE g.stu_score >= 90;

2. 外連接

-- 左連接：學生表全保留，成績無則 NULL
SELECT s.stu_id, s.stu_name, g.stu_score
FROM student s
LEFT JOIN grade g ON s.stu_id = g.stu_id;

-- 右連接：成績表全保留
SELECT s.stu_id, s.stu_name, g.stu_score
FROM student s
RIGHT JOIN grade g ON s.stu_id = g.stu_id;

四、子查詢

1. IN 子查詢

SELECT * FROM student
WHERE stu_id IN (SELECT stu_id FROM grade WHERE stu_score > 85);

2. EXISTS 子查詢

SELECT * FROM student
WHERE EXISTS (SELECT 1 FROM grade WHERE stu_score >= 100);

若子查詢返回任意行，EXISTS 為真。

3. ANY / ALL / SOME

-- ANY: 滿足任一值
SELECT * FROM student
WHERE stu_id > ANY (SELECT stu_id FROM grade WHERE stu_score < 67);

-- ALL: 滿足所有值
SELECT * FROM student
WHERE stu_id > ALL (SELECT stu_id FROM grade WHERE stu_score < 67);

五、結果集合並

SELECT stu_id FROM student WHERE stu_age < 13
UNION
SELECT stu_id FROM student WHERE stu_sex = '女';
-- UNION 去重，UNION ALL 保留重複

六、排序與分頁

ORDER BY stu_score DESC       -- 降序
ORDER BY stu_score ASC        -- 升序(默認)
ORDER BY RAND()               -- 隨機(大表慎用)

LIMIT 5                       -- 前5行
LIMIT 10, 5                   -- 跳過10行取5行 (第11-15行)

七、別名

-- 列別名
SELECT stu_id AS '學號', stu_score AS '分數' FROM grade;

-- 表別名
SELECT s.stu_id, s.stu_name, g.stu_score
FROM student s, grade g
WHERE s.stu_id = g.stu_id;

八、正則表達式查詢 (REGEXP)

實戰示例

-- 以數字開頭
SELECT * FROM student WHERE stu_name REGEXP '^[0-9]';

-- 包含字母和數字
SELECT * FROM student WHERE stu_name REGEXP '[a-zA-Z0-9]';

-- 連續兩個 o
SELECT * FROM student WHERE stu_name REGEXP 'o{2}';

九、性能提示

1. EXPLAIN 分析執行計劃，關注 type、key、rows、Extra
2. 避免 SELECT *，只查所需列
3. LIKE '%keyword' 導致全表掃描，全文檢索用全文索引或 ES
4. REGEXP 不走普通索引，大表慎用
5. 適當添加組合索引，遵循最左前綴

本文涵蓋 MySQL 查詢 90% 以上常用場景，建議收藏作為速查手冊，實戰中結合 EXPLAIN 與慢查詢日誌持續優化。