企业官网建设流程全解析

数据库基础：DDL数据定义语言

前言：本文是“数据库基础”系列的第八篇。上一篇我们学习了 DML（数据操作语言），掌握了数据的增删改操作。本篇我们将进入 DDL（数据定义语言，Data Definition Language）的学习——这是 SQL 语言中最基础的部分，负责数据库和表结构的创建、修改与删除。如果说 DML 是在操作数据，那 DDL 就是在搭建存放数据的仓库。掌握了 DDL，才算真正学会了“造表”。

目录

• 一、库的管理
• 二、表的管理
• 三、常见数据类型
• 四、常见约束
• 五、标识列
• 六、总结

一、库的管理

1.1 库的创建

语法：

CREATEDATABASE[IFNOTEXISTS]库名;

示例：

-- 创建数据库 mydb（如果已存在则忽略）CREATEDATABASEIFNOTEXISTSmydb;-- 创建数据库时指定字符集CREATEDATABASEmydb2CHARACTERSETutf8mb4;

1.2 库的修改

-- 修改数据库的字符集ALTERDATABASEmydbCHARACTERSETutf8mb4;

1.3 库的删除

-- 删除数据库（如果存在则删除，避免报错）DROPDATABASEIFEXISTSmydb;

二、表的管理

2.1 表的创建

语法：

CREATETABLE表名(列名 列的类型[(长度)][约束],列名 列的类型[(长度)][约束],...列名 列的类型[(长度)][约束]);

示例：

以下案例涉及major表（专业表）和stuinfo表（学生表），两者存在外键关联。先创建主表major，再创建从表stuinfo。

-- 第一步：创建主表 major（专业表）CREATETABLEmajor(idINTPRIMARYKEY,majornameVARCHAR(20)NOTNULL);-- 第二步：创建从表 stuinfo（学生表），引用 major 表的 id 作为外键CREATETABLEstuinfo(idINTPRIMARYKEY,-- 学号，主键stunameVARCHAR(20)NOTNULL,-- 姓名，非空genderCHAR(1),-- 性别seatINTUNIQUE,-- 座位号，唯一ageINTDEFAULT18,-- 年龄，默认18majoridINT,-- 专业编号，外键CONSTRAINTfk_stuinfo_majorFOREIGNKEY(majorid)REFERENCESmajor(id));

通用的创建写法（推荐）：

CREATETABLEIFNOTEXISTSmajor(idINTPRIMARYKEY,majornameVARCHAR(20)NOTNULL);CREATETABLEIFNOTEXISTSstuinfo(idINTPRIMARYKEY,stunameVARCHAR(20)NOTNULL,genderCHAR(1),seatINTUNIQUE,ageINTDEFAULT18,majoridINT,CONSTRAINTfk_stuinfo_majorFOREIGNKEY(majorid)REFERENCESmajor(id));

2.2 表的修改

修改表名

-- 将表名 stuinfo 改为 studentALTERTABLEstuinfoRENAMETOstudent;

修改列名

-- 将列名 stuname 改为 studentnameALTERTABLEstuinfo CHANGECOLUMNstuname studentnameVARCHAR(20);

修改列的类型或约束

-- 修改 age 列的类型为 TINYINTALTERTABLEstuinfoMODIFYCOLUMNageTINYINT;

添加列

-- 添加新列 phoneALTERTABLEstuinfoADDCOLUMNphoneVARCHAR(11);

删除列

-- 删除 phone 列ALTERTABLEstuinfoDROPCOLUMNphone;

2.3 表的删除

-- 删除表（如果存在则删除）-- 注意：删除从表前需要先删除外键约束，或先删除从表再删主表ALTERTABLEstuinfoDROPFOREIGNKEYfk_stuinfo_major;-- 先删外键DROPTABLEIFEXISTSstuinfo;DROPTABLEIFEXISTSmajor;

2.4 表的复制

仅复制表结构

