这份SQL速查手册，从基础到进阶，我们用50条SQL常见语句覆盖了各类数据库操作。不仅包含SQL标准语法，我们还特别标注了各主流数据库的差异点，仅供参考。

一、基础操作（CRUD）

1、创建表（含：主键、外键、默认值、约束）

-- [通用]创建学生表（含主键、非空、默认值、外键） CREATE TABLE students ( id INT PRIMARY KEY AUTO_INCREMENT, -- [MySQL]自增主键 -- id SERIAL PRIMARY KEY, -- [PostgreSQL]SERIAL=自增整型 -- id INT IDENTITY(1,1) PRIMARY KEY, -- [SQL Server] -- id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, -- [Oracle 12c+] name VARCHAR(100) NOT NULL, -- 姓名非空 email VARCHAR(255) UNIQUE, -- 邮箱唯一 gender CHAR(1) CHECK (gender IN (M,F)), -- 性别检查约束 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- 默认当前时间 -- created_at TIMESTAMP DEFAULT NOW(), -- [PostgreSQL] -- created_at DATETIME2 DEFAULT GETDATE(), -- [SQL Server] -- created_at TIMESTAMP DEFAULT SYSDATE, -- [Oracle] class_id INT, FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE CASCADE -- 级联删除 -- [Oracle]我们需先创建约束名：CONSTRAINT fk_class FOREIGN KEY ... );

解析：

主键：唯一标识行，自动创建索引。外键：保证引用完整性，支持级联操作。默认值：插入时，若未指定，则自动填充。检查约束：限制字段取值范围。

进阶用法：

-- [PostgreSQL]生成列（计算列） ALTER TABLE students ADD COLUMN full_name TEXT GENERATED ALWAYS AS (name || ( || email || )) STORED; -- [SQL Server]计算列 ALTER TABLE students ADD full_name AS (name + ( + email + )); -- [MySQL 8.0+]生成列 ALTER TABLE students ADD COLUMN full_name VARCHAR(300) AS (CONCAT(name, (, email, ))) STORED;

2、插入数据（单行、多行、子查询、ON DUPLICATE KEY）

-- 单行插入 INSERT INTO students (name, email, gender, class_id) VALUES (张三, zhang@example.com, M, 1); -- 多行插入（标准SQL） INSERT INTO students (name, email, gender, class_id) VALUES (李四, li@example.com, F, 2), (王五, wang@example.com, M, 1); -- 从其他表插入（子查询） INSERT INTO students (name, email, gender, class_id) SELECT name, email, gender, 1 FROM temp_students WHERE status = active; -- [MySQL]存在则更新（UPSERT） INSERT INTO students (id, name, email) VALUES (1, 张三, zhang@new.com) ON DUPLICATE KEY UPDATE name = VALUES(name), email = VALUES(email); -- [PostgreSQL]UPSERT（ON CONFLICT） INSERT INTO students (id, name, email) VALUES (1, 张三, zhang@new.com) ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email; -- [SQL Server]MERGE实现UPSERT MERGE students AS target USING (SELECT 1 as id, 张三 as name, zhang@new.com as email) AS source ON target.id = source.id WHEN MATCHED THEN UPDATE SET name = source.name, email = source.email WHEN NOT MATCHED THEN INSERT (id, name, email) VALUES (source.id, source.name, source.email);

解析：

ON DUPLICATE KEY UPDATE是MySQL特有。ON CONFLICT是PostgreSQL 9.5+标准。MERGE是SQL Server/Oracle标准语法，功能最全。

进阶用法：

-- [PostgreSQL]返回插入的ID（用于后续关联） INSERT INTO students (...) VALUES (...) RETURNING id; -- [MySQL]获取自增ID SELECT LAST_INSERT_ID(); -- [SQL Server]OUTPUT子句 INSERT INTO students (...) OUTPUT INSERTED.id VALUES (...);

3、查询基础（SELECT+WHERE+ORDER BY+LIMIT）

-- 基础查询+条件+排序+分页 SELECT id, name, email, created_at FROM students WHERE class_id = 1 AND gender = M AND created_at >= 2025-01-01 ORDER BY created_at DESC LIMIT 10 OFFSET 20; -- [MySQL/PostgreSQL] -- [SQL Server]分页 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY created_at DESC) as rn FROM students WHERE class_id = 1 ) t WHERE rn BETWEEN 21 AND 30; -- [Oracle 12c+]分页 SELECT * FROM students WHERE class_id = 1 ORDER BY created_at DESC OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

解析：

LIMIT OFFSET：MySQL/PostgreSQL标准，简单高效。ROW_NUMBER()：SQL Server/Oracle传统分页。OFFSET FETCH：Oracle 12c+/SQL Server 2012+标准语法。

进阶用法：

-- 随机取3条 SELECT * FROM students ORDER BY RAND() LIMIT 3; -- [MySQL] SELECT * FROM students ORDER BY RANDOM() LIMIT 3; -- [PostgreSQL] SELECT TOP 3 * FROM students ORDER BY NEWID(); -- [SQL Server] -- 条件搜索（⚠️ 我们需注意：安全防注入） -- 在应用层，我们应使用参数绑定： -- SELECT ... WHERE name = ? AND class_id = ?

4、更新数据（UPDATE+JOIN+条件）

-- 基础更新 UPDATE students SET email = new@example.com, updated_at = NOW() WHERE id = 1; -- [MySQL/PostgreSQL]关联更新（我们使用JOIN） UPDATE students s JOIN classes c ON s.class_id = c.id SET s.email = CONCAT(s.name, @, c.school, .edu) WHERE c.school = 清华; -- [SQL Server]关联更新 UPDATE s SET email = s.name + @ + c.school + .edu FROM students s JOIN classes c ON s.class_id = c.id WHERE c.school = 清华; -- [Oracle]关联更新（我们使用子查询） UPDATE students s SET email = ( SELECT s.name || @ || c.school || .edu FROM classes c WHERE c.id = s.class_id ) WHERE EXISTS ( SELECT 1 FROM classes c WHERE c.id = s.class_id AND c.school = 清华 );

