MySQL学习记录DAY9-复习深化

复习整理

>>>B树

 特点：一个节点可以存储超过两个元素，可以拥有超过两个子节点

            每个节点的所有子树高度一致

            比较矮

性质：阶的意思是一个节点最多拥有子节点的个数

        m阶B树的性质（m>=2）

        假设一个节点存储的元素个数为x

        根节点：1<=x<=m-1

        非根节点：(m/2)(向上取整)-1<=x<=m-1

        如果有子节点，子节点个数y=x+1

        所以根节点：2<=x<=m

        非根节点：(m/2)(向上取整)<=y<=m

        数据库一般用200-300阶的B树

1、面试题1

有一个包含员工及其薪水的表格，但是有些记录已经过时，且包含过时的薪水信息。假设工资每年增加，求每个员工的当前工资。输出员工的ID、名字、姓氏、部门id和当前工资。按员工ID升序排列。

方法1：

思路：列出要输出的字段

select id,first_name,last_name,department_id,salary

from ms_employee_salary

员工的当前工资=所有该员工薪资记录的最大值

直接在薪资前加max（）不可取

可以通过group by函数取最大值

select id,first_name,last_name,department_id,max(salary) as max_salary

from ms_employee_salary

group by id,first_name,last_name,department_id

order by id asc;

方法2：

用id划分区间，从中选出每位员工当前薪资的数据

select id,first_name,last_name,department_id,max(salary) over(partition by id) as current_salary

from ms_employee_salary

order by id

此时数据若有重复，用distinct（id）进行数据去重

select distinct(id),first_name,last_name,department_id,max(salary) over(partition by id) as current_salary

from ms_employee_salary

order by id

知识点复习：1、group by的运用 

                        ⚠：在一条select语句当中，如果有group by语句的话，select后面只能跟：参加分组的字段，以及分组函数，其他一律不能跟

                      2、partition by的运用 max(salary) over(partition by id) as current_salary

                      多行处理函数（字段名） over（partition by 字段名（unique约束的字段））

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

先算出每个部门的最高薪水

select deptno,max(salary) as max_sal  from emp group by deptno;

把上述查询数据作为一张临时表

用最高薪水和部门编号连接

with t as (select deptno,max(salary) as max_sal  from emp group by deptno)

select e.ename,e.deptno,t.max_sal from emp e join t on e.deptno=t.deptno,t.max_sal=e.salary order by deptno asc;

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

先求部门的平均薪水

select deptno,avg(salary) as avg_sal from emp group by deptno order by deptno;

把上述查询作为一张临时表

with t as (select deptno,avg(salary) as avg_sal from emp group by deptno order by deptno)

select e.ename,e.salary from emp e join t on e.deptno =t.deptno and e.salary > t.avg_sal;

4、取得部门中平均薪水的等级

先求各部门的平均薪水

select deptno,avg(salary) as avg_sal from emp group by deptno;

然后内连接

with t as (select deptno,avg(salary) as avg_sal from emp group by deptno)
select t.deptno,s.grade from t join salgrade s on t.avg_sal between s.lowsal and s.highsal order by deptno;

5、取得部门中平均的薪水等级

先求所有员工的薪水等级

select e.ename,e.salary,e.deptno,s.grade from emp e join salgrade s on e.salary between s.lowsal and s.highsal order by e.deptno;

再通过部门分组对薪水等级进行平均

with t as (select e.ename,e.salary,e.deptno,s.grade from emp e join salgrade s on e.salary between s.lowsal and s.highsal order by e.deptno)

select deptno,avg(grade) as avg_grade from t group by deptno order by avg_grade;

但是使用临时表反而会更加复杂化

select e.deptno,avg(s.grade) from emp e join salgrade s on e.salary between s.lowsal and s.highsal group by deptno;

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

方法1：使用limit

select ename,salary from emp order by salary desc limit 0,1;
方法2：表的自连接

select a.salary from emp a join emp b on a.salary < b.salary;

该语句通过自己同一列数字的相互比较可以找出所有比最大值小的数

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

7、取得平均薪水最高的部门的部门编号（给出两种解决方案）

方法1：

思路：先求出每个部门平均薪水

select deptno, avg(salary) as avg_salary from emp group by deptno order by avg_salary desc;

再取出最高的部门编号

select deptno from emp group by deptno order by avg(salary) desc limit 1;

方法2：

思路：求出每个部门平均薪水中的最大值

select deptno, avg(salary) as avg_salary from emp group by deptno;

