MySQL表复合查询的实现

目录 前言 一、案例准备 二、基本查询 三、多表查询 四、子查询 4.1 单行子查询 4.2 多行子查询 4.3 多列子查询 4.4 FROM子句中的子查询 4.5 合并查询 4.5.1 UNION 4.5.2 UNION ALL 五、自连接 六、

                        目录前言一、案例准备二、基本查询三、多表查询四、子查询4.1 单行子查询4.2 多行子查询4.3 多列子查询4.4 FROM子句中的子查询4.5 合并查询4.5.1 UNION4.5.2 UNION ALL五、自连接六、内外连接6.1 内连接6.2 外连接6.2.1 左外连接6.2.2 右外连接

前言

对MySQL表的基本查询还远远达不到实际开发过程中的需求，因此还需要掌握对数据库表的复合查询。本文介绍了多表查询、子查询、自连接、内外连接等复合查询的案例。

一、案例准备

来自oracle 9i的经典测试表：

emp员工表

mysql> select * from emp; +--------+--------+-----------+------+---------------------+---------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +--------+--------+-----------+------+---------------------+---------+---------+--------+ | 007369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL | 20 | | 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 | 30 | | 007521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 | 30 | | 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL | 20 | | 007654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 | 30 | | 007698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850.00 | NULL | 30 | | 007782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450.00 | NULL | 10 | | 007788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000.00 | NULL | 20 | | 007839 | KING | PRESIDENT | NULL | 1981-11-17 00:00:00 | 5000.00 | NULL | 10 | | 007844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 | 0.00 | 30 | | 007876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100.00 | NULL | 20 | | 007900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950.00 | NULL | 30 | | 007902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL | 20 | | 007934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300.00 | NULL | 10 | +--------+--------+-----------+------+---------------------+---------+---------+--------+ 14 rows in set (0.00 sec)

dept部门表

mysql> select * from dept; +--------+------------+----------+ | deptno | dname | loc | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 4 rows in set (0.00 sec)

salgrade工资等级表

mysql> select * from salgrade; +-------+-------+-------+ | grade | losal | hisal | +-------+-------+-------+ | 1 | 700 | 1200 | | 2 | 1201 | 1400 | | 3 | 1401 | 2000 | | 4 | 2001 | 3000 | | 5 | 3001 | 9999 | +-------+-------+-------+ 5 rows in set (0.00 sec)

二、基本查询

MySQL表的基本查询都是针对一张表进行的查询操作，在实际开发过程中还远远不够。以下是以下基本查询的案例：

查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J

按照部门号升序而雇员的工资降序排序

使用年薪进行降序排序

注意：年薪 = 月薪 * 12 + 绩效奖，其中有的绩效comm为NULL，在MySQL中有NULL参与运算的结果都为NULL，因此要使用到ifnull函数。

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

注意：因为要使用到max聚合函数，因此不能直接将聚会函数返回的结果作为where筛选的条件去找某一个具体的记录。因此可以先找出最大的薪资，在根据薪资找到该条记录。

但是这样的话就要使用两条SQL语句，因此可以使用子查询：

内部select查询到的结果，作为外部where筛选的条件。

显示工资高于平均工资的员工信息

和上面的一样，也需要用到子查询。

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

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

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

三、多表查询

实际开发中，数据往往来自不同的表，因此需要多表查询。以下是使用emp、dept、salgrade三张表进行多表查询的案例：

显示雇员名、雇员工资以及所在部门的名字

由于以上要查询的数据分别来自于emp表和dept表，因此要联合这两张表进行查询：

使用上面的查询方法查询出来的包含许多错误的结果，因此需要使用emp.deptno = dept.deptno条件来进行查询：

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

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

四、子查询

子查询是指嵌入到其他SQL语句中的select语句，也叫嵌套查询。

4.1 单行子查询

单行子查询指的是返回一行记录的子查询，例如：

显示SMITH同一部门的员工

首先从emp表中找出SMITH所在部门的部门号：

然后将该部门号作为筛选的条件，筛选出与该部门号相同的员工信息，并且不包含SMITH：

由此可见，子查询就是将第一次select查询的结果，作为第二次select查询的筛选条件。

4.2 多行子查询

多行子查询就是返回多行记录的子查询，此时一般会用于IN、ALL、ANY 这些关键字：

IN：表示存在，即需满足存在条件ALL：表示所有，即需满足所有条件ANY：表示任一，即需满足任一条件

查询案例：

IN关键字：查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10号部门自己的

首先查询出10号部门所有的岗位

然后将这些岗位信息作为下一次查询的筛选条件进行查询

最后去掉10号部门的员工信息

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

首先查找出30号部门所有的员工工资

然后将其作为筛选条件查找出比30号部门的所有员工工资都高的员工信息

ANY关键字：显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）

该案例的前面部分和上面的案例一样，也是首先找出30号部门所有员工的工资，然后再使用ANY关键字找出比部门30的任意员工的工资高的员工信息：

任一当然也包含了30号部门的内部员工，因此只需大于30号部门最低的员工工资的员工都会被筛选出来。

4.3 多列子查询

单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的。而多列子查询则是指查询返回多个列数据的子查询语句。

案例：查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人

首先查找出SMITH的部门号和岗位信息

然后以SMITH的部门号和岗位信息作为筛选条件进行筛选

最后去掉SMITH的相关信息

4.4 FROM子句中的子查询

FROM子句中的子查询就是指子查询语句出现在FROM后面，其实就是把子查询的结果当成一张临时表使用。

查询案例：

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

首先对部门进行分组，获取其部门号即平均工资

将查询结果作为一张临时表，获取其与emp表的笛卡尔积

最后在笛卡尔积表当中筛选出每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

查找每个部门工资最高的人的姓名、工资、部门、最高工资

首先分组查询获取每个部门的部门号和最高工资

然后将查询结果作为临时表，并获取其与emp表的笛卡尔积

从获取的笛卡尔积中筛选出每个部门工资最高的人的姓名、工资、部门、最高工资

显示每个部门的信息（部门名，编号，地址）和人员数量

方法一：使用子查询

首先对部门进行分组，查找每个部门对应的人数

将查询的结果作为临时表，获取其与dept表的笛卡尔积

从笛卡尔积表中筛选出每个部门的信息及其部门人数

方法二：使用多表

4.5 合并查询

在实际应用中，为了合并多个SELECT的执行结果，可以使用集合操作符 UNION，UNION ALL