解析：

MySQL/PostgreSQL支持UPDATE ... FROM ...或UPDATE ... JOIN ...SQL Server使用UPDATE alias SET ... FROM ... JOIN ...Oracle需使用子查询或MERGE

进阶用法：

-- 限制更新行数（防误操作） UPDATE students SET status = inactive WHERE ... LIMIT 1; -- [MySQL] -- 返回被更新的行（PostgreSQL） UPDATE students SET ... WHERE ... RETURNING *; -- 条件更新（CASE WHEN） UPDATE students SET grade = CASE WHEN score >= 90 THEN A WHEN score >= 80 THEN B ELSE C END;

5、删除数据（DELETE+JOIN+TRUNCATE）

-- 删除指定行 DELETE FROM students WHERE id = 1; -- [MySQL/PostgreSQL]关联删除 DELETE s FROM students s JOIN classes c ON s.class_id = c.id WHERE c.name = 已解散班级; -- [SQL Server] DELETE s FROM students s JOIN classes c ON s.class_id = c.id WHERE c.name = 已解散班级; -- [Oracle]（只能子查询） DELETE FROM students WHERE class_id IN ( SELECT id FROM classes WHERE name = 已解散班级 ); -- 快速清空表（不记录日志，不可回滚） TRUNCATE TABLE students; -- [Oracle]需加COMMIT;

解析：

DELETE可加WHERE，可回滚，记录日志。TRUNCATE快速清空，重置自增ID（MySQL/PostgreSQL），不可回滚。

进阶用法：

-- 删除重复数据（保留最小ID） DELETE s1 FROM students s1 JOIN students s2 WHERE s1.email = s2.email AND s1.id > s2.id; -- [PostgreSQL]我们使用CTE删除 WITH duplicates AS ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) as rn FROM students ) DELETE FROM students WHERE id IN (SELECT id FROM duplicates WHERE rn > 1); -- 软删除（推荐生产环境） ALTER TABLE students ADD COLUMN is_deleted BOOLEAN DEFAULT FALSE; UPDATE students SET is_deleted = TRUE WHERE ...; -- 查询时加：WHERE is_deleted = FALSE

二、复杂JOIN/子查询

6、INNER/LEFT/RIGHT/FULL JOIN

-- 内连接（只返回匹配行） SELECT s.name, c.name as class_name FROM students s INNER JOIN classes c ON s.class_id = c.id; -- 左连接（返回左表所有+匹配右表） SELECT s.name, c.name as class_name FROM students s LEFT JOIN classes c ON s.class_id = c.id; -- 右连接（返回右表所有+匹配左表） SELECT s.name, c.name as class_name FROM students s RIGHT JOIN classes c ON s.class_id = c.id; -- 全外连接（左右表所有行）[PostgreSQL/SQL Server/Oracle] SELECT s.name, c.name as class_name FROM students s FULL OUTER JOIN classes c ON s.class_id = c.id; -- [MySQL]不支持FULL JOIN，需我们用UNION模拟 SELECT ... FROM students s LEFT JOIN classes c ... UNION SELECT ... FROM students s RIGHT JOIN classes c ... WHERE s.id IS NULL;

解析：

INNER JOIN：交集。LEFT JOIN：左表为主。FULL JOIN：并集（MySQL需模拟）。

进阶用法：

-- 自连接（查询同一班级的学生对） SELECT s1.name as student1, s2.name as student2 FROM students s1 JOIN students s2 ON s1.class_id = s2.class_id AND s1.id < s2.id; -- 我们使用USING简化（字段名相同） SELECT name, class_name FROM students JOIN classes USING (class_id); -- 我们需注意：字段名必须相同

7、子查询（标量、行、表、关联子查询）

-- 标量子查询（返回单值） SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count FROM users u; -- 行子查询（返回一行多列） SELECT * FROM products WHERE (category_id, price) = ( SELECT category_id, MAX(price) FROM products GROUP BY category_id LIMIT 1 ); -- 表子查询（派生表） SELECT u.name, o.total FROM users u JOIN ( SELECT user_id, SUM(amount) as total FROM orders GROUP BY user_id ) o ON u.id = o.user_id; -- 关联子查询（依赖外层） SELECT name, salary FROM employees e1 WHERE salary > ( SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id -- 依赖外层 e1 );

解析：

标量子查询：用于SELECT/WHERE，必须返回一行一列。行子查询：用于WHERE，返回一行多列。表子查询：FROM子句中，可JOIN。关联子查询：性能较差，尽量改写为JOIN。

进阶用法：

-- 我们用EXISTS替代IN（性能更好） SELECT * FROM students s WHERE EXISTS ( SELECT 1 FROM enrollments e WHERE e.student_id = s.id AND e.course_id = 101 ); -- 我们用NOT EXISTS查没有报名学生 SELECT * FROM students s WHERE NOT EXISTS ( SELECT 1 FROM enrollments e WHERE e.student_id = s.id );

8、UNION/UNION ALL/INTERSECT/EXCEPT

-- 合并结果集（去重） SELECT name FROM students WHERE class_id = 1 UNION SELECT name FROM teachers WHERE dept = 数学; -- 合并不去重（性能更好） SELECT name FROM students WHERE class_id = 1 UNION ALL SELECT name FROM teachers WHERE dept = 数学; -- 交集（PostgreSQL/SQL Server/Oracle） SELECT product_id FROM orders_2024 INTERSECT SELECT product_id FROM orders_2025; -- 差集（PostgreSQL/SQL Server/Oracle） SELECT product_id FROM orders_2024 EXCEPT SELECT product_id FROM orders_2025; -- [MySQL]模拟INTERSECT SELECT DISTINCT o1.product_id FROM orders_2024 o1 JOIN orders_2025 o2 ON o1.product_id = o2.product_id; -- [MySQL]模拟EXCEPT SELECT DISTINCT product_id FROM orders_2024 WHERE product_id NOT IN (SELECT product_id FROM orders_2025 WHERE product_id IS NOT NULL);

