-- 关系型数据库如何保证数据的完整性
-- 1. 实体完整性 - 每一条数据都是独一无二的没有冗余数据 - 主键/唯一索引
-- 2.参照完整性/引用完整性 -外键 
-- 3.领域完整性/域完整性 - 没有无效的数据 - 数据类型/非空约束/默认值约束/检查约束


-- 创建人力资源管理系统数据库
drop database if exists HRS;
create database HRS default charset utf8;

-- 切换数据库上下文环境
use HRS;

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

-- 添加部门记录
insert into TbDept values (10, '会计部', '北京');
insert into TbDept values (20, '研发部', '成都');
insert into TbDept values (30, '销售部', '重庆');
insert into TbDept values (40, '运维部', '深圳');

-- 创建员工表
create table TbEmp
(
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 comment '所在部门编号',
primary key (eno)
);

-- 添加外键约束
alter table TbEmp add constraint fk_dno foreign key (dno) references TbDept (dno);

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

-- 查询薪资最高的员工姓名和工资
select ename, sal from tbemp where sal=(select max(sal) from tbemp);
-- 查询员工的姓名和年薪((月薪+补贴)*12)
select ename, if(comm, sal*12+comm*12, sal*12) from tbemp;
select ename, (sal+ifnull(comm, 0))*12 from tbemp;
-- 查询有员工的部门的编号和人数
select dno, count(dno) from tbemp group by dno;
-- 查询所有部门的名称和人数(左外连接)
select dname, ifnull(countdno,0) from tbdept t1 left outer join 
(select dno, count(dno) as countdno from tbemp group by dno) t2
on t1.dno=t2.dno;
-- 查询薪资最高的员工(Boss除外)的姓名和工资
select ename, max(sal) from tbemp where sal=(select max(sal) from tbemp where mgr is not null);

-- 查询薪水超过平均薪水的员工的姓名和工资
select ename, if(comm,sal+comm,sal) from tbemp where
if(comm, sal+comm, sal)>(select avg(if(comm,sal+comm,sal)) from tbemp);

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

-- 查询部门中薪水最高的人姓名、工资和所在部门名称
select ename, sal ,dname from tbdept t3 inner join 
(select ename,sal ,t1.dno from tbemp t1 inner join
(select dno, max(sal) as maxsal from tbemp group by dno) t2
on t1.dno = t2.dno where sal=maxsal) t4 on t3.dno = t4.dno;
-- 查询主管的姓名和职位
select ename, job from tbemp where eno in
(select distinct mgr from tbemp where mgr is not null); 

-- 要用去重或者集合运算的时候可以用exists 或者not exists
select ename, job from tbemp t1 where exists
(select 'x' from tbemp t2 where t1.eno=t2.mgr)
-- 查询不是主管的姓名和职位
select ename, job from tbemp t1 where not exists
(select 'x' from tbemp t2 where t1.eno=t2.mgr)
-- 查询薪资排名4~6名的员工姓名和工资
select ename, if(comm, sal+comm, sal) from tbemp
order by if(comm, sal+comm, sal) desc limit 3 offset 3;



-- 创建视图

create view vw_dept_count as
select dname, ifnull(countdno,0) from tbdept t1 left outer join 
(select dno, count(dno) as countdno from tbemp group by dno) t2
on t1.dno=t2.dno;

select * from vw_dept_count;

-- 删除视图
drop view vw_dept_count;

-- 创建索引
create index idx_emp_ename on tbemp (ename);
-- create index idx_emp_ename on tbemp (ename(2));
explain select * from tbemp where ename='王二娃';

-- 删除索引
alter table tbemp drop index idx_emp_ename;


-- 注意：表的索引相当于一本书的目录，他可以加速查询
-- 但是索引会让增删改变得更慢 因为增删改数据时索引也需要更新
-- 所以索引要建在经常被用作筛选条件的列上