当前位置：首页 > news >正文

MySQL复合查询

news 2025/8/24 11:26:54

文章目录

基本查询回顾
多表查询
自连接
子查询
- 单行子查询
- 多行子查询
- 多列子查询
- 在from子句中使用子查询
- 合并查询
- - union
  - union all

基本查询回顾

查询的员工部门表结构：

mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| dept            |
| emp             |
| salgrade        |
+-----------------+
3 rows in set (0.00 sec)mysql> desc dept;
+--------+--------------------------+------+-----+---------+-------+
| Field  | Type                     | Null | Key | Default | Extra |
+--------+--------------------------+------+-----+---------+-------+
| deptno | int(2) unsigned zerofill | NO   |     | NULL    |       |
| dname  | varchar(14)              | YES  |     | NULL    |       |
| loc    | varchar(13)              | YES  |     | NULL    |       |
+--------+--------------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)mysql> desc emp;
+----------+--------------------------+------+-----+---------+-------+
| Field    | Type                     | Null | Key | Default | Extra |
+----------+--------------------------+------+-----+---------+-------+
| empno    | int(6) unsigned zerofill | NO   |     | NULL    |       |
| ename    | varchar(10)              | YES  |     | NULL    |       |
| job      | varchar(9)               | YES  |     | NULL    |       |
| mgr      | int(4) unsigned zerofill | YES  |     | NULL    |       |
| hiredate | datetime                 | YES  |     | NULL    |       |
| sal      | decimal(7,2)             | YES  |     | NULL    |       |
| comm     | decimal(7,2)             | YES  |     | NULL    |       |
| deptno   | int(2) unsigned zerofill | YES  |     | NULL    |       |
+----------+--------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)mysql> desc salgrade;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| grade | int(11) | YES  |     | NULL    |       |
| losal | int(11) | YES  |     | NULL    |       |
| hisal | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

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

mysql> select job, ename, sal from emp where (sal>500 or job='MANAGER') and ename like 'J%';
+---------+-------+---------+
| job     | ename | sal     |
+---------+-------+---------+
| MANAGER | JONES | 2975.00 |
| CLERK   | JAMES |  950.00 |
+---------+-------+---------+
2 rows in set (0.00 sec)

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

mysql> select ename, sal, deptno from emp order by deptno asc, sal desc;
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| KING   | 5000.00 |     10 |
| CLARK  | 2450.00 |     10 |
| MILLER | 1300.00 |     10 |
| SCOTT  | 3000.00 |     20 |
| FORD   | 3000.00 |     20 |
| JONES  | 2975.00 |     20 |
| ADAMS  | 1100.00 |     20 |
| SMITH  |  800.00 |     20 |
| BLAKE  | 2850.00 |     30 |
| ALLEN  | 1600.00 |     30 |
| TURNER | 1500.00 |     30 |
| WARD   | 1250.00 |     30 |
| MARTIN | 1250.00 |     30 |
| JAMES  |  950.00 |     30 |
+--------+---------+--------+
14 rows in set (0.00 sec)

使用年薪进行降序排序：

mysql> select ename, sal*12+ifnull(comm,0) year_sal from emp order by year_sal desc;
+--------+----------+
| ename  | year_sal |
+--------+----------+
| KING   | 60000.00 |
| SCOTT  | 36000.00 |
| FORD   | 36000.00 |
| JONES  | 35700.00 |
| BLAKE  | 34200.00 |
| CLARK  | 29400.00 |
| ALLEN  | 19500.00 |
| TURNER | 18000.00 |
| MARTIN | 16400.00 |
| MILLER | 15600.00 |
| WARD   | 15500.00 |
| ADAMS  | 13200.00 |
| JAMES  | 11400.00 |
| SMITH  |  9600.00 |
+--------+----------+
14 rows in set (0.00 sec)

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

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

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

mysql> select ename, sal from emp where sal>(select avg(sal) from emp);
+-------+---------+
| ename | sal     |
+-------+---------+
| JONES | 2975.00 |
| BLAKE | 2850.00 |
| CLARK | 2450.00 |
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
6 rows in set (0.00 sec)

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

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

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

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

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

