DSL、DDL、DCL、DML、TCL详解

一、数据查询语言（DSL：Data Query Language）

DSL（Data Selection Language）是 SQL 中最常用的模块，主要用于从数据库表中查询数据，不修改数据本身，仅对数据进行"读取"操作。其核心命令只有一个 —— SELECT，但通过与其他子句的组合，能实现复杂的查询逻辑。

1、核心语法结构

DSL 的基本语法遵循严格的顺序（子句顺序不可随意调整），完整结构如下：

SELECT [DISTINCT] 列名1, 列名2, 聚合函数(列名) AS 别名
FROM 表名1
[JOIN 表名2 ON 表名1.关联列 = 表名2.关联列] -- 多表关联
[WHERE 行过滤条件] -- 过滤行数据（在分组前执行）
[GROUP BY 分组列1, 分组列2] -- 按列分组
[HAVING 分组过滤条件] -- 过滤分组结果（在分组后执行）
[ORDER BY 排序列1 [ASC/DESC], 排序列2 [ASC/DESC]] -- 排序结果
[LIMIT 起始索引, 条数]; -- 限制返回行数（不同数据库语法有差异）

2、关键子句解析与示例

（1）基础查询：SELECT + FROM

用于查询单个表的指定列或所有列（不推荐使用*，会降低效率且易出错）。

示例1：查询student表中所有学生的id、name、age：

-- 推荐：指定列查询
SELECT id, name, age FROM student;

-- 不推荐：查询所有列（若表结构变更，可能导致结果异常）
SELECT * FROM student;

应用场景：通常在报表生成、数据可视化等只需要部分列数据的场景中使用。

（2）去重查询：DISTINCT

用于去除查询结果中重复的行，仅保留唯一值（作用于所有指定列的组合，而非单个列）。

示例2：查询student表中所有不重复的"班级"：

SELECT DISTINCT class FROM student;

示例3：查询学生表中不同班级和性别的组合：

SELECT DISTINCT class, gender FROM student;

（3）条件过滤：WHERE

用于筛选符合条件的行，支持多种运算符：

• 比较运算符：=（等于）、!=/<>（不等于）、>（大于）、<（小于）、>=（大于等于）、<=（小于等于）、BETWEEN...AND...（范围）、IN（在集合中）、NOT IN（不在集合中）、LIKE（模糊匹配）、IS NULL（空值）、IS NOT NULL（非空值）。
• 逻辑运算符：AND（且）、OR（或）、NOT（非）。

示例4：查询student表中"年龄大于18且班级为'高三(1)班'"的学生：

SELECT id, name, age
FROM student
WHERE age > 18 AND class = '高三(1)班';

示例5：模糊查询姓"张"的学生：

SELECT id, name
FROM student
WHERE name LIKE '张%';

示例6：查询年龄在18到20之间的学生：

SELECT id, name, age
FROM student
WHERE age BETWEEN 18 AND 20;

（4）多表关联：JOIN

当数据分散在多个表中时，需通过JOIN关联查询，常见类型包括：

• INNER JOIN：只返回两表中匹配的行（交集）。
• LEFT JOIN：返回左表所有行，右表匹配的行显示，不匹配的行显示NULL。
• RIGHT JOIN：返回右表所有行，左表匹配的行显示，不匹配的行显示NULL。
• FULL JOIN：返回两表所有行，不匹配的行显示NULL（MySQL不支持，需用UNION模拟）。

示例7：关联student（学生表）和score（成绩表），查询学生姓名及对应的数学成绩：

SELECT s.name AS 学生姓名, sc.math AS 数学成绩
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id; -- s、sc为表别名，简化代码

示例8：三表关联查询学生信息、课程信息和成绩：

SELECT s.name, c.course_name, sc.score
FROM student s
JOIN score sc ON s.id = sc.student_id
JOIN course c ON sc.course_id = c.id;

（5）分组与聚合：GROUP BY + 聚合函数

• 聚合函数：对分组后的数据进行计算，常见函数有COUNT()（计数）、SUM()（求和）、AVG()（平均值）、MAX()（最大值）、MIN()（最小值）。
• GROUP BY：按指定列对数据分组，分组列必须出现在SELECT中（除聚合函数外）。

示例9：按"班级"分组，统计每个班级的学生人数和平均年龄：

SELECT class AS 班级,
       COUNT(id) AS 学生人数,  -- 统计每个班级的学生数量（用主键id避免空值影响）
       AVG(age) AS 平均年龄
FROM student
GROUP BY class;  -- 分组列class必须在SELECT中

示例10：按班级和性别分组统计：

SELECT class, gender, COUNT(*) AS 人数
FROM student
GROUP BY class, gender;

