MySQL 有很多高級(jí)但實(shí)用的功能，能讓你的查詢變得更簡潔、更高效。

今天分享 10 個(gè)我在工作中經(jīng)常使用的 SQL 技巧，不用死記硬背，掌握了就能立刻提升你的數(shù)據(jù)庫操作水平！

1. CTE（WITH子句）——讓復(fù)雜查詢變清晰

-- 傳統(tǒng)子查詢，難以閱讀
SELECT nickname 
FROM system_users 
WHERE dept_id IN (
    SELECT id FROM system_dept WHERE `name` = 'IT部'
);
-- 使用CTE，邏輯清晰
WITH ny_depts AS (
    SELECT id FROM system_dept WHERE `name` = 'IT部'
)
SELECT u.nickname
FROM system_users u
JOIN ny_depts nd ON u.dept_id = nd.id;

解釋：

• WITH ny_depts AS (...)：先創(chuàng)建一個(gè)臨時(shí)結(jié)果集，叫 ny_depts，里面只包含“IT部”的部門名稱。
• SELECT u.nickname FROM system_users u JOIN ny_depts...：再從用戶表中找出那些部門ID在ny_depts里的員工昵稱。

好處：把找部門和找人分成兩步，邏輯更清楚，比嵌套子查詢好讀多了。

2. 窗口函數(shù) —— 不分組也能統(tǒng)計(jì)

SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
    AVG(salary) OVER (PARTITION BY department) AS avg_salary
FROM employees;

解釋：

• PARTITION BY department：按部門“分組”，但不合并行，每行仍然保留。
• RANK() OVER (...)：在每個(gè)部門內(nèi)部，按薪水從高到低排名（相同薪水并列）。
• AVG(salary) OVER (...)：計(jì)算每個(gè)部門的平均工資，并顯示在每一行里。

對比 GROUP BY：GROUP BY 會(huì)把多行合并成一行，而窗口函數(shù)保留原始行，同時(shí)加上統(tǒng)計(jì)值。

3. 條件聚合 —— 一行查出多個(gè)統(tǒng)計(jì)

SELECT 
    YEAR(created_at) AS year,
    COUNT(*) AS total,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed,
    SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS revenue
FROM orders
GROUP BY YEAR(created_at);

解釋：

• YEAR(created_at)：提取訂單年份。
• COUNT(*)：該年總訂單數(shù)。
• COUNT(CASE WHEN status = 'completed' THEN 1 END)： 如果狀態(tài)是 'completed'，就返回 1，否則返回 NULL；
• COUNT() 只統(tǒng)計(jì)非 NULL 值，所以這行就是“完成的訂單數(shù)”。
• SUM(CASE WHEN ... THEN amount ELSE 0 END)：只對完成的訂單求金額總和。

關(guān)鍵：不用寫多個(gè)子查詢，一條語句搞定全年報(bào)表！

4. 自連接 —— 同一張表自己連自己

SELECT e1.name, e2.name
FROM employees e1
JOIN employees e2 
  ON e1.department = e2.department
  AND e1.id < e2.id  
  AND ABS(e1.salary - e2.salary) <= e1.salary * 0.1;

解釋：

• employees e1 JOIN employees e2：把員工表當(dāng)成兩個(gè)副本（e1 和 e2）來連接。
• e1.department = e2.department：只找同一個(gè)部門的人。
• e1.id < e2.id：避免重復(fù)配對（比如 Alice-Bob 和 Bob-Alice 只保留一個(gè)）。
• ABS(...)：計(jì)算兩人薪水差是否 ≤ 10%。

用途：找“相似記錄”“配對關(guān)系”“上下級(jí)”等場景非常有用。

5.EXISTS替代IN—— 更高效的存在判斷

SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.customer_id = c.id AND o.amount > 1000
);

解釋：

• 對每一位客戶 c，檢查是否存在一筆訂單滿足：
  • 訂單的 customer_id 等于這個(gè)客戶的 id
  • 訂單金額 > 1000
• SELECT 1：這里不需要返回具體字段，只要知道“有沒有”就行，所以用 1 最輕量。
• 為什么快？：一旦找到一條匹配訂單，就立刻停止搜索，不像 IN 可能要加載全部訂單 ID。

注意：如果子查詢可能返回 NULL，IN 會(huì)失效（因?yàn)?x IN (..., NULL) 永遠(yuǎn)為 UNKNOWN），而 EXISTS 不受影響。

