人力资源管理系统

夜雨声烦丿

已于 2024-06-27 16:23:24 修改

阅读量141

点赞数 4

文章标签：数据库服务器 java

于 2024-06-26 11:47:09 首次发布

本文链接：https://blog.csdn.net/byyyyy11/article/details/139984158

版权

1、ER图

2、数据库模型图

3、DDL

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;

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 `positions_ibfk_1` (`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=16 DEFAULT CHARSET=utf8;

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;

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;

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 '密码',
  `first_name` varchar(50) NOT NULL COMMENT '名',
  `last_name` varchar(50) 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 '入职日期',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

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 `employee_salaries_ibfk_1` (`user_id`),
  KEY `employee_salaries_ibfk_2` (`grade_id`),
  CONSTRAINT `employee_salaries_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `employee_salaries_ibfk_2` FOREIGN KEY (`grade_id`) REFERENCES `salary_grades` (`grade_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8;

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 `employee_trainings_ibfk_1` (`user_id`),
  KEY `employee_trainings_ibfk_2` (`training_id`),
  CONSTRAINT `employee_trainings_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `employee_trainings_ibfk_2` FOREIGN KEY (`training_id`) REFERENCES `trainings` (`training_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

4、DML

INSERT INTO `departments` VALUES ('2', '培训与发展部门');
INSERT INTO `departments` VALUES ('1', '安保部门');
INSERT INTO `departments` VALUES ('4', '招聘与配置部门');
INSERT INTO `departments` VALUES ('3', '组织文化部门');
INSERT INTO `departments` VALUES ('5', '财务部门');

INSERT INTO `users` VALUES ('1', '韩信', '123', '韩信', '兵仙', '男', 'hanxin@example.com', '13800138001', '2020-01-01');
INSERT INTO `users` VALUES ('2', '牛魔王', '123', '牛魔王', '平天大圣', '男', 'niumowang@example.com', '13900139002', '2021-05-15');
INSERT INTO `users` VALUES ('3', '项羽', '123', '项羽', '西楚霸王', '男', 'xiangyu@example.com', '13700137003', '2022-09-01');
INSERT INTO `users` VALUES ('4', '诸葛亮', '123', '诸葛亮', '卧龙', '男', 'zhugeliang@example.com', '13735660000', '2023-01-01');
INSERT INTO `users` VALUES ('5', '庞统', '123', '庞统', '凤雏', '男', 'pangtong@example.com', '13832677457', '2023-02-01');

INSERT INTO `salary_grades` VALUES ('1', '临时工', '3000.00', '5000.00');
INSERT INTO `salary_grades` VALUES ('2', '中级工', '5500.00', '8000.00');
INSERT INTO `salary_grades` VALUES ('3', '高级工', '9000.00', '15000.00');
INSERT INTO `salary_grades` VALUES ('4', '高管', '12000.00', '20000.00');
INSERT INTO `salary_grades` VALUES ('5', '专家', '30000.00', '50000.00');

INSERT INTO `trainings` VALUES ('1', '培训与发展项目', '提升员工的职业发展能力', '2023-01-01');
INSERT INTO `trainings` VALUES ('2', '安全意识培训', '提升员工的安全防范意识', '2023-02-01');
INSERT INTO `trainings` VALUES ('3', '人力资源招聘与配置培训', '提升员工的专业知识和技能', '2023-03-01');
INSERT INTO `trainings` VALUES ('4', '企业文化培训', '提升员工对组织的认同感和归属感', '2023-04-01');
INSERT INTO `trainings` VALUES ('5', '财务管理培训', '提升员工财务风控意识', '2023-05-01');

INSERT INTO `positions` VALUES ('11', '培新与发展经理', '1');
INSERT INTO `positions` VALUES ('12', '保安大队长', '2');
INSERT INTO `positions` VALUES ('13', '面试官', '3');
INSERT INTO `positions` VALUES ('14', '组织部总监', '4');
INSERT INTO `positions` VALUES ('15', '财务部长', '5');

INSERT INTO `employee_trainings` VALUES ('1', '1', '1', '2023-03-15');
INSERT INTO `employee_trainings` VALUES ('2', '2', '2', '2023-04-10');
INSERT INTO `employee_trainings` VALUES ('3', '1', '3', '2023-05-05');
INSERT INTO `employee_trainings` VALUES ('4', '3', '1', '2023-03-16');
INSERT INTO `employee_trainings` VALUES ('5', '2', '3', '2023-05-06');

INSERT INTO `employee_salaries` VALUES ('10', '1', '1', '3500.00', '2020-01-01');
INSERT INTO `employee_salaries` VALUES ('11', '2', '2', '7000.00', '2021-05-15');
INSERT INTO `employee_salaries` VALUES ('12', '3', '3', '12000.00', '2022-09-01');
INSERT INTO `employee_salaries` VALUES ('13', '4', '4', '20000.00', '2023-01-01');
INSERT INTO `employee_salaries` VALUES ('14', '5', '5', '50000.00', '2023-02-01');

5、基础查询

-- 用中文显示用户姓名和手机号
select username'姓名',phone'手机号' from users;

-- 根据部门名称模糊查询***部，需要走索引
explain select * from departments where department_name like '___门';

-- 根据培训记录表查询所有用户的培训id数量，根据培训次数倒叙排序
select user_id'员工id',count(training_id) '培训次数' from employee_trainings  group by user_id order by '培训次数' desc;

6、复杂查询

-- 正确显示职位信息对应培训应用信息多表联合查询
select * from positions,trainings where department_id=training_id;

-- 正确使用聚合函数，使用子查询，查看工种最大薪资>所有工种的平均最大薪资的工种
select grade_name '工种',maximum_salary '最大薪资' from salary_grades
where 
	maximum_salary>(select avg(maximum_salary) from salary_grades) 
order by grade_name limit 1;

-- 使用子查询查询最小薪资<所有工种的平均最小薪资的工种，并利用多表查询查询用户姓名和密码
select username'姓名',`password`'密码',grade_name'工种',maximum_salary'最大薪资' from salary_grades,users
where minimum_salary>(select avg(minimum_salary) from salary_grades)and user_id=grade_id order by grade_name;

7、触发器

DELIMITER $$
CREATE TRIGGER before_user_email_update
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM users WHERE email = NEW.email AND user_id <> NEW.user_id) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '新的邮箱地址已经被其他用户使用';
    END IF;
END $$
DELIMITER ;

DELIMITER $$
CREATE TRIGGER before_department_delete
BEFORE DELETE ON departments
FOR EACH ROW
BEGIN
    DECLARE employee_count INT;
    
    -- 检查是否有员工在该部门下
    SELECT COUNT(*) INTO employee_count FROM positions WHERE department_id = OLD.department_id;
    
    IF employee_count > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '该部门下仍有员工，不可删除。';
    END IF;
END $$
DELIMITER ;

8、存储过程

-- 更新员工薪资记录
DELIMITER $$
CREATE PROCEDURE UpdateEmployeeSalary(
    IN p_salary_id INT,
    IN p_salary DECIMAL(10,2)
)
BEGIN
    UPDATE employee_salaries
    SET salary = p_salary
    WHERE salary_id = p_salary_id;
END $$
DELIMITER ;


-- 调用存储过程
CALL UpdateEmployeeSalary(10, 3800.00);

-- 删除员工培训记录
DELIMITER $$
CREATE PROCEDURE DeleteEmployeeTraining(
    IN p_id INT
)
BEGIN
    DELETE FROM employee_trainings
    WHERE id = p_id;
END $$
DELIMITER ;


-- 调用存储过程
CALL DeleteEmployeeTraining(5);