MySQL 基礎查詢完整指南：SELECT 語法、條件、分組、排序、LIMIT、子查詢、UNION、正則表達式實戰

MySQL 基礎查詢完整指南：SELECT 語法、條件、分組、排序、LIMIT、子查詢、UNION、正則表達式實戰

MySQL SELECT 語法靈活強大，支援標準 SQL 並擴充實用功能（如 LIMIT、REGEXP、GROUP_CONCAT）。本文以實戰範例系統講解查詢核心子句與進階技巧。

基本語法架構

SELECT 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]

建立測試資料

CREATE TABLE fruits (
  f_id CHAR(10) NOT NULL PRIMARY KEY,
  s_id INT NOT NULL,
  f_name CHAR(255) NOT NULL,
  f_price DECIMAL(8,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO fruits(f_id,s_id,f_name,f_price) VALUES
('a1',101,'apple',5.2), ('b1',102,'blackberry',5.2),
('bs1',105,'orange',5.2), ('bs2',103,'melon',5.2),
('t1',106,'banana',5.2);

一、基礎查詢與條件過濾

-- 全欄位
SELECT * FROM fruits;

-- 指定欄位
SELECT f_id, f_name FROM fruits;

-- 比較運算子
SELECT f_id, f_name FROM fruits WHERE f_price > 5.1;

-- IN / NOT IN
SELECT f_id, f_name FROM fruits WHERE s_id IN (101,102);
SELECT f_id, f_name FROM fruits WHERE s_id NOT IN (101,102);

-- BETWEEN / NOT BETWEEN
SELECT f_id, f_name FROM fruits WHERE f_price BETWEEN 2 AND 10;
SELECT f_id, f_name FROM fruits WHERE f_price NOT BETWEEN 2 AND 10;

二、LIKE 字元模糊比對

-- % 匹配任意長度（含 0）
SELECT f_id, f_name FROM fruits WHERE f_name LIKE 'b%y';  -- blackberry

-- _ 匹配單一字元
SELECT f_id, f_name FROM fruits WHERE f_name LIKE '____n';  -- melon, orange

三、NULL 值判斷

CREATE TABLE customers (
  c_id INT AUTO_INCREMENT PRIMARY KEY,
  c_name CHAR(25) NOT NULL,
  c_city CHAR(50) NULL
);

INSERT INTO customers(c_name,c_city) VALUES('liming','china'), ('hongfang',NULL);

SELECT * FROM customers WHERE c_city IS NULL;
SELECT * FROM customers WHERE c_city IS NOT NULL;

四、邏輯運算子與去重

SELECT f_id, f_name FROM fruits WHERE f_name LIKE '____n' AND f_id='bs2';
SELECT f_id, f_name FROM fruits WHERE f_name LIKE '____n' OR f_id='bs2';
SELECT DISTINCT s_id FROM fruits;

五、分組彙總與 HAVING

-- 基礎分組計數
SELECT s_id, COUNT(1) AS total FROM fruits GROUP BY s_id;

-- GROUP_CONCAT 顯示分組明細（MySQL 特有，極實用）
SELECT s_id, GROUP_CONCAT(f_name) AS names FROM fruits GROUP BY s_id;

-- HAVING 過濾分組
SELECT s_id, GROUP_CONCAT(f_name) AS names FROM fruits GROUP BY s_id HAVING COUNT(f_name) > 1;

-- WITH ROLLUP 產生總計行
SELECT s_id, COUNT(1) AS total FROM fruits GROUP BY s_id WITH ROLLUP;

注意：WITH ROLLUP 不能與 ORDER BY 同時使用。

六、LIMIT 分頁

-- 前 5 筆
SELECT * FROM fruits LIMIT 5;

-- 跳過前 4 筆，取 3 筆（第 5~7 筆）
SELECT * FROM fruits LIMIT 4, 3;

-- MySQL 5.6+ 等價寫法
SELECT * FROM fruits LIMIT 3 OFFSET 4;

對比 SQL Server 2012+：ORDER BY s_id OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY（需先排序）。

七、子查詢與 ANY/ALL

CREATE TABLE tbl1(num1 INT);
CREATE TABLE tbl2(num2 INT);
INSERT INTO tbl1 VALUES(1),(4),(13),(27);
INSERT INTO tbl2 VALUES(6),(14),(11),(20);

-- ANY：大於子查詢任一值即符合
SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);
-- 結果：13, 27

-- ALL：大於子查詢所有值才符合
SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2);
-- 結果：27

八、合併查詢 UNION

SELECT s_id, f_name, f_price FROM fruits WHERE f_price < 9.0
UNION
SELECT s_id, f_name, f_price FROM fruits WHERE s_id IN (101,103);

• UNION：去重、預設排序。
• UNION ALL：不去重、不排序、效能更好。
• 兩查詢欄位數、型別必須對應。

九、正則表達式 REGEXP（MySQL 強項，SQL Server 無內建）

十、實用技巧總結

1. GROUP_CONCAT 替代應用層拼接分組明細。
2. LIMIT offset, count 分頁簡潔，大偏移量優化用 WHERE id > last_id LIMIT N。
3. REGEXP 處理複雜字元模式，效能較 LIKE 低，建議配合索引前綴或全文索引。
4. UNION ALL 替代 UNION 除非需去重。
5. 子查詢優先改寫為 JOIN，MySQL 最佳化器對子查詢優化較弱。
6. 條件判斷用 CASE WHEN，避免應用層多次查詢。