MySQL练习题及解题思路

于 2020-04-09 20:29:23 发布
阅读量805 收藏 2
点赞数
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_44660150/article/details/105419198
版权

1、取得每个部门最高薪水的人员名称
第一步：求出每个部门的最高薪水
SELECT e.deptno,MAX(e.sal) FROM emp e GROUP BY e.deptno;
第二部：将以上查询结果当成一个临时表t(deptno,maxsal)
SELECT e.deptno,e.ename,e.sal FROM
(SELECT e.deptno,MAX(e.sal) AS maxsal FROM emp e GROUP BY e.deptno)
t JOIN emp e ON t.deptno=e.deptno WHERE t.maxsal=e.sal ORDER BY e.deptno;

2、哪些人的薪水在部门平均薪水之上？
查出平均工资SELECT e.deptno,AVG(e.sal) AS aal FROM emp e GROUP BY e.deptno;
SELECT e.deptno,e.ename,e.sal,a.aal FROM
(SELECT e.deptno,AVG(e.sal) AS aal FROM emp e GROUP BY e.deptno) a
JOIN emp e ON a.deptno=e.deptno WHERE e.sal>a.aal ;
SELECT e.deptno;

3、取得部门中（所有人的）平均薪水等级(平均的薪水等级）
求平均薪水的等级
求部门平均薪水SELECT e.deptno,AVG(e.sal) AS tsal FROM emp e GROUP BY e.deptno;
SELECT t.deptno,s.grade
FROM ( SELECT e.deptno,AVG(e.sal) AS tsal FROM emp e GROUP BY e.deptno) t
JOIN salgrade s
ON t.tsal between s.losal and s.hisal ;
求部门中所有人的平均的薪水等级
SELECT e.deptno,e.ename,s.grade
FROM emp e JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal ORDER BY e.deptno;
求部门平均等级，将以上查询结果当成临时表t
SELECT t.deptno,avg(t.grade)AS vgrade FROM (SELECT e.deptno,e.ename,s.grade
FROM emp e JOIN salgrade s
ON e.sal BETWEEN s.losal AND s.hisal ORDER BY e.deptno)t GROUP BY t.deptno;

4、求最高薪水(两种方法不准用MAX）
方法一 SELECT sal from emp ORDER BY sal DESC LIMIT 1;

方法二 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;
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,AVG(e.sal)as avgsal from emp e GROUP BY e.deptno;
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.dname;
第二步等级最低的部门名称
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.dname)t JOIN salgrade s ON t.avgsal BETWEEN s.losal
AND s.hisal;
第三步：将以上查询结果当成临时表t
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.dname)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.dname)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.dname)t JOIN salgrade s ON t.avgsal BETWEEN s.losal
AND s.hisal)t);
7、 求平均薪水等级最低的部门的部门名称
select d.dname, avg(s.grade) agrade
from emp e
join dept d on e.deptno =d.deptno
join salgrade s on e.sal between s.losal and s.hisal
group by d.dname order by agrade asc limit 1;

8、取得比普通员工（院代码没有在mgr字段出现的）的最高薪水还要高的领导人姓名
第一步找出比普通员工（员工代码没有在mgr上出现的）的最高薪水还要高的经理人姓名
SELECT DISTINCT mgr from emp;
select * from emp where empno in (SELECT DISTINCT mgr from emp);
select max(sal) from emp where empno not in (SELECT DISTINCT mgr from emp where mgr is not null);
not in 不会自动忽略空值，in会自动忽略空值
select ename from emp where sal>1600;
最终语句
select ename from emp where sal>(select max(sal) from emp where empno not in (SELECT DISTINCT mgr from emp where mgr is not null));

9、 取得薪水最高的前五名员工(mysql 默认排序是升序）
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;
第二部计算每个等级的员工数
select t.grade,COUNT(t.ename )as con 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、面试题（58-59）
有三个表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));

create table c(cno int(4) primary key auto_increment,cname varchar(32),cteacher varchar(32));

