mysql工资数据表怎么打 工资表数据库设计

（一）测试任务

任务描述：公司薪酬系统用于记录并发放员工薪资。该系统的数据库设计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;