掌握数据库设计是Python开发者进阶的必修课。本文以学校选课系统为实战场景，带你深入理解SQL DDL（数据定义语言）的核心用法，从建库建表到表结构修改，一次搞定。

SQL四大分类速览

SQL（结构化查询语言）是关系型数据库的核心操作语言，通常分为四大类，各司其职：

  • DDL（数据定义语言）：操作数据库对象（创建、删除、修改），如CREATE、DROP、ALTER。
  • DML（数据操作语言）：操作数据表中的数据，如INSERT、DELETE、UPDATE、SELECT。
  • DCL（数据控制语言）：管理访问权限，如GRANT、REVOKE。
  • TCL（事务控制语言）：管理事务，如COMMIT、ROLLBACK。

理解这些分类，能帮助你像使用JavaScript、Python、Java、C++、Go等编程语言一样，精准选择数据库操作工具。

SQL语法规范

SQL不区分大小写，但为了代码可读性，建议：

  • 关键字大写（如CREATE、DROP）
  • 自定义名称小写（如数据库名、表名、字段名）

⚠️ 如果团队有明确规范，务必遵守，个人习惯不能凌驾于团队之上。

️ 实战：建库建表（学校选课系统）

我们创建一个名为 school 的数据库，围绕学院、老师、学生、课程、选课五大实体设计表结构。实体关系如下：

  • 学生与学院：多对一
  • 老师与学院：多对一
  • 老师与课程：多对一（简化设计）
  • 学生与课程：多对多（需要中间表）

共5张表：学院表（tb_college）、学生表（tb_student）、教师表（tb_teacher）、课程表（tb_course）、选课记录表（tb_record）。

具体DDL语句如下：

-- 如果存在名为school的数据库就删除它
DROP DATABASE IF EXISTS `school`;
-- 创建名为school的数据库并设置默认的字符集和排序方式
CREATE DATABASE `school` DEFAULTCHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
-- 切换到school数据库上下文环境
USE `school`;
-- 创建学院表
CREATE TABLE `tb_college`
(
`col_id`    int unsigned AUTO_INCREMENT      COMMENT '编号',
`col_name`  varchar(50)  NOT NULL            COMMENT '名称',
`col_intro` varchar(500) NOT NULLDEFAULT'' COMMENT '介绍',
PRIMARY KEY (`col_id`)
);
-- 创建学生表
CREATE TABLE `tb_student`
(
`stu_id`    int unsigned NOT NULL           COMMENT '学号',
`stu_name`  varchar(20)  NOT NULL           COMMENT '姓名',
`stu_sex`   boolean      NOT NULLDEFAULT1 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`)
);
-- 创建教师表
CREATE TABLE `tb_teacher`
(
`tea_id`    int unsigned NOT NULL                COMMENT '工号',
`tea_name`  varchar(20)  NOT NULL                COMMENT '姓名',
`tea_title` varchar(10)  NOT NULLDEFAULT'助教' 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`)
);
-- 创建课程表
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`)
);
-- 创建选课记录表
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`)
);

