菜鸟之路day31一一MySQL之多表设计

作者：blue

时间：2025.5.9

0.概述

内容学习自黑马程序员BV1m84y1w7Tb

一.多表设计

1.1一对多

一对多关系实现：在数据库表中多的一方，添加字段，来关联一的一方的主键

例子：一个部门对应多个员工，设计部门表如下

-- 部门
create table tb_dept (
    id int unsigned primary key auto_increment comment 'ID',
    name varchar(10) not null unique comment '部门名称',
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '修改时间'
) comment '部门表';

为员工表新添加‘部门ID’字段，关联部门表

alter table tb_emp
    add dept_id int unsigned null comment '部门ID';

注意这样简单的添加两张表之间的联系，只是在逻辑层面添加了两张表的联系

在数据库层面并没有物理层面上的联系

我们可以通过添加外键的方式为两张表添加物理层面上的联系，使两张表是存在实际关联的

在平时开发过程中，更常使用的是逻辑外键

1.2一对一

案例：用户与身份证信息的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率

实现：在任意一方加入外键，惯量另一方的主键，并且设置外键为唯一的(UNIQUE)

示例：

-- 创建学生表
CREATE TABLE students (
       student_id INT PRIMARY KEY AUTO_INCREMENT,
       name VARCHAR(50) NOT NULL,
       age INT,
       gender INT,
       email VARCHAR(100) UNIQUE
);

-- 创建学生证表，与学生表一对一关系
CREATE TABLE student_identities (
             identity_id INT PRIMARY KEY AUTO_INCREMENT,
             student_id INT UNIQUE,
             identity_number VARCHAR(20) UNIQUE NOT NULL,
             issue_date DATE NOT NULL,
             expiry_date DATE NOT NULL,
             FOREIGN KEY (student_id) REFERENCES students(student_id)
);

1.3多对多

案例：学生与课程的关系

关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择

实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

示例：

学生选课表，他与学生表之间是多对多的关系，即一个学生可以选择多门课程，一个课程可以被多个学生选择，为了符合这种多对多的关系，我决定建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

-- 创建课程表
CREATE TABLE courses (
       course_id INT PRIMARY KEY AUTO_INCREMENT,
       course_name VARCHAR(100) NOT NULL,
       teacher VARCHAR(50),
       credit TINYINT
);

-- 创建选课关系表（中间表）
CREATE TABLE student_courses (
       id INT PRIMARY KEY AUTO_INCREMENT,
       student_id INT NOT NULL,
       course_id INT NOT NULL,
       FOREIGN KEY (student_id) REFERENCES students(student_id),
       FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

案例：

分类表

create table category
(
    id          int unsigned auto_increment comment '主键ID'
        primary key,
    name        varchar(20)                null comment '分类名称',
    type        tinyint unsigned           null comment '分类类型 1.菜品分类，2.套餐分类',
    sort        tinyint unsigned           not null comment '排序',
    status      tinyint unsigned default 0 null comment '状态字段： 0：停用 1：启用',
    create_time datetime                   not null,
    update_time datetime                   not null
)
    comment '分类表';

菜品表

create table dish
(
    id          int unsigned auto_increment comment '菜品id'
        primary key,
    name        varchar(20)                  not null comment '菜品名称',
    category_id int unsigned                 not null comment '分类id',
    price       decimal(8, 2)                not null comment '价格',
    image       varchar(300)                 not null comment '图像',
    description varchar(200)                 null,
    status      tinyint unsigned default '0' not null comment '状态',
    create_time datetime                     not null comment '创建时间',
    update_time datetime                     not null comment '更新时间'
)
    comment '菜品表';

套餐类

create table setmeal
(
    id          int auto_increment comment '主键ID'
        primary key,
    name        varchar(20)      not null comment '套餐名称',
    category_id int unsigned     not null,
    price       decimal(8, 2)    not null,
    image       varchar(300)     not null comment '图片',
    description varchar(200)     null comment '描述信息',
    status      tinyint unsigned not null comment '状态 0 停售 1 起售',
    create_time datetime         not null,
    update_time datetime         not null
)
    comment '套餐类';

套餐菜品关系表

create table setmeal_dish
(
    id         int unsigned auto_increment comment '主键ID'
        primary key,
    setmeal_id int unsigned     not null comment '套餐ID',
    dish_id    int unsigned     not null,
    copies     tinyint unsigned not null
)
    comment '套餐菜品关系表';