MySQL——多表查询

多表查询

多表查询的出现，是为了解决当我们的数据不能存放在一张表上，或者我们的数据本身就是存在多张表上，需要根据字段之间的关系，联合多张表查询出想要的数据。那么根据业务实现的关系，表与表之前也出现了三种基本关系

• 一对一
• 一对多（多对一）
• 多对多

一对多（多对一）

这种关系的典型案例就是员工和部门之间的关系，一个员工只能属于一个部分，而一个部门可以拥有多个员工。

这种文字描述的关系体现在数据表上的就是：在多的一方建立外键，指向一的一方的主键。在案例中就是将员工信息表中的部分信息做外键，关联部门表中的部门信息。

新建两个表，一个是员工表一个是部门表，建表语句如下：

# 部门表 create table if not exists department( department_id int primary key, department_name varchar(20) ); # 员工表 create table if not exists employee( employee_id int , employee_name varchar(20), department_id int , foreign key (department_id) references department(department_id) );

数据内容如下：

部门表：

员工表：

从建表语句中可以看到，我们员工表的部门信息与部门表的部门信息是建立了外键约束。那么一对多的关系，就是一个员工对应一个部门，多对一就是一个部门可以对应多个员工。

多对多

这种关系的最典型的案例就是学生与课程的关系，一个学生可以选择多个课程，一个课程也可以被多个学生选择，那么两者的关系就形成了多对多的关系。

这种文字的多对多关系描述要如何提现到数据库中呢，单一的表肯定是无法描述多对多的关系，因为一个属性只能存储一个值，多个属性肯定就不仅是一个值，那么就需要一个单独的表去描述两个表之间的关系，这个表叫做中间表。

也就是说我们在描述多对多关系的时候，我们需要三张表来描述它们之间的关系：

# 学生表 create table if not exists student( student_id int, student_name varchar(20) ); # 课程表 create table if not exists class( class_id int , class_name varchar(20) ); # 中间表 create table if not exists course_selection( id int, student_id int , class_id int );

他们的数据内容如下：

学生表：

班级表：

中间表：

对于前两个表中的数据我们已经非常熟悉了，对于中间表的数据我们需要解释一下，如何去看这张表的数据呢，student_id表示学生的id，class_id表示班级的id，那么相同的student_id就表示同一个学生，那么同一个学生选择多个课程就用多个相同的student_id对应多个不同的class_id就表示同一个课程，那么我们要如何去查询数据呢？比如我们要查询id为1的学生选择了什么课程：

当我们对中间表进行查询的时候，根据筛选的条件，当条件是student_id的时候，就表示查询对应学生选择的课程，则学生号相同，课程号不同，对应的，如果要查询某一个课程被多少学生选择，我们的语句就变成了：

这样，我们就查询出了某一个课程被多少个学生所选择，至于后面更加详细的操作，我们在实际使用到的时候再去使用和介绍。

一对一

一对一关系的典型案例就是用户与用户详情的关系。

一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。

将文字描述在数据库中实现就是将任意一方加入外键，关联另一方的主键，并且设置外键为唯一的（UNIQUE）

多表查询

从多张表中进行查询

select * from 表名1,表名2;

多张表就是在form关键字的后面添加多张表，多张表之间用逗号隔开：

但是在这种情况下会出现很多的错误数据，比如张三的信息应该数据财务部，但是却和所有的部门都进行了匹配，那么这种情况肯定是不行的，这种现象叫做笛卡尔积。

笛卡尔积：是指在数学中，两个集合A和B集合的所有组合情况（在多表查询时，需要消除无效的笛卡尔积。）

消除笛卡尔积的方法就是在多表查询的时候添加条件，条件就是将两个表相关联的字段相等即可：

在上图中，employee表和department表是通过department_id这个字段关联在一起的，也就是连个表都存在一个字段叫做department_id，那么在查询的时候只保留这两个字段相等的值即可。

多表查询的分类

连接查询：

内连接：相当于查询A，B交集部分数据

外连接：

左外连接：查询左表的所有数据，以及两张表交集部分数据

右外连接：查询右表的所有数据，以及两个表交集部分数据

自连接：当前表与自身的连接查询，自连接必须使用表别名

子查询：

将自身分为两个表进行查询。

内连接

内连接查询的是两张表交集的部分。

隐式内连接：

select 字段 from 表名1,表名2 where 条件……;

显式内连接：

select 字段 from 表1 [inner] join 表2 on 连接条件……;

两者的区别主要在于表的连接形式以及连接条件的表现形式，就是是否显式的使用关键字。

首先演示使用隐式内连接的方式进行多表查询，要查询员工的姓名以及对应的部门的名称：

在上图中，我们演示了如果使用隐式内连接的方式在两张表中查询数据。

然后演示一下显式内连接，同样是查询员工的姓名以及对应部门的名称：

查询结果与之前是一样的，只不过使用了inner join和on关键字。

如果两个表中的字段有相同的部门，可以通过给表起别名，然后用“别名.列名”的方式指明他是来自那张表的列

外连接

左外连接

select 字段 from 表1 left join 表2 on 条件;

相当于查询表1（左表）的所有数据包含表1和表2交集部分的数据

右外连接

select 字段 from 表1 right join 表2 on 条件;

相当于查询表2（右表）的所有数据包含表1和表2交集部分的数据

首先演示一下左外连接，比如要查询员工的所有信息，并且查询员工对应的部门的名称：

上图就是将员工表的所有数据全部查出，并且展示部分的部门表的数据。

然后演示右外连接，比如查询出所有的部门表的信息，并且输出部门对应的员工的姓名：

这样就是查询出所有的部门以及部门对应的员工的名称。

在使用的时候，一般左外连接会用的比较多一点。

自连接

自己连接自己，自连接查询，可以是内连接查询，也可以是外连接查询

select 字段 from 表A 别名A join 表A 别名B on 条件……;

我们对员工表添加一个新的列叫做领导id：

这一列存放的是领导的ID，但是领导的信息和员工的信息在同一张表上，现在要查询员工对应领导的信息，就需要使用自连接进行查询：

注意此时他的条件，是自己关联自己，但是on之后的条件就变成了同一个表中的两个关键的字段，manager对应的就是员工的id，所以让这两个字段相等，就可以查询出员工对应的领导的信息。

使用外连接可以查询出没有领导的员工，如果要查询出有领导的员工可以使用内连接

这样查询，则会查询交集的数据，不会出现有空值的数据出现

在使用自连接的时候，必须使用别名，否则会报错，因为他不知道你的字段是来自哪一个表，尽管都来自于同一个表。

联合查询

将多次查询的结果联合起来，形成一个新的查询结果集。

select 字段 from 表A …… union [all] select 字段 from 表B ……;

比如当需要将两个条件查询的结果进行展示的时候：

比如现在我有两个语句，现在我要同时展示这两条语句的结果：

那么只要满足任意一个sql语句的结果都会被展示。

union all会直接合并两条sql语句的结果，如果中间有重复的，也就是同时满足两条sql语句的结果，则两次都保留：

对于联合查询的多张表的列数必须保持一致，字段类型也需要把持一致。

union表示对查询结果去重，union all表示直接合并。