100个数据库通用SQL语句查询技巧-优快云博客

文章目录

100个数据库通用SQL语句查询技巧

100个数据库通用SQL语句查询技巧

以下是100个通用SQL查询技巧，覆盖基础查询、过滤、聚合、连接、子查询、窗口函数、性能优化等场景，每个技巧包含详细说明、代码示例、注意事项，适用于MySQL、SQL Server、PostgreSQL等主流数据库（差异处会特别标注）。

一、基础查询技巧（1-10）

1. 选择特定列而非全部（避免`SELECT *`）

场景：只需要表中部分字段时，减少数据传输和内存占用。
代码示例：

-- 低效：查询所有列（可能包含不需要的大字段如text/blob）
SELECT * FROM user;

-- 高效：只查询需要的列
SELECT id, username, create_time FROM user;

注意：SELECT *会导致新增字段时查询结果不稳定，且可能包含未授权字段。

2. 用别名简化查询（`AS`）

场景：字段名或表名过长时，提升可读性。
代码示例：

-- 表别名（常用于连接查询）
SELECT u.id, u.username, o.order_no 
FROM user AS u
JOIN order AS o ON u.id = o.user_id;

-- 字段别名（美化结果或处理计算字段）
SELECT SUM(amount) AS total_amount, COUNT(*) AS order_count FROM order;

注意：别名不区分大小写，避免使用关键字（如AS "user"需加引号）。

3. 去重查询（`DISTINCT`）

场景：获取某列不重复的值（如统计有多少种状态）。
代码示例：

-- 查询所有不重复的用户角色
SELECT DISTINCT role FROM user;

-- 多列去重（需多列组合完全相同才会去重）
SELECT DISTINCT role, dept_id FROM user;

注意：DISTINCT作用于其后所有列，而非仅前一列；与COUNT结合时COUNT(DISTINCT col)统计去重后的数量。

4. 限制结果数量（`LIMIT`/`TOP`）

场景：快速预览数据或分页查询（MySQL用LIMIT，SQL Server用TOP，PostgreSQL用LIMIT）。
代码示例：

-- MySQL/PostgreSQL：查询前10条用户数据
SELECT * FROM user LIMIT 10;

-- SQL Server：查询前10条
SELECT TOP 10 * FROM user;

-- 分页：查询第11-20条（MySQL/PostgreSQL）
SELECT * FROM user LIMIT 10 OFFSET 10; -- 偏移10条，取10条

注意：LIMIT后偏移量（OFFSET）过大会导致性能下降（如LIMIT 100000 OFFSET 100000），建议用WHERE条件优化。

5. 条件过滤（`WHERE`基础）

场景：筛选符合条件的记录（等于、不等于、范围等）。
代码示例：

-- 等于（=）
SELECT * FROM user WHERE status = 1;

-- 不等于（!= 或 <>）
SELECT * FROM user WHERE role != 'admin';

-- 数值范围（BETWEEN，包含边界）
SELECT * FROM order WHERE amount BETWEEN 100 AND 1000;

-- 枚举值（IN，代替多个OR）
SELECT * FROM user WHERE dept_id IN (1, 3, 5);

注意：BETWEEN在日期类型中需注意边界（如BETWEEN '2023-01-01' AND '2023-01-31'包含31日全天）。

6. 模糊查询（`LIKE`）

场景：按部分字符匹配（如搜索用户名包含“张”的用户）。
代码示例：

-- %匹配任意多个字符（包括0个）
SELECT * FROM user WHERE username LIKE '张%'; -- 以“张”开头

-- _匹配单个字符
SELECT * FROM user WHERE phone LIKE '138_5678'; -- 第4位任意字符

-- 转义特殊字符（如查询包含“%”的字段）
SELECT * FROM product WHERE name LIKE '%\%%' ESCAPE '\'; -- 用\转义

注意：LIKE '%xxx'会导致索引失效（无法使用前缀索引），大数据量下建议用全文索引（如FULLTEXT）。

7. 空值处理（`IS NULL`/`IS NOT NULL`）

场景：筛选字段为NULL或非NULL的记录（NULL≠空字符串’'）。
代码示例：

-- 查询未填写邮箱的用户
SELECT * FROM user WHERE email IS NULL;

-- 查询已填写手机号的用户
SELECT * FROM user WHERE phone IS NOT NULL;

-- 替换NULL为默认值（用COALESCE）
SELECT username, COALESCE(email, '未设置') AS email FROM user;

注意：NULL = NULL结果为NULL（不成立），必须用IS NULL判断；COALESCE可接收多个参数，返回第一个非NULL值。

8. 多条件组合（`AND`/`OR`/`NOT`）

场景：多条件筛选时，用逻辑运算符组合。
代码示例：

-- AND：同时满足
SELECT * FROM user WHERE role = 'user' AND status = 1;

-- OR：满足其一（注意优先级，建议用括号）
SELECT * FROM user WHERE (dept_id = 1 AND status = 1) OR (dept_id = 2 AND status = 0);

-- NOT：取反
SELECT * FROM user WHERE NOT (role = 'admin');

注意：AND优先级高于OR，复杂条件必须加括号明确逻辑；NOT IN效率较低，可考虑NOT EXISTS。

9. 排序查询（`ORDER BY`）

场景：按指定字段升序/降序排列结果。
代码示例：

-- 单字段排序（默认ASC升序）
SELECT * FROM order ORDER BY create_time DESC; -- 按创建时间降序（最新在前）

-- 多字段排序（先按dept_id升序，再按salary降序）
SELECT * FROM user ORDER BY dept_id ASC, salary DESC;

-- 按表达式排序（如按字符串长度）
SELECT * FROM product ORDER BY LENGTH(name) ASC;

注意：ORDER BY会增加查询开销（需排序操作），大数据量下建议结合索引优化；NULL值在排序时通常视为最小值（升序排在最前）。

10. 计算字段（表达式/函数）

场景：查询时对字段进行计算或转换（如拼接字符串、计算金额）。
代码示例：

-- 字符串拼接（MySQL用CONCAT，SQL Server用+，PostgreSQL用||）
SELECT CONCAT(username, '(', id, ')') AS user_info FROM user; -- MySQL

-- 数值计算（如订单总金额=数量*单价）
SELECT order_id, quantity, price, quantity * price AS total FROM order_item;

-- 日期转换（如格式化时间）
SELECT id, DATE_FORMAT(create_time, '%Y-%m-%d') AS create_date FROM user; -- MySQL

注意：计算字段无法直接使用索引，复杂计算建议在应用层处理。

二、聚合与分组技巧（11-20）

11. 计数（`COUNT`）

场景：统计记录数量（总条数、非空字段数、去重后数量）。
代码示例：

-- 统计总记录数（包含NULL）
SELECT COUNT(*) AS total FROM user;

-- 统计非空字段数（排除NULL）
SELECT COUNT(email) AS email_count FROM user; -- email为NULL的不计数

-- 统计去重后的值数量
SELECT COUNT(DISTINCT dept_id) AS dept_count FROM user;

注意：COUNT(*)效率通常高于COUNT(1)（部分数据库优化相同），COUNT(col)不统计NULL值。

