09.homework

-- 如果存在就删除名为hrs的数据库
drop database if exists `hrs`;

-- 创建名为hrs的数据库并指定默认的字符集
create database `hrs` default charset utf8mb4;

-- 切换到hrs数据库
use `hrs`;

-- 创建部门表
create table `tb_dept`
(
`dno` int not null comment '编号',
`dname` varchar(10) not null comment '名称',
`dloc` varchar(20) not null comment '所在地',
primary key (`dno`)
);

-- 插入4个部门
insert into `tb_dept` values 
    (10, '会计部', '北京'),
    (20, '研发部', '成都'),
    (30, '销售部', '重庆'),
    (40, '运维部', '深圳');

-- 创建员工表
create table `tb_emp`
(
`eno` int not null comment '员工编号',
`ename` varchar(20) not null comment '员工姓名',
`job` varchar(20) not null comment '员工职位',
`mgr` int comment '主管编号',
`sal` int not null comment '员工月薪',
`comm` int comment '每月补贴',
`dno` int not null comment '所在部门编号',
primary key (`eno`),
constraint `fk_emp_mgr` foreign key (`mgr`) references tb_emp (`eno`),
constraint `fk_emp_dno` foreign key (`dno`) references tb_dept (`dno`)
);

-- 插入14个员工
insert into `tb_emp` values 
    (7800, '张三丰', '总裁', null, 9000, 1200, 20),
    (2056, '乔峰', '分析师', 7800, 5000, 1500, 20),
    (3088, '李莫愁', '设计师', 2056, 3500, 800, 20),
    (3211, '张无忌', '程序员', 2056, 3200, null, 20),
    (3233, '丘处机', '程序员', 2056, 3400, null, 20),
    (3251, '张翠山', '程序员', 2056, 4000, null, 20),
    (5566, '宋远桥', '会计师', 7800, 4000, 1000, 10),
    (5234, '郭靖', '出纳', 5566, 2000, null, 10),
    (3344, '黄蓉', '销售主管', 7800, 3000, 800, 30),
    (1359, '胡一刀', '销售员', 3344, 1800, 200, 30),
    (4466, '苗人凤', '销售员', 3344, 2500, null, 30),
    (3244, '欧阳锋', '程序员', 3088, 3200, null, 20),
    (3577, '杨过', '会计', 5566, 2200, null, 10),
    (3588, '朱九真', '会计', 5566, 2500, null, 10);



-- 查询员工及其主管的姓名
select eno,ename,mgr from tb_emp as t1;
select ename,mgr from tb_emp as t2;
select t2.ename,t1.ename from t2 left join t1 on t2.mgr=t1.eno;
select t2.ename,t1.ename from (select ename,mgr from tb_emp) as t2 left join (select eno,ename,mgr from tb_emp) as t1 on t2.mgr=t1.eno;
-- 方法一：
select * -- t1.ename,t2.ename  
from tb_emp as t1 inner join tb_emp as t2 on t1.mgr=t2.eno;
-- 方法二：
select ename ,(select ename from tb_emp as t2 where t2.eno=t1.mgr) from tb_emp as t1;

-- 查询月薪最高的员工姓名和月薪
select max(sal) from tb_emp;
select ename,sal from tb_emp where sal=(select max(sal) from tb_emp);
-- 方法二：不考虑最高月薪有多个，排序后取第一个
select ename,sal from tb_emp order by sal desc limit 1;
-- 方法三：月薪大于等于所有人的月薪 all
select ename,sal from tb_emp where sal>=all(select sal from tb_emp );
-- 方法四：判断 where 条件 是否为空 
select ename,sal from tb_emp as t1  where not exists(select sal from tb_emp as t2 where t2.sal>t1.sal);

-- 查询员工的姓名和年薪(年薪=(sal+comm)*13)
select ename,(sal+if(comm is null ,0,comm))*13 from tb_emp;
-- 查询部门的编号和人数
select dno,count(*) from tb_emp group by dno;
select tb_dept.dno,count(eno) from tb_dept left join tb_emp on tb_dept.dno=tb_emp.dno group by tb_dept.dno;

-- 查询部门人数超过5个人的部门的编号和人数
select dno,count(*) from tb_emp group by dno having count(*)>5;
-- 查询所有部门的名称和人数
select dname,count(eno) from tb_dept left join tb_emp on tb_dept.dno=tb_emp.dno group by tb_dept.dno;
-- 方法二
select dname,(select count(eno) from tb_emp where tb_dept.dno=tb_emp.dno) from tb_dept;

-- 查询月薪超过平均月薪的员工的姓名和月薪
select ename,sal from tb_emp where sal>(select avg(sal) from tb_emp);
-- 查询月薪超过其所在部门平均月薪的员工的姓名、部门编号和月薪
select ename,tb_emp.dno,sal from tb_emp left join (select dno,avg(sal) as avg1 from tb_emp group by dno) as t1
on t1.dno = tb_emp.dno where sal > avg1;
-- 方法二:
select ename,dno,sal,avg(sal) over(partition by dno) as 平均月薪 from tb_emp;
select ename,dno,sal from (select ename,dno,sal,avg(sal) over(partition by dno) as 平均月薪 from tb_emp) as t1 where sal>平均月薪;
-- 方法三：
select dno,avg(sal) as avg1 from tb_emp group by dno;
select ename,dno,sal
from tb_emp as t1
where sal >(select avg1 from (select avg(sal) as avg1,dno from tb_emp group by dno) as t2 where t2.dno=t1.dno);
-- 方法四：
select ename,dno,sal
from tb_emp as t1
where sal > (select avg(sal) from tb_emp as t2 where t1.dno=t2.dno);

-- 查询部门中月薪最高的人姓名、月薪和所在部门名称
select ename,tb_emp.dno,sal from tb_emp left join (select dno,max(sal) as max1 from tb_emp group by dno) as t1
on t1.dno = tb_emp.dno where sal = max1;

select ename,sal,dname from (select ename,tb_emp.dno,sal from tb_emp left join (select dno,max(sal) as max1 from tb_emp group by dno) as t1
on t1.dno = tb_emp.dno where sal = max1) as t1 natural join tb_dept;
-- 方法二:
select * from tb_emp natural join tb_dept;
select ename,sal,dname,max(sal) over(partition by dno) as 最高月薪 from (select * from tb_emp natural join tb_dept) as t1  ;
select ename,sal,dname from (select ename,sal,dname,max(sal) over(partition by dno) as 最高月薪 from (select * from tb_emp natural join tb_dept) as t1) as t2 where sal=最高月薪;
-- 查询主管的姓名和职位
select mgr from tb_emp group by mgr;
select ename,job from tb_emp where eno = any(select mgr from tb_emp group by mgr);

-- 查询普通员工的姓名和职位
select ename,job from tb_emp where eno not in (select ifnull(mgr,0) from tb_emp group by mgr);


-- 查询主管和普通员工的平均月薪
select avg(sal) from tb_emp where eno = any(select mgr from tb_emp group by mgr);
select avg(sal) from tb_emp where eno not in (select ifnull(mgr,0) from tb_emp group by mgr);

-- 方法二：
select distinct mgr from tb_emp where mgr is not null;
select *,if(eno in (select distinct mgr from tb_emp where mgr is not null),'主管','普通员工') as type from tb_emp;
select type,avg(sal) from (select *,if(eno in (select distinct mgr from tb_emp where mgr is not null),'主管','普通员工') as type from tb_emp) as t1 group by type;