Sql详解之Ddl
SQL 详解之 DDL（数据定义语言）
一、什么是 DDL？
概念与作用
DDL（Data Definition Language，数据定义语言）是 SQL 的一个核心组成部分，主要用于定义数据库结构，包括数据库本身、数据表、视图、索引、约束等对象。
常见命令
| 命令 | 用途 |
|---|---|
CREATE |
创建数据库对象（如库、表等） |
DROP |
删除数据库对象 |
ALTER |
修改数据库对象结构 |
TRUNCATE |
快速清空表中的所有数据 |
✅ 最佳实践： 始终在生产环境执行 DDL 之前先备份数据，或者在测试环境中模拟执行，避免误操作带来结构性数据丢失。
二、使用场景导入：设计一个学校选课系统
场景需求
在学校中存在以下实体与关系：
- 一个学院有多个老师和学生（多对一）
- 一个老师授课多门课程（多对一）
- 一个学生可以选多门课程，一门课程也能被多个学生选择（多对多）
解决思路
- 多对一关系通过外键字段建立
- 多对多关系通过“中间表”来维系
三、建库建表的教学实战
创建数据库
DROP DATABASE IF EXISTS `school`;
CREATE DATABASE `school` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `school`;
🔍 说明：
utf8mb4是推荐字符集，支持国际化与 Emoji；COLLATE定义了默认排序规则。
创建表结构
1. 学院表（tb_college）
CREATE TABLE `tb_college` (
`col_id` INT UNSIGNED AUTO_INCREMENT COMMENT '编号',
`col_name` VARCHAR(50) NOT NULL COMMENT '名称',
`col_intro` VARCHAR(500) NOT NULL DEFAULT '' COMMENT '介绍',
PRIMARY KEY (`col_id`)
);
2. 学生表（tb_student）
CREATE TABLE `tb_student` (
`stu_id` INT UNSIGNED NOT NULL COMMENT '学号',
`stu_name` VARCHAR(20) NOT NULL COMMENT '姓名',
`stu_sex` BOOLEAN NOT NULL DEFAULT 1 COMMENT '性别',
`stu_birth` DATE NOT NULL COMMENT '出生日期',
`stu_addr` VARCHAR(255) DEFAULT '' COMMENT '籍贯',
`col_id` INT UNSIGNED NOT NULL COMMENT '所属学院',
PRIMARY KEY (`stu_id`),
CONSTRAINT `fk_student_col_id` FOREIGN KEY (`col_id`) REFERENCES `tb_college`(`col_id`)
);
3. 教师表（tb_teacher）
CREATE TABLE `tb_teacher` (
`tea_id` INT UNSIGNED NOT NULL COMMENT '工号',
`tea_name` VARCHAR(20) NOT NULL COMMENT '姓名',
`tea_title` VARCHAR(10) NOT NULL DEFAULT '助教' COMMENT '职称',
`col_id` INT UNSIGNED NOT NULL COMMENT '所属学院',
PRIMARY KEY (`tea_id`),
CONSTRAINT `fk_teacher_col_id` FOREIGN KEY (`col_id`) REFERENCES `tb_college`(`col_id`)
);
4. 课程表（tb_course）
CREATE TABLE `tb_course` (
`cou_id` INT UNSIGNED NOT NULL COMMENT '编号',
`cou_name` VARCHAR(50) NOT NULL COMMENT '名称',
`cou_credit` INT NOT NULL COMMENT '学分',
`tea_id` INT UNSIGNED NOT NULL COMMENT '授课老师',
PRIMARY KEY (`cou_id`),
CONSTRAINT `fk_course_tea_id` FOREIGN KEY (`tea_id`) REFERENCES `tb_teacher`(`tea_id`)
);
5. 选课记录表（tb_record）
CREATE TABLE `tb_record` (
`rec_id` BIGINT UNSIGNED AUTO_INCREMENT COMMENT '选课记录号',
`stu_id` INT UNSIGNED NOT NULL COMMENT '学号',
`cou_id` INT UNSIGNED NOT NULL COMMENT '课程编号',
`sel_date` DATE NOT NULL COMMENT '选课日期',
`score` DECIMAL(4,1) COMMENT '考试成绩',
PRIMARY KEY (`rec_id`),
CONSTRAINT `fk_record_stu_id` FOREIGN KEY (`stu_id`) REFERENCES `tb_student`(`stu_id`),
CONSTRAINT `fk_record_cou_id` FOREIGN KEY (`cou_id`) REFERENCES `tb_course`(`cou_id`),
CONSTRAINT `uk_record_stu_cou` UNIQUE (`stu_id`, `cou_id`)
);
四、表设计细节与实践建议
1. 字符集选择：推荐 utf8mb4
SHOW CHARACTER SET;
| Charset | Maxlen | 说明 |
|---|---|---|
| utf8 | 3 | 不支持 Emoji |
| utf8mb4 | 4 | 推荐，Emoji 支持 |
| latin1 | 1 | 欧洲语言优化 |
💡 最佳实践： 默认使用 utf8mb4，确保兼容所有语言和符号。
2. 存储引擎选择
SHOW ENGINES\G
| 引擎名 | 是否支持事务 | 是否支持外键 | 锁类型 |
|---|---|---|---|
| InnoDB | ✅ | ✅ | 行锁 |
| MyISAM | ❌ | ❌ | 表锁 |
| MEMORY | ❌ | ❌ | 表锁 |
💡 建议： 除特殊用途外，应优先使用 InnoDB。
3. 数据类型选择指南
| 类型 | 用途 | 建议 |
|---|---|---|
VARCHAR |
可变长度文本 | 推荐用于短文本字段 |
DECIMAL |
精确数字，如金额 | 用于财务相关数据 |
DATETIME |
日期时间 | 范围大于 TIMESTAMP |
BOOLEAN |
布尔值 | 实际为 TINYINT(1) |
TEXT/BLOB |
长文本/二进制对象 | 不能建索引，慎用 |
五、修改与删除表结构
删除表
DROP TABLE IF EXISTS `tb_student`;
⚠️ 外键约束存在时需先删除引用或禁用外键检查。
修改表结构
添加列
ALTER TABLE `tb_student` ADD COLUMN `stu_tel` VARCHAR(20) DEFAULT '' COMMENT '联系电话';
删除列
ALTER TABLE `tb_student` DROP COLUMN `stu_tel`;
修改列数据类型
ALTER TABLE `tb_student` MODIFY COLUMN `stu_sex` CHAR(1) DEFAULT 'M' COMMENT '性别';
修改列名称
ALTER TABLE `tb_student` CHANGE COLUMN `stu_sex` `stu_gender` BOOLEAN DEFAULT 1 COMMENT '性别';
删除外键
ALTER TABLE `tb_student` DROP FOREIGN KEY `fk_student_col_id`;
添加外键
ALTER TABLE `tb_student`
ADD CONSTRAINT `fk_student_col_id`
FOREIGN KEY (`col_id`) REFERENCES `tb_college`(`col_id`);
表名重命名
ALTER TABLE `tb_student` RENAME TO `tb_stu_info`;
🚫 警告： 表重命名容易造成应用程序失败，应谨慎使用！
六、DDL 最佳实践总结
| 实践建议 | 原因与说明 |
|---|---|
| 所有 DDL 操作前做好备份 | 避免结构变更引发数据丢失 |
| 数据表使用 utf8mb4 编码 | 兼容 Emoji 与国际字符 |
| 尽量使用 InnoDB 引擎 | 提供事务支持、行级锁、高并发性能 |
使用 IF EXISTS/IF NOT EXISTS |
增强 SQL 的健壮性，避免重复操作报错 |
| 合理设计字段类型与约束 | 提升数据一致性与检索效率 |
| 表结构变更谨慎上线 | 可能影响生产系统的正常运行 |
| 文档化每一次建表或变更 | 方便团队协作与历史追踪 |
如你希望继续学习 DML（数据操作语言），我可以接着生成下一章内容：## SQL详解之DML。是否继续？