12. 求和与平均值（`SUM`/`AVG`）

场景：计算数值字段的总和或平均值（如总销售额、平均工资）。
代码示例：

-- 求和（忽略NULL）
SELECT SUM(amount) AS total_amount FROM order WHERE status = 2; -- 已支付订单总金额

-- 平均值（忽略NULL）
SELECT AVG(salary) AS avg_salary FROM user WHERE dept_id = 3; -- 3号部门平均工资

-- 过滤NULL值（等价于默认行为，但更清晰）
SELECT SUM(COALESCE(bonus, 0)) AS total_bonus FROM user; -- 奖金为NULL的按0计算

注意：SUM/AVG自动忽略NULL值，若需包含NULL（按0计算），用COALESCE转换。

13. 最大与最小值（`MAX`/`MIN`）

场景：获取字段的最大/最小值（如最高工资、最早订单时间）。
代码示例：

-- 最大值
SELECT MAX(salary) AS max_salary, dept_id FROM user GROUP BY dept_id; -- 各部门最高工资

-- 最小值
SELECT MIN(create_time) AS first_order_time FROM order; -- 最早订单时间

-- 非数值类型（如字符串最大/小值，按字典序）
SELECT MAX(username) AS max_username FROM user; -- 用户名按字母排序的最大值

注意：MAX/MIN支持数值、日期、字符串类型，字符串按字典序比较。

14. 分组查询（`GROUP BY`）

场景：按字段分组统计（如按部门分组统计人数）。
代码示例：

-- 按单字段分组
SELECT dept_id, COUNT(*) AS user_count FROM user GROUP BY dept_id;

-- 按多字段分组（组合分组）
SELECT dept_id, role, COUNT(*) AS count FROM user GROUP BY dept_id, role; -- 部门+角色的组合分组

-- 分组后排序
SELECT dept_id, SUM(salary) AS total_salary 
FROM user 
GROUP BY dept_id 
ORDER BY total_salary DESC;

注意：GROUP BY后SELECT的字段必须是分组字段或聚合函数（否则结果不可预测，MySQL默认允许但不推荐）。

15. 过滤分组结果（`HAVING`）

场景：对GROUP BY的结果进行筛选（WHERE用于分组前过滤，HAVING用于分组后）。
代码示例：

-- 错误：WHERE不能直接用聚合函数
-- SELECT dept_id, COUNT(*) AS cnt FROM user WHERE cnt > 10 GROUP BY dept_id;

-- 正确：用HAVING过滤分组后结果
SELECT dept_id, COUNT(*) AS user_count 
FROM user 
GROUP BY dept_id 
HAVING user_count > 10; -- 筛选人数超过10的部门

注意：HAVING可使用别名或聚合函数，WHERE不行；HAVING在分组后执行，效率低于WHERE，能在WHERE中过滤的尽量不用HAVING。

16. 分组内拼接字符串（`GROUP_CONCAT`/`STRING_AGG`）

场景：将分组内的字段值拼接为一个字符串（如聚合某部门所有用户名）。
代码示例：

-- MySQL：GROUP_CONCAT（默认用,分隔）
SELECT dept_id, GROUP_CONCAT(username) AS user_list 
FROM user 
GROUP BY dept_id;

-- 自定义分隔符+去重+排序
SELECT dept_id, GROUP_CONCAT(DISTINCT username ORDER BY username SEPARATOR ';') AS user_list 
FROM user 
GROUP BY dept_id;

-- SQL Server 2017+/PostgreSQL：STRING_AGG
SELECT dept_id, STRING_AGG(username, ',') AS user_list 
FROM user 
GROUP BY dept_id;

注意：GROUP_CONCAT有长度限制（MySQL默认1024字符），可通过group_concat_max_len调整；拼接大量数据可能影响性能。

17. 条件聚合（`CASE WHEN`+聚合函数）

场景：按条件统计不同分类的数量（如同一分组内统计不同状态的订单数）。
代码示例：

-- 统计各用户的已支付/未支付订单数
SELECT 
  user_id,
  COUNT(CASE WHEN status = 2 THEN 1 END) AS paid_count, -- 已支付（status=2）
  COUNT(CASE WHEN status != 2 THEN 1 END) AS unpaid_count -- 未支付
FROM order 
GROUP BY user_id;

注意：CASE WHEN不满足条件时返回NULL，COUNT会忽略NULL，等价于条件统计；也可用SUM(CASE WHEN ... THEN 1 ELSE 0 END)实现。

18. 累计求和（基础版）

场景：计算累计值（如按日期累计销售额）。
代码示例：

-- 按日期排序后，累计每天的销售额（子查询版）
SELECT 
  sale_date,
  daily_sale,
  (SELECT SUM(daily_sale) FROM sales s2 WHERE s2.sale_date <= s1.sale_date) AS total_sale
FROM (
  SELECT sale_date, SUM(amount) AS daily_sale FROM order GROUP BY sale_date
) s1
ORDER BY sale_date;

注意：基础版用子查询实现，效率较低，大数据量建议用窗口函数（见技巧36）。

19. 分组取第一条数据（子查询+`MIN`/`MAX`）

场景：按分组获取某字段最小/最大值对应的整条记录（如各部门工资最高的用户信息）。
代码示例：

-- 方法1：子查询获取分组最大值，再关联原表
SELECT u.* 
FROM user u
JOIN (
  SELECT dept_id, MAX(salary) AS max_salary 
  FROM user 
  GROUP BY dept_id
) t ON u.dept_id = t.dept_id AND u.salary = t.max_salary;

注意：若分组内存在多个最大值（如同一部门2人工资相同且最高），会返回多条记录；更灵活的方式用窗口函数（见技巧37）。

20. 防止聚合函数返回`NULL`（`COALESCE`）

场景：当聚合结果为空时（如无符合条件的记录），返回0或默认值（避免NULL）。
代码示例：

-- 若无数据，COUNT(*)返回0，但若加WHERE可能返回NULL（如无status=3的订单）
SELECT COALESCE(SUM(amount), 0) AS total_amount 
FROM order 
WHERE status = 3; -- 若status=3的订单不存在，返回0而非NULL

三、连接查询技巧（21-30）

21. 内连接（`INNER JOIN`）

场景：获取两表中匹配条件的交集记录（只返回双方都有数据的行）。
代码示例：

-- 查询用户及其订单（只返回有订单的用户）
SELECT u.username, o.order_no 
FROM user u
INNER JOIN order o ON u.id = o.user_id; -- 等价于 JOIN（默认内连接）

注意：内连接会过滤掉不匹配的记录，若需保留所有用户（包括无订单的），用左连接。

22. 左连接（`LEFT JOIN`）

场景：保留左表所有记录，右表无匹配时返回NULL（如查询所有用户，包括无订单的）。
代码示例：

-- 左表：user，右表：order（保留所有用户）
SELECT u.username, o.order_no 
FROM user u
LEFT JOIN order o ON u.id = o.user_id;

-- 左连接+过滤右表（只保留无订单的用户）
SELECT u.username 
FROM user u
LEFT JOIN order o ON u.id = o.user_id
WHERE o.user_id IS NULL; -- 右表无匹配（订单ID为NULL）

