ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL工资管理系统 [打印本页]
作者: 科技颠覆者    时间: 2024-12-25 22:46
标题: 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企服之家，中国第一个企服评测及商务社交产业平台。



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4