JavaWeb-28-数据库多表设计

最新推荐文章于 2024-04-29 15:15:56 发布

学不会is dog

最新推荐文章于 2024-04-29 15:15:56 发布

阅读量575

点赞数

分类专栏： JavaWeb 文章标签：数据库 MySQL DataGrip 后端

本文链接：https://blog.csdn.net/yelitoudu/article/details/132805543

版权

JavaWeb 专栏收录该内容

35 篇文章 5 订阅

订阅专栏

多表设计

一、概述

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：

一对多(多对一)
多对多
一对一

二、一对多(多对一)

1. 表结构设计

(1) 需求

根据页面原型及需求文档，完成部门及员工模块的表结构设计。

(2) 分析

关系：一个部门有多个员工，而一个员工只属于一个部门，因此，部门与员工的关系是一对多(多对一)
连接：

(3) 实现

数据库

-- 创建数据库并使用
create database if not exists db02;
use db02;

员工表

-- 员工表
create table tb_emp(
    # 基础字段ID: int数字类型, 不存负数所以是无符号, 主键, 自增
    id int unsigned primary key auto_increment comment 'ID',
    # 用户名: varchar字符串类型(长度最长20位), 必填非空, 唯一
    username varchar(20) not null unique comment '用户名',
    # 密码: varchar字符串类型(长度最长32位), 选填, 为空时默认设置密码为 '123456'
    password varchar(32) default '123456' comment '密码',
    # 员工姓名: varchar字符串类型(长度最长10位), 必填非空
    name varchar(10) not null comment '员工姓名',
    # 性别: tinyint数字类型, 不使用负数所以是无符号, 必填非空, 1代表男, 2代表女
    gender tinyint unsigned not null comment '性别: 1-男, 2-女',
    # 图像: 存放图像路径，所以使用varchar字符串类型, 选填
    image varchar(300) comment '图像url',
    # 职位: tinyint数字类型, 不使用负数所以是无符号, 选填, 1代表班主任 ,...
    job tinyint unsigned comment '职位: 1-班主任, 2-讲师, 3-学工主管, 4-教研主管',
    # 入职日期: 要求格式为xxxx-xx-xx, 所以使用date日期类型, 选填
    entry_date date comment '入职日期',
    # 归属的部门ID: 存放部门表的部门ID(部门表主键), int数字类型, 不使用负数所以是无符号, 选填
    dept_id int unsigned comment '归属的部门ID',
    # 基础字段创建时间、修改时间: datetime日期时间类型, 必填非空
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '修改时间'
) comment '员工表';

部门表

-- 部门表
create table tb_dept(
    # 基础字段ID: int数字类型, 不适用负数所以是无符号, 主键, 自增
    id int unsigned primary key auto_increment comment 'ID',
    # 部门名称: varchar字符串类型(长度最长10位), 必填非空, 唯一
    name varchar(10) not null unique comment '部门名称',
    # 基础字段创建时间、修改时间: datetime日期时间类型, 必填非空
    create_time datetime not null comment '创建时间',
    update_time datetime not null comment '修改时间'
) comment '部门表';

(4) 小结

设计部门以及员工表，由于一个部门对应多个员工，而一个员工只归属于一个部门
因此在设计这两个表结构的时候，需要在员工表中增加一个dept_id字段：这个字段指定了当前员工的所属部门，记录该员工所属部门的ID

有了这层关系之后，将来要搜索某一个员工的所属部门会非常容易，例如：
- 我想搜索2号员工张无忌的所属部门，看到张无忌的dept_id是2号
- 2号部门对应着部门表就是教研部，这样就知道2号员工张无忌的所属部门是教研部

这层关系中：
- 部门表是1的一方，而员工表是多的一方
- 一个部门会对应多个员工，但是一个员工只能归属一个部门
- 因此在一对多的关系中，可以把1的一方称为父表，把多的一方称为子表
- 因为一个父亲可以有多个儿子，但是一个儿子只能有一个父亲

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

2. 外键

(1) 插入测试数据

-- 插入测试数据
# 部门表测试数据
insert into tb_dept (id, name, create_time, update_time)
values (1, '学工部', now(), now()),
       (2, '教研部', now(), now()),
       (3, '咨询部', now(), now()),
       (4, '就业部', now(), now()),
       (5, '人事部', now(), now());

