这份SQL速查手册，从基础到进阶，我们用50条SQL常见语句覆盖了各类数据库操作。不仅包含SQL标准语法，我们还特别标注了各主流数据库的差异点，仅供参考。
（图片来源网络，侵删）
解析：
主键：唯一标识行，自动创建索引。外键：保证引用完整性，支持级联操作。默认值：插入时，若未指定，则自动填充。检查约束：限制字段取值范围。进阶用法：
-- [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实现UPSERTMERGE students AS targetUSING (SELECT 1 as id, '张三' as name, 'zhang@new.com' as email) AS sourceON target.id = source.idWHEN MATCHED THEN UPDATE SET name = source.name, email = source.emailWHEN 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]获取自增IDSELECT LAST_INSERT_ID();-- [SQL Server]OUTPUT子句INSERT INTO students (...) OUTPUT INSERTED.id VALUES (...);3、查询基础（SELECT+WHERE+ORDER BY+LIMIT）-- 基础查询+条件+排序+分页SELECT id, name, email, created_atFROM studentsWHERE class_id = 1 AND gender = 'M' AND created_at >= '2025-01-01'ORDER BY created_at DESCLIMIT 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 sJOIN classes c ON s.class_id = c.idSET s.email = CONCAT(s.name, '@', c.school, '.edu')WHERE c.school = '清华';-- [SQL Server]关联更新UPDATE sSET email = s.name + '@' + c.school + '.edu'FROM students sJOIN classes c ON s.class_id = c.idWHERE c.school = '清华';-- [Oracle]关联更新（我们使用子查询）UPDATE students sSET 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 sJOIN classes c ON s.class_id = c.idWHERE c.name = '已解散班级';-- [SQL Server]DELETE sFROM students sJOIN classes c ON s.class_id = c.idWHERE 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 s1JOIN 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_nameFROM students sINNER JOIN classes c ON s.class_id = c.id;-- 左连接（返回左表所有+匹配右表）SELECT s.name, c.name as class_nameFROM students sLEFT JOIN classes c ON s.class_id = c.id;-- 右连接（返回右表所有+匹配左表）SELECT s.name, c.name as class_nameFROM students sRIGHT JOIN classes c ON s.class_id = c.id;-- 全外连接（左右表所有行）[PostgreSQL/SQL Server/Oracle]SELECT s.name, c.name as class_nameFROM students sFULL OUTER JOIN classes c ON s.class_id = c.id;-- [MySQL]不支持FULL JOIN，需我们用UNION模拟SELECT ... FROM students s LEFT JOIN classes c ...UNIONSELECT ... 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 student2FROM students s1JOIN students s2 ON s1.class_id = s2.class_id AND s1.id < s2.id;-- 我们使用USING简化（字段名相同）SELECT name, class_nameFROM students JOIN classes USING (class_id); -- 我们需注意：字段名必须相同7、子查询（标量、行、表、关联子查询）-- 标量子查询（返回单值）SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_countFROM 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.totalFROM users uJOIN ( SELECT user_id, SUM(amount) as total FROM orders GROUP BY user_id) o ON u.id = o.user_id;-- 关联子查询（依赖外层）SELECT name, salaryFROM employees e1WHERE 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 sWHERE EXISTS ( SELECT 1 FROM enrollments e WHERE e.student_id = s.id AND e.course_id = 101);-- 我们用NOT EXISTS查没有报名学生SELECT * FROM students sWHERE 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 = 1UNIONSELECT name FROM teachers WHERE dept = '数学';-- 合并不去重（性能更好）SELECT name FROM students WHERE class_id = 1UNION ALLSELECT name FROM teachers WHERE dept = '数学';-- 交集（PostgreSQL/SQL Server/Oracle）SELECT product_id FROM orders_2024INTERSECTSELECT product_id FROM orders_2025;-- 差集（PostgreSQL/SQL Server/Oracle）SELECT product_id FROM orders_2024EXCEPTSELECT product_id FROM orders_2025;-- [MySQL]模拟INTERSECTSELECT DISTINCT o1.product_idFROM orders_2024 o1JOIN orders_2025 o2 ON o1.product_id = o2.product_id;-- [MySQL]模拟EXCEPTSELECT DISTINCT product_id FROM orders_2024WHERE 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_scoreFROM students sJOIN high_score_students h ON s.id = h.student_id;-- 多层CTEWITH 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_orderFROM students s;-- 优化1：改写为LATERAL JOIN（PostgreSQL/MySQL 8.0+）SELECT s.name, o.order_date as last_orderFROM students sJOIN 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_orderFROM students sJOIN ranked_orders r ON s.id = r.user_id AND r.rn = 1;解析：
相关子查询性能差（N+1问题）。优先改写为JOIN+窗口函数或LATERAL。进阶用法：
-- 我们要查询高于部门平均工资的员工（窗口函数版）SELECT name, salary, dept_avgFROM ( SELECT name, salary, dept_id, AVG(salary) OVER (PARTITION BY dept_id) as dept_avg FROM employees) tWHERE salary > dept_avg;三、聚合与分组11、GROUP BY+HAVING+多级聚合-- 我们要查询每个班级平均分>85的学生人数和最高分SELECT c.name as class_name, COUNT(*) as student_count, MAX(e.score) as max_scoreFROM classes cJOIN students s ON c.id = s.class_idJOIN exam_results e ON s.id = e.student_idGROUP BY c.nameHAVING 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_avgFROM 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_salaryFROM employeesGROUP 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, genderUNION ALLSELECT dept, NULL, COUNT(*), AVG(salary) FROM employees GROUP BY deptUNION ALLSELECT 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_amountFROM 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_scoreFROM 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_scoreFROM 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_namesFROM studentsGROUP BY class_id;-- [MySQL]SELECT class_id, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') as student_namesFROM studentsGROUP BY class_id;-- [SQL Server]SELECT class_id, STRING_AGG(name, ', ') WITHIN GROUP (ORDER BY name) as student_namesFROM studentsGROUP BY class_id;-- [Oracle]SELECT class_id, LISTAGG(name, ', ') WITHIN GROUP (ORDER BY name) as student_namesFROM studentsGROUP 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_diffFROM 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 quartileFROM 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_totalFROM sales;-- 移动平均（最近3天）SELECT order_date, amount, AVG(amount) OVER ( ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) as moving_avg_3dFROM 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_earnerFROM 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;五、递归CTE22、递归查询组织架构（树形结构）-- 我们要查询某员工的所有下属（向下递归）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>EmployeeWITH 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]查询JSONBSELECT 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 studentsFROM exam_resultsGROUP BY class_id;-- [PostgreSQL]SELECT class_id, JSONB_AGG(JSONB_BUILD_OBJECT('name', name, 'score', score)) as studentsFROM exam_resultsGROUP 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_namesFROM students GROUP BY class_id;解析：
ANY/ALL用于数组比较。UNNEST展开数组为多行。ARRAY_AGG行转数组。28、XML处理（SQL Server/Oracle）-- [SQL Server]查询XMLSELECT T.c.value('(name/text())[1]', 'VARCHAR(100)') as name, T.c.value('(age/text())[1]', 'INT') as ageFROM users_xmlCROSS APPLY xml_data.nodes('/user') T(c);-- [Oracle]SELECT EXTRACTVALUE(xml_data, '/user/name') as name, EXTRACTVALUE(xml_data, '/user/age') as ageFROM users_xml;-- 更新XMLUPDATE 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]SAVEPOINTSAVEPOINT 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 + 1WHERE 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 scansFROM 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]使用EXECUTEDO $$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]PREPARESET @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.sqlmysql -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 ASSELECT DATE_TRUNC('month', sale_date) as month, region, SUM(amount) as total_sales, COUNT(*) as order_countFROM salesGROUP BY 1, 2ORDER BY 1 DESC;-- 刷新数据（全量）REFRESH MATERIALIZED VIEW mv_monthly_sales;-- [PostgreSQL 9.4+]并发刷新（不影响查询）REFRESH MATERIALIZED VIEW CONCURRENTLY mv_monthly_sales; -- 需唯一索引-- [Oracle]自动刷新CREATE MATERIALIZED VIEW mv_salesBUILD IMMEDIATEREFRESH FAST ON COMMIT -- 我们提交时自动刷新（需物化视图日志）AS SELECT ...;-- [SQL Server]用索引视图（Indexed View）模拟CREATE VIEW vw_monthly_sales WITH SCHEMABINDING ASSELECT YEAR(sale_date) as yr, MONTH(sale_date) as mth, region, SUM(amount) as total, COUNT_BIG(*) as cnt -- ⚠️ COUNT_BIG是一个必须满足的要求FROM dbo.salesGROUP 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 ASSELECT customer_id, SUM(amount) as totalFROM 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+可物化）
✗
重复引用子查询
进阶用法：
-- [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) = 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、数据脱敏与审计日志-- 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.comFROM users;-- [PostgreSQL]创建视图自动脱敏CREATE VIEW users_masked ASSELECT id, name, REGEXP_REPLACE(phone, '(\\d{3})\\d{4}(\\d{4})', '\\1****\\2') as phone, REGEXP_REPLACE(email, '(.{2}).+(@.+)', '\\1***\\2') as emailFROM 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_usersAFTER UPDATE OR DELETE ON usersFOR 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 salesFOR SELECTUSING (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）：实现数据隔离（如：多租户、部门数据隔离）。定期审计：我们定期检查权限分配是否合理。转载请注明来自德立，本文标题：《数据库sql语句大全（50条SQL常见语句）》
京公网安备11000000000001号
京ICP备11000001号
还没有评论，来说两句吧...