萬盛學電腦網

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

MySQL 查詢、子查詢與連接查詢完全指南:從語法到優化實戰

一、SELECT 查詢五大核心子句執行順序

邏輯執行順序(非書寫順序):

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

理解此順序是寫對複雜查詢的前提(如 WHERE 不能用別名,HAVING 可用聚合/別名)。

二、WHERE 條件構建全攻略

1. 比較運算符

運算符含義示例
=, !=, <>等於、不等於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}$'

2. 邏輯運算符優先級

NOT > AND > OR
-- 建議顯式加括號消除歧義
WHERE (status = 1 AND type = 'A') OR (status = 2 AND type = 'B')

三、GROUP BY 分組聚合:五大聚合函數與常見誤區

函數說明示例
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

⚠️ 關鍵誤區:非聚合列未出現在 GROUP BY 中

-- 錯誤示範(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;

HAVING vs WHERE

  • 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 排序優化

  • ORDER BY col ASC/DESC:默認 ASC
  • ORDER BY RAND():極慢,參考《MySQL RAND() 隨機查詢優化》
  • 索引消除排序:若查詢走索引 (cat_id, price DESC),且 ORDER BY cat_id, price DESC → **無 filesort**
  • 多鍵排序:ORDER BY cat_id ASC, price DESC

五、LIMIT 分頁:淺分頁 vs 深分頁

-- 淺分頁(高效)
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;

六、子查詢三大類型與優化策略

1. WHERE 型(標量/列/行子查詢)

-- 標量子查詢(返回單值):最新商品
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. FROM 型(派生表/物化表)

-- 查詢掛科≥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 需手動優化。

3. EXISTS 型(相關子查詢,半連接)

-- 查詢有商品的分類
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 集合操作

操作語法去重性能適用
UNIONSELECT a FROM t1 UNION SELECT a FROM t2是(排序/哈希去重)較慢需唯一結果集
UNION ALLSELECT 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 / RIGHT / INNER JOIN

核心概念:驅動表 vs 被驅動表

  • 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';

九、優化實戰檢查清單

  1. EXPLAIN 所有高頻 SQL,關注 type、key、rows、Extra
  2. 為 JOIN ON、WHERE、GROUP BY、ORDER BY 列建立**最優複合索引**
  3. 大表 JOIN → 拆分為多次單表查詢 + 應用層組裝
  4. 子查詢 → 優先改寫為 JOIN 或 EXISTS
  5. OR → UNION ALL
  6. SELECT * → 顯式列名
  7. 深分頁 → 書籤分頁 / 延遲關聯
  8. 隱式類型轉換 → 參數類型與列類型嚴格一致

結語

MySQL 查詢能力的核心在於:**理解邏輯執行順序、熟練運用子查詢類型、精準選擇連接方式、堅持優化鐵律**。從「寫出正確結果」進階到「寫出高性能 SQL」,關鍵在於**以執行計劃為鏡,以索引為矛,以業務為盾**,在正確性、性能、可維護性中找到平衡點。

copyright © 萬盛學電腦網 all rights reserved