MySql--练习

mysql练习

涉及到的表：

员工表：

部门表：

工资等级表：

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

第一步：取得每个部门最高薪水【按照部门分组求最大值】
select deptno,max(sal) as maxsal from emp group by deptno;

第二步：将上面的查询结果当做临时表t，t表和emp e表进行表连接，
条件：t.deptno=e.deptno and t.maxsal=e.sal

select e.ename,t.*
from emp e
join (select deptno,max(sal) as maxsal from emp group by deptno) t
on t.deptno=e.deptno and t.maxsal=e.sal;

2.求出薪水在部门的平均薪水之上的人员

第一步：找出部门的平均薪水【按照部门编号分组求平均薪水】
select deptno,avg(sal) as avgsal from emp group by deptno;

第二步：将上面的查询结果当做临时表t，t表和emp e表进行表连接：
条件：t.deptno=e.deptno and e.sal>t.avgsal

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

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

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

第一步：取得部门的平均薪水
select deptno,avg(sal) as avgsal from emp group by deptno;

第二步：将上面的查询结果当做临时表t，t表和salgrade s进行连接，
条件：t.avgsal between s.losal and s.hisal

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

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

第一步：取得每一个员工的薪水等级
select e.ename,e.sal,e.deptno,s.grade
from emp e
join salgrade s
on e.sal between s.losal and s.hisal;

第二步：在以上的sql语句基础之上，继续以部门编号分组，求等级的平均值
select e.deptno,avg(s.grade)
from emp e
join salgrade s
on e.sal between s.losal and s.hisal
group by e.deptno;

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

方案一：按照薪水降序排列，取第一个
select sal from emp order by sal desc limit 1;

方案二：自连接
select sal from emp;//a表
select sal form emp;//b表
a表和b表进行连接：条件是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 deptno,avg(sal) as avgsal from emp group by deptno;

第二步：取得平均薪水的最大值
select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1;

第三步：第一步和第二步联合
select deptno,avg(sal) as avgsal
from emp
group by deptno
having avg(sal)=(select avg(sal) avgsal from emp group by deptno order by avgsal desc limit 1);

方案二：max函数

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

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

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

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

第一步：求各个部门平均薪水的等级
select deptno,avg(sal) as avgsal from emp group by deptno;

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

第二步：获取最高等级值
select max(s.grade)
from (select avg(sal) as avgsal from emp group by deptno) t
join salgrade s
on t.avgsal between s.losal and s.hisal;

第三步：第一步和第二步联合
select t.dname,t.avgsal,s.grade
from (select d.dname,avg(e.sal) as avgsal from emp e join dept d on e.dept=d.deptno group by d.dname) t
join salgrade s
on t.avgsal between s.losal and s.hisal
where s.grade=(
        select max(s.grade)
        from (select avg(sal) as avgsal from emp group by deptno) t
        join salgrade s
        on t.avgsal between s.losal and s.hisal);

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

第一步：找出普通员工
select * from emp where empno not in(select distinct mgr from emp);
以上语句无法查询到结果，因为not in不会自动忽略null，需要手动排除null：
select * from emp where empno not in(select distinct mgr from emp where mgr is not null);

第二步：找出普通员工最高薪水
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);

第三步：找出薪水高于1600（第二步的结果）即可
select ename,sal from emp where sal>(select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null));

注意：not in不会自动忽略空值，但是in会自动忽略空值

select * from emp where empno in(select distinct mgr from emp);

补充：case...when...then...when...then...else...end使用在DQL语句中，类似于java中的switch...case

select ename,sal,(case job when 'manager' then sal*1.1 when 'salesman' then sal*1.5 end) as newsal
from emp;

其他情况保持原薪水：
select ename,sal,(case job when 'manager' then sal*1.1 when 'salesman' then sal*1.5 else sal end) as newsal
from emp;

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

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

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

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

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

select ename,hiredate from emp order by hiredate desc limit 5;

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

第一步：找出每一个员工的薪水等级
select e.ename,e.sal,s.grade from emp e join salgrade s 
on e.sal between s.losal and s.hisal;

第二步：在以上结果的基础之上，按照grade分组，计数
select s.grade,count(*)
from emp e
join salgrade s
on e.sal between s.losal and s.hisal
group by s.grade;

13.面试题

有3个表：S(学生表)，C（课程表），SC（学生所选课表）
S(SNO,SNAME)代表（学号，姓名）
C(CNO,CNAME,CTEACHER)代表（课号，课名，教师）
SC(SNO,CNO,SCGRADE)代表（学号，课号，成绩）
问题：
1.找出没选过"黎明"老师的所有学生姓名。
2.列出2门以上（含两门）不及格学生姓名及平均成绩
3.既学过1号课程又学过2号课程所有学生的姓名

解：

• 主键只有一个：sno+cno是复合主键
• 外键有两个：同时sno是外键，cno也是外键

问题一：找出没选过"黎明"老师的所有学生姓名

第一步：找出黎明老师所授课程编号
select cno from c where cteacher='黎明';

