DDL 数据定义
DDL(Data Definition Language,数据定义语言)用来定义数据库的结构——建表、删表、改表。它是 SQL 三大类(DML、DDL、DCL)里和"数据形状"打交道的那一类。在插入任何数据之前,你必须先有表。
1. CREATE TABLE 建表
建表是 DDL 里最核心的操作。把表想象成 Excel 表格:每列有列名、类型、约束,这些信息一次性声明在 CREATE TABLE 里:
-- CREATE TABLE 是 DDL 里最核心的语句
-- 声明列名、类型、约束
CREATE TABLE students (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
age INT,
email VARCHAR(100) UNIQUE,
class_id INT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 语法骨架:
-- CREATE TABLE 表名 (
-- 列名1 类型1 [约束],
-- 列名2 类型2 [约束],
-- ...
-- 表级约束(可选)
-- );几个关键概念:
- 主键(Primary Key):唯一标识一行的列,值不能重复、不能为 NULL。常是自增 ID。
- 类型(Data Type):决定每列能存什么。VARCHAR 是字符串、INT 是整数、TIMESTAMP 是时间。
- 约束(Constraint):限制数据的合法性,挡住非法数据进入表。
2. 常用数据类型
选对类型既省空间又提高性能。下面是后端 90% 场景用到的类型清单:
-- 整数类型
TINYINT -- 1 字节,-128 到 127(常用作 0/1 布尔)
SMALLINT -- 2 字节
INT -- 4 字节,最常用
BIGINT -- 8 字节,自增主键、时间戳常用
-- 浮点与定点
FLOAT -- 4 字节单精度
DOUBLE -- 8 字节双精度
DECIMAL(10,2) -- 定点数,共 10 位、小数 2 位,存钱必用
-- 字符串
CHAR(10) -- 定长 10 字符,不足补空格,适合固定长度(如手机号)
VARCHAR(255) -- 变长,最多 255 字符,最常用
TEXT -- 长文本(文章正文),最多 64KB
LONGTEXT -- 超长文本,最多 4GB
-- 时间日期
DATE -- 仅日期 YYYY-MM-DD
TIME -- 仅时间 HH:MM:SS
DATETIME -- 日期 + 时间
TIMESTAMP -- 时间戳,自动跟随时区
-- 其他
BOOLEAN -- 真/假(底层是 TINYINT(1))
JSON -- MySQL 5.7+ 原生支持
ENUM('a','b') -- 枚举,只能取列表里的值几个易踩坑的点:存钱永远用 DECIMAL 而不是 FLOAT(浮点数有精度误差,0.1 + 0.2 不等于 0.3);VARCHAR(255) 的 255 不是越大越好(索引长度受限、内存占用增加);CHAR vs VARCHAR 选哪个看字段是否定长——手机号用 CHAR(11)、姓名用 VARCHAR(50)。
3. 约束(Constraints)
约束是表设计的灵魂——它从源头挡住脏数据,比应用层校验更可靠。约束分列级和表级两种写法:
-- 列级约束(跟在列定义后面)
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL, -- 不允许为空
email VARCHAR(100) UNIQUE NOT NULL, -- 唯一 + 非空
age INT CHECK (age >= 0 AND age <= 150), -- 检查约束
role VARCHAR(20) DEFAULT 'user', -- 默认值
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 表级约束(单独一行,适合复合主键、外键)
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
user_id INT NOT NULL,
amount DECIMAL(10,2) NOT NULL,
-- 外键:user_id 必须在 users 表里存在
FOREIGN KEY (user_id) REFERENCES users(id)
);常用约束速查:
NOT NULL:列必须有值,不能存 NULL。建议字段默认都加上。UNIQUE:值在表内唯一(如 email、手机号)。PRIMARY KEY:主键,等价于 NOT NULL + UNIQUE。DEFAULT:插入时没指定就用默认值。CHECK:自定义条件(age 必须 ≥ 0)。FOREIGN KEY:外键,值必须在另一张表里存在。
4. DROP 与 TRUNCATE
删表和清空数据是两种不同操作,新手常混淆:
-- DROP TABLE:删表(连带删数据,小心!)
DROP TABLE students;
-- 加 IF EXISTS,表不存在也不报错(推荐)
DROP TABLE IF EXISTS students;
-- TRUNCATE TABLE:清空表数据但保留表结构
-- 比DELETE快(直接重置数据文件),会重置自增ID,不能回滚
TRUNCATE TABLE students;
-- 区别:
-- DROP 彻底删除表(结构+数据)
-- TRUNCATE 清空数据(保留结构,重置自增ID)
-- DELETE 按条件删除(不重置自增ID,可回滚)生产环境 DROP 表要三思——数据没了就是没了(除非有备份)。我见过有人本想 DROP 一张测试表,结果误删了生产订单表,直接上头条新闻。养成习惯:DDL 操作前先备份,DROP 前 SELECT COUNT(*) 看一眼影响多少行。
5. ALTER TABLE 改表
表建好后难免要改:加列、删列、改类型、加约束。ALTER TABLE 是这些操作的入口:
-- ALTER TABLE:修改已有表的结构
-- 1. 加列
ALTER TABLE students ADD COLUMN phone VARCHAR(20);
-- 2. 加多列
ALTER TABLE students
ADD COLUMN gender CHAR(1),
ADD COLUMN address VARCHAR(200);
-- 3. 删列
ALTER TABLE students DROP COLUMN phone;
-- 4. 修改列类型(MySQL 用 MODIFY,标准用 ALTER COLUMN)
ALTER TABLE students MODIFY COLUMN name VARCHAR(100);
-- 5. 重命名列
ALTER TABLE students CHANGE COLUMN name full_name VARCHAR(100);
-- 6. 重命名表
ALTER TABLE students RENAME TO pupils;
-- 7. 加约束
ALTER TABLE students ADD CONSTRAINT chk_age CHECK (age >= 0);
ALTER TABLE students ADD UNIQUE (email);注意:大表 ALTER 可能很慢(MySQL 5.6 之前会锁表重建整张表),生产环境常用 pt-online-schema-change 这种工具做在线 DDL。如果不是紧急需求,在低峰期跑更稳妥。
6. 表设计的经验之谈
良好的表设计能让后续查询事半功倍。下面几条是真实项目里的血泪经验:
-- 表设计原则(经验之谈)
-- 1. 每张表都要有主键(通常是自增 INT 或 BIGINT)
CREATE TABLE posts (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
...
);
-- 2. 用 DECIMAL 存钱,绝不用 FLOAT
price DECIMAL(10,2) NOT NULL -- 而不是 FLOAT
-- 3. 时间字段统一用 TIMESTAMP 或 DATETIME
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-- 4. 字段尽量 NOT NULL,NULL 会让查询/索引变复杂
-- 5. 外键要不要加?
-- OLTP 业务库:加,保证数据完整性
-- 高并发互联网:常不加(性能开销),由应用层保证
-- 6. 字段名用蛇形命名(snake_case),如 user_id, created_at更深入的设计哲学(三大范式、反范式)我们在 JOIN 篇和实战篇还会展开。现在先记住一条:字段尽量 NOT NULL,主键一定有,类型尽量精确。
小结
DDL 是 SQL 的"建筑工人"——它负责把数据库的骨架搭起来。这一篇你学会了建表(CREATE)、删表(DROP/TRUNCATE)、改表(ALTER)、选类型、加约束。下一篇我们看 DML——把真正的数据塞进表里。
← 上一篇 SELECT 基础语法
下一篇 DML 数据操作 →