解析：

UNION：自动去重，排序。UNION ALL：保留重复，更快。INTERSECT/EXCEPT：集合运算，MySQL不支持。

进阶用法：

-- 多层UNION（注意括号） (SELECT ... UNION SELECT ...) UNION SELECT ...; -- 指定列别名（第一个SELECT定义结构） (SELECT name as label FROM students) UNION (SELECT dept_name as label FROM departments);

9、WITH子句（CTE - Common Table Expression）

-- 简单CTE（提升可读性） WITH high_score_students AS ( SELECT student_id, AVG(score) as avg_score FROM exam_results GROUP BY student_id HAVING AVG(score) > 90 ) SELECT s.name, h.avg_score FROM students s JOIN high_score_students h ON s.id = h.student_id; -- 多层CTE WITH cte1 AS (SELECT ...), cte2 AS (SELECT ... FROM cte1 WHERE ...), cte3 AS (SELECT ... FROM cte2 JOIN ...) SELECT * FROM cte3;

解析：

CTE提升SQL可读性和模块化。可递归。所有主流数据库支持（MySQL 8.0+）。

进阶用法：

-- CTE用于UPDATE/DELETE（PostgreSQL/SQL Server） WITH cte AS ( SELECT id FROM students WHERE class_id = 999 ) DELETE FROM enrollments WHERE student_id IN (SELECT id FROM cte); -- [MySQL 8.0+]也支持 WITH cte AS (...) UPDATE ... SET ... WHERE id IN (SELECT id FROM cte);

10、相关子查询优化（改写为JOIN/窗口函数）

-- 原始：我们查询每个学生最新订单 SELECT s.name, (SELECT o.order_date FROM orders o WHERE o.user_id = s.id ORDER BY o.order_date DESC LIMIT 1) as last_order FROM students s; -- 优化1：改写为LATERAL JOIN（PostgreSQL/MySQL 8.0+） SELECT s.name, o.order_date as last_order FROM students s JOIN LATERAL ( SELECT order_date FROM orders WHERE user_id = s.id ORDER BY order_date DESC LIMIT 1 ) o ON true; -- 优化2：我们使用窗口函数（推荐） WITH ranked_orders AS ( SELECT user_id, order_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date DESC) as rn FROM orders ) SELECT s.name, r.order_date as last_order FROM students s JOIN ranked_orders r ON s.id = r.user_id AND r.rn = 1;

解析：

相关子查询性能差（N+1问题）。优先改写为JOIN+窗口函数或LATERAL。

进阶用法：

-- 我们要查询高于部门平均工资的员工（窗口函数版） SELECT name, salary, dept_avg FROM ( SELECT name, salary, dept_id, AVG(salary) OVER (PARTITION BY dept_id) as dept_avg FROM employees ) t WHERE salary > dept_avg;

三、聚合与分组

11、GROUP BY+HAVING+多级聚合

-- 我们要查询每个班级平均分>85的学生人数和最高分 SELECT c.name as class_name, COUNT(*) as student_count, MAX(e.score) as max_score FROM classes c JOIN students s ON c.id = s.class_id JOIN exam_results e ON s.id = e.student_id GROUP BY c.name HAVING AVG(e.score) > 85 -- HAVING过滤分组后结果 ORDER BY student_count DESC;

解析：

GROUP BY按字段分组。HAVING是分组后的条件（WHERE是分组前）。可嵌套聚合函数：MAX(AVG(...))需用子查询或窗口函数。

进阶用法：

-- 分组后，保留原始明细（PostgreSQL/SQL Server/Oracle） SELECT *, COUNT(*) OVER (PARTITION BY class_id) as class_size, AVG(score) OVER (PARTITION BY class_id) as class_avg FROM exam_results; -- [MySQL 8.0+]同样支持窗口函数

12、ROLLUP/CUBE/GROUPING SETS（多维聚合）

-- [PostgreSQL/SQL Server/Oracle]ROLLUP：生成小计+总计 SELECT dept, gender, COUNT(*) as cnt, AVG(salary) as avg_salary FROM employees GROUP BY ROLLUP(dept, gender); -- 生成(dept,gender), (dept,null), (null,null) -- CUBE：所有组合 GROUP BY CUBE(dept, gender); -- (dept,gender), (dept,null), (null,gender), (null,null) -- GROUPING SETS：自定义组合 GROUP BY GROUPING SETS ((dept), (gender), ()); -- [MySQL]不支持，我们需用UNION模拟 SELECT dept, gender, COUNT(*), AVG(salary) FROM employees GROUP BY dept, gender UNION ALL SELECT dept, NULL, COUNT(*), AVG(salary) FROM employees GROUP BY dept UNION ALL SELECT NULL, NULL, COUNT(*), AVG(salary) FROM employees;

解析：

ROLLUP：层级小计（如：年→月→日）。CUBE：所有维度组合。GROUPING SETS：自由组合。

进阶用法：

-- 判断是否为汇总行（PostgreSQL/SQL Server） SELECT dept, gender, GROUPING(dept) as is_dept_total, -- 1=汇总行 GROUPING(gender) as is_gender_total, COUNT(*) FROM employees GROUP BY ROLLUP(dept, gender);

13、DISTINCT聚合与FILTER子句

-- 计算去重后的数量 SELECT COUNT(DISTINCT user_id) as unique_users, SUM(amount) as total_amount FROM orders; -- [PostgreSQL/SQL Server]FILTER子句（条件聚合） SELECT COUNT(*) FILTER (WHERE status = completed) as completed_orders, COUNT(*) FILTER (WHERE status = cancelled) as cancelled_orders, AVG(score) FILTER (WHERE score > 0) as avg_positive_score FROM orders; -- [MySQL]我们用CASE WHEN模拟 SELECT SUM(CASE WHEN status = completed THEN 1 ELSE 0 END) as completed_orders, AVG(CASE WHEN score > 0 THEN score END) as avg_positive_score FROM orders;

解析：

COUNT(DISTINCT ...)去重计数。FILTER是标准SQL，更简洁高效。MySQL需用CASE WHEN+聚合函数模拟。