mysql> select job, count(*) '雇员总数', avg(sal) '平均工资' from emp group by job;
+-----------+--------------+--------------+
| job       | 雇员总数     | 平均工资     |
+-----------+--------------+--------------+
| ANALYST   |            2 |  3000.000000 |
| CLERK     |            4 |  1037.500000 |
| MANAGER   |            3 |  2758.333333 |
| PRESIDENT |            1 |  5000.000000 |
| SALESMAN  |            4 |  1400.000000 |
+-----------+--------------+--------------+
5 rows in set (0.00 sec)

多表查询

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

案例：

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

因为上面的数据来自EMP和DEPT表，因此要联合查询

在这里插入图片描述

mysql> select * from emp, dept limit 12;
+--------+-------+----------+------+---------------------+---------+--------+--------+--------+------------+----------+
| empno  | ename | job      | mgr  | hiredate            | sal     | comm   | deptno | deptno | dname      | loc      |
+--------+-------+----------+------+---------------------+---------+--------+--------+--------+------------+----------+
| 007369 | SMITH | CLERK    | 7902 | 1980-12-17 00:00:00 |  800.00 |   NULL |     20 |     10 | ACCOUNTING | NEW YORK |
| 007369 | SMITH | CLERK    | 7902 | 1980-12-17 00:00:00 |  800.00 |   NULL |     20 |     20 | RESEARCH   | DALLAS   |
| 007369 | SMITH | CLERK    | 7902 | 1980-12-17 00:00:00 |  800.00 |   NULL |     20 |     30 | SALES      | CHICAGO  |
| 007369 | SMITH | CLERK    | 7902 | 1980-12-17 00:00:00 |  800.00 |   NULL |     20 |     40 | OPERATIONS | BOSTON   |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 |     10 | ACCOUNTING | NEW YORK |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 |     20 | RESEARCH   | DALLAS   |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 |     30 | SALES      | CHICAGO  |
| 007499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 | 300.00 |     30 |     40 | OPERATIONS | BOSTON   |
| 007521 | WARD  | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 |     30 |     10 | ACCOUNTING | NEW YORK |
| 007521 | WARD  | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 |     30 |     20 | RESEARCH   | DALLAS   |
| 007521 | WARD  | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 |     30 |     30 | SALES      | CHICAGO  |
| 007521 | WARD  | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 | 500.00 |     30 |     40 | OPERATIONS | BOSTON   |
+--------+-------+----------+------+---------------------+---------+--------+--------+--------+------------+----------+
12 rows in set (0.00 sec)

此时就需要对表的数据进行筛选，只要emp表中的deptno = dept表中的deptno字段的记录：

mysql> select ename, sal, dname from emp, dept where emp.deptno=dept.deptno;
+--------+---------+------------+
| ename  | sal     | dname      |
+--------+---------+------------+
| SMITH  |  800.00 | RESEARCH   |
| ALLEN  | 1600.00 | SALES      |
| WARD   | 1250.00 | SALES      |
| JONES  | 2975.00 | RESEARCH   |
| MARTIN | 1250.00 | SALES      |
| BLAKE  | 2850.00 | SALES      |
| CLARK  | 2450.00 | ACCOUNTING |
| SCOTT  | 3000.00 | RESEARCH   |
| KING   | 5000.00 | ACCOUNTING |
| TURNER | 1500.00 | SALES      |
| ADAMS  | 1100.00 | RESEARCH   |
| JAMES  |  950.00 | SALES      |
| FORD   | 3000.00 | RESEARCH   |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
14 rows in set (0.00 sec)

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

mysql> select emp.deptno, dname, ename, sal from emp, dept where emp.deptno=dept.deptno and emp.deptno=10;
+--------+------------+--------+---------+
| deptno | dname      | ename  | sal     |
+--------+------------+--------+---------+
|     10 | ACCOUNTING | CLARK  | 2450.00 |
|     10 | ACCOUNTING | KING   | 5000.00 |
|     10 | ACCOUNTING | MILLER | 1300.00 |
+--------+------------+--------+---------+
3 rows in set (0.00 sec)

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

