MySQL工资管理系统

前言：

   •  工资管理系统是一个用于记录员工薪资信息、计算薪资、管理薪资发放等功能的系统。该系统旨在资助企业高效、正确地处理员工的薪资数据，并提供方便的查询和报表功能。    •  系统的重要功能包括：员工信息管理：记录员工的基本信息，如姓名、性别、职位等。    •  薪资项目设置：定义薪资构成项目，如基本工资、奖金、补助等。    •  薪资发放管理：记录薪资发放记录，包括发放时间、发放金额等。    •  薪资计算：根据员工的薪资项目和考勤数据，主动计算员工的薪资总额。    •  报表生成：生成薪资明细报表、薪资汇总报表等，方便管理人员举行统计分析    一、ER图

二、数据库模型图

三、DDL

1. CREATE TABLE Employees (
2.     employee_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID',
3.     name VARCHAR(100) NOT NULL COMMENT '员工姓名',
4.     gender ENUM('男', '女') NOT NULL COMMENT '性别',
5.     position VARCHAR(100) NOT NULL COMMENT '职位',
6.     hire_date DATE NOT NULL COMMENT '入职日期'
7. );
9. CREATE TABLE SalaryItems (
10.     item_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '薪资项目ID',
11.     item_name VARCHAR(100) NOT NULL COMMENT '薪资项目名称',
12.     description VARCHAR(255) COMMENT '薪资项目描述'
13. );
15. CREATE TABLE SalaryStandards (
16.     standard_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '薪资标准ID',
17.     item_id INT NOT NULL COMMENT '薪资项目ID',
18.     amount DECIMAL(10, 2) NOT NULL COMMENT '薪资金额',
19.     FOREIGN KEY (item_id) REFERENCES SalaryItems(item_id)
20. );
22. CREATE TABLE SalaryDetails (
23.     detail_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '薪资详情ID',
24.     employee_id INT NOT NULL COMMENT '员工ID',
25.     item_id INT NOT NULL COMMENT '薪资项目ID',
26.     amount DECIMAL(10, 2) NOT NULL COMMENT '薪资金额',
27.     payment_date DATE NOT NULL COMMENT '支付日期',
28.     FOREIGN KEY (employee_id) REFERENCES Employees(employee_id),
29.     FOREIGN KEY (item_id) REFERENCES SalaryItems(item_id)
30. );
32. CREATE TABLE SalaryPayments (
33.     payment_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '支付ID',
34.     payment_date DATE NOT NULL COMMENT '支付日期',
35.     total_amount DECIMAL(10, 2) NOT NULL COMMENT '总金额'
36. );
38. CREATE TABLE SalaryPaymentDetails (
39.     detail_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '支付详情ID',
40.     payment_id INT NOT NULL COMMENT '支付ID',
41.     employee_id INT NOT NULL COMMENT '员工ID',
42.     amount DECIMAL(10, 2) NOT NULL COMMENT '支付金额',
43.     FOREIGN KEY (payment_id) REFERENCES SalaryPayments(payment_id),
44.     FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
45. );

复制代码

四、DML

