小组作业 工资管理系统

已于 2024-06-28 16:39:58 修改
阅读量456 收藏
点赞数 8
文章标签： 数据库 oracle linux
于 2024-06-26 15:37:34 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2301_82209311/article/details/139963160
版权

1.ER图

数据库模型ER图

2.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月工资

3.简单查询与多表联合复杂查询

简单查询

查询1: 查询所有员工的姓名和职位。

SELECT name, position FROM Employees;

运行结果：

查询2: 查询薪资项目为"基本工资"的金额。

SELECT amount FROM SalaryStandards WHERE item_id = (SELECT item_id FROM SalaryItems WHERE item_name = '基本工资');

 

查询3: 查询2023年5月份支付的总金额。

SELECT total_amount FROM SalaryPayments WHERE payment_date = '2023-05-30';

 

复杂查询 

查询1: 查询每个员工的总薪资（假设为所有薪资项目的总和）。
 

SELECT e.name, SUM(sd.amount) AS total_salary
FROM Employees e
JOIN SalaryDetails sd ON e.employee_id = sd.employee_id
GROUP BY e.employee_id, e.name;

 

查询2: 查询没有领取过奖金的员工姓名。

SELECT name
FROM Employees e
LEFT JOIN SalaryDetails sd ON e.employee_id = sd.employee_id AND sd.item_id = (SELECT item_id FROM SalaryItems WHERE item_name = '奖金')
WHERE sd.detail_id IS NULL;

查询3: 查询每个职位的平均薪资（假设为所有员工该职位薪资的总和的平均值）。 

SELECT e.position, AVG(sd.amount) AS average_salary
FROM Employees e
JOIN SalaryDetails sd ON e.employee_id = sd.employee_id
GROUP BY e.position;

4.触发器

1-插入 -

-- 插入触发器：SalaryPayments表中插入新的支付记录会使SalaryPaymentDetails表中为每个员工插入相应的支付详情

DELIMITER //
CREATE TRIGGER after_salary_payment_insert
AFTER INSERT ON SalaryPayments
FOR EACH ROW
BEGIN
    INSERT INTO SalaryPaymentDetails (payment_id, employee_id, amount)
    SELECT NEW.payment_id, e.employee_id, sd.amount
    FROM Employees e
    JOIN SalaryDetails sd ON e.employee_id = sd.employee_id
    WHERE e.name = '孙悟空';
END //
DELIMITER ;

测试语句

INSERT INTO SalaryPayments (payment_date, total_amount) VALUES ('2023-07-30', 7500.00);
SELECT * FROM SalaryPaymentDetails WHERE payment_id = 4;

2-修改-

-- 修改触发器:SalaryPayments表中的total_amount字段被更新时SalaryPaymentDetails表中对应支付ID的孙悟空的支付金额也会被更新

DELIMITER //
CREATE TRIGGER after_salary_payment_update
AFTER UPDATE ON SalaryPayments
FOR EACH ROW
BEGIN
    UPDATE SalaryPaymentDetails
    SET amount = NEW.total_amount
    WHERE payment_id = NEW.payment_id AND employee_id = (SELECT employee_id FROM Employees WHERE name = '孙悟空');
END //
DELIMITER ;

测试语句

UPDATE SalaryPayments SET total_amount = 8000.00 WHERE payment_id = 3;
select * FROM SalaryPaymentDetails WHERE payment_id = 3;

-删除-

-- 删除触发器：SalaryPayments表中删除支付记录时SalaryPaymentDetails表中所有对应的支付详情也会被删除

DELIMITER //
CREATE TRIGGER after_salary_payment_delete
AFTER DELETE ON SalaryPayments
FOR EACH ROW
BEGIN
    DELETE FROM SalaryPaymentDetails WHERE payment_id = OLD.payment_id;
END //
DELIMITER ;

测试语句

DELETE FROM SalaryPayments WHERE payment_id = 3;
select * from SalaryPaymentDetails where payment_id = 3;

5.存储过程

DELIMITER //
CREATE PROCEDURE InsertSalaryPayments(IN paymentDate DATE)
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE curEmployeeId, curItemId, curAmount DECIMAL(10, 2);
    DECLARE curEmployeeCursor CURSOR FOR 
        SELECT employee_id, item_id, amount 
        FROM SalaryDetails 
        WHERE payment_date = (SELECT MAX(payment_date) FROM SalaryDetails);
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    -- 插入 SalaryPayments 记录
    INSERT INTO SalaryPayments (payment_date, total_amount)
    SELECT paymentDate, SUM(amount) AS total_amount
    FROM SalaryDetails
    WHERE payment_date = (SELECT MAX(payment_date) FROM SalaryDetails);

    -- 获取新插入的 SalaryPayments 的 ID
    SET @lastPaymentId = LAST_INSERT_ID();

    -- 打开游标以遍历员工薪资详情
    OPEN curEmployeeCursor;
    read_loop: LOOP
        FETCH curEmployeeCursor INTO curEmployeeId, curItemId, curAmount;
        IF done THEN
            LEAVE read_loop;
        END IF;

        -- 插入 SalaryPaymentDetails 记录
        INSERT INTO SalaryPaymentDetails (payment_id, employee_id, amount)
        VALUES (@lastPaymentId, curEmployeeId, curAmount);
    END LOOP;
    CLOSE curEmployeeCursor;
END //
DELIMITER ;
要使用此存储过程，您可以调用它并传入您想要插入的支付日期，例如：

sql
CALL InsertSalaryPayments('2023-07-30');
这将会：

在SalaryPayments表中插入一个新的支付记录，其中payment_date是传入的日期，total_amount是基于当前最新的SalaryDetails数据计算得出的。

为每个员工在SalaryPaymentDetails表中插入一条记录，分配他们各自的薪资。

工资管理系统的存储过程主要就是用于记录员工的薪资分配，计算薪资，管理薪资发放等功能。 基于用户的需求，先创建一个名为SalaryPayments的存储过程，然后再为这个存储过程添加一些必要的参数内容，例如：payment，total amount等，然后根据需要的信息再插入薪资项目表，薪资标准表，薪资详情表等信息

羊皮6
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值