第7章：创建与管理表

本章目标

学完本章后，你将能够：

使用 CREATE TABLE 创建表，熟练掌握列定义、数据类型、约束的写法
理解并运用 AUTO_INCREMENT、ENGINE、CHARSET 等表选项
区分临时表与永久表，以及从已有表创建新表的 LIKE 和 AS SELECT 两种方式
使用 ALTER TABLE 进行表结构变更（增删改列、索引、约束、重命名等）
掌握 DROP TABLE、TRUNCATE TABLE、RENAME TABLE 的表管理操作

前置知识

第4章：会创建数据库、理解字符集
第5章：掌握数值和字符串类型
第6章：掌握日期和特殊类型

7.1 CREATE TABLE 完整语法

语法结构概览

sql

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] 表名 (
    列名 数据类型 [NOT NULL | NULL] [DEFAULT 默认值]
        [AUTO_INCREMENT] [UNIQUE [KEY]] [[PRIMARY] KEY]
        [COMMENT '列注释'],
    列名 数据类型 ...,
    ...
    [表级约束]      -- PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, INDEX
) [ENGINE=引擎名]
  [AUTO_INCREMENT=起始值]
  [DEFAULT CHARSET=字符集名]
  [COLLATE=排序规则名]
  [COMMENT='表注释'];

这不是 MySQL 语法的全部，但覆盖了 95% 以上的实际使用场景。

最简单的建表

sql

-- 创建一张最简的学生表
CREATE TABLE student (
    id INT,
    name VARCHAR(50),
    age INT
);

-- 查看表结构
DESC student;
-- +-------+-------------+------+-----+---------+-------+
-- | Field | Type        | Null | Key | Default | Extra |
-- +-------+-------------+------+-----+---------+-------+
-- | id    | int         | YES  |     | NULL    |       |
-- | name  | varchar(50) | YES  |     | NULL    |       |
-- | age   | int         | YES  |     | NULL    |       |
-- +-------+-------------+------+-----+---------+-------+

这能工作，但缺失了很多"好习惯"：没有注释、没有主键、没有 NOT NULL 约束。

完整的建表示例

sql

