MySQL_DML_DDL_约束与事务详解

本文系统总结 MySQL 三大语言体系——DML（增删改）、DDL（库表结构管理）、DCL（事务控制），并深入讲解约束条件与视图。涵盖 INSERT/UPDATE/DELETE/TRUNCATE 语法与对比、库表创建修改删除、六大约束、ACID 四大特性、SAVEPOINT 部分回滚及四种事务隔离级别（脏读/不可重复读/幻读），附大量实战示例与对比表格，是面试与开发的必备参考。

MySQL数据操作、表管理、约束与事务详解（DML/DDL/DCL全总结）

目录

• 前言

• 数据准备

• 一、DML数据操作语言

  • 1.1 插入（INSERT）

  • 1.2 修改（UPDATE）

  • 1.3 删除（DELETE）

  • 1.4 TRUNCATE 截断表

• 二、DDL数据定义语言

  • 2.1 库的管理

  • 2.2 表的管理

  • 2.3 常见数据类型

• 三、约束条件

  • 3.1 约束概念

  • 3.2 六大约束分类

  • 3.3 添加约束时机

  • 3.4 列级约束 vs 表级约束

  • 3.5 主键 vs 唯一对比

  • 3.6 外键设置注意事项

  • 3.7 实战：学生表 + 专业表

  • 3.8 修改表时添加约束

  • 3.9 修改表时删除约束

  • 3.10 标识列（AUTO_INCREMENT）

• 四、视图

• 五、DCL事务控制语言

  • 5.1 事务概念

  • 5.2 ACID四大特性

  • 5.3 事务创建

  • 5.4 SAVEPOINT 保存点与部分回滚

  • 5.5 事务隔离级别

• 总结

前言

在 MySQL 中，SQL 语句按照功能可以划分为三大语言体系：

• DML（Data Manipulation Language，数据操作语言）：用于对表中的数据进行增、删、改，主要包括 INSERT、UPDATE、DELETE、TRUNCATE 等语句。

• DDL（Data Definition Language，数据定义语言）：用于定义和管理数据库及表的结构，主要包括 CREATE、ALTER、DROP 等语句。

• DCL（Data Control Language，事务控制语言）：用于管理事务和权限，本文重点关注事务相关的 COMMIT、ROLLBACK、SAVEPOINT 以及事务隔离级别等。

本文将围绕这三大语言体系展开，系统讲解每个知识点的语法格式、实战示例和对比要点。约束（保证数据可靠性）和事务（保证操作原子性）是面试的重点，请务必掌握。

数据准备

本文示例基于 beauty、boys、account、stuinfo、major、book、author 共 7 张表。完整建表与数据脚本见配套文件 00_数据准备_建表与数据.sql，在 Navicat 中全选运行即可。

说明：第一节（DML）的 beauty / boys 示例从空表开始演示数据操作流程，便于读者循序渐进理解每条语句的效果；其余章节（视图、事务等）基于以下初始数据。

beauty 表（女神信息）

boys 表（男神信息）

account 表（账户信息）

stuinfo 表（学生信息）

major 表（专业信息）

book 表（图书信息）

author 表（作者信息）

一、DML数据操作语言

DML 主要用于对表中的 数据 进行增删改，是日常开发中使用频率最高的一类 SQL。

1.1 插入（INSERT）

两种语法格式

方式一：使用 VALUES

— 语法格式一：指定列名 + 值
INSERT INTO 表名(列名1, 列名2, ...) VALUES(值1, 值2, ...);

— 语法格式一（省略列名）：按表中列的顺序插入所有列
INSERT INTO 表名 VALUES(值1, 值2, ...);

方式二：使用 SET

— 语法格式二：列名 = 值 的形式
INSERT INTO 表名 SET 列名1 = 值1, 列名2 = 值2, ...;

两种方式对比

结论：实际开发推荐使用方式一，功能更全面。

插入注意事项

示例：基础插入

— 1. 插入的值的类型要与列的类型一致或兼容
INSERT INTO beauty (id, `name`, sex, borndate, phone, photo, boyfriend_id)
VALUES (1, '迪丽热巴', '女', '1992-06-03', '17000000000', NULL, 1);

— 2. 不可以为null的列必须插入值，可以为null的列如何插入值？
— 方式一：显式写 NULL
INSERT INTO beauty (id, `name`, sex, borndate, phone, photo, boyfriend_id)
VALUES (2, '迪丽热巴', NULL, NULL, NULL, NULL, 1);

— 方式二：省略可以为null的列（photo、borndate 等可省）
INSERT INTO beauty (id, `name`, sex, boyfriend_id)
VALUES (3, '赵丽颖', '女', 2);

— 3. 列的顺序是否可以调换？可以
INSERT INTO beauty (`name`, sex, id, boyfriend_id)
VALUES ('赵丽颖2', '女', 4, 2);

— 4. 列数和值的个数是否必须一致？必须一致（不一致会报错）
— INSERT INTO beauty (id, `name`, sex, boyfriend_id)
— VALUES (5, '赵丽颖', '女', NULL, 2); — 错误：列数4个，值5个

— 5. 可以省略列名，默认是所有列，且顺序与表结构一致
INSERT INTO beauty VALUES (6, '杨幂', '女', '1986-08-12', '18000000000', NULL, 3);

运行结果：

Affected rows: 5

执行后 beauty 表新增 5 条记录（id 1~4、6）。其中 id=2 的 sex/borndate/phone 均为 NULL，id=3、4 省略了可空列（borndate/phone/photo 默认 NULL），id=5 的错误示例已注释跳过。执行后表数据如下：

示例：方式二（SET）插入

— 正确写法
INSERT INTO beauty
SET id = 7, name = '刘亦菲', sex = '女';

