SQL语句全面指南：规范、技巧与避坑实战

引言

SQL作为关系型数据库的核心语言，其语句的规范使用直接关系到数据安全性与系统性能。本文系统化整理SQL四大类语句（DDL/DML/DCL/TCL），结合官方文档标准与实战经验，涵盖语法规范、高频错误场景及优化方案，助你规避95%的SQL陷阱。

一、数据定义语言（DDL）

管理数据库对象结构

1. CREATE

规范

sql

复制

下载

-- 建表示例（MySQL）
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department ENUM('IT','HR','Sales') DEFAULT 'IT',
    salary DECIMAL(10,2) CHECK (salary > 0),
    INDEX idx_department (department)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

易错点：

未指定字符集导致乱码（推荐utf8mb4）
忽略存储引擎选择（事务场景用InnoDB）
缺失主键引发全表扫描

2. ALTER

应用场景：

新增索引优化查询
修改字段类型适配业务变更

sql

复制

下载

-- 危险操作！大表修改锁表
ALTER TABLE orders MODIFY COLUMN amount DOUBLE(12,2);

避坑方案：

使用pt-online-schema-change工具在线修改
先创建新列再迁移数据

3. DROP vs TRUNCATE

语句	是否可回滚	重置自增ID	性能
`DROP`	❌	❌	最快
`TRUNCATE`	❌	✅	快
`DELETE`	✅	❌	慢

致命错误：

sql

复制

下载

DROP TABLE production_data; -- 无备份直接执行

二、数据操作语言（DML）

处理数据记录

1. SELECT

优化规范：

sql

复制

下载

-- 避免SELECT * 
SELECT id, name FROM users WHERE status=1;

-- JOIN显式指定关联条件
SELECT o.id, u.name 
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

性能陷阱：

无索引的LIKE前缀匹配：WHERE name LIKE '%son'
OR条件导致索引失效（改用UNION）
在WHERE中使用函数：WHERE YEAR(create_time)=2023

2. INSERT

批量插入优化：

sql

复制

下载

-- 单条插入（不推荐）
INSERT INTO log (event) VALUES ('login');

-- 批量插入（效率提升10倍+）
INSERT INTO log (event) 
VALUES ('login'), ('logout'), ('purchase');

常见错误：

未指定列名导致列顺序依赖
忽略NOT NULL约束引发报错

3. UPDATE

原子性保障：

sql

复制

下载

START TRANSACTION;
UPDATE accounts SET balance=balance-100 WHERE user_id=5;
UPDATE accounts SET balance=balance+100 WHERE user_id=9;
COMMIT;

灾难性操作：

sql

复制

下载

UPDATE products SET price=0; -- 缺失WHERE条件！

4. DELETE

级联删除方案：

sql

复制

下载

-- 创建表时定义外键约束
CREATE TABLE orders (
    id INT,
    user_id INT,
    FOREIGN KEY (user_id) 
        REFERENCES users(id)
        ON DELETE CASCADE
);

三、数据控制语言（DCL）

权限管理

1. GRANT

最小权限原则：

sql

复制

下载

-- 授权特定操作
GRANT SELECT, INSERT ON db1.* TO 'report_user'@'192.168.%';

-- 禁止权限扩散
REVOKE GRANT OPTION FROM 'dev_user'@'%';

2. REVOKE

权限回收陷阱：

sql

复制

下载

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_user'; -- 立即生效
DROP USER 'old_user'; -- 彻底删除

注意：权限变更需FLUSH PRIVILEGES刷新（MySQL 8.0+自动处理）

四、事务控制语言（TCL）

保证数据一致性

事务ACID实现

sql

复制

下载

START TRANSACTION;

SAVEPOINT before_update;
UPDATE inventory SET stock=stock-1 WHERE product_id=101;
-- 业务逻辑校验
IF (SELECT stock FROM inventory WHERE product_id=101) < 0 THEN
    ROLLBACK TO before_update;
ELSE
    COMMIT;
END IF;

隔离级别问题：

幻读：SELECT ... FOR UPDATE
脏读：设置READ COMMITTED级别

五、高级语句实战技巧

1. 窗口函数（分析场景）

sql

复制

下载

-- 部门薪资排名
SELECT 
    name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

2. 公用表表达式（CTE）

sql

复制

下载

WITH RegionalSales AS (
    SELECT region, SUM(amount) total_sales
    FROM orders
    GROUP BY region
)
SELECT region, total_sales 
FROM RegionalSales
WHERE total_sales > 100000;

3. 执行计划分析（EXPLAIN）

sql

复制

下载

EXPLAIN ANALYZE
SELECT * FROM products 
WHERE category_id IN (SELECT id FROM categories WHERE name='Electronics');

关键指标：

type：ALL（全表扫描）→ 需优化
rows：预估扫描行数
Extra：Using filesort（需索引优化）

六、跨数据库差异备忘

语句	MySQL	PostgreSQL	SQL Server
分页查询	`LIMIT 10`	`LIMIT 10`	`OFFSET 10 ROWS`
字符串拼接	`CONCAT()`	`\|\|`	`+`
当前时间	`NOW()`	`NOW()`	`GETDATE()`

未解疑问与实践计划

问题：如何在不锁表的情况下对大表（亿级）添加索引？
探索方向：
- MySQL在线DDL（ALGORITHM=INPLACE）
- 使用Percona Toolkit的pt-online-schema-change
问题：窗口函数在分布式数据库（如TiDB）中的性能优化方案？
实践计划：
- 对比TiDB 7.0与MySQL 8.0的执行计划
- 测试分区表对窗口函数的影响