第5章：DDL 数据定义语言

5.1 数据库操作

创建数据库

基本语法

CREATE DATABASE [IF NOT EXISTS] 数据库名
[CHARACTER SET 字符集]
[COLLATE 排序规则];

示例

-- 创建简单数据库
CREATE DATABASE school_db;

-- 创建数据库（如果不存在）
CREATE DATABASE IF NOT EXISTS school_db;

-- 创建数据库并指定编码
CREATE DATABASE school_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- 完整示例
CREATE DATABASE IF NOT EXISTS school_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

查看数据库

-- 查看所有数据库
SHOW DATABASES;

-- 查看创建数据库的SQL
SHOW CREATE DATABASE school_db;

-- 查看当前使用的数据库
SELECT DATABASE();

使用数据库

-- 切换到指定数据库
USE school_db;

-- 注意：使用 USE 后，后续操作都在该数据库中进行

修改数据库

-- 修改数据库编码
ALTER DATABASE school_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

删除数据库

-- 删除数据库（谨慎操作！）
DROP DATABASE school_db;

-- 删除数据库（如果存在）
DROP DATABASE IF EXISTS school_db;

⚠️ 警告：删除数据库会删除其中的所有表和数据，操作不可逆！

5.2 数据表操作

创建表

基本语法

CREATE TABLE [IF NOT EXISTS] 表名 (
    字段名1 数据类型 [约束条件] [注释],
    字段名2 数据类型 [约束条件] [注释],
    ...
    [表约束]
) [ENGINE=存储引擎] [DEFAULT CHARSET=字符集] [COMMENT='表注释'];

示例：创建学生表

CREATE TABLE IF NOT EXISTS students (
    -- 主键字段
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID',
    
    -- 普通字段
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    gender ENUM('男', '女') DEFAULT '男' COMMENT '性别',
    age TINYINT UNSIGNED COMMENT '年龄',
    phone CHAR(11) COMMENT '手机号',
    email VARCHAR(100) COMMENT '邮箱',
    address VARCHAR(200) COMMENT '地址',
    
    -- 时间字段
    birthday DATE COMMENT '生日',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    -- 状态字段
    is_active BOOLEAN DEFAULT TRUE COMMENT '是否在读'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

查看表

-- 查看当前数据库所有表
SHOW TABLES;

-- 查看表结构
DESC students;
-- 或
DESCRIBE students;

-- 查看创建表的SQL
SHOW CREATE TABLE students;

-- 查看表状态
SHOW TABLE STATUS LIKE 'students';

修改表

修改表名

-- 修改表名
ALTER TABLE students RENAME TO student_info;

-- 或
RENAME TABLE students TO student_info;

添加字段

-- 添加单个字段
ALTER TABLE students ADD COLUMN score DECIMAL(5, 2) COMMENT '成绩';

-- 添加字段到指定位置
ALTER TABLE students ADD COLUMN student_no VARCHAR(20) FIRST;  -- 添加到第一列
ALTER TABLE students ADD COLUMN class VARCHAR(20) AFTER name;  -- 添加到 name 字段后

修改字段

-- 修改字段类型
ALTER TABLE students MODIFY COLUMN age SMALLINT UNSIGNED COMMENT '年龄';

-- 修改字段名和类型
ALTER TABLE students CHANGE COLUMN phone mobile CHAR(11) COMMENT '手机号';

-- 修改字段默认值
ALTER TABLE students ALTER COLUMN age SET DEFAULT 18;

-- 删除字段默认值
ALTER TABLE students ALTER COLUMN age DROP DEFAULT;

删除字段

-- 删除字段
ALTER TABLE students DROP COLUMN address;

添加约束

-- 添加主键
ALTER TABLE students ADD PRIMARY KEY (id);

-- 添加唯一约束
ALTER TABLE students ADD UNIQUE INDEX uk_email (email);

-- 添加普通索引
ALTER TABLE students ADD INDEX idx_name (name);

-- 添加外键
ALTER TABLE scores ADD CONSTRAINT fk_student 
FOREIGN KEY (student_id) REFERENCES students(id);

删除约束

-- 删除主键
ALTER TABLE students DROP PRIMARY KEY;

-- 删除索引
ALTER TABLE students DROP INDEX idx_name;

-- 删除外键
ALTER TABLE scores DROP FOREIGN KEY fk_student;

删除表

-- 删除表
DROP TABLE students;

-- 删除表（如果存在）
DROP TABLE IF EXISTS students;

-- 清空表数据（保留表结构）
TRUNCATE TABLE students;

区别：

• DROP TABLE：删除表结构和数据
• TRUNCATE TABLE：只删除数据，保留表结构，速度更快

5.3 表约束

约束类型

约束	关键字	说明
主键约束	PRIMARY KEY	唯一标识每条记录，非空且唯一
非空约束	NOT NULL	字段不能为空
唯一约束	UNIQUE	字段值不能重复
默认值约束	DEFAULT	设置默认值
外键约束	FOREIGN KEY	关联其他表的主键
检查约束	CHECK	检查数据有效性（MySQL 8.0+）

主键约束（PRIMARY KEY）

-- 方式1：单列主键
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 方式2：单列主键（单独声明）
CREATE TABLE students (
    id INT,
    name VARCHAR(50),
    PRIMARY KEY (id)
);

-- 方式3：多列主键（联合主键）
CREATE TABLE scores (
    student_id INT,
    course_id INT,
    score DECIMAL(5, 2),
    PRIMARY KEY (student_id, course_id)
);

-- 自增主键（最常用）
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

非空约束（NOT NULL）

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,  -- 姓名不能为空
    age INT,
    email VARCHAR(100) NOT NULL  -- 邮箱不能为空
);

