50条SQL常用语句速查手册，标注MySQL、Oracle等方言差异

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

一、基础操作（CRUD）

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

sql

-- [通用]创建学生表（含主键、非空、默认值、外键）
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）

sql

-- 单行插入
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标准语法，功能最全。

进阶用法：

sql

-- [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）

sql

-- 基础查询+条件+排序+分页
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+标准语法。

进阶用法：

sql

-- 随机取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+条件）

sql

-- 基础更新
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

进阶用法：

sql

-- 限制更新行数（防误操作）
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）

sql

-- 删除指定行
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），不可回滚。

进阶用法：

sql

-- 删除重复数据（保留最小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

sql

-- 内连接（只返回匹配行）
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需模拟）。

进阶用法：

sql

-- 自连接（查询同一班级的学生对）
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、子查询（标量、行、表、关联子查询）

sql

-- 标量子查询（返回单值）
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。

进阶用法：

sql

-- 我们用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

sql

-- 合并结果集（去重）
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不支持。

进阶用法：

sql

-- 多层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）

sql

-- 简单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+）。

进阶用法：

sql

-- 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/窗口函数）

sql

-- 原始：我们查询每个学生最新订单
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。

进阶用法：

sql

-- 我们要查询高于部门平均工资的员工（窗口函数版）
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+多级聚合

sql

-- 我们要查询每个班级平均分>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(...))需用子查询或窗口函数。

进阶用法：

sql

-- 分组后，保留原始明细（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（多维聚合）

sql

-- [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：自由组合。

进阶用法：

sql

-- 判断是否为汇总行（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子句

sql

-- 计算去重后的数量
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（字符串聚合）

sql

-- [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）

sql

-- [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()

sql

-- 按部门分组，按工资排序
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）。

进阶用法：

sql

-- 我们要查询各部门工资前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()（前后行访问）

sql

-- 我们要查询学生成绩及与前一名的分差
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()

sql

-- 我们要将学生按成绩分为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（累计/移动平均）

sql

-- 累计销售额
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()

sql

-- 我们要查询每个部门最高工资者姓名
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、窗口函数性能优化

sql

-- 优化：分区字段加索引
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、递归查询组织架构（树形结构）

sql

-- 我们要查询某员工的所有下属（向下递归）
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、查询路径（祖先路径/分隔符路径）

sql

-- 生成路径：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、递归限制与优化

sql

-- 我们要限制递归深度（防止死循环）
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）

sql

-- [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_SET
PostgreSQL：->, ->>, ||(合并), #>(路径)

26、JSON聚合与生成

sql

-- [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）

sql

-- [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

-- [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）

sql

-- 标准事务
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：完全隔离。

sql

-- [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、乐观锁（版本号/时间戳）

sql

-- 表结构
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、死锁检测与处理

sql

-- [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）

sql

-- [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）

sql

-- 查询只访问索引，不回表
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、索引失效场景

sql

-- 失效：左模糊
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、索引监控与维护

sql

-- [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、查询元数据（表结构/索引/约束）

sql

-- [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（拼接执行）

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、性能监控（慢查询/连接数）

sql

-- [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）

sql

-- [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、分区表（大表优化）

sql

-- [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），即预计算加速

sql

-- [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：无原生支持，需我们用程序或事件调度模拟。

性能优化：

sql

-- [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 表变量

sql

-- 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]无全局临时表 → 用普通表+应用层管理

我们对比一下：

类型	作用域	持久性	索引支持	适用场景
CTE	语句内	无	✗	逻辑分层、递归
临时表	会话/事务	会话结束自动删	✓	中间结果、复杂多步处理
表变量	批处理内	批处理结束删	⚠️有限	小数据、替代临时表
物化CTE	语句内	无（PostgreSQL 12+可物化）	✗	重复引用子查询

进阶用法：

sql

-- [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注入）

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) = N'SELECT 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、数据脱敏与审计日志

sql

-- 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、权限控制与角色管理

sql

-- 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）：实现数据隔离（如：多租户、部门数据隔离）。
定期审计：我们定期检查权限分配是否合理。

一、基础操作（CRUD） ​

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

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

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

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

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

二、复杂JOIN/子查询 ​

6、INNER/LEFT/RIGHT/FULL JOIN ​

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

8、UNION/UNION ALL/INTERSECT/EXCEPT ​

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

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

三、聚合与分组 ​

11、GROUP BY+HAVING+多级聚合 ​

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

13、DISTINCT聚合与FILTER子句 ​

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

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

四、窗口函数 ​

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

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

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

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

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

21、窗口函数性能优化 ​

五、递归CTE ​

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

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

24、递归限制与优化 ​

六、JSON/XML/数组处理 ​

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

26、JSON聚合与生成 ​

27、数组操作（PostgreSQL） ​

28、XML处理（SQL Server/Oracle） ​

29、JSON/XML性能优化 ​

七、事务控制与并发控制 ​

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

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

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

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

34、死锁检测与处理 ​

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

35、索引类型与创建 ​

36、执行计划分析（EXPLAIN） ​

37、覆盖索引（Covering Index） ​

38、索引失效场景 ​

39、索引监控与维护 ​

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

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

41、动态SQL（拼接执行） ​

42、数据库备份与恢复 ​

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

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

45、分区表（大表优化） ​

十、物化视图 ​

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

十一、临时表/CTE/变量 ​

47、临时表 vs CTE vs 表变量 ​

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

十二、审计/脱敏/安全 ​

49、数据脱敏与审计日志 ​

50、权限控制与角色管理 ​

一、基础操作（CRUD）

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

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

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

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

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

二、复杂JOIN/子查询

6、INNER/LEFT/RIGHT/FULL JOIN

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

8、UNION/UNION ALL/INTERSECT/EXCEPT

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

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

三、聚合与分组

11、GROUP BY+HAVING+多级聚合

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

13、DISTINCT聚合与FILTER子句

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

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

四、窗口函数

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

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

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

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

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

21、窗口函数性能优化

五、递归CTE

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

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

24、递归限制与优化

六、JSON/XML/数组处理

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

26、JSON聚合与生成

27、数组操作（PostgreSQL）

28、XML处理（SQL Server/Oracle）

29、JSON/XML性能优化

七、事务控制与并发控制

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

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

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

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

34、死锁检测与处理

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

35、索引类型与创建

36、执行计划分析（EXPLAIN）

37、覆盖索引（Covering Index）

38、索引失效场景

39、索引监控与维护

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

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

41、动态SQL（拼接执行）

42、数据库备份与恢复

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

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

45、分区表（大表优化）

十、物化视图

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

十一、临时表/CTE/变量

47、临时表 vs CTE vs 表变量

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

十二、审计/脱敏/安全

49、数据脱敏与审计日志

50、权限控制与角色管理