数据库day04

-- 如果存在就删除名为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);

1. 查询月薪最高的员工姓名和月薪

-- 面试可能要求用3中以上方法写
-- 方法1：排序 + limit（有bug，性能差，不推荐）
  select ename,
	     sal
    from tb_emp
order by sal desc
   limit 1;

-- 方法2：嵌套查询
select ename,
       sal
  from tb_emp
  where sal = ( select max(sal) from tb_emp);

-- 1. 关系运算：封闭 - 关系运算的结果仍然是关系
-- a. 投影（select）
-- b. 别名（as）
-- c. 选择（where）
-- d. 交集（intersect）/ 并集（union）/ 差集（except）
-- e. 连接（join）

-- 2. 谓词 --> true / false / unknown
-- in / not in
-- exists / not exists
-- is pull / is not pull
-- all / any / some

-- 方法3：all 谓词
select ename,
	   sal
  from tb_emp
 where sal >= all(select sal from tb_emp);

-- 查常量
select 'x' from tb_emp;
select 'x' from dual;

-- 方法4：exists 谓词
select ename,
       sal
  from tb_emp t1
 where not exists (select 'x' 
					  from tb_emp t2 
					 where t2.sal > t1.sal);

-- 方法5：计数法（可推广到前几名）
select ename,
       sal
  from tb_emp t1
 where (select count(*) 
		  from tb_emp t2 
		 where t2.sal > t1.sal) = 0;

select concat('a', 'xxx') from dual;

2. 查询员工的姓名和年薪(年薪=(sal+comm)*13)

-- 推荐用coalesce()
  select ename as 姓名,
		 (sal + coalesce(comm, 0))*13 as 年薪
    from tb_emp
order by 年薪 desc;

select ename as 姓名,
       (sal + ifnull(comm, 0))*13 as 年薪
  from tb_emp;

3. 查询有员工的部门的编号和人数

 select dno,
         count(*)
    from tb_emp
group by dno;

4. 查询所有部门的名称和人数

  select dname,
         count(eno)
    from tb_emp right outer join tb_dept
      on tb_emp.dno = tb_dept.dno
group by tb_dept.dno;

  select dname,
		 coalesce(total, 0) as total
	from tb_dept t1 left join (select dno,
									  count(*) as total
								 from tb_emp
							 group by dno) t2
	  on t1.dno = t2.dno;

5. 查询月薪超过平均月薪的员工的姓名和月薪

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

6. 查询月薪超过其所在部门平均月薪的员工的姓名、部门编号和月薪

select ename, 
       dno, 
       sal 
  from tb_emp natural join (   select dno, 
							   	  	   avg(sal) as avg_sal 
							      from tb_emp 
							  group by dno) t
 where sal > avg_sal;


select ename, 
       t1.dno, 
       sal 
  from tb_emp t1 inner join (   select dno, 
							   	  	   avg(sal) as avg_sal 
							      from tb_emp 
							  group by dno) t2
    on t1.dno = t2.dno 
 where sal > avg_sal;

7. 查询部门中月薪最高的人姓名、月薪和所在部门名称

select ename, 
       dno, 
       sal 
  from tb_emp natural join tb_dept natural join (   select dno, 
														   max(sal) as max_sal 
							                          from tb_emp 
												  group by dno) t
 where sal = max_sal;


select ename,
       sal,
	   dname
  from tb_emp t1 inner join tb_dept t2 
				 inner join ( select dno,
									 max(sal) as max_sal
							    from tb_emp
						    group by dno) t3
	on t1.dno = t2.dno
   and t2.dno = t3.dno
 where sal = max_sal;

8. 查询主管的姓名和职位

select ename,
	   job
  from tb_emp t1
 where exists (select 'x' 
				 from tb_emp t2 
				where t1.eno = t2.mgr);

select ename,
	   job
  from tb_emp 
  where eno in (select distinct mgr 
						   from tb_emp
						  where mgr is not null);

select ename,
	   job
  from tb_emp right join (select distinct mgr 
									 from tb_emp
									where mgr is not null) t
    on tb_emp.eno = t.mgr;

9. 查询月薪排名4~6名的员工排名、姓名和月薪

-- MySQL 5.x
select *
  from (select @x := @x + 1 as r,
			   ename,
			   sal
		  from tb_emp, (select @x := 0) t1
	  order by sal desc) t2
 where r between 4 and 6;

-- MySQL 8.x
-- 窗口函数
-- row_number() - 对列排名
-- rank() - 排序 并列之后 按数量排
-- dense_rank() - 排序 并列之后 按排名排

select * 
  from (select ename,
			   sal,
			   row_number() over (order by sal desc) as r1,
			   rank() over (order by sal desc) as r2,
			   dense_rank() over (order by sal desc) as r3
		  from tb_emp) t
 where r2 between 4 and 6;

10. 查询每个部门月薪排前2名的员工姓名、月薪和部门编号

-- MySQL 5.x
  select ename,
	     sal,
         dno
    from tb_emp t1
 where   (select count(*)
		  from tb_emp t2
		 where t1.dno = t2.dno
           and t2.sal > t1.sal) < 2
order by dno asc, sal desc;


-- MySQL 8.x
select ename,
	   sal,
       dno
  from (select ename,
			   sal,
			   dno,
			   rank() over (partition by dno  order by sal desc) as r
		  from tb_emp) t
  where r < 3;

11. DCL - Data Control Language - 数据控制语言

-- grant（授予权限）/ revoke（召回权限）

-- 删除用户
drop user 'wangdachui'@'%';

-- 创建用户（访问账号）
create user 'wangdachui'@'%'identified by 'wang.618.888';

create user 'wangdachui'@'10.7.183.%' identified by 'Dachui.618';

-- 修改用户密码
alter user 'wangdachui'@'%' identified by 'Hello.world!123';

-- 授权
grant select on hrs.* to 'wangdachui'@'%';

grant insert, delete, update on hrs.* to 'wangdachui'@'%';

grant select on school.* to 'wangdachui'@'%';

grant create on *.* to 'wangdachui'@'%';

-- 授予所有权限
grant all privileges on *.* to 'wangdachui'@'%' with grant option;


-- 召回
revoke create on *.* from 'wangdachui'@'%';

revoke select on school.* from 'wangdachui'@'%';

-- 召回所有权限
revoke all privileges on *.* from 'wangdachui'@'%';