SQL 笔记（年代久远，归档博客）

适用场景：前端开发中偶尔写 SQL、做全栈开发时的数据库操作参考。
目标数据库：PostgreSQL 优先，兼顾 MySQL 通用语法。

第一章：SELECT 基础

核心原则

避免 SELECT \*：明确列出所需列，减少 IO 和网络传输。生产代码中 SELECT * 是坏习惯，因为表结构变更会导致查询结果变化。
为列取别名：使用 AS（可省略），在子查询和复杂表达式中尤其重要。
WHERE 筛选：支持 =、<、>、<=、>=、<>、!=，配合 AND、OR、NOT。

NULL 处理

-- 判断 NULL
WHERE column IS NULL;
WHERE column IS NOT NULL;

-- 用 COALESCE 替代 Oracle 的 nvl
SELECT COALESCE(column, 'default') FROM table;

CASE WHEN（通用 if-else）

SELECT 
  CASE 
    WHEN sal > 3000 THEN 'high'
    WHEN sal > 1500 THEN 'mid'
    ELSE 'low'
  END AS salary_level
FROM emp;

第二章：ORDER BY 排序

-- 单列排序（默认 ASC）
SELECT * FROM emp ORDER BY sal DESC;

-- 多列排序：优先级从左到右
SELECT * FROM emp ORDER BY deptno ASC, sal DESC;

-- NULL 值排序
ORDER BY column ASC NULLS LAST;   -- NULL 排在最后
ORDER BY column ASC NULLS FIRST;  -- NULL 排在最前

-- 用 CASE 让关键值排前面
ORDER BY CASE WHEN job = 'MANAGER' THEN 0 ELSE 1 END, sal DESC;

第三章：多表操作

JOIN 类型

类型	说明	示例
`INNER JOIN`	只返回匹配的行	`A JOIN B ON A.id = B.a_id`
`LEFT JOIN`	左表全部保留，右表无匹配填 NULL	`A LEFT JOIN B ON ...`
`RIGHT JOIN`	右表全部保留	`A RIGHT JOIN B ON ...`
`FULL OUTER JOIN`	两表都保留，无匹配填 NULL	PostgreSQL 支持

MySQL 不直接支持 FULL OUTER JOIN，需用 LEFT JOIN + UNION + RIGHT JOIN 模拟。

显式 JOIN 优于隐式写法：A JOIN B ON condition 比 A, B WHERE condition 更清晰，条件与连接逻辑分离。

集合操作

-- 并集（去重）
SELECT a FROM t1 UNION SELECT a FROM t2;

-- 并集（不去重，更快）
SELECT a FROM t1 UNION ALL SELECT a FROM t2;

-- 交集
SELECT a FROM t1 INTERSECT SELECT a FROM t2;

-- 差集（PostgreSQL / MySQL 8.0+）
SELECT a FROM t1 EXCEPT SELECT a FROM t2;

注意：NOT IN 遇到 NULL 会返回空结果，建议用 NOT EXISTS 替代。

横向连接（LATERAL，PostgreSQL 独有）

允许子查询引用前面 FROM 项的列：

SELECT p.id, v.*
FROM polygons p,
LATERAL vertices(p.poly) v;  -- v 可以使用 p 的列

第四章：INSERT / UPDATE / DELETE

INSERT

-- 显式列（推荐）
INSERT INTO weather (city, temp_lo, temp_hi, date)
VALUES ('San Francisco', 46, 50, '1994-11-27');

-- 从查询插入
INSERT INTO table_b SELECT * FROM table_a WHERE condition;

UPDATE

-- 基本更新
UPDATE emp SET sal = sal * 1.1 WHERE deptno = 10;

-- 关联更新（PostgreSQL 用 FROM）
UPDATE emp e
SET sal = ns.sal
FROM new_sal ns
WHERE e.deptno = ns.deptno;

-- RETURNING 返回更新后的值（PostgreSQL / MySQL 8.0+ 不支持）
UPDATE emp SET sal = sal * 1.1 WHERE deptno = 10
RETURNING empno, ename, sal;

DELETE

DELETE FROM products WHERE price = 10;

去重删除：先用 SELECT 找出重复行，确认后再替换为 DELETE。

第五章：聚合与分组

基础聚合

