欢迎光临
MySQL 篇-深入了解多表设计、多表查询
   

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

🔥博客主页: 【小扳_-CSDN博客】

❤感谢大家点赞👍收藏⭐评论✍ 

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

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

文章目录

        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 篇-深入了解多表设计、多表查询,第3张

员工表:

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

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

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

delete from department where id = 2;

执行结果如下:

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

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

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

delete from employee where name = '王五';

执行结果如下:

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

        物理外键:

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

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

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

        1.2 多表设计 - 一对一

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

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

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

代码如下:

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 '身份证号码',
    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 篇-深入了解多表设计、多表查询,第7张

用户表:

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

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

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

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

        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 篇-深入了解多表设计、多表查询,第9张

                                中间表:

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

                                学生表:

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

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

        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 篇-深入了解多表设计、多表查询,第12张

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

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

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

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

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

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

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

 
 歌颂祖国的文章  如来佛祖的来历  六月雪花语诗句  蓝色妖姬利群多少钱一包  满天星花怎么养殖方法冬天  黄姜粉 
打赏
版权声明:本文采用知识共享 署名4.0国际许可协议 [BY-NC-SA] 进行授权
文章名称:《MySQL 篇-深入了解多表设计、多表查询》
文章链接:https://goodmancom.com/wl/175899.html