技术栈：吃透286个SQL关键字及相关扩展语法元素（二）

欢迎大家来到IT世界,在知识的湖畔探索吧!

欢迎大家来到IT世界,在知识的湖畔探索吧!

接上回：技术栈：吃透286个SQL关键字及相关扩展语法元素（一）

五、事务控制与锁

• BEGIN TRANSACTION (START TRANSACTION): 启动事务。如：BEGIN TRANSACTION。
• COMMIT: 提交事务（永久保存修改）。如：COMMIT。
• ROLLBACK: 回滚事务（撤销未提交修改）。如：ROLLBACK。
• SAVEPOINT: 设置事务保存点。如：SAVEPOINT savepoint_name。
• ROLLBACK TO SAVEPOINT: 回滚到保存点。如：ROLLBACK TO SAVEPOINT savepoint_name。
• RELEASE SAVEPOINT: 删除保存点。如：RELEASE SAVEPOINT savepoint_name。
• SET TRANSACTION: 设置事务属性（如隔离级别）。如：SET TRANSACTION ISOLATION LEVEL READ COMMITTED。
• SET AUTOCOMMIT: 设置自动提交模式（开/关）。如：SET AUTOCOMMIT ON。
• WAIT/NOWAIT: 处理锁等待行为。如：SELECT * FROM table_name FOR UPDATE NOWAIT。
• FOR UPDATE: 查询时对行加排他锁（防其他事务修改）。如：SELECT * FROM table_name FOR UPDATE。
• FOR SHARE: 查询时对行加共享锁（允许读禁止写）。如：SELECT * FROM table_name FOR SHARE。
• LOCK TABLE: 显式锁定整个表。如：LOCK TABLE table_name IN EXCLUSIVE MODE。
• SET TRANSACTION ISOLATION LEVEL: 明确设置事务隔离级别。如：SET TRANSACTION ISOLATION LEVEL READ COMMITTED。
• READ COMMITTED: 事务隔离级别之一（读取已提交数据）。如：SET TRANSACTION ISOLATION LEVEL READ COMMITTED。
• REPEATABLE READ: 事务隔离级别之一（可重复读）。如：SET TRANSACTION ISOLATION LEVEL REPEATABLE READ。
• SERIALIZABLE: 事务隔离级别之一（串行化，最高隔离级别）。如：SET TRANSACTION ISOLATION LEVEL SERIALIZABLE。
• FOR UPDATE OF: 指定锁定的列（Oracle 等）。如：SELECT column1 FROM table_name FOR UPDATE OF column1，精细化锁控制。
• SET TRANSACTION READ WRITE/READ ONLY: 设置事务读写模式（只读事务可优化性能）。如：SET TRANSACTION READ ONLY。
• LOCK … IN SHARE MODE: 加共享锁（MySQL，允许读但阻止写）。如：SELECT * FROM table_name LOCK IN SHARE MODE。
• NOWAIT: 锁等待时立即返回错误（Oracle、PostgreSQL）。如：SELECT * FROM table_name FOR UPDATE NOWAIT。
• SKIP LOCKED: 跳过已锁定行（Oracle 12c+、PostgreSQL 9.5+）。如：SELECT * FROM table_name FOR UPDATE SKIP LOCKED。

六、条件与逻辑

