mysql练习第一部分

楼主最近一段时间刚学完数据库，下面的是针对数据库中的连表查询，子查询等综合使用，适合新手，后续还会更新第二部分。如果需要Navicat，或者sqlyog，等数据库管理工具，可以在下方留言，我会上传破解版资源，如下练习欢迎指正。

使用的资源数据在我发布的资源中 mysql练习用资源库

下面是基本查询中比较值得做的练习，都是楼主自己写的

1. 列出至少有一个员工的部门名。

select d.dname,d.deptno from dept d left join (select e.deptno,count() from emp e group by e.deptno having count() >= 1) new on d.deptno in (new.deptno);

2.列出月薪比“SMITH”多的所有员工。

select * from emp e where e.sal > (select e1.sal from emp e1 where e1.ename = ‘smith’);

3. 列出所有员工的姓名及其直接上级的姓名。

select e.ename 员工名,m.ename 上级名 from emp e left join emp m on e.mgr = m.empno;

4. 列出受雇日期早于其直接上级的所有员工的编号、姓名、部门名称。

select e.empno 编号,e.ename 姓名,d.dname 部门名,e.hiredate,m.ename,m.hiredate from emp e left join dept d on e.deptno = d.deptno left join emp m on e.mgr = m.empno where e.hiredate < m.hiredate;

5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

select * from dept d left join emp e on d.deptno = e.deptno;

7. 列出最低月薪大于1500的各种工作及从事此工作的全部雇员人数。

select jobandsal.,jobandnum.numofjob from ( select min(e.sal) minsal,e.job from emp e group by e.job having minsal > 1500) jobandsal left join ( select count() numofjob,e.job from emp e group by e.job) jobandnum on jobandsal.job = jobandnum.job;

8. 列出在部门“SALES”（销售部）工作的员工的姓名。

select e.ename,e.deptno,newd.dname from emp e left join (select d.deptno,d.dname from dept d where d.dname = ‘sales’) newd on e.deptno = newd.deptno where e.deptno = newd.deptno;

9. 列出月薪高于公司平均月薪的所有员工，所在部门，上级领导，工资等级。

select e2.ename,e2.empno,e2.mgr,d.DNAME,e3.ename,e2.sal,ssal.GRADE from emp e2 left join dept d on e2.deptno = d.deptno left join emp e3 on e2.mgr = e3.empno left join salgrade ssal on e2.sal between ssal.LOSAL and ssal.HISAL where e2.sal > (select avg(e1.sal) avgsal from emp e1);

10. 列出与“SCOTT”从事相同工作的所有员工及部门名称。

select e2.*,d.dname from emp e2 left join dept d on e2.deptno = d.deptno where e2.job = (select e1.job from emp e1 where e1.ename = ‘scott’);

11. 列出月薪等于部门30中员工的月薪的所有员工的姓名和月薪。

select e2.ename,e2.sal from emp e2 where e2.sal in (select e1.sal from emp e1 where e1.deptno = 30);

12. 列出月薪高于在部门30工作的所有员工的月薪的员工的姓名、月薪、部门名称。

select newe.ename,newe.sal,d.DNAME from (select * from emp e2 where e2.deptno != 30) newe left join dept d on newe.deptno = d.deptno where newe.sal > (select max(e1.sal) from emp e1 where e1.deptno = 30);

13. 列出每个部门工作的员工的数量、平均工资和平均服务期限。

select new1.,new.service_year from (select e2.DEPTNO,avg(e2.SAL), count()from emp e2 GROUP BY e2.DEPTNO) new1 left join (select e.DEPTNO,avg(TIMESTAMPdiff(year,e.HIREDATE,CURRENT_DATE)) service_year from emp e GROUP BY e.DEPTNO) new on new1.DEPTNO = new.DEPTNO ;

14. 列出所有员工的姓名、部门名称和部门平均工资。

select e1.ename,e1.deptno,d.dname,avgsal.avsal from emp e1 left join dept d on e1.deptno = d.deptno left join (select e2.deptno,avg(sal) avsal from emp e2 group by e2.deptno) avgsal on e1.deptno = avgsal.deptno;

15. 列出所有部门的详细信息和部门人数。

select d.* ,new.num from dept d left join (select e.deptno,count(*) num from emp e group by e.deptno) new on d.deptno = new.deptno;

16. 列出各种工作的最低工资及从事此工作的员工姓名。

select e2.ename,e2.job,new.最低工资 from emp e2 join (select min(e1.sal) 最低工资,e1.job from emp e1 group by e1.job) new on e2.job = new.job and e2.sal = new.最低工资;

17. 列出各个部门的MANAGER(经理)的最低月薪。

select min(e.sal) from emp e where e.job = ‘manager’;

18. 列出所有员工的年总收入，按年总收入从低到高排序。

select e.*,e.sal *12 as 年总收入 from emp e order by 年总收入;

19. 列出员工的上级主管信息，并要求这些主管的月薪超过3000。

select e2.* from emp e2 where e2.empno in (select distinct(e1.mgr) from emp e1) and e2.sal > 3000;

20. 列出部门名称中带“S”字符的部门员工的月薪合计、部门人数。

select d.dname,d.deptno,sum(e.sal) 月薪合计,count(*) 部门人数
from emp e
right join dept d
on e.deptno = d.deptno
where d.dname like ‘%s%’
group by d.dname,d.deptno;

21.求出部门平均月薪最高的部门名和平均月薪。

select avg(e.sal) 部门平均月薪,d.dname from emp e inner join dept d on e.deptno = d.deptno group by e.deptno having 部门平均月薪 = (select max(部门平均月薪a ) 部门最大平均月薪 from (select avg(e.sal) 部门平均月薪a ,d.dname from emp e inner join dept d on e.deptno = d.deptno group by e.deptno) new);

简便写法：不严格
select max(temp.avgsal),d.dname
from (select avg(sal) avgsal,deptno from emp group by deptno) temp
join dept d on temp.deptno = d.deptno;
写法2，使用分页查询获得
select temp.avgsal,d.dname
from (select avg(sal) avgsal,deptno from emp group by deptno order by avgsal desc limit 1) temp
join dept d on temp.deptno = d.deptno;

查询出每个月薪级别中最高月薪的员工信息