MySQL练习题

重点掌握:
1 通过连接同时锁定两个条件
4 逻辑学习一下
5 目前解决limit中并列排名的问题
8 挺有意思，可以看一下
14 BOSS的问题
26 平均年限的两种求法(to_days()和datediff())

use bjpowernode;
-- 1 取得每个部门最高薪水的人员名称
select a.ename,a.sal,a.deptno from emp a join
(select deptno, max(sal) 最高工资 from emp group by deptno) b
on a.deptno=b.deptno and a.sal=b.最高工资;

-- 2 哪些人的薪水在部门平均薪水之上
select a.ename,a.sal from emp a join
(select deptno, avg(sal) 平均工资 from emp group by deptno) b
on a.deptno=b.deptno and a.sal>b.平均工资;

-- 3 取得部门中所有人的平均的薪水等级
select e.deptno, avg(s.grade) 平均等级 from emp e join salgrade s
on e.sal between s.losal and hisal
group by deptno;

-- 4 不准使用组函数Max，给出最高薪水（给出两种解决方案）
select sal from emp order by sal desc limit 1;
-- 方法二:使用自连接(最大值不会小于表中任何一个工资)
select sal 最高工资 from emp where sal not in
(select a.sal from emp a join emp b
on a.sal < b.sal);
-- 方法三
select * from emp where sal >= all(select sal from emp);

-- 5 取得平均薪水最高的部门的部门编号
-- 我的方法:使用limit都会导致忽略并列情况
select deptno, avg(sal) 平均工资 from emp group by deptno
order by 平均工资 desc limit 1;
-- 答案
select deptno, avg(sal) 平均工资 from emp group by deptno
having avg(sal)=(select avg(sal) av from emp group by deptno  order by av desc limit 1);

-- 6 取得平均薪水最高的部门名称
select a.dname, b.平均工资 from dept a join
(select deptno, avg(sal) 平均工资 from emp group by deptno) b
on a.deptno=b.deptno order by b.平均工资 desc limit 1;
-- 方法二:可以取到并列情况.(having执行顺序在select之后)
select d.dname,avg(e.sal) 平均工资 from emp e join dept d on e.deptno=d.deptno
group by e.deptno
having 平均工资=(select avg(sal) av from emp group by deptno  order by av desc limit 1);

-- 7 求平均薪水的等级最高的部门的部门名称
select d.deptno,d.dname,b.av,b.grade from dept d join
(select a.deptno,a.av,grade from salgrade s join
(select deptno, avg(sal) av from emp group by deptno) a
on a.av between s.losal and s.hisal) b 
on d.deptno=b.deptno
where b.grade=(select max(grade) from salgrade s join
(select avg(sal) av from emp group by deptno) a
on a.av between s.losal and s.hisal);