• AND: 逻辑与（两条件同时满足）。如：SELECT * FROM table_name WHERE column1 > 0 AND column2 < 10。
• OR: 逻辑或（两条件满足其一）。如：SELECT * FROM table_name WHERE column1 > 0 OR column2 < 10。
• NOT: 逻辑非（取反条件）。如：SELECT * FROM table_name WHERE NOT column1 > 0。
• IN: 判断值是否在指定列表。如：SELECT * FROM table_name WHERE column1 IN (1, 2, 3)。NOT IN：判断值不在指定列表或子查询结果中，是 IN 高频反义用法。如：SELECT * FROM t WHERE id NOT IN (1,2,3)。
• BETWEEN: 判断值是否在范围内（包含端点）。如：SELECT * FROM table_name WHERE column1 BETWEEN 1 AND 10。
• LIKE: 模式匹配（配合%, _通配符）。如：SELECT * FROM table_name WHERE column1 LIKE ‘a%’。实际使用中 %（任意字符）、_（单个字符） 作为LIKE的核心通配符，是高频使用的 “隐性关键字”，常与LIKE配合使用（如：WHERE name LIKE ‘张%’匹配 “张” 开头的名字）。
• ESCAPE: 转义LIKE中的通配符。如：SELECT * FROM table_name WHERE column1 LIKE ‘a\%’ ESCAPE ‘\’。
• IS NULL: 判断值为 NULL。如：SELECT * FROM table_name WHERE column1 IS NULL。
• IS NOT NULL: 判断值不为 NULL。如：SELECT * FROM table_name WHERE column1 IS NOT NULL。IS NOT NULL 与 != NULL 的区别：虽IS NOT NULL已列出，但需强调：NULL不能用=或!=判断（如：col != NULL永远返回 NULL，需用col IS NOT NULL），这是=易错点，属于IS NULL的配套注意事项。
• CASE: 条件表达式（类似if-else）。如：SELECT column1, CASE WHEN column1 > 0 THEN ‘positive’ ELSE ‘negative’ END FROM table_name。CASE 表达式完整形式：CASE 除了CASE WHEN … THEN … ELSE，还有 “简单 case” 形式（如：CASE col WHEN 1 THEN ‘a’ WHEN 2 THEN ‘b’ ELSE ‘c’ END），虽基于 CASE，但两种形式均为高频用法，需明确区别。CASE 的所有 THEN 分支返回值必须兼容同一数据类型（如：不能同时返回 INT 和 VARCHAR），否则会报错（如：SQL Server 会强制转换，可能导致非预期结果），如：CASE WHEN col > 0 THEN 1 ELSE ‘no’ END会报错（INT 与 VARCHAR 不兼容），需统一为：CASE WHEN col > 0 THEN ‘1’ ELSE ‘no’ END。
• COALESCE(): 返回第一个非NULL值。如：SELECT COALESCE(column1, 0) FROM table_name。
• NULLIF(): 两值相等返回 NULL，否则返回第一个值。如：SELECT NULLIF(column1, 0) FROM table_name。典型用途：用于避免除法除零错误（如：SELECT 1 / NULLIF(column1, 0)，当 column1=0 时返回 NULL，而非报错），在 MySQL、PostgreSQL 等数据库中，1/0会直接报错，而1/NULLIF(0,0)返回 NULL，从而避免报错。
• XOR: 逻辑异或（MySQL，两条件一真一假则为真）。如：SELECT * FROM table_name WHERE column1 > 0 XOR column2 < 10。
• NULLS FIRST: 在 ORDER BY 中指定 NULL 值排在前面。如：SELECT * FROM table_name ORDER BY column1 NULLS FIRST。
• NULLS LAST: 在 ORDER BY 中指定 NULL 值排在后面。如：SELECT * FROM table_name ORDER BY column1 NULLS LAST。
• REGEXP: 正则表达式匹配（MySQL扩展，如：col REGEXP ‘^[0-9]+#39;）。如：SELECT * FROM table_name WHERE column1 REGEXP ‘^[0-9]+#39;。
• ALL: 与比较运算符结合，判断值满足子查询所有结果（如：col > ALL (SELECT col FROM t2)）。如：SELECT * FROM table1 WHERE column1 > ALL (SELECT column1 FROM table2)。
• SIMILAR TO: 正则风格模式匹配（PostgreSQL 扩展，支持 % 、 _ 及正则元字符）。如：SELECT * FROM table_name WHERE column1 SIMILAR TO ‘a%’。
• COLLATE: 在表达式中临时指定排序规则。如：SELECT column1 COLLATE “en_US” FROM table_name。

七、函数与表达式

