MySQL连表查询

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
1
2
3
4

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

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

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

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

这里class表的id字段多了一个空格，数据库引擎找不到相同的列，结果就是交叉连接，排列组合
数据库引擎找不到相同的列，结果会很奇葩；

只返回了满足条件的前三行

2. 交叉连接

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

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

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

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

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

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

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

想要继续缩小范围，可以在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
1
2
3

这样看似乎没什么问题，但是如果一些学生没有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
1
2
3

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

我们对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
1
2
3

全外连接的本质就是并集