SELECT deptno, COUNT(*), AVG(sal), MAX(sal)
FROM emp
GROUP BY deptno
HAVING COUNT(*) > 3;   -- HAVING 在分组后筛选

WHERE 与 HAVING 的区别

WHERE：在分组和聚合之前筛选行，不能包含聚合函数。
HAVING：在分组和聚合之后筛选分组，通常包含聚合函数。

FILTER 子句（PostgreSQL）

对单个聚合函数进行条件过滤：

SELECT 
  city,
  COUNT(*) FILTER (WHERE temp_lo < 45) AS cold_days,
  MAX(temp_lo) AS max_temp
FROM weather
GROUP BY city;

第六章：窗口函数（重点更新）

窗口函数是现代化 SQL 中最重要的技能之一，不折叠行，在保留原始行的基础上添加计算值。

语法结构

window_function() OVER (
  PARTITION BY column   -- 分组
  ORDER BY column       -- 组内排序
  frame_clause          -- 窗口帧
)

排名函数

函数	行为	示例结果
`ROW_NUMBER()`	唯一序号，无重复	1, 2, 3, 4
`RANK()`	相同值同排名，后续跳号	1, 2, 2, 4
`DENSE_RANK()`	相同值同排名，后续不跳号	1, 2, 2, 3

-- 每个部门薪资前三名
WITH ranked AS (
  SELECT 
    deptno, ename, sal,
    ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS rn
  FROM emp
)
SELECT * FROM ranked WHERE rn <= 3;

前后行访问：LAG / LEAD

SELECT 
  sale_date,
  amount,
  LAG(amount) OVER (ORDER BY sale_date) AS prev_amount,   -- 前一行
  LEAD(amount) OVER (ORDER BY sale_date) AS next_amount,  -- 后一行
  amount - LAG(amount) OVER (ORDER BY sale_date) AS change
FROM sales;

LAG 和 LEAD 常用于计算环比、同比、前后差异等。

累计与滑动窗口