— 错误写法：不可省略 id（主键非空约束）
INSERT INTO beauty
SET name = '刘亦菲', sex = '女'; — 报错：id 没有默认值

运行结果：

Affected rows: 1

正确写法新增 id=7 的刘亦菲记录，其余可空列默认为 NULL。错误写法会报错：Field 'id' doesn't have a default value。执行后表数据如下：

示例：多行插入（仅方式一支持）

— 方式一支持一次性插入多行数据
INSERT INTO beauty VALUES
(8, '杨幂2', '女', '1986-08-12', '15000000000', NULL, 3),
(9, '杨幂3', '女', '1986-08-12', '16000000000', NULL, 3),
(10, '杨幂4', '女', '1986-08-12', '17000000000', NULL, 3),
(11, '杨幂5', '女', '1986-08-12', '18000000000', NULL, 3),
(12, '杨幂6', '女', '1986-08-12', '19000000000', NULL, 3),
(13, '杨幂7', '女', '1986-08-12', '11000000000', NULL, 3),
(14, '杨幂78', '女', '1986-08-12', '12000000000', NULL, 3);

— 方式二不支持多行插入，下面写法是错误的：
— INSERT INTO beauty
— SET id=15, name='刘亦菲2', sex='女',
— SET id=16, name='刘亦菲3', sex='女',
— SET id=17, name='刘亦菲4', sex='女';

运行结果：

Affected rows: 7

一次性插入 7 行记录（id 8~14），全部姓名以"杨幂"开头，boyfriend_id 均为 3。执行后 beauty 表共 13 条记录。

1.2 修改（UPDATE）

修改单表记录语法

— 精简版语法
UPDATE 表名
SET 列名 = 新值, 列名 = 新值, ...
WHERE 筛选条件;

注意：UPDATE 的执行逻辑顺序是 先 WHERE 筛选出要修改的行，再 SET 更新列的值，书写时按 SET、WHERE 顺序即可。

修改多表记录语法（JOIN + SET）

— 精简版语法
UPDATE 表1 别名
INNER | LEFT | RIGHT JOIN 表2 别名
ON 连接条件
SET 列 = 新值, 列 = 新值, ...
WHERE 筛选条件;

示例：修改单表

— 修改姓名以"杨"开头的女明星的手机号
UPDATE beauty SET phone = '1919999999'
WHERE name LIKE '杨%';

— 修改 boys 表中 id 为 1 的名称为：汪峰2，魅力值为：10000
UPDATE boys SET boyName = '汪峰2', userCP = 10000
WHERE id = 1;

运行结果：

Affected rows: 8（第1条） + 1（第2条）

• 第1条：name 以"杨"开头的 8 条记录（id 6、8~14），phone 全部改为 1919999999

• 第2条：boys 表 id=1 的记录改为 boyName=汪峰2，userCP=10000

示例：修改多表（修改汪峰对应的女明星手机号）

— 修改汪峰2对应的女明星的手机号为911
UPDATE boys bo
INNER JOIN beauty b ON bo.id = b.boyfriend_id
SET b.phone = '911'
WHERE bo.boyName = '汪峰2';

— 修改没有男朋友的女明星的男朋友编号为 1
UPDATE boys bo
RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
SET b.boyfriend_id = 1
WHERE bo.id IS NULL;

运行结果：

Affected rows: 2（第1条） + 1（第2条）

• 第1条：通过 INNER JOIN 找到 boyfriend_id=1（汪峰2）的女明星（id 1、2），phone 改为 911

• 第2条：通过 RIGHT JOIN 找到没有男朋友的女明星（id=7 刘亦菲，boyfriend_id 为 NULL），将其 boyfriend_id 改为 1

执行后 beauty 表关键变化：

• id=1、2 的 phone 从 1919999999/NULL 改为 911

• id=7 的 boyfriend_id 从 NULL 改为 1

1.3 删除（DELETE）

单表删除语法

DELETE FROM 表名 WHERE 筛选条件;

多表删除语法

— 精简版语法
DELETE 表1的别名, 表2的别名
FROM 表1 别名
INNER | LEFT | RIGHT JOIN 表2 别名 ON 连接条件
WHERE 筛选条件;

示例：单表删除

— 删除手机号以91开头的女神信息
DELETE FROM beauty WHERE phone LIKE '91%';

运行结果：

Affected rows: 2

phone 以 91 开头的记录（id=1、2，phone 均为 911）被删除。执行后 beauty 表剩余 11 条记录（id 3、4、6~14）。

示例：多表删除（删除刘恺威及女朋友信息）

— 删除刘恺威的信息以及他女朋友的信息
DELETE b, bo
FROM beauty b
INNER JOIN boys bo ON b.boyfriend_id = bo.id
WHERE bo.boyName = '刘恺威';

运行结果：

Affected rows: 9（beauty 8 行 + boys 1 行）

通过 INNER JOIN 找到 boyfriend_id=3（刘恺威）的女明星（id 6、8~14）以及 boys 表中 id=3 的记录，一并删除。执行后：

• beauty 剩 3 行（id 3、4、7）

• boys 剩 4 行（id 1 汪峰2/10000、id 2 冯绍峰/700、id 4 黄晓明/900、id 5 张继科/850）

1.4 TRUNCATE 截断表

语法

TRUNCATE [TABLE] 表名;

TRUNCATE 用于一次性清空表中的所有数据，比 DELETE FROM 表名 更高效。

DELETE vs TRUNCATE 五大区别对比表

示例：对比演示

— DELETE 清空后再插入，自增长列从断点值继续
SELECT * FROM beauty;
DELETE FROM beauty;
— 此时再插入数据，id 会从断点（如 15）继续增长

