实训作业-人事资源管理系统

er图 模型图

DDL与DML

DROP TABLE IF EXISTS `departments`;
CREATE TABLE `departments` (`department_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门ID',`department_name` varchar(100) NOT NULL COMMENT '部门名称',PRIMARY KEY (`department_id`),UNIQUE KEY `department_name` (`department_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;INSERT INTO `departments` VALUES ('5', 'IT部');
INSERT INTO `departments` VALUES ('1', '人力资源部');
INSERT INTO `departments` VALUES ('3', '工程部');
INSERT INTO `departments` VALUES ('4', '市场部');
INSERT INTO `departments` VALUES ('2', '财务部');DROP TABLE IF EXISTS `employee_salaries`;
CREATE TABLE `employee_salaries` (`salary_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '薪资记录ID',`user_id` int(11) DEFAULT NULL COMMENT '员工ID',`grade_id` int(11) DEFAULT NULL COMMENT '薪资等级ID',`salary` decimal(10,2) NOT NULL COMMENT '薪资',`start_date` date NOT NULL COMMENT '薪资开始日期',PRIMARY KEY (`salary_id`),KEY `grade_id` (`grade_id`),KEY `user_id` (`user_id`),CONSTRAINT `employee_salaries_ibfk_1` FOREIGN KEY (`grade_id`) REFERENCES `salary_grades` (`grade_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,CONSTRAINT `employee_salaries_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;INSERT INTO `employee_salaries` VALUES ('1', '1', '1', '3500.00', '2020-01-01');
INSERT INTO `employee_salaries` VALUES ('2', '2', '2', '7000.00', '2021-05-15');
INSERT INTO `employee_salaries` VALUES ('3', '3', '3', '12000.00', '2022-09-01');
INSERT INTO `employee_salaries` VALUES ('4', '4', '4', '15000.00', '2020-01-01');
INSERT INTO `employee_salaries` VALUES ('5', '5', '5', '18000.00', '2021-05-15');
INSERT INTO `employee_salaries` VALUES ('6', '6', '3', '13000.00', '2022-08-02');DROP TABLE IF EXISTS `employee_trainings`;
CREATE TABLE `employee_trainings` (`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '培训记录ID',`user_id` int(11) DEFAULT NULL COMMENT '员工ID',`training_id` int(11) DEFAULT NULL COMMENT '培训ID',`attendance_date` date NOT NULL COMMENT '参加日期',PRIMARY KEY (`id`),KEY `training_id` (`training_id`),KEY `user_id` (`user_id`),CONSTRAINT `employee_trainings_ibfk_1` FOREIGN KEY (`training_id`) REFERENCES `trainings` (`training_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,CONSTRAINT `employee_trainings_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;INSERT INTO `employee_trainings` VALUES ('1', '1', '1', '2023-01-02');
INSERT INTO `employee_trainings` VALUES ('2', '2', '2', '2023-02-16');
INSERT INTO `employee_trainings` VALUES ('3', '3', '3', '2023-03-31');
INSERT INTO `employee_trainings` VALUES ('4', '4', '4', '2023-04-16');
INSERT INTO `employee_trainings` VALUES ('5', '5', '5', '2023-05-02');DROP TABLE IF EXISTS `positions`;
CREATE TABLE `positions` (`position_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '职位ID',`position_name` varchar(100) NOT NULL COMMENT '职位名称',`department_id` int(11) DEFAULT NULL COMMENT '所属部门ID',PRIMARY KEY (`position_id`),UNIQUE KEY `position_name` (`position_name`),KEY `department_id` (`department_id`),CONSTRAINT `positions_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;INSERT INTO `positions` VALUES ('1', '招聘专员', '1');
INSERT INTO `positions` VALUES ('2', '财务经理', '2');
INSERT INTO `positions` VALUES ('3', '软件工程师', '3');
INSERT INTO `positions` VALUES ('4', '市场经理', '4');
INSERT INTO `positions` VALUES ('5', 'IT支持', '5');DROP TABLE IF EXISTS `salary_grades`;
CREATE TABLE `salary_grades` (`grade_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '薪资等级ID',`grade_name` varchar(50) NOT NULL COMMENT '薪资等级名称',`minimum_salary` decimal(10,2) NOT NULL COMMENT '最低薪资',`maximum_salary` decimal(10,2) NOT NULL COMMENT '最高薪资',PRIMARY KEY (`grade_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;INSERT INTO `salary_grades` VALUES ('1', '初级', '3000.00', '5000.00');
INSERT INTO `salary_grades` VALUES ('2', '中级', '5000.00', '8000.00');
INSERT INTO `salary_grades` VALUES ('3', '高级', '8000.00', '12000.00');
INSERT INTO `salary_grades` VALUES ('4', '资深', '12000.00', '18000.00');
INSERT INTO `salary_grades` VALUES ('5', '总监级', '18000.00', '25000.00');DROP TABLE IF EXISTS `trainings`;
CREATE TABLE `trainings` (`training_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '培训ID',`training_name` varchar(100) NOT NULL COMMENT '培训名称',`description` text COMMENT '培训描述',`training_date` date NOT NULL COMMENT '培训日期',PRIMARY KEY (`training_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;INSERT INTO `trainings` VALUES ('1', '新员工入职培训', '公司文化、规章制度等', '2023-01-01');
INSERT INTO `trainings` VALUES ('2', '项目管理培训', '敏捷开发、Scrum等', '2023-02-15');
INSERT INTO `trainings` VALUES ('3', '编程技能培训', 'Python、Java等', '2023-03-30');
INSERT INTO `trainings` VALUES ('4', '市场营销培训', 'SEO、广告策略等', '2023-04-15');
INSERT INTO `trainings` VALUES ('5', '领导力发展培训', '团队管理、沟通技巧等', '2023-05-01');DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (`user_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工ID',`username` varchar(50) NOT NULL COMMENT '用户名',`password` varchar(255) NOT NULL COMMENT '密码',`gender` enum('男','女') NOT NULL COMMENT '性别',`email` varchar(100) DEFAULT NULL COMMENT '邮箱',`phone` varchar(20) DEFAULT NULL COMMENT '电话',`hire_date` date NOT NULL COMMENT '入职日期',`department_id` int(11) NOT NULL,PRIMARY KEY (`user_id`),UNIQUE KEY `username` (`username`),UNIQUE KEY `email` (`email`),UNIQUE KEY `phone` (`phone`),KEY `department_id` (`department_id`),CONSTRAINT `users_ibfk_1` FOREIGN KEY (`department_id`) REFERENCES `departments` (`department_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;INSERT INTO `users` VALUES ('1', 'user1', 'pass123', '男', '[e-mail]', '1234567890', '2020-01-01', '1');
INSERT INTO `users` VALUES ('2', 'user2', 'pass456', '女', '[e-mail]', '0987654321', '2020-02-15', '2');
INSERT INTO `users` VALUES ('3', 'user3', 'pass789', '男', '[e-mail]', '1112223333', '2020-03-30', '3');
INSERT INTO `users` VALUES ('4', 'user4', 'passabc', '女', '[e-mail]', '3334445555', '2020-04-15', '4');
INSERT INTO `users` VALUES ('5', 'user5', 'passxyz', '男', '[e-mail]', '5556667777', '2020-05-01', '5');
INSERT INTO `users` VALUES ('6', 'user6', 'pass988', '男', '[e-mail]', '4578946413', '2020-08-02', '3');

查询

##查询所有员工信息
SELECT * FROM users;##查询薪资等级为“中级”的薪资范围SELECT minimum_salary, maximum_salary FROM salary_grades WHERE grade_name = '中级';
##查询入职时间大于2020-03-31的员工信息
SELECT * FROM users WHERE hire_date > '2020-03-31';
##查询参加过新员工入职培训的员工的用户名和入职日期
SELECT users.username, users.hire_date
FROM users 
JOIN employee_trainings  ON users.user_id = employee_trainings.user_id
JOIN trainings  ON employee_trainings.training_id = trainings.training_id
WHERE trainings.training_name = '新员工入职培训';
##找出薪资等级为"高级"且薪资超过其薪资等级内平均薪资的员工。
SELECT u.username, es.salary
FROM users u
JOIN employee_salaries es ON u.user_id = es.user_id
JOIN salary_grades sg ON es.grade_id = sg.grade_id
WHERE sg.grade_name = '高级' AND es.salary > (SELECT AVG(salary) FROM employee_salaries es2 JOIN salary_grades sg2 ON es2.grade_id = sg2.grade_id WHERE sg2.grade_name = '高级'
);
##找出员工数量最多的部门及其员工数量。
SELECT departments.department_name, COUNT(users.user_id) AS employee_count
FROM departments 
JOIN users  ON departments.department_id = users.department_id
GROUP BY departments.department_name
ORDER BY employee_count DESC
LIMIT 1;

触发器和存储过程

delimiter $$
CREATE TRIGGER update_salaries ##创建一个·触发器
BEFORE UPDATE on employee_salaries ##在修改薪资表后执行
for each ROW
BEGIN
DECLARE result DECIMAL(10,4);##定义一个变量
DECLARE result1 DECIMAL(10,4);##定义第二个变量
SET result=(new.salary-old.salary)/old.salary*100;
SET result1=(old.salary-new.salary)/old.salary*100;
IF result>20 THEN ##如果上浮动超过20执行下面语句
SIGNAL SQLSTATE '45000' SET message_text='更改工资上下浮动不能超过20%';
end if;
IF result1>20 THEN##如果下浮动超过20执行下面语句
SIGNAL SQLSTATE '45000' SET message_text='更改工资上下浮动不能超过20%';
end IF;
END;
$$
delimiter;

delimiter $$
CREATE PROCEDURE create_user(
IN in_user_id int, in in_grade_id int)##创建1个添加新员工的触发器，定义员工id和薪资等级id
BEGIN
INSERT INTO users VALUES(in_user_id,'杀','pass213','男','[e-mail]','1589756324','2020-01-01',1);
INSERT INTO employee_salaries VALUES(7,in_user_id,in_grade_id,13500,'2021-05-15');##向员工表和员工薪资表中插入信息
end $$
delimiter;
CALL create_user(7,3);##测试