Mysql学习03

– 查询员工的姓名和年薪((月薪+补贴)*13)（处理空值）
select ename, (sal+ifnull(comm,0))*13 as annsal
from tb_emp order by annsal desc;

– 查询有员工的部门的编号和人数
select dno, count(eno) from tb_emp group by dno with rollup;

– 查询所有部门的名称和人数
select dname, ifnull(total,0) from tb_dept t1 left join
(select dno, count(eno) as total from tb_emp group by dno) t2
on t1.dno=t2.dno;

– 查询月薪最高的员工(Boss除外)的姓名和月薪
select ename, sal from tb_emp
where sal=(
select max(sal) from tb_emp where mgr is not null
);

– 查询薪水超过平均薪水的员工的姓名和月薪
select @a:=(select avg(sal) from tb_emp);

select @a;

select ename, sal, round(sal-@a,2) from tb_emp
where sal>@a;

– 查询薪水超过其所在部门平均月薪的员工的姓名、部门编号和月薪
select ename, t1.dno, sal from tb_emp t1 inner join
(select dno, avg(sal) as avgsal from tb_emp group by dno) t2
on t1.dno=t2.dno where sal>avgsal;

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

– 查询主管的姓名和职位
– distinct - 耗时操作
– in / not in - 耗时操作（集合中元素多了以后性能急剧下降）
– 建议：使用exits/ not exists取代去重和集合运算
select ename, job from tb_emp where eno in (
select distinct mgr from tb_emp where mgr is not null
);

Hao（jackfrued） 2020/11/18 11:16:13
– 查询主管的姓名和职位
– distinct - 耗时操作
– in / not in - 耗时操作（集合中元素多了以后性能急剧下降）
– 建议：使用exists/ not exists取代去重和集合运算
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 t1
where exists (select ‘x’ from tb_emp t2 where t1.eno=t2.mgr);

– 查询月薪排名4~6名的员工排名、姓名和月薪
select rn, ename, sal from
(select @no:=@no+1 as rn, ename, sal from tb_emp
order by sal desc) t1, (select @no:=0) t2
limit 3 offset 3;

select @no:=@no+1 as rn, ename, sal
from tb_emp, (select @no:=3) t2
order by sal desc limit 3,3;

Hao（jackfrued） 2020/11/18 11:52:00
show variables like ‘%password%’;

– 如果需要使用弱口令可以先修改两个全局变量
– 注意：商业项目中绝不应该为数据库用户设置弱口令
set global validate_password_policy=0;
set global validate_password_length=6;

– 创建用户
create user ‘wangdachui’@’%’ identified by ‘123456’;

– 授予权限
grant select on hrs.tb_emp to ‘wangdachui’@’%’;

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

– 将所有数据库所有对象的所有权限授予’wangdachui’@’%’
grant all privileges on . to ‘wangdachui’@’%’ with grant option;

– 召回权限
revoke all privileges on . from ‘wangdachui’@’%’;

revoke insert on hrs.* from ‘wangdachui’@’%’;

– 删除用户
drop user ‘wangdachui’@’%’;

– 修改用户
alter user ‘wangdachui’@’%’ identified by ‘654321’;