---
title: "07-数据表的创建与完整性约束"
created: 2026-01-07
tags:
  - 项目筑基
---

# 数据表的创建与完整性约束

### **6.8 数据表的创建**

#### **6.8.1 选择数据库**

在创建表之前，必须先选择数据库：

```sql
USE 学生管理;
```

#### **6.8.2 数据类型**

##### **整数类型**

| **类型** | **字节** | **范围（有符号）** | **范围（无符号）** |
| --- | --- | --- | --- |
| **TINYINT** | 1 | -128 ~ 127 | 0 ~ 255 |
| **SMALLINT** | 2 | -32768 ~ 32767 | 0 ~ 65535 |
| **MEDIUMINT** | 3 | -8388608 ~ 8388607 | 0 ~ 16777215 |
| **INT** | 4 | -2^31 ~ 2^31-1 | 0 ~ 2^32-1 |
| **BIGINT** | 8 | -2^63 ~ 2^63-1 | 0 ~ 2^64-1 |

```sql
-- 无符号整数示例
年龄 TINYINT UNSIGNED  -- 0-255，适合存储年龄
```

##### **浮点数类型**

| **类型** | **说明** |
| --- | --- |
| **FLOAT** | 单精度浮点数 |
| **DOUBLE** | 双精度浮点数 |
| **DECIMAL(M,D)** | 精确小数，M是总位数，D是小数位数 |

```sql
-- 货币推荐使用 DECIMAL
价格 DECIMAL(10,2)  -- 最大99999999.99
```

##### **字符串类型**

| **类型** | **说明** | **最大长度** |
| --- | --- | --- |
| **CHAR(n)** | 定长字符串 | 255字符 |
| **VARCHAR(n)** | 变长字符串 | 65535字节 |
| **TEXT** | 长文本 | 65535字节 |
| **MEDIUMTEXT** | 中等长度文本 | 16MB |
| **LONGTEXT** | 超长文本 | 4GB |

```mermaid
flowchart LR
    subgraph "CHAR vs VARCHAR"
        C["CHAR(10) 存储 'abc'<br>→ 占用10个字符空间（补空格）"]
        V["VARCHAR(10) 存储 'abc'<br>→ 占用3个字符 + 1字节长度标识"]
    end
```

| **类型** | **适用场景** |
| --- | --- |
| **CHAR** | 固定长度：身份证号、手机号、邮编 |
| **VARCHAR** | 可变长度：姓名、地址、备注 |

##### **日期时间类型**

| **类型** | **格式** | **范围** |
| --- | --- | --- |
| **DATE** | YYYY-MM-DD | 1000-01-01 ~ 9999-12-31 |
| **TIME** | HH:MM:SS | -838:59:59 ~ 838:59:59 |
| **DATETIME** | YYYY-MM-DD HH:MM:SS | 1000年 ~ 9999年 |
| **TIMESTAMP** | YYYY-MM-DD HH:MM:SS | 1970年 ~ 2038年 |
| **YEAR** | YYYY | 1901 ~ 2155 |

```sql
-- TIMESTAMP 会自动转换时区，DATETIME 不会
创建时间 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
```

##### **其他类型**

| **类型** | **说明** |
| --- | --- |
| **BLOB** | 二进制大对象 |
| **ENUM** | 枚举类型 |
| **SET** | 集合类型 |
| **JSON** | JSON格式数据（MySQL 5.7+） |

```sql
-- ENUM 示例
性别 ENUM('男', '女')

-- JSON 示例
配置信息 JSON
```

#### **6.8.3 创建表的基本语法**

```sql
USE 学生管理;

CREATE TABLE 学生
(
    学号 CHAR(10),
    姓名 VARCHAR(20),
    性别 CHAR(2),
    年龄 INT,
    入学日期 DATE
);
```

#### **6.8.4 创建表的完整示例**

```sql
USE 学生管理;

CREATE TABLE 学生
(
    学号 CHAR(10) NOT NULL,
    姓名 VARCHAR(20) NOT NULL,
    性别 CHAR(2),
    出生日期 DATE,
    班级 VARCHAR(30),
    联系电话 CHAR(11),
    备注 TEXT
);
```

