您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL SELECT 語法全攻略：條件、排序、分頁、聚合、多表連接、日期、模糊、變數

編輯：MySQL 資料庫相關知識

基礎語法骨架

SELECT [DISTINCT] 選擇清單
FROM 表來源 (單表/連接/子查詢)
[WHERE 條件]
[GROUP BY 分組欄位 [HAVING 分組條件]]
[ORDER BY 排序欄位 [ASC|DESC]]
[LIMIT [offset,] row_count];

1. 條件過濾 (WHERE)

比較運算子：=, <>, >, <, >=, <=
邏輯運算子：AND, OR, NOT (優先級：NOT > AND > OR)
IN (val1, val2...) / NOT IN
BETWEEN min AND max (含邊界)
IS NULL / IS NOT NULL (NULL 不能用 = 比較)
範例：WHERE score > 95 AND (dept = 'CS' OR dept = 'EE')

2. 排序 (ORDER BY)

ORDER BY birth DESC, last_name ASC;  -- 先出生日期降序，同日期則姓氏升序
-- NULL 預設排在最前 (ASC) 或最後 (DESC)

3. 分頁 (LIMIT)

LIMIT 5;           -- 前 5 行
LIMIT 10, 5;       -- 跳過 10 行，取 5 行 (第 11-15 行)
LIMIT 5 OFFSET 10; -- 同義寫法 (標準 SQL)

4. 輸出列運算與別名

SELECT 17, FORMAT(SQRT(3*3+4*4), 0) AS calc;  -- 17, 5
SELECT CONCAT(first_name, ' ', last_name) AS full_name,
       CONCAT(city, ',', state) AS birth_place
FROM president;

5. 日期操作

格式：YYYY-MM-DD (年在前)
函數：YEAR(), MONTH(), DAYOFMONTH(), DAYNAME(), TO_DAYS()
範例：WHERE MONTH(birth)=3 AND DAYOFMONTH(birth)=6 (3 月 6 日出生)
間隔天數：TO_DAYS(death) - TO_DAYS(birth) AS age_days
相對日期：WHERE expiration > CURDATE() AND expiration < DATE_ADD(CURDATE(), INTERVAL 60 DAY)

6. 模糊查詢 (LIKE)

%：任意長度字元 (含 0)
_：單一字元
WHERE last_name LIKE 'W%' (W 開頭)
WHERE last_name LIKE '%W%' (含 W)
效能：前導 % 無法使用索引，後導 % 可用前綴索引

7. 使用者變數

SELECT @birth := birth FROM president WHERE last_name = 'Adams';
SELECT * FROM president WHERE birth < @birth ORDER BY birth;

8. 聚合統計

COUNT(*) 計算所有行 (含 NULL)；COUNT(col) 排除 NULL
DISTINCT 去重：SELECT DISTINCT state FROM president
GROUP BY 分組統計：SELECT sex, COUNT(*) FROM student GROUP BY sex
進階：SELECT state, COUNT(*) AS cnt FROM president GROUP BY state ORDER BY cnt DESC LIMIT 4 (產生總統最多的 4 州)

9. 多表連接 (JOIN)

-- 內連接 (交集)
SELECT s.student_id, e.date, sc.score, e.type
FROM score sc
JOIN event e ON sc.event_id = e.event_id
WHERE e.date = '2003-09-12';

-- 三表連接
SELECT st.name, e.name, sc.score, e.type
FROM student st
JOIN score sc ON st.student_id = sc.student_id
JOIN event e ON sc.event_id = e.event_id
WHERE e.date = '2003-09-12';

-- 左連接 (保留左表)
SELECT st.student_id, st.name, COUNT(a.date) AS absences
FROM student st
LEFT JOIN absence a ON st.student_id = a.student_id
GROUP BY st.student_id;