• CAST(): 数据类型转换（CAST(expr AS type)）。如：SELECT CAST(‘123’ AS INT) FROM table_name。
• CONVERT(): 数据类型转换（SQL Server）。如：SELECT CONVERT(INT, ‘123’) FROM table_name。
• COLLATE: 指定字符排序规则。如：SELECT column1 COLLATE “en_US” FROM table_name。
• 聚合函数: SUM()（求和）、AVG()（平均值）、COUNT()（计数）、MIN()（最小值）、MAX()（最大值）等。如：SELECT SUM(column1) FROM table_name。
• 字符串函数: CONCAT()（拼接）、SUBSTRING()（截取子串）、LENGTH()（长度）等。如：SELECT CONCAT(column1, column2) FROM table_name。
• 数值函数: ROUND()（四舍五入）、CEIL()（向上取整）、FLOOR()（向下取整）、ABS()（绝对值）等。如：SELECT ROUND(column1, 2) FROM table_name。
• 日期函数: CURRENT_DATE（当前日期）、CURRENT_TIMESTAMP（当前时间戳）、DATEADD()（日期加减）等。如：SELECT CURRENT_DATE FROM table_name。
• 窗口函数: ROW_NUMBER()（行号）、RANK()（排名）、LEAD()（后n行值）、LAG()（前n行值）等。如：SELECT column1, ROW_NUMBER() OVER() FROM table_name。
• GREATEST: 返回参数列表的最大值（如：GREATEST(1,3,5)=5）。如：SELECT GREATEST(column1, column2) FROM table_name。
• LEAST: 返回参数列表的最小值。如：SELECT LEAST(column1, column2) FROM table_name。
• NVL(): 替换NULL值（Oracle， NVL(col, 0) 类似 COALESCE）。如：SELECT NVL(column1, 0) FROM table_name。
• NVL2(): 扩展NULL替换（Oracle， NVL2(col, val1, val2)，非空返回 val1 否则 val2）。如：SELECT NVL2(column1, ‘a’, ‘b’) FROM table_name。
• DECODE(): 多条件匹配（Oracle，如：DECODE(col, 1, ‘a’, 2, ‘b’, ‘c’)）。如：SELECT DECODE(column1, 1, ‘a’, 2, ‘b’, ‘c’) FROM table_name。
• TO_CHAR(): 转换为字符串（Oracle、PostgreSQL，如：TO_CHAR(date, ‘YYYY-MM-DD’)）。如：SELECT TO_CHAR(date_column, ‘YYYY-MM-DD’) FROM table_name。
• TO_DATE(): 字符串转日期（Oracle、PostgreSQL，如：TO_DATE(‘2023-01-01’, ‘YYYY-MM-DD’)）。如：SELECT TO_DATE(‘2023-01-01’, ‘YYYY-MM-DD’) FROM table_name。

八、特殊用途与扩展