mysql> select ename, sal, grade from emp,salgrade where sal between losal and hisal;
+--------+---------+-------+
| ename  | sal     | grade |
+--------+---------+-------+
| SMITH  |  800.00 |     1 |
| ALLEN  | 1600.00 |     3 |
| WARD   | 1250.00 |     2 |
| JONES  | 2975.00 |     4 |
| MARTIN | 1250.00 |     2 |
| BLAKE  | 2850.00 |     4 |
| CLARK  | 2450.00 |     4 |
| SCOTT  | 3000.00 |     4 |
| KING   | 5000.00 |     5 |
| TURNER | 1500.00 |     3 |
| ADAMS  | 1100.00 |     1 |
| JAMES  |  950.00 |     1 |
| FORD   | 3000.00 |     4 |
| MILLER | 1300.00 |     2 |
+--------+---------+-------+
14 rows in set (0.00 sec)

自连接

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

案例：

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

子查询

mysql> select empno, ename from emp where empno=(select mgr from emp where ename='FORD');
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.00 sec)

使用多表查询（自查询）

mysql> select leader.empno, leader.ename from emp leader,emp worker where worker.mgr=leader.empno and worker.ename='FORD';
+--------+-------+
| empno  | ename |
+--------+-------+
| 007566 | JONES |
+--------+-------+
1 row in set (0.00 sec)

子查询

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

单行子查询

返回一行记录的子查询

显示SMITH同一部门的员工：

mysql> select ename, job from emp where job=(select job from emp where ename='SMITH');
+--------+-------+
| ename  | job   |
+--------+-------+
| SMITH  | CLERK |
| ADAMS  | CLERK |
| JAMES  | CLERK |
| MILLER | CLERK |
+--------+-------+
4 rows in set (0.00 sec)

多行子查询

返回多行记录的子查询

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

mysql> select ename, job, sal, deptno from emp where job in(select job from emp where deptno=10);
+--------+-----------+---------+--------+
| ename  | job       | sal     | deptno |
+--------+-----------+---------+--------+
| JONES  | MANAGER   | 2975.00 |     20 |
| BLAKE  | MANAGER   | 2850.00 |     30 |
| CLARK  | MANAGER   | 2450.00 |     10 |
| KING   | PRESIDENT | 5000.00 |     10 |
| SMITH  | CLERK     |  800.00 |     20 |
| ADAMS  | CLERK     | 1100.00 |     20 |
| JAMES  | CLERK     |  950.00 |     30 |
| MILLER | CLERK     | 1300.00 |     10 |
+--------+-----------+---------+--------+
8 rows in set (0.00 sec)

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

mysql> select ename, sal, deptno from emp where sal > all(select sal from emp where deptno=30);
+-------+---------+--------+
| ename | sal     | deptno |
+-------+---------+--------+
| JONES | 2975.00 |     20 |
| SCOTT | 3000.00 |     20 |
| KING  | 5000.00 |     10 |
| FORD  | 3000.00 |     20 |
+-------+---------+--------+
4 rows in set (0.00 sec)

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

mysql> select ename, sal, deptno from emp where sal > any(select sal from emp where deptno=30);
+--------+---------+--------+
| ename  | sal     | deptno |
+--------+---------+--------+
| ALLEN  | 1600.00 |     30 |
| WARD   | 1250.00 |     30 |
| JONES  | 2975.00 |     20 |
| MARTIN | 1250.00 |     30 |
| BLAKE  | 2850.00 |     30 |
| CLARK  | 2450.00 |     10 |
| SCOTT  | 3000.00 |     20 |
| KING   | 5000.00 |     10 |
| TURNER | 1500.00 |     30 |
| ADAMS  | 1100.00 |     20 |
| FORD   | 3000.00 |     20 |
| MILLER | 1300.00 |     10 |
+--------+---------+--------+
12 rows in set (0.01 sec)

多列子查询

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

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

mysql> select ename from emp where (deptno, job) = (select deptno, job from emp where ename='SMITH') and ename!='SMITH';
+-------+
| ename |
+-------+
| ADAMS |
+-------+
1 row in set (0.00 sec)

