MySQL连表查询

时间:2024-05-04 17:04:44

MySQL简介,我们为什么要学习各种join

MySQL是SQL的一种,SQL意为结构化查询语言(Structure Query Language),MySQL可以应用于现实世界的各种结构化数据。

SQL(结构化查询语言),处理结构化数据的查询语言,结构化数据就是一张表,数据结构是关系,元组的结合

以下内容能看懂就看,看不懂可以自行百度

  1. 数据库(DB)是现代信息系统的核心组成部分,用于存储、管理和检索数据。
    它具备永久存储、有组织和可共享的特点。
  2. 数据库管理系统(DBMS)是一个软件系统,提供数据定义和数据操纵功能。
  3. 数据库系统(DBS)则是一个更广泛的术语,包括数据库(DB)、数据库管理系统(DBMS)、应用程序和数据库管理员(DBA)。
  4. 数据模型是数据库系统的核心和基础,分为概念模型(实体-联系模型)、逻辑模型和物理模型三类。
  5. 数据模型的组成三要素:
    数据结构(最重要,按照这个分类),数据操作,完整性约束条件
  6. 关系型数据库的数据结构是关系;数据操作包括:查询,更新(增删改);完整性约束条件包括①实体完整性(针对主属性,主属性不能为空),②参照完整性(外码,外码要么为空,要么是另一列中的值),③用户定义完整性(语义,比如性别,不能为空,比如年龄,不能是负数,比如成绩,必须在0-100之间)
  7. 现有的RDB(关系型数据库)无法处理相关数据处理问题,于是新的非关系数据库技术出现了,能很好的解决大数据相关的问题,叫NOSQL
  8. 结构化数据:可以通过固有键值获取相关数值,且数据的格式固定
  9. 非结构化数据:声音等信息,歧义严重,无法通过键值获取相关数值
  10. 半结构化数据:通过灵活键值获取相关数值,数据的格式不固定(XML,JSON等)

MySQL是关系型数据库,存储的是关系模型数据。

MySQL是一种计算机语言,只要是计算机语言,在解决现实问题的过程种,往往都对现实世界进行了抽象与映射,MySQL也不例外,一张表,往往就是对现实世界实体/映射关系的一种抽象。

join就是对各种实体之间关系的一种汇总,不管我们想要哪些结果,都可以通过join完成

在这里插入图片描述

Join的类型

在这里插入图片描述

参考文章:
SQL 连接(JOIN)

内连接,外连接,全外连接,交叉连接,自然连接

不同逻辑关系,我们能采取的最优策略(表结构设计)

表的关系,就是现实世界的实体之间的关系
1对1,1对多,多对多
不同的关系,我们有不同的策略进行处理

1对1

合法情况下,1名丈夫只能与1位女性结为夫妇

在这里插入图片描述
在这里插入图片描述

1. 表合并(最优)

普通的mysql表自带字段之间1对1的逻辑

在这里插入图片描述
优点:查询快,单表即可完成
没有缺点

2.添加外键

在这里插入图片描述
缺点:连表查询相比于单表查询比较慢

3. 创建中间表

一张新表,把逻辑关系保存起来
在这里插入图片描述
缺点:两次连表,更慢了

1对多

1个班级里面可以有多个学生,但是1个学生只能是某个班的学生
在这里插入图片描述

1.表合并

在这里插入图片描述
缺点:
①班级表有数据冗余,班级表内的字段,反反复复的出现,同一个班级内有多少学生,就要出现多少次
②修改麻烦,需要一个一个修改(学生换班,需要全都改;班级改名字,也需要都改)

2.“一”表加外键

在这里插入图片描述
缺点:
①班级表有数据冗余,班级表内的字段,反反复复的出现,同一个班级内有多少学生,就要出现多少次
②修改麻烦,需要一个一个修改(学生换班,学生表不需要改,班级表修改一条记录;班级改名字,班级表中旧名字都得改)

3.“多”表加外键(最优)

