经典的MySQL试题数据库ZS74520的博客-

1.取得每个部门最高薪水的人员名称

第一步：求出每个部门的最高薪水

seelct   e.deptno,max(e.sal) as maxsal from   emp e group by   e.deptno; 

将以上查询结果当成一个临时表t（deptno,maxsal）

select   e.deptno,e.ename,t.maxsal,e.sal from (seelct     e.deptno,max(e.sal) as maxsal    from     emp e    group by     e.deptno)t join   emp e on   t.deptno = d.deptno where   t.maxsal = e.sal order by   e.deptno; 

2.哪些人的薪水在部门平均薪水之上

第一步：求出每个部门的平均薪水

select     e.deptno,avg(e.sal) as avgsal from     emp e group by     e.deptno; 

将以上查询结果当成临时表t(deptno,avgsal)

select   t.deptno,e.ename from (select     e.deptno,avg(e.sal) as avgsal   from     emp e   group by     e.deptno)t join   emp e on   e.deptno = t.deptno where   e.sal > t.avfsal; 

3.取得部门中（所有人的）平均薪水等级

3.1取得部门中所有人的平均薪水等级
第一步：求出部门的平均薪水

select   e.deptno,avg(e.sal) as avgsal from   emp e group by   e.deptno; 

将以上查询结果当成临时表t(deptno,avgsal)

select   t.deptno,t.avgsal,s.grade from (select     e.deptno,avg(e.sal) as avgsal   from     emp e   group by     e.deptno)t join   salgrade s on   t.avgsal between s.losal and s.hisal; 

3.2取得部门中所有人的平均的薪水等级
第一步：求出每个人的薪水等级

select   e.deptno,e.enamem,s.grade from   emp e join   salgrade s on   e.sal between s.losal and s.hisal roder by   e.deptno; 

将以上查询数据当成临时表t(deptno,ename,grade)

select   t.deptno,avg(t.grade) as avgGrade from (select     e.deptno,e.enamem,s.grade   from     emp e   join     salgrade s   on     e.sal between s.losal and s.hisal)t group by   t.deptno; 

4.不用组函数（MAX），取得最高薪水（给出两种解决方案）

第一种：

select   sal from   emp order by   sal desc limit 1; 

第二种：
首先找到a表中的薪水低于b表中的薪水（去重）

select distinct a.sal from   emp a join   emp b on   a.sal < b.sal; 

然后：找到不在表中的数据
不在表中的薪水就是最高的薪水

select   sal from   emp where   sal not in(select distinct a.sal      from        emp a      join        emp b      on        a.sal < b.sal); 

5.取得平均薪水的最高部门编号

第一步：求出平均薪水

select   e.deptno,avg(e.sal) as avgsal from   emp e group by   e.deptno; 

第二步：将以上查询数据结果当成临时表t(deptno,avgsal)
然后求出平均薪水的最大值

select max(t.avgsal) as maxAvgSal from (select     e.deptno,avg(e.sal) as avgsal   from     emp e   group by     e.deptno)t; 

第三步：拿出最高平均薪水进行过滤

select   e.deptno,avg(e.sal) as avgsal from   emp e group by   e.deptno having   avgsal = (select max(t.avgsal) as maxAvgSal     from (select          e.deptno,avg(e.sal) as avgsal        from          emp e        group by          e.deptno) t); 

6.取得平均薪水最高的部门的部门名称

select   e.deptno,d.dname,avg(e.sal) as avgsal from   emp e join   dept d on   e.deptno = d.deptno group by   e.deptno,d.dname having   avgsal = (select max(t.avgsal) as maxAvgSal     from (select          e.deptno,avg(e.sal) as avgsal        from          emp e        group by          e.deptno) t); 

7.求平均薪水的等级最低的部门的部门名称

第一步：求出部门的平均薪水和部门名称

select   e.deptno,d.dname,avg(e.sal) as avgsal from   emp e join   dept d on   e.deptno = d.deptno group by   e.deptno,d.deptno; 

第二步：将以上查询结果当成临时表t(deptno,avgsal)与salgrade表连接
从而求出每个部门薪水等级

select   t.deptno,t.dname,s.grade from (select     e.deptno,d.dname,avg(e.sal) as avgsal   from     emp e   join     dept d   on     e.deptno = d.deptno   group by     e.deptno,d.deptno)t        join          salgrade s        on          t.avgsal between s.losal and s.hisal; 