注意：左连接的过滤条件若写在WHERE中，会导致左连接退化为内连接（需将右表条件写在ON中）。

23. 右连接（`RIGHT JOIN`）

场景：保留右表所有记录，左表无匹配时返回NULL（与左连接相反，较少用）。
代码示例：

-- 右表：order，左表：user（保留所有订单，包括用户信息缺失的）
SELECT u.username, o.order_no 
FROM user u
RIGHT JOIN order o ON u.id = o.user_id;

注意：右连接可转换为左连接（交换表顺序），可读性更好，建议优先用左连接。

24. 全连接（`FULL JOIN`）

场景：保留两表所有记录，无匹配时返回NULL（MySQL不支持，可用UNION模拟）。
代码示例：

-- PostgreSQL/SQL Server：全连接
SELECT u.username, o.order_no 
FROM user u
FULL JOIN order o ON u.id = o.user_id;

-- MySQL：用LEFT JOIN + RIGHT JOIN + UNION模拟全连接
SELECT u.username, o.order_no 
FROM user u
LEFT JOIN order o ON u.id = o.user_id
UNION
SELECT u.username, o.order_no 
FROM user u
RIGHT JOIN order o ON u.id = o.user_id;

注意：UNION会去重，UNION ALL不查重（效率更高，确定无重复时用）。

25. 多表连接（3表及以上）

场景：关联多个表查询（如用户→订单→订单详情）。
代码示例：

-- 关联3表：用户、订单、订单详情
SELECT 
  u.username, 
  o.order_no, 
  oi.product_name, 
  oi.quantity 
FROM user u
LEFT JOIN order o ON u.id = o.user_id
LEFT JOIN order_item oi ON o.id = oi.order_id
WHERE o.status = 2; -- 已支付订单

注意：多表连接时需明确每个连接的条件，避免笛卡尔积（如忘记写ON条件，导致记录数爆炸）。

26. 自连接（同表连接）

场景：将表与自身连接，用于查询层级关系（如员工与上级）或对比同表数据。
代码示例：

-- 员工表自连接：查询员工及其上级姓名
SELECT 
  e.name AS emp_name, 
  m.name AS manager_name 
FROM employee e
LEFT JOIN employee m ON e.manager_id = m.id; -- 上级ID关联自身ID

注意：自连接需用别名区分两个“表”，避免字段名冲突。

27. 连接条件优化（避免`OR`）

场景：连接条件中用OR会导致索引失效，影响性能。
代码示例：

-- 低效：连接条件含OR，可能无法使用索引
SELECT * 
FROM user u
JOIN order o ON u.id = o.user_id OR u.phone = o.user_phone;

-- 高效：拆分为两个连接+UNION（若结果允许去重）
SELECT * 
FROM user u
JOIN order o ON u.id = o.user_id
UNION
SELECT * 
FROM user u
JOIN order o ON u.phone = o.user_phone;

28. 用连接代替子查询（部分场景）

场景：部分子查询可改写为连接，提升性能（尤其是相关子查询）。
代码示例：

-- 子查询版：查询有订单的用户
SELECT * FROM user 
WHERE id IN (SELECT user_id FROM order);

-- 连接版：更高效（尤其订单表大时）
SELECT DISTINCT u.* 
FROM user u
JOIN order o ON u.id = o.user_id;

注意：IN子查询在MySQL中效率较低，可优先用连接；但EXISTS子查询效率通常与连接相当。

29. 限制连接结果（避免重复）

场景：左连接时右表有多条匹配记录，导致左表记录被重复（如1个用户有3个订单，左连接后用户记录显示3次）。
代码示例：

-- 问题：用户被重复显示（1用户3订单→3条记录）
SELECT u.id, u.username, o.order_no 
FROM user u
LEFT JOIN order o ON u.id = o.user_id;

-- 解决：只取每个用户的最新订单（用子查询限制右表）
SELECT u.id, u.username, o.order_no 
FROM user u
LEFT JOIN (
  SELECT user_id, MAX(order_no) AS order_no -- 或按时间取最新
  FROM order 
  GROUP BY user_id
) o ON u.id = o.user_id;

30. 连接时使用索引字段

场景：连接条件的字段若有索引，可大幅提升连接效率。
代码示例：

-- 高效：order.user_id有索引（假设已创建）
SELECT u.username, o.order_no 
FROM user u
JOIN order o ON u.id = o.user_id; -- o.user_id有索引，连接更快

-- 建议为连接字段创建索引
CREATE INDEX idx_order_user_id ON order(user_id);

四、子查询技巧（31-40）

31. 标量子查询（返回单个值）

场景：子查询返回单个值，可作为条件或字段使用（如获取某用户的最新订单时间）。
代码示例：

-- 作为条件
SELECT * FROM order 
WHERE create_time = (SELECT MAX(create_time) FROM order WHERE user_id = 100);

-- 作为字段
SELECT 
  username,
  (SELECT COUNT(*) FROM order WHERE user_id = u.id) AS order_count 
FROM user u;

注意：标量子查询若返回多个值会报错，需确保结果唯一。

32. 列子查询（返回单列多行）

场景：子查询返回单列多个值，常用IN/NOT IN判断（如查询属于某些部门的用户）。
代码示例：

-- IN：在子查询结果中
SELECT * FROM user 
WHERE dept_id IN (SELECT id FROM dept WHERE name LIKE '技术%');

-- NOT IN：不在子查询结果中
SELECT * FROM user 
WHERE dept_id NOT IN (SELECT id FROM dept WHERE status = 0);

注意：NOT IN子查询若包含NULL，会导致结果为空（因NULL无法比较），建议用NOT EXISTS替代。

33. 表子查询（返回多行多列）

场景：子查询返回多列，可作为临时表参与查询（如先过滤再连接）。
代码示例：

-- 子查询作为临时表（需别名）
SELECT t.dept_id, t.user_count, d.name 
FROM (
  SELECT dept_id, COUNT(*) AS user_count 
  FROM user 
  WHERE status = 1 
  GROUP BY dept_id
) t
JOIN dept d ON t.dept_id = d.id;

注意：表子查询需指定别名（如t），否则无法引用；复杂子查询可考虑用WITH（CTE）优化可读性。

34. 相关子查询（依赖外部查询）

场景：子查询使用外部查询的字段，每行执行一次（如查询每个用户的最大订单金额）。
代码示例：

-- 相关子查询：每个用户的最大订单金额
SELECT 
  u.id,
  u.username,
  (SELECT MAX(amount) FROM order o WHERE o.user_id = u.id) AS max_amount 
FROM user u;

注意：相关子查询效率较低（每行执行一次），大数据量建议用JOIN + GROUP BY或窗口函数替代。

35. `EXISTS`与`NOT EXISTS`（判断存在性）

场景：判断子查询是否有结果（存在则返回TRUE），效率通常高于IN（尤其子查询结果大时）。
代码示例：

-- EXISTS：查询有订单的用户（只要存在至少1个订单）
SELECT * FROM user u
WHERE EXISTS (SELECT 1 FROM order o WHERE o.user_id = u.id);