— TRUNCATE 清空后再插入，自增长列从 1 重新开始
TRUNCATE TABLE beauty;

— 重新插入数据（id 为 NULL 表示让自增长列自动赋值）
INSERT INTO beauty VALUES
(NULL, '杨幂2', '女', '1986-08-12', '15000000000', NULL, 3),
(NULL, '杨幂3', '女', '1986-08-12', '16000000000', NULL, 3),
(NULL, '杨幂4', '女', '1986-08-12', '17000000000', NULL, 3);
— 此时 id 从 1 开始重新编号

运行结果：

1. SELECT * FROM beauty（执行前，beauty 表当前 3 行）：

2. DELETE FROM beauty：

Affected rows: 3

3. TRUNCATE TABLE beauty：

Query OK, 0 rows affected (0.00 sec)

表已清空。若 id 列设置了 AUTO_INCREMENT，自增长计数器重置为 1。

4. INSERT 3 行：

Affected rows: 3

注意：当前 beauty 表的 id 列为 INT PRIMARY KEY（无 AUTO_INCREMENT），使用 NULL 插入会报错。需先将 id 列改为 INT PRIMARY KEY AUTO_INCREMENT 才能使用 NULL 自动赋值。DELETE 后再插入，自增长列从断点继续；TRUNCATE 后再插入，自增长列从 1 重新开始。

二、DDL数据定义语言

DDL 用于定义和管理数据库及表的结构（而非数据）。本节只给出精简版语法总结，完整官方语法过长，请参考 MySQL 官方文档。

2.1 库的管理

创建库

— 精简版语法
CREATE DATABASE [IF NOT EXISTS] 库名;

— 示例：创建 bigdata 数据库
CREATE DATABASE bigdata;

— 加 IF NOT EXISTS，避免库已存在时报错
CREATE DATABASE IF NOT EXISTS bigdata;

运行结果：

Query OK, 1 row affected (0.00 sec)
Query OK, 1 row affected, 1 warning (0.00 sec)

第一条创建 bigdata 库；第二条因库已存在但使用了 IF NOT EXISTS，仅产生 warning 不报错。

修改库

— 精简版语法（修改字符集、只读模式等）
ALTER DATABASE 库名 CHARACTER SET 字符集名;
ALTER DATABASE 库名 READ ONLY = 0或1;

— 示例：修改库的字符集为 utf8
ALTER DATABASE bigdata CHARACTER SET 'utf8';

— 设置库为只读模式（1 为只读，0 为可读写）
ALTER DATABASE bigdata READ ONLY = 0; — 1 表示只读模式

— 查看库的创建信息（\\G 标准化输出）
— SHOW CREATE DATABASE bigdata\\G;

运行结果：

Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

字符集改为 utf8；READ ONLY 设为 0（可读写）。

注意：实际开发中 不建议频繁修改库的字符集，应在创建时确定。

删除库

— 精简版语法
DROP DATABASE [IF EXISTS] 库名;

— 示例：删除 bigdata 库（加 IF EXISTS 更安全）
DROP DATABASE IF EXISTS bigdata;

运行结果：

Query OK, 0 rows affected (0.00 sec)

bigdata 库已删除。

2.2 表的管理

创建表语法总结（精简版）

— 精简版语法
CREATE TABLE 表名(
列名 列的类型[(长度) 约束],
列名 列的类型[(长度) 约束],
列名 列的类型[(长度) 约束],
...
);

查看表结构

— 查看表结构
DESC 表名;

— 查看表的创建语句
SHOW CREATE TABLE 表名;

— 查看索引（包含主键、唯一、外键）
SHOW INDEX FROM 表名;

修改表：ALTER TABLE

— 精简版语法
ALTER TABLE 表名 ADD | DROP | MODIFY | CHANGE | RENAME COLUMN 列名 [列的类型 约束];

示例：创建表 + 修改表

— 创建数据库并进入
CREATE DATABASE IF NOT EXISTS book;
USE book;

— 创建图书表 book
DROP TABLE IF EXISTS book;
CREATE TABLE IF NOT EXISTS book(
id int, — 编号
bName VARCHAR(255), — 图书名
price DOUBLE, — 价格
authorId int, — 作者编号
publishDate DATETIME — 出版日期
);

— 查看表结构
DESC book;

— 创建作者表 author
CREATE TABLE IF NOT EXISTS author(
id int,
au_name VARCHAR(20),
nation VARCHAR(10)
);

DESC author;

运行结果：

Query OK, 1 row affected (0.00 sec) — CREATE DATABASE
Query OK, 0 rows affected (0.01 sec) — CREATE TABLE book
Query OK, 0 rows affected (0.01 sec) — CREATE TABLE author

DESC book：

DESC author：

— 修改 book 表的列名 publishDate -> pubDate
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;

— 修改列的类型或约束（pubDate 改为时间戳 TIMESTAMP）
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;

— 添加新列（给 author 表加 age 列）
ALTER TABLE author ADD COLUMN age int;

— 删除列（删除 author 表的 age 列）
ALTER TABLE author DROP COLUMN age;

— 修改表名（author 重命名为 book_author）
ALTER TABLE author RENAME TO book_author;

— 查看修改后的表结构
DESC book;
DESC author;

运行结果：

Query OK, 0 rows affected (0.02 sec) — CHANGE publishDate -> pubDate
Query OK, 0 rows affected (0.02 sec) — MODIFY pubDate TIMESTAMP
Query OK, 0 rows affected (0.02 sec) — ADD age
Query OK, 0 rows affected (0.02 sec) — DROP age
Query OK, 0 rows affected (0.02 sec) — RENAME author -> book_author

