MySQL34道例题

MySQL34道例题

MySQL34道例题

老杜mysql课后34题，自己总结

sql脚本（练习的表之内的文件）

使用：

1、创建一个text的文本文件，将下面的SQL语句复制到文本文件中

2、将后缀改为.sql

3、在数据库中创建一个新的数据库（create database 数据库名），再使用数据库（use 数据库名）

4、source XX.sql 导入脚本文本文件

DROP TABLE IF EXISTS EMP;
DROP TABLE IF EXISTS DEPT;
DROP TABLE IF EXISTS SALGRADE;
 
CREATE TABLE DEPT
       (DEPTNO int(2) not null ,
    DNAME VARCHAR(14) ,
    LOC VARCHAR(13),
    primary key (DEPTNO)
    );
CREATE TABLE EMP
       (EMPNO int(4)  not null ,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR INT(4),
    HIREDATE DATE  DEFAULT NULL,
    SAL DOUBLE(7,2),
    COMM DOUBLE(7,2),
    primary key (EMPNO),
    DEPTNO INT(2) 
    )
    ;
 
CREATE TABLE SALGRADE
      ( GRADE INT,
    LOSAL INT,
    HISAL INT );
 
 
 
 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
10, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
20, 'RESEARCH', 'DALLAS'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
30, 'SALES', 'CHICAGO'); 
INSERT INTO DEPT ( DEPTNO, DNAME, LOC ) VALUES ( 
40, 'OPERATIONS', 'BOSTON'); 
commit;
  
INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
7369, 'SMITH', '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, 0, 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); 
commit;
  
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, 9999); 
commit;

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

第一步：找到每一个部门的最大值，因此需要根据部门编号进行分组，再筛选出最大值

第二步：将上面这个做为一张临时表

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

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

思路：

第一步：找出每一个部门的平均工资

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

第二步：将表进行连续查询

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

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

第一步：算出平均

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

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

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

错位思路：我把第一步做为了临时表，并进行连接

第一步：每一个部门的成员是上面等级

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

第二步：根据上表进行数据筛选

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

正确思路：

第一步：每一个部门的成员是上面等级

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

第二步：根据部门进行分组

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

4、不准用组函数（Max），取得最高薪酬

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

5、取得平均薪水最高的部门的部门编号（至少两种）

5.1、方案一

第一步：先根据部门进行分组，再计算出平均工资

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

第二步：判断最高平均工资(错误)

select deptno,max(avg(sal)) maxsal from emp group by deptno;

第二步：利用limit来获取降序后第一个

select deptno,avg(sal) maxsal from emp group by deptno order by maxsal desc limit 1;

5.2方案二：

第一步：筛选出最高工资

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

第二步：

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

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

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

方法一：

第一步：求出平均薪水的部门及其名字

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

第二步：找出平均薪水的部门名称以及薪资等级

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

8、取得比普通员工(员工编号没有在MGR字段上)的最高薪酬还要高的领导人姓名

第一步：筛选出领导的编号

select  distinct mgr from emp;

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的员工（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));

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 ename,sal,s.grade from emp join salgrade s on sal between losal and hisal;

第二步： 统计每一个等级有多少人，根据分组

select s.grade,count(*) from emp join salgrade s on sal between losal and hisal
group by s.grade;

13、 （面试题）有3个表 s（学生表）、c（课堂表）、sc（学生选课表）

S(sno,sname)===》（学号，姓名）
sc（sno，cno，scgrade）===》（学号，课程号，成绩）
c(sno,cname,cteacher)===>(学号、课程名、老师) 

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

使用自连接

select e.ename "员工",s.ename  "领导" from emp e join emp s on e.empno= s.mgr;

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

select  from emp e 
join emp s
on e.hiredate < s.hiredate and e.mgr = b.e;

思路：自连接

第一步：找到员工的直接上级，以及员工的编号

select
e.empno,e.ename
from 
emp e
join
emp t
on
e.mgr = t.empno and e.hiredate < t.hiredate;

第二步：将上面表与dept表进行连接

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

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

注意：不能使用内连接了，需要使用外连接

错误：

• 左连接，会将左边的表添加上，主要与左边的表进行连接和比较

select 
a.*,b.dname
from
emp a
left join
dept b
on
a.deptno=b.deptno;

正确：

select 
a.*,b.dname
from
emp a
right join
dept b
on
a.deptno=b.deptno;

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

意思：员工数量在5个以上的部门 (需要包括5个)

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

注意：count和sum的区别

18、列出薪金比“SMITH”多的所有员工信息

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

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

第一步：获取clerk的姓名和部门

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

第二步：统计部门人数

select
a.deptno,count(a.deptno)
from
emp a
group by 
a.deptno;

第三步：

select
e.ename,d.dname,t.numPeople
from
emp e
join
dept d
on
e.deptno = d.deptno and e.job='clerk'
join
(select a.deptno,count(a.deptno) numPeople from emp a group by a.deptno) t
on
t.deptno=d.deptno 
;

20、列出最低薪金大于1500的各种工作及从事此工工作的全部人数

