人力资源管理系统

部门表

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

​SET FOREIGN_KEY_CHECKS = 1;

 薪资表

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;

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');

SET FOREIGN_KEY_CHECKS = 1;

培训记录表

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;

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');

SET FOREIGN_KEY_CHECKS = 1;

职位表

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;

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);

SET FOREIGN_KEY_CHECKS = 1;

薪资等级表

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, '中级工', 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);

SET FOREIGN_KEY_CHECKS = 1;

培训表

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, '安全意识培训', '提升员工的安全防范意识', '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');

SET FOREIGN_KEY_CHECKS = 1;

员工表

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;

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');

SET FOREIGN_KEY_CHECKS = 1;