萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 基礎查詢教程:SELECT 語句從入門到進階

SELECT 基本語法結構

SELECT [DISTINCT] select_list
FROM table_references
[WHERE where_condition]
[GROUP BY col_list]
[HAVING having_condition]
[ORDER BY col_list [ASC|DESC]]
[LIMIT [offset,] row_count]

核心子句實戰

1. 投影:選擇欄位

-- 全欄位 (生產禁用)
SELECT * FROM users;

-- 指定欄位 + 別名 + 運算
SELECT id, username, email, YEAR(created_at) AS reg_year
FROM users;

2. 過濾:WHERE 條件

-- 比較運算子
WHERE age > 18 AND status = 'active'

-- 範圍
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31'

-- 集合
WHERE city IN ('Taipei', 'Taichung', 'Kaohsiung')

-- 模糊 (索引失效風險)
WHERE name LIKE '張%' -- 前綴可用索引
WHERE name LIKE '%偉' -- 後綴/中間 無法用索引

-- 空值
WHERE phone IS NULL
WHERE phone IS NOT NULL

3. 排序:ORDER BY

-- 多欄位排序
ORDER BY created_at DESC, id ASC

-- 運算式排序
ORDER BY LENGTH(content) DESC

-- NULL 排序控制 (8.0+)
ORDER BY col ASC NULLS FIRST / NULLS LAST

4. 限制:LIMIT 分頁

-- 前 10 筆
LIMIT 10
-- 或
LIMIT 0, 10

-- 第 3 頁,每頁 20 筆
LIMIT 40, 20
-- 或 8.0+
LIMIT 20 OFFSET 40

-- 大偏移量效能問題:配合覆蓋索引或游標分頁
SELECT id FROM t WHERE ... ORDER BY id LIMIT 1000000, 10; -- 慢
-- 優化:記住上一頁最大 id
SELECT * FROM t WHERE id > 1000000 ORDER BY id LIMIT 10;

5. 分組聚合:GROUP BY + HAVING

-- 每個城市用戶數
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city
HAVING COUNT(*) > 100
ORDER BY user_count DESC;

-- 注意:SELECT 列必須在 GROUP BY 或聚合函數中 (ONLY_FULL_GROUP_BY 模式)

6. 多表連接:JOIN

-- 內連接:雙方都有匹配
SELECT u.name, o.order_no
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- 左連接:保留左表所有行
SELECT u.name, o.order_no
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL; -- 找無訂單用戶

進階技巧

  • CTE (8.0+):WITH cte AS (...) SELECT ... 簡化複雜查詢
  • 窗口函數 (8.0+):ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)
  • JSON 查詢:JSON_EXTRACT(data, '$.key'), ->> 操作符
  • EXPLAIN 分析:EXPLAIN FORMAT=JSON SELECT ... 看執行計劃

常見錯誤避坑

  1. WHERE col = NULL → 永無結果,用 IS NULL
  2. NOT IN (subquery) 子查詢含 NULL → 全空結果,改 NOT EXISTS
  3. 隱式類型轉換:WHERE varchar_col = 123 導致索引失效
  4. SELECT * 生產環境禁用:網路傳輸、解析、緩存皆受影響
copyright © 萬盛學電腦網 all rights reserved