MySQL_DML_DDL_约束与事务详解

原创 已于 2026-09-04 19:40:10 修改 · 297 阅读 · 3 ·
本内容遵循CC 4.0 BY-SA版权协议
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
GEO检测
收录于
MySQL

当前文章被收录于：

MySQL
3 篇文章 0 人学习
订阅专栏 查看详情
于 2026-09-04 19:39:18 首次发布

本文还有配套的精品资源，点击获取 menu-r.4af5f7ec.gif

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

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

目录

前言

在 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 表（女神信息）

idnamesexborndatephoneboyfriend_id
1迪丽热巴女1992-06-03170000000001
2赵丽颖女1987-10-16170000000012
3杨幂女1986-08-12170000000023
4刘亦菲女1987-08-2517000000003NULL
5Anglelababy女1989-02-28170000000044
6古力娜扎女1992-05-0217000000005NULL
7景甜女1988-07-21170000000065

boys 表（男神信息）

idboyNameuserCP
1鹿晗800
2冯绍峰700
3刘恺威600
4黄晓明900
5张继科850

account 表（账户信息）

idusernamebalance
1兰智1000
2数加1000

stuinfo 表（学生信息）

idstuNamegenderseatagemajorid
1张三男1201
2李四女2211
3王五男3192
4赵六女4222
5钱七男5203
6孙八女6211
7周九男7193

major 表（专业信息）

idmajorName
1计算机科学
2软件工程
3数据科学

book 表（图书信息）

idbNamepriceauthorIdpubDate
1Java核心卷一89.0012020-01-15 00:00:00
2MySQL必知必会59.0022019-06-20 00:00:00
3Python编程79.0022021-03-10 00:00:00
4深入理解JVM108.0032018-09-01 00:00:00

author 表（作者信息）

idau_namenation
1鲁迅中国
2莫言中国
3余华中国
4村上春树日本

一、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, ...;
两种方式对比
对比项方式一（VALUES）方式二（SET）
支持多行插入支持不支持
支持子查询支持不支持
写法灵活度高（可省略列名、可调换顺序）低（必须逐列赋值）

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

插入注意事项
  1. 值的类型要与列的类型一致或兼容，否则插入失败。
  2. 不可以为 NULL 的列必须插入值；可以为 NULL 的列，可以用 NULL 表示不插入值，或直接省略该列。
  3. 列数与值的个数必须一致，否则报错。
  4. 列的顺序可以调换，但值要与之对应。
  5. 可以省略列名，此时默认按表中列的顺序插入所有列。
示例：基础插入
-- 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 的错误示例已注释跳过。执行后表数据如下：

idnamesexborndatephoneboyfriend_id
1迪丽热巴女1992-06-03170000000001
2迪丽热巴NULLNULLNULL1
3赵丽颖女NULLNULL2
4赵丽颖2女NULLNULL2
6杨幂女1986-08-12180000000003
示例：方式二（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。执行后表数据如下：

idnamesexborndatephoneboyfriend_id
1迪丽热巴女1992-06-03170000000001
2迪丽热巴NULLNULLNULL1
3赵丽颖女NULLNULL2
4赵丽颖2女NULLNULL2
6杨幂女1986-08-12180000000003
7刘亦菲女NULLNULLNULL
示例：多行插入（仅方式一支持）
-- 方式一支持一次性插入多行数据
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 五大区别对比表
对比项DELETETRUNCATE
是否可加 WHERE 条件可以加条件，按条件删除不可以加条件，直接清空全表
删除效率较低（逐行删除）更高（直接清空数据页）
自增长列行为删除后再插入，自增长列从断点值继续删除后再插入，自增长列从 1 重新开始
返回值有返回值（返回受影响行数）没有返回值
是否可回滚（事务）可以回滚不可以回滚
示例：对比演示
-- 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 行）：

idnamesexborndatephoneboyfriend_id
3赵丽颖女NULLNULL2
4赵丽颖2女NULLNULL2
7刘亦菲女NULLNULL1

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
idnamesexborndatephoneboyfriend_id
1杨幂2女1986-08-12150000000003
2杨幂3女1986-08-12160000000003
3杨幂4女1986-08-12170000000003

