MySQL数据库（8）—— 复合查询

目录

一，基本查询回顾

二，多表查询

2.1 关于多表查询

2.2 笛卡尔积

2.3 部分演示

三，自连接

四，子查询

4.1 单行子查询

4.2 多行子查询

4.3 多列子查询

4.4 from子句使用子查询

五，合并查询

---

一，基本查询回顾

我们还是以我们MySQL数据库（6）中创建的scott表做演示：MySQL数据库（6）—— 表的增删查改_mysql表删改字段-优快云博客

下面是三张表的各字段：

员工表（emp）：

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

 部门表dept：

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

工资等级表（salgrade） ：

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

 ①查询工资高于500或岗位为MANAGER的员工，并且要求是姓名首字母为J的员工

②查询员工信息，按部门号升序并且按员工工资降序 

③查询员工信息，按年薪降序排序

④查询工资最高的员工的名字和岗位 

完成这步操作我们需要两次查询，第一次是先查询最高工资具体数目，然后根据这个数目再查询对应员工和岗位，但是我们也可以使用子查询，如下：

⑤查询工资高于平均工资的员工信息 

⑥查询每个部门的平均工资和最高工资 

⑦查询平均工资低于2000的部门号和它的平均工资 

⑧查询每种岗位的雇员总数和平均工资 

二，多表查询

2.1 关于多表查询

• 我们上面的一些查询操作都是针对emp表这一张表来的，而在实际开发中其实需要将多张表关联起来查询，这叫做“多表查询”
• 我们只需要把要查询的多个表放到from后面再以逗号隔开即可，这时MySQL会给这多张表取笛卡尔积，作为多表查询的数据源
• 多表查询的本质就是个多张表取笛卡尔积，然后再在笛卡尔积中进行查询，简单来说就是把多张表合成一个大表，然后在大表中进行查询

2.2 笛卡尔积

所谓的对多张表取笛卡尔积，就是以类似穷举的方式，得到这多张表的记录中的所有可能组成的集合，比如下面就是没做任何处理的将员工表和部门表做笛卡尔积的结果：

• 前半部分是员工表的列信息，后半部分是部门表的列信息
• 取笛卡尔积时，会先从员工表中取第一条记录然后和部门表的四个记录依次做拼接，然后再从员工表取第二条记录再次做拼接，这样当取完员工表的最后一条记录后，就完成了笛卡尔积的大表 

笛卡尔积的过滤 

在上面的表格中我们可以看到，我们多张表做笛卡尔积后，有很多数据是重复的，也就是无意义的

实际上一个员工只有和自己所在的部门信息进行组合才是有意义的，也就是两张表对应的部门号列相同时，这条数据才是有意义的，如下：

通过 表名.表列 的形式就可以单独获取一张表里的单列啦

2.3 部分演示

①显示部门号为10的部门名、员工名和员工工资

②显示各个员工姓名、工资和工资级别

三，自连接

上面我们是对不同表进行笛卡尔积，当然我们也可以对一张表做笛卡尔积，如果一张表中的某个字段能够将表中的多条记录关联起来，那么就可以通过自连接将表中通过该字段关联的记录组合起来

①显示员工姓名为“FORD”的上级领导的编号和姓名 

我们可以用子查询先查询到FORD的领导的编号，再根据领导的编号去查询领导的名字：

可以看到上面的主查询和子查询都用到的是emp表，所以我们可以将emp表煜emp表做一次笛卡尔积，如下：

四，子查询

前面我们已经简单使用过子查询了，也叫做嵌套查询，分为单行子查询、多行子查询、多列子查询以及在from字句中使用的子查询

4.1 单行子查询

①显示与SMITH员工在同一部门的员工

此外，也可以用自连接搞定这道题：

4.2 多行子查询

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

①显示与10号部门下岗位相同的别的部门的员工的信息，不包含10号部门的员工（in关键字）

②显示工资比“30号部门的所有员工的工资”高的员工的名字、工资和部门号（all关键字）

我们也可以用单行子查询找到30号部门的最高工资，然后找到工资大于该最高工资的的员工信息：

③查询工资比“30号部门任意员工的工资”高的员工的姓名、工资和部门号，包含30号部门员工 

这道题也可以等价于找到比“30号部门最低工资”高的员工信息：

4.3 多列子查询

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

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

• 多列子查询得到的结果是多列数据，在比较多列数据时需要将待比较的多个列用括号括起来，如上图
• 多列子查询返回的如果是多行数据，那么也是也可以用in、all和any关键字进行筛选的，只是多列查询的表比较复杂，需要仔细甄别

4.4 from子句使用子查询

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

①显示每个高于“自己部门平均工资”的员工的平均工资

②显示每个部门工资最高的员工名字、工资、部门和部门最高工资 

③显示每个部门的部门名、部门编号、所在地和员工数量 

这道题也可以用多表查询来解决： 

五，合并查询

是将多个查询结果进行合并，使用的操作符有两个：

• union：用于取得两个查询结果的并集，会去掉重复行
• union all：也用取得两个查询结果的并集，但是不去掉重复行

①查询工资大于2500或者职位是MANAGER的员工

查询工资大于2500和职位是MANAGER的两个SQL如下：

然后我们可以使用一句SQL将这两个SQL连起来，称为合并查询：

注意：待合并的两个查询结果的列数必须一样，否则无法合并