MySQL DDL 数据定义语言

什么是 DDL

DDL（Data Definition Language，数据定义语言）是 SQL 的核心组成部分，专门用于定义和管理数据库的结构（Schema）。如果说数据库是一座大楼，那么 DDL 就是用来画图纸、打地基、建墙体和拆改结构的工具。

核心特点

操作对象：数据库、表、索引、视图、存储过程等结构对象，不直接操作表中的具体数据行。
自动提交：在大多数数据库系统（包括 MySQL）中，DDL 语句执行后会隐式提交（Auto-commit），这意味着一旦执行成功，无法通过 ROLLBACK 回滚（注：部分现代 DBMS 如 PostgreSQL 支持事务性 DDL，但 MySQL 传统上不支持）。
高风险性：DROP 或 TRUNCATE 等操作可能导致数据结构甚至数据永久丢失，生产环境需谨慎执行。

常用命令

命令	功能描述	风险等级
`CREATE`	创建新对象（库、表等）	低
`ALTER`	修改现有对象结构	中
`DROP`	删除对象及其所有数据	高
`TRUNCATE`	清空表数据但保留结构	高
`RENAME`	重命名对象	低

操作数据库

数据库是表的容器。在进行任何表操作之前，通常需要先管理好数据库本身。

查询数据库

查看当前数据库服务器上存在的所有数据库。

-- 列出所有数据库
SHOW DATABASES;

(执行结果将显示一个列表，包含 system 数据库和用户自定义数据库)

创建数据库

创建一个全新的数据库容器。为了避免因数据库已存在而报错，最佳实践是使用 IF NOT EXISTS 子句。

-- 基础创建
CREATE DATABASE your_database;

-- 推荐做法：如果不存在则创建，防止报错
CREATE DATABASE IF NOT EXISTS your_database 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_unicode_ci;

知识点补充：
字符集 (Character Set)：推荐使用 utf8mb4，它是 MySQL 中真正的 UTF-8 实现，支持存储 Emoji 表情等特殊字符（传统的 utf8 仅支持 3 字节，无法存储 Emoji）。
排序规则 (Collation)：utf8mb4_unicode_ci 提供了更准确的排序和比较规则。

删除数据库

彻底删除数据库及其包含的所有表和数据。此操作不可逆。

-- 基础删除
DROP DATABASE your_database_name;

-- 推荐做法：如果存在则删除，防止报错
DROP DATABASE IF EXISTS your_database_name;

选择/切换数据库

在执行表操作前，必须告诉 MySQL 当前要操作哪个数据库。

USE your_database;

执行成功后，后续的 CREATE TABLE 等命令将在该数据库上下文中执行。

操作数据表

表是数据库中存储数据的基本单元。合理设计表结构是高性能应用的基础。

查询表与表结构

查看当前库下的所有表：
```
SHOW TABLES;
```
查看表的详细结构（字段名、类型、是否为主键、默认值等）：
```
DESC table_name;
-- 或者
DESCRIBE table_name;
```

创建表

基础语法

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    PRIMARY KEY (column_name),
    [其他表级约束]
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

关键要素解析

列定义：列名数据类型约束条件。
主键 (PRIMARY KEY)：唯一标识每一行记录，不能为 NULL，且必须唯一。
存储引擎 (ENGINE)：MySQL 支持多种引擎，InnoDB 是默认且最推荐的引擎，支持事务、行级锁和外键。
字符集：建议显式指定 utf8mb4。

案例：创建用户表

CREATE TABLE users (
    user_id INT PRIMARY KEY,          -- 用户 ID，主键
    username VARCHAR(50) NOT NULL,    -- 用户名，非空
    email VARCHAR(100) UNIQUE         -- 邮箱，唯一约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

综合实战

设计一张规范的学生表

需求分析：

编号：主键，自增。
姓名：最长 10 个汉字，非空。
性别：仅“男”或“女”。
生日：年月日。
入学成绩：需精确到小数点后两位（如 598.50）。
邮件：最大 64 字符。
电话：可能包含 - 等非数字字符。
状态：正常 (1)、休学 (2)、毕业 (3)。

SQL 实现：

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '学生编号',
    
    -- 姓名：VARCHAR 适合长度变化的字符串，10 个汉字在 utf8mb4 下需考虑字符数而非字节
    student_name VARCHAR(10) NOT NULL COMMENT '姓名',
    
    -- 性别：CHAR(1) 固定长度效率高，配合 CHECK 约束保证数据质量
    gender CHAR(1) NOT NULL COMMENT '性别',
    
    birthday DATE COMMENT '出生日期',
    
    -- 成绩：DECIMAL(5,2) 表示总共 5 位，其中 2 位小数，最大值为 999.99
    entrance_score DECIMAL(5, 2) COMMENT '入学成绩',
    
    email VARCHAR(64) COMMENT '电子邮箱',
    
    -- 电话：包含 '-' 等符号，必须用字符串，不能用数字类型
    contact_phone VARCHAR(20) COMMENT '联系电话',
    
    -- 状态：TINYINT 节省空间，配合 CHECK 约束限制范围
    student_status TINYINT DEFAULT 1 COMMENT '状态：1-正常，2-休学，3-毕业',

    -- 表级约束
    CONSTRAINT chk_gender CHECK (gender IN ('男', '女')),
    CONSTRAINT chk_status CHECK (student_status IN (1, 2, 3)),
    CONSTRAINT chk_name_len CHECK (CHAR_LENGTH(student_name) <= 10)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生信息表';

说明：
使用了 AUTO_INCREMENT 让主键自动生成。
使用了 COMMENT 添加字段注释，便于后期维护。
使用了 CHECK 约束（MySQL 8.0.16+ 正式生效）在数据库层面保证数据合法性，减轻代码层压力。

修改表结构

业务需求是变化的，ALTER TABLE 允许我们在不删除表的情况下修改结构。

修改表名

ALTER TABLE old_table_name RENAME TO new_table_name;

添加列

-- 添加新列到末尾
ALTER TABLE customers ADD COLUMN phone_number VARCHAR(15);

-- 添加新列到指定位置 (FIRST 或 AFTER 某列)
ALTER TABLE customers ADD COLUMN age INT AFTER username;

修改列属性

用于修改数据类型、约束（如 NOT NULL）、默认值等，列名不变。

-- 将价格字段改为更精确的 decimal 类型
ALTER TABLE products MODIFY COLUMN price DECIMAL(10, 2) NOT NULL DEFAULT 0.00;

修改列名及属性

用于重命名列，同时也可以修改类型。需要写两次列名（旧名新名）。

-- 将 order_amount 改名为 total_amount，并修改类型
ALTER TABLE orders CHANGE COLUMN order_amount total_amount DECIMAL(8, 2);

删除列

-- 删除不再需要的列
ALTER TABLE customers DROP COLUMN email;

警告：删除列会导致该列所有数据永久丢失，请务必先备份！

删除表

-- 直接删除
DROP TABLE employees;

-- 安全删除：仅当表存在时执行，避免报错
DROP TABLE IF EXISTS customers;