当前位置：首页 > news >正文

MySQL DDL表操作全套教程：建表、数据类型、表修改删除实操指南

news 2026/7/11 21:03:25

前言

SQL 分为四大常用分类：DDL（数据定义语言）、DML（数据操纵语言）、DQL（数据查询语言）、DCL（数据控制语言），本篇聚焦DDL，DDL 用来定义数据库结构，核心包含数据库、数据表的创建、查看、修改、删除操作。本文顺着 3 个章节的学习顺序拆解：07 表创建与表查询、08 字段数据类型选型 + 实战案例、09 表结构修改与表删除，配套可直接运行的 MySQL 代码、易错点说明，适配新手入门做笔记、后端开发日常查阅。

一、07 DDL：数据表创建 + 表信息查询

1.1 建表语法规范

通用语法格式

CREATE TABLE 表名( 字段名1 字段数据类型 [列约束], 字段名2 字段数据类型 [列约束], 字段名3 字段数据类型 [列约束] )[表参数配置];

编写硬性规范：

每一个字段单独换行，逗号分隔字段，最后一行字段末尾不能加逗号
表名、字段名建议使用小写 + 下划线命名（业务通用规范，避开数据库关键字）
基础约束可选：NOT NULL非空、PRIMARY KEY主键、UNIQUE唯一、DEFAULT设置默认值

简易学生表示例（可直接执行）

CREATE TABLE student( id INT PRIMARY KEY COMMENT '学生主键编号', student_name VARCHAR(20) NOT NULL COMMENT '学生姓名', age INT DEFAULT 18 COMMENT '学生年龄，默认填充18' ) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT '学生基础信息表';

ENGINE = InnoDB：指定存储引擎，InnoDB 支持事务、外键，是 MySQL 业务开发首选引擎
CHARSET = utf8mb4：字符集支持 emoji 表情与全量中文，杜绝中文乱码问题
COMMENT：给表、字段写中文注释，方便后续项目维护排查

1.2 DDL 查表相关命令

查看当前库内全部数据表

SHOW TABLES;

查看单张表的字段结构（字段名、类型、约束、默认值）

DESC student; -- 等价完整写法 DESCRIBE student;

查看建表完整 SQL 语句（可复用建表模板、核对配置）

SHOW CREATE TABLE student;

1.3 新手高频踩坑

未切换目标数据库直接建表：执行USE 你的数据库名;切换数据库后再创建表
表名重复：先执行DROP TABLE IF EXISTS student;做前置安全判断，避免建表报错
字符集选 utf8 而非 utf8mb4：无法存储表情包、部分冷门汉字

二、08 DDL：MySQL 主流数据类型详解 + 落地案例

MySQL 数据类型分 4 大类：数值类型、字符串类型、日期时间类型、枚举集合类型，结合业务场景讲选型规则与实操案例。

2.1 数值类型（整数 + 小数）

类型	存储空间	适用业务场景
TINYINT	1 字节	状态标记：性别、启用禁用（0/1）、分值区间 0~127
INT	4 字节	主键 ID、普通整型计数，绝大多数整数业务首选
BIGINT	8 字节	分布式雪花 ID、超高自增主键，防止数值溢出
DECIMAL(M,D)	可变	财务金额（商品价格、账户余额），杜绝浮点数精度丢失，M 总位数、D 小数位数，例`DECIMAL(10,2)`代表最多 10 位数字，小数占 2 位

避坑：禁止用 FLOAT/DOUBLE 存金额，浮点计算会产生精度偏差，对账会出现数据差错。

2.2 字符串类型

类型	容量	选型建议
CHAR(N)	固定 N 字符	长度固定内容：手机号、身份证号、行政区划编码，查询速度更快
VARCHAR(N)	动态字符	变长文本：用户名、地址、简介，节省磁盘空间，N 填写业务预估最大字符长度
TEXT	大文本	文章正文、商品详情长文本，不要拿来做索引

实战：手机号字段定义phone CHAR(11) NOT NULL UNIQUE COMMENT '11位手机号'

2.3 日期时间类型

DATE：仅存年月日（yyyy-MM-dd），适用出生日期、订单结算日
DATETIME：年月日 + 时分秒，适用于订单创建时间、数据更新时间（范围不受时区限制）
TIMESTAMP：自动适配时区，适合记录接口请求瞬时时间，存量项目优先选 DATETIME 规避时区坑

案例：给学生表补充生日字段

ALTER TABLE student ADD birthday DATE COMMENT '学生出生日期';

