mysql查询（创建人力资源系统数据库）

以下是在mysql 环境下进行操作：

-- 创建人力资源管理系统数据库
drop database if exists HRS;
create database HRS default charset utf8;
-- 切换数据库上下文环境
use HRS;
-- 删除表
drop table if exists TbEmp;
drop table if exists TbDept;
-- 创建部门表
create table TbDept
(
dno int,                                        -- 部门编号
dname varchar(10) not null, -- 部门名称
dloc varchar(20) not null,  -- 部门所在地
primary key (dno)
);
-- 添加部门记录
insert into TbDept values 
(10, '会计部', '北京'),
(20, '研发部', '成都'),
(30, '销售部', '重庆'),
(40, '运维部', '深圳');
-- 创建员工表
create table TbEmp
(
empno int primary key,          -- 员工编号
ename varchar(20) not null, -- 员工姓名
job varchar(20) not null,       -- 员工职位
mgr int,                                        -- 主管编号
sal int not null,                       -- 员工月薪
comm int,                                       -- 每月补贴
dno int not null                        -- 部门编号
);
-- 添加外键约束
alter table TbEmp add constraint fk_emp_dno foreign key (dno) references TbDept(dno);
-- 添加员工记录
insert low_priority into TbEmp 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 ename ,sal from tbemp order by sal desc limit 0,1;  这样写万一最高有几个就不成立
-- 方法1
select ename,sal from tbemp where sal=(
select max(sal) from tbemp
);

-- 查询员工的姓名和年薪((月薪+补贴)*13)
-- 字符串连接用concat函数     运算可以直接做
select concat(job,':',ename) as title,(sal+ifnull(comm,0))*13 as salary from tbemp
order by salary desc;

-- 查询有员工的部门的编号和人数
select dno as '部门编号',count(dno) as '人数' from tbemp
group by dno;


-- 查询所有部门的名称和人数  (这里是用的右连接）
select dname 部门名称,ifnull(total,0) 总人数 from
(select dno,count(dno) as total from tbemp
group by dno) t1 right outer  join tbdept t2
on t1.dno=t2.dno;

-- 查询除老板外薪资最高的员工的姓名和工资    先查最高工资  再被作为条件
select ename,sal from tbemp
where sal=(
    select max(sal) from tbemp where mgr is not null
);
-- 查询薪水超过平均薪水的员工的姓名和工资
select ename,sal from tbemp
where sal>(select avg(sal) from tbemp);

-- 方法2
select ename,sal ,avgSal from tbemp t1
inner join
(select avg(sal) as avgSal from  tbemp)t2
on sal > avgSal; 

-- 和平均工资差值
select ename,sal ,sal - avgSal from tbemp t1
inner join
(select avg(sal) as avgSal from  tbemp)t2
on sal > avgSal;   
-- on sal > avgSal; 这个是粘表条件  没有就是笛卡尔积 多少*多少条数据

-- 查询薪水超过其所在部门平均薪水的员工的姓名、部门编号和工资
-- 先查部门编号 平均工资
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 and sal>avgSal;
-- t1.dno=t2.dno是粘表条件 表示只在所在部门去比  同时后面是 工资大于平均工资
-- inner join 是粘表
-- 增加查部门名称 
select ename,dname,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 and sal>avgSal inner join tbdept t3
on t1.dno=t3.dno;



-- 查询部门中薪水最高的人姓名、工资和所在部门名称  按部门分组 员工表 员工编号 员工名字工资
select 

-- 查询主管的姓名和职位
select ename,job from tbemp
where empno in (select distinct mgr from tbemp where mgr is not null);
-- 经验：尽可能不使用distinct去重和in集合运算  ，因为性能非常差
-- SQL优化：
-- 想去掉distinct和in 运算可以使用exists和Not exists操作

-- distinct 去重
-- 查询薪资排名4~6名的员工姓名和工资
select ename,sal from tbemp
order by sal desc
limit 3;

select ename,sal from tbemp
order by sal desc
limit 3,3;