CREATE TABLE IF NOT EXISTS student (
    id          INT UNSIGNED    NOT NULL AUTO_INCREMENT  COMMENT '学生ID，自增主键',
    name        VARCHAR(50)     NOT NULL                 COMMENT '学生姓名',
    gender      ENUM('M','F')   NOT NULL DEFAULT 'M'     COMMENT '性别：M=男 F=女',
    age         TINYINT UNSIGNED NOT NULL                COMMENT '年龄',
    email       VARCHAR(100)    NULL                     COMMENT '电子邮箱',
    phone       CHAR(11)        NULL DEFAULT NULL        COMMENT '手机号',
    class_id    INT UNSIGNED    NOT NULL DEFAULT 0       COMMENT '所属班级ID',
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',

    PRIMARY KEY (id),
    UNIQUE KEY uk_email (email),
    UNIQUE KEY uk_phone (phone),
    KEY idx_name (name),
    KEY idx_class_id (class_id)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='学生信息表';

开发规范：在团队开发中，建表语句应该对齐、有注释、有明确的约束。这样的 DDL 本身就是一份很好的文档。

7.2 列级约束 vs 表级约束

约束可以写在列定义的同一行（列级约束），也可以单独写在所有列定义之后（表级约束）。两者的效果是一样的，区别在于：

列级约束：紧跟在列定义后面，简洁，适合单列约束
表级约束：需要单独一行，显式指定列名。适合复合约束（多列组合的主键、唯一键、外键）

sql

-- 这两张表约束效果完全相同，但写法不同
-- 方式一：列级约束
CREATE TABLE t1 (
    id INT PRIMARY KEY,          -- 列级 PRIMARY KEY
    code VARCHAR(20) UNIQUE,     -- 列级 UNIQUE
    name VARCHAR(50) NOT NULL    -- 列级 NOT NULL（只有列级写法）
);

-- 方式二：表级约束
CREATE TABLE t2 (
    id INT,
    code VARCHAR(20),
    name VARCHAR(50) NOT NULL,
    PRIMARY KEY (id),             -- 表级 PRIMARY KEY
    UNIQUE (code)                 -- 表级 UNIQUE
);

-- 复合约束必须用表级写法
CREATE TABLE enrollments (
    student_id INT NOT NULL,
    course_id  INT NOT NULL,
    score      DECIMAL(4,1),
    PRIMARY KEY (student_id, course_id),  -- 复合主键：列级无法表示
    FOREIGN KEY (student_id) REFERENCES student(id),  -- 外键通常用表级
    FOREIGN KEY (course_id) REFERENCES course(id)
);

7.3 定义 PRIMARY KEY（主键）

单列主键

sql

-- 行内定义（最常用）
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- 等价于表级定义
CREATE TABLE users2 (
    id INT UNSIGNED AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    PRIMARY KEY (id)
);

复合主键

当单列不能唯一标识一行时，用多列组合：

sql

CREATE TABLE student_scores (
    student_id INT UNSIGNED NOT NULL,
    subject_id INT UNSIGNED NOT NULL,
    semester   VARCHAR(10) NOT NULL,
    score      DECIMAL(4,1),
    PRIMARY KEY (student_id, subject_id, semester)  -- 三列组合作为主键
);
-- 一个学生在同一学期同一科目只能有一条成绩记录

主键的设计原则

原则	说明
唯一性	主键必须唯一标识每一行
非空性	主键列自动 NOT NULL
稳定性	主键值不应该被修改（尽量不用业务数据做主键）
简洁性	主键越小越好（InnoDB 的聚簇索引中，主键决定数据的物理存储顺序）
递增性	自增整数主键写性能最好（新数据总是追加到 B+ 树的末尾）

7.4 AUTO_INCREMENT 详解

sql

-- 基本用法
CREATE TABLE articles (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL
);

INSERT INTO articles (title) VALUES ('文章1'), ('文章2'), ('文章3');

SELECT * FROM articles;
-- +----+---------+
-- | id | title   |
-- +----+---------+
-- |  1 | 文章1   |
-- |  2 | 文章2   |
-- |  3 | 文章3   |
-- +----+---------+

-- 获取最后插入的 AUTO_INCREMENT 值
SELECT LAST_INSERT_ID();

-- 手动指定自增值
INSERT INTO articles (id, title) VALUES (100, '手动指定ID');
-- 之后自动生成的值会从 101 开始

-- 修改自增起始值
ALTER TABLE articles AUTO_INCREMENT = 1000;
-- 之后插入从 1000 开始

-- 查看当前自增值
SELECT AUTO_INCREMENT FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'articles';

AUTO_INCREMENT 的规则和限制

每个表最多只能有一个 AUTO_INCREMENT 列
AUTO_INCREMENT 列必须被索引（通常是主键，也可以是普通索引）
AUTO_INCREMENT 列必须是 NOT NULL
删除行不会回收已使用的自增值（这是一个特性，不是 bug）
回滚事务后，已经分配的自增值也不会回收
如果插入时指定了大于当前 AUTO_INCREMENT 的值，自增值会被更新为该值 + 1
自增锁（auto-increment lock）在 MySQL 5.7 和 8.0 中有所变化（innodb_autoinc_lock_mode），影响着批量插入的性能

7.5 ENGINE 选择

sql

-- 查看支持的存储引擎
SHOW ENGINES;

-- 指定引擎建表
CREATE TABLE archives (
    id INT PRIMARY KEY AUTO_INCREMENT,
    data TEXT
) ENGINE=MyISAM;  -- 无事务、归档用

CREATE TABLE cache_data (
    `key` VARCHAR(100) PRIMARY KEY,
    value TEXT,
    expired_at DATETIME
) ENGINE=MEMORY;  -- 全内存表，重启数据消失

-- 查看表使用的引擎
SHOW TABLE STATUS LIKE 'archives'\G
-- 或
SELECT TABLE_NAME, ENGINE FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'my_database';

常用引擎速查：

引擎	特点	何时使用
InnoDB	事务、行级锁、外键、崩溃恢复	默认选择，99% 的表应该用它
MyISAM	无事务、表级锁、压缩表	只读归档表、数据仓库
MEMORY	数据在内存中，重启丢失	临时表、会话数据、缓存
ARCHIVE	高压缩比、只支持 INSERT/SELECT	日志归档
CSV	以 CSV 文件存储	数据交换

7.6 临时表（TEMPORARY TABLE）

临时表只在当前会话可见，会话结束时自动删除：

sql

-- 创建临时表
CREATE TEMPORARY TABLE temp_stats (
    category VARCHAR(50),
    total INT,
    average DECIMAL(10, 2)
);

-- 插入临时数据
INSERT INTO temp_stats VALUES ('电子产品', 150, 2999.99), ('图书', 80, 45.50);

-- 正常使用
SELECT * FROM temp_stats;

-- 临时表的特殊行为
-- 1. 可以和永久表同名（临时表会"遮蔽"永久表）
CREATE TABLE permanent_tbl (name VARCHAR(50));
CREATE TEMPORARY TABLE permanent_tbl (name VARCHAR(50));  -- 创建成功
-- 此时 SELECT * FROM permanent_tbl 查的是临时表
DROP TEMPORARY TABLE permanent_tbl;
-- 此时 SELECT * FROM permanent_tbl 查的是永久表（如果没有永久表，会报错）

-- 2. 断开连接后自动删除
-- 关闭 mysql 客户端再重连，temp_stats 表就不存在了

-- 3. SHOW TABLES 默认不显示临时表
SHOW TABLES;

-- 4. 删除临时表（加上 TEMPORARY 防止误删同名永久表）
DROP TEMPORARY TABLE IF EXISTS temp_stats;

临时表的典型用例

sql

-- 场景：计算各部门平均薪资，找出高于公司平均的部门
-- 用临时表存放中间结果

-- 步骤1：计算公司整体平均薪资
CREATE TEMPORARY TABLE company_avg AS
SELECT AVG(salary) AS avg_salary FROM employees;

-- 步骤2：各步查询
SELECT
    d.department_name,
    AVG(e.salary) AS dept_avg
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.id, d.department_name
HAVING AVG(e.salary) > (SELECT avg_salary FROM company_avg);

DROP TEMPORARY TABLE company_avg;

7.7 从已有表创建新表

MySQL 提供两种方式从已有表创建新表：

LIKE —— 复制结构（不含数据）

sql

-- 完全复制表结构（包括数据类型、索引、约束、AUTO_INCREMENT 等）
CREATE TABLE student_backup LIKE student;

-- 验证：两张表结构一样
DESC student;
DESC student_backup;

-- LIKE 复制了什么？
-- ✅ 列定义及数据类型
-- ✅ 索引（PRIMARY KEY, UNIQUE, INDEX, FULLTEXT）
-- ✅ 约束（NOT NULL, DEFAULT, FOREIGN KEY）
-- ✅ ENGINE、CHARSET、COMMENT 等表选项
-- ❌ 数据

-- 确认没有数据
SELECT COUNT(*) FROM student_backup;  -- 0

AS SELECT —— 复制结构 + 数据

sql

-- 复制结构 + 数据（注意：索引和约束不会被复制！）
CREATE TABLE student_copy AS SELECT * FROM student;

-- 验证：结构不完全一样！
DESC student;
DESC student_copy;
-- 你会发现：
-- ✅ 列定义和数据类型（基本正确，但 AUTO_INCREMENT 丢失）
-- ✅ 数据（完全复制）
-- ❌ PRIMARY KEY 丢失了！
-- ❌ UNIQUE 约束丢失了！
-- ❌ FOREIGN KEY 丢失了！
-- ❌ 索引丢失了！
-- ❌ AUTO_INCREMENT 丢失了！

-- 可以指定只复制部分列和部分行
CREATE TABLE student_young AS
SELECT id, name, age FROM student WHERE age < 25;

-- 可以加入计算列
CREATE TABLE student_summary AS
SELECT
    COUNT(*) AS total_students,
    AVG(age) AS avg_age
FROM student;

LIKE vs AS SELECT 对比

特性	CREATE TABLE ... LIKE	CREATE TABLE ... AS SELECT
复制数据	❌ 不复制	✅ 复制
复制索引	✅	❌
复制 PRIMARY KEY	✅	❌
复制 FOREIGN KEY	✅	❌
复制 AUTO_INCREMENT	✅	❌
复制约束	✅	❌（仅 NOT NULL 可能保留）
复制表选项（ENGINE 等）	✅	❌
可只复制部分列	❌（复制全部列）	✅
可加 WHERE 条件	❌（不复制数据）	✅
可创建计算列	❌	✅

最佳实践：如果需要完整克隆表结构，用 LIKE；如果需要基于查询结果创建表，用 AS SELECT，但记得手动添加必要的索引和约束。

7.8 ALTER TABLE 完整操作大全

ALTER TABLE 是 DDL 中最复杂的语句，因为它支持很多种操作。

添加列（ADD COLUMN）

sql

-- 在末尾添加列
ALTER TABLE student ADD COLUMN address VARCHAR(200);

-- 在最前面（FIRST）添加列
ALTER TABLE student ADD COLUMN uuid CHAR(36) FIRST;

-- 在指定列之后（AFTER）添加列
ALTER TABLE student ADD COLUMN birthday DATE AFTER name;

-- 一次添加多列
ALTER TABLE student
    ADD COLUMN nickname VARCHAR(50) AFTER name,
    ADD COLUMN remark TEXT AFTER address;

-- 添加列时指定约束和默认值
ALTER TABLE student ADD COLUMN status TINYINT NOT NULL DEFAULT 1 AFTER email;

删除列（DROP COLUMN）

sql

-- 删除单列
ALTER TABLE student DROP COLUMN remark;

-- 注意：删除列是不可逆的！数据永久丢失！
-- 如果该列被外键引用，必须先删除外键约束
-- 如果该列是索引的一部分，索引会被自动调整

修改列（MODIFY COLUMN）

MODIFY 改变列的数据类型和约束，但不改变列名：

sql

-- 修改数据类型（注意：可能造成数据截断！）
ALTER TABLE student MODIFY COLUMN name VARCHAR(100);

-- 修改约束（添加 NOT NULL + DEFAULT）
ALTER TABLE student MODIFY COLUMN email VARCHAR(100) NOT NULL DEFAULT 'unknown@example.com';

-- 修改多个列（MySQL 支持一次 ALTER 多个操作）
ALTER TABLE student
    MODIFY COLUMN age TINYINT UNSIGNED NOT NULL,
    MODIFY COLUMN phone CHAR(11) NULL;

-- ⚠️ 缩小 VARCHAR 长度可能导致数据被截断！
-- 如果 sql_mode 包含 STRICT_TRANS_TABLES，会报错阻止截断
-- 如果不包含，会截断并给警告（非常危险！）

重命名列（CHANGE COLUMN 和 RENAME COLUMN）

sql

-- 方式一：CHANGE COLUMN（同时修改列名和定义，必须重新指定数据类型）
-- CHANGE COLUMN 旧列名 新列名 新数据类型 [约束]
ALTER TABLE student CHANGE COLUMN address addr VARCHAR(200) NOT NULL;
-- 注意：即使只改名字不改类型，也必须把完整的类型和约束再写一遍！

-- 方式二：RENAME COLUMN（MySQL 8.0+ 新增，专门用于重命名）
ALTER TABLE student RENAME COLUMN addr TO address;
-- 这个更简洁，只改名字

-- 对比
-- CHANGE: 可以同时改名字 + 数据类型，但语法啰嗦。所有 MySQL 版本支持
-- RENAME: 只改名字，语法简洁。仅 MySQL 8.0+ 支持

表重命名（RENAME TO）

sql

-- 重命名表
ALTER TABLE student RENAME TO students;

-- 也可以用 RENAME TABLE（可以一次重命名多张表，且是原子操作！）
RENAME TABLE
    students TO student,
    old_name1 TO new_name1,
    old_name2 TO new_name2;
-- RENAME TABLE 可以同时重命名多张表，而且是原子操作——要么全部成功，要么全部失败

其他重要操作

sql

-- 修改表注释
ALTER TABLE student COMMENT = '学生信息表（已更新）';

-- 修改字符集
ALTER TABLE student CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 注意：DEFAULT CHARACTER SET（只改默认值）vs CONVERT TO CHARACTER SET（转换已有数据）

-- 修改存储引擎
ALTER TABLE student ENGINE = InnoDB;
-- 会重建整张表，大表操作耗时长且锁表

-- 修改自增起始值
ALTER TABLE student AUTO_INCREMENT = 10000;

-- 添加/删除索引（后面章节会细讲）
ALTER TABLE student ADD INDEX idx_name (name);
ALTER TABLE student DROP INDEX idx_name;

-- 添加/删除主键
ALTER TABLE student ADD PRIMARY KEY (id);
ALTER TABLE student DROP PRIMARY KEY;

-- 添加/删除外键
ALTER TABLE student ADD CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES classes(id);
ALTER TABLE student DROP FOREIGN KEY fk_class;

7.9 DROP TABLE 删除表

sql

-- 删除单张表
DROP TABLE student_copy;

-- 删除多张表
DROP TABLE t1, t2, t3;

-- 安全删除：加上 IF EXISTS
DROP TABLE IF EXISTS student_copy;
-- 表不存在也不会报错，适合自动化脚本

-- 删除临时表
DROP TEMPORARY TABLE IF EXISTS temp_stats;
-- 加上 TEMPORARY 防止误删同名永久表

DROP TABLE 同样没有回收站！删除的表和数据无法恢复（除非有备份）。生产环境中删除表是最需要谨慎的操作之一。

7.10 TRUNCATE TABLE vs DELETE

TRUNCATE TABLE 和 DELETE FROM table 都能清空表，但机制完全不同：

sql

-- 创建测试表
CREATE TABLE truncate_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data VARCHAR(100)
);