select max(t.avg_salary) from (select deptno, avg(salary) as avg_salary from emp group by deptno) t;

然后再用having筛选

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

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

思路：先取得平均薪水最高的部门

select deptno, avg(salary) as avg_salary from emp group by deptno order by avg_salary desc limit 1;

和部门表进行内连接

With t as (select deptno, avg(salary) as avg_salary from emp group by deptno order by avg_salary desc limit 1)

select t.avg_salary,d.dname1 from t join dept d on t.deptno=d.depno;

思路2：先连接再分组

select d.dname1,avg(e.salary) as avg_salary from emp e join dept d on e.deptno = d.depno group by d.dname1 order by avg_salary desc limit 1;

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

思路：按照部门名称分组，找出每个部门的平均薪水

select d.dname1,avg(e.salary) as avg_sal from emp e join dept d on e.deptno=d.depno group by d.depno order by avg_sal;

再匹配薪水等级

With t as (select d.dname1,avg(e.salary) as avg_sal from emp e join dept d on e.deptno=d.depno group by d.depno order by avg_sal)

select t.dname1,s.grade from t join salgrade s on t.avg_sal between s.lowsal and s.highsal where s.grade = (select grade from salgrade where (select avg(salary) as avg_sal from emp group by deptno order by avg_sal asc limit 1) between lowsal and highsal);

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

思路：1、找出普通员工及其最高薪水

select empno,ename,salary from emp where empno not in (select distinct mgr from emp where mgr is not null) order by salary desc limit 1;

或者

select max(salary) as max_sal from emp where empno not in (select distinct mgr from emp where mgr is not null)；

2、已得出普通员工的最高薪水，那么比葡萄员工薪水还要高的肯定是领导

select ename,salary from emp where salary>(select max(salary) as max_sal from emp where empno not in (select distinct mgr from emp where mgr is not null));

11、取得每个薪水等级有多少员工

思路：1、查询每位员工的薪水等级

select e.ename,e.salary,s.grade from emp e join salgrade s on e.salary between s.lowsal and s.highsal;

2、count函数

select s.grade,count(*) from emp e join salgrade s on e.salary between s.lowsal and s.highsal group by s.grade;

12、面试题2

 

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

select a.empno,a.ename,d.dname1 from emp a join emp b on a.mgr=b.empno and a.hiredate<b.hiredate join dept d on a.deptno = d.deptno;

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

select d.depno,d.dname1,e.* from dept d left join emp e on d.depno=e.deptno;

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

select deptno,count(*) from emp group by deptno having count(*)>=5;

16、列出薪金比smith多的所有员工信息

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

17、列出所有职位是’clerk‘的员工姓名及其部门名称、部门人数

思路：1、先找出相应职位的员工姓名、部门名称和部门编号

select ename,job from emp where job='clerk';

select e.ename,e.job,d.dname1,e.deptno from emp e join dept d on e.deptno=d.depno where job='clerk'; 

           2、创建部门编号和部门人数的临时表

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

           3、进行表连接

with t1 as (select e.ename,e.job,d.dname1,e.deptno from emp e join dept d on e.deptno=d.depno where job='clerk'),

t2 as (select deptno,count(*) as count_people from emp group by deptno)

select t1.*,t2.count_people from t1 join t2 on t1.deptno = t2.deptno;

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

思路：1、先取出最低薪资大于1500的工作的数据

select job, min(salary) as minsal from emp group by job having minsal>1500;

           2、count

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

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

select e.ename,d.dname1,t.ename,s.grade from emp e join dept d on e.deptno =d.depno left join emp t on e.mgr=t.empno join salgrade s on e.salary between s.lowsal and s.highsal where e.salary > (select avg(salary) from emp);

20、列出与’scott‘从事相同工作的所有员工及部门名称

select e.ename,d.dname1 from emp e join dept d on e.deptno=d.depno where e.job=(select job from emp where ename='scott') and e.ename not in ('scott');

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

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

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

select d.depno,count(e.ename),ifnull(avg(e.salary),0)

from emp e right join dept d on e.deptno=d.depno group by d.depno;

平均服务期限如何求？？即两个日期的年差

使用timestampdiff（间隔类型，前日期，后日期）

间隔类型有：second minute hour day week month quarter year

select d.depno,count(e.ename),ifnull(avg(e.salary),0),ifnull(timestampdiff(year,e.hiredate,now()),0)

from emp e right join dept d on e.deptno=d.depno group by d.depno;