2.4 完整综合建表示例（融合多类数据类型）

CREATE TABLE product( id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '商品主键自增ID', product_name VARCHAR(100) NOT NULL COMMENT '商品名称', price DECIMAL(12,2) NOT NULL COMMENT '商品售价', stock INT DEFAULT 0 COMMENT '库存数量', online_status TINYINT DEFAULT 0 COMMENT '上架状态：0下架、1上架', create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '商品录入时间' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '电商商品表';

三、09 DDL：数据表结构修改、数据表删除实操

3.1 ALTER TABLE：修改表结构（开发迭代高频操作）

3.1.1 新增字段

语法：ALTER TABLE 表名 ADD 字段名类型约束 [位置控制];

-- 在student表新增邮箱字段，放在name字段后面 ALTER TABLE student ADD email VARCHAR(50) COMMENT '学生邮箱' AFTER student_name;

3.1.2 修改已有字段（字段类型、约束、注释）

语法：ALTER TABLE 表名 MODIFY COLUMN 旧字段名新类型+约束;

-- 扩充姓名字段最大字符长度到30 ALTER TABLE student MODIFY COLUMN student_name VARCHAR(30) NOT NULL COMMENT '学生姓名（扩容）';

3.1.3 重命名字段

语法：ALTER TABLE 表名 CHANGE 旧字段名新字段名完整类型约束;

ALTER TABLE student CHANGE age student_age INT DEFAULT 18 COMMENT '学生年龄';

3.1.4 删除单个字段

语法：ALTER TABLE 表名 DROP COLUMN 待删字段名;

ALTER TABLE student DROP COLUMN email;

3.1.5 修改表名、表注释

-- 修改表名 student -> tb_student ALTER TABLE student RENAME TO tb_student; -- 更新表的注释 ALTER TABLE tb_student COMMENT '重构后的学生信息表';

3.2 DROP/TRUNCATE：数据表删除两类方案（核心区分）

3.2.1 DROP TABLE：整张表彻底销毁（结构 + 数据全部清除）

-- 安全写法：表存在时才执行删除，避免报错 DROP TABLE IF EXISTS tb_student;

效果：表结构、行数据、索引、约束全部清空，磁盘空间完整释放，删除后表不能复用，需要重新建表。

3.2.2 TRUNCATE TABLE：清空表里全部行数据，保留表结构

TRUNCATE TABLE product;

关键特性：

自增主键计数器重置归零，普通DELETE FROM product;只会删数据，自增序号不会重置
DDL 操作无法被事务回滚；DELETE 属于 DML 语句，事务内可以回滚撤销删除
大数据量清空场景，TRUNCATE 性能远优于 DELETE 逐行删除

3.3 生产环境高危操作红线提醒

禁止线上生产库直接执行 DROP TABLE：删表前先做完整数据备份，二次人工核验表名，杜绝误删业务核心数据表
TRUNCATE 不可回滚：执行前必须备份表内业务数据，不要在有活跃事务的业务表随意运行
ALTER 修改大表字段：百万级数据的大表执行结构变更会锁表阻塞业务流量，低峰期使用pt-online-schema-change做无锁 DDL 变更

四、三篇知识点串联复盘 & 学习建议

4.1 知识点递进逻辑

07 章节：掌握基础建表、查表语法，搭建数据表的基础骨架，是 DDL 入门第一步
08 章节：依据业务特性挑选适配的数据类型，类型选型出错会引发精度异常、存储空间浪费、索引失效等线上隐患
09 章节：应对业务迭代，做表结构动态调整；区分两类删表语法，规避线上删库删表事故

4.2 面试高频考点总结

CHAR、VARCHAR 的性能、存储空间差异
DECIMAL 存金额的必要性，浮点类型的精度缺陷
TRUNCATE、DELETE、DROP 三者的执行原理、事务回滚能力、自增主键变化区别
ALTER MODIFY 与 CHANGE 命令的用法差别：MODIFY 只改字段属性，CHANGE 支持重命名字段

五、结语

DDL 数据表操作是 MySQL 入门的地基内容，表结构设计优劣直接决定后续 SQL 查询效率、数据可靠性。新手练习顺序建议：手写建表 SQL → 对照业务做合理数据类型选型 → 练习结构变更命令，重点记忆线上高危 DDL 的风控规范，提前规避数据丢失、线上锁表等常见故障，为后续 DML 增删改、索引优化、SQL 性能调优打下基础。