-- 创建一张新表 stu_copy，只复制 stuinfo 的表结构（不包含数据）CREATETABLEstu_copyLIKEstuinfo;

复制表结构+数据

-- 创建一张新表 stu_copy2，复制 stuinfo 的结构和数据CREATETABLEstu_copy2ASSELECT*FROMstuinfo;

只复制部分字段

-- 只复制 id 和 stuname 字段（不复制数据）CREATETABLEstu_copy3ASSELECTid,stunameFROMstuinfoWHERE1=2;

三、常见数据类型

3.1 数值型

整型

-- 无符号整型（不能为负数）CREATETABLEtest_int(idINTUNSIGNED);

小数

-- DECIMAL 示例：总位数5，小数2位（范围：-999.99 ~ 999.99）CREATETABLEtest_decimal(priceDECIMAL(5,2));

3.2 字符型

-- CHAR 和 VARCHAR 对比CREATETABLEtest_string(nameCHAR(10),-- 固定10个字符，不足补空格addressVARCHAR(50)-- 可变长度，最多50个字符);

3.3 日期型

CREATETABLEtest_date(birthDATE,created_atDATETIMEDEFAULTNOW(),updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP);

四、常见约束

4.1 六大约束

4.2 约束的添加方式

列级约束（写在列定义后面）

-- 先创建主表CREATETABLEmajor(idINTPRIMARYKEY,majornameVARCHAR(20)NOTNULL);-- 再创建从表（列级约束）CREATETABLEstuinfo(idINTPRIMARYKEY,-- 主键stunameVARCHAR(20)NOTNULL,-- 非空genderCHAR(1)CHECK(genderIN('男','女')),-- 检查（MySQL无效）seatINTUNIQUE,-- 唯一ageINTDEFAULT18,-- 默认majoridINT,-- 外键（列级无效，需表级）CONSTRAINTfk_stuinfo_majorFOREIGNKEY(majorid)REFERENCESmajor(id));

表级约束（写在所有列定义之后）

-- 先创建主表CREATETABLEmajor(idINTPRIMARYKEY,majornameVARCHAR(20)NOTNULL);-- 再创建从表（表级约束）CREATETABLEstuinfo(idINT,stunameVARCHAR(20),genderCHAR(1),seatINT,ageINT,majoridINT,PRIMARYKEY(id),-- 主键UNIQUE(seat),-- 唯一FOREIGNKEY(majorid)REFERENCESmajor(id)-- 外键);

4.3 主键 vs 唯一键

4.4 外键

-- 创建主表 majorCREATETABLEmajor(idINTPRIMARYKEY,majornameVARCHAR(20)NOTNULL);-- 插入主表数据INSERTINTOmajorVALUES(1,'计算机科学'),(2,'软件工程'),(3,'大数据技术');-- 创建从表 stuinfo，添加外键约束CREATETABLEstuinfo(idINTPRIMARYKEY,stunameVARCHAR(20),majoridINT,CONSTRAINTfk_stuinfo_majorFOREIGNKEY(majorid)REFERENCESmajor(id));-- 插入从表数据（majorid 必须存在于 major 表中）INSERTINTOstuinfoVALUES(1,'张三',1);-- 有效INSERTINTOstuinfoVALUES(2,'李四',2);-- 有效INSERTINTOstuinfoVALUES(3,'王五',5);-- 报错！majorid=5 在 major 表中不存在

外键注意事项：

1. 在从表设置外键关系
2. 从表的外键列类型与主表的关联列类型要求一致或兼容
3. 主表的关联列必须是一个KEY（主键或唯一键）
4. 插入数据时：先插主表，再插从表
5. 删除数据时：先删从表，再删主表

4.5 修改表时添加约束

