【MySQL】复合查询

技术文档

【MySQL】复合查询

一、基本查询回顾
- 1.1 查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J
- 1.2 按照部门号升序而雇员的工资降序排序
- 1.3 使用年薪进行降序排序
- 1.4 显示工资最高的员工的名字和工作岗位
- 1.5 显示工资高于平均工资的员工信息
- 1.6 显示每个部门的平均工资和最高工资
- 1.7 显示平均工资低于2000的部门号和它的平均工资
- 1.8 显示每种岗位的雇员总数，平均工资
二、多表查询
- 2.1 显示雇员名、雇员工资以及所在部门的名字
- 2.2 显示部门号为10的部门名，员工名和工资
- 2.3 显示各个员工的姓名，工资，及工资级别
三、自连接
- 2.1 显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号--empno）
四、子查询
- 4.1 在where子句中使用的子查询
- - 4.1.1 单行子查询
  - - 4.1.1.1 显示SMITH同一部门的员工
  - 4.1.2 多行子查询
  - - 4.1.2.1 查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的
    - 4.1.2.2 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
    - 4.1.2.3 显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门的员工）
  - 4.1.3 多列子查询
  - - 4.1.3.1 查询和SMITH的部门和岗位完全相同的所有雇员，不含SMITH本人
- 4.2 在from子句中使用的子查询
- - 4.2.1 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
  - 4.2.2 查找每个部门工资最高的人的姓名、工资、部门、最高工资
  - 4.2.3 显示每个部门的信息（部门名，编号，地址）和人员数量
- 4.3 合并查询
- - 4.3.1 将工资大于2500或职位是MANAGER的人找出来
结尾

一、基本查询回顾

准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）

EMP员工表
DEPT部门表
SALGRADE工资等级表