第一步：找出薪金低于1500的工作

select
e.ename,e.job
from
emp e
where (sal+ifnull(comm,0))>1500;

第二步：统计各行业的人数

select 
count(job) jobNum,job
from
emp t
group by
job;

第三步：联合查询

select
e.ename,e.job,t.jobNUm
from
emp e
join
(select count(job) jobNum,job from emp  group by job) t
on
e.job = t.job
where (e.sal+ifnull(e.comm,0))>1500
order by t.jobNum desc;

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

错误：数据不完全

select
e.ename,e.job,e.deptno
from
emp e
where e.job like '%sale%';

正确方法：
第一步：获取到销售部编号

select
deptno
from 
dept
where dname like "%sale%";

第二步：查询数据

select
ename,job
from
emp
where deptno = (select deptno from dept where dname like "%sale%");

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

第一步：高于公司平均工资的所有员工

select ename,sal from emp where sal > (select avg(sal) from emp);

第二步：上级和员工的工作等级

select 
e.ename,t.ename mgr,s.grade
from
emp e
join 
salgrade s
on
e.sal between s.losal and s.hisal
left join
emp t
on
e.mgr =t.empno
;

第三步：将上面两个表进行内连接

select 
e.ename,t.ename mgr,s.grade
from
emp e
join 
salgrade s
on
e.sal between s.losal and s.hisal
left join
emp t
on
e.mgr =t.empno
where e.sal > (select avg(sal) from emp);

注意：这点要使用外连接，不要使用内连接

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

第一步：获取scott的工作，并查询所有相同的员工

select
e.ename,e.job
from
emp e
where job =(select job from emp where ename='scott');

第二步：将表进行联合

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

24、列出薪金等于部门编号为30中薪金的其他员工的姓名和薪金

第一步：求出部门30的员工的薪金

select
e.ename,e.sal
from
emp e
where e.deptno=30;

第二步：求出其他员工等于部门30的薪金

select
t.ename,t.sal
from
emp t
join 
(select e.ename,e.sal as sal  from emp e where e.deptno=30) e
on
e.sal = t.sal and t.deptno !=30;

25、列出薪金高于在部门编号为30的所有员工姓名、薪金和部门名称

第一步：高于部门编号为30的所有员工姓名、薪金

select
distinct(e.ename),e.sal,e.deptno
from
emp e
join
(select sal,deptno from emp where deptno=30) t
on
e.sal > t.sal and e.deptno !=30;

第二步：高于薪金的员工

select
distinct(e.ename),e.sal,e.deptno,d.dname
from
emp e
join
(select sal,deptno from emp where deptno=30) t
on
e.sal > t.sal and e.deptno !=30
join 
dept d
on
d.deptno=e.deptno;

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

服务期限:当前年-入职年

• TimeStampDiff(间隔类型，前一个日期，后一个日期)
  • 间隔类型：frac_second 毫秒 、second 秒 、minute 分钟、hour 小时、day 天、week 星期
  • TimeStampDiff(year,hiredate,now())

方法一：

select
count(e.ename) 员工数量,avg(e.sal) 平均工资,avg(date_format(now(),'%Y')-year(hiredate)) 平均服务期限
from 
emp e
group by
deptno;

方法二：

select
count(e.ename) 员工数量,avg(e.sal) 平均工资,avg(TimeStampDiff(year,hiredate,now())) 平均服务期限
from 
emp e
group by
deptno;

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

第一步：获取所有员工的姓名、姓名

select
e.ename,e.sal
from
emp e;

第二步：将表进行连接

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

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

第一步：部门详细信息：部门编号、部门名称、manager 管理者

select
d.deptno,d.dname,t.ename manager
from 
dept d
left join
(select ename,job,deptno from emp where job='manager') t
on
t.deptno = d.deptno;

第二步：获取到人数

select
count(deptno),deptno
from
emp 
group by
deptno;

第三步：进行联合查询

select
d.deptno,d.dname,t.ename manager,e.num
from 
dept d
left join
(select ename,job,deptno from emp where job='manager') t
on
t.deptno = d.deptno
left join
(select count(deptno) num,deptno from emp group by deptno) e
on
e.deptno = d.deptno;

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

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

select
min(sal),job
from
emp 
group by
job;

第二步：找到最低工资的员工

select
e.ename,e.job,d.sal
from
emp e
join
(select min(sal) sal,job from emp group by job) d
on
d.sal = e.sal and d.job = e.job
order by job;

30、列出各部门的Manager的最低薪金

select
sal,deptno
from
emp
where job='manager'

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

select
ename,(sal+ifnull(comm,0)) sal
from
emp
order by sal ;

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

select
e.ename 员工,d.ename 领导
from
emp e
join
emp d
on
e.mgr=d.ename and d.sal>3000;

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

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

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

select
ename,(case sal when TimeStampDiff(year,hiredate,now())>30 then sal*(1.1) else sal end) newSal,deptno
from
emp;

注意：case 字段 when 条件1 then 执行 else 执行 end