【MySQL学习】MySQL表的复合查询

前言

对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同一部门的员工

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

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

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

4.2 多行子查询

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

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

查询案例

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

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

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

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

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

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

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

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

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

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

4.3 多列子查询

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

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

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

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

3. 最后去掉SMITH的相关信息

4.4 FROM子句中的子查询

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

查询案例

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

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

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

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

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

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

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

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

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

方法一使用子查询

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

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

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

方法二使用多表

4.5 合并查询

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

4.5.1 UNION

该操作符用于取得两个结果集的并集。当使用该操作符时会自动去掉结果集中的重复行。

案例将工资大于2500或职位是MANAGER的人找出来

可以发现使用 OR 和 UNION 查询出来的结果相同。

4.5.2 UNION ALL

该操作符用于取得两个结果集的并集。当使用该操作符时不会去掉结果集中的重复行。

案例将工资大于2500或职位是MANAGER的人找出来

此时可以发现UNION和UNION ALL的唯一区别就是前者会对查找结果进行去重而后者不会。

五、自连接

所谓的自连接是指在同一张表连接查询。
查询案例

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

方法一子查询

首先从emp表中找出FORD领导的编号然后将其作为筛选条件查找出FORD的领导信息。

方法二自连接

1. 首先将两张emp表分别作为leader表和worker表查找出所有领导与员工之间的关系表

2. 然后从关系表中查找出员工为FORD的领导信息

六、内外连接

6.1 内连接

内连接实际上就是利用WHERE子句对两张表形成的笛卡尔积进行筛选因此前面所有的复合查询操作都属于内连接同时内连接也是实际开发过程中使用最多的连接查询。

内连接语法

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件;

案例显示SMITH的名字和部门名称

方法一使用前面的查询方式

方法二使用标准内连接查询

1. 首先通过内连接查询出所有员工与其所在部门名之间的关系

2. 从以上关系中筛选出SMITH与其部门名

6.2 外连接

外连接分为左外连接和右外连接。如果联合查询左侧的表完全显示我们就说是左外连接右侧的表完全显示就是右外连接。

6.2.1 左外连接

语法

select 字段名  from 表名1 left join 表名2 on 连接条件;

案例

-- 建两张表
create table stu (id int, name varchar(30)); -- 学生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam (id int, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8);

查询所有学生的成绩如果这个学生没有成绩也要将学生的个人信息显示出来

可以发现左外连接以左表的内容为准显示其全部内容如果右边没有对应信息则显示为NULL。

6.2.2 右外连接

语法

select 字段 from 表名1 right join 表名2  on 连接条件

案例

对stu表和exam表联合查询把所有的成绩都显示出来即使这个成绩没有学生与它对应也要显示出来

对dept表和emp表联合查询列出部门名称和这些部门的员工信息同时列出没有员工的部门

dept表左外连接emp表

emp表右外连接dept表

由此可见左外连接和右外连接可以相互转换。