增删改查 据说查询是最困难的……各种组合查询 联表查询
#0. 查询最高工资及其对应员工姓名select ename, sal from empwhere sal=(select max(sal) from emp);#如果有多个员工都是最高工资下面的方式将失效select ename, sal from emp order by sal desc limit 0, 1;#补充1：能否不使用聚合函数查出最高工资及其对应员工姓名select ename, sal from empwhere sal=(select sal from emp order by sal desc limit 0,1);#补充2：既不用排序也不用聚合函数查出最高工资及其对应员工姓名select ename, sal from empwhere sal not in (select distinct t1.sal from emp as t1inner join emp as t2 on t1.sal(select avg(sal) from emp);#5. 查询薪水超过其所在部门平均薪水的员工的姓名、部门名称和工资#where写法select ename, dname, t3.sal from(select eno, t1.dno, sal from emp as t1,(select dno, avg(sal) as avgsal from emp group by dno) as t2where t1.dno=t2.dno and sal>avgsal) as t3, emp as t4, dept as t5 where t3.eno=t4.eno and t5.dno=t3.dno;#inner join写法select ename, dname, t3.sal from(select eno, t1.dno, sal from emp as t1 inner join(select dno, avg(sal) as avgsal from emp group by dno) as t2on t1.dno=t2.dno and sal>avgsal) as t3 inner join emp as t4 on t3.eno=t4.eno inner join dept as t5 on t5.dno=t3.dno;#6. 查询部门中薪水最高的人姓名、工资和所在部门名称select ename, dname, t3.sal from(select eno, t1.dno, sal from emp as t1 inner join(select dno, max(sal) as maxsal from emp group by dno) as t2on t1.dno=t2.dno and sal=maxsal) as t3 inner join emp as t4 on t3.eno=t4.eno inner join dept as t5 on t5.dno=t3.dno;#7. 哪些人是主管select * from emp where eno in (select distinct mgr from emp);select * from emp where eno=any(select distinct mgr from emp);#补充：哪些人不是主管select * from emp where eno not in (select distinct mgr from emp where mgr is not null);#8. 求平均薪水最高的部门的名称和平均工资select dname as 部门名称, avgsal as 平均工资 from(select dno, avgsalfrom (select dno, avg(sal) as avgsal from empgroup by dno) t1 where avgsal=(select max(avgsal) from (select dno, avg(sal) as avgsal from emp group by dno) as t2)) as t3inner join dept as t4 on t3.dno=t4.dno;#9. 求薪水最高的前3名雇员select * from emp order by sal desc limit 0,3;#10.求薪水排在第4-6名雇员select * from emp order by sal desc limit 3,3;