MySQL每日一练--员工管理数据库

一，数据库操作

创建员工管理数据库EmpManager(5分)

二，数据表操作

1. 在数据库EmpManager中，创建部门表depart，表结构如下表所示(4分)

列名	数据类型	约束	备注
departid	Char(10)	主键	部门编号
departname	Char(50)	唯一	部门名称
departlocation	Char(50)	默认北京	部门所在地

1. 在数据库EmpManager中，创建职位表job，表结构如下表所示(4分)

列名	数据类型	约束	备注
jobid	Char(10)	主键	职位编号
jobname	Char(50)	非空	职位名称
joblevel	Int	非空，默认为1	职位级别

1. 在数据库EmpManager中，创建员工表employee，表结构如下表所示(7分)

列名	数据类型	约束	备注
empid	Char(10)	主键	员工编号
empname	Char(50)	非空	员工姓名
empsex	Char(4)	默认为男，检查只能是男或女	员工性别
empjob	Char(20)	外键	职位
empmgr	Char(10)		上级领导编号
diredate	Date		入职时间
Salary	Decimal(7,2)	检查大于0	工资
departid	Char(10)	外键	部门编号

三，数据操作

1、给部门表depart添加数据，如下图所示：(4分)

部门号	部门名	部门所在地点
01	总裁办	北京
02	财务处	上海
03	研发部	武汉
04	销售部	深圳

2、给职位表job添加数据，如下图所示：(4分)

职位号	职位名	职位级别
01	董事长	20
02	总经理	18
03	经理	16
04	销售员	11
05	分析师	14
06	文员	10
07	会计	12

3、给员工表employee添加数据，如下图所示：(7分)

员工号	姓名	性别	职位	领导	入职时间	工资	部门号
10001	老李	男	01		2001-09-01	99999	01
10002	刘备	男	02	10001	2005-10-01	50000	01
10003	关羽	男	03	10002	2006-05-01	30000	04
10004	张飞	男	03	10002	2006-05-01	30000	04
10005	诸葛亮	男	05	10002	2012-07-13	45000	03
10006	赵云	男	03	10002	2008-04-24	25000	02
20003	孙尚香	女	06	10006	2016-09-26	5500	03
10008	黄月英	女	07	10005	2014-01-12	13500	02
30010	徐庶	男	06	10006	2007-11-11	28000	02
30035	周仓	男	04	10003	2015-03-12	5800	04
40001	马超	男	04	10004	2018-08-22	7900	04
50001	孟获	男	04	10002	2021-02-13	4500	04

-- 1、给马超升职为经理，修改其职位为03，领导变成10002，工资涨3000；（5分）

-- 2、查询员工编号，姓名，性别，职位，入职时间，基本工资，要求使用列别名；（5分）

-- 3、查询02，03，04部门入职超过10年的员工信息；（5分）

-- 4、查询员工编号以1开头，且工资在30000及以上的员工信息；（5分）

-- 5、查询工资最高的前3-8名的员工信息；（6分）

-- 6、查询04部门的人数，及平均工资；（6分）

-- 7、查询每个部门的人数，平均工资，最高工资，最低工资，并显示人数在3人及以上的信息；（8分）

-- 8、查询职位级别在13以上的员工编号，员工姓名，员工性别，职位名称，入职时间，部门名称和工作地点（使用多表连接查询）（8分）

-- 9、查询赵云所领导的员工信息（使用子查询）（8分）

-- 10、显示非销售人员职位名称以及从事同一工作雇员的月工资的总和,并且要满足从事同一工作的雇员的月工资合计大于50000,输出结果按月工资的合计升序排列 （9分）

-- 一，	创建数据库EmpManager
-- 1，删除数据库
	DROP DATABASE IF EXISTS EmpManager;
-- 2，创建数据库
	CREATE DATABASE EmpManager;
-- 3，修改数据库编码方式和字符集排列顺序
	ALTER DATABASE EmpManager CHARACTER SET utf8 COLLATE utf8_bin;
-- 4，使用数据库
	USE EmpManager;