-- 累计求和
SELECT 
  trx_date,
  trx_cnt,
  SUM(trx_cnt) OVER (ORDER BY trx_date 
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM trx_log;

-- 移动平均（前 3 行）
SELECT 
  trx_date,
  trx_cnt,
  AVG(trx_cnt) OVER (ORDER BY trx_date 
                     ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM trx_log;

帧类型区别：

ROWS：按物理行数计算
RANGE：按值范围计算（Oracle 支持 RANGE INTERVAL，PostgreSQL 也支持动态窗口）

第七章：CTE（公共表表达式）

基本 CTE

WITH dept_stats AS (
  SELECT deptno, AVG(sal) AS avg_sal
  FROM emp
  GROUP BY deptno
)
SELECT e.ename, e.sal, d.avg_sal
FROM emp e JOIN dept_stats d ON e.deptno = d.deptno;

递归 CTE（替代 Oracle 的 CONNECT BY）

-- 组织架构树
WITH RECURSIVE org_tree AS (
  -- 锚点：根节点
  SELECT id, name, mgr_id, 1 AS level
  FROM emp WHERE mgr_id IS NULL
  
  UNION ALL
  
  -- 递归部分
  SELECT e.id, e.name, e.mgr_id, ot.level + 1
  FROM emp e
  JOIN org_tree ot ON e.mgr_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, id;

性能提示：CTE 在 PostgreSQL 中默认只计算一次，即使被引用多次。MySQL 中 CTE 可能被多次执行，需注意。

第八章：行列转换

行转列（Pivot）

传统方法：CASE WHEN + 聚合

SELECT 
  deptno,
  SUM(CASE WHEN job = 'CLERK' THEN 1 ELSE 0 END) AS clerks,
  SUM(CASE WHEN job = 'ANALYST' THEN 1 ELSE 0 END) AS analysts,
  SUM(CASE WHEN job = 'MANAGER' THEN 1 ELSE 0 END) AS managers
FROM emp
GROUP BY deptno;

PostgreSQL 专用：crosstab()

SELECT * FROM crosstab(
  'SELECT product_id, month, amount FROM sales ORDER BY 1, 2',
  'SELECT DISTINCT month FROM sales ORDER BY 1'
) AS ct(product_id INT, "January" NUMERIC, "February" NUMERIC);

列转行（Unpivot）

使用 UNION ALL：

SELECT product_id, 'January' AS month, january AS amount FROM sales
UNION ALL
SELECT product_id, 'February', february FROM sales;

第九章：日期时间处理

PostgreSQL 日期函数

-- 当前日期
SELECT CURRENT_DATE, NOW(), CURRENT_TIMESTAMP;

-- 日期加减
SELECT date_col + INTERVAL '5 days';
SELECT date_col + INTERVAL '3 months';
SELECT date_col + INTERVAL '1 year';

-- 日期差（返回天数）
SELECT date1 - date2;

-- 年龄差（返回年/月/日）
SELECT AGE(date1, date2);

-- 提取部分
SELECT EXTRACT(YEAR FROM date_col);
SELECT EXTRACT(MONTH FROM date_col);

-- 截断到月初/年初
SELECT DATE_TRUNC('month', NOW());
SELECT DATE_TRUNC('year', NOW());

与 MySQL / Oracle 的差异

需求	PostgreSQL	MySQL	Oracle
当前时间	`NOW()`	`NOW()`	`SYSDATE`
日期格式化	`TO_CHAR(date, 'YYYY-MM')`	`DATE_FORMAT(date, '%Y-%m')`	`TO_CHAR(date, 'YYYY-MM')`
日期加减月	`+ INTERVAL '3 months'`	`DATE_ADD(date, INTERVAL 3 MONTH)`	`ADD_MONTHS(date, 3)`
日期差（天）	`date1 - date2`	`DATEDIFF(date1, date2)`	`date1 - date2`

第十章：实用技巧

分页

-- 通用写法（PostgreSQL / MySQL）
SELECT * FROM emp ORDER BY empno LIMIT 10 OFFSET 20;

-- 标准 SQL
SELECT * FROM emp ORDER BY empno FETCH FIRST 10 ROWS ONLY;

不再推荐 Oracle 的 rownum 嵌套分页，现代数据库都支持 LIMIT / OFFSET。

模式统计（出现最频繁的值）

-- 找出 sal 出现次数最多的值
SELECT sal, COUNT(*) AS cnt
FROM emp
GROUP BY sal
ORDER BY cnt DESC
LIMIT 1;

找出两表差异

-- 在 A 中但不在 B 中
SELECT * FROM A
WHERE NOT EXISTS (SELECT 1 FROM B WHERE B.id = A.id);

-- 对称差集（两边都找）
(SELECT * FROM A EXCEPT SELECT * FROM B)
UNION ALL
(SELECT * FROM B EXCEPT SELECT * FROM A);

附：Oracle 特有语法的现代化替代

Oracle 写法	现代化替代	说明
`ROWNUM <= N` 分页	`LIMIT N` / `FETCH FIRST N ROWS ONLY`	更直观
`CONNECT BY` 层次查询	`WITH RECURSIVE`	标准 SQL，PostgreSQL/MySQL 8.0+ 支持
`NVL(a, b)`	`COALESCE(a, b)`	标准函数，支持多参数
`DECODE()`	`CASE WHEN`	可读性更好
`MERGE`	`INSERT ... ON CONFLICT`（PG）/ `ON DUPLICATE KEY UPDATE`（MySQL）	更简洁
`MODEL` 子句	用窗口函数 + CTE 替代	Oracle 独有，不通用
`INSERT ALL`	多条 `INSERT` 或 CTE	少见需求，可用程序逻辑处理

总结

主题	核心要点
SELECT	明确列名，避免 `SELECT *`
JOIN	显式 `JOIN ... ON` 优于隐式写法
NULL	用 `IS NULL` / `COALESCE`，避免 `NOT IN` 的 NULL 陷阱
窗口函数	`ROW_NUMBER`、`RANK`、`LAG`、`LEAD` 是核心技能
CTE	优先用 `WITH RECURSIVE` 替代 `CONNECT BY`
日期	PostgreSQL 用 `INTERVAL` 加减，`EXTRACT` 提取
分页	`LIMIT` / `OFFSET` 通用
行列转换	`CASE WHEN` + 聚合或 `crosstab()`

推荐数据库：如果新项目选型，PostgreSQL 功能最全面（支持 LATERAL、丰富的窗口函数、CTE 优化好）。MySQL 适合轻量场景，但复杂查询能力较弱。

posted on 2026-09-13 17:05 DavidXu2014 阅读(5) 评论(0) 收藏举报