数据定义语言

导学

SQL 分为四大类：DQL（查询）、DML（操纵）、DDL（定义）、DCL（控制）。本节聚焦 DDL——用于定义和修改数据库结构的语句，包括 CREATE、ALTER、DROP、TRUNCATE。

定义

DDL（Data Definition Language）：用于定义数据库对象（数据库、表、索引、视图等）结构的 SQL 语句。DDL 操作通常是隐式提交的，一旦执行不可回滚。

CREATE

创建数据库

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

-- 查看创建结果
SHOW DATABASES LIKE 'school';

结果：

Database
school

解读：IF NOT EXISTS 防止数据库已存在时报错。CHARACTER SET utf8mb4 支持完整的 Unicode 字符（包括 emoji）。

创建表

当前数据状态：数据库 school 已存在，但还没有任何表。

操作语句：

USE school;

-- 创建员工表
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '员工ID',
    emp_name VARCHAR(20) COMMENT '姓名',
    dept VARCHAR(20) COMMENT '部门',
    score DECIMAL(5,2) COMMENT '评分'
) ENGINE=InnoDB COMMENT='员工信息表';

-- 查看表结构
DESC employees;

操作后数据状态：

Field	Type	Null	Key	Default	Extra
emp_id	int(11)	NO	PRI	NULL	auto_increment
emp_name	varchar(20)	YES		NULL
dept	varchar(20)	YES		NULL
score	decimal(5,2)	YES		NULL

结果解读：

PRIMARY KEY 指定主键，AUTO_INCREMENT 让主键自增
ENGINE=InnoDB 指定存储引擎为 InnoDB，支持事务和外键

建表时插入数据

当前数据状态：employees 表已创建，但无任何数据。

操作语句：

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

SELECT * FROM employees;

操作后数据状态：

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

结果解读：emp_id 自动从 1 开始递增。大翔有完整数据，白歌的 score 为 NULL，可用于演示 NULL 处理。

创建索引

当前数据状态：employees 表已有 2 条数据，但目前只有主键索引。

操作语句：

-- 为 dept 列创建普通索引
CREATE INDEX idx_dept ON employees(dept);

-- 查看表的索引信息
SHOW INDEX FROM employees;

操作后数据状态（节选关键列）：

Table	Non_unique	Key_name	Seq_in_index	Column_name
employees	0	PRIMARY	1	emp_id
employees	1	idx_dept	1	dept

结果解读：Non_unique=1 表示 idx_dept 是普通索引，允许重复值。现在按 dept 查询时会使用该索引加速。

创建视图

当前数据状态：employees 表已有 2 条数据。

操作语句：

-- 创建一个只查看技术部员工的视图
CREATE VIEW tech_employees AS
SELECT emp_id, emp_name, dept, score
FROM employees
WHERE dept = '技术部';

-- 查询视图
SELECT * FROM tech_employees;

操作后数据状态：

emp_id	emp_name	dept	score
1	大翔	技术部	100
2	白歌	技术部	NULL

结果解读：视图 tech_employees 不存储实际数据，每次查询都会执行底层的 SELECT 语句。它封装了查询逻辑，让使用者无需关心 WHERE dept = '技术部' 这个条件。

ALTER

添加列

当前数据状态：employees 表现在有 4 个字段。

操作语句：

-- 在 score 列之后添加 remark 列
ALTER TABLE employees ADD remark VARCHAR(100) AFTER score;

DESC employees;

操作后数据状态（新增字段）：

Field	Type	Null	Key	Default	Extra
...	...	...	...	...	...
score	decimal(5,2)	YES		NULL
remark	varchar(100)	YES		NULL

结果解读：AFTER score 将新列放在 score 列之后。如果不指定 AFTER/FIRST，默认添加到最后一列。MySQL 5.7 中，添加列通常是 Online DDL 操作，不阻塞 DML。

修改列类型

当前数据状态：remark 列现在是 VARCHAR(100)。