14、STRING_AGG/GROUP_CONCAT/LISTAGG（字符串聚合）

-- [PostgreSQL]字符串聚合 SELECT class_id, STRING_AGG(name, , ORDER BY name) as student_names FROM students GROUP BY class_id; -- [MySQL] SELECT class_id, GROUP_CONCAT(name ORDER BY name SEPARATOR , ) as student_names FROM students GROUP BY class_id; -- [SQL Server] SELECT class_id, STRING_AGG(name, , ) WITHIN GROUP (ORDER BY name) as student_names FROM students GROUP BY class_id; -- [Oracle] SELECT class_id, LISTAGG(name, , ) WITHIN GROUP (ORDER BY name) as student_names FROM students GROUP BY class_id;

解析：

各数据库语法不同，但功能一致。支持排序、分隔符。我们要注意长度限制（Oracle默认4000字符）。

进阶用法：

-- [PostgreSQL]去重聚合 STRING_AGG(DISTINCT name, , ) -- [Oracle 21c+]去重 LISTAGG(DISTINCT name, , ) -- [MySQL]去重 GROUP_CONCAT(DISTINCT name ...)

15、自定义聚合函数（PostgreSQL/Oracle）

-- [PostgreSQL]创建自定义聚合：乘积 CREATE AGGREGATE product(numeric) ( SFUNC = numeric_mul, -- 累积函数 STYPE = numeric, -- 状态类型 INITCOND = 1 -- 初始值 ); SELECT product(price) FROM products WHERE category = Electronics; -- [Oracle]我们使用MODEL或PL/SQL（Procedural Language/SQL）来实现 -- 通常用EXP(SUM(LN(x)))近似（仅正数） SELECT EXP(SUM(LN(price))) FROM products WHERE price > 0;

解析：

PostgreSQL支持自定义聚合。Oracle/MySQL一般用数学变换或存储过程。

四、窗口函数

16、ROW_NUMBER()/RANK()/DENSE_RANK()

-- 按部门分组，按工资排序 SELECT name, dept, salary, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) as rn, -- 1,2,3,4（无并列） RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as rnk, -- 1,2,2,4（跳号） DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) as drnk -- 1,2,2,3（不跳号） FROM employees;

解析：

ROW_NUMBER()：严格排序，无重复序号。RANK()：并列排名，后续跳号（1,2,2,4）。DENSE_RANK()：并列排名，不跳号（1,2,2,3）。

进阶用法：

-- 我们要查询各部门工资前3名 WITH ranked AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) as rn FROM employees ) SELECT * FROM ranked WHERE rn <= 3;

17、LEAD()/LAG()（前后行访问）

-- 我们要查询学生成绩及与前一名的分差 SELECT name, score, LAG(score, 1) OVER (ORDER BY score DESC) as prev_score, score - LAG(score, 1) OVER (ORDER BY score DESC) as score_diff FROM exam_results; -- LEAD：获取下一行 LEAD(score, 1, 0) OVER (...) -- 第三个参数是默认值（无下一行时）

解析：

LAG(n)：前第n行。LEAD(n)：后第n行。常用在时间序列分析（环比、差值）。

18、NTILE()/PERCENT_RANK()/CUME_DIST()

-- 我们要将学生按成绩分为4档（优、良、中、差） SELECT name, score, NTILE(4) OVER (ORDER BY score DESC) as quartile FROM students; -- 百分位排名（0~1） PERCENT_RANK() OVER (ORDER BY score) -- 累计分布（<=当前值的比例） CUME_DIST() OVER (ORDER BY score)

解析：

NTILE(N)：分N桶，用在分组评级。PERCENT_RANK()：相对排名（最小=0，最大=1）。CUME_DIST()：累计分布函数。

19、SUM()/AVG() OVER（累计/移动平均）

-- 累计销售额 SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date) as running_total FROM sales; -- 移动平均（最近3天） SELECT order_date, amount, AVG(amount) OVER ( ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) as moving_avg_3d FROM sales;

解析：

ROWS BETWEEN ...是定义窗口范围。PRECEDING/FOLLOWING/CURRENT ROW。

进阶用法：

-- 按月累计 SUM(amount) OVER ( PARTITION BY YEAR(order_date), MONTH(order_date) ORDER BY order_date )

20、FIRST_VALUE()/LAST_VALUE()/NTH_VALUE()

-- 我们要查询每个部门最高工资者姓名 SELECT name, dept, salary, FIRST_VALUE(name) OVER ( PARTITION BY dept ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) as top_earner FROM employees; -- LAST_VALUE默认只到当前行，需我们指定范围 LAST_VALUE(name) OVER ( ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )

解析：

FIRST_VALUE/LAST_VALUE获取窗口内首尾值。注意：LAST_VALUE默认范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，需我们显式指定完整范围。

21、窗口函数性能优化

-- 优化：分区字段加索引 CREATE INDEX idx_emp_dept ON employees(dept); -- 我们要避免在ORDER BY使用表达式 -- 慢：ORDER BY UPPER(name) -- 快：ORDER BY name（我们需同时创建函数索引） -- [PostgreSQL]函数索引 CREATE INDEX idx_upper_name ON employees(UPPER(name)); -- 我们使用物化CTE减少重复计算 WITH sales_summary AS MATERIALIZED ( SELECT product_id, SUM(amount) as total FROM sales GROUP BY product_id ) SELECT ..., ROW_NUMBER() OVER (ORDER BY total DESC) ... FROM sales_summary;

五、递归CTE

22、递归查询组织架构（树形结构）

-- 我们要查询某员工的所有下属（向下递归） WITH RECURSIVE subordinates AS ( -- 初始成员（锚点） SELECT id, name, manager_id, 0 as level FROM employees WHERE id = 1 -- CEO UNION ALL -- 递归成员 SELECT e.id, e.name, e.manager_id, s.level + 1 FROM employees e JOIN subordinates s ON e.manager_id = s.id ) SELECT * FROM subordinates;

解析：