DROP database IF EXISTS `scott`;CREATE database IF NOT EXISTS `scott` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;USE `scott`;DROP TABLE IF EXISTS `dept`;CREATE TABLE `dept` ( `deptno` int(2) unsigned zerofill NOT NULL COMMENT \'部门编号\', `dname` varchar(14) DEFAULT NULL COMMENT \'部门名称\', `loc` varchar(13) DEFAULT NULL COMMENT \'部门所在地点\');DROP TABLE IF EXISTS `emp`;CREATE TABLE `emp` ( `empno` int(6) unsigned zerofill NOT NULL COMMENT \'雇员编号\', `ename` varchar(10) DEFAULT NULL COMMENT \'雇员姓名\', `job` varchar(9) DEFAULT NULL COMMENT \'雇员职位\', `mgr` int(4) unsigned zerofill DEFAULT NULL COMMENT \'雇员领导编号\', `hiredate` datetime DEFAULT NULL COMMENT \'雇佣时间\', `sal` decimal(7,2) DEFAULT NULL COMMENT \'工资月薪\', `comm` decimal(7,2) DEFAULT NULL COMMENT \'奖金\', `deptno` int(2) unsigned zerofill DEFAULT NULL COMMENT \'部门编号\');DROP TABLE IF EXISTS `salgrade`;CREATE TABLE `salgrade` ( `grade` int(11) DEFAULT NULL COMMENT \'等级\', `losal` int(11) DEFAULT NULL COMMENT \'此等级最低工资\', `hisal` int(11) DEFAULT NULL COMMENT \'此等级最高工资\');insert into dept (deptno, dname, loc)values (10, \'ACCOUNTING\', \'NEW YORK\');insert into dept (deptno, dname, loc)values (20, \'RESEARCH\', \'DALLAS\');insert into dept (deptno, dname, loc)values (30, \'SALES\', \'CHICAGO\');insert into dept (deptno, dname, loc)values (40, \'OPERATIONS\', \'BOSTON\');insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7369, \'SMITH\', \'CLERK\', 7902, \'1980-12-17\', 800, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7499, \'ALLEN\', \'SALESMAN\', 7698, \'1981-02-20\', 1600, 300, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7521, \'WARD\', \'SALESMAN\', 7698, \'1981-02-22\', 1250, 500, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7566, \'JONES\', \'MANAGER\', 7839, \'1981-04-02\', 2975, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7654, \'MARTIN\', \'SALESMAN\', 7698, \'1981-09-28\', 1250, 1400, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7698, \'BLAKE\', \'MANAGER\', 7839, \'1981-05-01\', 2850, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7782, \'CLARK\', \'MANAGER\', 7839, \'1981-06-09\', 2450, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7788, \'SCOTT\', \'ANALYST\', 7566, \'1987-04-19\', 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7839, \'KING\', \'PRESIDENT\', null, \'1981-11-17\', 5000, null, 10);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7844, \'TURNER\', \'SALESMAN\', 7698,\'1981-09-08\', 1500, 0, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7876, \'ADAMS\', \'CLERK\', 7788, \'1987-05-23\', 1100, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7900, \'JAMES\', \'CLERK\', 7698, \'1981-12-03\', 950, null, 30);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7902, \'FORD\', \'ANALYST\', 7566, \'1981-12-03\', 3000, null, 20);insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)values (7934, \'MILLER\', \'CLERK\', 7782, \'1982-01-23\', 1300, null, 10);insert into salgrade (grade, losal, hisal) values (1, 700, 1200);insert into salgrade (grade, losal, hisal) values (2, 1201, 1400);insert into salgrade (grade, losal, hisal) values (3, 1401, 2000);insert into salgrade (grade, losal, hisal) values (4, 2001, 3000);insert into salgrade (grade, losal, hisal) values (5, 3001, 9999);

【MySQL】复合查询

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

select * from emp where (sal>500 or job=\'MANAGER\') and ename like \'J%\';select * from emp where (sal>500 or job=\'MANAGER\') and substring(ename,1,1)=\'J\';

【MySQL】复合查询

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

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

【MySQL】复合查询

1.3 使用年薪进行降序排序

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

【MySQL】复合查询

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

select ename,job from emp where sal = (select max(sal) from emp);

【MySQL】复合查询

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

select * from emp where sal > (select avg(sal) from emp);

【MySQL】复合查询

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

select deptno,avg(sal) 平均 , max(sal) 最高 from emp group by deptno;

【MySQL】复合查询

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

select deptno,avg(sal) 平均 from emp group by deptno having 平均<2000;

【MySQL】复合查询

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

select job,avg(sal) 平均 from emp group by job;

【MySQL】复合查询

二、多表查询

实际开发中往往数据来自不同的表，所以需要多表查询。这里我用EMP，DEPT，SALGRADE这三张表来演示如何进行多表查询。

从第一张表中取第一条数据，与第二张表中所有的数据进行组合，从第一张表中取第二条数据，与第二张表中所有的数据进行组合，重复上面的步骤，然后不加过滤条件，得到的结果就是笛卡尔积。

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

因为雇员名、雇员工资，部门的名字这些数据分别来自EMP和DEPT表，因此要联合查询。

下面我将EMP和DEPT表进行笛卡尔积，发现形成了一个更大的表，表中有很多同一个人的数据，并且一行数据中还有部门号不同的数据，显然这就是无用的数据，需要将这些数据筛除掉。
【MySQL】复合查询

select ename,job,emp.deptno from emp,dept where emp.deptno=dept.deptno;

【MySQL】复合查询

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

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

【MySQL】复合查询

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

因为员工的姓名、工资相关数据来自EMP表，工资级别相关数据来自于SALGRADE表，所以这里需要对两张表进行笛卡尔积。

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

【MySQL】复合查询

三、自连接

自连接是指在同一张表连接查询，进行自连接时，由于使用的是同一个表名，直接使用会报错，所以需要给表取别名。

【MySQL】复合查询

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

自连接

select t2.empno,t2.ename from emp t1,emp t2 where t1.mgr=t2.empno and t1.ename=\'FORD\';

【MySQL】复合查询

子查询

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

【MySQL】复合查询

四、子查询

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

in关键字：用于判断某个值是否存在于一个指定的集合中，这个集合可以是一个具体的值列表，也可以是一个子查询的结果集。
all关键字：会将一个值与子查询返回的所有值进行比较，只有当该值满足与子查询返回的所有值的比较条件时，才会返回 TRUE。
any关键字：会将一个值与子查询返回的任意一个值进行比较，只要该值满足与子查询返回的任意一个值的比较条件，就会返回 TRUE。

4.1 在where子句中使用的子查询

4.1.1 单行子查询

单行子查询就是返回一行数据的子查询。

4.1.1.1 显示SMITH同一部门的员工

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

【MySQL】复合查询

4.1.2 多行子查询

多行子查询查询就是返回多行数据的子查询。

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

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

【MySQL】复合查询

4.1.2.2 显示工资比部门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);

【MySQL】复合查询

4.1.2.3 显示工资比部门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);