-- 先创建无约束的表CREATETABLEmajor(idINT,majornameVARCHAR(20));CREATETABLEstuinfo(idINT,stunameVARCHAR(20),genderCHAR(1),seatINT,ageINT,majoridINT);-- 添加非空约束ALTERTABLEstuinfoMODIFYCOLUMNstunameVARCHAR(20)NOTNULL;-- 添加默认约束ALTERTABLEstuinfoMODIFYCOLUMNageINTDEFAULT18;-- 添加主键ALTERTABLEstuinfoADDPRIMARYKEY(id);-- 添加唯一ALTERTABLEstuinfoADDUNIQUE(seat);-- 添加外键ALTERTABLEstuinfoADDCONSTRAINTfk_stuinfo_majorFOREIGNKEY(majorid)REFERENCESmajor(id);

4.6 修改表时删除约束

-- 删除非空约束ALTERTABLEstuinfoMODIFYCOLUMNstunameVARCHAR(20)NULL;-- 删除默认约束ALTERTABLEstuinfoMODIFYCOLUMNageINT;-- 删除主键ALTERTABLEstuinfoDROPPRIMARYKEY;-- 删除唯一ALTERTABLEstuinfoDROPINDEXseat;-- 删除外键ALTERTABLEstuinfoDROPFOREIGNKEYfk_stuinfo_major;

五、标识列（自增长列）

5.1 含义与特点

标识列（AUTO_INCREMENT）也称为自增长列，可以不用手动插入值，系统提供默认的序列值。

特点：

1. 标识列不一定必须和主键搭配，但要求是一个KEY（主键或唯一键）
2. 一个表最多只能有一个标识列
3. 标识列的类型只能是数值型（INT、BIGINT等）
4. 可以通过SET auto_increment_increment = 步长设置步长（MySQL 不支持设置起始值，但可以通过手动插入值来设定）

5.2 创建表时设置标识列

CREATETABLEtab_stu(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(20));INSERTINTOtab_stuVALUES(NULL,'shujia');SELECT*FROMtab_stu;

5.3 设置步长

-- 查看当前步长SHOWVARIABLESLIKE'%auto_increment%';-- 设置步长为 3SETauto_increment_increment=3;

5.4 修改表时设置标识列

ALTERTABLEtab_stuMODIFYCOLUMNidINTPRIMARYKEYAUTO_INCREMENT;

5.5 修改表时删除标识列

ALTERTABLEtab_stuMODIFYCOLUMNidINT;

六、总结

核心脉络

库的管理

• 创建：CREATE DATABASE 库名
• 修改：ALTER DATABASE 库名（一般改字符集）
• 删除：DROP DATABASE 库名

表的管理

• 创建：CREATE TABLE 表名 (字段名 字段类型 [约束], ...)
  • 常见数据类型：INT、VARCHAR、CHAR、DATE、DECIMAL 等
  • 常见约束：NOT NULL、DEFAULT、PRIMARY KEY、UNIQUE、FOREIGN KEY
• 修改：ALTER TABLE 表名 ADD / MODIFY / CHANGE / DROP / RENAME
• 删除：DROP TABLE 表名

约束

• 六大约束：NOT NULL（非空）、DEFAULT（默认）、PRIMARY KEY（主键）、UNIQUE（唯一）、FOREIGN KEY（外键）、CHECK（检查）
• 两种写法：列级约束（跟在字段后面）、表级约束（写在所有字段最后）

标识列（AUTO_INCREMENT）

• 必须搭配KEY（主键或唯一键）
• 字段类型必须为数值型（INT、BIGINT 等）
• 可设置步长：SET auto_increment_increment = 步长

三个关键要点：

1. 数据类型：VARCHAR是开发中最常用的字符串类型，INT是最常用的整数类型，DATETIME和TIMESTAMP需根据是否涉及时区来选择
2. 约束：主键用于唯一标识一行，唯一键用于保证字段值不重复，外键用于维护表间关系
3. 标识列：自增长列必须为数值型且必须是一个KEY，一张表最多只有一个

系列直达

• 上篇：数据库基础：DML 数据操作语言
• 本篇：数据库基础：DDL 数据定义语言（本文）
• 下篇：数据库基础：TCL事务与DDL视图