WITH RECURSIVE：PostgreSQL/MySQL 8.0+/SQLite。SQL Server/Oracle用WITH ...（无需RECURSIVE）。必须有终止条件（如：层级限制）。

23、查询路径（祖先路径/分隔符路径）

-- 生成路径：CEO>Manager>Employee WITH RECURSIVE hierarchy AS ( SELECT id, name, manager_id, name as path, 0 as level FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id, CONCAT(h.path, > , e.name), -- [MySQL/PostgreSQL] -- h.path + > + e.name, -- [SQL Server] h.level + 1 FROM employees e JOIN hierarchy h ON e.manager_id = h.id ) SELECT id, name, path, level FROM hierarchy;

解析：

适用于菜单、分类、评论回复等树形结构。路径可用于前端展示或权限匹配。

24、递归限制与优化

-- 我们要限制递归深度（防止死循环） WITH RECURSIVE cte AS ( SELECT ..., 1 as depth FROM ... WHERE ... UNION ALL SELECT ..., depth + 1 FROM ... JOIN cte WHERE depth < 10 -- ⚠️ 关键点 ) SELECT * FROM cte; -- [PostgreSQL]我们要防止递归查询执行过久（安全兜底） SET statement_timeout = 30s; -- SET LOCAL statement_timeout = 30s; -- 推荐使用LOCAL限定作用域 -- 优化：我们在manager_id字段加索引 CREATE INDEX idx_emp_manager ON employees(manager_id);

六、JSON/XML/数组处理

25、JSON查询与更新（MySQL/PostgreSQL）

-- [MySQL]查询JSON字段 SELECT name, JSON_EXTRACT(profile, $.age) as age, profile->$.city as city, -- 简写 profile->>$.city as city_text -- 去引号 FROM users; -- [PostgreSQL]查询JSONB SELECT name, profile->age as age, -- 返回jsonb profile->>city as city_text -- 返回text -- 更新JSON字段 UPDATE users SET profile = JSON_SET(profile, $.age, 30) WHERE id = 1; -- [MySQL] UPDATE users SET profile = profile || {"age": 30}::jsonb -- [PostgreSQL] WHERE id = 1;

解析：

MySQL：JSON_EXTRACT, ->, ->>, JSON_SETPostgreSQL：->, ->>, ||(合并), #>(路径)

26、JSON聚合与生成

-- [MySQL]生成JSON数组 SELECT class_id, JSON_ARRAYAGG(JSON_OBJECT(name, name, score, score)) as students FROM exam_results GROUP BY class_id; -- [PostgreSQL] SELECT class_id, JSONB_AGG(JSONB_BUILD_OBJECT(name, name, score, score)) as students FROM exam_results GROUP BY class_id; -- 从表生成JSON对象 SELECT JSON_OBJECT(id, id, name, name) FROM students LIMIT 1; -- {"id": 1, "name": "张三"}

27、数组操作（PostgreSQL）

-- [PostgreSQL]数组字段 CREATE TABLE tags ( id SERIAL PRIMARY KEY, name TEXT, keywords TEXT[] -- 字符串数组 ); -- 查询包含某关键词 SELECT * FROM tags WHERE 数据库 = ANY(keywords); -- 数组长度 SELECT name, ARRAY_LENGTH(keywords, 1) as tag_count FROM tags; -- 展开数组（转行） SELECT name, UNNEST(keywords) as keyword FROM tags; -- 聚合为数组 SELECT class_id, ARRAY_AGG(name) as student_names FROM students GROUP BY class_id;

解析：

ANY/ALL用于数组比较。UNNEST展开数组为多行。ARRAY_AGG行转数组。

28、XML处理（SQL Server/Oracle）

-- [SQL Server]查询XML SELECT T.c.value((name/text())[1], VARCHAR(100)) as name, T.c.value((age/text())[1], INT) as age FROM users_xml CROSS APPLY xml_data.nodes(/user) T(c); -- [Oracle] SELECT EXTRACTVALUE(xml_data, /user/name) as name, EXTRACTVALUE(xml_data, /user/age) as age FROM users_xml; -- 更新XML UPDATE users_xml SET xml_data.modify(replace value of (/user/age/text())[1] with "30") WHERE id = 1;

29、JSON/XML性能优化

-- [MySQL]我们对JSON字段创建虚拟列+索引 ALTER TABLE users ADD COLUMN city VARCHAR(100) GENERATED ALWAYS AS (profile->>$.city) STORED; CREATE INDEX idx_user_city ON users(city); -- [PostgreSQL]我们对JSONB创建GIN索引 CREATE INDEX idx_profile_gin ON users USING GIN(profile); -- [PostgreSQL]特定路径索引 CREATE INDEX idx_profile_city ON users((profile->>city));

七、事务控制与并发控制

30、事务控制（BEGIN/COMMIT/ROLLBACK）

-- 标准事务 BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; -- 回滚 BEGIN; ... ROLLBACK; -- [PostgreSQL/SQL Server]SAVEPOINT SAVEPOINT before_update; ... ROLLBACK TO SAVEPOINT before_update; RELEASE SAVEPOINT before_update;

31、隔离级别（READ COMMITTED/SERIALIZABLE...）

-- [PostgreSQL] BEGIN ISOLATION LEVEL SERIALIZABLE; ... -- [MySQL] SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; START TRANSACTION; -- [SQL Server] SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; BEGIN TRAN; -- 查看当前隔离级别 SHOW TRANSACTION ISOLATION LEVEL; -- [PostgreSQL/MySQL] DBCC USEROPTIONS; -- [SQL Server]

隔离级别

：

（1）READ UNCOMMITTED

：脏读。

（2）READ COMMITTED

（默认）：不可重复读。

（3）REPEATABLE READ

（MySQL默认）：幻读。

（4）SERIALIZABLE：完全隔离。

32、锁机制（FOR UPDATE/LOCK IN SHARE MODE）

