MySQL 數據檢索全攻略：從 SELECT 基礎到條件查詢、排序、聚合實戰

MySQL SELECT 語句完整實戰指南：基礎語法、條件過濾、多表關聯、排序分頁、聚合統計、常用函數，適合從建表到業務查詢的全階段學習。

一、基礎語法結構

SELECT [DISTINCT] 列名/表達式
FROM 表名 [別名]
[WHERE 條件]
[GROUP BY 分組列]
[HAVING 分組過濾]
[ORDER BY 排序列 [ASC|DESC]]
[LIMIT 偏移量, 行數]

二、核心查詢實戰 (基於示例表 mytable)

-- 表結構: name, sex, birth, birthaddr
-- 數據: abccs/f/1977-07-07/china, mary/f/1978-12-12/usa, tom/m/1970-09-02/usa

1. 全表查詢

SELECT * FROM mytable;
-- 結果: 3 行 4 列

2. 條件查詢 (WHERE)

-- 單條件
SELECT * FROM mytable WHERE name = 'tom';

-- 組合條件 (AND/OR/NOT)
SELECT * FROM mytable WHERE sex = 'f' AND birthaddr = 'china';
-- 結果: abccs 行

-- 模糊匹配
SELECT * FROM mytable WHERE name LIKE 'm%';  -- mary
SELECT * FROM mytable WHERE name LIKE '%a%'; -- mary, abccs

-- 範圍查詢
SELECT * FROM mytable WHERE birth BETWEEN '1970-01-01' AND '1978-12-31';

3. 投影查詢 (指定列)

SELECT name FROM mytable;
SELECT name, birth FROM mytable;

4. 排序 (ORDER BY)

-- 升序 (默認)
SELECT name, birth FROM mytable ORDER BY birth;
-- tom(1973), abccs(1977), mary(1978)

-- 降序
SELECT name, birth FROM mytable ORDER BY birth DESC;
-- mary, abccs, tom

-- 多列排序
SELECT * FROM mytable ORDER BY sex ASC, birth DESC;

5. 聚合統計 (GROUP BY + 聚合函數)

-- 總行數
SELECT COUNT(*) FROM mytable;  -- 3

-- 分組計數
SELECT sex, COUNT(*) FROM mytable GROUP BY sex;
-- f:2, m:1

-- 多維聚合
SELECT sex, birthaddr, COUNT(*), AVG(YEAR(birth)) 
FROM mytable GROUP BY sex, birthaddr;

-- 分組過濾 (HAVING)
SELECT sex, COUNT(*) FROM mytable GROUP BY sex HAVING COUNT(*) > 1;
-- f:2

6. 修正數據 (UPDATE)

UPDATE mytable SET birth = '1973-09-02' WHERE name = 'tom';

三、進階實用技巧

四、性能關鍵提示

• 避免 SELECT *：明確列名，利用覆蓋索引
• WHERE/LIMIT 列建索引：EXPLAIN 驗證 type=ref/range
• 大表分頁優化：WHERE id > last_id LIMIT 10 (游標分頁) 替代 OFFSET
• 聚合索引下推 (ICP)：5.6+ 自動將 WHERE 條件下推到存儲引擎層過濾