【MySQL】8.复合查询

一.基本查询回顾(新增子查询)

//1.查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们姓名首字母为‘J’
select * from emp where (sal>500 or job='MANAGER') and left(ename, 1)='J';

//2.按照部门号升序而雇员工资降序排序
select * from emp order by deptno asc, sal desc;

//3.使用年薪进行降序排序
select ename, sal, sal*12+ifnull(comm, 0) as 年薪 from emp order by 年薪 desc;

//4.显示工资最高的员工的名字和工作岗位
select ename, job from emp where sal=(select max(sal) from emp); //子查询

//5.显示工资高于平均工资的员工信息
select * from emp where sal > (select avg(sal) from emp); //子查询

//6.显示每个部门的平均工资和最高工资
select deptno, format(avg(sal), 2) as 平均工资, format(max(sal), 2) as 最高工资 from emp group by deptno;