-- 二，数据表操作
-- 1、在数据库EmpManager中，创建部门表depart，表结构如下表所示(4分)
DROP TABLE IF EXISTS depart;
CREATE TABLE depart(
departid CHAR(10) PRIMARY KEY,-- 主键	部门编号
departname CHAR(50) UNIQUE,   -- 唯一	部门名称
departlocation CHAR(50) DEFAULT '北京'   -- 默认北京	部门所在地	
);
-- 2、在数据库EmpManager中，创建职位表job，表结构如下表所示(4分)
DROP TABLE IF EXISTS job;
CREATE TABLE job(
jobid CHAR(10) PRIMARY KEY,     -- 主键	职位编号
jobname CHAR(50) NOT NULL,      -- 非空	职位名称
joblevel INT NOT NULL DEFAULT 1 -- 非空，默认为1  职位级别
);
-- 3、在数据库EmpManager中，创建员工表employee，表结构如下表所示(7分)
DROP TABLE IF EXISTS employee;
CREATE TABLE employee(
empid CHAR(10) PRIMARY KEY,-- 主键	员工编号
empname CHAR(50) NOT NULL,-- 非空	员工姓名
empsex CHAR(4) DEFAULT '男' CHECK(empsex IN ('男','女')),-- 默认为男，检查只能是男或女	员工性别
empjob CHAR(20) REFERENCES job(jobid),-- 外键	职位
empmgr CHAR(10),               -- 上级领导编号
diredate DATE,                 -- 入职时间
salary DECIMAL(7,2) CHECK(salary>0),-- 检查大于0	工资
departid CHAR(10) REFERENCES depart(departid)-- 外键	部门编号
);
-- 三，数据操作
SELECT * FROM depart;
SELECT * FROM job;
SELECT * FROM employee;
-- 1、给部门表depart添加数据，如下图所示：(4分)
INSERT INTO depart (departid,departname,departlocation) VALUES 
('01','总裁办','北京'),
('02','财务处','上海'),
('03','研发部','武汉'),
('04','销售部','深圳');
-- 2、给职位表job添加数据，如下图所示：(4分)
INSERT INTO job (jobid,jobname,joblevel) VALUES 
('01','董事长',20),
('02','总经理',18),
('03','经理',16),
('04','销售员',11),
('05','分析师',14),
('06','文员',10),
('07','会计',12);
-- 3、给员工表employee添加数据，如下图所示：(7分)
INSERT INTO employee (empid,empname,empsex,empjob,empmgr,diredate,Salary,departid) VALUES 
('10001','老李','男','01',NULL,'2001-09-01',99999,'01'),
('10002','刘备','男','02','10001','2005-10-01',50000,'01'),
('10003','关羽','男','03','10002','2006-05-01',30000,'04'),
('10004','张飞','男','03','10002','2006-05-01',30000,'04'),
('10005','诸葛亮','男','05','10002','2012-07-13',45000,'03'),
('10006','赵云','男','03','10002','2008-04-24',25000,'02'),
('20003','孙尚香','女','06','10006','2016-09-26',5500,'03'),
('10008','黄月英','女','07','10005','2014-01-12',13500,'02'),
('30010','徐庶','男','06','10006','2007-11-11',28000,'02'),
('30035','周仓','男','04','10003','2015-03-12',5800,'04'),
('40001','马超','男','04','10004','2018-08-22',7900,'04'),
('50001','孟获','男','04','10002','2021-02-13',4500,'04');
-- 四，完成如下查询题目
-- 1、给马超升职为经理，修改其职位为03，领导变成10002，工资涨3000；（5分）
UPDATE employee SET empjob='03',empmgr='10002',Salary=Salary+3000 WHERE empname='马超';
-- 2、查询员工编号，姓名，性别，职位，入职时间，基本工资，要求使用列别名；（5分）
SELECT empid AS '员工编号',empname AS '姓名',empsex AS '性别',empjob AS '职位',diredate AS '入职时间', Salary AS '基本工资' FROM employee;
-- 3、查询02，03，04部门入职超过10年的员工信息；（5分）
SELECT * FROM employee WHERE DATEDIFF(CURDATE(), diredate)/365 > 10 AND departid IN ('02', '03', '04');
-- 4、查询员工编号以1开头，且工资在30000及以上的员工信息；（5分）
SELECT * FROM employee WHERE empid LIKE '1%' AND salary >= 30000;
-- 5、查询工资最高的前3-8名的员工信息；（6分）
SELECT * FROM employee ORDER BY Salary DESC LIMIT 2, 6;
-- 6、查询04部门的人数，及平均工资；（6分）
SELECT COUNT(*) AS 人数,AVG(salary) AS 平均工资 FROM employee WHERE departid = '04';
-- 7、查询每个部门的人数，平均工资，最高工资，最低工资，并显示人数在3人及以上的信息；（8分）
SELECT departname AS 部门名称,COUNT(employee.empid) AS 人数,AVG(employee.salary) AS 平均工资,MAX(employee.salary) AS 最高工资,MIN(employee.salary) AS 最低工资
FROM depart
INNER JOIN employee ON depart.departid=employee.departid
GROUP BY depart.departname
HAVING COUNT(employee.empid)>=3;
-- 8、查询职位级别在13以上的员工编号，员工姓名，员工性别，职位名称，入职时间，部门名称和工作地点（使用多表连接查询）（8分）
SELECT empid AS 员工编号,empname AS 员工姓名,empsex AS 员工性别,jobname AS 职位名称,diredate AS 入职时间,departname AS 部门名称,departlocation AS 工作地点
FROM employee
INNER JOIN depart ON employee.departid = depart.departid
INNER JOIN job ON employee.empjob=job.jobid
WHERE joblevel>=13;
-- 9、查询赵云所领导的员工信息（使用子查询）（8分）
SELECT * FROM employee
WHERE empmgr = (SELECT empid FROM employee WHERE empname = '赵云');
-- 10、显示非销售人员职位名称以及从事同一工作雇员的月工资的总和,并且要满足从事同一工作的雇员的月工资合计大于50000,输出结果按月工资的合计升序排列 （9分）
SELECT jobname AS 职位名称,SUM(salary) AS 月工资的总和 FROM job INNER JOIN employee ON job.jobid=employee.empjob
WHERE jobname!='销售员' GROUP BY jobname
HAVING SUM(employee.salary)>50000
ORDER BY SUM(employee.salary) ASC;