（6）分组过滤：HAVING

WHERE无法过滤分组后的结果（如"学生人数大于50的班级"），需用HAVING，且HAVING必须跟在GROUP BY后。

示例11：筛选出"学生人数大于50"的班级及其平均年龄：

SELECT class AS 班级,
       COUNT(id) AS 学生人数,
       AVG(age) AS 平均年龄
FROM student
GROUP BY class
HAVING COUNT(id) > 50;  -- 过滤分组结果，仅保留人数>50的班级

示例12：筛选平均年龄大于18的班级：

SELECT class, AVG(age) AS avg_age
FROM student
GROUP BY class
HAVING AVG(age) > 18;

（7）排序：ORDER BY

对查询结果按指定列排序，ASC（升序，默认）、DESC（降序），支持多列排序（先按第一列排，第一列相同则按第二列排）。

示例13：查询student表，按"年龄降序"、"姓名升序"排序：

SELECT id, name, age
FROM student
ORDER BY age DESC, name ASC;

示例14：按班级升序排列，同班级按年龄降序排列：

SELECT class, name, age
FROM student
ORDER BY class ASC, age DESC;

（8）限制行数：LIMIT

用于分页查询或限制返回结果数量，不同数据库语法差异：

• MySQL/MariaDB：LIMIT 起始索引, 条数（起始索引从0开始）。
• Oracle：ROWNUM <= 条数（需注意排序后分页需嵌套子查询）。
• SQL Server：TOP 条数 或 OFFSET 起始索引 ROWS FETCH NEXT 条数 ROWS ONLY。

示例15：MySQL中查询student表第2页数据（每页10条）：

-- 第1页：LIMIT 0,10；第2页：LIMIT 10,10；第n页：LIMIT (n-1)*10,10
SELECT id, name, age
FROM student
ORDER BY id ASC
LIMIT 10, 10;

示例16：SQL Server的分页查询：

-- SQL Server 2012及以上版本
SELECT id, name, age
FROM student
ORDER BY id
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

3 、注意事项

1. 子句顺序不可乱：DSL的执行顺序与书写顺序不同（执行顺序：FROM→JOIN→WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT），但书写时必须按"SELECT→FROM→JOIN→WHERE→GROUP BY→HAVING→ORDER BY→LIMIT"的顺序。

2. 避免使用SELECT ：会查询不必要的列，增加IO开销；若表结构变更（如新增/删除列），可能导致程序报错。

3. DISTINCT作用于所有列：若SELECT后有多个列，DISTINCT会对这些列的组合去重，而非仅对第一个列去重。

4. NULL值处理：判断空值必须用IS NULL/IS NOT NULL，不能用=/!=（NULL与任何值比较结果都是NULL）。

5. 聚合函数与NULL：

   • COUNT(*)统计所有行（包括NULL）
   • COUNT(列名)统计非NULL行
   • SUM()/AVG()会忽略NULL值

6. LIMIT的性能问题：当偏移量（起始索引）过大时（如LIMIT 100000, 10），数据库会扫描大量无用数据，建议用"主键过滤"优化（如WHERE id > 100000 LIMIT 10）。

7. JOIN的性能优化：

   • 确保关联字段有索引
   • 避免不必要的表连接
   • 使用合适的JOIN类型（INNER JOIN通常比OUTER JOIN性能更好）

8. GROUP BY的使用：

   • SELECT中的非聚合列必须出现在GROUP BY中
   • 大数据量分组查询可能很耗资源

9. 子查询优化：复杂的子查询可以考虑重写为JOIN操作，通常性能更好。

二、数据操作语言（DML：Data Manipulation Language）

DML（Data Manipulation Language）是SQL中用于修改数据库数据的语言，它包括"插入（INSERT）、更新（UPDATE）、删除（DELETE）"三类核心操作。这些操作都属于"事务性操作"，意味着操作后需要通过COMMIT提交使更改永久生效，或在出错时使用ROLLBACK回滚到操作前的状态。

2.1 核心命令与语法详解

（1）数据插入：INSERT命令

INSERT语句用于向数据库表中添加新记录，支持多种插入方式：

最佳实践：指定列插入

-- 向student表安全插入一条学生数据，明确指定列名
-- 这样即使表结构变更（如新增列），语句也不会出错
INSERT INTO student (id, name, age, class)
VALUES (1, '张三', 18, '高三(1)班');

批量插入（高效方式）

-- 一次性插入多条数据，减少与数据库的交互次数
-- 适用于初始数据导入或批量添加场景
INSERT INTO student (id, name, age, class)
VALUES
(2, '李四', 17, '高三(2)班'),
(3, '王五', 18, '高三(1)班'),
(4, '赵六', 19, '高三(3)班');