INSERT INTO truncate_test (data) VALUES ('a'), ('b'), ('c');

-- 方式一：TRUNCATE（DDL）
TRUNCATE TABLE truncate_test;
-- 结果：表空了，AUTO_INCREMENT 重置为 1

-- 方式二：DELETE（DML）
DELETE FROM truncate_test;
-- 结果：表空了，但 AUTO_INCREMENT 不会重置

完整对比

维度	TRUNCATE	DELETE (无 WHERE)
SQL 分类	DDL	DML
能否回滚	❌ 不能（隐式提交）	✅ 可以（在事务中）
触发触发器	❌ 不触发	✅ 逐行触发
AUTO_INCREMENT	重置为 1	继续原来的值
执行速度	很快（直接释放空间）	慢（逐行删除 + 写 undo log）
WHERE 条件	❌ 不支持	✅ 支持
WHERE id > 10	❌ 只能全表	✅ 可以部分删除
空间释放	立即归还给 OS（取决于引擎）	空间保留给后续插入
外键约束	如果被其他表的外键引用，会失败	如果外键约束阻止，会失败

sql

-- 验证 TRUNCATE 不能被回滚
START TRANSACTION;
TRUNCATE TABLE truncate_test;
ROLLBACK;  -- ❌ TRUNCATE 隐式提交了，回滚无效！

