MySQL数据库⑦_复合查询+内外链接（多表/子查询）

1. 回顾基本查询

2. 多表查询

2.1 笛卡尔积初步过滤

3. 自连接

4. 子查询

4.1 单行子查询

4.2 多行子查询

4.2 多列子查询

4.2 from子句中使用子查询

5. 合并查询

6. 内外链接

6.1 内连接

6.2 左外链接

6.2 右外连接

本篇完。

1. 回顾基本查询

先回顾一下前两篇内容，然后融合起来学一学复合查询

MySQL数据库⑤_基本查询DQL_表的增删查改DML-CSDN博客

MySQL数据库⑥_内置函数(日期函数+字符串函数+数学函数等)-CSDN博客

测试还是用基本查询使用的雇员信息表：

雇员信息表包括员工表（emp）、部门表（dept）和工资等级表（salgrade）。

员工表（emp）中包含如下字段：

雇员编号（empno）。
雇员姓名（ename）。
雇员职位（job）。
雇员领导编号（mgr）。
雇佣时间（hiredate）。
工资月薪（sal）。
奖金（comm）。
部门编号（deptno）。

员工表（emp）中的内容如下：

部门表（dept）中包含如下字段：

部门编号（deptno）。
部门名称（dname）。
部门所在地点（loc）。

部门表（dept）中的内容如下：

工资等级表（salgrade）中包含如下字段：

等级（grade）。
此等级最低工资（losal）。
此等级最高工资（hisal）。

工资等级表（salgrade）中的内容如下：

测试：查询工资高于500或岗位为MANAGER的员工，同时要求员工姓名的首字母为大写的J：在where子句中指明筛选条件为工资高于500或岗位为MANAGER，并且通过模糊匹配指明员工姓名的首字母为大写的J，在select的column列表中指明要查询的列为姓名、工资和岗位。如下：

查询员工信息，按部门号升序而员工工资降序显示：在select的column列表中指明要查询的列为姓名、部门号和工资，在order by子句中依次指明按部门号排升序和按员工工资排降序，即不同部门的员工按照部门号排升序，而同一部门的员工按员工工资排降序。如下：

查询员工信息，按年薪（年薪包含月薪+奖金）降序显示：在select的column列表中指明要查询的列为姓名和年薪，在order by子句中指明按年薪进行降序排序。如下：

由于NULL与任何值做计算得到的结果都是NULL，因此在计算年薪时不能直接用月薪的12倍加上每个员工的奖金，这样可能导致得到的年薪为NULL值。

在计算每个员工的年薪时，应该通过ifnull函数判断员工的奖金是否为NULL，如果不为NULL则ifnull函数返回员工的奖金，如果为NULL则ifnull函数返回0，避免让NULL值参与计算。

查询工资最高的员工的姓名和岗位：解决该问题需要进行两次查询，先对员工表进行一次查询得到最高工资，然后再根据最高工资对员工表进行一次查询，得到工资等于最高工资的员工的姓名和岗位。如下：

此外，这种问题还可以使用子查询，将第一次查询的SQL语句用括号括起来，作为最高工资直接在第二次查询的SQL语句中使用。如下：

查询工资高于平均工资的员工信息：解决该问题也需要进行两次查询，先对员工表进行一次查询得到平均工资，然后再根据平均工资对员工表进行一次查询，筛选出工资高于平均工资的员工信息，该问题同样可以使用子查询。如下：

查询每个部门的平均工资和最高工资：在group by子句中指明按照部门号进行分组，在select语句中使用avg函数和max函数，分别查询每个部门的平均工资和最高工资。如下：

查询平均工资低于2000的部门号和它的平均工资：在group by子句中指明按照部门号进行分组，在select语句中使用avg函数查询每个部门的平均工资，在having子句中指明筛选条件为平均工资小于2000。如下：

查询每种岗位的雇员总数和平均工资：在group by子句中指明按照岗位进行分组，在select语句中使用count函数和avg函数，分别查询每种岗位的雇员总数和平均工资。如下：

2. 多表查询

工作中复合查询和内外链接是比较重要的。

上面的基础查询都是在一张表的基础上进行的查询，而实际开发中往往需要将多张表关联起来进行查询，这就叫做多表查询。
在进行多表查询时，只需要将多张表的表名依次放到from子句之后，用逗号隔开即可，这时MySQL将会对给定的这多张表取笛卡尔积，作为多表查询的初始数据源。
多表查询的本质，就是对给定的多张表取笛卡尔积，然后在笛卡尔积中进行查询。

所谓的对多张表取笛卡尔积，就是得到这多张表的记录的所有可能有序对组成的集合，比如下面对员工表和部门表进行多表查询，由于查询语句中没有指明筛选条件，因此最终得到的结果便是员工表和部门表的笛卡尔积。

