MySQL测试题——开发公司人事管理系统,包括 Employee表 和 Department表
一、需求分析
我们的开发团队,计划开发一款公司人事管理软件,用于帮助中小型企业进行更加高效的人事管理。现在需要对数据库部分进行设计和开发,根据对需求和立项的分析,我们确定该数据库中最核心的两个表为员工表(Employee)和部门表(Department)。两个表的结构如下:
Employee表:
字段名 | 数据类型 | 约束 | 说明 |
employeeId | int | 非空,主键,自增 | 员工编号 |
name | varchar | 非空 | 姓名 |
birth | date |
| 出生日期 |
joblevel | varchar | 长度10 | 职位级别 |
salary | decimal | 长度10,2位小数 | 薪资 |
phone | varchar | 长度11 | 电话 |
departmentId | int | 外键,非空,引用Department表的主键 | 所属部门 |
Department表:
字段名 | 数据类型 | 约束 | 说明 |
departmentId | int | 非空,主键,自增 | 部门编号 |
name | varchar | 长度20 | 部门名称 |
二、具体要求
- 使用DDL语言创建数据库(Company),创建数据表Employee和Department;
- 使用DML语言插入测试数据,具体数据如下(请按照测试数据插入):
Department:
employeeId | name | birth | joblevel | salary | phone | departmentId |
1001 | 张强 | 1986-2-3 | 一级 | 8000 | 13585422655 | 1 |
1003 | 萌萌 | 1990-4-19 | 二级 | 6000 | 18548775264 | 2 |
1004 | 李小峰 | 1973-7-20 | 二级 | 5700 | 18625489512 | 1 |
1006 | 刘珊 | 1976-6-28 | 一级 | 7500 | 18524811174 | 2 |
1007 | 李梅 | 1980-1-29 | 二级 | 5500 | 13958621455 | 3 |
1008 | 张宝玉 | 1982-9-23 | 二级 | 5600 | 13715620210 | 1 |
1009 | 陈大壮 | 1978-5-21 | 一级 | 7700 | 15848562585 | 4 |
1010 | 张天琪 | 1980-9-15 | 二级 | 5000 | 13965815822 | 4 |
Emploment:
departmentId | name |
1 | 商务部 |
2 | 行政部 |
3 | 财务部 |
4 | 研发部 |
3、考试结束时,分离出数据脚本文件和查询脚本文件,一并上交;
三、功能实现
创建数据库:
CREATE DATABASE Company;
创建Employee表:
CREATE TABLE employee (
employeeId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
birth DATE,
joblevel VARCHAR(10),
salary DECIMAL(10,2),
phone VARCHAR(11),
departmentId INT NOT NULL
);
创建Department表:
CREATE TABLE Department (
departmentId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
设置外键:
ALTER TABLE Employee
ADD CONSTRAINT fk_departmentId
FOREIGN KEY (departmentId)
REFERENCES department(departmentId)
插入数据:
-- ----------------------------
-- Records of department
-- ----------------------------
INSERT INTO department VALUES ('1', '商务部');
INSERT INTO department VALUES ('2', '行政部');
INSERT INTO department VALUES ('3', '财务部');
INSERT INTO department VALUES ('4', '研发部');
-- ----------------------------
-- Records of employee
-- ----------------------------
INSERT INTO employee VALUES ('1001', '张强', '1986-02-03', '一级', '8000.00', '13585422655', '1');
INSERT INTO employee VALUES ('1003', '萌萌', '1990-04-19', '二级', '6000.00', '18548775264', '2');
INSERT INTO employee VALUES ('1004', '李小峰', '1973-07-20', '二级', '5700.00', '18625489512', '1');
INSERT INTO employee VALUES ('1006', '刘珊', '1976-06-28', '一级', '7500.00', '18524811174', '2');
INSERT INTO employee VALUES ('1007', '李梅', '1980-01-29', '二级', '5500.00', '13958621455', '3');
INSERT INTO employee VALUES ('1008', '张宝玉', '1982-09-23', '二级', '5600.00', '13715620210', '1');
INSERT INTO employee VALUES ('1009', '陈大壮', '1978-05-21', '一级', '7700.00', '15848562585', '4');
INSERT INTO employee VALUES ('1010', '张天琪', '1980-09-15', '二级', '5000.00', '13965815822', '4');
题目:
1、通过层层面试筛选,你正式成为该公司研发部的一名二级员工,请以自己的信息,插入一条数据到员工表;(5)
INSERT INTO employee VALUES (NULL, '杨明金', '2005-06-01', '二级', '4500.00', '18877778888', '4');
2、统计研发部的员工信息,并按照薪资升序排列;(5)
SELECT * FROM employee e,department d
WHERE e.departmentid=d.departmentid
AND d.name='研发部'
ORDER BY salary
3、给二级员工中,薪资低于5500的,增加薪资500元,并将数据更新到数据库;(10)
UPDATE employee SET salary=salary+500
WHERE joblevel='二级' AND salary<5500
4、查询统计每个部门的员工平均薪资,并显示部门名称和平均薪资;(10)
SELECT d.name 部门名称,ROUND(AVG(salary),2) 平均薪资 FROM employee e,department d
WHERE e.departmentid=d.departmentid
GROUP BY d.name
5、“陈大壮”由于个人原因,从公司离职了,故需从数据库中,删除他的个人信息,请使用DML语言实现该需求;(10)
DELETE FROM employee WHERE name='陈大壮'
6、李梅昨天去换手机卡了,新的号码是15677289289,在系统中,需要对她的电话进行更新。(10)
UPDATE employee SET phone='15677289289' WHERE name='李梅'
7、查询公司中年龄大于35岁姓张的员工信息;(10)
SELECT * FROM employee
WHERE FLOOR(DATEDIFF(CURDATE(),birth)/365)>35
AND name like '张%'
8、计算年龄最大的员工和年龄最小的员工相差的岁数;(10)
SELECT DATEDIFF(MAX(birth),MIN(birth))/365 FROM employee
9、查看最大年龄和最小年龄:
SELECT MAX(DATEDIFF(CURDATE(),birth)/365) 最大年龄,MIN(DATEDIFF(CURDATE(),birth)/365) 最小年龄 FROM employee;
提示:
内置函数curdate()显示当前日期
内置函数datediff(date1,date2)用于计算日期date1和date2之间相隔的天数
本文来自博客园,作者:明金同学,转载请注明原文链接:https://www.cnblogs.com/vmuu/p/15663655.html
公众号:【明金同学】