MySQL学习之——多表查询

MySQL学习之——多表查询

上节课我们不是学习了外键吗？外键是在两张表之间建立联系。其实就能够引申到多表查询的范畴。
如果两张表没有通过外键建立连接的时候，这个时候能不能联合查询呢？

一、多表关系

1.1 一对多

案例：部门和员工
关系：一个部门对应多个员工
实现：在多的一方建立外键，指向另一方的主键。也就是在员工表建立外键references 部门表(主键)。

1.2 多对多

案例：学生和课程
关系：一个学生可以选多门课程，一个课程也可以被多个学生选择
实现：建立一张中间表格，设置两个外键分别指向学生表主键和课程表主键。多对多可以拆分成一对多。

1.3 一对一

案例：用户和用户详情
关系：一个用户只有一个用户详情。这个主要是用于单表拆分的，将一张表的基础字段一张表，把详情放到另一个表里。这样能够提升操作效率。
实现：在任意一方加入外键，指向另一张表的主键，并且外键设为（UNIQUE）

二、多表查询

2.1内连接查询

如果我们直接使用

SELECT * FROM 表1，表2;
1

这样会得到表1和表2的所有组合，就是我们所说的笛卡尔积；

本来每个人只有一个部门的，即employee表中的DEPART_ID对应department表中的DEPART_ID。
但现在一看就是两张表的所有组合。
为了消除无效组合，我们需要加入条件查询两张表的共有数据。
如：

SELECT * FROM 表1，表2 WHERE 条件;
eg:
	SELECT 
		* 
	FROM 
		employee，department 
	WHERE 
		employee.DEPART_ID=department.DEPART_ID
		;
1
2
3
4
5
6
7
8
9

这种查询实际上是是内连接INNER查询的缩写形式，实际上的形式应当为

SELECT * FROM 表1 INNER JOIN 表2 WHERE 条件;
eg：
SELECT * FROM employee as e INNER JOIN department as d ON e.DEPART_ID=d.DEPART_ID;
1
2
3

ps:内外连接的条件用ON 不用WHERE;

2.2 外连接

外连接分为左外连接和右外连接。
左外连接是显示左表所有数据和右表中两表有交集的数据

SELECT 表1全字段,表2想要的字段 FROM 表一 LEFT [OUTER] JOIN 表二 ON 条件;
eg:
SELECT e.*,d.DEPART_ID FROM employee as e LEFT OUTER JOIN department as d ON e.DEPART_ID=d.DEPART_ID;
1
2
3

右外连接是显示右表所有数据和左表中两表有交集的数据

SELECT 表1想要字段,表2全字段 FROM 表一 RIGHT [OUTER] JOIN 表二 ON 条件;
eg:
SELECT e.DEPART_ID,d.* FROM employee as e RIGHT OUTER JOIN department as d ON e.DEPART_ID=d.DEPART_ID;
1
2
3

2.3 自连接

我觉得这个就很神奇，比如说下面这张图中，每一行数据有MANAGER_ID,也就是这个人的主管的工号。那我想知道主管是谁怎么办？

这就可以用到内连接的概念了！

内连接示例

SELECT a.NAME ,b.NAME FROM employee a, employee b where a.MANAGER_ID=b.EMPLOYEE_ID;
1

相当于把一张表看成了两张表来用。

2.4 联合查询

SELECT * FROM 表1 
UNION [ALL]
SELECT * FROM 表2;
1
2
3

联合查询的一些规则：
1、多条查询语句的查询列数必须保持一致，否则报错。

2、多条查询语句的列值类型和顺序最好保持一致。

3、UNION 关键字默认不查询重复记录，但只要有一个字段不一样就认为是两条记录。但UNION ALL 可以显示重复数据

这么看来联合查询像是很多数据分布在不同的表里面，想要同时用这些表，就用联合查询。

2.5 子查询

子查询与我们经常说的子表有些联系，就是你在一个表中查询的结果可以作为下一次查询的依据，这个一句可以是条件值 WHERE,来源FROM, 显示字段SELECT后。

查询出来的类型也有不同的种类：
1.标量子查询(一个值)
2.列子查询(查询结果为一列,一个字段的多条数据)
3.行子查询(查询结果为一行,多个字段的一条数据)
4.表子查询(查询结果为一个表)

1)标量子查询

标量子查询一般放在WHERE后面当条件值，如

查询一个研发部的所有员工;
SELECT * FROM employee WHERE DEPART_ID=$研发部ID;
1
2

但研发部ID只有在department表中，所以我们要先去deparment表中查询‘研发部’的DEPART_ID

SELECT DEPART_ID FROM department WHERE DEPART='研发部'; 
1

所以合并之后就是

SELECT * FROM employee WHERE DEPART_ID=(SELECT DEPART_ID FROM department WHERE DEPART='研发部');
1

查询结果如下：

2)列子查询

列子查询会让我们想到是多个值，也是可以放在WHERE后面当条件值的。
常常会用在以下运算符之中

那我们显示研发部和销售部的所有员工数据应该怎么写呢？

SELECT * FROM employee WHERE DEPART_ID IN (SELECT DEPART_ID FROM department WHERE DEPART='研发部' OR DEPART='销售部');
1