数据表的修改与删除

6.10 综合示例：创建完整的表结构

-- 创建数据库
CREATE DATABASE IF NOT EXISTS 学校管理系统
CHARACTER SET utf8mb4;

USE 学校管理系统;

-- 学院表
CREATE TABLE 学院
(
    学院编号 CHAR(2) PRIMARY KEY,
    学院名称 VARCHAR(30) NOT NULL UNIQUE,
    学院电话 CHAR(12),
    学院地址 VARCHAR(100)
);

-- 教师表
CREATE TABLE 教师
(
    教师编号 CHAR(10) PRIMARY KEY,
    姓名 VARCHAR(20) NOT NULL,
    性别 ENUM('男', '女'),
    出生日期 DATE,
    职称 VARCHAR(20),
    学院编号 CHAR(2),
    入职日期 DATE DEFAULT (CURRENT_DATE),
    CONSTRAINT FK_教师_学院
        FOREIGN KEY (学院编号) REFERENCES 学院(学院编号)
        ON UPDATE CASCADE
);

-- 课程表
CREATE TABLE 课程
(
    课程编号 CHAR(8) PRIMARY KEY,
    课程名称 VARCHAR(50) NOT NULL,
    学时数 INT CHECK (学时数 > 0 AND 学时数 <= 200),
    学分数 DECIMAL(3,1) CHECK (学分数 > 0 AND 学分数 <= 10),
    课程性质 VARCHAR(10) DEFAULT '必修',
    课程介绍 TEXT
);

-- 学生表
CREATE TABLE 学生
(
    学号 CHAR(10) PRIMARY KEY,
    姓名 VARCHAR(20) NOT NULL,
    性别 ENUM('男', '女'),
    出生日期 DATE,
    身份证号 CHAR(18) UNIQUE,
    班级 VARCHAR(30),
    学院编号 CHAR(2),
    入学日期 DATE,
    CONSTRAINT FK_学生_学院
        FOREIGN KEY (学院编号) REFERENCES 学院(学院编号)
);

-- 选课成绩表
CREATE TABLE 选课成绩
(
    学号 CHAR(10),
    课程编号 CHAR(8),
    成绩 DECIMAL(5,2) CHECK (成绩 >= 0 AND 成绩 <= 100),
    选课日期 DATE DEFAULT (CURRENT_DATE),
    CONSTRAINT PK_选课成绩 PRIMARY KEY (学号, 课程编号),
    CONSTRAINT FK_选课_学生
        FOREIGN KEY (学号) REFERENCES 学生(学号)
        ON DELETE CASCADE,
    CONSTRAINT FK_选课_课程
        FOREIGN KEY (课程编号) REFERENCES 课程(课程编号)
);

6.11 数据表的修改

6.11.1 使用 USE 选择数据库

USE 学校管理系统;

6.11.2 查看表信息

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

-- 查看建表语句
SHOW CREATE TABLE 学生;

-- 查看所有表
SHOW TABLES;

-- 查看表的索引
SHOW INDEX FROM 学生;

6.11.3 列的修改

添加新列

-- 添加单列（默认添加到最后）
ALTER TABLE 学生
ADD 手机号 CHAR(11);

-- 添加到指定位置
ALTER TABLE 学生
ADD 邮箱 VARCHAR(50) AFTER 姓名;

-- 添加到第一列
ALTER TABLE 学生
ADD 记录ID INT FIRST;

-- 同时添加多列
ALTER TABLE 学生
ADD (
    QQ号 VARCHAR(15),
    微信号 VARCHAR(30)
);

修改列的类型

-- 修改列的数据类型
ALTER TABLE 学生
MODIFY 手机号 VARCHAR(15);

-- 修改列的数据类型并设置约束
ALTER TABLE 学生
MODIFY 手机号 VARCHAR(15) NOT NULL;

修改列名

-- 修改列名（同时可以改类型）
ALTER TABLE 学生
CHANGE 手机号 联系电话 VARCHAR(15);

-- MySQL 8.0+ 可以只改列名
ALTER TABLE 学生
RENAME COLUMN 联系电话 TO 手机号;

删除列

-- 删除单列
ALTER TABLE 学生
DROP COLUMN QQ号;

-- 删除多列
ALTER TABLE 学生
DROP COLUMN 微信号,
DROP COLUMN 邮箱;

修改列的默认值

-- 设置默认值
ALTER TABLE 课程
ALTER COLUMN 课程性质 SET DEFAULT '选修';

-- 删除默认值
ALTER TABLE 课程
ALTER COLUMN 课程性质 DROP DEFAULT;

6.11.4 约束的修改

添加主键约束

-- 添加主键
ALTER TABLE 表名
ADD PRIMARY KEY (列名);

-- 添加命名的主键
ALTER TABLE 表名
ADD CONSTRAINT PK_表名 PRIMARY KEY (列名);

-- 添加复合主键
ALTER TABLE 表名
ADD PRIMARY KEY (列名1, 列名2);

删除主键约束

-- 如果主键是自增的，需要先去掉自增
ALTER TABLE 表名
MODIFY 列名 INT;  -- 去掉 AUTO_INCREMENT

-- 删除主键
ALTER TABLE 表名
DROP PRIMARY KEY;

添加外键约束

ALTER TABLE 教师
ADD CONSTRAINT FK_教师_学院
    FOREIGN KEY (学院编号) REFERENCES 学院(学院编号)
    ON UPDATE CASCADE
    ON DELETE SET NULL;

删除外键约束

-- 查看外键名称
SHOW CREATE TABLE 教师;