【MySQL】复合查询

4.1.3 多列子查询

多列子查询就是指查询返回多个列数据的子查询语句

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

select * from emp where (deptno,job)=(select deptno,job from emp where ename=\'SMITH\') and ename\'SMITH\';select * from emp where (deptno,job)in(select deptno,job from emp where ename=\'SMITH\') and ename\'SMITH\';

【MySQL】复合查询

4.2 在from子句中使用的子查询

子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。不要单纯的认为只有内存/磁盘中存储真实存在的表结构才是表，我们筛选出来的也是表。

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

select ename,t1.deptno,sal,myavg from emp t1,(select deptno,avg(sal) myavg from emp group by deptno) t2 where t1.deptno=t2.deptno and sal > myavg;

【MySQL】复合查询

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

select ename,sal,t1.deptno,mymax from emp t1,(select deptno,max(sal) mymax from emp group by deptno) t2 where t1.deptno=t2.deptno and sal = mymax;

【MySQL】复合查询

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

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

【MySQL】复合查询

4.3 合并查询

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

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

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

select * from emp where sal>2500 union select * from emp where job=\'MANAGER\';select * from emp where sal>2500 union all select * from emp where job=\'MANAGER\';

【MySQL】复合查询

结尾

如果有什么建议和疑问，或是有什么错误，大家可以在评论区中提出。
希望大家以后也能和我一起进步！！🌹🌹
如果这篇文章对你有用的话，希望大家给一个三连支持一下！！🌹🌹

【MySQL】复合查询

【MySQL】复合查询

目录

一、基本查询回顾

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

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

1.3 使用年薪进行降序排序

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

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

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

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

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

二、多表查询

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

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

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

三、自连接

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

四、子查询

4.1 在where子句中使用的子查询

4.1.1 单行子查询

4.1.1.1 显示SMITH同一部门的员工

4.1.2 多行子查询

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

4.1.2.2 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

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

4.1.3 多列子查询

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

4.2 在from子句中使用的子查询

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

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

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

4.3 合并查询

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

结尾

公告

DeepSeek全套部署资料免费下载

免费可商用字体批量下载

标签

【MySQL】复合查询

目录

一、基本查询回顾

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

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

1.3 使用年薪进行降序排序

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

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

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

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

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

二、多表查询

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

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

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

三、自连接

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

四、子查询

4.1 在where子句中使用的子查询

4.1.1 单行子查询

4.1.1.1 显示SMITH同一部门的员工

4.1.2 多行子查询

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

4.1.2.2 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

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

4.1.3 多列子查询

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

4.2 在from子句中使用的子查询

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

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

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

4.3 合并查询

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

结尾

相关问题

公告

DeepSeek全套部署资料免费下载

免费可商用字体批量下载

标签