操作语句：

-- 将 remark 列长度扩展为 200
ALTER TABLE employees MODIFY remark VARCHAR(200);

DESC employees;

操作后数据状态：

Field	Type	Null	Key	Default	Extra
remark	varchar(200)	YES		NULL

结果解读：MODIFY 用于修改列的数据类型和属性，但不能修改列名。将 VARCHAR 从小改大通常是在线操作；但从大改小可能导致数据截断，需谨慎。

修改列名

当前数据状态：remark 列已存在。

操作语句：

-- 将 remark 列改名为 note（MySQL 特有 CHANGE 语法）
ALTER TABLE employees CHANGE remark note VARCHAR(200);

DESC employees;

操作后数据状态：

Field	Type	Null	Key	Default	Extra
note	varchar(200)	YES		NULL

结果解读：CHANGE 语法需要写两次列名（旧名和新名），可以同时修改列名和类型。这是 MySQL 特有的语法。

删除列

当前数据状态：employees 表有 note 列。

操作语句：

-- 删除 note 列
ALTER TABLE employees DROP COLUMN note;

DESC employees;

操作后数据状态：note 列已从表结构中移除，原有数据也被删除。

结果解读：删除列会立即释放该列占用的磁盘空间（InnoDB 会重建表）。此操作在大表上可能耗时较长。

添加主键

假设有一张没有主键的临时表：

当前数据状态：

CREATE TABLE temp_logs (
    log_id INT,
    content VARCHAR(200)
);

INSERT INTO temp_logs VALUES (1, '系统启动'), (2, '用户登录');

log_id	content
1	系统启动
2	用户登录

操作语句：

ALTER TABLE temp_logs ADD PRIMARY KEY (log_id);

操作后数据状态：

DESC temp_logs;

Field	Type	Null	Key	Default	Extra
log_id	int(11)	NO	PRI	NULL
content	varchar(200)	YES		NULL

结果解读：添加主键后，log_id 列被自动设为 NOT NULL，且表会按主键构建聚簇索引。

删除索引

当前数据状态：employees 表有 idx_dept 索引。

操作语句：

ALTER TABLE employees DROP INDEX idx_dept;

SHOW INDEX FROM employees;

操作后数据状态：只剩 PRIMARY 索引，idx_dept 已被删除。

结果解读：删除索引后，按 dept 列的查询将退化为全表扫描，但写入操作会变快。

DROP

删除数据库（极其危险）

-- 先创建一个测试数据库
CREATE DATABASE test_db;

-- 确认存在
SHOW DATABASES LIKE 'test_db';

Database
test_db

-- 删除数据库（极其危险，不可逆）
DROP DATABASE IF EXISTS test_db;

-- 确认已删除
SHOW DATABASES LIKE 'test_db';

结果：查询返回空集，数据库已被彻底删除。

⚠️ 警告：DROP 操作不可逆，没有回收站。生产环境执行前必须确认备份。

删除表

当前数据状态：temp_logs 表存在且有数据。

操作语句：

-- 删除表
DROP TABLE IF EXISTS temp_logs;

-- 确认已删除
SHOW TABLES LIKE 'temp_logs';

结果：表结构和数据全部被删除，返回空集。

删除索引

当前数据状态：重新为 employees 表创建索引：

CREATE INDEX idx_emp_name ON employees(emp_name);
SHOW INDEX FROM employees;

操作语句：

DROP INDEX idx_emp_name ON employees;

操作后数据状态：idx_emp_name 索引被删除，不影响表数据。

TRUNCATE

清空表数据

当前数据状态：employees 表有 2 条数据。

操作语句：

TRUNCATE TABLE employees;

SELECT * FROM employees;

操作后数据状态：返回空集，表中无任何数据，但表结构保留。

DESC employees;

表结构依然存在，且 AUTO_INCREMENT 计数器被重置为 1：

SHOW CREATE TABLE employees;
-- CREATE TABLE `employees` (...) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