在这里插入图片描述
优点:
①班级表和学生表都不会数据冗余,学生表多一列即可
②修改学生班级,只需要修改一次即可(学生换班,修改学生表的c_id;班级改名字,只需要改一次即可)

3. 创建中间表

不会造成冗余,但是多次连表会增大查询的开销,能少连表就少连表

多对多

在这里插入图片描述
1个学生可以选择多门课程去学习,1门课程也可以被多个学生选择

1. 表合并

合并两张表,会造成大量的冗余
在这里插入图片描述

2. 添加外键

不管在那张表里面加外键,还是会造成数据的冗余

在这里插入图片描述

3. 创建新表(最优)

在这里插入图片描述
这样做,两张表都不会造成冗余,虽然需要多次连表查询,但是比数据冗余要好

连接类型

内连接,外连接,交叉连接,自然连接,全外连接
在这里插入图片描述
class表
在这里插入图片描述
student表
在这里插入图片描述

1. 自然连接(不建议使用)

 SELECT 
	*
  FROM student s
  NATURAL JOIN class c

更简单的方法去连接两张表,但是不建议使用它,因为有的时候自然连接会产生出乎意料的结果

自然连接不需要打上连接条件的列名,数据库引擎会自己看着办,找到两个表共同的列连接。

PS: 关键字NATURAL和USING不能同时出现

数据库引擎找不到相同的列,结果与交叉连接无异;

这里class表的id字段多了一个空格,数据库引擎找不到相同的列,结果就是交叉连接,排列组合
在这里插入图片描述数据库引擎找不到相同的列,结果会很奇葩;
在这里插入图片描述
只返回了满足条件的前三行

2. 交叉连接

 SELECT * FROM student s CROSS JOIN class c
 -- 两种写法等价
 select * from student s,class s

查出来的结果是两张表笛卡尔积之后的结果,也就是各个字段排列组合之后的结果,这种排列组合忽略表之间的外键限制

返回左表中的所有行,左表中的每一行与右表中的所有行排列组合

20个学生,3个班,最后查出来就是3×20 = 60条结果

在这里插入图片描述

交叉连接/自然连接正常使用

我们可以通过where后面加限制条件(外键或自定义的其他条件),减少排列组合的数量

select * from student s,class c where s.c_id = c.id

在这里插入图片描述
想要继续缩小范围,可以在where后面继续加筛选条件

当我们明确连接条件的id之后,交叉连接和内连接的查询结果相同

当其中左表/右表无法匹配(值为null)时,两者都不会返回这种无法匹配的行,所以本质上都是在求交集

3. 内连接

和明确主键连接条件的交叉连接本质无异,都是求交集

select * from student s inner join class c on s.c_id = c.id
-- 两者等价
select * from student s join class c on s.c_id = c.id

在这里插入图片描述

这样看似乎没什么问题,但是如果一些学生没有c_id

在这里插入图片描述

我们再去查询,结果就会没有这些学生

在这里插入图片描述

4. 外连接

左外连接,join左边的表的所有记录都会被保留,无论是否为空;

右外连接,join右边的表的所有记录都会被保留,无论是否为空;

select * from student s left outer join class c on s.c_id = c.id
-- 两种写法等价
select * from student s left join class c on s.c_id = c.id

在这里插入图片描述

我们可以看到,刚才交叉连接和内连接缺失的一部分学生,左外连接查到了

我们对class表加一个软件三班,但是学生表没有任何一条记录的c_id是4,然后分别进行内连接查询和右外连接查询

在这里插入图片描述
内连接查询
在这里插入图片描述
既没有缺少的学生,也没有多余的班级(不满足连接条件就不显示)

右外连接

在这里插入图片描述

我们看到了多余的班级!!!

外连接是我们平常使用最频繁的连接方式,因为它可以保证我们想要的保留的数据,哪怕不满足连接条件也会被保留

5. 全外连接

mysql没有full outer join,不过我们可以用左外连接+union+右外连接间接实现全外连接

select * from student s left join class c on s.c_id = c.id
union
select * from student s right join class c on s.c_id = c.id

在这里插入图片描述

全外连接的本质就是并集