Mysql 笔记2 emp dept HRs

-- 注意事项
-- 1.给数据库和表起名字时尽量选择全小写
-- 2.作为筛选条件的字符串是否区分大小写看设置的校对规则utf8_bin 区分
drop database if exists hrs;
create database hrs default charset utf8 collate utf8_general_ci;use hrs;
drop table if exists tb_emp;
drop table if exists tb_dept;create table tb_dept(
dpno int not null comment '部门编号',
dpname varchar(20) not null comment '部门名称',
dploc varchar(20) not null comment '地址',
primary key (dpno)
);
insert into tb_dept values
(1001,'会计部','北京'),
(1002,'研发部','成都'),
(1003,'销售部','重庆'),
(1004,'运维部','深圳');create table tb_emp(
empno int not null comment '工号',
empname varchar(20) not null comment '姓名',
job varchar(20) not null comment '员工职位',
empmgr int null comment '主管姓名',
sal int not null comment '薪资',
comm int comment '每月补贴',
empsex bool default 1 comment '性别',
dpno int not null comment '所在部门编号',
primary key(empno)
);
-- 自参照完整性
-- 级联更新（delete cascade on update cascade）foreign key dpno 删除和更新都会在tb_emp中删除更新
-- alter table tb_emp add constraint fk_emp_dp foreign key (dpno) references tb_dept(dpno) on delete cascade on update cascade;
alter table tb_emp add constraint fk_emp_dp foreign key (dpno) references tb_dept(dpno) on delete restrict on update restrict;-- 默认不让删除
-- alter table tb_emp add constraint fk_emp_dp foreign key (dpno) references tb_dept(dpno) on delete set null on update restrict;-- 删除后dpno变成null
alter table tb_emp drop foreign key fk_emp_dp;
alter table tb_emp add constraint fk_em_mgrreign foreign key (empmgr) references tb_emp(empno);
alter table tb_emp drop foreign key fk_em_mgrreign;-- 删除自参照约束insert into tb_emp values
(7800,'张三丰','总裁',null,90000,800,1,1001),
(2056,'乔峰','分析师',7800,90000,700,1,1004),
(2567,'杨逍','架构师',7800,80000,800,0,1002),
(2008,'杨不悔','大会计',7800,90000,500,0,1001),
(2643,'周芷若','销售经理',7800,8000,7800,0,1003),
(1352,'杨过','程序员',2567,9000,1000,1,1002),
(1341,'赵敏','会计1',2008,9000,500,0,1001),
(1654,'小昭','会计2',2008,8000,800,0,1001),
(1987,'张翠山','程序员2',2567,8000,600,0,1002),
(1563,'殷素素','运维员1',2056,8000,800,0,1004),
(1744,'叶青梅','销售员1',2643,8000,800,0,1003);

use hrs;
-- alter table tb_emp add column hiredate date ;after mgr/first
-- select * from tb_emp;
-- 查询薪资最高的员工；
-- select empname from tb_emp  where sal=(select max(sal) from tb_emp);
-- 查询员工的姓名和年薪（月薪+补贴）*12
-- select empname, (sal+comm)*12 from tb_emp;
-- 查询有员工的部门编号和人数
-- select dpno, count(dpno) from tb_emp group by dpno;
-- 查询所有部门的名称和人数
-- select dpname,total from tb_dept t1 inner join (select dpno, count(dpno) as total 
-- from tb_emp group by dpno) t2 on t1.dpno=t2.dpno ;
-- select dpname,count(empno) from tb_emp t1 inner join tb_dept t2 on t1.dpno=t2.dpno group by t1.dpno;
-- 查询薪资最高的员工（除Boss）的姓名和工资
-- select empname,(sal+comm) from tb_emp where (sal+comm)=(select max(sal+comm) from tb_emp where empmgr is not null) ;
-- 查询薪水超过平均薪水的员工姓名和工资
-- elect empname,(sal+comm)from tb_emp where (sal+comm)>(select avg(sal+comm) from tb_emp);
-- 查询薪水超过所在部门平均薪水的员工姓名、部门编号和工资
-- select empname,t1.dpno,sal,round(avgsal, 2) from tb_emp t1 inner join 
-- (select dpno,avg(sal) as avgsal from tb_emp group by dpno) t2 on t1.dpno=t2.dpno where sal >avgsal;
-- select  dpno, avg(sal+comm) from tb_emp group by dpno
-- select empname,dpno,(sal+comm) from tb_emp t1 where t1.dpno= (select dpno,avg(sal+comm) from tb_emp group by dpno) t2;
-- 查询部门中薪水最高的人的姓名、工资和所在部门名称
-- select dpno, max(sal) as msal from tb_emp group by dpno;
-- select empname,sal from tb_emp inner join (select * from tb_dept t1 inner join (select dpno, max(sal) as msal from tb_emp group by dpno) t2 on t1.dpno=t2.dpno) t3 where tb_emp.dpno=t3.dpno and tb_emp.sal=t3.msal;