修改后 DESC book：

修改后 DESC book_author（原 author 表）：

删除表

— 精简版语法
DROP TABLE [IF EXISTS] 表名;

— 示例：删除 book_author 表
DROP TABLE IF EXISTS book_author;

运行结果：

Query OK, 0 rows affected (0.01 sec)

book_author 表已删除。

表的复制

表的复制是创建表的一种常用方式，主要有以下几种场景：

— 先准备数据：创建作者表并插入数据
CREATE TABLE IF NOT EXISTS author(
id int,
au_name VARCHAR(20),
nation VARCHAR(10)
);

INSERT INTO author VALUES
(1, '鲁迅', '中国'),
(2, '莫言', '中国'),
(3, '余华', '中国'),
(4, '村上春树', '日本');

运行结果：

Query OK, 0 rows affected (0.01 sec) — CREATE TABLE
Affected rows: 4 — INSERT

author 表数据：

— 1. 复制表的结构 + 数据
CREATE TABLE copy1 SELECT * FROM author;

— 2. 仅复制表的结构（不复制数据）
CREATE TABLE copy2 LIKE author;

— 3. 复制表的部分数据
CREATE TABLE copy3 SELECT
id,
au_name
FROM author
WHERE nation = '中国';

— 4. 复制表的某些字段（不复制数据，where 0 永远为假）
CREATE TABLE copy4 SELECT id, au_name FROM author WHERE 0;

— 5. 复制表的某些字段（复制全部数据，where 1 永远为真）
CREATE TABLE copy5 SELECT id, au_name FROM author WHERE 1;

运行结果：

Query OK, 4 rows affected (0.01 sec) — copy1（4行数据）
Query OK, 0 rows affected (0.01 sec) — copy2（0行数据）
Query OK, 3 rows affected (0.01 sec) — copy3（3行数据）
Query OK, 0 rows affected (0.01 sec) — copy4（0行数据）
Query OK, 4 rows affected (0.01 sec) — copy5（4行数据）

copy1（全部结构 + 全部数据）：

copy2（仅结构，0 行数据）：空表

copy3（nation=‘中国’ 的部分数据，仅 id 和 au_name 列）：

copy4（仅结构，WHERE 0 永假，仅 id 和 au_name 列）：空表

copy5（WHERE 1 永真，全部数据，仅 id 和 au_name 列）：

2.3 常见数据类型

MySQL 提供了丰富的数据类型，这里仅作简要总结，详细说明请参考 MySQL 官方文档。

数值型

日期型

字符型

经验建议：日常开发中字符串类型首选 varchar(255)。

三、约束条件

3.1 约束概念

约束（Constraint）是一种限制，用于限制表中的数据，保证表中的数据的准确性和可靠性。例如：学号不能为空且唯一、性别只能是男或女、员工部门编号必须来自部门表等，都依赖约束来保证。

3.2 六大约束分类

3.3 添加约束时机

约束可以在两个时机添加：

3.4 列级约束 vs 表级约束

列级约束语法格式：

CREATE TABLE 表名(
列名 列的类型 约束条件,
列名 列的类型 约束条件,
...
);

表级约束语法格式：

CREATE TABLE 表名(
列名 列的类型,
列名 列的类型,
...
CONSTRAINT 约束名 约束类型(字段),
...
);

3.5 主键 vs 唯一对比

3.6 外键设置注意事项

设置外键约束时，需注意以下几点：

3.7 实战：学生表 + 专业表

下面分别用列级约束和表级约束两种方式创建学生表（stuinfo，从表）和专业表（major，主表）。

方式一：列级约束版

— 创建数据库并进入
CREATE DATABASE IF NOT EXISTS students;
USE students;

— 创建学生表（从表）—— 列级约束
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
id int PRIMARY KEY, — 主键约束
stuName VARCHAR(20) NOT NULL, — 非空约束
gender CHAR(1) CHECK(gender='男' OR gender='女'), — 检查约束（mysql5.x 不支持）
seat int UNIQUE, — 唯一约束
age int DEFAULT 18, — 默认约束
— 外键约束（列级约束中不生效，仅语法支持）
majorId int REFERENCES major(id)
);

— 创建专业表（主表）
DROP TABLE IF EXISTS major;
CREATE TABLE IF NOT EXISTS major(
id int PRIMARY KEY,
majorName VARCHAR(20)
);

— 查看表结构
DESC stuinfo;

— 查看索引（包含：主键、外键、唯一）
SHOW INDEX FROM stuinfo;

运行结果：

Query OK, 1 row affected (0.00 sec) — CREATE DATABASE
Query OK, 0 rows affected (0.01 sec) — CREATE stuinfo
Query OK, 0 rows affected (0.01 sec) — CREATE major

DESC stuinfo（列级约束版）：

SHOW INDEX FROM stuinfo：

注意：列级约束中 外键约束不生效（REFERENCES major(id) 语法被接受但不实际创建外键），SHOW INDEX 中无外键索引。如需外键请使用表级约束。

方式二：表级约束版

— 先删从表再删主表
DROP TABLE IF EXISTS stuinfo; — 从表
CREATE TABLE IF NOT EXISTS stuinfo(
id int,
stuName VARCHAR(20),
gender CHAR(1),
seat int,
age int,
majorid int,
— 表级约束
CONSTRAINT pk PRIMARY KEY(id), — 主键
CONSTRAINT uq UNIQUE(seat), — 唯一
CONSTRAINT ck CHECK(gender='男' OR gender='女'), — 检查
CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id) — 外键
);

— 创建专业表（主表）
DROP TABLE IF EXISTS major;
CREATE TABLE IF NOT EXISTS major(
id int PRIMARY KEY,
majorName VARCHAR(20)
);