-- [PostgreSQL/MySQL]行级锁（悲观锁） SELECT * FROM accounts WHERE id = 1 FOR UPDATE; -- [MySQL]共享锁 SELECT * FROM accounts WHERE id = 1 LOCK IN SHARE MODE; -- [SQL Server] SELECT * FROM accounts WITH (UPDLOCK) WHERE id = 1; -- [Oracle] SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

解析：

FOR UPDATE：排他锁，防止其他事务修改。用在“先查后改”场景（如：库存扣减）。

33、乐观锁（版本号/时间戳）

-- 表结构 ALTER TABLE products ADD COLUMN version INT DEFAULT 0; -- 更新时检查版本 UPDATE products SET stock = stock - 1, version = version + 1 WHERE id = 100 AND version = 5; -- ⚠️ 关键点：检查旧版本 -- 检查影响行数，0表示并发冲突 -- 应用层重试或报错

解析：

无锁，靠版本号检测冲突。适合读多写少场景。

34、死锁检测与处理

-- [PostgreSQL]查看锁 SELECT * FROM pg_locks; -- [MySQL]查看事务 SHOW ENGINE INNODB STATUS; -- [SQL Server] SELECT * FROM sys.dm_tran_locks; -- 避免死锁原则： -- 1、按固定顺序访问表/行 -- 2、减少事务粒度 -- 3、设置锁超时：SET lock_timeout = 5000; -- 5秒

八、索引优化/执行计划分析

35、索引类型与创建

-- B-Tree索引（默认） CREATE INDEX idx_student_class ON students(class_id); -- 唯一索引 CREATE UNIQUE INDEX idx_email ON students(email); -- 复合索引 CREATE INDEX idx_name_class ON students(name, class_id); -- [PostgreSQL]部分索引（条件索引） CREATE INDEX idx_active_users ON users(email) WHERE is_active = true; -- [MySQL]前缀索引 CREATE INDEX idx_name_prefix ON students(name(10)); -- [PostgreSQL/MySQL 8.0+]降序索引 CREATE INDEX idx_created_desc ON students(created_at DESC);

36、执行计划分析（EXPLAIN）

-- [PostgreSQL/MySQL] EXPLAIN ANALYZE SELECT * FROM students WHERE class_id = 1; -- [SQL Server] SET STATISTICS IO ON; SELECT * FROM students WHERE class_id = 1; -- 我们主要看： -- - type: index/ref/ALL（全表扫描） -- - rows: 预估行数 -- - cost: 代价 -- - actual time: 实际耗时

37、覆盖索引（Covering Index）

-- 查询只访问索引，不回表 CREATE INDEX idx_covering ON students(class_id, name, email); -- 以下查询可覆盖： SELECT name, email FROM students WHERE class_id = 1; -- [SQL Server]INCLUDE语法 CREATE INDEX idx_cover ON students(class_id) INCLUDE (name, email);

38、索引失效场景

-- 失效：左模糊 SELECT * FROM students WHERE name LIKE %三; -- 失效：函数操作 SELECT * FROM students WHERE UPPER(name) = ZHANG; -- 失效：类型转换 SELECT * FROM students WHERE class_id = 1; -- class_id是INT -- 失效：OR条件无索引 SELECT * FROM students WHERE name = 张三 OR email = ...; -- 优化：函数索引/表达式索引 CREATE INDEX idx_upper_name ON students((UPPER(name))); -- [PostgreSQL]

39、索引监控与维护

-- [PostgreSQL]查看索引使用率 SELECT schemaname, tablename, indexname, idx_scan as scans FROM pg_stat_user_indexes; -- 重建索引（碎片整理） REINDEX INDEX idx_student_class; -- [PostgreSQL] ALTER INDEX idx_student_class REBUILD; -- [SQL Server] -- [MySQL]分析表 ANALYZE TABLE students;

九、元数据查询/动态SQL/管理

40、查询元数据（表结构/索引/约束）

-- [PostgreSQL] SELECT column_name, data_type FROM information_schema.columns WHERE table_name = students; -- [MySQL] DESCRIBE students; -- 或 SHOW CREATE TABLE students; -- [SQL Server] EXEC sp_columns students; -- [Oracle] SELECT column_name, data_type FROM user_tab_columns WHERE table_name = STUDENTS;

41、动态SQL（拼接执行）

-- [PostgreSQL]使用EXECUTE DO $$ DECLARE table_name TEXT := students; sql TEXT; BEGIN sql := SELECT COUNT(*) FROM || quote_ident(table_name); EXECUTE sql; END $$; -- [SQL Server] DECLARE @sql NVARCHAR(MAX) = SELECT * FROM + @table_name; EXEC sp_executesql @sql; -- [MySQL]PREPARE SET @sql = CONCAT(SELECT * FROM , @table_name); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

⚠️ 安全警告：动态SQL易导致SQL注入！我们务必使用参数绑定或转义函数（如：quote_ident）。

42、数据库备份与恢复

# [PostgreSQL]备份 pg_dump -U user -d dbname -f backup.sql # 恢复 psql -U user -d dbname -f backup.sql # [MySQL] mysqldump -u user -p dbname > backup.sql mysql -u user -p dbname < backup.sql # [SQL Server] (T-SQL) BACKUP DATABASE dbname TO DISK = path.bak; RESTORE DATABASE dbname FROM DISK = path.bak;

43、性能监控（慢查询/连接数）

-- [MySQL]慢查询日志 SET GLOBAL slow_query_log = ON; SET GLOBAL long_query_time = 1; -- 秒 -- [PostgreSQL]日志配置（postgresql.conf） log_min_duration_statement = 1000 -- 1秒 -- 我们查看当前连接 SHOW PROCESSLIST; -- [MySQL] SELECT * FROM pg_stat_activity; -- [PostgreSQL] sp_who2; -- [SQL Server]

44、数据迁移（ETL/COPY/BULK INSERT）

-- [PostgreSQL]快速导入 COPY students FROM /path/data.csv WITH CSV HEADER; -- [MySQL] LOAD DATA INFILE /path/data.csv INTO TABLE students FIELDS TERMINATED BY , LINES TERMINATED BY \n IGNORE 1 ROWS; -- [SQL Server] BULK INSERT students FROM path\data.csv WITH (FORMAT=CSV);