-- 8 取得比普通员工（员工代码没在mgr字段出现的）最高薪水更高的领导人姓名
-- note:使用not in的时候一定要小心NULL,否则结果什么都查不到.
select ename, sal from emp where empno in (select distinct mgr from emp) and sal >
(select max(sal) 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 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 grade,count(*) 人数 from emp join salgrade s on emp.sal between s.losal and s.hisal
group by grade order by grade;

-- 13 面试题
-- 13.1显示没选”黎明“老师课的学生姓名,使用not in切记小心!
select sno,sname from s where sno not in 
(select distinct sno from sc where cno in (select cno from c where cteacher='黎明'));
-- 方法二
SELECT sname from s WHERE sno not in (SELECT sno FROM sc WHERE cno = any(SELECT cno FROM c WHERE cteacher= '黎明')); 
-- 13.2列出2门以上（含2门）不及格学生姓名及平均成绩
select s.sname,avg(scgrade) from s join sc on s.sno=sc.sno where s.sno in
(select sc.sno from sc where scgrade < 60
group by sc.sno having count(*)>=2)
group by s.sno;
-- 13.3学过1号课程和2号课程的所有学生的姓名
select distinct sname from s join sc 
on s.sno=sc.sno where sc.cno in(1,2);

-- 14 列出所有员工及领导的姓名
select a.ename 员工,ifnull(b.ename, 'Boss') 领导 from emp a left join emp b
on a.mgr=b.empno;

-- 15 列出受雇日期早于其上级的所有员工的编号、姓名、部门名称
select a.empno 编号,a.ename 姓名,d.dname 部门 from dept d join emp a
on d.deptno=a.deptno left join emp b
on a.mgr=b.empno where a.hiredate<b.hiredate;

-- 16 列出部门名称和这些部门的员工信息，同时列出没有员工的部门
select * from dept left join emp 
on dept.deptno=emp.deptno order by dept.dname;

-- 17 列出至少有5名员工的所有部门
select dept.deptno, dname, count(*) 人数 from dept join emp 
on dept.deptno=emp.deptno 
group by dept.deptno having 人数>=5;

-- 18 列出薪水比simith多的所有员工信息
select * from emp where sal>
(select sal from emp where ename='simith');

-- 19 列出所有岗位为clerk的姓名及部门名称，部门人数
select b.ename,a.dname,c.num 人数 from dept a join
(select ename,deptno from emp where job='clerk') b on a.deptno=b.deptno
join (select deptno, count(*) num from emp group by deptno) c
on b.deptno=c.deptno;
-- 方法二:先计算出每个部门的人数,然后作为临时表
select a.ename,b.dname,b.numbers from emp a join
(select d.deptno,d.dname,count(*) as numbers from dept d join emp e on d.deptno=e.deptno group by e.deptno) b
on a.deptno=b.deptno 
where a.job='clerk';

-- 20 列出最低薪水大于1500的各种工作及从事此工作的全部雇员人数
select job, min(sal) msal, count(*) 人数 from emp group by job having msal>1500;





-- 21 列出在部门sales工作的员工的姓名，假定不知道销售部的部门编号
select emp.ename from emp join dept on emp.deptno=dept.deptno
where dept.dname='sales';

-- 22 列出薪水高于公司平均薪水的所有员工，所在部门，上级领导，薪水等级
select e1.ename 姓名,d.dname 部门,e2.ename 领导, s.grade 薪水等级
from dept d join emp e1 on d.deptno=e1.deptno 
left join emp e2 on e1.mgr=e2.empno
join salgrade s on e1.sal between s.losal and s.hisal
where e1.sal>(select avg(sal) from emp); 

-- 23 列出与scott从事相同工作的所有员工及部门名称
select emp.ename,dname,job
from emp join dept on emp.deptno=dept.deptno
where job=(select job from emp where ename='scott') and ename != 'scott';

-- 24 列出薪水等于部门30中员工的薪水的其他员工的姓名和薪水
select ename,sal,deptno from emp where sal=any(select sal from emp where deptno=30)
and deptno<>30;

-- 25 列出薪水高于部门30的全部员工的员工姓名，薪水，部门名称
select ename,sal,dname from emp join dept on dept.deptno=emp.deptno
where sal> (select max(sal) from emp where deptno=30)
and emp.deptno<>30;

-- 26 列出在每个部门工作的员工数量，平均工资和平均服务期限
select d.deptno,dname,count(ename) 员工数量,ifnull(avg(sal),0) 平均工资,
ifnull(avg((to_days(now())-to_days(e.hiredate))/365),0) as avgyear
from dept d left join emp e on e.deptno=d.deptno group by d.deptno;

-- 27 列出所有员工的姓名、部门名称、工资
select ename,dname,sal from emp e join dept d
on e.deptno=d.deptno;

-- 28 列出所有部门的详细信息和人数
select d.deptno,dname,loc,count(e.ename) from dept d left join emp e
on d.deptno=e.deptno group by d.deptno;

-- 29 列出各种工作的最低工资以及从事此工作的雇员姓名
select a.ename,b.job,b.msal from emp a join
(select job,min(sal) msal from emp group by job) b
on a.job=b.job and a.sal=b.msal;
-- 方法二
select job, min(sal), group_concat(ename) 员工 from emp 
group by job;

-- 30 列出各个部门的manager的最低薪水
-- 思考一下:这个方法可以筛选到领导
select a.deptno,min(a.sal) from emp a join emp b
on a.empno=b.mgr group by a.deptno;

-- 31 列出员工的年工资，按年薪从低到高排序
select ename,(sal+ifnull(comm,0))*12 年薪 from emp order by 年薪;

-- 32 求出员工领导的薪水超过3000的员工姓名和领导姓名
select a.ename,b.ename from emp a left join emp b
on a.mgr=b.empno
where b.sal>3000;

-- 33 求出部门名称中，带有‘s’字符的部门员工的工资合计，部门人数
select d.deptno,dname, sum(sal), count(ename) from dept d left join emp e
on d.deptno=e.deptno
group by d.deptno having dname regexp 's';

-- 34 给任职时间超过35年的员工加薪10%
update emp set sal=sal*1.1 where
(to_days(now())-to_days(hiredate))/365>35;

下面提供建表代码

drop table if exists dept;
drop table if exists salgrade;
drop table if exists emp;
 
create table dept(
		deptno int(10) primary key,
		dname varchar(14),
		loc varchar(13)
		);
		
create table salgrade(
		grade int(11),
		losal int(11),
		hisal int(11)
		);
		
create table emp(
		empno int(4) primary key,
		ename varchar(10),
		job varchar(9),
		mgr int(4),
		hiredate date,
		sal double(7,2),
		comm double(7,2),
		deptno int(2)
		);
		
insert into dept(deptno,dname,loc) values(10,'ACCOUNTING','NEW YORK');
insert into dept(deptno,dname,loc) values(20,'RESEARCHING','DALLAS');
insert into dept(deptno,dname,loc) values(30,'SALES','CHICAGO');
insert into dept(deptno,dname,loc) values(40,'OPERATIONS','BOSTON');
 
insert into salgrade(grade,losal,hisal) values(1,700,1200);
insert into salgrade(grade,losal,hisal) values(2,1201,1400);
insert into salgrade(grade,losal,hisal) values(3,1401,2000);
insert into salgrade(grade,losal,hisal) values(4,2001,3000);
insert into salgrade(grade,losal,hisal) values(5,3001,5000);
 
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7369,'SIMITH','CLERK',7902,'1980-12-17',800,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,null,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,null,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7839,'KING','PRESIDENT',null,'1981-11-17',5000,null,10);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,null,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7900,'JAMES','CLERK',7698,'1981-12-03',950,null,30);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,null,20);
insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)
	values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,null,10);
	
