大家好，我是大华！

MySQL 有很多高级但实用的功能，能让你的查询变得更简洁、更高效。但你是否遇到过这些问题，怎么说呢，

• 复杂查询写出来像面条一样难读？
• 统计数据需要写多个子查询，性能低下？
• JSON字段解析总是让你头疼？
• 批量更新关联数据要循环调用接口？说起来，
• 生成列、窗口函数这些高级功能从未敢尝试？

今天分享10个我在工作中经常使用的SQL技巧。不用死记硬背，掌握了就能立刻提高你的数据库操作水平！

. CTE——让复杂查询变清晰

痛点：嵌套子查询难以阅读和维护，逻辑混乱不堪！

-- 传统子查询，难以阅读
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里的员工昵称。

好处：把找部门和找人分成两步。逻辑更清楚，比嵌套子查询好读多了。

. 窗口函数 —— 不分组也能统计

痛点：需要同时展示原始数据和统计结果时不得不分组或多次查询！

SELECT
name,department,salary。RANK OVER AS rank_in_dept,G OVER AS avg_salary
FROM employees;

• p strong》对比 GROUP BY:GROUP BY会把多行合并成一行。而窗口函数保留原始行,同时加上统计值。/ strong》

  h2>.条件聚合——一行查出多个统计/ h2》 p strong》痛点:需要多次运算或子查询才能得到不同状态的统计!/ strong》

  pre code class="hljs language-sql" lang="sql">SELECT YEAR AS year。COUNT AS total,COUNT AS completed,SUM AS revenue FROM orders GROUP BY YEAR;code>/ pre》

  p strong》解释:/ strong》 ul》 li》YEAR:提取订单年份./ li》 li》COUNT:该年总订单数./ li》 li COUNT:如果状态是 completed。就返回,否则返回 NULL;COUNT只统计非NULL值。所以这行就是"完成的订单数"./ li》 li SUM:只对完成的订单求金额总和./ li_ / ul_

  p strong关键:不用写多个子查询,一条语句搞定全年报表!/ strong_

  h2>.自连接——同一张表自己连自己/ h2_ p strong痛点:需要比较同表中不同记录间关系时无法直接实现!/ strong_

  pre code class="hljs language-sql" lang="sql">SELECT e1.name,e2.name FROM employees e1 JOIN employees e2 ON e1.department = e2.department AND e1.id lt; 老实说，e2.id AND ABS le;老实说，e1.salary *;code>/ pre_

  p strong解释:/ strong_ ul_ li employees e1 JOIN employees e2:把员工表当成两个副本来连接./ li_ li e1.department = e2.department:只找同一个部门的人./ li_ lli id lt;e2.id避免重复配对./ lili ABS):计算两人薪水差是否le % . / lili_ _ _ _ / ul_

  p strong用途:/强调找"相似记录""配对关系""上下级等场景非常有用. _ /强调_/强化

  h3.EXISTS替代IN——更高效的存在判断/h3_ p强化痛点:IN子查询性能低下且容易受NULL影响!老实说，/强化

  precode class="hljs language-sql" lang="sql">SELECT name FROM customers c WHERE EXISTS;/pre

  p強化解释: ull对每一位客户c,检查是否存在一笔订单满足: ulli订单的customerid等于这个客户的id/ili订单金额gt;/i/u/liSELECT :这里不需要返回具体字段。只要知道“有没有”就行,so用最轻量./ _strong为什么快?:一旦找到一条匹配订单,就立刻停止搜索,不像IN可能要加载全部订单ID./ 从注意来看，如果子查询可能返回NULL。IN会失效永远为UNKNOWN),而EXISTS不受影响./ h3.JSON函数——轻松读取JSON字段/h3_ p強化痛点:JSON数据解析繁琐且容易出错!/強化

  pre_code class="hljs language-sql" lang="sql">SELECT name,profile->'$.address.city' AS city JSON_EXTRACTAS age FROM users WHERE profile->'$.city'='Beijing';code>/pre
  说到p強化_解释，_ _ul__l_profile是一个JSON类型字段,_比如:{address:{city:"Beijing"}。age:}_/
  l_profile->'$.address.city': l_strong_u_-gt;-gt，是简写,_等价于JSON_UNQUOTE)_/_l__l返回字符串Beijing_/
  l_JSON_EXTRACT_:返回_/
  l_WHERE profile->'$.city'='Beijing':筛选城市是北京的使用者._/
  h3.生成列——数据库自动帮你算/h3_
  从p強化_痛点来看。_重复计算导致性能浪费且容易出错!_/強化_
  pre_code class="hljs language-sql" lang="sql">CREATE TABLE products。height DECIMAL,area DECIMALASSTORED);INSERT INTO products,);code>/pre
  p強化_解释的观点是，_ _ul__l_area DECIMALAS./_/_u-/stron/l_i插入时只需给width和height_。area自动变成_.-_/_u/l_h3.多表更新——一条语句更新关联数据/h3__
  p強力___痛点___跨表批量更新要循环处理或手动关联!__/
  pre_code class=_"hljs language-sql"_lang=_"sql"_UPDATE customers c JOIN _AS total FROM orders GROUP BY customer_id_)o ON c.id=o.customer_id SET c.total_spent=o.total_;其实，__/
  p強力___解释___:
  

  -ul_ 子查询o先按客户ID统计每个人的总消费. 把客户表和统计结果连接起来. spent=o.total直接把统计值写回客户表. /ul__

  p強力___好处___不用在程序里循环“查一个、改一个”。减少网络开销，**保证原子性**.__
  h3.GROUP_CONCAT——多行变一行/h3__
  p強力___痛点___将多行聚合为标量值麻烦!__/
  pre_code class=_"hljs language-sql"_lang=_"sql"_SELECT department GROUP_CONCATAS members FROM employees GROUP BY department_;__/
  p強力___解释___:
  

  -ul_ CONCAT把每个部门所有员工名字拼成一个字符串. ul

  p典型用途导出名单、展示标签、汇总明细等.
  

  默认最多拼个字符,可通过SET SESSION groupconcatmax_len=.调大.

  h3.INSERT...ON DUPLICATE KEY UPDATE——智能插入更新/h3__
  p***强力____*_***痛点****UPSERT需手动先INSERT再UPDATE!*//****
  ***pre***_***码class***=_*"***"***"language-***"***-***sq"***"_**lang***=_*"***"***-**"*-**"*-"***-**"**-"***-**"*-"***-"*/**
  INSERT INTO page_views
  VALUES)
  ON DUPLICATE KEY UPDATE view_count=view_count+;***/***/**
  ***/*/*/*/*/***
  ******/*/*/****
  *****/*试图插入新记录页面/home今天日期访问次数为。*****如果因为唯一索引冲突导致插入失败：
  *-就执行ON DUPLICATE KEY UPDATE部分**
  *-把原有view_count加**
  ***/*
  *****效果第一次访问创建记录之后每次访问自动+完美实现计数器!***/*
  从**前提**来看，必须有主键或唯一索引否则不会触发更新.
  

  blockquote*p这篇文章首发于公众号程序员刘大华专注前后端开发实战笔记关注我少走弯路共进步!

  h4 data-idheading-📌往期精彩h4

  *p《asyncawait到底要不要加try-catch?异步错误处理常用方法》《如何SpringBoot当前线程?种方法自己试过有效》《Java开发必看什么时候for什么时候Stream?》《别再乱newArrayList大Java容器选型案例篇看懂