# 员工表测试数据
INSERT INTO tb_emp
(id, username, password, name, gender, image, job, entry_date, dept_id, create_time, update_time)
VALUES (1, 'jinyong', '123456', '金庸', 1, '1.jpg', 4, '2000-01-01', 2, now(), now()),
       (2, 'zhangwuji', '123456', '张无忌', 1, '2.jpg', 2, '2015-01-01', 2, now(), now()),
       (3, 'yangxiao', '123456', '杨逍', 1, '3.jpg', 2, '2008-05-01', 2, now(), now()),
       (4, 'weiyixiao', '123456', '韦一笑', 1, '4.jpg', 2, '2007-01-01', 2, now(), now()),
       (5, 'changyuchun', '123456', '常遇春', 1, '5.jpg', 2, '2012-12-05', 2, now(), now()),
       (6, 'xiaozhao', '123456', '小昭', 2, '6.jpg', 3, '2013-09-05', 1, now(), now()),
       (7, 'jixiaofu', '123456', '纪晓芙', 2, '7.jpg', 1, '2005-08-01', 1, now(), now()),
       (8, 'zhouzhiruo', '123456', '周芷若', 2, '8.jpg', 1, '2014-11-09', 1, now(), now()),
       (9, 'dingminjun', '123456', '丁敏君', 2, '9.jpg', 1, '2011-03-11', 1, now(), now()),
       (10, 'zhaomin', '123456', '赵敏', 2, '10.jpg', 1, '2013-09-05', 1, now(), now()),
       (11, 'luzhangke', '123456', '鹿杖客', 1, '11.jpg', 1, '2007-02-01', 1, now(), now()),
       (12, 'hebiweng', '123456', '鹤笔翁', 1, '12.jpg', 1, '2008-08-18', 1, now(), now()),
       (13, 'fangdongbai', '123456', '方东白', 1, '13.jpg', 2, '2012-11-01', 2, now(), now()),
       (14, 'zhangsanfeng', '123456', '张三丰', 1, '14.jpg', 2, '2002-08-01', 2, now(), now()),
       (15, 'yulianzhou', '123456', '俞莲舟', 1, '15.jpg', 2, '2011-05-01', 2, now(), now()),
       (16, 'songyuanqiao', '123456', '宋远桥', 1, '16.jpg', 2, '2010-01-01', 2, now(), now()),
       (17, 'chenyouliang', '123456', '陈友谅', 1, '17.jpg', NULL, '2015-03-21', NULL, now(), now());

(2) 发现问题

现象：部门数据可以直接删除，然而还有部分员工归属于该部门下，此时就出现了数据的不完整、不一致问题。

(3) 解决问题

目前上述的两张表，只是在逻辑层面建立了关联，但是在数据库层面，并未建立关联，所以是无法保证数据的一致性和完整性的。
需要在数据库层面，进行物理外键约束，才可以保证数据的一致性和完整性的。

(4) 外键语法

-- 创建表时指定
create table 表名(
	字段名    数据类型,
	...
	[constraint]   [外键名称]  foreign  key (外键字段名)   references   主表 (字段名)	
);

-- 建完表后，添加外键
alter table  表名  add constraint  外键名称  foreign key (外键字段名) references  主表(字段名);

-- 解除外键
alter table 表名 drop foreign key (外键字段名);

(5) 外键约束

① SQL操作

-- 物理外键约束
-- 员工表
create table tb_emp
(
    id          int unsigned primary key auto_increment comment 'ID',
    username    varchar(20)      not null unique comment '用户名',
    password    varchar(32) default '123456' comment '密码',
    name        varchar(10)      not null comment '员工姓名',
    gender      tinyint unsigned not null comment '性别: 1-男, 2-女',
    image       varchar(300) comment '图像url',
    job         tinyint unsigned comment '职位: 1-班主任, 2-讲师, 3-学工主管, 4-教研主管',
    entry_date  date comment '入职日期',
    dept_id     int unsigned comment '归属的部门ID',
    create_time datetime         not null comment '创建时间',
    update_time datetime         not null comment '修改时间',
    # 创建表的时候，完成外键约束
    constraint tb_emp_fk_dept_id foreign key (dept_id) references tb_dept(id)
) comment '员工表';

-- 建完表后，添加外键
alter table tb_emp add constraint tb_emp_fk_dept_id foreign key (dept_id) references tb_dept(id);

② 图形化操作

记得先解除外键约束

(6) 物理外键与逻辑外键

三、一对一

1. 概述

案例：例如用户与身份证信息的关系
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率

2. 需求

如下用户表所示：
- 该表既包含用户的基本信息，也包含用户的身份信息
- 基本信息：id、姓名、性别、手机号、学历
- 身份信息：民族、生日、身份证号、身份证签发机关、身份证有效期开始时间、身份证有效期结束时间

