MySQL复合查询

文章目录

练习

  • 查询工资大于500或岗位为manager的雇员,同时还要满足姓名的首字母是大写的J
1
select * from emp where (sal>2500 or job='MANAGER') and substring(ename,1,1)='J';

或者

1
select * from emp where (sal>2500 or job='MANAGER') and ename like 'J%';
  • 按照部门号升序而雇员的工资降序排序
1
select * from emp order by deptno asc ,sal desc;
  • 使用年薪进行降序排列,先把每个人的年薪算出来
1
select sal*12+ifnull(comm,0) 年薪 from emp order by 年薪 desc;

在这里插入图片描述

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

先获得最高的工资是多少,再在where里面使用这个数据,我们就可以使用符合查询

1
select ename,job,max(sal*12+ifnull(comm,0)) from emp;

在where里面套select,里面的先查询,进行子查询

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

在这里插入图片描述

  • 显示工资高于平均工资的员工信息
1
select * from emp where sal>(select avg(sal) from emp);
  • 显示每个部门的平均工资和最高工资
1
select max(sal) 最高工资,avg(sal) 平均工资 from emp group by deptno;
  • 显示平均工资低于2000的部门号和它的平均工资

having是在group by 之后进行操作

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

在这里插入图片描述

  • 显示每种岗位的雇员总数,平均工资
1
select job,count(ename),avg(sal) from emp group by job;

在这里插入图片描述

多表查询

多个组合成笛卡尔集
因为我们的数据都会被拆成不能再拆
重命名可以再from里面进行,select里面也可以,但是不能再where里面进行重命名

从第一张表中的第一条记录和另一张表中的所有记录进行组合,就是把数据一条一条的取出来,

所以,在我看来,所有的多表查询,都是单表查询
计算多张表里面的数据

1
select count(*) from emp one,emp two,emp three;
  • 显示雇员名,雇员工资以及所在的部门名,通过雇员所在的部门id来查找对应的值,使用外键
1
select ename,sal,dname,dept.deptno from emp,dept where emp.deptno=dept.deptno;

我们认为所有的select查询出来的:”记录“,都可以把它看作”表“

  • 显示部门号为10的部门名,员工名和工资
1
select ename,dname,sal,emp.deptno from emp,dept where dept.deptno=emp.deptno and emp.deptno=10;
  • 显示各个员工的姓名,工资,及工资级别

因为前一个表会和后一个表的每一项都进行一个组合

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

自链接

自链接是值在同一张表连接查询

  • 显示ford的上级领导的编号和姓名

可以先把两张表进行组合

1
select mgr.ename,mgr.empno from emp work,emp mgr where work.ename='FORD' and work.mgr=mgr.empno;

在这里插入图片描述

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

在这里插入图片描述

子查询

也叫做嵌套查询
在其他sql当中嵌套select的过程

单行子查询

查询的结果只有一列,一行

  • 显示和SMITH同一部分的员工
1
select ename from emp where (select deptno from emp where ename='SMITH')=deptno;

在这里插入图片描述

多行子查询

查询的结果有多行的时候,可以用一些关键字来进行过滤

  • in 在一个范围内查找,不存在(属于一员)

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

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

在这里插入图片描述

  • all关键字(比所有的都怎么样)

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

1
select ename,sal,deptno from emp where sal> all(select distinct sal from emp where deptno=30);
  • any(比任意一个都要高)

显示工资比部门30的任意员工的工资都高的员工姓名,工资和部门号

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

多列子查询

  • 查找和smith的部门和岗位完全相同的所有雇员,不含smith本人
1
select * from emp where (deptno,job)=(select deptno,job from emp where ename='SMITH') and ename!='SMITH' ;
  • 显示每个高于自己部门平均工资的员工的姓名,部门,工资,平均工资
1
select * from emp one,(select deptno,avg(sal) 平均工资 from emp group by deptno) avg_tb where (avg_tb.deptno=one.deptno) and sal>平均工资;
  • 查找每个部门工资最高的人的姓名,工资,部门,最高工资
1
select ename,sal,emp.deptno,maxsal from emp,(select ename,sal,deptno,max(sal) maxsal from emp group by deptno) maxtb where emp.deptno=maxtb.deptno and emp.sal=maxtb.maxsal;
  • 显示每个部门的信息(部门名,部门编号,地址)
1
select getno.deptno,dname,loc,num from (select deptno,count(*) num from emp group by deptno) getno,(select * from dept) deptdetail where getno.deptno=deptdetail.deptno;

合并查询

  • union,基本不会用

将最终查询的结果进行结果,会自动去掉结果中的重复行

1
select ename from emp where job='MANAGER' union select  sal>2500;
1
2
3
select * from emp where job='MANAGER' 
union
select * from emp where sal>2500;
  • union all:使用完,不会去掉重复的数据

MySQL复合查询
http://example.com/2022/10/12/MySQL复合查询/
作者
Zevin
发布于
2022年10月12日
许可协议