-- NOT EXISTS：查询无订单的用户
SELECT * FROM user u
WHERE NOT EXISTS (SELECT 1 FROM order o WHERE o.user_id = u.id);

注意：EXISTS子查询只要找到第一条匹配记录就停止，效率高；子查询中的SELECT 1可替换为任意值（不影响结果）。

36. 用`EXISTS`替代`DISTINCT`（连接去重）

场景：连接查询后用DISTINCT去重，可改为EXISTS提升效率。
代码示例：

-- 低效：连接后去重
SELECT DISTINCT u.* 
FROM user u
JOIN order o ON u.id = o.user_id;

-- 高效：用EXISTS判断存在性（无需去重）
SELECT u.* 
FROM user u
WHERE EXISTS (SELECT 1 FROM order o WHERE o.user_id = u.id);

37. 子查询与连接的转换（灵活选择）

场景：多数子查询可与连接互相转换，根据性能选择。
代码示例：

-- 子查询版：查询订单金额大于用户平均订单金额的订单
SELECT o.* 
FROM order o
WHERE o.amount > (
  SELECT AVG(amount) FROM order WHERE user_id = o.user_id
);

-- 连接版：先计算用户平均金额，再连接
SELECT o.* 
FROM order o
JOIN (
  SELECT user_id, AVG(amount) AS avg_amount 
  FROM order 
  GROUP BY user_id
) t ON o.user_id = t.user_id
WHERE o.amount > t.avg_amount;

注意：相关子查询适合小数据量，连接适合大数据量（可利用索引）。

38. `WITH`子句（CTE，公用表表达式）

场景：将子查询定义为临时表，提升复杂查询的可读性（尤其多层子查询）。
代码示例：

-- 用WITH定义临时表（MySQL 8.0+/SQL Server/PostgreSQL支持）
WITH user_avg AS (
  SELECT user_id, AVG(amount) AS avg_amount 
  FROM order 
  GROUP BY user_id
)
-- 使用临时表
SELECT o.* 
FROM order o
JOIN user_avg ua ON o.user_id = ua.user_id
WHERE o.amount > ua.avg_amount;

注意：CTE是临时的，仅在当前查询中有效；递归CTE可处理层级数据（如部门树）。

39. 递归CTE（处理层级数据）

场景：查询树形结构数据（如部门层级、评论回复）。
代码示例：

-- 部门表（id, name, parent_id），查询所有部门的完整路径
WITH RECURSIVE dept_tree AS (
  -- 锚点：顶级部门（parent_id = 0）
  SELECT id, name, parent_id, CAST(name AS CHAR(100)) AS path 
  FROM dept 
  WHERE parent_id = 0
  UNION ALL
  -- 递归：子部门关联父部门
  SELECT d.id, d.name, d.parent_id, CONCAT(dt.path, '->', d.name) AS path 
  FROM dept d
  JOIN dept_tree dt ON d.parent_id = dt.id
)
SELECT * FROM dept_tree;

注意：递归CTE需包含锚点查询（非递归部分）和递归查询（与CTE自身连接），避免无限递归（确保层级有终点）。

40. 子查询中的`LIMIT`（限制结果）

场景：子查询中用LIMIT获取前N条数据（如查询每个用户的前3个订单）。
代码示例：

-- MySQL：子查询+LIMIT（需关联外部字段）
SELECT u.id, u.username, o.order_no, o.create_time 
FROM user u
JOIN (
  SELECT * FROM order o1 
  WHERE (SELECT COUNT(*) FROM order o2 WHERE o2.user_id = o1.user_id AND o2.create_time >= o1.create_time) <= 3
) o ON u.id = o.user_id;

注意：更简单的方式用窗口函数ROW_NUMBER（见技巧47）。

五、窗口函数技巧（41-50）

41. 窗口函数基础（`OVER()`）

场景：窗口函数在一组行上计算聚合值，不改变原表行数（区别于GROUP BY）。
代码示例：

-- 计算所有用户的平均工资，同时保留每条用户记录
SELECT 
  id, username, salary,
  AVG(salary) OVER() AS avg_salary -- 窗口函数：所有行的平均值
FROM user;

注意：窗口函数支持MySQL 8.0+、SQL Server 2012+、PostgreSQL，低版本数据库不支持。

42. 分区窗口（`PARTITION BY`）

场景：按字段分区（分组）计算窗口函数（如各部门的平均工资）。
代码示例：

-- 按部门分区，计算每个部门的平均工资
SELECT 
  id, username, dept_id, salary,
  AVG(salary) OVER(PARTITION BY dept_id) AS dept_avg_salary
FROM user;

注意：PARTITION BY类似GROUP BY，但不合并行，每行都显示分区内的聚合结果。

43. 排序窗口（`ORDER BY`在`OVER`中）

场景：窗口内按字段排序（如计算累计销售额）。
代码示例：

-- 按日期排序，计算累计销售额
SELECT 
  sale_date, daily_sale,
  SUM(daily_sale) OVER(ORDER BY sale_date) AS total_sale -- 累计求和
FROM (
  SELECT sale_date, SUM(amount) AS daily_sale FROM order GROUP BY sale_date
) t;

44. 排名函数（`ROW_NUMBER`）

场景：为每行分配唯一排名（同一分组内序号递增，无并列）。
代码示例：

-- 按部门分区，按工资降序排名（1,2,3...）
SELECT 
  username, dept_id, salary,
  ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rank
FROM user;

注意：即使工资相同，ROW_NUMBER也会分配不同排名（如两个并列第一会显示1和2）。

45. 排名函数（`RANK`）

场景：排名允许并列，跳过后续名次（如1,1,3…）。
代码示例：

-- 按部门分区，工资排名（允许并列，跳过名次）
SELECT 
  username, dept_id, salary,
  RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rank
FROM user;
-- 结果示例：工资9000→1，9000→1，8000→3

46. 排名函数（`DENSE_RANK`）

场景：排名允许并列，不跳过后续名次（如1,1,2…）。
代码示例：

-- 按部门分区，工资排名（允许并列，不跳名次）
SELECT 
  username, dept_id, salary,
  DENSE_RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rank
FROM user;
-- 结果示例：工资9000→1，9000→1，8000→2

47. 取每组前N条（`ROW_NUMBER`+子查询）

场景：按分组获取前N条记录（如各部门工资前2的用户）。
代码示例：

-- 步骤1：用ROW_NUMBER分组排名
WITH ranked_user AS (
  SELECT 
    username, dept_id, salary,
    ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rank
  FROM user
)
-- 步骤2：筛选排名≤2的记录
SELECT username, dept_id, salary 
FROM ranked_user 
WHERE rank <= 2;

48. 前后行数据（`LAG`/`LEAD`）

场景：获取当前行的前N行（LAG）或后N行（LEAD）数据（如计算日销售额环比）。
代码示例：

-- 计算每天销售额与前一天的差值
WITH daily_sale AS (
  SELECT sale_date, SUM(amount) AS amount FROM order GROUP BY sale_date
)
SELECT 
  sale_date,
  amount,
  LAG(amount, 1) OVER(ORDER BY sale_date) AS prev_day_amount, -- 前1天销售额
  amount - LAG(amount, 1) OVER(ORDER BY sale_date) AS diff