第三步：将以上查询结果当成临时表
从而求出最低等级

select min(t.grade) as minGrade from (select            t.deptno,t.dname,s.grade          from (select              e.deptno,d.dname,avg(e.sal) as avgsal            from              emp e            join              dept d            on              e.deptno = d.deptno            group by              e.deptno,d.deptno)t                 join                   salgrade s                 on                   t.avgsal between s.losal and s.hisal)t; 

第四步：将最低等级代入部门所对应的编号和名称当中，得出最后结果

select   t.deptno,t.dname,s.grade from (select     e.deptno,d.dname,avg(e.sal) as avgsal   from     emp e   join     dept d   on     e.deptno = d.deptno   group by     e.deptno,d.deptno)t        join          salgrade s        on          t.avgsal between s.losal and s.hisal        where          s.grade = (select min(t.grade) as minGrade from (select            t.deptno,t.dname,s.grade          from (select              e.deptno,d.dname,avg(e.sal) as avgsal            from              emp e            join              dept d            on              e.deptno = d.deptno            group by              e.deptno,d.deptno)t                 join                   salgrade s                 on                   t.avgsal between s.losal and s.hisal)t); 

8.取得比普通员工（员工代码没有在mgr表上出现的）的最高薪水还要高的经理人姓名

第一步：找出普通员工（员工代码没有出现在mgr上的）
先找出mgr有哪些人

select distinct mgr from emp; 

注：
not in不会自动忽略控制（会有null参与数学运算）
in会自动会略空值（null不会参与数学运算）

第二步：找出普通员工的最高薪水

select max(sal) as maxsal from   emp where   empno not in(select distinct mgr from emp where mgr is not null); 

第三步：求出符合条件的经理的姓名

select ename from emp where sal > (select max(sal) as maxsal          from            emp          where            empno not in(select distinct mgr from emp where mgr is not null)); 

9.取得薪水最高的前五名员工

select * from emp order by sal desc limit 0,5; 

10.取得薪水最高的第六到第十名员工

select * from emp order by sal desc limit 5,5; 

11.取得最后入职的5名员工

select * from emp order by hiredate desc limit 5; 

12.取得每个薪水等级有多少员工

第一步：查出每个员工的薪水等级

select   e.ename,s.grade from   emp e join   salgrade s on   e.sal between s.losal and s.hisal order by   s.grade; 

第二步：将以上查询结果当成临时表t(ename,grade)
从而求出等级的总和

select   t.grade,count(t.ename) as totalEmp from (select     e.ename,s.grade   from     emp e   join     salgrade s   on     e.sal between s.losal and s.hisal)t group by   t.grade; 

**13.有3张表s(学生表),c(课程表),sc(学生选课表)

s(sno,sname) 代表 (学号，姓名)
c(cno,cname,cteacher) 代表 (课号，课名，教师)
sc(sno,cno,scgrade) 代表 (学号，课号，成绩)**

问题：
1.找出没选过“黎明”老师的所有学生姓名
2.列出2门以上（含2门）不及格学生姓名及平均成绩
3.学过1号课程又学过2号课所有学生的姓名

第一步：首先创建表并且添加数据

create table s(   sno int(4) primary key auto_increment,   sname varchar(32) ); insert into s(sname) values('zhangsan'); insert into s(sname) values('lisi'); insert into s(sname) values('wangwu'); insert into s(sname) values('zhaoliu'); create table c(   cno int(4) primary key auto_increment,   cname varchar(32),   cteacher varchar(32) ); insert into c(cname,cteacher) values('Java','吴老师'); insert into c(cname,cteacher) values('C++','王老师'); insert into c(cname,cteacher) values('C##','张老师'); insert into c(cname,cteacher) values('MySQL','郭老师'); insert into c(cname,cteacher) values('oRACLE','黎明'); create table sc(   sno int(4),   cno int(4),   scgrade double(3,1), constraint sc_sno_pk primary key(sno,cno), constraint sc_sno_fk foreign key(sno) references s(sno), constraint sc_cno_fk foreign key(cno) references c(cno), ); insert into sc(sno,cno,scgrade) values(1,1,30); insert into sc(sno,cno,scgrade) values(1,2,50); insert into sc(sno,cno,scgrade) values(1,3,80); insert into sc(sno,cno,scgrade) values(1,4,90); insert into sc(sno,cno,scgrade) values(1,5,70); insert into sc(sno,cno,scgrade) values(2,2,80); insert into sc(sno,cno,scgrade) values(2,3,50); insert into sc(sno,cno,scgrade) values(2,4,70); insert into sc(sno,cno,scgrade) values(2,5,80); insert into sc(sno,cno,scgrade) values(3,1,60); insert into sc(sno,cno,scgrade) values(3,2,70); insert into sc(sno,cno,scgrade) values(3,3,80); insert into sc(sno,cno,scgrade) values(4,3,50); insert into sc(sno,cno,scgrade) values(4,4,80); 