员工表和部门表的笛卡尔积由两部分组成，前半部分是员工表的列信息，后半部分是部门表的列信息。
对员工表和部门表取笛卡尔积时，会先从员工表中选出一条记录与部门表中的所有记录进行组合，然后再从员工表中选出另一条记录与部门表中的所有记录进行组合，以此类推，最终得到的就是这两张表的笛卡尔积。

2.1 笛卡尔积初步过滤

需要注意的是，对多张表取笛卡尔积后得到的数据并不都是有意义的，比如对员工表和部门表取笛卡尔积时，员工表中的每一个员工信息都会和部门表中的每一个部门信息进行组合，而实际一个员工只有和自己所在的部门信息进行组合才是有意义的，这里需要从笛卡尔积中筛选出员工的部门号和部门的编号相等记录。如下：

进行笛卡尔积的多张表中可能会存在相同的列名，这时在选中列名时需要通过表名.列名的方式进行指明。

显示部门号为10的部门名、员工名和员工工资：由于部门名只有部门表中才有，而员工名和员工工资只有员工表中才有，因此需要同时使用员工表和部门表进行多表查询，在where子句中指明筛选条件为员工的部门号等于部门编号，并且部门号为10的记录。如下：

第一个筛选条件已经筛选出员工的部门号和部门编号相等的记录，因此在筛选部门号等于10的部门时，可以使用员工表中的部门号，也可以使用部门表中的部门编号。

显示各个员工的姓名、工资和工资级别：由于员工名和工资只有员工表中才有，而工资级别只有工资等级表中才有，因此需要同时使用员工表和工资等级表进行多表查询，在where子句中指明筛选条件为员工的工资在losal和hisal之间的记录。如下：

员工表和工资等级表的笛卡尔积中，将每一个员工的信息和每一个工资等级的信息都进行了组合，而实际一个员工只有和自己的工资对应的工资等级信息进行组合才是有意义的。
因此需要根据各个工资等级的最低工资和最高工资判断一个员工是否属于该工资等级，进而筛选出有意义的记录。

3. 自连接

自连接是指在同一张表进行连接查询，也就是说我们不仅可以取不同表的笛卡尔积，也可以对同一张表取笛卡尔积。
如果一张表中的某个字段能够将表中的多条记录关联起来，那么就可以通过自连接将表中通过该字段关联的记录组合起来。

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

解决该问题可以使用子查询，先对员工表进行查询得到FORD的领导的编号，然后再根据领导的编号对员工表进行查询得到FORD领导的姓名。如下：

此外，解决该问题也可以使用自连接，因为员工表中的mgr字段能够将表中员工的信息和员工领导的信息关联起来。如下：

对员工表进行自连接后，在where子句中指明筛选条件为员工的领导编号等于领导的编号，这时就能筛选出每个员工信息与其领导信息组合形成的记录，进一步指明筛选条件为员工的姓名为FORD，这时便能筛选出员工FORD的信息和他的领导的信息组成的记录。如下：

由于自连接是对同一张表取笛卡尔积，因此在自连接时至少需要给一张表取别名，否则无法区分这两张表中的列。

4. 子查询

子查询是指嵌入在其他SQL语句中的查询语句，也叫嵌套查询。
子查询可分为单行子查询、多行子查询、多列子查询，以及在from子句中使用的子查询。

4.1 单行子查询

单行子查询，是指返回单行单列数据的子查询。

练习：显示SMITH同一部门的员工，但不含SMITH：

在子查询中查询SMITH所在的部门号，在where子句中指明筛选条件为员工部门号等于子查询返回的部门号，并且员工的姓名不为SMITH。如下：

此外，解决该问题也可以使用自连接，因为和SMITH同一部门的员工的信息也在员工表当中，因此对员工表进行自连接后，在where子句中指明表1的员工姓名为SMITH，并且表1和表2的部门号必须相等，并且表2的员工姓名不为SMITH，这样也能筛选出和SMITH同一部门的员工信息。如下：

4.2 多行子查询

多行子查询，是指返回多行单列数据的子查询

in关键字演示：显示和10号部门的工作岗位相同的员工的名字、岗位、工资和部门号，但是不包含10号部门的员工

先查询10号部门有哪些工作岗位，在查询时最好对结果进行去重，因为10号部门的某些员工的工作岗位可能是相同的。如下：

然后将上述查询作为子查询，在查询员工表时在where子句中使用in关键字，判断员工的工作岗位是子查询得到的若干岗位中的一个，如果是则符合筛选条件，由于要求筛选出来的员工不包含10号部门的，因此还需要在where子句中指明筛选条件为部门号不等于10。如下：

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

