MySQL复合查询

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列表中指明要查询的列为姓名、部门号和工资在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函数分别查询每种岗位的雇员总数和平均工资。如下

多表查询

多表查询

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

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

说明一下

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

笛卡尔积的初步过滤

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

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

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

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

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

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

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

说明一下

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

自连接

自连接

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

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

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

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

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

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

子查询

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

单行子查询

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

显示SMITH同一部门的员工

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

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

多行子查询

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

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号部门的因此不需要再对部门号进行过滤。如下

多列子查询

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

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

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

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

说明一下

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

在from子句中使用子查询

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

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

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

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

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

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

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

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

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

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

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

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

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

如下

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

说明一下

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

合并查询

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

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

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

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

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

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

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

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

说明一下

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