注意：当前 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 列名 [列的类型 约束];
操作语法说明
添加列ALTER TABLE 表名 ADD COLUMN 列名 类型 [约束];新增一列
删除列ALTER TABLE 表名 DROP COLUMN 列名;删除一列
修改列类型/约束ALTER TABLE 表名 MODIFY COLUMN 列名 新类型 [新约束];只改类型或约束
修改列名+类型ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 新类型 [新约束];列名和类型都能改
修改表名ALTER TABLE 表名 RENAME TO 新表名;重命名表
示例：创建表 + 修改表
-- 创建数据库并进入
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：

FieldTypeNullKeyDefaultExtra
idintYES
NULL
bNamevarchar(255)YES
NULL
pricedoubleYES
NULL
authorIdintYES
NULL
publishDatedatetimeYES
NULL

DESC author：

FieldTypeNullKeyDefaultExtra
idintYES
NULL
au_namevarchar(20)YES
NULL
nationvarchar(10)YES
NULL
-- 修改 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：

FieldTypeNullKeyDefaultExtra
idintYES
NULL
bNamevarchar(255)YES
NULL
pricedoubleYES
NULL
authorIdintYES
NULL
pubDatetimestampYES
NULL

修改后 DESC book_author（原 author 表）：

FieldTypeNullKeyDefaultExtra
idintYES
NULL
au_namevarchar(20)YES
NULL
nationvarchar(10)YES
NULL
删除表
-- 精简版语法
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 表数据：

idau_namenation
1鲁迅中国
2莫言中国
3余华中国
4村上春树日本
复制场景语法说明
复制结构 + 数据CREATE TABLE copy1 SELECT * FROM author;复制全部结构和全部数据
仅复制结构CREATE TABLE copy2 LIKE author;只复制表结构，不复制数据
复制部分数据CREATE TABLE copy3 SELECT id, au_name FROM author WHERE nation = '中国';复制部分列 + 满足条件的数据
复制某些字段（无数据）CREATE TABLE copy4 SELECT id, au_name FROM author WHERE 0;WHERE 0 永假，只复制列结构
复制某些字段（含全部数据）CREATE TABLE copy5 SELECT id, au_name FROM author WHERE 1;WHERE 1 永真，复制列 + 全部数据
-- 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（全部结构 + 全部数据）：

idau_namenation
1鲁迅中国
2莫言中国
3余华中国
4村上春树日本

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

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

idau_name
1鲁迅
2莫言
3余华

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

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

idau_name
1鲁迅
2莫言
3余华
4村上春树

2.3 常见数据类型

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

数值型
类型说明典型用途
int标准整数编号、计数
bigint大整数大范围编号
decimal定点数金额、精度要求高的场景
float单精度浮点一般小数
double双精度浮点精度较高的小数
日期型
类型说明格式示例
date日期1992-06-03
time时间12:30:00
datetime日期+时间1992-06-03 12:30:00
timestamp时间戳20260902120000
year年份2026
字符型
类型说明范围/用途
char定长字符0~255，适合固定长度
varchar变长字符0~65535，最常用（经验值：varchar(255)）
text长文本文章、备注等长文本
blob二进制大对象较长的二进制数据（如图片）

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

三、约束条件

3.1 约束概念

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

3.2 六大约束分类

约束关键字用途举例
非空NOT NULL保证该字段的值不能为空姓名、学号
默认DEFAULT保证该字段有默认值性别默认"男"
主键PRIMARY KEY保证字段值唯一且非空学号、员工编号
唯一UNIQUE保证字段值唯一，可以为空座位号
检查CHECK检查字段值满足条件年龄范围、性别
外键FOREIGN KEY限制两表关系，值必须来自主表关联列学生表的专业编号、员工表的部门编号

3.3 添加约束时机

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

  1. 创建表时添加约束：在 CREATE TABLE 时直接定义约束。
  2. 修改表时添加约束：用 ALTER TABLE 为已存在的表追加约束。

3.4 列级约束 vs 表级约束

