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

6.8 数据表的创建

6.8.1 选择数据库

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

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 
-- 无符号整数示例
年龄 TINYINT UNSIGNED  -- 0-255，适合存储年龄
浮点数类型
类型 说明
FLOAT 单精度浮点数
DOUBLE 双精度浮点数
DECIMAL(M,D) 精确小数，M是总位数，D是小数位数 
-- 货币推荐使用 DECIMAL
价格 DECIMAL(10,2)  -- 最大99999999.99
字符串类型
类型 说明 最大长度
CHAR(n) 定长字符串 255字符
VARCHAR(n) 变长字符串 65535字节
TEXT 长文本 65535字节
MEDIUMTEXT 中等长度文本 16MB
LONGTEXT 超长文本 4GB 
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 
-- TIMESTAMP 会自动转换时区，DATETIME 不会
创建时间 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
其他类型
类型 说明
BLOB 二进制大对象
ENUM 枚举类型
SET 集合类型
JSON JSON格式数据（MySQL 5.7+） 
-- ENUM 示例
性别 ENUM('男', '女')

-- JSON 示例
配置信息 JSON

6.8.3 创建表的基本语法

USE 学生管理;

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

6.8.4 创建表的完整示例

USE 学生管理;

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

查看表结构：

DESC 学生;
-- 或者
SHOW CREATE TABLE 学生;
image-8b517709

6.8.5 计算列（生成列）

💡 MySQL 5.7+ 支持生成列

CREATE TABLE 商品
(
    商品编号 CHAR(10),
    商品名称 VARCHAR(50),
    单价 DECIMAL(10,2),
    数量 INT,
    总价 DECIMAL(12,2) AS (单价 * 数量) STORED
);
生成列类型 说明
VIRTUAL 虚拟列，查询时计算，不占存储空间
STORED 存储列，插入/更新时计算，占用存储空间 
-- 字符串连接示例
CREATE TABLE 员工
(
    姓 VARCHAR(10),
    名 VARCHAR(10),
    全名 VARCHAR(20) AS (CONCAT(姓, 名)) VIRTUAL
);

6.9 完整性约束

image-04cd276c

6.9.1 空值约束（NULL / NOT NULL）

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

测试:

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

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

6.9.2 主键约束（PRIMARY KEY）

单列主键
-- 写法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 (学号)
);
复合主键（联合主键）
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 ✗ 两个都相同，不允许！
自增主键
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）

创建主表和从表
-- 步骤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
外键约束的作用
-- 在主表插入数据
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
级联操作
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不支持）
级联操作测试
-- 创建测试表
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）

-- 单列唯一约束
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 约束（会解析但不生效）

-- 性别检查
CREATE TABLE 教师
(
    教师编号 CHAR(10) PRIMARY KEY,
    姓名 VARCHAR(20) NOT NULL,
    性别 CHAR(2) CHECK (性别 IN ('男', '女')),
    年龄 INT CHECK (年龄 >= 18 AND 年龄 <= 65),
    工资 DECIMAL(10,2) CHECK (工资 > 0)
);
常用检查约束示例
-- 成绩范围检查
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 之前的替代方案
-- 使用 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）

CREATE TABLE 课程
(
    课程编号 CHAR(8) PRIMARY KEY,
    课程名称 VARCHAR(50) NOT NULL,
    学分 DECIMAL(3,1) DEFAULT 2.0,
    课程性质 VARCHAR(10) DEFAULT '必修',
    创建时间 DATETIME DEFAULT CURRENT_TIMESTAMP
);
常用默认值
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 更新时自动更新时间戳

⬅️ MySQL 数据库管理 🏠 00-数据库 ➡️ 数据表的修改与删除