MySQL 篇-深入了解多表设计、多表查询

MySQL 篇-深入了解多表设计、多表查询,第1张

博客主页: 【小扳_-CSDN博客】
❤感谢大家点赞收藏⭐评论✍
 

MySQL 篇-深入了解多表设计、多表查询,第2张

MySQL 篇-深入了解多表设计、多表查询,第3张

文章目录

        1.0 多表设计概述

        1.1 多表设计 - 一对多

        1.2 多表设计 - 一对一

        1.3 多表设计 - 多对多

        2.0 多表查询概述

        2.1 多表查询 - 内连接

        2.2 多表查询 - 外连接

        2.3 多表查询 - 子查询


        1.0 多表设计概述

        多表设计是指在数据库中将数据分散存储在多个表中的设计方法。这种设计方法通常用于将数据按照不同的实体或属性进行划分,以便更好地组织和管理数据。

        在多表设计中,不同的表之间通常会通过外键来建立关联关系,从而实现数据之间的引用和关联。这种设计方法有助于减少数据冗余、提高数据的一致性和完整性,并且可以更好地支持数据的查询和分析。

        总的来说,为了数据在表中更好的管理,将数据拆分到不同的表中。而表与表之间通过外键来建立联系。

        多表设计的类型主要分为:一对多、一对一、多对多。

        1.1 多表设计 - 一对多

        在数据库设计中,一对多关系指的是一个实体在另一个实体中有多个关联记录的关系。通常使用外键来实现一对多关系。假设我们有两个实体 A 和 B ,A 实体可以有多个关联的 B 实体记录,而B实体只能关联一个 A 实体记录。

外键语法:

-- 创建表时指定 create table 表名( 字段名 数据结构, ... [constraint] [外键名] foreign key(外键字段名) references 主表(字段名) ); -- 建完表后,添加外键 alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(字段名);

        举个例子,部门与员工的关系,一个部门可以有多个员工,而一个员工只能属于一个部门,这就构成了一对多的关系,为了建立部门与员工的关系,则在员工表中添加外键即可。

代码如下:

create table department( id tinyint unsigned primary key comment '序号', name varchar(10) not null comment '部门名称', last_time datetime not null comment '最后的 *** 作时间' )comment '部门表';create table employee( id tinyint unsigned primary key comment 'id号', name varchar(10) not null comment '名字', department_id tinyint unsigned comment '部门号', last_time datetime not null comment '最后的 *** 作时间' )comment '员工表';-- 添加外键约束 alter table employee add foreign key (department_id) references department(id);-- 添加部门表中的数据 insert into department values (1,'学工部',now()),(2,'教研部',now()),(3,'教学部',now()),(4,'后勤部',now()); -- 添加员工表中的数据 insert into employee values (1,'张三',1,now()),(2,'李四',2,now()),(3,'王五',2,now()),(4,'赵六',4,now());

部门表:

MySQL 篇-深入了解多表设计、多表查询,第4张

员工表:

MySQL 篇-深入了解多表设计、多表查询,第5张

        这两个表已经建立了物理联系,通过外键来建立物理上的联系是为了保证数据的一致性和完整性。

        比如,现在要删除部门表中的 '教研部' 数据,代码如下:

delete from department where id = 2;

执行结果如下:

MySQL 篇-深入了解多表设计、多表查询,第6张

        由于添加了外键联系,为了确保数据的一致性和完整性,所以影响该 *** 作失败。

而对与删除员工表中的数据则可以删除成功,代码如下:

delete from employee where name = '王五';

执行结果如下:

MySQL 篇-深入了解多表设计、多表查询,第7张

        物理外键:

        使用 foreign key 定义外键关联另外一张表。但是会影响增、删、改的效率(因为需要检查外键关系)、仅用于单节点数据库,不适用与分布式、集群场景、容易引发数据库的死锁问题、消耗性能。所以我们一般建立表与表之间的逻辑外键联系,而不建立物理外键联系。

        关于在一对多关系中在哪一个表中添加外键:

        简单粗暴的说,在一对多中,代表多的表需要添加外键,一个员工表与一个部门表,显然员工表是代表多的一方,部门表代表少的一方。因为一个部门有很多员工,而一个员工只能属于一个部门。

        1.2 多表设计 - 一对一

        在数据库中,一对一关系是指两个实体之间存在一种一对一的关联关系。这种关系通常通过在两个表之间共享一个相同的主键来实现。

        在任意一方加入外键,关联另外一个的主键,并且设置外键为唯一的 unique 。

        举个例子,用户与身份z信息的关系,一对一关系,用于单表的拆分,将一张表的基础字段放在一张表中,其他字段放在另一张表中,以提升 *** 作效率。

代码如下:

