【MySQL学习】MySQL表的复合查询2

【MySQL学习】MySQL表的复合查询1：https://developer.aliyun.com/article/1384034

查询案例：

IN关键字：查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10号部门自己的

1. 首先查询出10号部门所有的岗位

2. 然后将这些岗位信息作为下一次查询的筛选条件进行查询

3. 最后去掉10号部门的员工信息

ALL关键字：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

1. 首先查找出30号部门所有的员工工资

2. 然后将其作为筛选条件查找出比30号部门的所有员工工资都高的员工信息

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

该案例的前面部分和上面的案例一样，也是首先找出30号部门所有员工的工资，然后再使用ANY关键字找出比部门30的任意员工的工资高的员工信息：

任一当然也包含了30号部门的内部员工，因此只需大于30号部门最低的员工工资的员工都会被筛选出来。

4.3 多列子查询

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

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

1. 首先查找出SMITH的部门号和岗位信息

2. 然后以SMITH的部门号和岗位信息作为筛选条件进行筛选

3. 最后去掉SMITH的相关信息

4.4 FROM子句中的子查询

FROM子句中的子查询就是指子查询语句出现在FROM后面，其实就是把子查询的结果当成一张临时表使用。

查询案例：

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

1. 首先对部门进行分组，获取其部门号即平均工资

2. 将查询结果作为一张临时表，获取其与emp表的笛卡尔积

3. 最后在笛卡尔积表当中筛选出每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

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

1. 首先分组查询获取每个部门的部门号和最高工资

2. 然后将查询结果作为临时表，并获取其与emp表的笛卡尔积

3. 从获取的笛卡尔积中筛选出每个部门工资最高的人的姓名、工资、部门、最高工资

显示每个部门的信息（部门名，编号，地址）和人员数量

方法一：使用子查询

1. 首先对部门进行分组，查找每个部门对应的人数

2. 将查询的结果作为临时表，获取其与dept表的笛卡尔积

3. 从笛卡尔积表中筛选出每个部门的信息及其部门人数

方法二：使用多表

4.5 合并查询

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

4.5.1 UNION

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

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

可以发现，使用 OR 和 UNION 查询出来的结果相同。

4.5.2 UNION ALL

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

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

此时可以发现UNION和UNION ALL的唯一区别就是前者会对查找结果进行去重，而后者不会。

五、自连接

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

查询案例：

显示员工FORD的上级领导的编号和姓名

方法一：子查询

首先从emp表中找出FORD领导的编号，然后将其作为筛选条件查找出FORD的领导信息。

方法二：自连接

1. 首先将两张emp表分别作为leader表和worker表，查找出所有领导与员工之间的关系表

2. 然后从关系表中，查找出员工为FORD的领导信息

六、内外连接

6.1 内连接

内连接实际上就是利用WHERE子句对两张表形成的笛卡尔积进行筛选，因此前面所有的复合查询操作都属于内连接，同时内连接也是实际开发过程中使用最多的连接查询。

内连接语法：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;

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

方法一：使用前面的查询方式

方法二：使用标准内连接查询

1. 首先通过内连接查询出所有员工与其所在部门名之间的关系

2. 从以上关系中筛选出SMITH与其部门名

6.2 外连接

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

6.2.1 左外连接

语法：

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

案例：

-- 建两张表
create table stu (id int, name varchar(30)); -- 学生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam (id int, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8);

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

可以发现，左外连接以左表的内容为准，显示其全部内容，如果右边没有对应信息，则显示为NULL。

6.2.2 右外连接

语法：

select 字段 from 表名1 right join 表名2  on 连接条件；

案例：

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

对dept表和emp表联合查询，列出部门名称和这些部门的员工信息，同时列出没有员工的部门

dept表左外连接emp表：

emp表右外连接dept表：

由此可见左外连接和右外连接可以相互转换。