45、分区表（大表优化）

-- [PostgreSQL 10+] CREATE TABLE sales ( id SERIAL, sale_date DATE, amount DECIMAL ) PARTITION BY RANGE (sale_date); CREATE TABLE sales_2025 PARTITION OF sales FOR VALUES FROM (2025-01-01) TO (2026-01-01); -- [MySQL 8.0+]RANGE分区示例 CREATE TABLE sales ( id INT AUTO_INCREMENT, sale_date DATE NOT NULL, amount DECIMAL(10,2), region VARCHAR(50), PRIMARY KEY (id, sale_date) -- ⚠️ 分区键必须包含在主键中 ) PARTITION BY RANGE (YEAR(sale_date)) ( PARTITION p2024 VALUES LESS THAN (2025), PARTITION p2025 VALUES LESS THAN (2026), PARTITION p2026 VALUES LESS THAN (2027), PARTITION pmax VALUES LESS THAN MAXVALUE ); -- [PostgreSQL 12+]HASH分区（负载均衡） CREATE TABLE user_logs ( id BIGSERIAL, user_id INT, log_time TIMESTAMP ) PARTITION BY HASH (user_id); CREATE TABLE user_logs_p0 PARTITION OF user_logs FOR VALUES WITH (MODULUS 4, REMAINDER 0); CREATE TABLE user_logs_p1 PARTITION OF user_logs FOR VALUES WITH (MODULUS 4, REMAINDER 1); CREATE TABLE user_logs_p2 PARTITION OF user_logs FOR VALUES WITH (MODULUS 4, REMAINDER 2); CREATE TABLE user_logs_p3 PARTITION OF user_logs FOR VALUES WITH (MODULUS 4, REMAINDER 3); -- 分区维护：添加/删除分区 -- [PostgreSQL] CREATE TABLE sales_2026 PARTITION OF sales FOR VALUES FROM (2026-01-01) TO (2027-01-01); DROP TABLE sales_2024; -- 删除旧分区（秒级） -- [MySQL] ALTER TABLE sales ADD PARTITION ( PARTITION p2027 VALUES LESS THAN (2028) ); ALTER TABLE sales DROP PARTITION p2024;

解析：

分区类型：RANGE（时间）、LIST（枚举）、HASH（散列）、KEY（MySQL）。优势：提升查询性能（分区裁剪）、简化数据管理（按分区删除/备份）。注意：MySQL主键必须包含分区字段。PostgreSQL支持默认分区（DEFAULT）。我们查询时带上分区键，才能触发“分区裁剪”。

进阶用法：

-- [PostgreSQL]分区表+索引（每个子表独立索引） CREATE INDEX idx_sales_date ON sales USING BRIN(sale_date); -- 适合时序数据 -- [MySQL]分区+二级索引 CREATE INDEX idx_region ON sales(region); -- 全局索引（MySQL 8.0+支持本地索引）

十、物化视图

46、物化视图（Materialized View），即预计算加速

-- [PostgreSQL]我们创建物化视图（需手动刷新） CREATE MATERIALIZED VIEW mv_monthly_sales AS SELECT DATE_TRUNC(month, sale_date) as month, region, SUM(amount) as total_sales, COUNT(*) as order_count FROM sales GROUP BY 1, 2 ORDER BY 1 DESC; -- 刷新数据（全量） REFRESH MATERIALIZED VIEW mv_monthly_sales; -- [PostgreSQL 9.4+]并发刷新（不影响查询） REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales; -- 需唯一索引 -- [Oracle]自动刷新 CREATE MATERIALIZED VIEW mv_sales BUILD IMMEDIATE REFRESH FAST ON COMMIT -- 我们提交时自动刷新（需物化视图日志） AS SELECT ...; -- [SQL Server]用索引视图（Indexed View）模拟 CREATE VIEW vw_monthly_sales WITH SCHEMABINDING AS SELECT YEAR(sale_date) as yr, MONTH(sale_date) as mth, region, SUM(amount) as total, COUNT_BIG(*) as cnt -- ⚠️ COUNT_BIG是一个必须满足的要求 FROM dbo.sales GROUP BY YEAR(sale_date), MONTH(sale_date), region; -- 创建唯一聚集索引 → 成为“索引视图” CREATE UNIQUE CLUSTERED INDEX idx_mv_sales ON vw_monthly_sales(yr, mth, region); -- [MySQL]无原生物化视图 → 我们用定时任务+临时表替代

解析：

物化视图=预计算+存储结果，适合复杂聚合、报表场景。PostgreSQL：手动刷新，支持并发。Oracle：支持自动刷新（FAST/COMPLETE）。SQL Server：索引视图=物化视图，但限制多（SCHEMABINDING、COUNT_BIG等）。MySQL：无原生支持，需我们用程序或事件调度模拟。

性能优化：

-- [PostgreSQL]为物化视图建索引 CREATE INDEX idx_mv_sales_region ON mv_monthly_sales(region); -- 我们查询时直接查物化视图 SELECT * FROM mv_monthly_sales WHERE region = North AND month >= 2025-01-01;

十一、临时表/CTE/变量

47、临时表 vs CTE vs 表变量

-- 1、CTE（逻辑抽象，不存数据） WITH regional_sales AS ( SELECT region, SUM(amount) as total FROM sales GROUP BY region ) SELECT * FROM regional_sales WHERE total > 10000; -- 2、临时表（会话级/事务级） -- [PostgreSQL/MySQL/SQL Server] CREATE TEMP TABLE tmp_top_customers AS SELECT customer_id, SUM(amount) as total FROM orders GROUP BY customer_id HAVING SUM(amount) > 5000; -- [SQL Server]表变量（内存优先，小数据） DECLARE @TopCustomers TABLE ( customer_id INT, total DECIMAL(10,2) ); INSERT INTO @TopCustomers SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id HAVING SUM(amount) > 5000; -- 3、全局临时表（跨会话，少用） -- [SQL Server] 以##开头 CREATE TABLE ##GlobalTemp (id INT); -- [PostgreSQL]无全局临时表 → 用普通表+应用层管理