FROM daily_sale;

注意：LAG(amount, 1, 0)中的0是默认值（当前行为第一行时返回0）。

49. 窗口框架（`ROWS BETWEEN`）

场景：定义窗口内的行范围（如计算近3天的平均销售额）。
代码示例：

-- 计算包括当前行的近3天平均销售额
WITH daily_sale AS (
  SELECT sale_date, SUM(amount) AS amount FROM order GROUP BY sale_date
)
SELECT 
  sale_date,
  amount,
  AVG(amount) OVER(
    ORDER BY sale_date 
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW -- 前2行到当前行（共3行）
  ) AS avg_3days
FROM daily_sale;

说明：ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW表示从分区第一行到当前行（默认累计）。

50. 聚合窗口与分组聚合的区别

场景：明确窗口函数与GROUP BY的差异（窗口函数保留原行数，GROUP BY合并行）。
代码示例：

-- GROUP BY：合并行（每个部门1行）
SELECT dept_id, AVG(salary) AS dept_avg FROM user GROUP BY dept_id;

-- 窗口函数：保留所有行（每行都显示部门平均）
SELECT id, username, dept_id, salary, AVG(salary) OVER(PARTITION BY dept_id) AS dept_avg FROM user;

六、数据修改技巧（51-60）

51. 插入多条记录（`INSERT`批量）

场景：一次性插入多条记录，比单条插入效率高。
代码示例：

-- 批量插入
INSERT INTO user (username, age) VALUES
('张三', 25),
('李四', 30),
('王五', 28);

注意：批量插入的记录数不宜过多（如MySQL建议单次不超过1000条），避免连接超时。

52. 从查询结果插入（`INSERT SELECT`）

场景：将查询结果插入到表中（如复制数据、备份表）。
代码示例：

-- 备份用户表（插入所有状态为1的用户到备份表）
INSERT INTO user_backup (id, username, create_time)
SELECT id, username, create_time FROM user WHERE status = 1;

注意：目标表与查询结果的字段数量和类型必须匹配。

53. 条件更新（`UPDATE`+`WHERE`）

场景：只更新符合条件的记录（避免全表更新）。
代码示例：

-- 只更新3号部门的用户状态
UPDATE user SET status = 0 WHERE dept_id = 3;

-- 基于其他表的字段更新（关联更新）
UPDATE user u
JOIN dept d ON u.dept_id = d.id
SET u.status = 0 
WHERE d.name = '临时部门';

注意：UPDATE不带WHERE会更新全表，操作前务必确认条件；建议先执行SELECT验证条件。

54. 用其他表的数据更新（`UPDATE JOIN`）

场景：根据另一表的数据更新当前表（如用订单表的最新时间更新用户表）。
代码示例：

-- MySQL：用JOIN更新
UPDATE user u
JOIN (
  SELECT user_id, MAX(create_time) AS last_order_time 
  FROM order 
  GROUP BY user_id
) o ON u.id = o.user_id
SET u.last_order_time = o.last_order_time;

-- SQL Server：用FROM更新
UPDATE u
SET u.last_order_time = o.last_order_time
FROM user u
JOIN (
  SELECT user_id, MAX(create_time) AS last_order_time 
  FROM order 
  GROUP BY user_id
) o ON u.id = o.user_id;

55. 批量更新不同值（`CASE WHEN`）

场景：一次性更新多条记录的不同值（如根据ID设置不同状态）。
代码示例：

-- 按ID更新不同状态
UPDATE user SET status = CASE 
  WHEN id = 1 THEN 1
  WHEN id = 2 THEN 0
  WHEN id = 3 THEN 2
  ELSE status -- 不匹配的保持原状态
END
WHERE id IN (1, 2, 3); -- 限制更新范围（必加，否则全表执行CASE）

56. 删除重复记录（保留一条）

场景：删除表中重复的记录，只保留一条（如按username去重）。
代码示例：

-- 方法：删除ID不是最小值的重复记录
DELETE FROM user 
WHERE id NOT IN (
  SELECT min_id FROM (
    SELECT MIN(id) AS min_id FROM user GROUP BY username -- 按username分组，取最小ID
  ) t
);

注意：删除前先备份数据；MySQL中不能直接删除子查询中同表的记录，需嵌套一层子查询。

57. 条件删除（`DELETE`+`WHERE`）

场景：只删除符合条件的记录（避免误删全表）。
代码示例：

-- 删除30天前的测试订单
DELETE FROM order 
WHERE create_time < DATE_SUB(CURDATE(), INTERVAL 30 DAY) 
  AND is_test = 1;

注意：DELETE不带WHERE会删除全表数据，建议先执行SELECT确认条件；重要操作前开启事务（BEGIN），确认后再提交（COMMIT）。

58. 从多表删除（`DELETE JOIN`）

场景：根据关联表的条件删除记录（如删除没有部门的用户）。
代码示例：

-- MySQL：删除无对应部门的用户
DELETE u FROM user u
LEFT JOIN dept d ON u.dept_id = d.id
WHERE d.id IS NULL; -- 部门不存在

-- SQL Server：用FROM删除
DELETE u
FROM user u
LEFT JOIN dept d ON u.dept_id = d.id
WHERE d.id IS NULL;

59. 限制删除/更新的行数（`LIMIT`）

场景：批量删除/更新时限制行数，避免长时间锁表（MySQL支持LIMIT）。
代码示例：

-- 每次删除1000条过期数据（分批次执行）
DELETE FROM log 
WHERE create_time < '2023-01-01' 
LIMIT 1000;

注意：SQL Server用TOP（DELETE TOP (1000) FROM log ...），PostgreSQL用LIMIT。

60. 插入或更新（`INSERT ... ON DUPLICATE KEY UPDATE`）

场景：插入记录时，若主键/唯一键冲突则更新字段（避免先查后插）。
代码示例：

-- MySQL：若id已存在则更新username和age
INSERT INTO user (id, username, age) 
VALUES (1, '张三', 26)
ON DUPLICATE KEY UPDATE 
  username = VALUES(username), 
  age = VALUES(age);

-- SQL Server：用MERGE
MERGE INTO user u
USING (SELECT 1 AS id, '张三' AS username, 26 AS age) t
ON u.id = t.id
WHEN MATCHED THEN 
  UPDATE SET username = t.username, age = t.age
WHEN NOT MATCHED THEN 
  INSERT (id, username, age) VALUES (t.id, t.username, t.age);

注意：需确保表有主键或唯一键（否则ON DUPLICATE KEY不生效）。

七、日期与时间处理技巧（61-70）

61. 获取当前时间（`NOW()`/`CURRENT_TIMESTAMP`）

场景：插入或更新记录时记录当前时间。
代码示例：

-- 获取当前日期时间（含时分秒）
SELECT NOW(); -- 2023-10-22 15:30:45
SELECT CURRENT_TIMESTAMP(); -- 同上