对比项列级约束表级约束
语法位置直接写在列定义后所有列定义之后单独写
语法格式列名 类型 约束条件CONSTRAINT 约束名 约束类型(字段)
支持的约束默认、非空、主键、唯一、检查（外键语法支持但无效果）主键、唯一、检查、外键（不支持非空、默认）
是否可命名约束一般不可可以自定义约束名

列级约束语法格式：

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

表级约束语法格式：

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

3.5 主键 vs 唯一对比

对比项主键（PRIMARY KEY）唯一（UNIQUE）
保证唯一性可以可以
是否允许为空不能可以
一个表中有几个至多一个可以有多个
是否允许组合（多列组合）允许，但不推荐允许，但不推荐

3.6 外键设置注意事项

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

  1. 在从表中设置外键关系（从表引用主表）。
  2. 从表的外键列类型与主表的关联列类型要求一致或兼容，名称无要求。
  3. 主表的关联列必须是一个 key（一般是主键或唯一）。
  4. 插入数据时先主后从，删除数据时先从后主。

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（列级约束版）：

FieldTypeNullKeyDefaultExtra
idintNOPRINULL
stuNamevarchar(20)NO
NULL
genderchar(1)YES
NULL
seatintYESUNINULL
ageintYES
18
majorIdintYES
NULL

SHOW INDEX FROM stuinfo：

TableNon_uniqueKey_nameSeq_in_indexColumn_name
stuinfo0PRIMARY1id
stuinfo0seat1seat

注意：列级约束中 外键约束不生效（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（表级约束版）：

FieldTypeNullKeyDefaultExtra
idintNOPRINULL
stuNamevarchar(20)YES
NULL
genderchar(1)YES
NULL
seatintYESUNINULL
ageintYES
NULL
majoridintYESMULNULL

SHOW INDEX FROM stuinfo：

TableNon_uniqueKey_nameSeq_in_indexColumn_name
stuinfo0PRIMARY1id
stuinfo0uq1seat
stuinfo1fk_stuinfo_major1majorid

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

3.8 修改表时添加约束

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

  1. 添加列级约束（用 MODIFY）：
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型 约束条件(新约束);
  1. 添加表级约束（用 ADD）：
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（无约束）：

FieldTypeNullKeyDefaultExtra
idintYES
NULL
stuNamevarchar(20)YES
NULL
genderchar(1)YES
NULL
seatintYES
NULL
ageintYES
NULL
majoridintYES
NULL
-- 添加非空约束（列级）
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（添加约束后）：

FieldTypeNullKeyDefaultExtra
idintNOPRINULL
stuNamevarchar(20)NO
NULL
genderchar(1)YES
NULL
seatintYESUNINULL
ageintYES
18
majoridintYESMULNULL

SHOW INDEX FROM stuinfo：

TableNon_uniqueKey_nameSeq_in_indexColumn_name
stuinfo0PRIMARY1id
stuinfo0seat1seat
stuinfo1stuinfo_ibfk_11majorid

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（删除约束后）：

FieldTypeNullKeyDefaultExtra
idintYES
NULL
stuNamevarchar(20)YES
NULL
genderchar(1)YES
NULL
seatintYES
NULL
ageintYES
NULL
majoridintYES
NULL

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

查询外键约束名称

如果不确定外键约束的名称，可以通过 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;

运行结果：

CONSTRAINT_NAMECOLUMN_NAMEREFERENCED_TABLE_NAMEREFERENCED_COLUMN_NAME
stuinfo_ibfk_1majoridmajorid

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

3.10 标识列（AUTO_INCREMENT）

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

标识列的特点
  1. 标识列不一定必须和主键搭配，但要求它是一个 key（主键或唯一）。
  2. 一个表中 至多一个 标识列。
  3. 标识列的类型 只能是数值类型。
  4. 标识列可以通过 SET auto_increment_increment = xxx; 设置 步长（注意：只允许设置步长，不允许设置偏移量）。
示例：创建并使用自增长列
-- 查看默认步长
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%’：

