邏輯執行順序(非書寫順序):
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT
理解此順序是寫對複雜查詢的前提(如 WHERE 不能用別名,HAVING 可用聚合/別名)。
| 運算符 | 含義 | 示例 |
|---|---|---|
=, !=, <> | 等於、不等於 | WHERE status = 1 |
>, <, >=, <= | 大小比較 | WHERE price BETWEEN 100 AND 200 |
IN (v1,v2...) | 集合成員 | WHERE cat_id IN (1,3,5) |
BETWEEN ... AND ... | 閉區間範圍 | WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31' |
IS NULL / IS NOT NULL | 空值判斷 | WHERE deleted_at IS NULL |
LIKE | 模糊匹配 | WHERE name LIKE '張%', WHERE code LIKE '_A__' |
REGEXP | 正則匹配 | WHERE phone REGEXP '^1[3-9]\d{9}$' |
NOT > AND > OR
-- 建議顯式加括號消除歧義
WHERE (status = 1 AND type = 'A') OR (status = 2 AND type = 'B')
| 函數 | 說明 | 示例 |
|---|---|---|
COUNT(*) | 統計行數(含 NULL) | SELECT COUNT(*) FROM orders |
COUNT(col) | 統計 col 非 NULL 行數 | SELECT COUNT(phone) FROM users |
SUM(col) | 求和 | SELECT SUM(amount) FROM orders |
AVG(col) | 平均值 | SELECT AVG(score) FROM exams |
MAX/MIN(col) | 極值 | SELECT MAX(created_at) FROM logs |
-- 錯誤示範(MySQL ONLY_FULL_GROUP_BY 模式下報錯)
SELECT cat_id, goods_name, MAX(price) FROM goods GROUP BY cat_id;
-- goods_name 未聚合也未在 GROUP BY,結果不確定(取組內第一行)
-- 正確寫法 1:只選聚合列 + 分組列
SELECT cat_id, MAX(price) FROM goods GROUP BY cat_id;
-- 正確寫法 2:需顯示商品名 → 子查詢/窗口函數
SELECT g.* FROM goods g
JOIN (
SELECT cat_id, MAX(price) AS max_price FROM goods GROUP BY cat_id
) m ON g.cat_id = m.cat_id AND g.price = m.max_price;
WHERE:針對**原始表行**過濾,**聚合前**,不可用聚合函數/別名HAVING:針對**分組後結果**過濾,**聚合後**,可用聚合函數/SELECT 別名-- 查詢積壓貨款超過 2 萬的分類
SELECT cat_id, SUM(shop_price * goods_number) AS total_stock_value
FROM goods
GROUP BY cat_id
HAVING total_stock_value > 20000;
ORDER BY col ASC/DESC:默認 ASCORDER BY RAND():極慢,參考《MySQL RAND() 隨機查詢優化》(cat_id, price DESC),且 ORDER BY cat_id, price DESC → **無 filesort**ORDER BY cat_id ASC, price DESC-- 淺分頁(高效)
SELECT * FROM orders ORDER BY id DESC LIMIT 10 OFFSET 0; -- 第 1 頁
-- 深分頁(低效:掃描 OFFSET+N 行)
SELECT * FROM orders ORDER BY id DESC LIMIT 10 OFFSET 1000000;
-- 深分頁優化:延遲關聯 / 書籤分頁
SELECT * FROM orders
WHERE id < (SELECT id FROM orders ORDER BY id DESC LIMIT 1 OFFSET 1000000)
ORDER BY id DESC LIMIT 10;
-- 標量子查詢(返回單值):最新商品
SELECT * FROM goods WHERE goods_id = (SELECT MAX(goods_id) FROM goods);
-- 列子查詢(返回單列多行):每個分類最新商品
SELECT cat_id, goods_id, goods_name
FROM goods
WHERE goods_id IN (SELECT MAX(goods_id) FROM goods GROUP BY cat_id);
-- 查詢掛科≥2門學生的平均分
SELECT name, AVG(score)
FROM student
WHERE name IN (
SELECT name FROM (
SELECT name, COUNT(*) AS fail_cnt
FROM student
WHERE score < 60
GROUP BY name
HAVING fail_cnt >= 2
) AS t
)
GROUP BY name;
MySQL 8.0+ 會自動物化派生表(建立臨時表+索引),性能大幅提升。5.7 需手動優化。
-- 查詢有商品的分類
SELECT cat_id, cat_name
FROM category c
WHERE EXISTS (SELECT 1 FROM goods g WHERE g.cat_id = c.cat_id);
EXISTS 只關心**是否存在**,不返回數據,常比 IN 快(尤其外表大、內表小)NOT EXISTS 替代 NOT IN 避免 NULL 陷阱| 操作 | 語法 | 去重 | 性能 | 適用 |
|---|---|---|---|---|
| UNION | SELECT a FROM t1 UNION SELECT a FROM t2 | 是(排序/哈希去重) | 較慢 | 需唯一結果集 |
| UNION ALL | SELECT a FROM t1 UNION ALL SELECT a FROM t2 | 否 | **快**(無去重) | 允許重複/已知無重複 |
-- 分頁查詢不同分類商品,合併結果
(SELECT * FROM goods WHERE cat_id=3 ORDER BY price DESC LIMIT 3)
UNION ALL
(SELECT * FROM goods WHERE cat_id=4 ORDER BY price DESC LIMIT 3)
ORDER BY price DESC;
-- 統計多表同結構數據
SELECT id, SUM(num) FROM (
SELECT * FROM sales_2023
UNION ALL
SELECT * FROM sales_2024
) AS all_sales
GROUP BY id;
LEFT JOIN:**左表為驅動表**,左表所有行保留,右表無匹配則 NULL 填充RIGHT JOIN:右表驅動,**建議統一改寫為 LEFT JOIN**(可讀性統一)INNER JOIN:僅保留**雙邊匹配**的行(交集)-- 多表連接:商品 + 分類 + 品牌
SELECT g.goods_id, g.goods_name, c.cat_name, b.brand_name
FROM goods g
LEFT JOIN category c ON g.cat_id = c.cat_id
LEFT JOIN brand b ON g.brand_id = b.brand_id
LIMIT 5;
-- 條件下推到 ON vs WHERE
-- 1. ON 中放關聯條件 + 右表過濾條件
SELECT * FROM goods g
LEFT JOIN category c ON g.cat_id = c.cat_id AND c.is_show = 1;
-- 結果:goods 全保留,category 條件不滿足則 NULL
-- 2. WHERE 中放左表過濾 + 關聯後過濾
SELECT * FROM goods g
LEFT JOIN category c ON g.cat_id = c.cat_id
WHERE g.is_on_sale = 1 AND c.is_show = 1;
-- 等同 INNER JOIN(因 c.is_show=1 過濾掉了 NULL 行)
CREATE TABLE team (tid INT PRIMARY KEY, tname VARCHAR(10));
INSERT INTO team VALUES (1,'申花'),(2,'紅牛'),(3,'火箭');
CREATE TABLE match (
id INT PRIMARY KEY, zid INT, kid INT, res VARCHAR(10), mtime DATE
);
INSERT INTO match VALUES
(1,1,2,'2:0','2006-05-21'),
(2,3,2,'2:1','2006-06-21'),
(3,1,3,'2:2','2006-06-11'),
(4,2,1,'2:4','2006-07-01');
-- 需求:輸出 2006-06-01 至 2006-07-01 的比賽,顯示主隊、客隊名稱
SELECT
m.id,
t1.tname AS home_team,
m.res,
t2.tname AS away_team,
m.mtime
FROM `match` m
LEFT JOIN team t1 ON m.zid = t1.tid -- 主隊
LEFT JOIN team t2 ON m.kid = t2.tid -- 客隊
WHERE m.mtime BETWEEN '2006-06-01' AND '2006-07-01';
EXPLAIN 所有高頻 SQL,關注 type、key、rows、ExtraJOIN ON、WHERE、GROUP BY、ORDER BY 列建立**最優複合索引**JOIN → 拆分為多次單表查詢 + 應用層組裝JOIN 或 EXISTSOR → UNION ALLSELECT * → 顯式列名MySQL 查詢能力的核心在於:**理解邏輯執行順序、熟練運用子查詢類型、精準選擇連接方式、堅持優化鐵律**。從「寫出正確結果」進階到「寫出高性能 SQL」,關鍵在於**以執行計劃為鏡,以索引為矛,以業務為盾**,在正確性、性能、可維護性中找到平衡點。