mysql数据库的创建和查询

##创建数据库
DROP DATABASE IF EXISTS HRS;
CREATE DATABASE HRS DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

USE HRS;

CREATE TABLE TbDept 
( 
dno INT NOT NULL COMMENT '部门编号', 
dname VARCHAR ( 10 ) NOT NULL COMMENT '部门名称', 
dloc VARCHAR ( 20 ) NOT NULL COMMENT '部门所在地'
);

ALTER TABLE TbDept ADD CONSTRAINT Pk_Dept PRIMARY KEY ( dno );

INSERT INTO TbDept ( dno, dname, dloc )
VALUES
    ( 10, '会计部', '北京' ),
    ( 20, '研发部', '成都' ),
    ( 30, '销售部', '重庆' ),
    ( 40, '运维部', '深圳' );

CREATE TABLE TbEmp 
(
eno INT NOT NULL COMMENT '员工编号',
ename VARCHAR ( 20 ) NOT NULL COMMENT '员工姓名',
job VARCHAR ( 10 ) NOT NULL COMMENT '职位',
mgr INT COMMENT '直接主管编号',
sal INT NOT NULL COMMENT '月薪',
comm INT COMMENT '月补贴',
dno INT NOT NULL COMMENT '所属部门编号' 
);

ALTER TABLE TbEmp ADD CONSTRAINT Pk_Emp_Eno PRIMARY KEY ( eno );
ALTER TABLE TbEmp ADD CONSTRAINT Fk_Emp_Dno FOREIGN KEY ( dno ) REFERENCES TbDept ( dno );

INSERT INTO TbEmp ( eno, ename, job, mgr, sal, comm, dno )
VALUES
    ( 7800, '张三丰', '总裁', NULL, 9000, 1800, 20 ),
    ( 2056, '乔峰', '分析师', 7800, 5000, 2000, 20 ),
    ( 3088, '李莫愁', '设计师', 2056, 3500, NULL, 20 ),
    ( 3211, '张无忌', '程序员', 2056, 3200, NULL, 20 ),
    ( 3233, '丘处机', '程序员', 2056, 3400, 800, 20 ),
    ( 3251, '张翠山', '程序员', 2056, 4000, NULL, 20 ),
    ( 5566, '宋远桥', '会计师', 7800, 4000, 1500, 10 ),
    ( 5234, '郭靖', '出纳', 5566, 2000, 200, 10 ),
    ( 3344, '黄蓉', '销售主管', 7800, 3000, 2500, 30 ),
    ( 1359, '胡一刀', '销售员', 3344, 1800, NULL, 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 
where sal=(select max(sal) from TbEmp);

-- 查询员工的姓名（隐去名字）和年薪((月薪+补贴)*12)（列可以做运算）
select concat(substr(ename, 1, 1), '**') as 姓, (sal+ifnull(comm, 0))*12 as 年薪 
from TbEmp order by 年薪 desc;

-- 查询有员工的部门的编号和人数（分组查询和聚合函数）
select dno as 部门编号, count(dno) as 人数 
from TbEmp group by dno;
-- having 人数 between 3 and 5;

-- 查询所有部门的名称和人数（左外连接和子查询）
-- 说明：连接多张表时如果没有连表条件将产生笛卡尔积
select dname as 部门名称, ifnull(total, 0) as 人数 
from TbDept t1 left outer join
(select dno, count(dno) as total from TbEmp group by dno) t2 
on t1.dno=t2.dno;

-- 查询月薪最高的员工(Boss除外)的姓名和月薪（判断空值）
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) as avgsal from TbEmp);

-- 查询月薪超过其所在部门平均薪水的员工的姓名、部门编号和月薪
select ename, t1.dno, sal, sal-avgsal 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, job from TbEmp 
where eno in (select distinct mgr from TbEmp where mgr is not null);

-- 一般情况下应该尽可能避免使用集合运算（in / not in）和去重操作（distinct）
-- 推荐使用exists或者not exists来替换掉集合运算和去重操作
select ename, job from TbEmp t1 
where exists (select 'x' from TbEmp t2 where t1.eno=t2.mgr);

-- 查询月薪排名4~6名的员工姓名和工资
select ename, sal from TbEmp order by sal desc limit 3 offset 3;
select ename, sal from TbEmp order by sal desc limit 3,3;