唯一约束（UNIQUE）

-- 方式1：列级约束
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100) UNIQUE,  -- 邮箱必须唯一
    phone CHAR(11) UNIQUE       -- 手机号必须唯一
);

-- 方式2：表级约束
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    email VARCHAR(100),
    phone CHAR(11),
    UNIQUE KEY uk_email (email),
    UNIQUE KEY uk_phone (phone)
);

-- 方式3：添加唯一索引（效果相同）
ALTER TABLE students ADD UNIQUE INDEX uk_email (email);

默认值约束（DEFAULT）

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    gender ENUM('男', '女') DEFAULT '男',  -- 默认性别为男
    age INT DEFAULT 18,                    -- 默认年龄18
    is_active BOOLEAN DEFAULT TRUE,        -- 默认可用
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP  -- 默认当前时间
);

外键约束（FOREIGN KEY）

-- 创建班级表
CREATE TABLE classes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

-- 创建学生表（带外键）
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    class_id INT,
    -- 添加外键约束
    CONSTRAINT fk_class 
    FOREIGN KEY (class_id) REFERENCES classes(id)
    ON DELETE SET NULL      -- 删除班级时，学生班级设为NULL
    ON UPDATE CASCADE       -- 班级ID更新时，同步更新
);

外键选项：

• ON DELETE CASCADE：删除父表记录时，自动删除子表相关记录
• ON DELETE SET NULL：删除父表记录时，子表外键设为 NULL
• ON DELETE RESTRICT：禁止删除有子表引用的父表记录
• ON UPDATE CASCADE：更新父表主键时，自动更新子表外键

检查约束（CHECK）

-- MySQL 8.0+ 支持
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 0 AND age <= 150),  -- 年龄范围检查
    gender ENUM('男', '女'),
    email VARCHAR(100),
    CHECK (email LIKE '%@%')  -- 邮箱格式检查
);

实操案例

案例：完整的学生管理系统

1. 创建数据库

-- 创建数据库
CREATE DATABASE IF NOT EXISTS school_db
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE school_db;

2. 创建班级表

