(一)测试任务
任务描述:公司薪酬系统用于记录并发放员工薪资。该系统的数据库设计E-R图如图1所示。
图1 薪酬系统E-R图
该子系统涉及的表中各字段定义见表1到表3。
表1 部门表t_department
字段名 | 数据类型 | 是否允许空 | 是否为主键(默认为否) | 其它约束 | 字段说明 |
id | int | 否 | 是 | 标识列,从1开始自动增长 | 编号 |
dept_name | varchar(20) | 否 | 名称 | ||
description | varchar(100) | 默认为null | 描述 | ||
manager_id | int | 否 | 外键 | 部门经理ID |
表2 雇员表t_employee
字段名 | 数据类型 | 是否允许空 | 是否为主键(默认为否) | 其它约束 | 字段说明 |
id | int | 否 | 是 | 标识列,从1开始自动增长 | 编号 |
name | varchar(10) | 否 | 姓名 | ||
level | int | 否 | 级别限制在1-15 | 级别 | |
dept_id | int | 否 | 外键 | 所属部门ID |
表3 薪水表t_salary
字段名 | 数据类型 | 是否允许空 | 是否为主键(默认为否) | 其它约束 | 字段说明 |
id | int | 否 | 是 | 标识列,从1开始自动增长 | 编号 |
basic_salary | decimal(10, 1) | 否 | 限制必须大于0 | 基本工资 | |
performance_salary | decimal(10, 1) | 否 | 绩效工资 | ||
salary_date | datetime | 否 | |||
employee_id | int | 否 | 外键 | 薪酬所属雇员ID |
请按以上设计完成数据库创建、数据表创建和数据操作任务:
任务1:创建数据库(5分)
创建数据库EmployeeSalaryManagement。如果原来有该数据库,请先删除。
任务2:创建数据表及约束(30分)
(1)按表1到表3所列出字段, 创建数据表t_department、t_employee、t_salary,将三个表的主键列设为标识列,自动从1开始增长。(10分)
(2)根据表1到表3中的约束要求,建立好主建、外键、默认、检查、非空等约束。(20分)
CREATE DATABASE IF NOT EXISTS EmployeeSalaryManagement_dsw;
USE EmployeeSalaryManagement_dsw;
CREATE TABLE IF NOT EXISTS t_department_dsw(
id_dsw1 int PRIMARY key auto_increment,
dept_name_dsw varchar(20) NOT NULL,
description_dsw varchar(100) DEFAULT NULL,
manager_id_dsw int not null
)ENGINE=InnoDB;
SELECT*FROM t_department_dsw;
CREATE TABLE IF NOT EXISTS t_employee_dsw(
id_dsw int PRIMARY key auto_increment,
name_dsw varchar(10)not null,
level_dsw int not null check(level_dsw between 1 and 15),
dept_id_dsw int not null,
CONSTRAINT fk_t_employee_dsw_t_department_dsw FOREIGN KEY(dept_id_dsw)REFERENCES t_department_dsw(id_dsw1)on DELETE CASCADE on UPDATE CASCADE
)ENGINE=InnoDB;
SELECT*FROM t_employee_dsw;
CREATE TABLE IF NOT EXISTS t_salary_dsw(
id_dsw2 int PRIMARY key auto_increment,
basic_salary_dsw decimal(10, 1)not null check(basic_salary_dsw>0),
performance_salary_dsw decimal(10, 1)not null ,
salary_date_dsw datetime not null,
employee_id_dsw int not null,
CONSTRAINT fk_t_salary_dsw_t_employee_dsw FOREIGN KEY(employee_id_dsw)REFERENCES t_employee_dsw(id_dsw)on DELETE CASCADE on UPDATE CASCADE
)ENGINE = InnoDB;
SELECT*FROM t_salary_dsw;
任务3:数据库操作(50分)
(1)在三个表中分别插入5条测试数据(样本数据包含下面题目中使用的数据)。
insert into t_department_dsw values(null,'销售部', NULL, '0020');
insert into t_department_dsw values(null,'人事部', NULL, '0002');
insert into t_department_dsw values(null,'生产部', NULL, '0027');
insert into t_department_dsw values(null,'后勤部', NULL, '0012');
insert into t_department_dsw values(null,'公关部', NULL, '0007');
SELECT*FROM t_department_dsw;
insert into t_employee_dsw values(null,'王旭','002','1');
insert into t_employee_dsw values(null,'张世杰','012','1');
insert into t_employee_dsw values(null,'闻康','011','1');
insert into t_employee_dsw values(null,'曾裕豪','002','1');
insert into t_employee_dsw values(null,'胡佳','012','1');
insert into t_employee_dsw values(null,'孙达','012','2');
insert into t_employee_dsw values(null,'黄伟英','011','2');
insert into t_employee_dsw values(null,'蒋珂','005','2');
insert into t_employee_dsw values(null,'刘鹏','010','2');
insert into t_employee_dsw values(null,'刘明','015','3');
insert into t_employee_dsw values(null,'胡迎','005','3');
insert into t_employee_dsw values(null,'危博文','014','3');
insert into t_employee_dsw values(null,'黄佳','004','3');
insert into t_employee_dsw values(null,'张三','015','4');
insert into t_employee_dsw values(null,'胡好','005','4');
insert into t_employee_dsw values(null,'博文','014','4');
insert into t_employee_dsw values(null,'秦明','003','4');
SELECT*FROM t_employee_dsw;
insert into t_salary_dsw values(null,'3000','200','2018-2-5','1');
insert into t_salary_dsw values(null,'3000','100','2018-10-7','2');
insert into t_salary_dsw values(null,'3000','200','2018-2-9','3');
insert into t_salary_dsw values(null,'2000','110','2018-2-5','4');
insert into t_salary_dsw values(null,'2000','100','2018-10-7','5');
insert into t_salary_dsw values(null,'2000','200','2018-2-9','6');
insert into t_salary_dsw values(null,'3000','200','2018-2-5','7');
insert into t_salary_dsw values(null,'3000','100','2018-10-7','8');
insert into t_salary_dsw values(null,'3000','200','201-2-9','9');
insert into t_salary_dsw values(null,'2000','110','2018-2-5','10');
insert into t_salary_dsw values(null,'2000','100','2019-10-7','11');
insert into t_salary_dsw values(null,'2000','200','2019-2-9','12');
insert into t_salary_dsw values(null,'2000','100','2019-10-7','13');
insert into t_salary_dsw values(null,'3000','200','2018-2-9','14');
insert into t_salary_dsw values(null,'2000','110','2019-2-5','15');
insert into t_salary_dsw values(null,'2000','100','2019-10-7','16');
insert into t_salary_dsw values(null,'2000','200','2020-2-9','17');
SELECT*FROM t_salary_dsw;
(2)查询t_employee表中的员工id、员工姓名和部门编号。
SELECT id_dsw,name_dsw,dept_id_dsw
FROM t_employee_dsw;
(3)查询员工姓名含‘张’的员工id、员工姓名和部门编号。
SELECT id_dsw,name_dsw,dept_id_dsw
FROM t_employee_dsw
WHERE name_dsw like '%张%';
(4)查询部门的人数和平均工资。
SELECT COUNT(b.dept_id_dsw),AVG(c.basic_salary_dsw)
FROM t_salary_dsw c,t_employee_dsw b
where b.dept_id_dsw=c.employee_id_dsw;
(5)删除雇员‘张三’的所有信息。
delete from t_salary_dsw
WHERE id_dsw2=(SELECT id_dsw from t_employee_dsw where name_dsw='张三');
SELECT*FROM t_salary_dsw;
delete from t_employee_dsw
where name_dsw='张三';
SELECT*FROM t_employee_dsw;