-- 验证 DELETE 可以被回滚
START TRANSACTION;
DELETE FROM truncate_test;
ROLLBACK;  -- ✅ 数据恢复！
SELECT COUNT(*) FROM truncate_test;  -- 0

选择原则：需要回滚能力 → DELETE；要清空大表并重置自增 → TRUNCATE；只删除部分行 → DELETE ... WHERE。

7.11 查看表信息

sql

-- 查看表结构（简易版）
DESC student;
DESCRIBE student;
SHOW COLUMNS FROM student;

-- 查看完整建表语句
SHOW CREATE TABLE student\G
-- *************************** 1. row ***************************
--        Table: student
-- Create Table: CREATE TABLE `student` (
--   `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '学生ID，自增主键',
--   `name` varchar(50) NOT NULL COMMENT '学生姓名',
--   ...
-- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ...

-- 查看表详细信息
SHOW TABLE STATUS LIKE 'student'\G
-- 包含：Engine, Version, Row_format, Rows, Avg_row_length,
-- Data_length, Max_data_length, Index_length, Data_free,
-- Auto_increment, Create_time, Update_time, Check_time,
-- Collation, Checksum, Create_options, Comment

-- 列出当前数据库的所有表
SHOW TABLES;

-- 模式匹配过滤
SHOW TABLES LIKE '%student%';

-- 从 information_schema 查询表信息
SELECT TABLE_NAME, ENGINE, TABLE_ROWS, AVG_ROW_LENGTH,
       DATA_LENGTH, INDEX_LENGTH, CREATE_TIME
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database_name';

7.12 综合示例：设计一个课程管理系统

把本章学到的建表知识综合运用起来：

sql

-- 1. 学生表
CREATE TABLE IF NOT EXISTS students (
    id          INT UNSIGNED    NOT NULL AUTO_INCREMENT  COMMENT '学生ID',
    sno         CHAR(10)        NOT NULL                 COMMENT '学号',
    name        VARCHAR(50)     NOT NULL                 COMMENT '姓名',
    gender      ENUM('M','F')   NOT NULL                 COMMENT '性别',
    birth_date  DATE            NULL                     COMMENT '出生日期',
    dept_id     INT UNSIGNED    NOT NULL                 COMMENT '院系ID',
    created_at  TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_sno (sno),
    KEY idx_name (name),
    KEY idx_dept (dept_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

-- 2. 院系表
CREATE TABLE IF NOT EXISTS departments (
    id      INT UNSIGNED    NOT NULL AUTO_INCREMENT  COMMENT '院系ID',
    name    VARCHAR(100)    NOT NULL                 COMMENT '院系名称',
    code    CHAR(4)         NOT NULL                 COMMENT '院系代码',
    PRIMARY KEY (id),
    UNIQUE KEY uk_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='院系表';

-- 3. 课程表
CREATE TABLE IF NOT EXISTS courses (
    id          INT UNSIGNED    NOT NULL AUTO_INCREMENT  COMMENT '课程ID',
    name        VARCHAR(100)    NOT NULL                 COMMENT '课程名',
    credit      DECIMAL(3,1)    NOT NULL DEFAULT 0.0     COMMENT '学分',
    dept_id     INT UNSIGNED    NOT NULL                 COMMENT '开课院系',
    teacher_id  INT UNSIGNED    NULL                     COMMENT '授课教师',
    PRIMARY KEY (id),
    KEY idx_dept (dept_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程表';

-- 4. 选课表（多对多关系）
CREATE TABLE IF NOT EXISTS enrollments (
    student_id  INT UNSIGNED    NOT NULL  COMMENT '学生ID',
    course_id   INT UNSIGNED    NOT NULL  COMMENT '课程ID',
    semester    VARCHAR(20)     NOT NULL  COMMENT '学期（如 2026-春季）',
    score       DECIMAL(4,1)    NULL      COMMENT '成绩',
    PRIMARY KEY (student_id, course_id, semester),
    KEY idx_course (course_id),
    CONSTRAINT fk_ent_student FOREIGN KEY (student_id) REFERENCES students(id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_ent_course  FOREIGN KEY (course_id)  REFERENCES courses(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='选课记录表';

-- 查看所有表的创建语句
SHOW CREATE TABLE students\G
SHOW CREATE TABLE departments\G
SHOW CREATE TABLE courses\G
SHOW CREATE TABLE enrollments\G

常见错误

错误 1：CREATE TABLE AS SELECT 后忘记加索引

现象：用 CREATE TABLE t2 AS SELECT * FROM t1 创建了新表，查询很慢，发现 t2 上没有索引。

原因：AS SELECT 不会复制原表的索引和约束。

解决：创建后手动添加必要的索引。

sql

ALTER TABLE t2 ADD PRIMARY KEY (id);
ALTER TABLE t2 ADD INDEX idx_name (name);

错误 2：修改 VARCHAR 长度后数据被截断

现象：ALTER TABLE t MODIFY COLUMN name VARCHAR(10); 之后，原本长度超过 10 的数据被静默截断了。

原因：如果 sql_mode 不严格，MySQL 允许截断数据并只给一个警告。

解决：修改前先确认没有数据会超出新长度。

sql

-- 先检查是否有超长的数据
SELECT MAX(CHAR_LENGTH(name)) FROM t;
-- 确保 sql_mode 包含 STRICT_TRANS_TABLES
SET sql_mode = 'STRICT_TRANS_TABLES';

错误 3：DROP TABLE 和 TRUNCATE TABLE 混淆

现象：想清空表数据但保留表结构，却误用了 DROP TABLE。

原因：没有理解两个操作的本质区别（DROP 删除表结构和数据，TRUNCATE 只清空数据）。

助记：

DROP —— 删除整个表（连"壳"都没了）
TRUNCATE —— 清空表内容（"壳"还在）
DELETE —— 逐行删除内容（可控、可回滚）

错误 4：忘记在 ALTER TABLE 时指定列位置，导致新列在末尾

现象：ALTER TABLE student ADD COLUMN email VARCHAR(100); 后，email 总是在最后一列。但业务上希望它在 name 旁边。

原因：默认 ADD COLUMN 将列添加在最后。

解决：使用 FIRST 或 AFTER 指定位置：

sql

ALTER TABLE student ADD COLUMN email VARCHAR(100) AFTER name;

错误 5：大表直接 ALTER TABLE 导致业务中断

现象：对几百万行的表执行 ALTER TABLE（如添加索引、修改列类型），操作执行了 30 分钟，期间表被锁定，业务请求大量超时。

原因：某些 ALTER TABLE 操作会锁表（复制整表数据）。MySQL 5.6+ 支持 Online DDL，但并非所有操作都是 Online 的。

解决：

使用 ALGORITHM=INPLACE（尽量在线完成，不锁表）
使用 LOCK=NONE（完全不锁表）
或用第三方工具（pt-online-schema-change、gh-ost）做无锁表变更

sql

ALTER TABLE large_table
    ADD COLUMN new_column INT,
    ALGORITHM=INPLACE, LOCK=NONE;

本章练习

建表综合练习：根据以下需求创建完整的建表语句（要求：有主键、有合适的约束、有注释）：
图书管理系统
- books 表：图书 ID、ISBN（唯一）、书名、作者、出版社、出版年份、价格（精确到分）、库存数量、创建时间
- members 表：会员 ID、姓名、手机号（唯一）、邮箱、注册日期、会员等级（普通/银卡/金卡）
- borrow_records 表：记录 ID、会员 ID、图书 ID、借书日期、应还日期、实际还书日期、是否逾期
复制表结构：用 CREATE TABLE ... LIKE 复制 books 表的完整结构到 books_backup，用 DESC 对比两张表，确认索引和约束都被复制了。然后用 CREATE TABLE ... AS SELECT * FROM books WHERE 1=0 再复制一次，对比三张表的差异。
ALTER TABLE 练习：在 members 表上依次执行以下操作： (a) 添加列 gender CHAR(1)，放在 name 后面 (b) 将 email 列的数据类型改为 VARCHAR(150) (c) 添加列 address VARCHAR(200)，默认值为空字符串 (d) 删除 address 列每次操作后用 DESC 验证。
TRUNCATE vs DELETE 实验：创建一张测试表，插入 3 条数据。开启事务 → TRUNCATE → 回滚，看数据是否恢复。再开启事务 → DELETE → 回滚，看数据是否恢复。记录你的结论。
RENAME TABLE 原子性：创建三张表，用一条 RENAME TABLE ... TO ... 同时重命名它们。解释为什么原子性重命名在某些场景下很有用（比如表替换的零停机部署）。
临时表练习：创建一个永久表 employees（含 salary 列），插入 5 条记录。创建一个临时表，存入 employees 的平均薪资。查询哪些员工的薪资高于平均薪资，用到临时表。断开重连后确认临时表已自动删除。

第7章：创建与管理表 ​

本章目标 ​

前置知识 ​

7.1 CREATE TABLE 完整语法 ​

语法结构概览 ​

最简单的建表 ​

完整的建表示例 ​

7.2 列级约束 vs 表级约束 ​

7.3 定义 PRIMARY KEY（主键） ​

单列主键 ​

复合主键 ​

主键的设计原则 ​

7.4 AUTO_INCREMENT 详解 ​

AUTO_INCREMENT 的规则和限制 ​

7.5 ENGINE 选择 ​

7.6 临时表（TEMPORARY TABLE） ​

临时表的典型用例 ​

7.7 从已有表创建新表 ​

LIKE —— 复制结构（不含数据） ​

AS SELECT —— 复制结构 + 数据 ​

LIKE vs AS SELECT 对比 ​

7.8 ALTER TABLE 完整操作大全 ​

添加列（ADD COLUMN） ​

删除列（DROP COLUMN） ​

修改列（MODIFY COLUMN） ​

重命名列（CHANGE COLUMN 和 RENAME COLUMN） ​

表重命名（RENAME TO） ​

其他重要操作 ​

7.9 DROP TABLE 删除表 ​

7.10 TRUNCATE TABLE vs DELETE ​

完整对比 ​

7.11 查看表信息 ​

7.12 综合示例：设计一个课程管理系统 ​

常见错误 ​

错误 1：CREATE TABLE AS SELECT 后忘记加索引 ​

错误 2：修改 VARCHAR 长度后数据被截断 ​

错误 3：DROP TABLE 和 TRUNCATE TABLE 混淆 ​

错误 4：忘记在 ALTER TABLE 时指定列位置，导致新列在末尾 ​

错误 5：大表直接 ALTER TABLE 导致业务中断 ​

本章练习 ​