create table user( id tinyint unsigned primary key comment 'id号', name varchar(10) not null comment '名字', phone varchar(11) comment '电话号码', degree varchar(10) comment '学历', birthday date comment '出生日期' )comment '用户表';create table user_id_card( id_card varchar(18) primary key comment '身份z号码', issued varchar(10) not null, fk_id tinyint unsigned, constraint fk foreign key (fk_id) references user(id) )comment '用户id表';-- 添加数据 insert into user values (1,'鹰王','18812340001','初中','1960-11-06'); insert into user values (2,'辐王','18812340002','高中','1961-11-06'),(3,'龙王','18812340003','高中','1962-11-06');insert into user_id_card values (100000000010000011,'朝阳',1),(100000000010000022,'西阳',2),(100000000010000033,'东阳',3);

             用户ID表:

MySQL 篇-深入了解多表设计、多表查询,第8张

用户表:

MySQL 篇-深入了解多表设计、多表查询,第9张

        通过物理外键已经建立好了两个表的联系了。

        关于在一对一关系中在哪一个表中添加外键:

        在一对一中,任意一个表中都可以添加外键,任选一个表即可。

        1.3 多表设计 - 多对多

        在多表设计中,多对多关系通常需要使用一个中间表来实现。这种中间表包含两个外键,分别指向参与关系的两个表。这样就可以实现多对多关系的表示。

        举个例子,学生与课程的关系,一个学生可以选修多门课程,一门课程也可以供多个学生选择。

代码如下:

create table student( id tinyint primary key comment 'id号', name varchar(10) not null comment '名字', no varchar(20) comment '学号' )comment '学生表';create table course( id tinyint primary key comment 'id号', name varchar(10) not null unique comment '课程名' )comment '课程表';create table course_student( id tinyint primary key comment 'id号', student_id tinyint comment '外键id号', course_id tinyint comment '外键id号', constraint fk_s foreign key (student_id) references student(id), constraint fk_c foreign key (course_id) references course(id) )comment '学生与课程的中间表';-- 添加数据 insert into student values (1,'张三',2002350101),(2,'李四',2002350102),(3,'王五',2002350103); insert into course values (1,'Java'),(2,'PHP'),(3,'MySQL'); insert into course_student values (1,1,1),(2,1,2),(3,1,3),(4,2,1),(5,2,3),(6,3,2);

                                课程表:

MySQL 篇-深入了解多表设计、多表查询,第10张

                                中间表:

MySQL 篇-深入了解多表设计、多表查询,第11张

                                学生表:

MySQL 篇-深入了解多表设计、多表查询,第12张

        小结:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键。

        2.0 多表查询概述

        多表查询是指在数据库中同时查询多个表的数据的 *** 作。通过多表查询,可以根据不同表之间的关联关系,将数据连接起来。

        多表查询的方式:内连接、外连接、子查询。

        2.1 多表查询 - 内连接

        内连接是一种多表查询的方式,用于检索两个或多个表中满足连接条件的数据。内连接只返回满足连接条件的行,即两个表中的数据必须在连接条件下匹配才会被检索出来。

内连接语法:

-- 隐式内连接 select 字段列表 from 表1,表2 where 条件 ...; -- 显示内连接 select 字段列表 from 表1 [inner] join 表2 on 链接条件 ...;

        显示内连接更推荐使用,因为它提供了更清晰的语法结构和更好的可读性。隐式内连接虽然在一些情况可以简化语句,但是容易造成混淆和错误,不易维护。但是两者的效果是一样的。

        2.2 多表查询 - 外连接

        在数据库中,多表查询可以通过外连接来实现。外连接是一种连接 *** 作,用于检索两个或多个表中的数据,即使其中一个表中的数据在另一个表中没有匹配项也可以检索出来。在外连接中,常见的类型有左外连接、右外连接。左外连接会返回左表中的所有数据,即使右表中没有匹配项;右外连接会返回右表中的所有数据,即使左表中没有匹配项。

外连接语法:

-- 左外链接 select 字段列表 from 表1 left [outer] join 表2 on 连接条件 ...;-- 右外链接 select 字段列表 from 表1 right [outer] join 表2 on 连接条件 ...;

        左外链接与右外链接可以相互转换的,一般习惯用左外链接方式来查询多表。

        2.3 多表查询 - 子查询

        在数据库中,多表查询是指从多个表中检索数据的 *** 作。而子查询是指在一个查询中嵌套另一个查询的 *** 作。所以子查询也称为嵌套查询。

可以具体分为:

        标量子查询:子查询返回的结果为单个值。

MySQL 篇-深入了解多表设计、多表查询,第13张

        列子查询:子查询返回的结果为一列。

MySQL 篇-深入了解多表设计、多表查询,第14张

        行子查询:子查询返回的结果为一行。

MySQL 篇-深入了解多表设计、多表查询,第15张

        表子查询:子查询返回的结果为多行多列。

        返回的多行多列就是一个表,常作为临时表,常用的 *** 作符:in 。

MySQL 篇-深入了解多表设计、多表查询,第16张

欢迎分享,转载请注明来源:内存溢出

原文地址: http://outofmemory.cn/sjk/13518829.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2024-03-10
下一篇 2024-03-15

发表评论

登录后才能评论

评论列表(0条)

保存