— 插入数据时，先插入主表，再插入从表
— 删除数据时，先删除从表，再删除主表

— 查看表结构
DESC stuinfo;

— 查看索引（包含：主键、唯一、外键）
SHOW INDEX FROM stuinfo;

运行结果：

Query OK, 0 rows affected (0.01 sec) — CREATE stuinfo（含表级约束）
Query OK, 0 rows affected (0.01 sec) — CREATE major

DESC stuinfo（表级约束版）：

SHOW INDEX FROM stuinfo：

与列级约束版相比，表级约束版成功创建了外键索引 fk_stuinfo_major。

3.8 修改表时添加约束

当表已经创建好后，可以通过 ALTER TABLE 追加约束。分两种语法：

ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 约束条件(新约束);

ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型(字段名) [外键引用];

示例：先建无约束表，再逐一添加约束

— 创建无约束的学生表（从表）
DROP TABLE IF EXISTS stuinfo;
CREATE TABLE IF NOT EXISTS stuinfo(
id int,
stuName VARCHAR(20),
gender CHAR(1),
seat int,
age int,
majorid int
);

— 创建专业表（主表）
DROP TABLE IF EXISTS major;
CREATE TABLE IF NOT EXISTS major(
id int PRIMARY KEY,
majorName VARCHAR(20)
);

DESC stuinfo;

运行结果：

Query OK, 0 rows affected (0.01 sec) — CREATE stuinfo
Query OK, 0 rows affected (0.01 sec) — CREATE major

DESC stuinfo（无约束）：

— 添加非空约束（列级）
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NOT NULL;

— 添加默认约束（列级）
ALTER TABLE stuinfo MODIFY COLUMN age int DEFAULT 18;

— 添加主键（列级）
ALTER TABLE stuinfo MODIFY COLUMN id int PRIMARY KEY;
DESC stuinfo;

— 添加主键（表级，等价写法）
— ALTER TABLE stuinfo ADD PRIMARY KEY(id);

— 添加唯一（列级）
ALTER TABLE stuinfo MODIFY COLUMN seat int UNIQUE;

— 添加唯一（表级，等价写法）
— ALTER TABLE stuinfo ADD UNIQUE(seat);

— 添加检查约束（列级）
ALTER TABLE stuinfo MODIFY COLUMN gender CHAR(1) CHECK(gender='男' OR gender='女');

— 添加外键约束（表级，注意先确保主表 major 已存在）
ALTER TABLE stuinfo ADD FOREIGN KEY(majorid) REFERENCES major(id);

DESC stuinfo;
SHOW INDEX FROM stuinfo;

运行结果：

Query OK, 0 rows affected (0.02 sec) — MODIFY stuName NOT NULL
Query OK, 0 rows affected (0.02 sec) — MODIFY age DEFAULT 18
Query OK, 0 rows affected (0.02 sec) — MODIFY id PRIMARY KEY
Query OK, 0 rows affected (0.02 sec) — MODIFY seat UNIQUE
Query OK, 0 rows affected (0.02 sec) — MODIFY gender CHECK
Query OK, 0 rows affected (0.02 sec) — ADD FOREIGN KEY

DESC stuinfo（添加约束后）：

SHOW INDEX FROM stuinfo：

3.9 修改表时删除约束

删除约束同样通过 ALTER TABLE 实现，但不同约束的删除方式略有差异：

— 删除非空约束（改为允许为空）
ALTER TABLE stuinfo MODIFY COLUMN stuName VARCHAR(20) NULL;

— 删除默认约束（去掉默认值）
ALTER TABLE stuinfo MODIFY COLUMN age int;

— 删除主键
ALTER TABLE stuinfo DROP PRIMARY KEY;

— 删除唯一约束（删除的是索引 index）
ALTER TABLE stuinfo DROP INDEX seat;

— 删除外键（注意：设置外键时建议给别名！）
ALTER TABLE stuinfo DROP FOREIGN KEY stuinfo_ibfk_1;

DESC stuinfo;
SHOW INDEX FROM stuinfo;

运行结果：

Query OK, 0 rows affected (0.02 sec) — MODIFY stuName NULL
Query OK, 0 rows affected (0.02 sec) — MODIFY age（去默认值）
Query OK, 0 rows affected (0.02 sec) — DROP PRIMARY KEY
Query OK, 0 rows affected (0.02 sec) — DROP INDEX seat
Query OK, 0 rows affected (0.02 sec) — DROP FOREIGN KEY

DESC stuinfo（删除约束后）：

所有约束均已移除，表恢复为无约束状态。

查询外键约束名称

如果不确定外键约束的名称，可以通过 INFORMATION_SCHEMA 查询：

SELECT
CONSTRAINT_NAME,
COLUMN_NAME,
REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_SCHEMA = 'students'
AND TABLE_NAME = 'stuinfo'
AND REFERENCED_TABLE_NAME IS NOT NULL;

运行结果：

注意：需在执行 DROP FOREIGN KEY 之前运行此查询，删除外键后结果为空。

3.10 标识列（AUTO_INCREMENT）

标识列（自增长列）：可以不用手动插入值，系统会提供默认的序列值。

标识列的特点

示例：创建并使用自增长列

— 查看默认步长
SHOW VARIABLES LIKE '%auto_increment%';

— 创建表时设置自增长列
CREATE TABLE tab_stu(
id int PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);

— 插入数据时，id 传 NULL，由系统自动赋序列值
INSERT INTO tab_stu VALUES(NULL, '张三');

SELECT * FROM tab_stu;

运行结果：

SHOW VARIABLES LIKE ‘%auto_increment%’：