从其他表查询插入

-- 将student表中高三学生数据复制到senior_three表
-- 注意：两表结构可以不同，只需保证SELECT的列与INSERT的列匹配
INSERT INTO senior_three (id, name, age, class)
SELECT id, name, age, class
FROM student
WHERE class LIKE '高三%';

（2）数据更新：UPDATE命令

UPDATE用于修改现有记录，必须配合WHERE条件使用：

基础更新示例

-- 更新单个学生的年龄
-- 关键点：WHERE条件确保只更新目标记录
UPDATE student
SET age = 19
WHERE id = 1;

批量更新示例

-- 更新整个班级学生的年龄
-- 使用表达式计算新值：原年龄+1
UPDATE student
SET age = age + 1
WHERE class = '高三(1)班';

多表关联更新（MySQL语法）

-- 更新张三的数学成绩
-- 通过JOIN关联student和score表
-- 注意：不同数据库语法可能不同
UPDATE student s
JOIN score sc ON s.id = sc.student_id
SET sc.math = 95
WHERE s.name = '张三';

（3）数据删除：DELETE命令

DELETE用于移除表中的记录，必须谨慎使用WHERE条件：

安全删除单条记录

-- 删除指定ID的学生
-- 删除前建议先用SELECT验证条件
DELETE FROM student
WHERE id = 4;

条件删除多条记录

-- 删除年龄超限的学生
-- 注意：WHERE条件要确保精确性
DELETE FROM student
WHERE age > 20;

多表关联删除（MySQL语法）

-- 删除张三的成绩记录
-- sc指定只删除score表的记录
-- 不指定会同时删除两表匹配的记录
DELETE sc
FROM student s
JOIN score sc ON s.id = sc.student_id
WHERE s.name = '张三';

2.2 关键注意事项

1. WHERE子句的重要性

• 安全机制：UPDATE和DELETE不加WHERE会操作全表数据
• 验证流程：建议先执行SELECT确认条件准确性，再执行修改

  -- 先查询验证
  SELECT * FROM student WHERE class = '高三(1)班';
  -- 确认无误后再更新
  UPDATE student SET status = '毕业' WHERE class = '高三(1)班';
  

2. 事务控制

• 显式事务：DML操作应在事务中完成

  START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  -- 确认无误后提交
  COMMIT;
  -- 或出错时回滚
  ROLLBACK;
  

3. 主键冲突处理

• MySQL方案：

  INSERT INTO products (id, name, price)
  VALUES (101, '手机', 2999)
  ON DUPLICATE KEY UPDATE name='手机', price=2999;
  

• Oracle方案：

  MERGE INTO products p
  USING (SELECT 101 AS id, '手机' AS name, 2999 AS price FROM dual) n
  ON (p.id = n.id)
  WHEN MATCHED THEN UPDATE SET p.name = n.name, p.price = n.price
  WHEN NOT MATCHED THEN INSERT (id, name, price) VALUES (n.id, n.name, n.price);
  

4. DELETE与TRUNCATE对比

特性	DELETE	TRUNCATE
语法	DELETE FROM table WHERE...	TRUNCATE TABLE table
条件过滤	支持WHERE子句	不支持任何条件
事务	可回滚	不可回滚
性能	逐行删除，较慢	直接清空表，极快
适用场景	删除部分数据	清空整个表

5. 外键约束处理

• 级联删除：配置外键时设置ON DELETE CASCADE

  CREATE TABLE score (
    id INT PRIMARY KEY,
    student_id INT,
    FOREIGN KEY (student_id) 
      REFERENCES student(id)
      ON DELETE CASCADE
  );
  

• 手动处理：先删除从表数据

  -- 先删除成绩记录
  DELETE FROM score WHERE student_id = 1;
  -- 再删除学生记录
  DELETE FROM student WHERE id = 1;
  

三、数据定义语言（DDL：Data Definition Language）

数据定义语言(DDL)，专门用于定义和管理数据库结构及其组件。与DML(数据操作语言)不同，DDL操作具有以下显著特点：

• 立即生效：执行后无需COMMIT命令即永久生效
• 不可回滚：无法通过ROLLBACK撤销操作
• 自动提交：会隐式提交当前事务中的所有未提交操作
• 结构影响：主要作用于数据库对象而非数据本身

3.1 核心命令与语法详解

(1) 数据库操作

创建数据库

-- 创建名为"school"的数据库，字符集为utf8mb4
CREATE DATABASE IF NOT EXISTS school
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

参数说明：

• IF NOT EXISTS：避免重复创建时出错
• utf8mb4：完全兼容UTF-8，支持4字节的Unicode字符(如emoji)
• COLLATE：指定排序规则，utf8mb4_unicode_ci支持不区分大小写的排序