先查询30号部门员工的工资，在查询时最好对结果进行去重，因为30号部门的某些员工的工资可能是相同的。如下：

然后将上述查询作为子查询，在查询员工表时在where子句中使用all关键字，判断员工的工资是否高于子查询得到的所有工资，如果是则符合筛选条件。如下：

但实际这道题也等价于找到工资高于30号部门的最高工资的员工，因此也可以使用单行子查询得到30号部门的最高工资，然后判断员工的工资是否高于子查询得到的最高工资即可。如下：

any关键字演示：显示工资比30号部门的任意员工的工资高的员工的姓名、工资和部门号，包含30号部门的员工

解决该题目也需要先查询30号部门员工的工资，然后在查询员工表时在where子句中使用any关键字，判断员工的工资是否高于子查询的得到的工资中的某一个，如果是则符合条件。如下：

但实际这道题也等价于找到工资高于30号部门的最低工资的员工，因此也可以使用单行子查询得到30号部门的最低工资，然后判断员工的工资是否高于子查询得到的最低工资即可，由于要求筛选出来的员工包含30号部门的，因此不需要再对部门号进行过滤。如下：

4.2 多列子查询

多列子查询，是指返回多列数据的子查询。

演示：显示和SMITH的部门和岗位完全相同的员工，不包含SMITH本人

先查询SMITH所在部门的部门号和他的岗位。如下：

然后将上述查询作为子查询，在查询员工表时在where子句中，指明筛选条件为部门号和岗位等于子查询得到的部门号和岗位，并且员工的姓名不为SMITH即可。如下：

多列子查询得到的结果是多列数据，在比较多列数据时需要将待比较的多个列用圆括号括起来。
多列子查询返回的如果是多行数据，在筛选数据时也可以使用in、all和any关键字。

4.2 from子句中使用子查询

子查询语句不仅可以出现在where子句中，也可以出现在from子句中。
子查询语句出现from子句中，其查询结果将会被当作一个临时表使用。

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

首先查询每个部门的平均工资。如下：

由于显示信息中包含部门的平均工资，需要同时使用员工表和上述的查询结果进行多表查询，这时可以将上述查询作为子查询放在from子句中，然后对员工表和临时表取笛卡尔积，在where子句中指明筛选条件为员工的部门号等于临时表中的部门号，并且员工的工资大于临时表中的平均工资。如下：

在from子句中使用子查询时，必须给子查询得到的临时表取一个别名，否则查询将会出错。

练习：显示每个部门工资最高的员工的姓名、工资、部门和部门的最高工资

先查询每个部门的最高工资。如下：

将上述查询作为子查询放在from子句中，然后对员工表和临时表取笛卡尔积，在where子句中指明筛选条件为员工的部门号等于临时表中的部门号，并且员工的工资等于临时表中的最高工资。如下：

练习：显示每个部门的部门名、部门编号、所在地址和人员数量

在group by子句中指明按照部门号进行分组，分别查询每个部门的人员数量。如下：

将上述查询作为子查询放在from子句中，然后对员工表和临时表取笛卡尔积，在where子句中指明筛选条件为员工的部门号等于临时表中的部门号即可。如下：

此外，除了上述子查询+多表查询的方式外，也可以只使用多表查询解决该问题。

但由于题目同时要求显示每个部门的部门名和所在地址，因此在group by子句中需要添加按照部门名和地址进行分组。如下：

因为在select语句中新增了要显示部门名和所在地址，因此需要在group by子句中也添加这两个字段，表明当部门号相同时按照部门名进行分组，当部门名也相同时继续按照所在地址进行分组。
但实际在上述场景中部门号相同的记录，它们的部门名和所在地址也一定是相同的，因此在我们看来group by中继续添加这两个字段没什么意义，但MySQL语句要求我们必须添加。

5. 合并查询

合并查询，是指将多个查询结果进行合并，可使用的操作符有union和union all。

显示工资大于2500或职位是MANAGER的员工：

查询工资大于2500的员工的SQL如下：

查询职位是MANAGER的员工的SQL如下：

要查询工资大于2500或职位是MANAGER的员工，可以使用or操作符将where子句中的两个条件关联起来。如下：

在合并查询这里，可以使用union操作符将上述的两条查询SQL连接起来，这时将会得到两次查询结果的并集，并且会对合并后的结果进行去重。如下：

此外，也可以使用union all操作符将上述的两条查询SQL连接起来，这时将也会得到两次查询结果的并集，但不会对合并后的结果进行去重。如下：

6. 内外链接