Variable_nameValue
auto_increment_increment1
auto_increment_offset1
Query OK, 0 rows affected (0.01 sec)   -- CREATE TABLE
Affected rows: 1                       -- INSERT

SELECT * FROM tab_stu：

idname
1张三
示例：设置步长
-- 设置步长为 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：

idname
1张三
4张三1
7张三2
10张三3
13张三4
16张三5

已有 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：

stuNamemajorName
张三计算机科学

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：

特性全称含义
原子性（A）Atomicity一个事务不可再分割，要么都执行，要么都不执行
一致性（C）Consistency一个事务执行会使数据从一个一致状态切换到另一个一致状态
隔离性（I）Isolation一个事务的执行不受其他事务的干扰
持久性（D）Durability一个事务一旦提交，则会永久改变数据库的数据

理解要点：

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

  • 一致性 强调"数据正确"，转账前后两人总金额应保持 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（部分）：

EngineSupportComment
InnoDBDEFAULTSupports transactions, row-level locking, and foreign keys
MyISAMYESMyISAM storage engine
MemoryYESHash based, stored in memory, useful for temporary tables

SHOW VARIABLES LIKE ‘autocommit’：

Variable_nameValue
autocommitON
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：

idusernamebalance
1兰智1000
2数加1000
示例：演示事务（银行转账）

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

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

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

idusernamebalance
1兰智1000
2数加1000

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

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

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

idusernamebalance
1兰智500
2数加1000

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

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

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

idusernamebalance
1兰智500
2数加1500

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

-- 步骤4：提交事务
COMMIT;

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

idusernamebalance
1兰智500
2数加1500

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

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

阶段兰智余额数加余额说明
事务开启前10001000初始状态
UPDATE 兰智后5001000事务内可见
UPDATE 数加后5001500事务内可见
ROLLBACK后10001000全部撤销，恢复初始

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

5.4 SAVEPOINT 保存点与部分回滚

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

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

SET autocommit = 0;
START TRANSACTION;

初始状态：

idusernamebalance
1兰智500
2数加1500
-- 步骤1：删除id=1
DELETE FROM account WHERE id = 1;

步骤1：删除兰智后：

idusernamebalance
2数加1500
-- 步骤2：设置保存点a
SAVEPOINT a;

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

idusernamebalance
2数加1500

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

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

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

idusernamebalance
（空）

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

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

idusernamebalance
2数加1500

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

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

-- 选择一：提交
COMMIT;

COMMIT后最终状态：

idusernamebalance
2数加1500

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

-- 选择二：整体回滚
ROLLBACK;

ROLLBACK后最终状态：

idusernamebalance
1兰智500
2数加1500

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

总结对比表：

操作阶段account表记录说明
事务开启前兰智500, 数加1500初始状态
DELETE id=1后数加1500删除兰智
SAVEPOINT a数加1500标记当前状态
DELETE id=2后（空）全部删除
ROLLBACK TO a后数加1500撤销删除id=2，保留删除id=1
COMMIT后数加1500永久生效，只有数加
ROLLBACK后兰智500, 数加1500全部撤销，恢复初始

5.5 事务隔离级别

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

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

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

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

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

四种隔离级别

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

隔离级别说明
READ UNCOMMITTED（读未提交）最低隔离级别，允许读取未提交数据
READ COMMITTED（读提交）只能读取已提交数据，Oracle 默认级别
REPEATABLE READ（可重复读）MySQL 默认 级别，事务内多次读结果一致
SERIALIZABLE（串行化）最高隔离级别，强制事务串行执行，性能最低
隔离级别对比表格
隔离级别脏读不可重复读幻读实现方式
READ UNCOMMITTED会出现会出现会出现读未提交数据
READ COMMITTED避免会出现会出现每次读生成新快照
REPEATABLE READ（MySQL 默认）避免避免会出现*事务内使用同一快照
SERIALIZABLE避免避免避免强制加锁串行执行

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

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

运行结果：

@@transaction_isolation
REPEATABLE-READ
设置隔离级别
-- 精简版语法
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 运行结果：

idusernamebalance
1兰智800

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

Session A 回滚：