分析：

清空表中所有数据，但保留表结构
比 DELETE FROM employees 快得多（TRUNCATE 是 DDL，直接删除数据文件后重建；DELETE 是 DML，逐行删除并记录日志）
不可回滚（即使放在事务中）
自增计数器重置为 1
有外键约束时无法执行

TRUNCATE vs DELETE 对比实验

创建对比表：

CREATE TABLE delete_demo (
    id INT PRIMARY KEY AUTO_INCREMENT,
    val VARCHAR(50)
);

INSERT INTO delete_demo (val) VALUES ('A'), ('B'), ('C');

id	val
1	A
2	B
3	C

DELETE 操作：

DELETE FROM delete_demo;
INSERT INTO delete_demo (val) VALUES ('D');
SELECT * FROM delete_demo;

id	val
4	D

DELETE 后自增 ID 从 4 开始。

TRUNCATE 操作：

TRUNCATE TABLE delete_demo;
INSERT INTO delete_demo (val) VALUES ('E');
SELECT * FROM delete_demo;

id	val
1	E

TRUNCATE 后自增 ID 重置为 1。

DDL 与事务

MySQL 5.7 中，DDL 语句会隐式提交当前事务。这意味着：

当前数据状态：创建测试表

CREATE TABLE trx_test (id INT PRIMARY KEY);

操作语句：

START TRANSACTION;
INSERT INTO trx_test VALUES (1);
INSERT INTO trx_test VALUES (2);

-- 此时事务中有 2 条未提交的 INSERT
SELECT * FROM trx_test;

id
1
2

-- 执行 DDL，隐式提交！
TRUNCATE TABLE delete_demo;  -- 任意 DDL 都会触发隐式提交

ROLLBACK;

-- 检查 trx_test
SELECT * FROM trx_test;

操作后数据状态：

id
1
2

结果解读：尽管执行了 ROLLBACK，但 INSERT 的数据依然存在。因为 TRUNCATE 触发了隐式提交，导致之前的 INSERT 被永久保存。ROLLBACK 只能回滚 TRUNCATE 之后的内容。

常见误区

误区	正解
`TRUNCATE` 可以回滚	不可以。`TRUNCATE` 是 DDL，隐式提交，不受事务控制。
`DROP` 和 `DELETE` 一样	`DROP` 删除整个表（结构+数据）；`DELETE` 只删数据，保留结构。
`ALTER` 随时都能执行	大表 `ALTER` 可能锁表很长时间。MySQL 5.7 的 Online DDL 有所改进，但仍需谨慎。

面试考点

Q：DELETE、TRUNCATE、DROP 的区别？

特性	DELETE	TRUNCATE	DROP
类型	DML	DDL	DDL
删除内容	行数据	全部数据	表结构+数据
速度	慢（逐行）	快（删文件）	最快
可回滚	✅	❌	❌
自增重置	❌	✅	N/A
触发器	✅ 触发	❌ 不触发	N/A
外键约束	可以	不可以	可以

Q：为什么 DDL 不能回滚？

DDL 在 MySQL 中会触发隐式提交，结束当前事务。这是因为在数据库引擎层面，结构变更需要立即持久化，以确保元数据一致性。

Q：大表加列怎么避免锁表？

MySQL 5.7 支持 Online DDL（如 ALGORITHM=INPLACE, LOCK=NONE），可以在不阻塞 DML 的情况下执行部分 ALTER 操作。但对于所有场景，更安全的做法是用工具如 pt-online-schema-change。

小结

DDL 定义数据库对象结构：CREATE、ALTER、DROP、TRUNCATE
TRUNCATE 比 DELETE 快，但不可回滚且重置自增
DDL 会隐式提交事务，混合使用时需特别注意
DROP 极其危险，生产环境务必确认备份

下一章引子：ALTER 可以修改表结构，但具体有哪些操作、每种操作的风险是什么？