修改数据库

-- 修改school数据库的字符集和排序规则
ALTER DATABASE school
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_unicode_ci;

应用场景：

• 解决数据库迁移时的字符集兼容问题
• 需要支持新语言字符时调整字符集

删除数据库

-- 安全删除school数据库
DROP DATABASE IF EXISTS school;

注意事项：

• 删除操作会同时删除该库中所有表、视图、存储过程等对象
• 生产环境建议先备份再执行删除操作
• MySQL中数据库实际对应数据目录下的一个文件夹

(2) 表操作

创建表

-- 完整的学生表创建示例
CREATE TABLE IF NOT EXISTS student (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID',
    name VARCHAR(50) NOT NULL COMMENT '学生姓名',
    age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '学生年龄',
    gender ENUM('男','女','其他') NOT NULL DEFAULT '男' COMMENT '性别',
    class VARCHAR(50) NOT NULL COMMENT '班级名称',
    student_no VARCHAR(20) UNIQUE NOT NULL COMMENT '学号',
    phone CHAR(11) COMMENT '联系电话',
    address TEXT COMMENT '家庭住址',
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生基本信息表';

关键要素详解：

1. 数据类型选择：

   • VARCHAR vs CHAR：变长vs定长，VARCHAR适合长度不固定数据
   • INT系列：根据范围选择TINYINT/SMALLINT/INT/BIGINT
   • 时间类型：DATETIME(8字节) vs TIMESTAMP(4字节)

2. 约束类型：

   • PRIMARY KEY：主键约束，确保唯一且非空
   • UNIQUE：唯一约束，允许NULL值
   • NOT NULL：非空约束
   • DEFAULT：默认值约束
   • CHECK：检查约束(MySQL 8.0+支持)

3. 表选项：

   • ENGINE：存储引擎(InnoDB/MyISAM等)
   • CHARSET：字符集设置
   • COMMENT：表注释
   • AUTO_INCREMENT：自增起始值设置

修改表结构

-- 添加新列
ALTER TABLE student
ADD COLUMN email VARCHAR(100) COMMENT '电子邮箱';

-- 修改列定义
ALTER TABLE student
MODIFY COLUMN phone VARCHAR(20) COMMENT '联系电话(支持国际号码)';

-- 重命名列
ALTER TABLE student
CHANGE COLUMN address home_address TEXT COMMENT '家庭住址';

-- 添加约束
ALTER TABLE student
ADD CONSTRAINT uk_email UNIQUE (email);

-- 删除约束
ALTER TABLE student
DROP INDEX uk_email;

-- 修改表选项
ALTER TABLE student
ENGINE=InnoDB, DEFAULT CHARSET=utf8mb4;

删除表

-- 安全删除表
DROP TABLE IF EXISTS student_backup;

-- 清空表(DDL方式，比DELETE快且不记录日志)
TRUNCATE TABLE student_log;

(3) 索引操作

创建索引

-- 单列索引
CREATE INDEX idx_student_name ON student(name);

-- 多列复合索引
CREATE INDEX idx_student_class_age ON student(class, age DESC);

-- 唯一索引
CREATE UNIQUE INDEX uk_student_phone ON student(phone);

-- 前缀索引(适合长文本)
CREATE INDEX idx_student_address ON student(address(20));

索引设计原则：

1. 选择性原则：选择区分度高的列(如ID、学号)
2. 最左前缀原则：复合索引(a,b,c)只能用于a|a,b|a,b,c查询
3. 覆盖索引：索引包含查询所需全部字段时可避免回表
4. 避免过度索引：每个索引会增加写操作开销

删除索引

-- 删除普通索引
DROP INDEX idx_student_name ON student;

-- 删除主键索引(需先删除自增属性)
ALTER TABLE student MODIFY id INT;
ALTER TABLE student DROP PRIMARY KEY;

(4) 视图操作

创建视图

-- 创建班级学生统计视图
CREATE VIEW v_class_student_count AS
SELECT 
    class,
    COUNT(*) AS student_count,
    AVG(age) AS avg_age,
    COUNT(CASE WHEN gender='男' THEN 1 END) AS male_count,
    COUNT(CASE WHEN gender='女' THEN 1 END) AS female_count
FROM student
GROUP BY class;

-- 带检查选项的视图
CREATE VIEW v_senior_students AS
SELECT id, name, class
FROM student
WHERE class LIKE '高三%'
WITH CHECK OPTION;

视图类型：

1. 简单视图：基于单表查询
2. 复杂视图：包含聚合、多表连接等
3. 物化视图：实际存储结果集(MySQL不直接支持)

修改视图

-- 修改视图定义
ALTER VIEW v_class_student_count AS
SELECT 
    class,
    COUNT(*) AS student_count,
    MAX(age) AS max_age,
    MIN(age) AS min_age
FROM student
GROUP BY class;

删除视图

-- 安全删除视图
DROP VIEW IF EXISTS v_old_student_view;

3.2 高级DDL操作

分区表操作

-- 按范围分区
CREATE TABLE sales (
    id INT AUTO_INCREMENT,
    sale_date DATE NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (id, sale_date)
) PARTITION BY RANGE (YEAR(sale_date)) (
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

-- 新增分区
ALTER TABLE sales REORGANIZE PARTITION pmax INTO (
    PARTITION p2021 VALUES LESS THAN (2022),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

临时表操作

-- 会话级临时表
CREATE TEMPORARY TABLE temp_student SELECT * FROM student WHERE class='高三(1)班';

-- 事务结束自动删除
DROP TEMPORARY TABLE IF EXISTS temp_student;

3.3 最佳实践与注意事项

1. 变更管理：

   • 所有DDL操作应通过变更管理系统审核
   • 生产环境变更应在低峰期执行
   • 重大变更前必须备份数据

2. 性能考虑：

   • 大表ALTER操作可能导致锁表，考虑使用在线DDL工具(pt-online-schema-change)
   • 索引创建优先在非业务高峰期进行

3. 兼容性问题：

   • 不同数据库版本间DDL语法可能有差异
   • 存储引擎特性差异(InnoDB vs MyISAM)

4. 安全建议：

   • 限制生产环境直接执行DDL的账号权限
   • 敏感操作(如DROP)应实施二次确认机制

5. 监控与维护：

   • 定期检查数据库对象碎片情况
   • 监控无效索引的使用情况
   • 统计表大小增长趋势

四、数据控制语言（DCL：Data Control Language）

DCL(Data Control Language)是SQL语言的重要组成部分，用于控制数据库的访问权限，包括授予权限、回收权限、管理用户等操作。其主要命令为GRANT(授予)和REVOKE(回收)。

4.1 核心语法与示例详解

(1) 用户管理(以MySQL为例)

创建用户

创建用户时需要指定三个关键信息：用户名、登录主机和密码。主机地址可以限制用户只能从特定位置访问数据库。

-- 创建用户"user1"，允许从任何主机远程登录(使用%通配符)，密码为"123456"
CREATE USER 'user1'@'%' IDENTIFIED BY '123456';

-- 创建用户"user2"，仅允许从本地主机登录
CREATE USER 'user2'@'localhost' IDENTIFIED BY '123456';

-- 创建用户"user3"，仅允许从特定IP(192.168.1.100)登录
CREATE USER 'user3'@'192.168.1.100' IDENTIFIED BY '123456';

修改用户密码

-- 修改"user1"的密码为"654321"，符合新的密码策略要求
ALTER USER 'user1'@'%' IDENTIFIED BY '654321';

-- MySQL 5.7及以下版本修改密码的语法
SET PASSWORD FOR 'user1'@'%' = PASSWORD('654321');

删除用户

-- 删除用户"user1"
DROP USER 'user1'@'%';

-- 删除不存在的用户时会报错，可以先判断存在再删除(MySQL 8.0+)
DROP USER IF EXISTS 'user1'@'%';

(2) 权限授予：GRANT详解

MySQL的权限系统非常细致，可以分为多个层级：

1. 全局权限：作用于整个MySQL服务器
2. 数据库级权限：作用于特定数据库
3. 表级权限：作用于特定表
4. 列级权限：作用于特定列
5. 存储过程/函数权限：作用于特定存储过程或函数

常见权限分类

权限类型	权限说明	适用场景
数据操作权限	SELECT, INSERT, UPDATE, DELETE	常规数据操作
结构操作权限	CREATE, ALTER, DROP, INDEX	数据库结构变更
管理权限	CREATE USER, GRANT OPTION, SUPER	系统管理操作
特殊权限	ALL PRIVILEGES, PROCESS, FILE	高级管理权限

权限授予示例

示例1：授予数据库级权限

-- 授予"user1"对"school"数据库的所有表的SELECT、INSERT、UPDATE、DELETE权限
GRANT SELECT, INSERT, UPDATE, DELETE ON school.* TO 'user1'@'%';

-- 授予"user2"对"school"数据库的所有权限
GRANT ALL PRIVILEGES ON school.* TO 'user2'@'localhost';

示例2：授予表级权限

-- 授予"user1"对"school.student"表的SELECT权限
GRANT SELECT ON school.student TO 'user1'@'%';

-- 授予"user1"对"school.teacher"表的INSERT权限
GRANT INSERT ON school.teacher TO 'user1'@'%';

示例3：授予列级权限

-- 授予"user1"对"school.student"表的"name"和"age"列的SELECT权限
GRANT SELECT(name, age) ON school.student TO 'user1'@'%';

示例4：授予管理权限

-- 授予"admin"用户创建用户和授予权限的权限
GRANT CREATE USER, GRANT OPTION ON *.* TO 'admin'@'%';

示例5：授予所有权限

-- 授予"root"用户对所有数据库的所有权限
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

(3) 权限回收：REVOKE详解

权限回收是安全策略中的重要环节，当员工离职或角色变更时，应及时回收不必要的权限。

权限回收示例

基本回收

-- 回收"user1"对"school"数据库所有表的DELETE权限
REVOKE DELETE ON school.* FROM 'user1'@'%';

-- 回收"user1"对"school.student"表的SELECT权限
REVOKE SELECT ON school.student FROM 'user1'@'%';

特殊回收

-- 回收"user1"的所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'user1'@'%';

-- 回收"user1"的GRANT OPTION权限
REVOKE GRANT OPTION ON *.* FROM 'user1'@'%';

4.2 安全注意事项与实践建议

1. 权限最小化原则

• 开发环境：开发者通常只需要SELECT、INSERT、UPDATE、DELETE权限
• 测试环境：测试人员可能需要CREATE TEMPORARY TABLES权限
• 生产环境：严格限制ALTER、DROP等危险权限

最佳实践：

-- 为报表用户仅授予只读权限
GRANT SELECT ON analytics.* TO 'report_user'@'10.0.0.%';

-- 为应用用户授予基本操作权限
GRANT SELECT, INSERT, UPDATE ON app_db.* TO 'app_user'@'app_server';

2. 登录主机限制

• 避免使用%通配符，尽量限制到特定IP或IP段
• 对于内部系统，可以限制到特定VLAN
• 对于远程访问，考虑使用VPN或SSH隧道

示例：

-- 仅允许从办公网络访问
CREATE USER 'office_user'@'192.168.1.%' IDENTIFIED BY 'ComplexP@ssw0rd2023';

-- 仅允许从跳板机访问
CREATE USER 'admin_user'@'10.0.0.100' IDENTIFIED BY 'AnotherC0mplexP@ss';

3. 密码安全策略

• 长度要求：至少12个字符
• 复杂度要求：包含大小写字母、数字和特殊字符
• 有效期：90天强制更换
• 历史密码：禁止使用最近5次用过的密码

MySQL 8.0密码策略配置：

-- 设置全局密码策略
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.number_count = 1;
SET GLOBAL validate_password.special_char_count = 1;

4. 权限生效机制

• 在MySQL中，执行FLUSH PRIVILEGES会重新加载权限表
• 在Oracle中，权限变更通常立即生效
• 在SQL Server中，可能需要重新登录才能使权限生效

MySQL权限刷新：

-- 使权限变更立即生效
FLUSH PRIVILEGES;

-- 对于大量权限变更，可以考虑重启服务
-- sudo systemctl restart mysql

5. 不同数据库的DCL差异

特性	MySQL	Oracle	SQL Server	PostgreSQL
用户创建	CREATE USER	CREATE USER	CREATE LOGIN	CREATE USER
权限授予	GRANT	GRANT	GRANT + ADD ROLE	GRANT
权限回收	REVOKE	REVOKE	REVOKE + DROP ROLE	REVOKE
角色管理	8.0+支持	完善	完善	完善
权限粒度	全局/库/表/列	对象/系统	服务器/数据库/对象	对象/列

Oracle示例：

-- 创建用户并授予权限
CREATE USER orcl_user IDENTIFIED BY "P@ssw0rd"
DEFAULT TABLESPACE users
QUOTA 100M ON users;

GRANT CREATE SESSION, SELECT ANY TABLE TO orcl_user;

SQL Server示例：

-- 创建登录和用户
CREATE LOGIN sql_user WITH PASSWORD = 'C0mplexP@ss';
USE my_database;
CREATE USER sql_user FOR LOGIN sql_user;

-- 授予权限
GRANT SELECT, INSERT ON dbo.Customers TO sql_user;

PostgreSQL示例：

-- 创建用户并授予权限
CREATE USER pg_user WITH PASSWORD 'Secur3P@ss';
GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO pg_user;

五、事务控制语言（TCL：Transaction Control Language）

5.1 事务的 ACID 特性深入解析

原子性（Atomicity）

• 本质：事务是不可分割的工作单元，要么全部执行成功，要么全部执行失败，不存在中间状态
• 实现机制：数据库通过撤销日志（Undo Log）记录事务每一步操作，如果事务失败，系统会按照日志反向执行所有操作
• 典型应用场景：
  • 银行转账业务：从账户A转100元到账户B，必须同时完成扣款和入账两个操作
  • 订单系统：创建订单时，需要同时减少库存和生成订单记录

一致性（Consistency）

• 本质：事务执行前后，数据库从一个一致性状态转变为另一个一致性状态
• 实现机制：
  • 通过数据库约束（主键、外键、唯一性约束等）
  • 通过业务规则验证
  • 通过触发器（Trigger）等机制
• 示例：
  • 转账前后总金额必须相等
  • 订单金额必须等于商品单价乘以数量
  • 外键关联的数据必须存在（如订单必须关联有效的用户ID）

隔离性（Isolation）

• 本质：并发事务相互隔离，一个事务的执行不应影响其他事务
• 常见并发问题：
  • 脏读（Dirty Read）：读取到其他事务未提交的数据
  • 不可重复读（Non-repeatable Read）：同一事务内两次读取同一数据结果不同
  • 幻读（Phantom Read）：同一事务内两次查询返回的行数不同
• 隔离级别（从低到高）：
  1. READ UNCOMMITTED（读未提交）
  2. READ COMMITTED（读已提交）
  3. REPEATABLE READ（可重复读）
  4. SERIALIZABLE（串行化）

持久性（Durability）

• 本质：事务一旦提交，其结果就是永久性的，即使系统故障也不会丢失
• 实现机制：
  • 重做日志（Redo Log）：记录事务对数据页的物理修改
  • 预写式日志（WAL）机制：先写日志再修改数据
  • 定期检查点（Checkpoint）机制

5.2 核心命令与详细示例

（1）提交事务：COMMIT

完整示例（MySQL环境）：

-- 开始事务（显式开启）
START TRANSACTION;

-- 设置保存点（可选）
SAVEPOINT before_transfer;

-- 执行转账操作：A账户转出100元
UPDATE accounts SET balance = balance - 100 
WHERE account_id = 'A' AND balance >= 100; -- 余额检查

-- 检查受影响行数，确保扣款成功
SELECT ROW_COUNT() INTO @rows_affected;

-- 如果扣款成功，则继续转账
IF @rows_affected > 0 THEN
    -- B账户转入100元
    UPDATE accounts SET balance = balance + 100 
    WHERE account_id = 'B';
    
    -- 记录交易流水
    INSERT INTO transaction_logs 
    (tx_id, from_account, to_account, amount, tx_time)
    VALUES 
    (UUID(), 'A', 'B', 100, NOW());
    
    -- 提交事务
    COMMIT;
    SELECT 'Transaction completed successfully' AS result;
ELSE
    -- 回滚事务
    ROLLBACK;
    SELECT 'Transaction failed: insufficient balance' AS result;
END IF;

（2）回滚事务：ROLLBACK

高级用法示例：

-- 开始事务
BEGIN;

-- 操作1：减少库存
UPDATE products SET stock = stock - 1 
WHERE product_id = 1001 AND stock > 0;

-- 检查库存是否足够
SELECT ROW_COUNT() INTO @inventory_updated;

-- 操作2：创建订单
INSERT INTO orders 
(order_id, customer_id, product_id, quantity, order_date)
VALUES
('ORD20230001', 'CUST1001', 1001, 1, CURDATE());

-- 获取订单ID
SET @new_order_id = LAST_INSERT_ID();

-- 操作3：扣除账户余额
UPDATE customer_accounts 
SET balance = balance - (SELECT price FROM products WHERE product_id = 1001)
WHERE customer_id = 'CUST1001';

-- 检查是否所有操作都成功
IF @inventory_updated > 0 AND ROW_COUNT() > 0 THEN
    -- 所有操作成功，提交事务
    COMMIT;
    SELECT CONCAT('Order placed successfully. Order ID: ', @new_order_id) AS message;
ELSE
    -- 任一操作失败，回滚所有更改
    ROLLBACK;
    
    -- 检查具体失败原因
    IF @inventory_updated <= 0 THEN
        SELECT 'Transaction failed: product out of stock' AS message;
    ELSE
        SELECT 'Transaction failed: insufficient account balance' AS message;
    END IF;
END IF;

（3）保存点：SAVEPOINT

复杂事务处理示例：

-- 开始事务
START TRANSACTION;

-- 阶段1：创建客户记录
INSERT INTO customers 
(customer_id, name, email, registration_date)
VALUES
('CUST2001', 'John Doe', 'john@example.com', CURDATE());

-- 设置保存点1
SAVEPOINT after_customer_creation;

-- 阶段2：创建客户账户
INSERT INTO customer_accounts
(account_id, customer_id, account_type, balance)
VALUES
('ACC3001', 'CUST2001', 'SAVINGS', 0);

-- 检查是否成功
SELECT ROW_COUNT() INTO @account_created;

IF @account_created > 0 THEN
    -- 阶段3：初始存款
    UPDATE customer_accounts 
    SET balance = balance + 500
    WHERE account_id = 'ACC3001';
    
    -- 设置保存点2
    SAVEPOINT after_initial_deposit;
    
    -- 阶段4：创建借记卡
    INSERT INTO debit_cards
    (card_number, account_id, expiry_date, cvv)
    VALUES
    ('4111111111111111', 'ACC3001', DATE_ADD(CURDATE(), INTERVAL 3 YEAR), FLOOR(RAND()*900)+100);
    
    -- 检查是否成功
    SELECT ROW_COUNT() INTO @card_created;
    
    IF @card_created > 0 THEN
        -- 所有操作成功，提交事务
        COMMIT;
        SELECT 'New customer onboarding completed successfully' AS result;
    ELSE
        -- 回滚到初始存款后的状态
        ROLLBACK TO after_initial_deposit;
        COMMIT;
        SELECT 'Customer created with account but failed to issue debit card' AS result;
    END IF;
ELSE
    -- 回滚到客户创建后的状态
    ROLLBACK TO after_customer_creation;
    COMMIT;
    SELECT 'Customer created but failed to create account' AS result;
END IF;

5.3 高级注意事项与最佳实践

存储引擎支持差异

• MySQL：

  • InnoDB：完全支持事务、行级锁、外键
  • MyISAM：不支持事务，表级锁，崩溃后恢复困难
  • 其他引擎：Memory（内存表，不支持事务）、NDB（集群引擎，有限事务支持）

• Oracle：所有表空间默认支持事务

• SQL Server：所有表默认支持事务

自动提交机制的深入理解

• 默认行为：

  -- MySQL默认
  SHOW VARIABLES LIKE 'autocommit';  -- 通常为ON
  
  -- 临时关闭（仅当前会话）
  SET autocommit = 0;
  
  -- 永久修改（需要重启）
  SET GLOBAL autocommit = 0;
  

• 编程接口中的处理：

  • JDBC：可通过connection.setAutoCommit(false)关闭
  • Python MySQLdb：conn.autocommit(False)
  • ORM框架：通常有事务管理机制（如Hibernate的@Transactional）

隔离级别详解与选择建议

隔离级别	脏读	不可重复读	幻读	性能影响
READ UNCOMMITTED	可能	可能	可能	最低
READ COMMITTED	避免	可能	可能	较低
REPEATABLE READ	避免	避免	可能	中等
SERIALIZABLE	避免	避免	避免	最高

设置示例：

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置全局隔离级别（需要重启）
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 仅设置下一个事务的隔离级别
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

保存点的高级应用

• 嵌套事务模拟：通过保存点实现类似嵌套事务的效果
• 部分回滚：只回滚事务中的部分操作，保留其他操作
• 限制：
  • 不能跨越事务边界
  • 提交或完全回滚后所有保存点自动清除
  • 不同数据库实现的保存点数量限制不同

长事务处理策略

1. 优化事务设计：

   • 将大事务拆分为多个小事务
   • 避免在事务中进行耗时操作（如网络请求、文件IO）

2. 锁管理：

   -- 查询当前锁情况（MySQL）
   SHOW ENGINE INNODB STATUS;
   
   -- 查询长时间运行的事务
   SELECT * FROM information_schema.innodb_trx 
   WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;
   

3. 超时设置：

   -- 设置事务超时（MySQL 5.7+）
   SET SESSION innodb_lock_wait_timeout = 30;  -- 单位秒
   
   -- Oracle设置
   ALTER SESSION SET ddl_lock_timeout = 30;
   

4. 监控与告警：

   • 监控事务执行时间
   • 设置自动终止长时间运行的事务
   • 记录事务日志用于分析优化

六、五大语言对比与应用场景

语言分类	核心命令	作用	事务特性	关键注意事项
DSL	SELECT	查询数据	无（仅读取）	子句顺序、NULL处理、索引优化
DML	INSERT、UPDATE、DELETE	修改数据	支持（需 COMMIT/ROLLBACK）	必须加WHERE、主键冲突处理
DDL	CREATE、ALTER、DROP	定义结构	无（立即生效，不可回滚）	谨慎使用DROP、字符集统一
DCL	GRANT、REVOKE	控制权限	无	权限最小化、密码安全
TCL	COMMIT、ROLLBACK、SAVEPOINT	管理事务	核心（保证 ACID）	关闭自动提交、避免长事务