**查看表结构：**

```sql
DESC 学生;
-- 或者
SHOW CREATE TABLE 学生;
```

![[image-8b517709.png]]

#### **6.8.5 计算列（生成列）**

> 💡 MySQL 5.7+ 支持生成列

```sql
CREATE TABLE 商品
(
    商品编号 CHAR(10),
    商品名称 VARCHAR(50),
    单价 DECIMAL(10,2),
    数量 INT,
    总价 DECIMAL(12,2) AS (单价 * 数量) STORED
);
```

| **生成列类型** | **说明** |
| --- | --- |
| **VIRTUAL** | 虚拟列，查询时计算，不占存储空间 |
| **STORED** | 存储列，插入/更新时计算，占用存储空间 |

```sql
-- 字符串连接示例
CREATE TABLE 员工
(
    姓 VARCHAR(10),
    名 VARCHAR(10),
    全名 VARCHAR(20) AS (CONCAT(姓, 名)) VIRTUAL
);
```

### **6.9 完整性约束**

![[image-04cd276c.png]]

#### **6.9.1 空值约束（NULL / NOT NULL）**

```sql
CREATE TABLE 教师
(
    教师编号 CHAR(10) NOT NULL,
    姓名 VARCHAR(20) NOT NULL,
    性别 CHAR(2),           -- 默认允许NULL
    联系电话 CHAR(11) NULL  -- 显式允许NULL
);
```

**测试:**

```sql
-- 成功
INSERT INTO 教师(教师编号, 姓名) VALUES ('T001', '张老师');

-- 失败：Column '姓名' cannot be null
INSERT INTO 教师(教师编号) VALUES ('T002');
```

![[image-417e415a.png]]

#### **6.9.2 主键约束（PRIMARY KEY）**

##### **单列主键**

```sql
-- 写法1：列级约束
CREATE TABLE 学生
(
    学号 CHAR(10) PRIMARY KEY,
    姓名 VARCHAR(20) NOT NULL
);

-- 写法2：表级约束
CREATE TABLE 学生
(
    学号 CHAR(10),
    姓名 VARCHAR(20) NOT NULL,
    PRIMARY KEY (学号)
);

-- 写法3：命名约束
CREATE TABLE 学生
(
    学号 CHAR(10),
    姓名 VARCHAR(20) NOT NULL,
    CONSTRAINT PK_学生 PRIMARY KEY (学号)
);
```

##### **复合主键（联合主键）**

```sql
CREATE TABLE 选课成绩
(
    学号 CHAR(10),
    课程编号 CHAR(8),
    成绩 DECIMAL(5,2),
    CONSTRAINT PK_选课成绩 PRIMARY KEY (学号, 课程编号)
);
```

**复合主键说明：**

| **学号** | **课程编号** | **成绩** | **是否允许** |
| --- | --- | --- | --- |
| S001 | C001 | 85 | ✓ |
| S001 | C002 | 90 | ✓ 学号相同，课程不同，允许 |
| S002 | C001 | 88 | ✓ 课程相同，学号不同，允许 |
| S001 | C001 | 92 | ✗ 两个都相同，不允许！ |

##### **自增主键**

```sql
CREATE TABLE 订单
(
    订单ID INT PRIMARY KEY AUTO_INCREMENT,
    订单日期 DATE,
    客户名称 VARCHAR(50)
);

-- 插入时不需要指定订单ID
INSERT INTO 订单(订单日期, 客户名称) VALUES ('2024-01-15', '张三');
INSERT INTO 订单(订单日期, 客户名称) VALUES ('2024-01-16', '李四');
```

> 💡 **AUTO\_INCREMENT 特点：**
>
> - 每个表只能有一个自增列
> - 自增列必须是主键或唯一键
> - 默认从1开始，每次增加1
> - 可以手动指定起始值：`AUTO_INCREMENT = 1000`