Query OK, 0 rows affected (0.01 sec) — CREATE TABLE
Affected rows: 1 — INSERT

SELECT * FROM tab_stu：

示例：设置步长

— 设置步长为 3（每次自增 3）
SET auto_increment_increment = 3;
— 注意：只允许设置步长，不允许设置偏移量

INSERT INTO tab_stu VALUES
(NULL, '张三1'),
(NULL, '张三2'),
(NULL, '张三3'),
(NULL, '张三4'),
(NULL, '张三5');

— 此时 id 序列为：1, 4, 7, 10, 13（步长为 3）

运行结果：

Query OK, 0 rows affected (0.00 sec) — SET auto_increment_increment=3
Affected rows: 5 — INSERT

SELECT * FROM tab_stu：

已有 id=1，步长为 3，后续 id 依次为 4、7、10、13、16。

四、视图

视图概念

视图（View）是一种 虚拟表，和普通表一样使用，但它本身并不存储数据，而是 通过表动态生成数据。视图基于基表的查询结果而存在，可以简化复杂查询、保护数据安全。

创建视图

— 精简版语法
CREATE VIEW 视图名
AS
SELECT 查询语句;

— 示例：创建视图 myv1，关联学生表和专业表
CREATE VIEW myv1
AS
SELECT
stuname,
majorname
FROM
stuinfo s
INNER JOIN major m ON s.majorid = m.id
WHERE s.stuName LIKE '张%';

— 使用视图（像普通表一样查询）
SELECT * FROM myv1;

运行结果：

Query OK, 0 rows affected (0.01 sec) — CREATE VIEW

SELECT * FROM myv1：

stuinfo 中姓"张"的只有张三（id=1, majorid=1），对应 major 表 id=1 的"计算机科学"，结果为 1 行。

修改视图

— 精简版语法
ALTER VIEW 视图名
AS
SELECT 查询语句;

— 示例：修改视图 myv1，改为只查 stuinfo
ALTER VIEW myv1
AS
SELECT * FROM stuinfo;

运行结果：

Query OK, 0 rows affected (0.01 sec)

视图 myv1 现在等同于 SELECT * FROM stuinfo，查询时返回 stuinfo 全部 7 行数据。

删除视图

— 精简版语法
DROP VIEW [IF EXISTS] 视图名;

— 示例：删除视图 myv1
DROP VIEW IF EXISTS myv1;

运行结果：

Query OK, 0 rows affected (0.00 sec)

视图 myv1 已删除。

五、DCL事务控制语言

5.1 事务概念

事务：一个或一组 SQL 语句组成的一个执行单元，这个执行单元 要么全部执行，要么全部不执行。

经典案例：银行转账

兰智余额：1000
数加余额：1000

— 步骤一：兰智转账 500 给数加
UPDATE 表 SET 兰智余额 = 500 WHERE name = '兰智';

— ⚠ 意外发生（如断电、程序异常），下面语句未执行
— 步骤二：数加收到 500
UPDATE 表 SET 数加余额 = 1500 WHERE name = '数加';

如果没有事务保护，就会出现 兰智扣了钱但数加没收到 的严重数据错误。事务的作用就是保证这两步要么都成功，要么都回滚，从而保证数据的一致性。

5.2 ACID四大特性

事务具有四大特性，简称 ACID：

理解要点：

• 原子性 强调"不可分割"，对应银行转账的两步要么都成功要么都回滚。

• 一致性 强调"数据正确"，转账前后两人总金额应保持 2000 不变。

• 隔离性 强调"互不干扰"，多个事务并发执行时不会互相影响。

• 持久性 强调"永久生效"，commit 后即使断电数据也不丢失。

5.3 事务创建

隐式事务

事务没有明显的开始和结束标记，例如一条 INSERT、UPDATE、DELETE 语句本身就是单独的事务，默认会自动提交。

显式事务

事务具有明显的开启和结束标记，需手动控制。完整步骤如下：

— 步骤一：开启事务（前提：先禁用自动提交）
SET autocommit = 0;
START TRANSACTION; — 可选的

— 步骤二：编写事务中的 SQL 语句（select、insert、update、delete）
— 语句1；
— 语句2；
— …..

— 步骤三：结束事务
COMMIT; — 提交事务（所有改动永久生效）
ROLLBACK; — 回滚事务（所有改动撤销）
SAVEPOINT 节点名; — 设置保存点

示例：查看引擎 + 创建账户表

— 查看 MySQL 引擎（默认引擎 InnoDB，支持事务）
SHOW ENGINES;

— 查看 MySQL 是否开启自动提交
SHOW VARIABLES LIKE 'autocommit';

— 创建账户表
DROP TABLE IF EXISTS account;
CREATE TABLE account(
id int PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
balance DOUBLE
);

— 插入数据
INSERT INTO account(username, balance) VALUES
('兰智', 1000),
('数加', 1000);

— 恢复自增步长为 1
SET auto_increment_increment = 1;

运行结果：

SHOW ENGINES（部分）：

SHOW VARIABLES LIKE ‘autocommit’：

Query OK, 0 rows affected (0.01 sec) — CREATE TABLE
Affected rows: 2 — INSERT
Query OK, 0 rows affected (0.00 sec) — SET auto_increment_increment=1

SELECT * FROM account：

示例：演示事务（银行转账）

本示例以 account 表初始数据（兰智 1000、数加 1000）为起点，分步展示事务执行过程中每个阶段的数据状态，帮助初学者直观理解事务的 原子性 和 一致性。

— 开启事务
SET autocommit = 0;
START TRANSACTION;

步骤1：事务开启前，account 表数据：

此时两条记录的余额均为 1000，总金额 = 1000 + 1000 = 2000。这就是事务开始前的一致状态。

