MySQL工资管理系统

不做林黛玉

已于 2024-06-28 15:50:17 修改

阅读量483

点赞数 3

文章标签： mysql android 数据库

于 2024-06-27 23:14:58 首次发布

本文链接：https://blog.csdn.net/2301_80147875/article/details/140026150

版权

前言：

• 工资管理系统是一个用于记录员工薪资信息、计算薪资、管理薪资发放等功能的系统。该系统旨在帮助企业高效、准确地处理员工的薪资数据，并提供方便的查询和报表功能。

• 系统的主要功能包括：员工信息管理：记录员工的基本信息，如姓名、性别、职位等。

• 薪资项目设置：定义薪资构成项目，如基本工资、奖金、津贴等。

• 薪资发放管理：记录薪资发放记录，包括发放时间、发放金额等。

• 薪资计算：根据员工的薪资项目和考勤数据，自动计算员工的薪资总额。

• 报表生成：生成薪资明细报表、薪资汇总报表等，方便管理人员进行统计分析

一、ER图

二、数据库模型图

三、DDL

CREATE TABLE Employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '员工ID',
    name VARCHAR(100) NOT NULL COMMENT '员工姓名',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    position VARCHAR(100) NOT NULL COMMENT '职位',
    hire_date DATE NOT NULL COMMENT '入职日期'
);
 
CREATE TABLE SalaryItems (
    item_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '薪资项目ID',
    item_name VARCHAR(100) NOT NULL COMMENT '薪资项目名称',
    description VARCHAR(255) COMMENT '薪资项目描述'
);
 
CREATE TABLE SalaryStandards (
    standard_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '薪资标准ID',
    item_id INT NOT NULL COMMENT '薪资项目ID',
    amount DECIMAL(10, 2) NOT NULL COMMENT '薪资金额',
    FOREIGN KEY (item_id) REFERENCES SalaryItems(item_id)
);
 
CREATE TABLE SalaryDetails (
    detail_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '薪资详情ID',
    employee_id INT NOT NULL COMMENT '员工ID',
    item_id INT NOT NULL COMMENT '薪资项目ID',
    amount DECIMAL(10, 2) NOT NULL COMMENT '薪资金额',
    payment_date DATE NOT NULL COMMENT '支付日期',
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id),
    FOREIGN KEY (item_id) REFERENCES SalaryItems(item_id)
);
 
CREATE TABLE SalaryPayments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '支付ID',
    payment_date DATE NOT NULL COMMENT '支付日期',
    total_amount DECIMAL(10, 2) NOT NULL COMMENT '总金额'
);
 
CREATE TABLE SalaryPaymentDetails (
    detail_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '支付详情ID',
    payment_id INT NOT NULL COMMENT '支付ID',
    employee_id INT NOT NULL COMMENT '员工ID',
    amount DECIMAL(10, 2) NOT NULL COMMENT '支付金额',
    FOREIGN KEY (payment_id) REFERENCES SalaryPayments(payment_id),
    FOREIGN KEY (employee_id) REFERENCES Employees(employee_id)
);

四、DML

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

五、三个简单查询

1.查询名为孙悟空的员工薪资详细

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

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

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

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

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

六、复杂查询

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

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

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

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

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

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

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

1.在插入薪资时，确保薪资金额不超过薪资标准

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

测试语句：尝试插入一个超过薪资标准的薪资详情

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

上面的测试语句应该会抛出一个错误，因为6000.00超过了基本工资的薪资标准5000.00

2.在插入薪资支付详细时，自动更新薪资支付的总金额

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

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

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

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

SELECT * FROM SalaryPayments WHERE payment_id = 4;

3.当员工离职时，自动删除其所有的薪资详细和薪资支付详细

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

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

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

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

DELIMITER //CREATE TRIGGER trg_after_employee_hireAFTER INSERT ON EmployeesFOR EACH ROWBEGIN    
DECLARE base_salary_amount DECIMAL(10, 2);    -- 假设基本工资的item_id总是1，检查是否存在对应的薪资标准    
SELECT amount INTO base_salary_amount FROM SalaryStandards WHERE item_id = 1;    -- 检查是否成功获取到基本工资的金额   
 IF base_salary_amount IS NOT NULL THEN        -- 如果成功获取到，则插入到SalaryDetails表中       
 INSERT INTO SalaryDetails (employee_id, item_id, amount, payment_date)       
 VALUES (NEW.employee_id, 1, base_salary_amount, CURDATE());    ELSE        -- 如果没有获取到基本工资的金额（可能是SalaryStandards表中没有对应的记录），则使用一个默认值        
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 ;

测试语句：

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