⚠️ DDL关键细节

  • 反引号（`）：包裹数据库名、表名、字段名，避免与SQL关键字冲突。
  • 字符集设置：使用 DEFAULT CHARACTER SET utf8mb4 指定utf8mb4，支持Emoji。查看所有字符集：
    show character set;
    。如需永久设置，修改配置文件：character-set-server=utf8mb4（新手慎操作）。
  • database与schema：二者可互换，例如 DATABASE 和 SCHEMA 等效：CREATE SCHEMA school 与 CREATE DATABASE school 效果相同。
  • 约束：not null（非空）、default（默认值）、primary key（主键）、foreign key（外键，使用 references 引用）。约束命名使用 constriant。
  • COMMENT：使用 COMMENT 添加注释，提升可维护性。
  • 存储引擎：通过 show engines 查看，推荐使用InnoDB，在建表语句末尾添加 engine=innodb。

代码示例：

show engines\G

说明：上面的 \G 是为了换一种输出方式，在命令行客户端中，如果表的字段很多一行显示不完，就会导致输出的内容看起来非常不舒服，使用 \G 可以将记录的每个列以独占整行的的方式输出，这种输出方式在命令行客户端中看起来会舒服很多。

*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
9 rows in set (0.00 sec)

存储引擎对比：

特性

InnoDB

MRG_MYISAM

MEMORY

MyISAM

存储限制

有

没有

有

有

事务

支持

锁机制

行锁

表锁

表锁

表锁

B树索引

支持

支持

支持

支持

哈希索引

支持

全文检索

支持（5.6+）

支持

集群索引

支持

数据缓存

支持

支持

索引缓存

支持

支持

支持

支持

数据可压缩

支持

内存使用

高

低

中

低

存储空间使用

高

低

低

批量插入性能

低

高

高

高

是否支持外键

支持

InnoDB是唯一支持外键、事务和行锁的引擎，适合高并发场景。

数据类型选择技巧

原则：够用即可、精准匹配，避免浪费存储空间。不确定时，使用MySQL帮助系统：

? data types

说明：在 MySQLWorkbench 中，不能使用获取帮助，要使用对应的命令。

You asked for help about help category: "Data Types"
For more information, type 'help ', where  is one of the following
topics:
   AUTO_INCREMENT
   BIGINT
   BINARY
   BIT
   BLOB
   BLOB DATA TYPE
   BOOLEAN
   CHAR
   CHAR BYTE
   DATE
   DATETIME
   DEC
   DECIMAL
   DOUBLE
   DOUBLE PRECISION
   ENUM
   FLOAT
   INT
   INTEGER
   LONGBLOB
   LONGTEXT
   MEDIUMBLOB
   MEDIUMINT
   MEDIUMTEXT
   SET DATA TYPE
   SMALLINT
   TEXT
   TIME
   TIMESTAMP
   TINYBLOB
   TINYINT
   TINYTEXT
   VARBINARY
   VARCHAR
   YEAR DATA TYPE

查看varchar帮助：

? varchar

执行结果：

Name: 'VARCHAR'
Description:
[NATIONAL] VARCHAR(M) [CHARACTER SET charset_name] [COLLATE
collation_name]
A variable-length string. M represents the maximum column length in
characters. The range of M is 0 to 65,535. The effective maximum length
of a VARCHAR is subject to the maximum row size (65,535 bytes, which is
shared among all columns) and the character set used. For example, utf8
characters can require up to three bytes per character, so a VARCHAR
column that uses the utf8 character set can be declared to be a maximum
of 21,844 characters. See
http://dev.mysql.com/doc/refman/5.7/en/column-count-limit.html.
MySQL stores VARCHAR values as a 1-byte or 2-byte length prefix plus
data. The length prefix indicates the number of bytes in the value. A
VARCHAR column uses one length byte if values require no more than 255
bytes, two length bytes if values may require more than 255 bytes.
*Note*:
MySQL follows the standard SQL specification, and does not remove
trailing spaces from VARCHAR values.
VARCHAR is shorthand for CHARACTER VARYING. NATIONAL VARCHAR is the
standard SQL way to define that a VARCHAR column should use some
predefined character set. MySQL uses utf8 as this predefined character
set. http://dev.mysql.com/doc/refman/5.7/en/charset-national.html.
NVARCHAR is shorthand for NATIONAL VARCHAR.
URL: http://dev.mysql.com/doc/refman/5.7/en/string-type-overview.html
  • 字符串：VARCHAR（变长）或 CHAR（定长），InnoDB中两者性能无本质区别。大字符串用TEXT，大字节用BLOB。
  • 浮点数：FLOAT（不推荐）或 DOUBLE，定点数用DECIMAL。
  • 时间日期：DATETIME优于TIMESTAMP（后者2038年溢出）。
  • 自增字段：MySQL 8.x修复了回溯问题，高并发场景建议使用分布式ID算法（如SnowFlake）。
[AFFILIATE_SLOT_1]

删除表和修改表

DDL还支持删除和修改表结构，以下以学生表（tb_student）为例。

1️⃣ 删除表（DROP TABLE）

推荐安全写法：

DROP TABLE `tb_student`;

或

DROP TABLE IF EXISTS `tb_student`;

⚠️ 如果表被外键引用，需先删除引用表或外键约束。

2️⃣ 修改表（ALTER TABLE）

核心关键字：ALTER TABLE。

  • 添加列：
    ALTER TABLE `tb_student` ADD COLUMN `stu_tel` varchar(20) NOT NULL COMMENT '联系电话';
    （注意非空约束与已有数据的冲突）。
  • 删除列：
    ALTER TABLE `tb_student` DROP COLUMN `stu_tel`;
    （数据永久丢失）。
  • 修改数据类型：
    ALTER TABLE `tb_student` MODIFY COLUMN `stu_sex` char(1) NOT NULL DEFAULT 'M' COMMENT '性别';
    （注意兼容性）。
  • 修改字段名称：
    ALTER TABLE `tb_student` CHANGE COLUMN `stu_sex` `stu_gender` boolean DEFAULT 1 COMMENT '性别';
    （CHANGE可改名，MODIFY不行）。
  • 删除约束：
    ALTER TABLE `tb_student` DROP FOREIGN KEY `fk_student_col_id`;
    。
  • 添加约束：
    ALTER TABLE `tb_student` ADD FOREIGN KEY (`col_id`) REFERENCES `tb_college` (`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`;
    （谨慎操作，会影响所有引用SQL）。
[AFFILIATE_SLOT_2]

总结

DDL操作不可逆，执行前务必确认并备份。修改表结构时尽量在业务低峰期进行。外键约束保障数据完整性，但可能影响性能，需根据场景权衡。统一编码规范（关键字大写、名称小写、添加注释），优先使用MySQL 8.x。掌握这些技能，无论你是写Python、JavaScript、Java、C++还是Go，都能游刃有余地设计高效数据库。

图片?helpon updateon deleterestrictrestrictcascadeset null