MySQL学习——复合查询

一、基本查询回顾

   准备查询测试表

查询测试表共有三张表，分别是员工表（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列表中指明要查询的列为姓名、工资和部门号。年薪用==sal*12+ifnull(comm,0)==表示.
注意:
由于NULL与任何值做计算得到的结果都是NULL，因此在计算年薪时不能直接用月薪的12倍加上每个员工的奖金，这样可能导致得到的年薪为NULL值。
在计算每个员工的年薪时，应该通过ifnull函数判断员工的奖金是否为NULL，如果不为NULL则ifnull函数返回员工的奖金，如果为NULL则ifnull函数返回0，避免让NULL值参与计算。

③ 显示工资最高的员工的名字和工作岗位

解决该类问题有两步,我们可以先通过select筛选出具有最高工资的人,然后再根据最高工资对员工表进行一次查询，得到工资等于最高工资的员工的姓名和岗位。如下：
1)得到最高工资是多少

2)将最高工资作为筛选条件进行筛选

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

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

解决该问题也需要进行两次查询,首先查出平均工资为多少,接着利用平均工资进行下一步的筛选.

⑤ 每个部门的平均工资和最高工资

首先根据max()和avg()进行筛选出最高工资和平均工资,然后再利用group by进行分组.

⑥ 显示平均工资低于2000的部门号和它的平均工资

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

⑦ 显示每种岗位的雇员总数，平均工资

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

二、多表查询

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

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

注意:

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

	 笛卡尔积的初步过滤

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

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

由于部门名只有在部门表中存在,而员工名和工资只在员工表中存在,所以我们需要对员工表和部门表进行多表查询.我们需要在where子句中指明筛选条件为员工的部门号等于部门编号，并且部门号为10的记录。如下：

② 显示各个员工的姓名，工资，及工资级别

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

三、自连接

 自连接的定义:
     自连接是指在同一张表进行连接查询，也就是说我们不仅可以取不同表的笛卡尔积，也可以对同一张表取笛卡尔积。

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

解决该问题可以先在emp表中使用子查询，先查到FORD领导的编号，然后再根据FORD领导的编号在员工表中通过领导的编号查找到领导的姓名。
第一步：

第二步：将第一次查询的结果作为第二次查询的一个约束条件

四、子查询

①、单行子查询

显示与SMITH同一部门的员工，但不包含SMITH

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

②、多行子查询

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

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

要解决该问题，首先要查询10号部门有哪些工作岗位，同时查询时要对查询结果进行去重操作。因为10号部门的某些员工的工作岗位可能是相同的。如下：

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

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

先查询30号部门的员工工资金额分别是多少，并且对工资的结果进行去重操作，因为工资金额可能会有重复的情况。

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

这个问题也等价于显示工资大于30号部门的所有员工的工资高的员工的姓名、工资和部门号。因此也可以使用单行子查询得到30号部门的最高工资，然后判断员工的工资是否高于子查询得到的最高工资即可。

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

首先先查询30号部门员工的工资，

然后在查询员工表时在where子句中使用any关键字，判断员工的工资是否高于子查询的得到的工资中的某一个，如果是则符合筛选条件。如下：

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

③、多列子查询

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

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

首先我们可以先查询SMITH的部门号和他的岗位，如下：

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

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

④、在from子句中进行子查询

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

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

我们首先查看每个部门的平均工资，如下：

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

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

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

首先我们先展示每个部门的最高工资，如下：

然后我们将以上查询结果作为一个临时表放在from字句后作为一个条件进行查询，在where子句中指明筛选条件为员工的部门号等于临时表中的部门号，并且员工的工资等于临时表中的最高工资。

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

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

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

五、合并查询

合并查询，是指将多个查询结果进行合并，可使用的操作符有union和union all。
注意：
1、union用于取得两个查询结果的并集，union会自动去掉结果集中的重复行。
2、union all也用于取得两个查询结果的并集，但union all不会去掉结果集中的重复行。

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

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

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

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

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

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

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