第二步：通过"学生所选课程"查询 cno=上面结果 的sno，这些sno都是选择黎明老师课程的学号
select sno from sc where cno=(select cno from c where cteacher='黎明');

第三步：在学生表中查询sno not in上面结果的数据
select sname 
from s 
where sno not in(select sno from sc where cno=(select cno from c where cteacher='黎明'));

问题二：列出2门以上（含两门）不及格学生姓名及平均成绩

第一步：列出2门以上（含两门）不及格学生姓名
select sc.sno,s.sname
from sc
join s
on sc.sno=s.sno
where sc.scgrade<60
group by sc.sno,s.sname
having count(*)>=2;

第二步：找出每一个学生的平均成绩
select sno,avg(scgrade) as avgscore from sc group by sno;

第三步：第一步和第二步联合
select t1.sname,t2.avgscore
from (select sc.sno,s.sname
            form sc
            join s
            on sc.sno=s.sno
            where sc.scgrade<60
            group by sc.sno,s.sname
            having count(*)>=2) t1
join (select sc.sno,avg(sc.scgrade) as avgscore from sc group by sc.sno) t2
on t1.sno=t2.sno;

问题三：既学过1号课程又学过2号课程所有学生的姓名

第一步：找出学过1号课程的学生
select sno from sc where cno=1;

第一步：找出学过2号课程的学生
select sno from sc where cno=2;

第三步：第一步和第二步联合
select s.sname 
from sc 
join s
on sc.sno=s.sno
where sc.cno=1 and sc.sno 
in(select sno from sc where cno=2);

14.列出所有员工及领导的姓名

emp a<员工表>
emp b<领导表>

select a.ename empname,b.ename leadername
from emp a 
left join emp b
on a.mgr=b.empno;

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

emp a<员工表>
emp b<领导表>

select a.empno '员工编号',a.ename '员工姓名',a.hiredate '员工入职日期',
       b.empno '领导编号',b.ename '领导姓名',b.hiredate '领导入职日期',
       d.dname
from emp a
join emp b
on a.mgr=b.empno
join dept d
on a.deptno=d.deptno
where a.hiredate<b.hiredate;

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

emp e<员工表>
dept d<部门表>

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

17.列出至少有5个员工的所有部门【部门详细信息】

emp e<员工表>
dept d<部门表>

select d.deptno,d.dname,d.loc,count(e.ename)
from emp e
join dept d
on e.deptno=d.deptno
group by d.deptno,d.dname,d.loc
having count(e.ename)>=5;

18.列出薪资比“smith”多的所有员工信息

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

19.列出所有“clerk”（办事员）的姓名及其部门名称，部门的人数

第一步：
select e.ename,d.dname 
from dept d 
join emp e 
on e.deptno=d.deptno
where e.job='clerk';

第二步：
select deptno,count(*) as totalEmp from emp e group by deptno;

联合：
select e.ename,d.dname,t.totalEmp
from dept d 
join emp e 
on e.deptno=d.deptno
join (select deptno,count(*) as totalEmp from emp e group by deptno) t
on d.deptno=t.deptno
where e.job='clerk';

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

select min(sal),count(*),job from emp group by job having min(sal)>1500;

21.列出在部门“sales”（销售部）工作的员工的姓名，假定不知道销售部的部门编号

select ename from emp where 
deptno=(select deptno from dept where dname='sales');

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

emp a<员工表>
emp b<领导表>
dept d<部门表>
salgrade s<工资等级表>

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

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

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

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

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

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

找出30部门的最高薪资
select max(sal) from emp where deptno=30;

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

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

第一步：将员工表emp e和部门表dept d进行表连接，将部门表数据全部显示
select e.*,d.*
from emp e
right join dept d
on e.deptno=d.deptno;
    
第二步：以上查询结果的基础之上按照d.deptno分组，按照e.ename计数

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

第三步：以上结果的基础之上继续计算平均工资
select d.deptno,count(e.ename) totalEmp,ifnull(avg(e.sal),0) avgsal
from emp e
right join dept d
on e.deptno=d.deptno
group by d.deptno;

第四步：在以上结果的基础之上继续计算平均服务期限

to_days():把日期转换为天

select d.deptno,
       count(e.ename) as totalEmp,
       ifnull(avg(e.sal),0) as avgsal,
       ifnull(avg((to_days(now())-to_days(hiredate))/365),0) as avgtime
from emp e
right join dept d
on e.deptno=d.deptno
group by d.deptno;

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

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

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

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

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

第一步：得出各种工作的最低工资：
select job,min(sal) as minsal
from emp
group by job;

第二步：将以上结果看做临时表t，和emp表进行连接
select e.ename,t.*
from emp e
join (select job,min(sal) as minsal
        from emp
        roup by job) t
on e.job=t.job and e.sal=t.minsal;

30.列出各个部门的manager的最低薪资

select deptno,min(sal)
from  emp
where job='manager'
group by deptno;

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

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

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

select a.ename empname,a.sal,b.ename leadername,b.sal
from emp a
join emp b
on a.mgr=b.empno
where b.sal>3000;

33.求出部门名称中，带‘S’字符的部门员工的工资合计、部门人数

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

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

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