select * from dept;
select * from salgrade;
select * from emp;

13题建表代码

create table c(
	cno int(2) primary key auto_increment,
	cname varchar(32),
	cteacher varchar(16)
	);
	
create table s(
	sno int(2) primary key auto_increment,
	sname varchar(16)
	);

create table sc(
	sno int(2),
	cno int(2),
	scgrade int(3),
	primary key(sno,cno)
	);
	
insert into c(cname,cteacher)values('语文','张老师');
insert into c(cname,cteacher)values('政治','王老师');
insert into c(cname,cteacher)values('英语','李老师');
insert into c(cname,cteacher)values('数学','赵老师');
insert into c(cname,cteacher)values('物理','黎明');

insert into s(sname)values('学生1');
insert into s(sname)values('学生2');
insert into s(sname)values('学生3');
insert into s(sname)values('学生4');

insert into sc(sno,cno,scgrade)values(1,1,40);
insert into sc(sno,cno,scgrade)values(1,2,30);
insert into sc(sno,cno,scgrade)values(1,3,20);
insert into sc(sno,cno,scgrade)values(1,4,80);
insert into sc(sno,cno,scgrade)values(1,5,60);
insert into sc(sno,cno,scgrade)values(2,1,60);
insert into sc(sno,cno,scgrade)values(2,2,60);
insert into sc(sno,cno,scgrade)values(2,3,60);
insert into sc(sno,cno,scgrade)values(2,4,60);
insert into sc(sno,cno,scgrade)values(2,5,40);
insert into sc(sno,cno,scgrade)values(3,1,60);
insert into sc(sno,cno,scgrade)values(3,2,80);