-- 删除外键（使用约束名）
ALTER TABLE 教师
DROP FOREIGN KEY FK_教师_学院;

-- 注意：删除外键后，索引可能还在，需要单独删除
ALTER TABLE 教师
DROP INDEX FK_教师_学院;

添加唯一约束

-- 添加唯一约束
ALTER TABLE 学生
ADD UNIQUE (身份证号);

-- 添加命名的唯一约束
ALTER TABLE 学生
ADD CONSTRAINT UK_学生_邮箱 UNIQUE (邮箱);

-- 添加复合唯一约束
ALTER TABLE 学生
ADD CONSTRAINT UK_班级姓名 UNIQUE (班级, 姓名);

删除唯一约束

-- 删除唯一约束（通过删除索引）
ALTER TABLE 学生
DROP INDEX UK_学生_邮箱;

添加检查约束

-- MySQL 8.0.16+
ALTER TABLE 学生
ADD CONSTRAINT CK_学生_年龄 CHECK (年龄 >= 0 AND 年龄 <= 150);

ALTER TABLE 成绩
ADD CHECK (成绩 BETWEEN 0 AND 100);

删除检查约束

ALTER TABLE 学生
DROP CHECK CK_学生_年龄;

-- 或者使用约束名
ALTER TABLE 表名
DROP CONSTRAINT 约束名;

添加/删除默认约束

-- 添加默认约束
ALTER TABLE 课程
ALTER 课程性质 SET DEFAULT '必修';

-- 删除默认约束
ALTER TABLE 课程
ALTER 课程性质 DROP DEFAULT;

6.11.5 修改表名

-- 方法1
RENAME TABLE 旧表名 TO 新表名;

-- 方法2
ALTER TABLE 旧表名 RENAME TO 新表名;

-- 批量重命名
RENAME TABLE
    表1 TO 新表1,
    表2 TO 新表2,
    表3 TO 新表3;

6.11.6 查看约束信息

-- 查看表的所有约束
SELECT
    CONSTRAINT_NAME,
    CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = '学校管理系统'
  AND TABLE_NAME = '学生';

-- 查看外键约束详情
SELECT
    CONSTRAINT_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = '学校管理系统'
  AND TABLE_NAME = '教师'
  AND REFERENCED_TABLE_NAME IS NOT NULL;

6.12 数据表的删除

6.12.1 删除单个表

DROP TABLE 课程;

6.12.2 安全删除

-- 存在才删除，避免报错
DROP TABLE IF EXISTS 课程;

6.12.3 删除多个表

DROP TABLE IF EXISTS 表1, 表2, 表3;

6.12.4 删除有外键关联的表

💡 删除顺序很重要：先删从表，再删主表

-- 正确的删除顺序
DROP TABLE IF EXISTS 选课成绩;  -- 从表
DROP TABLE IF EXISTS 学生;      -- 从表
DROP TABLE IF EXISTS 教师;      -- 从表
DROP TABLE IF EXISTS 课程;      -- 独立表
DROP TABLE IF EXISTS 学院;      -- 主表

6.12.5 强制删除（禁用外键检查）

-- 临时禁用外键检查
SET FOREIGN_KEY_CHECKS = 0;

-- 删除表（不受外键约束限制）
DROP TABLE IF EXISTS 学院;
DROP TABLE IF EXISTS 学生;

-- 重新启用外键检查
SET FOREIGN_KEY_CHECKS = 1;

⚠️ 警告：禁用外键检查可能导致数据不一致，谨慎使用！

6.12.6 清空表数据（保留结构）

-- TRUNCATE：快速清空，重置自增值
TRUNCATE TABLE 学生;

-- DELETE：逐行删除，不重置自增值
DELETE FROM 学生;

TRUNCATE vs DELETE vs DROP：

操作	保留结构	保留数据	重置自增	可回滚
DROP	✗	✗	-	✗
TRUNCATE	✓	✗	✓	✗
DELETE	✓	✗	✗	✓

6.13 快速参考卡片

数据库操作

-- 创建
CREATE DATABASE 数据库名 CHARACTER SET utf8mb4;

-- 查看
SHOW DATABASES;
SHOW CREATE DATABASE 数据库名;

-- 使用
USE 数据库名;

-- 修改
ALTER DATABASE 数据库名 CHARACTER SET utf8mb4;

-- 删除
DROP DATABASE IF EXISTS 数据库名;

数据表操作

-- 创建
CREATE TABLE 表名 (
    列名 类型 约束,
    ...
);

-- 查看
SHOW TABLES;
DESC 表名;
SHOW CREATE TABLE 表名;

-- 修改结构
ALTER TABLE 表名 ADD 列名 类型;
ALTER TABLE 表名 MODIFY 列名 新类型;
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型;
ALTER TABLE 表名 DROP COLUMN 列名;

-- 删除
DROP TABLE IF EXISTS 表名;
TRUNCATE TABLE 表名;

约束速查表

约束类型	添加语法	删除语法
PRIMARY KEY	ADD PRIMARY KEY (列)	DROP PRIMARY KEY
FOREIGN KEY	ADD FOREIGN KEY (列) REFERENCES 表(列)	DROP FOREIGN KEY 约束名
UNIQUE	ADD UNIQUE (列)	DROP INDEX 索引名
CHECK	ADD CHECK (条件)	DROP CHECK 约束名
DEFAULT	ALTER 列 SET DEFAULT 值	ALTER 列 DROP DEFAULT
NOT NULL	MODIFY 列 类型 NOT NULL	MODIFY 列 类型 NULL

---

⬅️ 数据表的创建与完整性约束 🏠 00-数据库 ➡️ 数据的增删改