MySQL工资管理系统
前言：•工资管理系统是一个用于记录员工薪资信息、计算薪资、管理薪资发放等功能的系统。该系统旨在资助企业高效、正确地处理员工的薪资数据，并提供方便的查询和报表功能。 •系统的重要功能包括：员工信息管理：记录员工的基本信息，如姓名、性别、职位等。 •薪资项目设置：定义薪资构成项目，如基本工资、奖金、补助等。 •薪资发放管理：记录薪资发放记录，包括发放时间、发放金额等。 •薪资计算：根据员工的薪资项目和考勤数据，主动计算员工的薪资总额。 •报表生成：生成薪资明细报表、薪资汇总报表等，方便管理人员举行统计分析 一、ER图
https://i-blog.csdnimg.cn/blog_migrate/0022bc61dcad8df70dbc45a7ea2b32d5.png
二、数据库模型图
https://i-blog.csdnimg.cn/blog_migrate/064d76c985879f103fbe1ed4ea715778.png
三、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 = '孙悟空';
https://i-blog.csdnimg.cn/blog_migrate/6621c44436e56183542d0605d00c7510.png
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;
https://i-blog.csdnimg.cn/blog_migrate/28a0afb4b1d83cd11bebe69977fd641e.png
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;
https://i-blog.csdnimg.cn/blog_migrate/6d482ada01b4b4a74785c9f3c9a4bdae.png
六、复杂查询
1.查询所有员工在指定月份的总薪资，包括基本工资，奖金和交通补贴
SELECT e.name AS 员工姓名, SUM(sd.amount) AS 总薪资FROMEmployees 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;
https://i-blog.csdnimg.cn/blog_migrate/8d247f8cb69aa742f6dd9e0284656fae.png
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;
https://i-blog.csdnimg.cn/blog_migrate/292ade718b914614acc57cafb809ce8a.png
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;
https://i-blog.csdnimg.cn/blog_migrate/7506db118dfea096398c074696d3b171.png
七、三个触发器和对应测试语句
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);
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]