我们对比一下：

进阶用法：

-- [PostgreSQL 12+]物化CTE（避免重复计算） WITH RECURSIVE cte AS MATERIALIZED ( SELECT ... UNION ALL SELECT ... ) SELECT * FROM cte JOIN ...; -- [SQL Server]为临时表加索引 CREATE INDEX idx_tmp_cust ON #TopCustomers(customer_id);

48、变量与参数绑定（防止SQL注入）

-- [PostgreSQL]在DO块中使用变量 DO $$ DECLARE v_threshold INT := 1000; v_count INT; BEGIN SELECT COUNT(*) INTO v_count FROM orders WHERE amount > v_threshold; RAISE NOTICE Orders > %: %, v_threshold, v_count; END $$; -- [MySQL]用户变量（@开头） SET @min_amount = 1000; SELECT COUNT(*) INTO @order_count FROM orders WHERE amount > @min_amount; SELECT @order_count; -- [SQL Server]局部变量 DECLARE @MinAmount DECIMAL(10,2) = 1000; DECLARE @OrderCount INT; SELECT @OrderCount = COUNT(*) FROM orders WHERE amount > @MinAmount; PRINT Count: + CAST(@OrderCount AS VARCHAR); -- ⚠️ 动态SQL必须参数绑定 -- [PostgreSQL] EXECUTE SELECT COUNT(*) FROM orders WHERE amount > $1 USING v_threshold; -- [MySQL] SET @sql = SELECT COUNT(*) FROM orders WHERE amount > ?; PREPARE stmt FROM @sql; EXECUTE stmt USING @min_amount; DEALLOCATE PREPARE stmt; -- [SQL Server] DECLARE @SQL NVARCHAR(MAX) = NSELECT COUNT(*) FROM orders WHERE amount > @amt; EXEC sp_executesql @SQL, N@amt DECIMAL(10,2), @amt = @MinAmount;

⚠️ 安全第一：

我们永远不要拼接用户输入到SQL字符串！我们要使用USING（PostgreSQL）、?（MySQL）、sp_executesql（SQL Server）进行参数绑定。

十二、审计/脱敏/安全

49、数据脱敏与审计日志

-- 1、查询时脱敏（动态脱敏） SELECT name, CONCAT(LEFT(phone, 3), ****, RIGHT(phone, 4)) as phone_masked, -- 138****1234 CONCAT(LEFT(email, 2), ***, SUBSTRING(email, LOCATE(@, email))) as email_masked -- ab***@gmail.com FROM users; -- [PostgreSQL]创建视图自动脱敏 CREATE VIEW users_masked AS SELECT id, name, REGEXP_REPLACE(phone, (\\d{3})\\d{4}(\\d{4}), \\1****\\2) as phone, REGEXP_REPLACE(email, (.{2}).+(@.+), \\1***\\2) as email FROM users; -- 2、审计日志（谁在什么时候改了什么？） -- [PostgreSQL]使用触发器+审计表 CREATE TABLE audit_log ( id SERIAL PRIMARY KEY, table_name TEXT, operation TEXT, -- INSERT/UPDATE/DELETE user_name TEXT DEFAULT CURRENT_USER, op_time TIMESTAMP DEFAULT NOW(), old_data JSONB, new_data JSONB ); CREATE OR REPLACE FUNCTION log_user_changes() RETURNS TRIGGER AS $$ BEGIN IF TG_OP = UPDATE THEN INSERT INTO audit_log(table_name, operation, old_data, new_data) VALUES (users, TG_OP, TO_JSONB(OLD), TO_JSONB(NEW)); RETURN NEW; ELSIF TG_OP = DELETE THEN INSERT INTO audit_log(table_name, operation, old_data) VALUES (users, TG_OP, TO_JSONB(OLD)); RETURN OLD; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; CREATE TRIGGER tr_audit_users AFTER UPDATE OR DELETE ON users FOR EACH ROW EXECUTE FUNCTION log_user_changes(); -- [MySQL]用通用审计插件或触发器类似实现

解析：

动态脱敏：适合前端展示、测试环境。审计日志：满足合规要求（GDPR、等保），记录数据变更历史。PostgreSQL触发器：强大灵活，支持JSONB记录完整前后镜像。

50、权限控制与角色管理

-- 1、创建角色 CREATE ROLE analyst; -- [PostgreSQL/SQL Server/Oracle] CREATE ROLE developer; -- 2、授权（最小权限原则） -- [PostgreSQL] GRANT SELECT ON sales, products TO analyst; GRANT SELECT, INSERT, UPDATE ON orders TO developer; GRANT USAGE ON SCHEMA public TO analyst; -- [MySQL] GRANT SELECT ON mydb.sales TO analyst@%; GRANT SELECT, INSERT, UPDATE ON mydb.orders TO developer@localhost; -- 3、列级权限（PostgreSQL/SQL Server） GRANT SELECT (name, email) ON users TO analyst; -- 只允许查部分列 -- 4、行级安全（RLS）— [PostgreSQL 9.5+] -- 启用行级安全 ALTER TABLE sales ENABLE ROW LEVEL SECURITY; -- 创建策略：用户只能看自己区域的数据 CREATE POLICY sales_region_policy ON sales FOR SELECT USING (region = current_setting(app.current_region)); -- 设置会话变量 SET app.current_region = North; SELECT * FROM sales; -- 只返回region=North的数据 -- [SQL Server]用安全谓词（Security Predicate）+策略实现类似功能 -- 5、查看权限 -- [PostgreSQL] \dp sales -- psql命令 -- 或 SELECT * FROM information_schema.table_privileges WHERE table_name = sales; -- [MySQL] SHOW GRANTS FOR analyst@%;

安全规范：

最小权限：我们别乱给权限，只给必要权限。角色管理：我们通过角色分配权限，而非直接给用户。行级安全（RLS）：实现数据隔离（如：多租户、部门数据隔离）。定期审计：我们定期检查权限分配是否合理。