create table sc(sno int(4),cno int(4),scgrade double(3,1),
constraint sc_sno_cno_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 s(sno,sname) values(1,‘a’);
insert into s(sno,sname) values(2,‘b’);
insert into s(sno,sname) values(3,‘c’);
insert into s(sno,sname) values(4,‘d’);

insert into c(cno,cname,cteacher) values(1,‘java’,‘王老师’);
insert into c(cno,cname,cteacher) values(2,‘C++’,‘张老师’);
insert into c(cno,cname,cteacher) values(3,‘C#’,‘李老师’);
insert into c(cno,cname,cteacher) values(4,‘mysql’,‘周老师’);
insert into c(cno,cname,cteacher) values(5,‘oracle’,‘黎明’);

insert into sc(sno,cno,scgrade) values(1,1,50);
insert into sc(sno,cno,scgrade) values(1,2,50);
insert into sc(sno,cno,scgrade) values(1,3,50);
insert into sc(sno,cno,scgrade) values(2,2,80);
insert into sc(sno,cno,scgrade) values(2,3,70);
insert into sc(sno,cno,scgrade) values(2,4,59);
insert into sc(sno,cno,scgrade) values(3,1,60);
insert into sc(sno,cno,scgrade) values(3,2,61);
insert into sc(sno,cno,scgrade) values(3,3,99);
insert into sc(sno,cno,scgrade) values(3,4,100);
insert into sc(sno,cno,scgrade) values(3,5,52);
insert into sc(sno,cno,scgrade) values(4,3,82);
insert into sc(sno,cno,scgrade) values(4,4,99);
insert into sc(sno,cno,scgrade) values(4,5,40);
问题：
1、找出没选过‘黎明’老师的所有学生姓名；
2、列出2门以上（含2门）不及格学生姓名及平均成绩；
3、既学过1号课程又学过2号课所有学生的姓名

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、 select * from sc where scgrade<60;先求不及格成绩
select sc.sno,s.sname,COUNT() as studentNum
from sc join s on sc.sno=s.sno
where scgrade<60 GROUP BY sc.sno,s.sname HAVING studentNum>=2;
求出平均成绩
SELECT sc.sno,avg(sc.scgrade)as scgrade
from sc group by sc.sno;
思路t1.sno=t2.sno;
最终语句
select t1.sname,t2.scgrade
from (select sc.sno,s.sname,COUNT() as studentNum
from sc join s on sc.sno=s.sno
where scgrade<60 GROUP BY sc.sno,s.sname 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号课所有学生的姓名
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.ename 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 d.dname,COUNT(e.deptno)as coun
FROM emp e
JOIN dept d
ON d.deptno=e.deptno
GROUP BY d.deptno,e.deptno having COUNT(e.deptno)>=5;
另一种写法
select d.deptno,d.dname,count(e.deptno) from
emp e join dept d on e.deptno = d.deptno
group by e.deptno having count(e.deptno) >= 5;
没要求部门名称可以这么写，只需部门编号
SELECT e.deptno,COUNT(e.ename)as conanme
FROM emp e
GROUP BY e.deptno
HAVING conanme>=5;

18、列出薪金比‘simith’多的所有员工信息
simith的工资 SELECT sal from emp where ename=‘simith’;
SELECT * FROM emp WHERE sal>(SELECT sal from emp where ename=‘simith’);

19、列出所有‘clerk’（办事员）的姓名及部门名称，部门人数
SELECT e.ename,d.dname
FROM emp e
JOIN dept d
on e.deptno=d.deptno
WHERE e.job=‘clerk’;
求出每个部门的人数
SELECT e.deptno,COUNT(e.ename)as couname
FROM emp e GROUP BY e.deptno;
最终语句
SELECT t1.deptno,t1.ename,t1.dname,t2.couname
FROM (SELECT e.ename, d.deptno,d.dname FROM emp e JOIN dept d
on e.deptno=d.deptno WHERE e.job=‘clerk’)t1 right
JOIN (SELECT e.deptno,COUNT(e.ename)as couname
FROM emp e GROUP BY e.deptno)t2
ON t1.deptno=t2.deptno;

20、列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
先求各种工作的最低薪资
SELECT e.job, MIN(sal) AS minsal,COUNT(ename)AS couname
FROM emp e
group by e.job HAVING minsal>1500;

21、列出在部门‘sales’（销售部）工作的员工的姓名，假定不知道销售部的部门编号
SELECT deptno from dept where dname=‘sales’;
SELECT ename,deptno from emp e where
deptno=(SELECT deptno from dept where dname=‘sales’);

22、列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，雇员的工资等级
select avg(sal) from emp;
最终语句
SELECT e.ename,d.dname,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) from emp);

23、列出与‘scott’从事相同工作的所有员工及部门名称
从事什么工作 SELECT job from emp where ename=‘scott’;
SELECT e.ename,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中员工的薪金的其他员工的姓名和薪金
用distinct去重
SELECT distinct sal from emp where deptno=30;
最终语句
SELECT ename,sal from emp where sal in
(SELECT distinct sal from emp where deptno=30) and
deptno<>30;

25、列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金，部门名称
SELECT sal from emp where deptno=30;
SELECT max(t1.sal) from emp where deptno=30;
最终语句
SELECT e.ename,e.sal,d.dname from emp e join dept d on e.deptno=d.deptno
where e.sal >(SELECT max(sal) from emp where deptno=30);

26、列出在每个部门工作的员工数量，平均工资和平均服务期限
to days(日期类型）->天数
获取数据库的系统当前时间的函数
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 couname,avg(e.sal)as avgsal,
avg((TO_DAYS(NOW())-TO_DAYS(hiredate))/365) as avgserveryear
from emp e GROUP BY e.deptno ;
(排序可在末尾加上ORDER BY couname ASC)

27、列出所有员工的姓名、部门名称、工资
SELECT d.dname,e.ename,e.sal from emp e right join dept d on
e.deptno=d.deptno ;
(排序可在末尾加上 ORDER BY d.dname)

28、列出所有部门的详细信息和人数
SELECT d.deptno,d.dname,d.loc,COUNT(e.ename)as total from emp e
right join dept d on d.deptno=e.deptno GROUP BY d.deptno,d.deptno,d.loc;

29、列出各种工作的最低工资以及从事此工作的雇员姓名
SELECT e.job,min(e.sal)as minsal from emp e group by e.job;

SELECT e.ename,t.minsal 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;

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

30、列出各个部门的manager的最低薪金
SELECT deptno,MIN(sal)as minsal from emp where job=‘manager’ group by deptno;

31、列出员工的年工资，按年薪从低到高排序
SELECT ename,(sal+IFNULL(comm,0))*12 as nianxin from emp order BY nianxin ASC;

32、求出员工领导的薪水超过3000的员工名称与领导姓名
SELECT e.ename,b.ename as leadername,b.sal
from emp e join emp b on e.mgr=b.empno where b.sal>3000;

33、求出部门名称中，带有‘s’字符的部门员工的工资合计、部门人数
SELECT dname,deptno from dept WHERE dname LIKE ‘%s%’;

SELECT t.dname,sum(e.sal),COUNT(e.ename)
FROM emp e join (SELECT dname,deptno from dept WHERE dname LIKE ‘%s%’)t
on t.deptno=e.deptno GROUP BY t.dname;

SELECT d.dname,SUM(e.sal),COUNT(e.ename) from emp e JOIN
dept d on e.deptno=d.deptno WHERE d.dname like ‘%s%’ GROUP BY d.dname;

34、给任职日期超过35年的员工加薪10%
create table emp_p as select * from emp;
UPDATE emp_p set sal=sal*1.1 where (TO_DAYS(NOW())-TO_DAYS(hiredate))>35;

蒋公子丨
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值