-- 获取当前日期（不含时间）
SELECT CURDATE(); -- 2023-10-22
SELECT DATE(NOW()); -- 同上

-- 获取当前时间（不含日期）
SELECT CURTIME(); -- 15:30:45

62. 日期加减（`DATE_ADD`/`DATE_SUB`）

场景：计算未来或过去的日期（如3天后、1小时前）。
代码示例：

-- 日期加（MySQL）
SELECT DATE_ADD(NOW(), INTERVAL 3 DAY); -- 3天后
SELECT DATE_ADD(NOW(), INTERVAL 1 HOUR); -- 1小时后

-- 日期减（MySQL）
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH); -- 1个月前

-- 通用写法（支持多数数据库）
SELECT NOW() + INTERVAL 3 DAY; -- 加3天
SELECT NOW() - INTERVAL 1 YEAR; -- 减1年

63. 计算日期差（`DATEDIFF`/`TIMESTAMPDIFF`）

场景：计算两个日期的差值（天数、小时数等）。
代码示例：

-- 计算天数差（MySQL/SQL Server）
SELECT DATEDIFF('2023-10-22', '2023-10-01'); -- 21（结束-开始）

-- 计算小时差（MySQL）
SELECT TIMESTAMPDIFF(HOUR, '2023-10-22 08:00', '2023-10-22 10:30'); -- 2.5→2（向下取整）

-- 计算分钟差
SELECT TIMESTAMPDIFF(MINUTE, '2023-10-22 08:00', '2023-10-22 10:30'); -- 150

64. 日期格式化（`DATE_FORMAT`/`TO_CHAR`）

场景：将日期转换为指定格式的字符串（如“2023年10月22日”）。
代码示例：

-- MySQL：DATE_FORMAT
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); -- 2023-10-22 15:30:45
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日'); -- 2023年10月22日

-- PostgreSQL：TO_CHAR
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS'); -- 2023-10-22 15:30:45

-- SQL Server：CONVERT
SELECT CONVERT(VARCHAR, NOW(), 23); -- 2023-10-22（格式代码23）

格式符说明：%Y（4位年）、%m（2位月）、%d（2位日）、%H（24小时）、%i（分钟）、%s（秒）。

65. 提取日期部分（年/月/日/时）

场景：从日期中提取年、月、日等部分（如按月份统计订单）。
代码示例：

-- 提取年份
SELECT YEAR(NOW()); -- 2023
SELECT EXTRACT(YEAR FROM NOW()); -- 2023（通用）

-- 提取月份
SELECT MONTH(NOW()); -- 10
SELECT EXTRACT(MONTH FROM NOW()); -- 10

-- 提取日
SELECT DAY(NOW()); -- 22
SELECT EXTRACT(DAY FROM NOW()); -- 22

-- 提取小时
SELECT HOUR(NOW()); -- 15

66. 当月第一天与最后一天

场景：查询当月数据（如本月订单）。
代码示例：

-- 当月第一天（MySQL）
SELECT DATE_FORMAT(NOW(), '%Y-%m-01'); -- 2023-10-01

-- 当月最后一天（MySQL）
SELECT LAST_DAY(NOW()); -- 2023-10-31

-- 查询本月订单
SELECT * FROM order 
WHERE create_time BETWEEN 
  DATE_FORMAT(NOW(), '%Y-%m-01') AND 
  LAST_DAY(NOW()) + INTERVAL 1 DAY - INTERVAL 1 SECOND; -- 包含最后一天23:59:59

67. 本周第一天与最后一天

场景：查询本周数据（注意：不同数据库对“周”的定义不同，MySQL默认周日为一周第一天）。
代码示例：

-- 本周第一天（MySQL，周一为一周第一天）
SELECT DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY);

-- 本周最后一天（MySQL，周日）
SELECT DATE_ADD(CURDATE(), INTERVAL 6 - WEEKDAY(CURDATE()) DAY);

-- 查询本周订单
SELECT * FROM order 
WHERE create_time BETWEEN 
  DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AND
  DATE_ADD(CURDATE(), INTERVAL 6 - WEEKDAY(CURDATE()) DAY) + INTERVAL 1 DAY - INTERVAL 1 SECOND;

68. 判断日期是否为工作日

场景：筛选工作日的数据（排除周六日）。
代码示例：

-- MySQL：WEEKDAY返回0-6（0=周一，6=周日）
SELECT * FROM order 
WHERE WEEKDAY(create_time) < 5; -- 0-4为工作日（周一至周五）

-- SQL Server：DATEPART(dw, ...)返回1-7（1=周日，7=周六）
SELECT * FROM order 
WHERE DATEPART(dw, create_time) BETWEEN 2 AND 6; -- 2-6为周一至周五

69. 时间戳与日期转换

场景：Unix时间戳（秒/毫秒）与日期字符串互转。
代码示例：

-- 日期转时间戳（秒，MySQL）
SELECT UNIX_TIMESTAMP('2023-10-22 15:30:00'); -- 1697962200

-- 时间戳转日期（MySQL）
SELECT FROM_UNIXTIME(1697962200, '%Y-%m-%d %H:%i:%s'); -- 2023-10-22 15:30:00

-- 毫秒时间戳转日期（需先转秒）
SELECT FROM_UNIXTIME(1697962200000 / 1000);

70. 处理时区问题

场景：转换不同时区的时间（如将UTC时间转为北京时间）。
代码示例：

-- MySQL：将UTC时间转为北京时间（UTC+8）
SELECT CONVERT_TZ(utc_time, '+00:00', '+08:00');

-- 或直接加8小时
SELECT utc_time + INTERVAL 8 HOUR AS beijing_time;

八、字符串处理技巧（71-80）

71. 字符串拼接（`CONCAT`/`||`）

场景：将多个字符串拼接为一个（如拼接姓名和ID）。
代码示例：

-- MySQL：CONCAT（NULL会导致结果为NULL）
SELECT CONCAT(username, '(', id, ')') AS user_info FROM user;

-- MySQL：CONCAT_WS（用分隔符拼接，忽略NULL）
SELECT CONCAT_WS('-', '2023', '10', '22') AS date; -- 2023-10-22

-- PostgreSQL/SQL Server：|| 运算符
SELECT username || '(' || id || ')' AS user_info FROM user;

72. 字符串长度（`LENGTH`/`CHAR_LENGTH`）

场景：获取字符串长度（字节数/字符数）。
代码示例：

-- 字节长度（LENGTH，受编码影响，如UTF8中汉字占3字节）
SELECT LENGTH('中国'); -- 6（UTF8编码）

-- 字符长度（CHAR_LENGTH，与编码无关）
SELECT CHAR_LENGTH('中国'); -- 2
SELECT CHAR_LENGTH('abc'); -- 3

73. 字符串截取（`SUBSTRING`）

场景：截取字符串的部分内容（如提取手机号前3位）。
代码示例：

-- 从第1位开始，截取3个字符（MySQL/SQL Server）
SELECT SUBSTRING(phone, 1, 3) AS phone_prefix FROM user; -- 138

-- 从第4位开始，截取到末尾（MySQL）
SELECT SUBSTRING(phone, 4) FROM user; -- ****5678

