工资管理系统

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

ER图

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

简单查询

查询性别为"男"的员工的姓名和入职日期 

SELECT * FROM employees WHERE employee_id = '男';

查询某个员工（例如employee_id为1）的所有薪资详情

SELECT * FROM SalaryDetails WHERE employee_id = 1;

多表联合复杂查询

查询每个员工的姓名、职位和他们的总薪资（包括所有薪资项目）

SELECT e.name, e.position, 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, e.position;

VISIO ER图

触发器

DELIMITER $$  -- 更改默认的语句分隔符为$$，这样可以在触发器内部使用分号
CREATE TRIGGER after_user_insert 
AFTER INSERT ON employees 
FOR EACH ROW  -- 触发器对每一行插入操作都执行一次
BEGIN  -- 触发器开始
    INSERT INTO employees (employee_id, position)  -- 在employeees表中插入一行数据
    VALUES (4, 经理);  -- 使用4,经理作为新插入行的employee_id，position为经理
END;  -- 触发器结束
$$  -- 触发器定义结束，使用新的分隔符
DELIMITER ;  -- 将语句分隔符改回为分号

存储过程

DELIMITER //

CREATE PROCEDURE GetEmployeeSalaryByMonth(IN empName VARCHAR(100), IN yearMonth DATE)
BEGIN
    -- 声明变量来存储薪资总额
    DECLARE totalSalary DECIMAL(10, 2) DEFAULT 0.00;

    -- 查询员工在指定月份的薪资总额
    SELECT SUM(amount)
    INTO totalSalary
    FROM SalaryDetails sd
    JOIN Employees e ON sd.employee_id = e.employee_id
    WHERE e.name = empName
    AND YEAR(sd.payment_date) = YEAR(yearMonth)
    AND MONTH(sd.payment_date) = MONTH(yearMonth);

    -- 返回薪资总额
    SELECT totalSalary AS TotalSalaryForMonth;
END //

DELIMITER ;

数据库模型图绘制