如果在业务系统中对用户的基本信息查询频率很高，但是对用户的身份信息查询频率很低
此时出于提高查询效率的考虑，可以将大表拆分成两张小表：
- 第一张表：存放的是用户的基本信息
- 第二张表：存放的是用户的身份信息
它们二者之间就是一对一的关系：一个用户只能对应一个身份证，而一个身份证也只能关联一个用户

3. 实现

关键点：在逻辑层面已经体现了，那在数据库层面该如何去体现它们两者之间是一对一的关系
- 只需要在任意一方加入外键，并设置外键为唯一的(UNIQUE)，关联另外一方的主键

用户基本信息表

-- 2. 一对一
# 创建用户基本信息表
create table tb_user
(
    id     int unsigned primary key auto_increment comment 'ID',
    name   varchar(10)      not null comment '姓名',
    gender tinyint unsigned not null comment '性别, 1 男  2 女',
    phone  char(11) comment '手机号',
    degree varchar(10) comment '学历'
) comment '用户信息表';

# 插入测试数据
insert into tb_user
values (1, '白眉鹰王', 1, '18812340001', '初中'),
       (2, '青翼蝠王', 1, '18812340002', '大专'),
       (3, '金毛狮王', 1, '18812340003', '初中'),
       (4, '紫衫龙王', 2, '18812340004', '硕士');

用户身份信息表

# 创建用户身份信息表
create table tb_user_card
(
    id           int unsigned primary key auto_increment comment 'ID',
    nationality  varchar(10)  not null comment '民族',
    birthday     date         not null comment '生日',
    idcard       char(18)     not null comment '身份证号',
    issued       varchar(20)  not null comment '签发机关',
    expire_begin date         not null comment '有效期限-开始',
    expire_end   date comment '有效期限-结束',
    # 在任意一方加入外键，设置外键为唯一的
    user_id      int unsigned not null unique comment '用户ID',
    # 关联另外一方的主键
    constraint fk_user_id foreign key (user_id) references tb_user (id)
) comment '用户信息表';

# 插入测试数据
insert into tb_user_card
values (1, '汉', '1960-11-06', '100000100000100001', '朝阳区公安局', '2000-06-10', null, 1),
       (2, '汉', '1971-11-06', '100000100000100002', '静安区公安局', '2005-06-10', '2025-06-10', 2),
       (3, '汉', '1963-11-06', '100000100000100003', '昌平区公安局', '2006-06-10', null, 3),
       (4, '回', '1980-11-06', '100000100000100004', '海淀区公安局', '2008-06-10', '2028-06-10', 4);

一个用户只能对应一个身份证：

四、多对多

1. 概述

比如：

① 学生与课程

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

② 老师与学生

案例：学生与老师的关系
关系：一个学生可以有多个代课老师，一个老师也可以教授多个学生

2. 需求

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

问题：
- ① 假设在学生表中设置外键，该字段仅能存一个值，如果一个学生选择了多门课，就不合适了
- ② 假设在课程表中设置外键，该字段也是仅能存一个值，如果一门课程被多个学生选择，也是不合适

解决问题：
- 此时需要建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

3. 实现

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

学生表

-- 3. 多对多
# 创建学生表
create table tb_student(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no   varchar(10) comment '学号'
) comment '学生表';

# 往学生表插入测试数据
insert into tb_student(name, no)
values ('黛绮丝', '2000100101'),
       ('谢逊', '2000100102'),
       ('殷天正', '2000100103'),
       ('韦一笑', '2000100104');

课程表

#创建课程表
create table tb_course
(
    id   int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';

# 往课程表插入测试数据
insert into tb_course (name)
values ('Java'),
       ('PHP'),
       ('MySQL'),
       ('Hadoop');

学生课程中间表

# 创建第三张中间表：学生课程关系表
create table tb_student_course
(
    id         int auto_increment comment '主键' primary key,
    student_id int not null comment '学生ID',
    course_id  int not null comment '课程ID',
    # 设置外键，分别关联两方主键
    constraint fk_courseid foreign key (course_id) references tb_course (id),
    constraint fk_studentid foreign key (student_id) references tb_student (id)
) comment '学生课程中间表';

# 插入测试数据
insert into tb_student_course(student_id, course_id)
values (1, 1),
       (1, 2),
       (1, 3),
       (2, 2),
       (2, 3),
       (3, 4);

此时，想要查询：
- 学号为1的学生黛绮丝她选修了哪些课程
- 此时可以到中间表中查询：根据 student_id=1 来查询，查询到 id=1 的学生选修了三门课程：1,2,3
- 拿到课程的 id 之后，就可以到课程表中进行查询，之后知道：当前 1号学生黛绮丝选修的课程分别是：Java、PHP、MySQL