-- PostgreSQL：用SUBSTR
SELECT SUBSTR(phone, 1, 3) AS phone_prefix FROM user;

74. 字符串替换（`REPLACE`）

场景：替换字符串中的指定内容（如打码手机号）。
代码示例：

-- 替换手机号中间4位为*
SELECT REPLACE(phone, SUBSTRING(phone, 4, 4), '****') AS masked_phone 
FROM user;
-- 13812345678 → 138****5678

75. 大小写转换（`UPPER`/`LOWER`）

场景：统一字符串大小写（如查询时忽略大小写）。
代码示例：

-- 转为大写
SELECT UPPER(username) AS upper_name FROM user;

-- 转为小写
SELECT LOWER(email) AS lower_email FROM user;

-- 忽略大小写查询（如查询包含“admin”的用户名，不区分大小写）
SELECT * FROM user WHERE LOWER(username) LIKE '%admin%';

76. 去除首尾空格（`TRIM`）

场景：清理字符串前后的空格（避免因空格导致的查询失败）。
代码示例：

-- 去除首尾空格
SELECT TRIM(username) AS trimmed_name FROM user;

-- 去除左侧空格
SELECT LTRIM(username) FROM user;

-- 去除右侧空格
SELECT RTRIM(username) FROM user;

-- 查询时清理空格（避免因用户输入空格导致匹配失败）
SELECT * FROM user WHERE TRIM(username) = '张三';

77. 字符串分割（`SUBSTRING_INDEX`/`STRING_SPLIT`）

场景：按分隔符分割字符串（如分割逗号分隔的标签）。
代码示例：

-- MySQL：SUBSTRING_INDEX（获取第n个分隔符前/后的内容）
SELECT SUBSTRING_INDEX('a,b,c,d', ',', 2); -- a,b（前2个）
SELECT SUBSTRING_INDEX('a,b,c,d', ',', -2); -- c,d（后2个）

-- SQL Server 2016+：STRING_SPLIT（返回表）
SELECT value FROM STRING_SPLIT('a,b,c,d', ','); -- 行：a、b、c、d

78. 字符串填充（`LPAD`/`RPAD`）

场景：在字符串左侧/右侧填充字符（如补零到固定长度）。
代码示例：

-- 左侧补零，总长度5（如订单号补零）
SELECT LPAD(order_no, 5, '0') AS padded_order_no FROM order; -- 123 → 00123

-- 右侧补空格，总长度10
SELECT RPAD(username, 10, ' ') AS padded_name FROM user;

79. 判断字符串包含（`LIKE`/`CHARINDEX`）

场景：判断字符串是否包含指定子串（如查询包含“技术”的部门）。
代码示例：

-- LIKE（简单包含）
SELECT * FROM dept WHERE name LIKE '%技术%';

-- 位置判断（返回子串位置，0表示不包含）
SELECT CHARINDEX('技术', name) AS pos FROM dept; -- MySQL用LOCATE
SELECT * FROM dept WHERE LOCATE('技术', name) > 0; -- MySQL

80. 正则表达式匹配（`REGEXP`）

场景：复杂模式匹配（如验证手机号、邮箱格式）。
代码示例：

-- MySQL：查询手机号（11位数字，以1开头）
SELECT * FROM user WHERE phone REGEXP '^1[0-9]{10}$';

-- 查询邮箱（含@和.）
SELECT * FROM user WHERE email REGEXP '^[a-zA-Z0-9]+@[a-zA-Z0-9]+\.[a-zA-Z0-9]+$';

-- PostgreSQL：用~运算符
SELECT * FROM user WHERE phone ~ '^1[0-9]{10}$';

注意：正则表达式匹配效率较低，非必要时用LIKE；复杂验证建议在应用层处理。

九、性能优化技巧（81-90）

81. 使用索引（基础）

场景：为查询频繁的字段创建索引，提升查询速度（如WHERE、JOIN、ORDER BY后的字段）。
代码示例：

-- 为常用过滤字段创建索引
CREATE INDEX idx_user_dept_id ON user(dept_id); -- 加速WHERE dept_id=?

-- 为连接字段创建索引
CREATE INDEX idx_order_user_id ON order(user_id); -- 加速JOIN user.id=order.user_id

-- 为排序字段创建索引
CREATE INDEX idx_order_create_time ON order(create_time); -- 加速ORDER BY create_time

注意：索引会减慢插入/更新/删除速度（需维护索引），避免为低频查询字段建索引。

82. 复合索引（多字段索引）

场景：查询条件包含多个字段时，创建复合索引（遵循“最左前缀原则”）。
代码示例：

-- 复合索引（dept_id在前，status在后）
CREATE INDEX idx_user_dept_status ON user(dept_id, status);

-- 可加速以下查询（匹配最左前缀）
SELECT * FROM user WHERE dept_id = 3; -- 有效
SELECT * FROM user WHERE dept_id = 3 AND status = 1; -- 有效

-- 无法加速（不匹配最左前缀）
SELECT * FROM user WHERE status = 1; -- 无效

注意：复合索引字段顺序很重要，将过滤性强（值分布散）的字段放前面。

83. 覆盖索引（包含查询所需所有字段）

场景：索引包含查询的所有字段，无需回表查询数据（提升效率）。
代码示例：

-- 查询需要id、username、dept_id（id为主键，已包含）
-- 创建覆盖索引（包含username和dept_id）
CREATE INDEX idx_user_dept_name ON user(dept_id, username);

-- 此查询可直接通过索引完成（覆盖索引）
SELECT id, username, dept_id FROM user WHERE dept_id = 3;

84. 避免索引失效（常见情况）

场景：某些操作会导致索引失效，需规避。
常见失效情况：

WHERE中用函数或表达式操作索引字段（如WHERE SUBSTR(phone, 1, 3) = '138'）；
LIKE以%开头（如WHERE username LIKE '%三'）；
WHERE中用OR连接非索引字段（如WHERE dept_id=3 OR age=25，age无索引）；
隐式类型转换（如WHERE phone = 13812345678，phone为字符串类型）。

优化示例：

-- 低效：函数操作索引字段（索引失效）
SELECT * FROM user WHERE SUBSTR(phone, 1, 3) = '138';

-- 高效：避免函数，直接匹配（索引有效）
SELECT * FROM user WHERE phone LIKE '138%';

85. 用`EXPLAIN`分析执行计划

场景：诊断查询性能问题，查看是否使用索引。
代码示例：

-- 在查询前加EXPLAIN
EXPLAIN SELECT * FROM user WHERE dept_id = 3 AND status = 1;

关键参数说明：

type：连接类型（const> eq_ref> ref> range> ALL，ALL表示全表扫描，性能最差）；
key：实际使用的索引（NULL表示未使用索引）；
rows：预计扫描的行数（值越小越好）。

86. 分页查询优化（避免大偏移量）

场景：LIMIT M OFFSET N中N过大时（如LIMIT 10 OFFSET 100000），性能差。
优化方案：用WHERE条件基于索引字段分页（如按自增ID）。
代码示例：

-- 低效：大偏移量（需扫描100010行后取10行）
SELECT * FROM user ORDER BY id LIMIT 10 OFFSET 100000;

