关系型数据库MySQL开发要点之多表设计案例详解代码实现

什么是多表设计

项目开发中

在进行数据库表结构设计时根据数据模型和业务关系

会根据业务需求和业务模块之间的关系分析设计表结构

由于业务之间互相关联所以表结构之间也存在着各种联系

主要分为以下三种

一对多

每个部门下是有多个员工的

但是一个员工只能归属一个部门

完成部门表的设计

建表并添加数据模型

先创建员工表

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 '图像',
    job tinyint unsigned comment '职位 1班主任 2讲师 3学工主管 4教研主任',
    entrydate date comment '入职时间',
    creat_time datetime not null comment '创建时间',
    update_time datetime not null comment '修改时间'
)comment '员工表';

再创建部门表

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

最后完善员工表信息

在员工表中关联部门的ID

完整代码

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 '图像',
    job tinyint unsigned comment '职位 1班主任 2讲师 3学工主管 4教研主任',
    entrydate date comment '入职时间',
    dept_id int unsigned comment '归属的部门id',
    creat_time datetime not null comment '创建时间',
    update_time datetime not null comment '修改时间'
)comment '员工表';

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

一一关联父表子表

测试

外键约束操作

我们在项目开发阶段额外添加外键直接在图形化界面中操作就行了

这样就好了

成功关联

我们在以后开发中应该使用逻辑外键

重点：一对一设计

案例用户和身份证号之间的关系

一对一关系多用于单表的拆分

将一张表的基础字段放在一张表中其他字段放在另一张表里

以操作操作效率

如果在后端开发中我们遇到了对一对一的关系的反复查询的时候

我们建议使用一对一的关系建立数据库

一对一可以看成是一种特殊的一对多关系

代码实现

USE bigdate1421;

DROP TABLE tb_user;
DELETE  FROM tb_user;

DROP TABLE tb_user_card;
DELETE  FROM tb_user_card;

-- 一对一 用户和身份证
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,'高畅',2,'111','大学'),
    (2,'栾增旭',1,'333','大学'),
    (3,'刘岩',1,'222','大学'),
    (4,'郑子烨',2,'444','大学'),
    (5,'于芯怡',2,'555','大学');

-- 第一张表式用户基本信息表 第二张表是用户身份信息表
CREATE TABLE tb_user_card(
    id int unsigned primary key auto_increment comment 'ID',
    nationality varchar(10) not null comment '民族',
    idcard char(18) not null comment '身份证号',
    expire_begin date not null 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,'汉','111111111111111111','2024-05-07',1),
    (2,'汉','222222222222222222','2024-05-07',2),
    (3,'汉','333333333333333333','2024-05-07',3),
    (4,'汉','444444444444444444','2024-05-07',4),
    (5,'汉','555555555555555555','2024-05-07',5);

展示

重点：多对多设计

学生与老师的关系

一个学生可以有多个老师

一个老师也可以有多个学生

多对多时借助外键是很难实现的

我们要借助中间表来实现

代码实现

USE bigdate1421;

-- 多对多 学生与课程

-- 第一张表
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
    ('高畅','5'),
    ('栾增旭','4'),
    ('刘岩','3'),
    ('郑子烨','2'),
    ('于芯怡','1');

-- 第二张表
create table tb_course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
)comment '课程表';

insert into tb_course (name) values
    ('Java'),
    ('Javascript'),
    ('golang'),
    ('python');

-- 第三张表 中间表
create table tb_student_course(
    id int auto_increment primary key comment '主键ID',
    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,1),
    (2,2),
    (2,3),
    (2,4),
    (3,1),
    (3,3),
    (4,3),
    (4,4);

展示