— 步骤2：兰智转出500
UPDATE account SET balance = 500 WHERE username = '兰智';

步骤2：兰智余额减500后（事务内可见，事务外不可见）：

此时如果在另一个会话中查询（默认 REPEATABLE READ 隔离级别），兰智的余额仍然是 1000，因为事务尚未提交。这就体现了事务的 隔离性（I）：未提交的修改对其他事务不可见。

— 步骤3：数加收到500
UPDATE account SET balance = 1500 WHERE username = '数加';

步骤3：数加余额加500后（事务内可见）：

此时事务内两条 UPDATE 都已执行，但尚未提交。总金额 = 500 + 1500 = 2000，一致性 得到保证。如果此时发生异常（如断电），事务会自动回滚，数据恢复到步骤1的状态。

— 步骤4：提交事务
COMMIT;

步骤4：COMMIT后（永久生效）：

提交后，修改永久生效，其他会话也能看到新数据。体现了 ACID 的 持久性（D） 和 一致性（C）。

对比：如果执行 ROLLBACK 而非 COMMIT：

ROLLBACK 会撤销事务内的所有操作，数据恢复到事务开启前的状态。体现了 ACID 的 原子性（A）：要么全做，要么全不做。

5.4 SAVEPOINT 保存点与部分回滚

保存点（SAVEPOINT）允许在事务中设置一个"标记点"，后续可以回滚到该标记点，而非回滚整个事务，从而实现 部分回滚。

前提：account 表当前数据为兰智 500、数加 1500（承接 5.3 银行转账 COMMIT 后的状态）。

SET autocommit = 0;
START TRANSACTION;

初始状态：

— 步骤1：删除id=1
DELETE FROM account WHERE id = 1;

步骤1：删除兰智后：

— 步骤2：设置保存点a
SAVEPOINT a;

步骤2：设置保存点a（数据不变，只是标记当前位置）：

保存点 a 记录了"id=1 已删除，id=2 还存在"这个状态。

— 步骤3：继续删除id=2
DELETE FROM account WHERE id = 2;

步骤3：删除数加后（表已空）：

— 步骤4：回滚到保存点a
ROLLBACK TO a;

步骤4：回滚到保存点a后（撤销步骤3，恢复步骤2的状态）：

ROLLBACK TO a 撤销了保存点 a 之后的所有操作（删除 id=2），但保留保存点之前的操作（删除 id=1）。这就是"部分回滚"。

步骤5：最终决定（二选一）：

— 选择一：提交
COMMIT;

COMMIT后最终状态：

提交后，删除 id=1 的操作永久生效，account 表只剩数加。

— 选择二：整体回滚
ROLLBACK;

ROLLBACK后最终状态：

整体回滚会撤销事务内的所有操作，包括保存点之前的删除 id=1。数据完全恢复到事务开启前的状态。

总结对比表：

5.5 事务隔离级别

当多个事务并发执行时，如果不加隔离，会出现以下三种数据读取问题：

脏读、不可重复读、幻读的含义

• 脏读（Dirty Read）：一个事务读取到了另一个事务 尚未提交 的修改数据。由于该数据可能被回滚，因此是"脏"的、不可靠的。

• 不可重复读（Non-repeatable Read）：在同一个事务内，两次读取同一行数据，结果不一致（因为别的事务在这两次读取之间提交了 UPDATE 修改）。强调的是 修改 导致的问题。

• 幻读（Phantom Read）：在同一个事务内，两次执行 相同的查询（通常是范围查询），结果集的行数不一致（因为别的事务提交了 INSERT/DELETE）。强调的是 新增/删除 导致的问题。

四种隔离级别

MySQL 提供四种隔离级别，隔离性从低到高、并发性能从高到低：

隔离级别对比表格

说明：在 MySQL 的 InnoDB 引擎中，REPEATABLE READ 级别通过 MVCC + 间隙锁（Next-Key Lock） 在很大程度上 避免了幻读（表中标注为 会出现*，表示理论上会出现但 InnoDB 实际已解决）。

查看隔离级别

— 查看当前 MySQL 隔离级别（默认 REPEATABLE-READ）
SELECT @@transaction_isolation;

运行结果：

设置隔离级别

— 精简版语法
SET SESSION | GLOBAL TRANSACTION ISOLATION LEVEL 隔离级别;

— 设置当前会话隔离级别为"读未提交"
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

— 设置为"读提交"
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

— 设置为"可重复读"（MySQL 默认）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

— 设置为"串行化"
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

运行结果：

Query OK, 0 rows affected (0.00 sec) — READ UNCOMMITTED
Query OK, 0 rows affected (0.00 sec) — READ COMMITTED
Query OK, 0 rows affected (0.00 sec) — REPEATABLE READ
Query OK, 0 rows affected (0.00 sec) — SERIALIZABLE

示例：不同隔离级别下的并发操作演示

以下示例通过两个 MySQL 会话（Session A 和 Session B）模拟并发场景，直观展示不同隔离级别下的脏读、不可重复读等问题。account 表初始数据：兰智 1000、数加 1000。

示例1：脏读演示（READ UNCOMMITTED）

场景：两个 MySQL 会话（Session A 和 Session B），account 表初始数据：兰智 1000、数加 1000。

准备工作（两个会话都执行）：

— 两个会话都先设置隔离级别为READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

Session A（修改数据但不提交）：

SET autocommit = 0;
START TRANSACTION;
— 兰智余额改为800（但尚未提交）
UPDATE account SET balance = 800 WHERE username = '兰智';
— 此时不要COMMIT

Session B（读取数据）：

SELECT * FROM account WHERE username = '兰智';