1. INSERT INTO Employees (name, gender, position, hire_date) VALUES
2. ('孙悟空', '男', '程序员', '2020-01-01'),
3. ('白骨精', '女', '产品经理', '2020-02-15'),
4. ('猪八戒', '男', 'UI设计师', '2020-03-08');
5. INSERT INTO SalaryItems (item_name, description) VALUES
6. ('基本工资', '员工的基本薪资'),
7. ('奖金', '根据业绩发放的额外薪资'),
8. ('交通补贴', '用于员工上下班交通费用的补贴');
9. INSERT INTO SalaryStandards (item_id, amount) VALUES
10. (1, 5000.00), -- 基本工资
11. (2, 2000.00), -- 奖金
12. (3, 500.00);  -- 交通补贴
13. INSERT INTO SalaryDetails (employee_id, item_id, amount, payment_date) VALUES
14. (1, 1, 5000.00, '2023-04-30'), -- 孙悟空的基本工资
15. (1, 2, 2000.00, '2023-04-30'), -- 孙悟空的奖金
16. (1, 3, 500.00, '2023-04-30'),  -- 孙悟空的交通补贴
17. (2, 1, 5000.00, '2023-04-30'), -- 白骨精的基本工资
18. (2, 3, 500.00, '2023-04-30'),  -- 白骨精的交通补贴
19. (3, 1, 5000.00, '2023-04-30'), -- 猪八戒的基本工资
20. (3, 3, 500.00, '2023-04-30');  -- 猪八戒的交通补贴
21. INSERT INTO SalaryPayments (payment_date, total_amount) VALUES
22. ('2023-04-30', 15500.00), -- 假设总金额为所有员工薪资之和
23. ('2023-05-30', 15000.00), -- 假设5月份没有奖金，所以总金额减少
24. ('2023-06-30', 15500.00); -- 假设6月份又发放了奖金
25. INSERT INTO SalaryPaymentDetails (payment_id, employee_id, amount) VALUES
26. (1, 1, 7500.00), -- 孙悟空4月工资：基本工资 + 奖金 + 交通补贴
27. (1, 2, 5500.00), -- 白骨精4月工资：基本工资 + 交通补贴
28. (1, 3, 5500.00), -- 猪八戒4月工资：基本工资 + 交通补贴
29. (2, 1, 5500.00), -- 孙悟空5月工资：没有奖金
30. (2, 2, 5500.00), -- 白骨精5月工资
31. (2, 3, 5500.00), -- 猪八戒5月工资
32. (3, 1, 7500.00), -- 孙悟空6月工资：基本工资 + 奖金 + 交通补贴（假设再次发放奖金）
33. (3, 2, 5500.00); -- 白骨精6月工资

复制代码

五、三个简朴查询

1.查询名为孙悟空的员工薪资具体

1. SELECT e.name, sd.item_id, si.item_name, sd.amount, sd.payment_date
2. FROM Employees e
3. JOIN SalaryDetails sd ON e.employee_id = sd.employee_id
4. JOIN SalaryItems si ON sd.item_id = si.item_id
5. WHERE e.name = '孙悟空';

复制代码

2.查询每个薪资项目标平均工资

1. SELECT si.item_name AS 薪资项目名称, AVG(ss.amount) AS 平均薪资金额
2. FROM SalaryItems siJOIN SalaryStandards ss ON si.item_id = ss.item_id
3. GROUP BY si.item_id, si.item_name;
4.   

复制代码

3.查询每个岗位的平均薪资（仅看基本工资）

1. SELECT e.position, AVG(ss.amount) AS average_salary
2. FROM Employees eJOIN SalaryDetails sd ON e.employee_id = sd.employee_id JOIN SalaryStandards ss ON
3. sd.item_id = ss.item_id JOIN SalaryItems si ON ss.item_id = si.item_id
4. WHERE si.item_name = '基本工资'GROUP BY e.position;

复制代码

六、复杂查询

1.查询所有员工在指定月份的总薪资，包括基本工资，奖金和交通补贴

1. SELECT     e.name AS 员工姓名,    SUM(sd.amount) AS 总薪资FROM  Employees eJOIN     SalaryDetails sd
2. ON e.employee_id = sd.employee_idWHERE
3. YEAR(sd.payment_date) = 2023 AND MONTH(sd.payment_date) = 4GROUP
4. BY e.employee_id, e.nameORDER BY     总薪资 DESC;

复制代码

2.查询每个职位的平均薪资（仅包括基本工资）

1. SELECT     e.position AS 职位,    AVG(CASE WHEN si.item_name = '基本工资' THEN sd.amount ELSE 0 END) AS 平均基本工资FROM   
2.   Employees eJOIN     SalaryDetails sd ON e.employee_id = sd.employee_id
3. JOIN     SalaryItems si ON sd.item_id = si.item_id
4. GROUP BY     e.positionORDER BY     平均基本工资 DESC;

复制代码

3.查询每个员工的总薪资（包括所有薪资项目）

1. SELECT     e.name AS 员工姓名,    SUM(sd.amount) AS 总薪资
2. FROM     Employees eJOIN     SalaryDetails sd ON e.employee_id = sd.employee_id
3. GROUP BY     e.employee_id, e.nameORDER BY     总薪资 DESC;

复制代码

