MySQL的复合查询

1. 多表查询

前面我们讲解的mysql表的查询都是对一张表进行查询，在实际开发中这远远不够。实际开发中往往数据来自不同的表，所以需要多表查询。我们还是用一个简单的公司管理系统，有三张表EMP、DEPT、SALGRADE来演示如何进行多表查询。

显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表，因此要联合查询。

可以看到雇员名、雇员工资在员工表，部门的名字在另外一张表。但是MySQL支持查询多张表。

它是从第一张表中选出第一条记录，和第二条表的所有记录进行组合。然后从第一张表中选出第二条记录，再和第二条表的所有记录进行组合，依此类推。

不加过滤条件，得到的结果称为笛卡尔积，它的本质其实就是数据的穷举。
因为是穷举，有许多的消息是没有意义的，在这个表中，部门号不一样的就没有意义，所以我们需要过滤掉。

在判断的时候，我们需要指明是哪张表的字段才能进行比较。下面我们再进行筛选：

多表查询的思路：
1.先确定和哪些表有关
2.把多张表转成一张表
3.根据要求进行比较过滤

显示各个员工的姓名，工资，及工资级别：
员工表在emp中，工作级别在salgrade表中：

因为薪水是在一个范围的，所以我们可以通过这个范围来过滤。

2. 自连接

自连接是指在同一张表连接查询。

我们直接自链接是不可以的，需要先给表重命名才能进行自链接。

显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号–empno）：

我们要找一个员工上级领导的姓名，因为上级领导也是员工，所以他也在这个表里。
方法一：使用的子查询
在MySQL中select是支持嵌套的：

这是分两步的查询，我们也可以合在一起。

方法二：使用多表查询（自查询）

我们先把所有的结果算出来了，下面我们就开始进行筛选，如果表1里的mgr等于表2的empno就是有效的：

3. 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。

3.1 单行子查询

返回一行记录的子查询。
显示和SMITH员工同一部门的员工：

3.2 多行单列子查询

返回多行记录的子查询。
in关键字；查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的。
首先，我们先把10号部门的工作岗位列举出来：

那么我们就需要找出其它部门和这几个岗位相同的，这里我们就需要使用关键字in：

in的意思是在这个范围中任何一个就被筛选出来。

最后我们需要把10号部门的给去掉：

all关键字；显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
按照之前的方法：

我们先把30号部门的最高工资取出来，然后用其它部门的来进行比较。

用all关键字：

这是30号部门的所有人的工资。

any关键字；显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

3.3 单行多列子查询

单行子查询是指子查询只返回单列，单行数据。多行子查询是指返回单列多行数据。都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句。

查询和SMITH员工的部门和岗位完全相同的所有雇员，不含SMITH本人。

我们要查询工作和部门都是这个的员工。

最后我们需要把SMITH自己给去掉。

3.4 在from子句中使用子查询

我们知道多表查询，在from后面是可以跟多张表进行笛卡尔积的。

在前面我们可以把查询的结果看作成一张表，然后进行笛卡尔积：

显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

我们先把每个部门的平均工资算出来。然后和我们的员工进行笛卡尔积。

最后根据这个表来筛选：

查找每个部门工资最高的人的姓名、工资、部门、最高工资。

首先，我们把每个部门的最高工资查出来。

然后我们和员工表进行笛卡尔积，把有效数据筛选出来。

4. 合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all

4.1 union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

将工资大于25000或职位是MANAGER的人找出来

4.2 union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

将工资大于2500或职位是MANAGER的人找出来

这里去掉了重复记录。

5. 内连接

表的连接分为内连和外连。内连接实际上就是利用where子句对两种表形成的笛卡儿积进行筛选，前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询。

正规的语法：

显示SMITH的名字和部门名称：

用前面的写法：

用标准的内连接写法：

6. 外连接

外连接分为左外连接和右外连接。

6.1 左外连接

如果联合查询，左侧的表完全显示我们就说是左外连接。

举个例子：
我们先建两张表：

我们先创建一个学生表，然后插入4条数据。

我们在成绩表也插入一些数据，但是两个表对应不上。

查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来。

当左边表和右边表没有匹配时，也会显示左边表的数据。

6.2 右外连接

如果联合查询，右侧的表完全显示我们就说是右外连接。

对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要显示出来