Session B 运行结果：

⚠ 脏读发生！ Session B 读到了 Session A 尚未提交的数据（800）。如果 Session A 接下来执行 ROLLBACK，这个 800 就是不存在的"脏"数据。

Session A 回滚：

ROLLBACK; — 兰智余额恢复为1000

Session B 再次读取：

SELECT * FROM account WHERE username = '兰智';

Session B 再次查询结果：

Session B 两次读取结果不一致（800 → 1000），因为它第一次读到的是未提交的"脏"数据。

示例2：不可重复读演示（READ COMMITTED）

场景：将两个会话的隔离级别改为 READ COMMITTED，演示同一事务内两次读取结果不同。

准备工作：

— 两个会话都设置隔离级别为READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Session A（开启事务，第一次读取）：

SET autocommit = 0;
START TRANSACTION;
SELECT * FROM account WHERE username = '兰智';

Session A 第一次读取结果：

Session B（修改兰智余额并提交）：

UPDATE account SET balance = 700 WHERE username = '兰智';
COMMIT; — Session B 提交了修改

Session A（第二次读取，同一事务内）：

SELECT * FROM account WHERE username = '兰智';

Session A 第二次读取结果：

⚠ 不可重复读发生！ Session A 在同一个事务内两次读取兰智的余额，结果不一致（1000 → 700），因为 Session B 在两次读取之间提交了修改。READ COMMITTED 避免了脏读（只能读到已提交的数据），但无法避免不可重复读。

Session A 提交：

COMMIT;

示例3：REPEATABLE READ 避免不可重复读（MySQL默认级别）

场景：将隔离级别改为 REPEATABLE READ，演示同一事务内两次读取结果一致。

准备工作：

— 两个会话都设置隔离级别为REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
— 重置数据
UPDATE account SET balance = 1000 WHERE username = '兰智';
UPDATE account SET balance = 1000 WHERE username = '数加';
COMMIT;

Session A（开启事务，第一次读取）：

SET autocommit = 0;
START TRANSACTION;
SELECT * FROM account;

Session A 第一次读取结果：

Session B（修改兰智余额并提交）：

UPDATE account SET balance = 600 WHERE username = '兰智';
COMMIT;

Session A（第二次读取，同一事务内）：

SELECT * FROM account;

Session A 第二次读取结果：

REPEATABLE READ 避免了不可重复读！ Session A 两次读取结果完全一致（兰智都是 1000），即使 Session B 已经提交了修改。这是因为 REPEATABLE READ 在事务开始时创建一个数据 快照，整个事务期间都使用这个快照读取数据。

Session A 提交后再次读取：

COMMIT;
SELECT * FROM account;

Session A 提交后读取结果：

事务提交后，Session A 才能看到 Session B 的修改（兰智 600）。

示例4：SERIALIZABLE 串行化

场景：最高隔离级别，事务串行执行，完全避免并发问题但性能最低。

准备工作：

— 两个会话都设置隔离级别为SERIALIZABLE
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
— 重置数据
UPDATE account SET balance = 1000 WHERE username = '兰智';
UPDATE account SET balance = 1000 WHERE username = '数加';
COMMIT;

Session A（开启事务，查询数据）：

SET autocommit = 0;
START TRANSACTION;
SELECT * FROM account;

Session A 查询结果：

Session B（尝试修改数据）：

SET autocommit = 0;
START TRANSACTION;
UPDATE account SET balance = 500 WHERE username = '兰智';
— 此时Session B会被阻塞（等待Session A释放锁）

SERIALIZABLE 级别下，Session B 的 UPDATE 会被 阻塞，直到 Session A 执行 COMMIT 或 ROLLBACK 释放锁。这是通过加锁实现的，完全避免了并发问题，但代价是性能最低。

Session A 提交后：

COMMIT;
— Session A提交后，Session B的UPDATE才能继续执行

Session B 执行结果（Session A提交后）：

Affected rows: 1 — 兰智余额改为500

— Session B 提交
COMMIT;

最终account表：

隔离级别总结对比

*InnoDB 通过 MVCC + 间隙锁在很大程度上避免了幻读。

选型建议：大多数业务场景使用 MySQL 默认的 REPEATABLE READ 即可。只有在对数据一致性要求极高（如金融核心系统）时才考虑 SERIALIZABLE，但要注意性能代价。

总结

本文系统覆盖了 MySQL 三大语言体系：

• DML（数据操作语言）：INSERT 插入（两种语法格式、多行插入）、UPDATE 修改（单表/多表）、DELETE 删除（单表/多表）、TRUNCATE 截断表，以及 DELETE 与 TRUNCATE 的五大区别。

• DDL（数据定义语言）：库的管理（创建/修改/删除）、表的管理（创建/查看/修改/删除/复制）、常见数据类型总结。

• 约束条件：六大约束分类、列级约束 vs 表级约束、主键 vs 唯一对比、外键设置注意事项、学生表+专业表实战、修改表时添加/删除约束、标识列 AUTO_INCREMENT。

• 视图：创建、修改、删除虚拟表。

• DCL（事务控制语言）：事务概念（银行转账案例）、ACID 四大特性、显式事务创建步骤、SAVEPOINT 保存点与部分回滚、四种事务隔离级别及脏读/不可重复读/幻读详解。

其中 约束 和 事务 是面试重点考察内容，建议重点掌握。事务部分理解 ACID 特性和隔离级别对应的并发问题，是面试和实际开发中都需要牢固掌握的基础知识。

写作说明：本文基于个人 SQL 学习笔记整理，DDL 官方完整语法过长，文中仅保留精简总结版。示例均经过实际运行验证，如有疏漏欢迎指正。