七、三个触发器和对应测试语句

1.在插入薪资时，确保薪资金额不高出薪资尺度

1. DELIMITER //CREATE TRIGGER trg_before_salary_details_insert
2. BEFORE INSERT ON SalaryDetailsFOR EACH ROWBEGIN  
3.   DECLARE std_amount DECIMAL(10, 2);
4.    SELECT amount INTO std_amount FROM SalaryStandards WHERE item_id = NEW.item_id;
5.    IF NEW.amount > std_amount THEN  
6.       SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '薪资金额不能超过薪资标准！';    END IF;END;//DELIMITER ;

复制代码

测试语句：尝试插入一个高出薪资尺度的薪资详情

1. INSERT INTO SalaryDetails (employee_id, item_id, amount, payment_date) VALUES (4, 1, 6000.00, '2023-07-01’);

复制代码

上面的测试语句应该会抛出一个错误，因为6000.00高出了基本工资的薪资尺度5000.00
2.在插入薪资付出具体时，主动更新薪资付出的总金额

1. DELIMITER //CREATE TRIGGER trg_after_salary_payment_details_insert
2. AFTER INSERT ON SalaryPaymentDetailsFOR EACH ROWBEGIN  
3.   UPDATE SalaryPayments    SET total_amount = total_amount + NEW.amount  
4.   WHERE payment_id = NEW.payment_id;END;//DELIMITER ;

复制代码

  n  测试语句：插入一个新的薪资付出详情，并检查薪资付出的总金额是否已更新

1. INSERT INTO SalaryPaymentDetails (payment_id, employee_id, amount) VALUES (4, 1, 2500.00);

复制代码

n -- 假设payment_id=4是一个新的付出ID-- 检查薪资付出的总金额是否已更新

1. SELECT * FROM SalaryPayments WHERE payment_id = 4;

复制代码

3.当员工离职时，主动删除其所有的薪资具体和薪资付出具体

1. DELIMITER //CREATE TRIGGER trg_after_employee_hire
2. AFTER INSERT ON EmployeesFOR EACH ROWBEGIN    -- 假设基本工资的item_id总是1   
3. INSERT INTO SalaryDetails (employee_id, item_id, amount, payment_date)  
4.   VALUES (NEW.employee_id, 1,
5. (SELECT amount FROM SalaryStandards WHERE item_id = 1), CURDATE());END;//DELIMITER ;

复制代码

    n   测试语句：-- 插入一个新员  

1. 工INSERT INTO Employees (name, gender, position, hire_date) VALUES('沙和尚', '男', '测试工程师', '2023-07-01

复制代码

  八、存储过程和对应测试语句

  

1. DELIMITER //CREATE TRIGGER trg_after_employee_hireAFTER INSERT ON EmployeesFOR EACH ROWBEGIN   
2. DECLARE base_salary_amount DECIMAL(10, 2);    -- 假设基本工资的item_id总是1，检查是否存在对应的薪资标准   
3. SELECT amount INTO base_salary_amount FROM SalaryStandards WHERE item_id = 1;    -- 检查是否成功获取到基本工资的金额   
4. IF base_salary_amount IS NOT NULL THEN        -- 如果成功获取到，则插入到SalaryDetails表中      
5. INSERT INTO SalaryDetails (employee_id, item_id, amount, payment_date)      
6. VALUES (NEW.employee_id, 1, base_salary_amount, CURDATE());    ELSE        -- 如果没有获取到基本工资的金额（可能是SalaryStandards表中没有对应的记录），则使用一个默认值        
7. INSERT INTO SalaryDetails (employee_id, item_id, amount, payment_date)        VALUES (NEW.employee_id, 1, 0.00, CURDATE());        -- 或者，你可以选择取消下面的注释来抛出一个错误        -- SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '基本工资的薪资标准未设置！';    END IF;END;//DELIMITER ;

复制代码

  测试语句：
  

1. INSERT INTO Employees (name, gender, position, hire_date) VALUES('沙和尚', '男', '测试工程师', '2023-07-01’);
2. SELECT * FROM SalaryDetails WHERE employee_id = (SELECT employee_id FROM Employees WHERE name = '沙和尚' LIMIT 1);

复制代码

  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。