ROLLBACK;  -- 兰智余额恢复为1000

Session B 再次读取：

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

Session B 再次查询结果：

idusernamebalance
1兰智1000

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 第一次读取结果：

idusernamebalance
1兰智1000

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

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

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

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

Session A 第二次读取结果：

idusernamebalance
1兰智700

⚠ 不可重复读发生！ 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 第一次读取结果：

idusernamebalance
1兰智1000
2数加1000

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

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

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

SELECT * FROM account;

Session A 第二次读取结果：

idusernamebalance
1兰智1000
2数加1000

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

Session A 提交后再次读取：

COMMIT;
SELECT * FROM account;

Session A 提交后读取结果：

idusernamebalance
1兰智600
2数加1000

事务提交后，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 查询结果：

idusernamebalance
1兰智1000
2数加1000

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表：

idusernamebalance
1兰智500
2数加1000
隔离级别总结对比
隔离级别脏读不可重复读幻读并发性能适用场景
READ UNCOMMITTED⚠会出现⚠会出现⚠会出现最高极少使用
READ COMMITTED✅避免⚠会出现⚠会出现较高Oracle默认，对一致性要求不高
REPEATABLE READ✅避免✅避免✅避免*中等MySQL默认，大多数场景
SERIALIZABLE✅避免✅避免✅避免最低对一致性要求极高

*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 官方完整语法过长，文中仅保留精简总结版。示例均经过实际运行验证，如有疏漏欢迎指正。