CREATE TABLE IF NOT EXISTS classes (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '班级ID',
    name VARCHAR(50) NOT NULL COMMENT '班级名称',
    grade VARCHAR(20) COMMENT '年级',
    teacher VARCHAR(50) COMMENT '班主任',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='班级表';

3. 创建学生表

CREATE TABLE IF NOT EXISTS students (
    -- 主键
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '学生ID',
    
    -- 学号（唯一）
    student_no VARCHAR(20) NOT NULL UNIQUE COMMENT '学号',
    
    -- 基本信息
    name VARCHAR(50) NOT NULL COMMENT '姓名',
    gender ENUM('男', '女') DEFAULT '男' COMMENT '性别',
    age TINYINT UNSIGNED CHECK (age >= 0 AND age <= 150) COMMENT '年龄',
    birthday DATE COMMENT '生日',
    
    -- 联系方式
    phone CHAR(11) UNIQUE COMMENT '手机号',
    email VARCHAR(100) UNIQUE COMMENT '邮箱',
    address VARCHAR(200) COMMENT '家庭住址',
    
    -- 外键：班级ID
    class_id INT COMMENT '班级ID',
    
    -- 状态
    is_active BOOLEAN DEFAULT TRUE COMMENT '是否在读',
    
    -- 时间戳
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    -- 外键约束
    CONSTRAINT fk_class FOREIGN KEY (class_id) 
    REFERENCES classes(id) ON DELETE SET NULL ON UPDATE CASCADE,
    
    -- 索引
    INDEX idx_name (name),
    INDEX idx_class (class_id)
    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

4. 创建课程表

CREATE TABLE IF NOT EXISTS courses (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '课程ID',
    course_no VARCHAR(20) NOT NULL UNIQUE COMMENT '课程编号',
    name VARCHAR(100) NOT NULL COMMENT '课程名称',
    credit DECIMAL(3, 1) DEFAULT 2.0 COMMENT '学分',
    teacher VARCHAR(50) COMMENT '授课教师',
    description TEXT COMMENT '课程描述',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程表';

5. 创建成绩表

CREATE TABLE IF NOT EXISTS scores (
    -- 联合主键
    student_id INT COMMENT '学生ID',
    course_id INT COMMENT '课程ID',
    
    -- 成绩
    score DECIMAL(5, 2) CHECK (score >= 0 AND score <= 100) COMMENT '成绩',
    exam_date DATE COMMENT '考试日期',
    
    -- 时间戳
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    
    -- 主键
    PRIMARY KEY (student_id, course_id),
    
    -- 外键
    CONSTRAINT fk_score_student FOREIGN KEY (student_id) 
    REFERENCES students(id) ON DELETE CASCADE ON UPDATE CASCADE,
    
    CONSTRAINT fk_score_course FOREIGN KEY (course_id) 
    REFERENCES courses(id) ON DELETE CASCADE ON UPDATE CASCADE
    
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成绩表';

6. 查看表结构

-- 查看所有表
SHOW TABLES;

-- 查看学生表结构
DESC students;

-- 查看创建表的SQL
SHOW CREATE TABLE students;

7. 修改表示例

-- 添加字段
ALTER TABLE students ADD COLUMN id_card CHAR(18) UNIQUE COMMENT '身份证号';

-- 修改字段
ALTER TABLE students MODIFY COLUMN address VARCHAR(300) COMMENT '家庭住址';

-- 添加索引
ALTER TABLE students ADD INDEX idx_student_no (student_no);

-- 查看索引
SHOW INDEX FROM students;

新手易错点

1. 约束使用错误

-- 错误：主键重复
CREATE TABLE students (
    id INT PRIMARY KEY,
    student_no VARCHAR(20) PRIMARY KEY,  -- 错误：不能有多个主键
    name VARCHAR(50)
);

-- 正确：使用联合主键或唯一约束
CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_no VARCHAR(20) UNIQUE,  -- 使用唯一约束
    name VARCHAR(50)
);

2. 创建表时字段格式错误

-- 错误：缺少逗号
CREATE TABLE students (
    id INT PRIMARY KEY
    name VARCHAR(50)  -- 错误：缺少逗号
);

-- 正确
CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

3. 删除操作误删

-- 危险操作：未确认就删除
DROP TABLE students;  -- 直接删除，数据全部丢失

-- 安全做法：先确认
-- 1. 查看表是否存在
SHOW TABLES LIKE 'students';

-- 2. 确认数据已备份
-- 3. 再执行删除
DROP TABLE IF EXISTS students;

4. 外键约束错误

-- 错误：外键类型不匹配
CREATE TABLE classes (
    id INT PRIMARY KEY
);

CREATE TABLE students (
    id INT PRIMARY KEY,
    class_id BIGINT,  -- 错误：类型不匹配（INT vs BIGINT）
    FOREIGN KEY (class_id) REFERENCES classes(id)
);

-- 正确：外键类型必须一致
CREATE TABLE students (
    id INT PRIMARY KEY,
    class_id INT,  -- 与引用字段类型一致
    FOREIGN KEY (class_id) REFERENCES classes(id)
);

小结

通过本章的学习，你掌握了：

• ✅ 数据库操作（创建、查看、修改、删除）
• ✅ 数据表操作（创建、查看、修改、删除）
• ✅ 表约束（主键、非空、唯一、默认值、外键）
• ✅ 完整的学生管理系统表结构设计

DDL 是操作数据库结构的基础，掌握这些命令对于数据库设计和管理至关重要。继续学习 第6章：DML 数据操纵语言。