• VALUES: 表值构造器。如：SELECT * FROM (VALUES (1), (2)) AS tmp。VALUES 作为表值构造器时的用法，区别于 INSERT 中的 VALUES。
• DUAL: 虚拟表（Oracle、MySQL，用于无表查询）。如：SELECT 1 FROM DUAL。
• USE/SET DATABASE: 切换数据库。如：USE db_name。
• DECLARE: 声明变量。如：DECLARE @var INT。
• SET @variable: 设置变量值。如：SET @var = 1。
• AUTO_INCREMENT: 自增列属性（MySQL）。如：CREATE TABLE table_name (id INT AUTO_INCREMENT PRIMARY KEY)。
• IDENTITY: 自增列属性（SQL Server）。如：CREATE TABLE table_name (id INT IDENTITY(1,1) PRIMARY KEY)。
• SERIAL: 自增列属性（PostgreSQL）。如：CREATE TABLE table_name (id SERIAL PRIMARY KEY)。
• GENERATED ALWAYS AS: 定义计算列（值由表达式生成）。如：CREATE TABLE table_name (column1 INT, column2 INT GENERATED ALWAYS AS (column1 * 2) STORED)。
• CREATE PROCEDURE: 创建存储过程。如：CREATE PROCEDURE proc_name AS SELECT * FROM table_name。
• CREATE FUNCTION: 创建函数。如：CREATE FUNCTION func_name() RETURNS INT AS $ SELECT 1 $ LANGUAGE SQL。
• CREATE TRIGGER: 创建触发器。如：CREATE TRIGGER trig_name BEFORE INSERT ON table_name FOR EACH ROW EXECUTE PROCEDURE func_name()。
• EXEC/EXECUTE: 执行存储过程（SQL Server）。如：EXEC proc_name。
• CALL: 调用存储过程（标准 SQL）。如：CALL proc_name。
• EXPLAIN: 生成查询执行计划（分析查询如何执行，如索引使用、表扫描方式），用于优化查询性能。所有数据库通用。如：EXPLAIN SELECT * FROM table_name WHERE column1 = ‘value’。
• EXPLAIN ANALYZE: 执行查询并返回实际执行计划+性能统计（PostgreSQL 扩展）。如：EXPLAIN ANALYZE SELECT * FROM table_name。
• SHOW: 查看元信息（SHOW DATABASES, SHOW TABLES等）。如：SHOW TABLES。
• DESCRIBE/DESC: 查看表/索引结构。如：DESCRIBE INDEX idx_name ON table。
• PREPARE: 预编译 SQL 语句，提升重复执行效率。如：PREPARE stmt FROM ‘SELECT * FROM table_name WHERE id = ?’。
• EXECUTE: 执行预编译的 SQL 语句（配合 PREPARE）。如：EXECUTE stmt USING @id。
• DEALLOCATE: 释放预编译的 SQL 语句。如：DEALLOCATE stmt。
• SHOW COLUMNS: 查看表的列信息（MySQL 等，类似 DESCRIBE）。如：SHOW COLUMNS FROM table_name。
• EXPLAIN PLAN: 生成查询执行计划（Oracle等，仅分析不执行查询）。如：EXPLAIN PLAN FOR SELECT * FROM table_name。
• PRAGMA: 编译指示（Oracle 等）。如：PRAGMA AUTONOMOUS_TRANSACTION 定义自治事务。
• EXTERNAL TABLE: 外部表，映射外部文件的数据结构（Oracle、Hive 等，如：CREATE EXTERNAL TABLE t1 … LOCATION ‘path’）。如：CREATE EXTERNAL TABLE table_name (column1 datatype) LOCATION ‘path’。
• TEMPORARY TABLESPACE: 临时表空间（Oracle，指定临时数据存储位置）。如：CREATE TEMPORARY TABLESPACE temp_ts。
• DATABASE LINK: 数据库链接，访问远程数据库（Oracle）。如：CREATE DATABASE LINK link_name CONNECT TO user IDENTIFIED BY pwd USING ‘db’。CONNECT BY：层级查询关键字（如：SELECT * FROM t START WITH id = 1 CONNECT BY PRIOR id = parent_id），用于查询树形结构数据（如：部门层级），是 Oracle 处理层级数据的核心关键字。
• SYNONYM: 创建同义词（别名），简化对象引用（Oracle、SQL Server）。如：CREATE SYNONYM syn_name FOR schema1.table1。
• FLASHBACK: 闪回查询，访问过去某时间点的数据（Oracle）。如：SELECT * FROM table_name AS OF TIMESTAMP TO_TIMESTAMP(‘2023-01-01’, ‘YYYY-MM-DD’)。
• SHOW WARNINGS: 显示最近 SQL 语句的警告信息（MySQL 扩展）。如：SHOW WARNINGS。
• SHOW CREATE TABLE：查看表的完整建表语句（如：SHOW CREATE TABLE t），用于获取表结构、索引、约束等详细信息，是调试和迁移的常用工具。
• SHOW INDEX FROM：查看表的索引信息（如：SHOW INDEX FROM t），用于分析索引是否生效，属于性能优化常用操作。
• DESCRIBE PROCEDURE: 在 SQL 中，DESCRIBE PROCEDURE用于查看存储过程的定义信息，包括参数、数据类型、返回值等。不同数据库语法略有差异，如 MySQL 用DESCRIBE或SHOW CREATE PROCEDURE，SQL Server 用sp_helptext，可帮助我们了解存储过程结构，便于维护和调试。

有了关键字这个工具，接下来我们来简单理一下解决问题的思路。如：想 “找出每个部门工资最高的人”，我们先想到的是 “按部门分组”（GROUP BY），再对每组 “取最高工资”（MAX()），最后用SELECT把结果提出来。这时候，GROUP BY、MAX()和SELECT自然就成了我们要用的关键字。刚开始用的时候，我们可以先从简单查询练起，熟悉SELECT+FROM+WHERE的组合；遇到复杂需求，再逐步加入JOIN（多表关联）、ORDER BY（排序）等等工具。这就像我们学开车，先会踩油门刹车，再练倒车入库，慢慢就熟练了。