标签
#mysql #数据库
MySQL中的DDL操作，MySQL中DML操作，MySQL查询数据，SQL函数，MySQL中的索引，MySQL事务，MySQL的用户管理，MySQL分页查询
weixin_42979760的博客
06-27 5033
MySQL中的DDL操作，MySQL中DML操作，MySQL查询数据，SQL函数，MySQL中的索引，MySQL事务，MySQL的用户管理，MySQL分页查询
CMOS中的 latch-up 闩锁效应、添加tap解决latch-up、使用combained area绘制TAP TAP的作用 IC后端版图【VLSI】
Jie_MSD的博客
10-28 1万+
本文详细阐述了闩锁效应latch-up问题、如何通过添加Tap来解决。详细讲述了如何通过combained area来绘制TAP
mysql数据库交流_MySQL数据库学习回顾：
weixin_29467373的博客
01-26 137
本帖最后由 时光之旅 于 2019-4-30 00:11 编辑MySQL数据库学习小结：1. 安装MySql和客户端SQLyog2. 了解SQL的分类：- DDL：数据定义语言。定义database、table等等- DML：数据操作语言。数据的增、删、改- DQL：数据查询语言。查询数据- DCL：数据控制语言。DBA使用的，管理MySql的用户、权限等等- TCL：事务管理语言。进行事务管理的...
高通GAIA V3命令参考手册的研读学习（15）：自定制命令的详细工作描述
qq_37191547的博客
02-05 376
里的示例一样，手机APP发出这个GetManuFacturer的命令发0x0000,那么耳机回复：0x8000, 同时在回复的playload部分，用最多15个字节的ACSII码来表达制造商信息（因此最多15个字母）。那么这个APP在通过GAIA协议控制这个耳机时，不仅可以通过前面讲过的vendor ID来识别，也可以更进一步通过这个命令来识别这个耳机，到底是不是本公司的。通过这个可变长度的playload，真的是可以传达极多的事先约定的信息，相当的灵活，真是不错！这个命令用来得到设备的制造商信息。
【MySQL】——MySQL 学习笔记一
Japathy
10-29 1262
[注：本文为转载文章，原文链接：MySQL 学习笔记 一] MySQL 学习笔记 一 一、数据库简单介绍 1、 按照数据库的发展时间顺序，主要出现了以下类型数据库系统： Ø 网状型数据库 Ø 层次型数据库 Ø 关系型数据库 Ø 面向对象数据库 上面4中数据库系统中，关系型数据库使用最为广泛。面向对象数据库则是由面向对象语言催生的新型数据库，目前的一些数据库系统，如：SQL Serv...
MySQL核心基础：SQL分类、函数约束、多表查询与事务隔离级别详解
最新发布
weixin_30231327的博客
08-05 273
SQL作为关系型数据库的核心操作语言，是数据存储与检索的基础。其按照功能可分为DDL（数据定义语言）、DML（数据操作语言）、DQL（数据查询语言）和DCL（数据控制语言），构成了对数据库结构、数据本身及访问权限的完整控制体系。理解并熟练运用这些分类，是进行高效、安全数据操作的前提。在此基础上，数据库函数与约束机制进一步保障了数据的规整性与业务规则的强制性，例如通过唯一约束防止数据重复，利用外键约束维护表间引用完整性（尽管在分布式架构中需谨慎使用）。多表查询，尤其是JOIN操作，则是处理复杂业务关系、从分散
SQL核心技能全景图：DDL数据定义、DML安全操作、DQL高级查询、多表JOIN与窗口函数实战
专注AI与大模型技术，深耕NLP、RAG、Agent开发，分享Python机器学习与深度学习实战经验，助力技术成长。
05-26 923
SQL是数据时代的通用语言，但你真的掌握它了吗？本文将带你进行一次SQL的深度探险。从基础的DDL建库建表规范，到DML数据操作的事务陷阱；从DQL单表查询的聚合分组，到多表关联的逻辑迷宫；再到危如累卵的SQL注入攻击与防御，以及数据分析利器窗口函数的高级玩法。全文结合Python代码实战与MySQL高级语法，为你构建一套完整的SQL知识体系。建议收藏，反复研读！
MySQL关键字分类与高级应用实战指南
weixin_32576389的博客
08-04 311
SQL关键字是关系型数据库操作的基础构建块，MySQL作为主流数据库管理系统，其关键字系统涵盖了数据定义、操作、事务控制等核心功能。从技术原理看，关键字通过语法解析器转化为执行计划，直接影响查询性能和数据处理效率。在工程实践中，合理使用DDL、DML等关键字能显著提升开发效率，特别是在高并发事务处理和大数据分析场景下。本文重点解析MySQL 8.0的700+保留字体系，包含窗口函数、JSON处理等新特性应用，并针对常见的JOIN优化、索引设计等性能问题提供解决方案。通过实际案例演示如何避免关键字冲突，以及云
16k字--快速上手MySQL
博客 | 复习知识点 + 练习 + 最终的领悟 | 全力以赴
01-14 848
本文系统介绍了MySQL数据库的基础知识和核心操作，主要包括以下内容：1. 数据库基础概念、SQL分类和存储引擎；2. 数据库的创建、查询、修改、删除及备份还原操作；3. 表结构的DDL操作和数据类型详解；4. 表的约束机制，包括主键、外键、唯一键等；5. 数据的DML操作（增删改查）及常用内置函数；6. 重点讲解了索引结构和事务特性（ACID）；7. 视图、用户权限管理及C/C++访问MySQL的接口方法。全文涵盖了MySQL从基础到高级的核心知识点，特别对索引、事务等关键概念进行了深入剖析，并提供了丰富
MySQL 核心知识点精简总结
SELdax的博客
06-25 163
后不输密码可隐藏输入。
MySQL 全套 SQL 语句系统精讲｜语法规范、性能避坑、企业级实战与学习复盘
2603_96488490的博客
07-03 300
MySQL数据库技术学习总结 摘要：本文系统总结了MySQL数据库技术的核心知识点，涵盖SQL语言体系、执行原理、语法规范及实战应用。通过Navicat16+MySQL8.0环境下的实操经验，重点分析了常见错误（如字符集乱码、分组报错等）的解决方案，并提供了完整的代码示例。内容包含四大语言分类（DDL、DML、DCL、TCL）、SELECT执行顺序、表设计规范、查询优化、事务控制等关键内容，同时分享了个人学习中的踩坑经验和遗留问题。文章既是对课程知识的系统归纳，也为MySQL学习者提供了实用参考。
【MQSQL】DDL&DML
https://blog.csdn.net/m0_74834215
12-15 2096
学习笔记记录
MySQL基础（DDL、DML、DQL）
热门推荐
来了点个赞再走...
05-16 1万+
我们开发应用程序的时候，程序中的所有数据，最后都需要保存到专业软件中。这些专业的保存数据的软件我们称为数据库。我们学习数据库，并不是学习如何去开发一个数据库软件，我们学习的是如何使用数据库以及数据库中的数据记录的操作。而数据库软件是由第三方公司研发。 SQL是用来存取关系数据库的语言，具有定义、操纵、控制和查询关系型数据库的四方面功能（DDL、DML、DQL、DCL）。所以针对四方面功能，我们学习对数据库的增、删、改、查。
MySQL基础篇 | union、limit、DDL、DML、约束
m0_61933976的博客
09-12 2293
MySQL基础篇【第五篇】| union、limit、DDL、DML、约束
【MySQL】基础部分——DDL,DML,DQL,DCL,函数,约束,多表查询,事务
weixin_45962681的博客
09-15 1300
users_details表中的user_id字段是外键，引用了users_basic表中的id字段，从而建立一对一的关系，每个用户在 users_basic 表和 users_details 表中只能有一条对应记录。emp表中有id，name，managerid，其中managerid的id指的是领导的id，领导id对应的name就是领导的name。所以不要把这个表看做一个表，同一个表看做两个表，一个是员工表，一个是领导表，员工表的managerid对应的就是领导表的主键id。一对一：用户对用户。
MySQL——DDL、DML、DQL、DCL语法总结
Bonner007的博客
04-22 1万+
MySQL语言总结归纳
MySQL，DDL，DML，MySQL约束
2301_80460489的博客
12-15 918
注意：delete和truncate原理不同，delete只删除内容，而truncate类似于drop table，可以理解为是将整个表删然后再创建该表；表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户表有些列的值（手机号)不能为空，有些列的值（身份证号)不能重复。insert into 表(列名1,列名2,列名3...) values (值1 ,值2,值3...);1，MySQL主键约束是一个列或者多个列的组合，其值能唯一地标识表中的每一行,方便在RDBMS中尽快的找到某一行。
MySQL基础：SQL分类DDL、DML、DQL、DCL；函数、约束、多表查询、事务、并发事务四大问题、事务隔离级别——脏写、脏读、不可重复读、幻读
qq_44700578的博客
05-16 2482
MySQL基础：SQL分类DDL、DML、DQL、DCL；函数、约束、多表查询、事务、并发事务四大问题、事务隔离级别——脏写、脏读、不可重复读、幻读
MySQL中DDL和DML
weixin_67707901的博客
07-27 5265
因为VARCHAR类型能够根据字符串的实际长度来动态改变所占字节的大小，所以在不能明确该字段具体需要多少字符时推荐使用VARCHAR类型，这样可以大大地节约磁盘空间、提高存储效率。​MySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例如，INT(4))。(2)列级约束包含在列定义中，直接跟在该列的其它定义之后，用空格分隔必指定列名；​可以对表中的数据做一些限制,从而保证数据的正确性,有效性,和完整性。​CHAR和VARCHAR表示的是字符的个数，而不是字节的个数。......
MySQL基础总结：DDL、DML和DQL
行行失故路
12-09 4658
MySQL基础总结，走过路过不要错过，错过了再重新进来！！！
MySQL数据库管理、DDL、DQL、DML、DCL等总结
m0_72892640的博客
10-21 1446
MySQL管理与基本操作指南 摘要：本文介绍了MySQL服务器在Windows和Linux系统下的启动、关闭方法，以及数据库和表的基本操作（DDL）。主要内容包括： 服务器管理：Windows通过services.msc或net命令，Linux使用systemctl/service命令管理MySQL服务 数据库操作：CREATE/SHOW/USE DATABASE等命令的使用 表操作：CREATE/ALTER/DESC TABLE等命令，包括创建表结构、修改表字段、添加约束等 数据类型和约束：详细说明了常用
c3750-ipservicesk9-mz.122-55.SE.bin
亲测可用的思科固件,欢迎大家下载使用
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值