表的连接分为内连接和外连接，外连接又分为左外链接和右外连接。

6.1 内连接

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

内连接的SQL如下：

select ... from t1 inner join t2 on 连接条件 [inner join t3 ON 连接条件] ... and 其他条件;

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

现在要显示SMITH的名字和其所在部门的名称，按照复合查询的做法就是，取员工表和部门表的笛卡尔积，在where子句中指明筛选条件为员工的部门号等于部门的部门号，筛选出每个员工匹配的部门信息，并指明员工姓名为SMITH，筛选出SMITH的信息和其所在部门的信息。如下：

实际上述多表查询的方式本质就是内连接，用标准的内连接SQL编写：

- 先对员工表和部门表取笛卡尔积。
- 在where子句中指明筛选条件为员工的部门号等于部门的编号，筛选出有意义的记录。
- 在group by子句中指明按照部门号进行分组，分别统计出每个部门的人数。
- union用于取得两个查询结果的并集，union会自动去掉结果集中的重复行。
- union all也用于取得两个查询结果的并集，但union all不会去掉结果集中的重复行。
- 待合并的两个查询结果的列的数量必须一致，否则无法合并。
- 待合并的两个查询结果对应的列属性可以不一样，但不建议这样做。
- [ ]中代表的是可选项。，可不加
- 内连接的条件通过连接条件指明，用户的其他筛选条件通过其他条件指明。
这样筛选出来的结果和多表查询筛选出来的结果是一样的，只不过是写法有一点不同而已。如下：

6.2 左外链接

左外连接的SQL如下：
```
select ... from t1 left join t2 on 连接条件 [left join t3 on 连接条件] ... AND 其他条件;
```
演示：查询所有学生的成绩，就算这个学生没有成绩，也要将学生的个人信息显示出来

        给出一张学生表和一张成绩表，学生表中的name代表的是学生的姓名，id代表的是学生的学号。如下：

成绩表中的id代表的是考试学生的学号，score代表的是学生的成绩。如下：

        现在要显示所有学生的成绩，如果直接使用内连接将学生表和成绩表连接起来，然后筛选出学生学号等于考试学生学号的记录，那么只能筛选出有考试成绩的学生信息。如下：

        而题目要求将没有成绩的学生的个人信息也要显示出来，也就是学生表当中的内容需要完全被显示出来。如果在连接学生表和成绩表时将学生表放在左侧，那么就可以使用左外连接，这时如果左侧表中的某条记录根据连接条件没有找到匹配的右侧表中的记录，就会直接显示左侧表中的记录信息，而其对应的右侧表中的列信息将会用NULL值进行填充。如下：

6.2 右外连接

右外连接的SQL如下：
```
select ... from t1 right join t2 on 连接条件 [right join t3 on 连接条件] ... AND 其他条件;
```
演示1：查询所有的成绩，就算这个成绩没有学生与它对应，也要将成绩信息显示出来

        题目要求将没有学生与它对应的成绩信息也要显示出来，也就是成绩表当中的内容需要完全被显示出来。如果在连接学生表和成绩表时将成绩表放在右侧，那么就可以使用右外连接，这时如果右侧表中的某条记录根据连接条件没有找到匹配的左侧表中的记录，就会直接显示右侧表中的记录信息，而其对应的左侧表中的信息将会用NULL值进行填充。如下：

演示2：列出部门名称和这些部门的员工信息，同时列出没有员工的部门

        由于题目要求同时列出部门名和员工信息，因此需要将部门表和员工表连接起来，连接条件就是部门的部门号要等于员工所在的部门号。如果直接使用内连接进行查询，那么将会显示出所有满足连接条件的记录。如下：

        而题目要求同时列出没有员工的部门，也就是部门表当中的内容需要完全被显示出来，如果在连接部门表和员工表时将部门表放在左侧，那么就可以使用左外连接。如下：

当然，如果要使用右外连接，那么可以在连接部门表和员工表时将部门表放在右侧。如下：

本篇完。

        工作中复合查询和内外链接是比较重要的。还有下一部分是MySQL也很重要的索引和事务，下一篇是MySQL索引。
- - 将员工表和部门号放在from子句中并通过inner join关键字隔开。
  - 在on子句后指明内连接的条件为员工的部门号等于部门的部门号，保证筛选出来的数据是有意义的。
  - 在and之后指明筛选条件为员工的姓名为SMITH。
  - [ ]中代表的是可选项。，可不加
  - 左外连接的条件通过连接条件指明，用户的其他筛选条件通过其他条件指明。
  - [ ]中代表的是可选项。，可不加
  - 左外连接的条件通过连接条件指明，用户的其他筛选条件通过其他条件指明。