1.找出没选过“黎明”老师的所有学生姓名
第一步：找出黎明老师授课的编号

select cno from c where cteacher = '黎明'; 

第二步：找出选过黎明老师的学生编号

select sno from sc where cno = (select cno from c where cteacher = '黎明'); 

第三步：找出最后结果

select * from s where sno not in(select sno from sc where cno = (select cno from c where cteacher = '黎明')); 

2.列出2门以上（含2门）不及格学生姓名及平均成绩
第一步：找出这类学生

select   sc.sno,sname,count(*) as studentNum from   sc join   s on   sc.sno = s.sno where   scgraade < 60 group by   sc.sno,ssname having   studentNum >=2; 

第二步：找出该类学生平均成绩

select   sc.sno,avg(sc.scgrade) as scgrade from   sc group by   sc.sno; 

第三步，将上述两张表进行联接

select   t1.sname,t2.avgscgrade from (select     sc.sno,sname,count(*) as studentNum   from     sc   join     s   on     sc.sno = s.sno   where     scgraade < 60 group by     sc.sno,ssname   having     studentNum >=2)t1 join (select     sc.sno,avg(sc.scgrade) as scgrade   from     sc   group by     sc.sno)t2 on   t1.sno = t2.sno; 

3.学过1号课程又学过2号课所有学生的姓名
第一步：分别找出1号课程和2号课程的学生

select sno from sc where cno = 1; select sno from sc where cno = 2; 

第二步：找出符合条件的学生

select   s.sname from   sc join   s on   sc.sno = s.sno where   cno = 1 and sc.sno in(select sno from sc where cno = 2); 

14.列出所有员工及领导的名字（所有，运用到外连接）

select   e.ename，b.name as leadername from   emp e left join   emp b on   e.mgr = b.empno; 

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

select   d.dname,e.empno,e.ename from   emp e join   emp b on   e.mgr = b.empno join   dept d on   e.deptno = d.deptno where   e.hiredate < b.hiredate; 

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

select   d.dname,e.* from   emp e right join   dept d on   e.deptno = d.deptno; 

17.列出至少5个员工的所有部门

第一步：先求出每个部门的员工数量并对其分组

select   e.deptno,count(e.ename) as totalEmp from   emp group by   e.deptno; 

第二步：分组不满意，having过滤找到大于5个员工的所有部门

select   e.deptno,count(e.ename) as totalEmp from   emp e group by   e.deptno having   totalEmp >= 5; 

18.列出薪水比‘SMITH’多的所有员工信息

第一步：找到SMITH的薪水

select sal from emp where ename = 'SMITH'; 

第二步：找出大于该工资的其他员工

select * from emp where sal > (select sal from emp where ename = 'SMITH'); 

19.列出所有‘CLERK’(办事员)的姓名及其部门名称，部门人数

第一步：先找出其办事员和所对应的部门，并将查询结果看作t1表

select   d.deptno,d.dname,e.ename from   emp e join   dept d on   e.deptno = d.deptno where   e.job = 'CLERK'; 

第二步：求出每个部门的员工数量，将查询结果看作t2表

select   e.deptno,count(e.ename) as totalEmp from   emp group by   e.deptno; 

第三步：将t1和t2连接

select       t1.deptno,t1.dname,t1.ename,t2.totalEmp from (select     d.deptno,d.dname,e.ename   from     emp e   join     dept d   on     e.deptno = d.deptno   where     e.job = 'CLERK')t1 join (select     e.deptno,count(e.ename) as totalEmp   from     emp   group by     e.deptno)t2 on   t1.deptno = t2.deptno; 

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

第一步：先求出每种工作岗位的最低薪水

select   e.job,min(e.sal) as minsal from   emp e group by   e.job; 

第二步：对查询结果不满意，用having过滤，求出薪水大于1500及雇员人数

