MySQL 有很多高级但实用的功能，能让你的查询变得更简洁、更高效。
今天分享 10 个我在工作中经常使用的 SQL 技巧，不用死记硬背，掌握了就能立刻提升你的数据库操作水平！
1. CTE（WITH子句）——让复杂查询变清晰
-- 传统子查询，难以阅读 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 (...)：先创建一个临时结果集，叫 ny_depts，里面只包含“IT部”的部门名称。SELECT u.nickname FROM system_users u JOIN ny_depts...：再从用户表中找出那些部门ID在ny_depts里的员工昵称。
好处：把找部门和找人分成两步，逻辑更清楚，比嵌套子查询好读多了。
2. 窗口函数 —— 不分组也能统计
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 (...)：在每个部门内部，按薪水从高到低排名（相同薪水并列）。AVG(salary) OVER (...)：计算每个部门的平均工资，并显示在每一行里。
对比 GROUP BY：GROUP BY 会把多行合并成一行，而窗口函数保留原始行，同时加上统计值。
3. 条件聚合 —— 一行查出多个统计
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(*)：该年总订单数。COUNT(CASE WHEN status = 'completed' THEN 1 END)： 如果状态是 'completed'，就返回 1，否则返回 NULL；COUNT() 只统计非 NULL 值，所以这行就是“完成的订单数”。SUM(CASE WHEN ... THEN amount ELSE 0 END)：只对完成的订单求金额总和。
关键：不用写多个子查询，一条语句搞定全年报表！
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：把员工表当成两个副本（e1 和 e2）来连接。e1.department = e2.department：只找同一个部门的人。e1.id < e2.id：避免重复配对（比如 Alice-Bob 和 Bob-Alice 只保留一个）。ABS(...)：计算两人薪水差是否 ≤ 10%。
用途：找“相似记录”“配对关系”“上下级”等场景非常有用。
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 等于这个客户的 id订单金额 > 1000SELECT 1：这里不需要返回具体字段，只要知道“有没有”就行，所以用 1 最轻量。为什么快？：一旦找到一条匹配订单，就立刻停止搜索，不像 IN 可能要加载全部订单 ID。
注意：如果子查询可能返回 NULL，IN 会失效（因为 x IN (..., NULL) 永远为 UNKNOWN），而 EXISTS 不受影响。
6. JSON 函数 —— 轻松读取 JSON 字段
SELECT name, profile->>'$.address.city' AS city, JSON_EXTRACT(profile, '$.age') AS age FROM users WHERE profile->>'$.city' = 'Beijing';
解释：
profile 是一个 JSON 类型字段，比如：{"address": {"city": "Beijing"}, "age": 30}profile->>'$.address.city'：->> 是简写，等价于 JSON_UNQUOTE(JSON_EXTRACT(...))返回字符串 "Beijing"（去掉引号）JSON_EXTRACT(profile, '$.age')：返回 30（带类型，可能是数字）WHERE profile->>'$.city' = 'Beijing'：筛选城市是北京的用户。
适用场景：用户偏好、动态表单、日志等结构不固定的字段。
7. 生成列 —— 数据库自动帮你算
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：
这是一个“存储型生成列”，数据库会自动计算 width * height 并存下来。如果不加 STORED，就是“虚拟列”（每次查询时计算，不占存储）。插入时只需给 width 和 height，area 自动变成 50。
优势：避免应用层重复计算，还能给 area 加索引加速查询！
8. 多表更新 —— 一条语句更新关联数据
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 统计每个人的总消费。UPDATE customers c JOIN o ...：把客户表和统计结果连接起来。SET c.total_spent = o.total：直接把统计值写回客户表。
好处：不用在程序里循环“查一个、改一个”，减少网络开销，保证原子性。
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 ...)：把每个部门的所有员工名字拼成一个字符串。ORDER BY salary DESC：按薪水从高到低排序后再拼接。SEPARATOR ', '：用逗号加空格分隔名字。
典型用途：导出名单、展示标签、汇总明细等。
默认最多拼 1024 字符，可通过 SET SESSION group_concat_max_len = 1000000; 调大。
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，今天日期，访问次数为 1。如果因为唯一索引冲突（比如 (page_url, view_date) 是唯一键）导致插入失败：就执行 ON DUPLICATE KEY UPDATE 部分把原有的 view_count 加 1效果：第一次访问创建记录，之后每次访问自动 +1，完美实现计数器！
前提：表必须有主键或唯一索引，否则不会触发更新。
到此这篇关于10个MySQL 高级用法的文章就介绍到这了,