-- SELECT e.empno,e.empname,d.dpno,d.dpname FROM tb_emp e inner JOIN tb_dept d ON e.dpno = d.dpno
-- WHERE (e.dpno, e.sal) IN (SELECT dpno, MAX(sal) FROM tb_emp GROUP BY dpno);
-- SELECT e.empno,e.empname,d.dpno,d.dpname FROM tb_emp e inner JOIN tb_dept d ON e.dpno = d.dpno
-- WHERE exists (select dpno, MAX(sal) FROM tb_emp GROUP BY dpno);
-- select t1.empname,t1.sal,t2.dpname from tb_emp t1 inner join tb_dpt t2 on t1.dpno=t2.dpno where (t1.dpno,t1.sal) in (select dpno,max(sal) from tb_emp group by dpno);
-- select empname,sal, dpname from tb_emp t1 inner join (select dpno, max(sal) as msal from tb_emp group by dpno) t2 on t1.sal=t2.msal and t1.dpno=t2.dpno inner join tb_dpt t3 on t1.dpno=t3.dpno;
-- 查询主管的姓名和职称
-- select empname,job from tb_emp where empno in (select distinct empmgr from tb_emp where empmgr is not null);
-- select empname,job from tb_emp t1 where exists(select 'x' from tb_emp t2 where t1.empno=t2.empmgr);

-- select 
-- 查月薪排名4~6的员工姓名和工资
-- select empname,sal from tb_emp order by sal desc limit 3,3;
-- select empname,sal from tb_emp order by sal desc limit 3 offset 3;
-- select 'x' from tb_temp t2 where t1.empno=t2.empmgr
-- select 'x';
-- dual 伪表不存在的表
-- select 'x' from dual;
-- select 'x' from tb_emp;
-- select 'x' from tb_emp where empno=7800;
-- select 'x' from tb_emp where empno=7900;
-- select 'x' from dual where exists(select empno from tb_emp where empname like '张%')
-- select 'x' from dual where exists(select empno from tb_emp where empname like '赵%')
-- explain 生成执行计划
-- select empno,empname from tb_emp where empno=7800;
-- explain select empno,empname from tb_emp where empname='张三丰';
-- explain select empno,empname from tb_emp where empname like '张%';
-- 索引(index)
-- 索引可以加速查询所以应该在用于查询筛选条件的列上建立索引.
-- 索引会使用额外的存储空间而且会让增删改查变得更慢（因为要更新索引）
-- 所以不能滥用索引
-- create index idx_emp_empname on tb_emp(empname);
-- explain select empno,empname from tb_emp where empname='张三丰';
-- drop index idx_emp_emname on tb_emp;

-- 视图:查询的快照（简化查询操作）
-- 通过视图可以将用户访问权限限制到某些指定的列上
-- 查询所有部门的名称和人数
-- create view vw_dept_emp_count as
-- select dpname ,total from tb_dept t1 left join (select dpno,count(dpno) as total from tb_emp group by dpno) t2 on t1.dpno=t2.dpno;
-- select * from vw_dept_emp_count;
-- drop view vm_dept_emp_count;
-- select * from vw_dept_emp_count
-- 重新定义界定符为$$
-- delimiter $$
-- 创建存储过程
-- deptno 输入参数， out avgsal 输出参数
-- create procedure sp_dept_avg_sal(deptno int,out avgsal float)
-- begin
    -- select avg(sal) into avgsal from tb_emp where dpno=deptno;
-- end$$
-- 将界定符还原回；
-- delimiter ;
-- call sp_dept_avg_sal(1002,@a);
-- select @a from dual;
-- select avg(sal) from tb_emp where dpno=1002
-- 触发器：在执行增删改操作时可以触发其它的级联操作，但是有可能导致“锁表”现象，实际开发中尽量避免使用触发器
/*update tb_dept set dpno=1005 where dpno=1002;
delimiter $$
create trigger tr_dept_update
after update on tb_dept for each row
begin 
    update tb_emp set dpno=new.dpno where dpno=old.dpno;
end $$
delimiter ;*/

-- update tb_dept set dpno=1005 where dpno=1001

-- DCL:授予权限（grant to） 和召回权限（revoke from）
-- HelloKitty可以从xx主机登录%：所有主机，localhost本机，IP:IP 地址的主机
-- create user "Hellokitty"@"120.10.20.30"/"%"/"localhost"
-- drop user "HelloKitty"@'%';
-- create user "helloKitty" @"%" identified by '123123';
-- 将数据库中hrs的所有权限给helloKitty
-- grant all privileges on hrs.* to "helloKitty"@'%';
-- revoke insert,delete,update on hrs.* from "helloKitty"@'%';
-- 事务（transaction） --把多个增删改做成一个不可分割的原子性操作
-- 要么全部都做，要么全都不做
-- begin/start transaction 
delete from tb_emp;
begin;
delete from tb_emp;
-- commit提交（事务中的所有操作都生效）
commit;
-- 回滚（事务中的所有操作都撤销）
rollback;

在MySQL会话中执行SET SQL_SAFE_UPDATES = 0;命令来关闭安全更新模式。注意，这仅影响当前会话，并且在会话结束时将恢复为之前的设置
SET SQL_SAFE_UPDATES = 0;