select   e.job,min(e.sal) as minsal，count(e.ename) as totalEmp from   emp e group by   e.job having   minsal > 1500; 

21.列出在部门‘SALES’<销售部>工作的员工姓名，假定不知道销售部门的部门编号

第一步：找出在部门名称为‘SALES’的部门编号

select deptno from dept where dname = 'SALES'; 

第二步：将上述查询结果带入此步查询结果中，找出员工姓名

select from emp wher deptno = (select deptno from dept where dname = 'SALES'); 

22.列出薪水高于公司平均薪水的所有员工，所在部门、上级领导、雇员的工资等级

第一步：求出公司的平均薪水

select avg(sal) as avgsal from emp; 

第二步：进行表连接，查询出对应结果

select   d.dname,   e.ename,   b.ename as leadername,   s.grade from   emp e join   dept d on   e.deptno = d.deptno left join   emp b on   e.mgr = b.empno join   salgrade s on   e.sal between s.losal and s.hisal where   e.sal > (select avg(sal) as avgsal from emp); 

23.列出与‘SCOTT’从事相同工作的所有员工及部门名称

第一步：查询出‘SCOTT’的工作感岗位

select job from emp where ename = 'SCOTT'; 

第二步：连接表将所有员工及部门名称查询出来

select   d.dname,   e.* from   emp e join   dept d on   e.deptno = d.deptno where   e.job = (select job from emp where ename = 'SCOTT'); 

24.列出薪水等于30中员工的薪水的其他员工的姓名和薪水

第一步：找出部门30中薪水有哪些值

select distinct sal from emp where deptno = 30; 

第二步：找出其他员工

select   deptno,ename,sal from   emp where   sal in(select distinct sal from emp where deptno = 30) and deptno <> 30; 

25.列出薪水高于部门30的所有员工的薪水的员工姓名和薪水、部门名称

第一步：先找出部门30的最高薪水

select max(sal) as maxsal from emp where deptno = 30; 

第二步：找出这些员工

select   d.dname,   e.ename,   e.sal from   emp e join   dept d on   e.deptno = d.deptno where   e.sal > (select max(sal) as maxsal from emp where deptno = 30); 

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

to_days(日期类型)–> 天数
获取数据库的系统当前时间的函数是：now()
第一步：先求出平均服务期限

select ename,(to_days(now()) - to_days(hiredate))/365 as serveryear from emp; select avg((to_days(now()) - to_days(hiredate))/365) as avgserveryear from emp; 

第二步：求出对应的员工信息

select   e.deptno, count(e.ename) as totalEmp, avg(e.sal) as avgsal, avg((to_days(now()) - to_days(hiredate))/365) as avgserveryear from   emp e group by   e.deptno; 

27.列出所有员工的姓名、部门名称和工资

select   d.dname,   e.ename,   e.sal from   emp e right join   dept d on   e.deptno = d.deptno; 

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

select   d.deptno,d.dname,d.loc,count(e.ename) as totalEmp from   emp e right join   dept d on   e.deptno = d.deptno group by   d.deptno,d.dname,d.loc; 

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

第一步：类出各个工作的最低工资

select   e.job,min(e.sal) as minsal from   emp e group by   e.job; 

第二步：将上述查询结果当成临时表t（job,minsal）进行连接

select   e.ename from   emp e join (select     e.job,min(e.sal) as minsal   from     emp e   group by     e.job) t on   e.job = t.job where   e.sal = t.minsal; 

30.列出各个部门MANAGER的最低薪水

select   e.deptno,min(e.sal) as minsal from   emp e where   e.job = 'MANAGER' group by   e.deptno; 

31.列出所有员工的年工资，按年薪从低到高排序

select ename,(sal + ifnull (comm,0)) * 12 as yearsal from emp order by yearsal asc; 

32.求出员工领导的薪水超过3000的员工名称和领导名称

select   e.ename,   b.ename as leadername from   emp e join   emo b on   e.mgr = b.empno where   b.sal > 3000; 

33.求部门名称中带“S”字符的部门员工的工资合计、部门人数

select   d.dname, sum(e.sal) as sumsal, count(e.ename) as totalEmp from   emp e join   dept d on   e.deptno = d.deptno where   d.dname like '$s%' group by   d.dname; 

34.给任职日期超过30年的员工加薪10%

update emp_bak1 set sal = sal * 1.1 where (to_days(now()) - to_days(hiredate))/365 > 30;