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

本文涉及的产品
云数据库 RDS MySQL,集群版 2核4GB 100GB
推荐场景:
搭建个人博客
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB PostgreSQL 版,企业版 4核16GB
推荐场景:
HTAP混合负载
简介: 关系型数据库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);

展示

相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
9天前
|
存储 关系型数据库 MySQL
探索MySQL:关系型数据库的基石
MySQL,作为全球最流行的开源关系型数据库管理系统(RDBMS)之一,广泛应用于各种Web应用、企业级应用和数据仓库中
|
7天前
|
关系型数据库 MySQL 网络安全
Mysql 数据库主从复制
在MySQL主从复制环境中,配置了两台虚拟机:主VM拥有IP1,从VM有IP2。主VM的`my.cnf`设置server-id为1,启用二进制日志;从VM设置server-id为2,开启GTID模式。通过`find`命令查找配置文件,编辑`my.cnf`,在主服务器上创建复制用户,记录二进制日志信息,然后锁定表并备份数据。备份文件通过SCP传输到从服务器,恢复数据并配置复制源,启动复制。检查复制状态确认运行正常。最后解锁表,完成主从同步,新用户在从库中自动更新。
921 6
Mysql 数据库主从复制
|
7天前
|
缓存 运维 关系型数据库
数据库容灾 | MySQL MGR与阿里云PolarDB-X Paxos的深度对比
经过深入的技术剖析与性能对比,PolarDB-X DN凭借其自研的X-Paxos协议和一系列优化设计,在性能、正确性、可用性及资源开销等方面展现出对MySQL MGR的多项优势,但MGR在MySQL生态体系内也占据重要地位,但需要考虑备库宕机抖动、跨机房容灾性能波动、稳定性等各种情况,因此如果想用好MGR,必须配备专业的技术和运维团队的支持。 在面对大规模、高并发、高可用性需求时,PolarDB-X存储引擎以其独特的技术优势和优异的性能表现,相比于MGR在开箱即用的场景下,PolarDB-X基于DN的集中式(标准版)在功能和性能都做到了很好的平衡,成为了极具竞争力的数据库解决方案。
|
5天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
6天前
|
关系型数据库 数据库 RDS
利用DTS将自建mysql5.7版本数据库迁移至对应rds报错
利用DTS将自建mysql5.7版本数据库迁移至对应rds报错
29 0
|
10天前
|
XML 关系型数据库 MySQL
支付系统----微信支付19---集成MyBatis-plus,数据库驱动对应的依赖版本设置问题,5没版本没有cj这个依赖,mysql驱动默认的是版本8,这里是一个父类,数据库都有,写个父类,继承就行
支付系统----微信支付19---集成MyBatis-plus,数据库驱动对应的依赖版本设置问题,5没版本没有cj这个依赖,mysql驱动默认的是版本8,这里是一个父类,数据库都有,写个父类,继承就行
|
10天前
|
Java 关系型数据库 MySQL
使用MySQL JDBC连接数据库
使用MySQL JDBC连接数据库
|
13天前
|
XML Java 关系型数据库
Action:Consider the following: If you want an embedde ,springBoot配置数据库,补全springBoot的xml和mysql配置信息就好了
Action:Consider the following: If you want an embedde ,springBoot配置数据库,补全springBoot的xml和mysql配置信息就好了
|
12天前
|
关系型数据库 MySQL 数据库
关系型数据库mysql数据增量恢复
【7月更文挑战第3天】
127 2
|
12天前
|
关系型数据库 MySQL Shell
关系型数据库mysql数据完全恢复
【7月更文挑战第3天】
83 2