#### **6.9.3 外键约束（FOREIGN KEY）**

##### **创建主表和从表**

```sql
-- 步骤1：创建主表（被引用的表）
CREATE TABLE 学院
(
    学院编号 CHAR(2) PRIMARY KEY,
    学院名称 VARCHAR(30) NOT NULL
);

-- 步骤2：创建从表（引用外键的表）
CREATE TABLE 教师
(
    教师编号 CHAR(10) PRIMARY KEY,
    姓名 VARCHAR(20) NOT NULL,
    学院编号 CHAR(2),
    CONSTRAINT FK_教师_学院
        FOREIGN KEY (学院编号) REFERENCES 学院(学院编号)
);
```

**外键约束关系图：**

![[image-d2e83e6f.png]]

##### **外键约束的作用**

```sql
-- 在主表插入数据
INSERT INTO 学院 VALUES ('01', '计算机学院');
INSERT INTO 学院 VALUES ('02', '数学学院');
INSERT INTO 学院 VALUES ('03', '外语学院');

-- 在从表插入数据
INSERT INTO 教师 VALUES ('T001', '张老师', '01');  -- ✓ 成功
INSERT INTO 教师 VALUES ('T002', '李老师', '02');  -- ✓ 成功
INSERT INTO 教师 VALUES ('T003', '王老师', '04');  -- ✗ 失败！学院04不存在
```

![[image-d1cac2fc.png]]

##### **级联操作**

```sql
CREATE TABLE 教师
(
    教师编号 CHAR(10) PRIMARY KEY,
    姓名 VARCHAR(20) NOT NULL,
    学院编号 CHAR(2),
    CONSTRAINT FK_教师_学院
        FOREIGN KEY (学院编号) REFERENCES 学院(学院编号)
        ON UPDATE CASCADE    -- 级联更新
        ON DELETE CASCADE    -- 级联删除
);
```

**级联操作选项：**

| **选项** | **说明** |
| --- | --- |
| **CASCADE** | 级联操作（删除/更新主表时，从表跟着变） |
| **SET NULL** | 设为NULL（主表删除/更新时，从表外键设为NULL） |
| **RESTRICT** | 限制（默认，主表有关联数据时不能删除/更新） |
| **NO ACTION** | 同 RESTRICT |
| **SET DEFAULT** | 设为默认值（InnoDB不支持） |

###### **级联操作测试**

```sql
-- 创建测试表
CREATE TABLE 部门
(
    部门编号 CHAR(2) PRIMARY KEY,
    部门名称 VARCHAR(30)
);

CREATE TABLE 员工
(
    员工编号 CHAR(10) PRIMARY KEY,
    姓名 VARCHAR(20),
    部门编号 CHAR(2),
    FOREIGN KEY (部门编号) REFERENCES 部门(部门编号)
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

-- 插入测试数据
INSERT INTO 部门 VALUES ('01', '技术部');
INSERT INTO 员工 VALUES ('E001', '张三', '01');

-- 测试级联更新
UPDATE 部门 SET 部门编号 = '11' WHERE 部门编号 = '01';
-- 员工表的部门编号也自动变成 '11'

-- 测试级联删除
DELETE FROM 部门 WHERE 部门编号 = '11';
-- 员工表中部门编号为 '11' 的记录也被删除
```

#### **6.9.4 唯一性约束（UNIQUE）**

```sql
-- 单列唯一约束
CREATE TABLE 学生
(
    学号 CHAR(10) PRIMARY KEY,
    身份证号 CHAR(18) UNIQUE,
    邮箱 VARCHAR(50) UNIQUE,
    姓名 VARCHAR(20)
);

-- 复合唯一约束
CREATE TABLE 学生
(
    学号 CHAR(10) PRIMARY KEY,
    班级 VARCHAR(20),
    姓名 VARCHAR(20),
    CONSTRAINT UK_班级姓名 UNIQUE (班级, 姓名)
);
```

**PRIMARY KEY vs UNIQUE：**

