MySQL复合查询

目录

一、多表查询

二、自连接

三、子查询

3.1 单行子查询

3.2 多行子查询

3.3 多列子查询

3.4 在from子句中使用子查询

四、合并查询

---

一、多表查询

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

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

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

笛卡尔积的初步过滤

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

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

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

由于部门名仅在部门表中，而员工名和员工工资仅在员工表中，因此需同时使用员工表和部门表进行多表查询，在where子句中指明筛选条件为员工的部门号等于部门编号，并且部门号为10的记录 

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

员工名和工资仅在员工表中，工资级别仅在工资等级表中，因此需同时使用员工表和工资等级表进行多表查询，在where子句中指明筛选条件为员工的工资在losal和hisal之间的记录

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

二、自连接

概念

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

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

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

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

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

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

三、子查询

3.1 单行子查询

单行子查询，即返回单行单列数据（唯一数据）的子查询

显示SMITH同一部门的员工

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

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

3.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号部门的，因此不需要再对部门号进行过滤

3.3 多列子查询

多列子查询，即返回多列数据的子查询

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

先查询SMITH所在部门的部门号和其岗位，将其作为子查询

在查询员工表时在where子句中，指明筛选条件为部门号和岗位等于子查询得到的部门号和岗位，并且员工的姓名不为SMITH即可

注意： 

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

3.4 在from子句中使用子查询

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

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

首先查询每个部门的平均工资

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

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

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

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

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

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

• 先对员工表和部门表取笛卡尔积
• 在where子句中指明筛选条件为员工的部门号等于部门的编号，筛选出有意义的记录
• 在group by子句中指明按照部门号进行分组，分别统计出每个部门的人数

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

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

四、合并查询

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

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

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

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

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

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

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

注意： 

• 待合并的两个查询结果的列的数量必须一致，否则无法合并
• 待合并的两个查询结果对应的列属性可以不一样，但不建议这样做