【MySQL】第十一弹---复合查询全攻略：多表、自连接、子查询与合并查询

✨个人主页： 熬夜学编程的小林

💗系列专栏： 【C语言详解】 【数据结构详解】【C++详解】【Linux系统编程】【MySQL】

目录

1. 复合查询

1.1 基本查询回顾

1.2 多表查询

1.3 自连接

1.4 子查询

1.4.1 单行子查询

1.4.2 多行子查询

1.4.3 多列子查询

1.4.4 在from子句中使用子查询

1.4.5 合并查询

1.4.5.1 union

1.4.5.3 union all

1. 复合查询

前面我们讲解的mysql表的查询都是对一张表进行查询，在实际开发中这远远不够。

1.1 基本查询回顾

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

# 使用%通配符
mysql> select * from emp where (sal > 500 or job ='MANAGER') and ename like 'J%';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007900 | JAMES | CLERK   | 7698 | 1981-12-03 00:00:00 |  950.00 | NULL |     30 |
+--------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)

# 使用字符串截取函数
mysql> select * from emp where (sal > 500 or job ='MANAGER') and substring(ename,1,1)='J';
+--------+-------+---------+------+---------------------+---------+------+--------+
| empno  | ename | job     | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+---------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 007900 | JAMES | CLERK   | 7698 | 1981-12-03 00:00:00 |  950.00 | NULL |     30 |
+--------+-------+---------+------+---------------------+---------+------+--------+
2 rows in set (0.00 sec)

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

select * from emp order by deptno asc,sal desc;

• 使用年薪进行降序排序 

年薪 = 月薪 * 12 或者 年薪 = 月薪 * 12 + 奖金(comm)

年薪 = 月薪 * 12

select ename,sal*12 年薪 from emp order by 年薪 desc;

 年薪 = 月薪 * 12 + 奖金

select ename,sal*12+ifnull(comm,0) 年薪 from emp order by 年薪 desc;

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

mysql> select ename,job,sal from emp where sal = (select max(sal) from emp);
+-------+-----------+---------+
| ename | job       | sal     |
+-------+-----------+---------+
| KING  | PRESIDENT | 5000.00 |
+-------+-----------+---------+
1 row in set (0.00 sec)

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

mysql> select * from emp where sal > (select avg(sal) from emp);
+--------+-------+-----------+------+---------------------+---------+------+--------+
| empno  | ename | job       | mgr  | hiredate            | sal     | comm | deptno |
+--------+-------+-----------+------+---------------------+---------+------+--------+
| 007566 | JONES | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 | NULL |     20 |
| 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 |
| 007902 | FORD  | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 | NULL |     20 |
+--------+-------+-----------+------+---------------------+---------+------+--------+
6 rows in set (0.00 sec)

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

mysql> select deptno,avg(sal),max(sal) from emp group by deptno;
+--------+-------------+----------+
| deptno | avg(sal)    | max(sal) |
+--------+-------------+----------+
|     10 | 2916.666667 |  5000.00 |
|     20 | 2175.000000 |  3000.00 |
|     30 | 1566.666667 |  2850.00 |
+--------+-------------+----------+
3 rows in set (0.00 sec)

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

mysql> select deptno,avg(sal) myavg from emp group by deptno having myavg < 2000;
+--------+-------------+
| deptno | myavg       |
+--------+-------------+
|     30 | 1566.666667 |
+--------+-------------+
1 row in set (0.00 sec)

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

mysql> select deptno,count(*),avg(sal) from emp group by deptno;
+--------+----------+-------------+
| deptno | count(*) | avg(sal)    |
+--------+----------+-------------+
|     10 |        3 | 2916.666667 |
|     20 |        5 | 2175.000000 |
|     30 |        6 | 1566.666667 |
+--------+----------+-------------+
3 rows in set (0.00 sec)

1.2 多表查询

实际开发中往往数据来自不同的表，所以需要多表查询。本节我们用一个简单的公司管理系统，有三张表EMP,DEPT,SALGRADE来演示如何进行多表查询。

案例：

• 显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表，因此要联合查询

其实我们只要emp表中的deptno = dept表中的deptno字段的记录 

select ename,sal,dname from emp,dept where emp.deptno = dept.deptno;

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

select dname,ename,sal from emp,dept where emp.deptno = dept.deptno and emp.deptno = 10;

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

select ename,sal,grade from emp,salgrade where sal>=losal and sal<=hisal;
select ename,sal,grade from emp,salgrade where sal between losal and hisal;

1.3 自连接

自连接是指在同一张表连接查询。

案例：

• 显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号--empno）

使用的子查询： 

select empno,ename from emp where empno = (select mgr from emp where ename = 'FORD');

使用多表查询（自查询） ：

1.4 子查询

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

1.4.1 单行子查询

返回一行记录的子查询

• 显示SMITH同一部门的员工 

select * from emp where deptno = (select deptno from emp where ename = 'SMITH');

1.4.2 多行子查询

返回多行记录的子查询。

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

select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno = 10) and deptno != 10;

select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno = 10) and deptno <> 10;

 不等于可以使用 != 也可以使用 <> 

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

查出30号部门的最高工资，大于这个最高工资的员工姓名、工资和部门号。

select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno = 30);

select ename,sal,deptno from emp where sal > all(select sal from emp where deptno = 30);

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

 查出30号部门的最低工资，大于这个最低工资的员工姓名、工资和部门号。

select ename,sal,deptno from emp where sal > (select min(sal) from emp where deptno = 30);

select ename,sal,deptno from emp where sal > any(select sal from emp where deptno = 30);

1.4.3 多列子查询

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

案例：

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

select ename from emp where (deptno,job) = (select deptno,job from emp where ename = 'SMITH') and ename <> 'SMITH';

1.4.4 在from子句中使用子查询

子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。

案例：

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

select ename,deptno,sal,format(asal,2) from emp,(select avg(sal) asal,deptno dt from emp group by deptno) tmp where emp.sal > tmp.asal and emp.deptno = tmp.dt;

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

select ename,sal,deptno,msal from emp,(select deptno dt,max(sal) msal from emp group by deptno) tmp where emp.deptno = tmp.dt and emp.sal = tmp.msal;

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

方法1：使用多表 

select dept.dname, dept.deptno, dept.loc,count(*) from emp, dept where emp.deptno=dept.deptno group by dept.deptno,dept.dname,dept.loc;

方法2：使用子查询 

select dept.deptno,dname,mycnt,loc from dept,(select count(*) mycnt,deptno from emp group by deptno) tmp where dept.depptno = tmp.deptno;

1.4.5 合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。

1.4.5.1 union

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

案例：

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

# 会去重
select * from emp where sal > 2500 union select * from emp where job = 'MANAGER';

1.4.5.3 union all

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

案例：

• 将工资大于25000或职位是MANAGER的人找出来 

# 不去重
select * from emp where sal > 25000 union all select * from emp where job = 'MANAGER';