一,数据库操作
创建员工管理数据库EmpManager(5分)
二,数据表操作
- 在数据库EmpManager中,创建部门表depart,表结构如下表所示(4分)
列名 | 数据类型 | 约束 | 备注 |
departid | Char(10) | 主键 | 部门编号 |
departname | Char(50) | 唯一 | 部门名称 |
departlocation | Char(50) | 默认北京 | 部门所在地 |
- 在数据库EmpManager中,创建职位表job,表结构如下表所示(4分)
列名 | 数据类型 | 约束 | 备注 |
jobid | Char(10) | 主键 | 职位编号 |
jobname | Char(50) | 非空 | 职位名称 |
joblevel | Int | 非空,默认为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;