| **对比项** | **PRIMARY KEY** | **UNIQUE** |
| --- | --- | --- |
| 允许NULL | ✗ 不允许 | ✓ 允许（但只能有一个） |
| 数量限制 | 每表只能有一个 | 可以有多个 |
| 自动创建索引 | 聚簇索引 | 非聚簇索引 |

#### **6.9.5 检查约束（CHECK）**

> ⚠️ **注意**：MySQL 8.0.16 之前版本不支持 CHECK 约束（会解析但不生效）

```sql
-- 性别检查
CREATE TABLE 教师
(
    教师编号 CHAR(10) PRIMARY KEY,
    姓名 VARCHAR(20) NOT NULL,
    性别 CHAR(2) CHECK (性别 IN ('男', '女')),
    年龄 INT CHECK (年龄 >= 18 AND 年龄 <= 65),
    工资 DECIMAL(10,2) CHECK (工资 > 0)
);
```

##### **常用检查约束示例**

```sql
-- 成绩范围检查
CREATE TABLE 成绩
(
    学号 CHAR(10),
    课程编号 CHAR(8),
    成绩 DECIMAL(5,2) CHECK (成绩 BETWEEN 0 AND 100),
    PRIMARY KEY (学号, 课程编号)
);

-- 手机号格式检查
CREATE TABLE 用户
(
    用户ID INT PRIMARY KEY AUTO_INCREMENT,
    手机号 CHAR(11) CHECK (手机号 REGEXP '^1[3-9][0-9]{9}$'),
    邮箱 VARCHAR(50) CHECK (邮箱 LIKE '%@%.%')
);

-- 日期逻辑检查
CREATE TABLE 项目
(
    项目编号 CHAR(10) PRIMARY KEY,
    开始日期 DATE,
    结束日期 DATE,
    CHECK (结束日期 > 开始日期)
);
```

##### **MySQL 8.0 之前的替代方案**

```sql
-- 使用 ENUM 替代简单的 CHECK
CREATE TABLE 教师
(
    教师编号 CHAR(10) PRIMARY KEY,
    姓名 VARCHAR(20) NOT NULL,
    性别 ENUM('男', '女')  -- 只能是 '男' 或 '女'
);

-- 使用触发器替代复杂的 CHECK
DELIMITER //
CREATE TRIGGER check_age_before_insert
BEFORE INSERT ON 教师
FOR EACH ROW
BEGIN
    IF NEW.年龄 < 18 OR NEW.年龄 > 65 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = '年龄必须在18-65之间';
    END IF;
END //
DELIMITER ;
```

#### **6.9.6 默认约束（DEFAULT）**

```sql
CREATE TABLE 课程
(
    课程编号 CHAR(8) PRIMARY KEY,
    课程名称 VARCHAR(50) NOT NULL,
    学分 DECIMAL(3,1) DEFAULT 2.0,
    课程性质 VARCHAR(10) DEFAULT '必修',
    创建时间 DATETIME DEFAULT CURRENT_TIMESTAMP
);
```

##### **常用默认值**

```sql
CREATE TABLE 订单
(
    订单ID INT PRIMARY KEY AUTO_INCREMENT,
    订单状态 VARCHAR(10) DEFAULT '待处理',
    数量 INT DEFAULT 1,
    折扣 DECIMAL(3,2) DEFAULT 1.00,
    创建时间 TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    更新时间 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                       ON UPDATE CURRENT_TIMESTAMP
);
```

**常用默认值函数：**

| **函数** | **说明** |
| --- | --- |
| `CURRENT_TIMESTAMP` | 当前日期时间 |
| `CURRENT_DATE` | 当前日期 |
| `CURRENT_TIME` | 当前时间 |
| `UUID()` | 生成UUID（需要触发器） |
| `ON UPDATE CURRENT_TIMESTAMP` | 更新时自动更新时间戳 |

---
⬅️ [[06-MySQL 数据库管理|MySQL 数据库管理]] 🏠 [[00-数据库|00-数据库]] ➡️ [[08-数据表的修改与删除|数据表的修改与删除]]