6. JSON 函數(shù) —— 輕松讀取 JSON 字段

SELECT 
    name,
    profile->>'$.address.city' AS city,
    JSON_EXTRACT(profile, '$.age') AS age
FROM users
WHERE profile->>'$.city' = 'Beijing';

解釋：

• profile 是一個(gè) JSON 類型字段，比如：{"address": {"city": "Beijing"}, "age": 30}
• profile->>'$.address.city'：
  • ->> 是簡寫，等價(jià)于 JSON_UNQUOTE(JSON_EXTRACT(...))
  • 返回字符串 "Beijing"（去掉引號(hào)）
• JSON_EXTRACT(profile, '$.age')：返回 30（帶類型，可能是數(shù)字）
• WHERE profile->>'$.city' = 'Beijing'：篩選城市是北京的用戶。

適用場景：用戶偏好、動(dòng)態(tài)表單、日志等結(jié)構(gòu)不固定的字段。

7. 生成列 —— 數(shù)據(jù)庫自動(dòng)幫你算

CREATE TABLE products (
    id INT PRIMARY KEY,
    width DECIMAL(10,2),
    height DECIMAL(10,2),
    area DECIMAL(10,2) AS (width * height) STORED
);
INSERT INTO products (id, width, height) VALUES (1, 5, 10);

解釋：

• area DECIMAL(...) AS (width * height) STORED：
  • 這是一個(gè)“存儲(chǔ)型生成列”，數(shù)據(jù)庫會(huì)自動(dòng)計(jì)算 width * height 并存下來。
  • 如果不加 STORED，就是“虛擬列”（每次查詢時(shí)計(jì)算，不占存儲(chǔ)）。
• 插入時(shí)只需給 width 和 height，area 自動(dòng)變成 50。

優(yōu)勢：避免應(yīng)用層重復(fù)計(jì)算，還能給 area 加索引加速查詢！

8. 多表更新 —— 一條語句更新關(guān)聯(lián)數(shù)據(jù)

UPDATE customers c
JOIN (
    SELECT customer_id, SUM(amount) AS total
    FROM orders
    GROUP BY customer_id
) o ON c.id = o.customer_id
SET c.total_spent = o.total;

解釋：

• 子查詢 o：先按客戶 ID 統(tǒng)計(jì)每個(gè)人的總消費(fèi)。
• UPDATE customers c JOIN o ...：把客戶表和統(tǒng)計(jì)結(jié)果連接起來。
• SET c.total_spent = o.total：直接把統(tǒng)計(jì)值寫回客戶表。

好處：不用在程序里循環(huán)“查一個(gè)、改一個(gè)”，減少網(wǎng)絡(luò)開銷，保證原子性。

9.GROUP_CONCAT—— 多行變一行

SELECT 
    department,
    GROUP_CONCAT(name ORDER BY salary DESC SEPARATOR ', ') AS members
FROM employees
GROUP BY department;

解釋：

• GROUP BY department：按部門分組。
• GROUP_CONCAT(name ...)：把每個(gè)部門的所有員工名字拼成一個(gè)字符串。
• ORDER BY salary DESC：按薪水從高到低排序后再拼接。
• SEPARATOR ', '：用逗號(hào)加空格分隔名字。

典型用途：導(dǎo)出名單、展示標(biāo)簽、匯總明細(xì)等。

默認(rèn)最多拼 1024 字符，可通過 SET SESSION group_concat_max_len = 1000000; 調(diào)大。

10.INSERT ... ON DUPLICATE KEY UPDATE—— 智能插入/更新

INSERT INTO page_views (page_url, view_date, view_count)
VALUES ('/home', CURDATE(), 1)
ON DUPLICATE KEY UPDATE 
    view_count = view_count + 1;

解釋：

• 嘗試插入一條新記錄：頁面 /home，今天日期，訪問次數(shù)為 1。
• 如果因?yàn)?strong>唯一索引沖突（比如 (page_url, view_date) 是唯一鍵）導(dǎo)致插入失?。?ul>
• 就執(zhí)行 ON DUPLICATE KEY UPDATE 部分
• 把原有的 view_count 加 1

前提：表必須有主鍵或唯一索引，否則不會(huì)觸發(fā)更新。

到此這篇關(guān)于10個(gè)MySQL 高級(jí)用法的文章就介紹到這了,更多相關(guān)MySQL 高級(jí)用法內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論