在from子句中使用子查询

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

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

mysql> select ename, emp.deptno, sal, avg_tb.avg_sal from emp, > (select deptno, avg(sal) avg_sal from emp group by deptno) avg_tb> where emp.deptno=avg_tb.deptno and emp.sal>avg_tb.avg_sal;
+-------+--------+---------+-------------+
| ename | deptno | sal     | avg_sal     |
+-------+--------+---------+-------------+
| ALLEN |     30 | 1600.00 | 1566.666667 |
| JONES |     20 | 2975.00 | 2175.000000 |
| BLAKE |     30 | 2850.00 | 1566.666667 |
| SCOTT |     20 | 3000.00 | 2175.000000 |
| KING  |     10 | 5000.00 | 2916.666667 |
| FORD  |     20 | 3000.00 | 2175.000000 |
+-------+--------+---------+-------------+
6 rows in set (0.00 sec)

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

mysql> select ename, sal, emp.deptno, max_tb.max_sal from emp, >(select deptno, max(sal) max_sal from emp group by deptno) max_tb > where emp.deptno=max_tb.deptno and emp.sal=max_tb.max_sal;
+-------+---------+--------+---------+
| ename | sal     | deptno | max_sal |
+-------+---------+--------+---------+
| BLAKE | 2850.00 |     30 | 2850.00 |
| SCOTT | 3000.00 |     20 | 3000.00 |
| KING  | 5000.00 |     10 | 5000.00 |
| FORD  | 3000.00 |     20 | 3000.00 |
+-------+---------+--------+---------+
4 rows in set (0.00 sec)

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

方法一：使用多表查询

mysql> select dept.dname, emp.deptno, dept.loc, count(*) cnt from emp, dept > where emp.deptno=dept.deptno group by dept.deptno, dept.loc, dept.dname;
+------------+--------+----------+-----+
| dname      | deptno | loc      | cnt |
+------------+--------+----------+-----+
| ACCOUNTING |     10 | NEW YORK |   3 |
| RESEARCH   |     20 | DALLAS   |   5 |
| SALES      |     30 | CHICAGO  |   6 |
+------------+--------+----------+-----+
3 rows in set (0.00 sec)

方法二：使用子查询

mysql> select dname, dept.deptno, loc, cnt_tb.cnt from dept, > (select emp.deptno, count(*) cnt from emp group by deptno) cnt_tb > where deept.deptno=cnt_tb.deptno;
+------------+--------+----------+-----+
| dname      | deptno | loc      | cnt |
+------------+--------+----------+-----+
| ACCOUNTING |     10 | NEW YORK |   3 |
| RESEARCH   |     20 | DALLAS   |   5 |
| SALES      |     30 | CHICAGO  |   6 |
+------------+--------+----------+-----+
3 rows in set (0.00 sec)

合并查询

union

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

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

mysql> select ename, job, sal from emp where sal>2500 union select ename, job, sal from emp where job='MANAGER';
+-------+-----------+---------+
| ename | job       | sal     |
+-------+-----------+---------+
| JONES | MANAGER   | 2975.00 |
| BLAKE | MANAGER   | 2850.00 |
| SCOTT | ANALYST   | 3000.00 |
| KING  | PRESIDENT | 5000.00 |
| FORD  | ANALYST   | 3000.00 |
| CLARK | MANAGER   | 2450.00 |
+-------+-----------+---------+
6 rows in set (0.00 sec)

union all

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

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

mysql> select ename, job, sal from emp where sal>2500 union all select ename, job, sal from emp where job='MANAGER';
+-------+-----------+---------+
| ename | job       | sal     |
+-------+-----------+---------+
| JONES | MANAGER   | 2975.00 |
| BLAKE | MANAGER   | 2850.00 |
| SCOTT | ANALYST   | 3000.00 |
| KING  | PRESIDENT | 5000.00 |
| FORD  | ANALYST   | 3000.00 |
| JONES | MANAGER   | 2975.00 |
| BLAKE | MANAGER   | 2850.00 |
| CLARK | MANAGER   | 2450.00 |
+-------+-----------+---------+
8 rows in set (0.00 sec)