---
title: "08-数据表的修改与删除"
created: 2026-01-07
tags:
  - 项目筑基
---

# 数据表的修改与删除

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

```sql
-- 创建数据库
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 选择数据库**

```sql
USE 学校管理系统;
```

#### **6.11.2 查看表信息**

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

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

-- 查看所有表
SHOW TABLES;

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

#### **6.11.3 列的修改**

##### **添加新列**

```sql
-- 添加单列（默认添加到最后）
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)
);
```

##### **修改列的类型**

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

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

##### **修改列名**

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

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

##### **删除列**

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

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

##### **修改列的默认值**

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

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

#### **6.11.4 约束的修改**

##### **添加主键约束**

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

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

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

##### **删除主键约束**

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

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

##### **添加外键约束**

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

##### **删除外键约束**

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

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

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

##### **添加唯一约束**

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

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

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

##### **删除唯一约束**

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

##### **添加检查约束**

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

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

##### **删除检查约束**

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

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

##### **添加/删除默认约束**

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

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

#### **6.11.5 修改表名**

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

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

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

#### **6.11.6 查看约束信息**

```sql
-- 查看表的所有约束
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 删除单个表**

```sql
DROP TABLE 课程;
```

#### **6.12.2 安全删除**

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

#### **6.12.3 删除多个表**

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

#### **6.12.4 删除有外键关联的表**

![[image-1d17a86f.png]]

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

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

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

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

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

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

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

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

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

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

**TRUNCATE vs DELETE vs DROP：**

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

### **6.13 快速参考卡片**

#### **数据库操作**

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

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

-- 使用
USE 数据库名;

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

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

#### **数据表操作**

```sql
-- 创建
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` |

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