-- 高效：用WHERE条件定位起点（只需扫描10行）
SELECT * FROM user WHERE id > 100000 ORDER BY id LIMIT 10;

注意：需确保id有序且连续（或用其他有序索引字段）。

87. 避免全表扫描（`ALL`）

场景：全表扫描（EXPLAIN中type=ALL）在大表中性能极差。
优化措施：

为WHERE、JOIN、ORDER BY字段创建索引；
避免SELECT *，只查需要的字段（可能命中覆盖索引）；
增加过滤条件，减少返回行数（如WHERE status=1）。

88. 批量操作替代循环单条操作

场景：循环插入/更新单条记录效率低，改为批量操作。
代码示例：

-- 低效：循环单条插入
INSERT INTO log (content) VALUES ('a');
INSERT INTO log (content) VALUES ('b');

-- 高效：批量插入
INSERT INTO log (content) VALUES ('a'), ('b'), ('c');

89. 避免`SELECT *`

场景：SELECT *会查询所有字段，包括不需要的大字段（如text），增加IO和内存消耗。
优化：明确指定需要的字段。
代码示例：

-- 低效：查询所有字段
SELECT * FROM product;

-- 高效：只查需要的字段
SELECT id, name, price FROM product;

90. 合理使用临时表与视图

场景：复杂查询可拆分为临时表（一次性）或视图（复用），提升可读性和性能。
代码示例：

-- 临时表（会话结束后自动删除）
CREATE TEMPORARY TABLE temp_sale AS 
SELECT user_id, SUM(amount) AS total FROM order GROUP BY user_id;

-- 查询临时表
SELECT * FROM temp_sale WHERE total > 1000;

-- 视图（永久保存查询逻辑，不存储数据）
CREATE VIEW v_user_order AS 
SELECT u.id, u.username, COUNT(o.id) AS order_count 
FROM user u
LEFT JOIN order o ON u.id = o.user_id
GROUP BY u.id;

-- 查询视图
SELECT * FROM v_user_order;

十、其他实用技巧（91-100）

91. 条件逻辑（`CASE WHEN`）

场景：查询时根据条件返回不同值（如将状态码转换为文字）。
代码示例：

-- 状态码转换
SELECT 
  id,
  username,
  CASE status 
    WHEN 0 THEN '禁用' 
    WHEN 1 THEN '正常' 
    ELSE '未知' 
  END AS status_text 
FROM user;

-- 范围条件
SELECT 
  id,
  salary,
  CASE 
    WHEN salary < 5000 THEN '低收入' 
    WHEN salary < 10000 THEN '中等收入' 
    ELSE '高收入' 
  END AS salary_level 
FROM user;

92. 随机取N条记录（`ORDER BY RAND()`）

场景：随机推荐（如随机取3个商品）。
代码示例：

-- MySQL/PostgreSQL：随机取3条
SELECT * FROM product ORDER BY RAND() LIMIT 3;

-- SQL Server：用NEWID()
SELECT TOP 3 * FROM product ORDER BY NEWID();

注意：ORDER BY RAND()在大表中效率低（需为所有行生成随机数并排序），可结合索引优化（如随机取ID再查询）。

93. 空值处理（`COALESCE`/`IFNULL`）

场景：将NULL转换为默认值（如查询时显示“无”代替NULL）。
代码示例：

-- COALESCE（通用，返回第一个非NULL值）
SELECT username, COALESCE(email, '无邮箱') AS email FROM user;

-- MySQL：IFNULL（两个参数）
SELECT username, IFNULL(email, '无邮箱') AS email FROM user;

-- SQL Server：ISNULL（两个参数）
SELECT username, ISNULL(email, '无邮箱') AS email FROM user;

94. 行转列（`CASE WHEN`+聚合）

场景：将行数据转换为列（如按月份统计销售额，列显示1-12月）。
代码示例：

-- 按月份行转列
SELECT 
  product_id,
  SUM(CASE WHEN month = 1 THEN amount ELSE 0 END) AS jan,
  SUM(CASE WHEN month = 2 THEN amount ELSE 0 END) AS feb,
  ... -- 其他月份
  SUM(CASE WHEN month = 12 THEN amount ELSE 0 END) AS dec
FROM sale
GROUP BY product_id;

95. 列转行（`UNION ALL`）

场景：将列数据转换为行（如将1-12月销售额转为多行）。
代码示例：

-- 将列转行
SELECT product_id, 'jan' AS month, jan AS amount FROM product_sale
UNION ALL
SELECT product_id, 'feb' AS month, feb AS amount FROM product_sale
UNION ALL
... -- 其他月份
SELECT product_id, 'dec' AS month, dec AS amount FROM product_sale;

96. 批量插入并返回自增ID

场景：插入多条记录后，获取生成的自增ID（用于后续操作）。
代码示例：

-- MySQL：用LAST_INSERT_ID()获取第一个ID，按自增步长计算其他ID
INSERT INTO user (username) VALUES ('a'), ('b'), ('c');
SELECT LAST_INSERT_ID() AS first_id; -- 假设返回100，则其他ID为101、102

-- PostgreSQL：用RETURNING返回所有ID
INSERT INTO user (username) VALUES ('a'), ('b'), ('c') RETURNING id;

97. 计算百分比（`ROUND`+聚合）

场景：计算占比（如各部门人数占总人数的百分比）。
代码示例：

-- 计算各部门人数占比
SELECT 
  dept_id,
  COUNT(*) AS dept_count,
  ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM user), 2) AS percentage -- 保留2位小数
FROM user 
GROUP BY dept_id;

98. 事务控制（`BEGIN`/`COMMIT`/`ROLLBACK`）

场景：确保多步操作要么全成功，要么全失败（如转账：扣钱+加钱）。
代码示例：

-- 开始事务
BEGIN;

-- 步骤1：扣除用户A的钱
UPDATE account SET balance = balance - 100 WHERE user_id = 1;

-- 步骤2：增加用户B的钱
UPDATE account SET balance = balance + 100 WHERE user_id = 2;

-- 检查是否有错误，无错误则提交
COMMIT;

-- 若有错误，回滚（取消所有操作）
-- ROLLBACK;

99. 快速复制表结构（含数据/不含数据）

场景：创建与原表结构相同的表（用于备份或测试）。
代码示例：

-- 复制表结构和数据
CREATE TABLE user_copy AS SELECT * FROM user;

-- 只复制表结构（不含数据）
CREATE TABLE user_empty AS SELECT * FROM user WHERE 1=0; -- 条件永远为假，无数据

-- 复制表结构和部分数据
CREATE TABLE user_active AS SELECT * FROM user WHERE status = 1;

100. 查询表结构（元数据）

场景：查看表的字段、类型、备注等信息（不同数据库语法不同）。
代码示例：

-- MySQL：查询表结构
DESCRIBE user; -- 简写：DESC user
-- 或查询information_schema
SELECT * FROM information_schema.COLUMNS WHERE TABLE_NAME = 'user';

-- SQL Server：
EXEC sp_help 'user';

-- PostgreSQL：
\d user;