【无标题】

神威难藏泪！

已于 2024-06-27 22:50:45 修改

阅读量320

点赞数 4

文章标签： java 前端数据库 mysql sql

于 2024-06-26 19:49:26 首次发布

本文链接：https://blog.csdn.net/2301_80888400/article/details/139994820

版权

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 '入职日期',
    phone_number VARCHAR(20) NOT NULL COMMENT '手机号码'
) 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, phone_number) VALUES
('孙悟空', '男', '程序员', '2020-01-01','13000000001'),
('白骨精', '女', '产品经理', '2020-02-15','13000000002'),
('猪八戒', '男', 'UI设计师', '2020-03-08','13000000003'),
('沙僧', '男', '后端工程师', '2020-04-10', '13000000004'),
('唐僧', '男', '项目经理', '2020-01-15', '13000000005');
INSERT INTO SalaryItems (item_name, description) VALUES
('基本工资', '员工的基本薪资'),
('奖金', '根据业绩发放的额外薪资'),
('交通补贴', '用于员工上下班交通费用的补贴'),
('餐补', '员工餐补'),
('通讯补贴', '员工通讯费用补贴');
INSERT INTO SalaryStandards (item_id, amount) VALUES
(1, 5000.00), -- 基本工资
(2, 2000.00), -- 奖金
(3, 500.00),  -- 交通补贴
(4, 300.00),  -- 餐补
(5, 200.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'),  -- 猪八戒的交通补贴
(3, 4, 300.00, '2023-04-30'),  -- 猪八戒的餐补
(4, 1, 5000.00, '2023-04-30'), -- 沙僧的基本工资
(4, 3, 500.00, '2023-04-30'),  -- 沙僧的交通补贴
(4, 4, 300.00, '2023-04-30'),  -- 沙僧的餐补
(5, 1, 5000.00, '2023-04-30'), -- 唐僧的基本工资
(5, 3, 500.00, '2023-04-30'),  -- 唐僧的交通补贴
(5, 4, 300.00, '2023-04-30'),  -- 唐僧的餐补
(5, 5, 200.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月份又发放了奖金
('2023-07-30', 16000.00), -- 假设7月份增加了某种形式的福利或津贴
('2023-08-30', 15200.00);  -- 假设8月份薪资微调，总金额略有下降
INSERT INTO SalaryPaymentDetails (payment_id, employee_id, amount) VALUES
(1, 1, 7500.00), -- 孙悟空4月工资：基本工资 + 奖金 + 交通补贴
(1, 2, 5500.00), -- 白骨精4月工资：基本工资 + 交通补贴
(1, 3, 5800.00), -- 猪八戒4月工资：基本工资 + 交通补贴 + 餐补
(1, 4, 5800.00), -- 沙僧4月工资：基本工资 + 交通补贴 + 餐补
(1, 5, 5800.00), -- 唐僧4月工资：基本工资 + 交通补贴 + 餐补 + 通讯补贴
(2, 1, 5500.00), -- 孙悟空5月工资：没有奖金
(2, 2, 5500.00), -- 白骨精5月工资
(2, 3, 5800.00), -- 猪八戒5月工资
(2, 4, 5800.00), -- 沙僧5月工资
(2, 5, 5800.00), -- 唐僧5月工资
(3, 1, 7500.00), -- 孙悟空6月工资：基本工资 + 奖金 + 交通补贴（假设再次发放奖金）
(3, 2, 5500.00), -- 白骨精6月工资
(3, 3, 5800.00), -- 猪八戒6月工资
(3, 4, 5800.00), -- 沙僧6月工资
(3, 5, 5800.00); -- 唐僧6月工资

基础查询

-- 中文显示姓名列与手机号
SELECT `name` AS '姓名',phone_number AS '手机号' FROM employees;
-- 根据名称进行模糊查询
EXPLAIN SELECT * FROM employees WHERE employee_id LIKE '僧%';
-- 统计每个员工的薪资支付详情条数，倒序排列结果
SELECT 
    spd.employee_id,
    COUNT(*) AS '支付条数'
FROM 
    SalaryPaymentDetails spd
GROUP BY 
    spd.employee_id
ORDER BY 
    '支付条数' DESC;

复杂查询

1、多表联合查询查询每个员工的姓名、职位和基本工资

--多表联合查询查询每个员工的姓名、职位和基本工资
SELECT 
    e.name AS employee_name, -- 员工姓名
    e.position AS employee_position, -- 职位
    sd.amount AS basic_salary -- 基本工资
FROM 
    Employees e -- 从员工表中选择数据
JOIN 
    SalaryDetails sd ON e.employee_id = sd.employee_id -- 根据员工ID连接员工表和薪资详情表
JOIN 
    SalaryItems si ON sd.item_id = si.item_id AND si.item_name = '基本工资' -- 连接薪资详情表和薪资项目表，并筛选基本工资项目
WHERE 
    sd.payment_date = '2023-04-30'; -- 如果需要指定某个支付日期的数据，可以添加此条件

2、使用聚合函数，子查询查询工资最高的职业

--使用聚合函数，子查询查询工资最高的职业
SELECT 
    e.position AS Highest_Paying_Position
FROM 
    Employees e
JOIN (
    SELECT 
        position, -- 选择职位
        SUM(amount) AS total_salary -- 计算每个职位的总薪资
    FROM 
        SalaryDetails sd
    JOIN 
        Employees e ON sd.employee_id = e.employee_id -- 将SalaryDetails表和Employees表通过员工ID关联起来
    GROUP BY 
        e.position -- 按职位分组
    ORDER BY 
        total_salary DESC -- 按总薪资降序排列
    LIMIT 1 -- 只选择薪资最高的那个职位
) AS highest_salary_positions ON e.position = highest_salary_positions.position; -- 将外部查询的结果与Employees表连接，找到薪资最高的那个职位的具体名称

3、使用聚合函数子查询多表联合查询选择员工姓名和他们在2023年4月的总支付金额

-- 使用聚合函数子查询多表联合查询选择员工姓名和他们在2023年4月的总支付金额 
SELECT 
    e.name AS employee_name,          -- 从Employees表中选择员工的姓名，并将其命名为employee_name
    COALESCE(p.total_payment_for_april_2023, 0) AS total_payment_for_april_2023 -- 使用COALESCE函数确保即使某个员工没有支付记录，总金额也返回0，而不是NULL。从子查询p中选择total_payment_for_april_2023，并将其命名为total_payment_for_april_2023
FROM 
    Employees e  -- 选择Employees表，并为其起一个别名e
LEFT JOIN (
    -- 这是一个子查询，用于计算每个员工在2023年4月的总支付金额
    SELECT 
        spd.employee_id,              -- 从SalaryPaymentDetails表中选择employee_id
        SUM(spd.amount) AS total_payment_for_april_2023 -- 计算每个员工的总支付金额，并将其命名为total_payment_for_april_2023
    FROM 
        SalaryPaymentDetails spd      -- 选择SalaryPaymentDetails表
    INNER JOIN 
        SalaryPayments sp ON spd.payment_id = sp.payment_id  -- 将SalaryPaymentDetails表和SalaryPayments表通过payment_id字段进行内连接
    WHERE 
        sp.payment_date BETWEEN '2023-04-01' AND '2023-04-30'  -- 筛选出2023年4月1日至2023年4月30日之间的支付记录
    GROUP BY 
        spd.employee_id                -- 按employee_id分组，计算每个员工的总支付金额
) p ON e.employee_id = p.employee_id   -- 将Employees表与子查询p进行左连接，基于employee_id字段进行匹配
ORDER BY 
    e.name;                            -- 按员工姓名进行排序

触发器

--工资表修改语句添加触发器，要求在修改基础工资时不允许上下浮动超过10%
DELIMITER //
CREATE TRIGGER trg_check_basic_salary_change
BEFORE UPDATE ON SalaryStandards
FOR EACH ROW
BEGIN
    DECLARE old_amount DECIMAL(10, 2);
    DECLARE new_amount DECIMAL(10, 2);
    DECLARE change_percentage DECIMAL(5, 2);

    -- 获取旧的基本工资和新的基本工资
    SET old_amount = OLD.amount;
    SET new_amount = NEW.amount;

    -- 计算变化的百分比
    IF old_amount = 0 THEN
        -- 如果旧的基本工资为0，则不进行百分比计算，因为除数为0
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot calculate percentage change when old amount is zero.';
    ELSE
        SET change_percentage = (new_amount - old_amount) / old_amount * 100;

        -- 检查百分比是否在允许的范围内（上下浮动不超过10%）
        IF change_percentage < -10 OR change_percentage > 10 THEN
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Percentage change of basic salary exceeds 10%.';
        END IF;
    END IF;
END //
DELIMITER ;
--SalaryPayments表删除语句添加触发器，要求在删除信息时先删除表中SalaryPayments的工资信息。
DELIMITER //
CREATE TRIGGER trg_delete_salary_payment_details
BEFORE DELETE ON SalaryPayments
FOR EACH ROW
BEGIN
    -- 在删除SalaryPayments的记录之前，先删除SalaryPaymentDetails中对应的记录
    DELETE FROM SalaryPaymentDetails WHERE payment_id = OLD.payment_id;
END //
DELIMITER ;

7.存储

DELIMITER //


-- 创建名为create_order_infos的存储过程，该过程用于添加薪资详情条目和更新薪资标准
CREATE PROCEDURE create_order_infos(
    -- 第一个员工的ID、薪资项目ID、薪资金额和支付日期
    IN employee_id_1 INT, item_id_1 INT, amount_1 DECIMAL(10, 2), payment_date_1 DATE,
    -- 第二个员工的ID、薪资项目ID、薪资金额和支付日期
    IN employee_id_2 INT, item_id_2 INT, amount_2 DECIMAL(10, 2), payment_date_2 DATE,
    -- 要更新薪资标准的员工的ID、薪资项目ID和新的薪资金额
    IN employee_id_update INT, item_id_update INT, new_amount DECIMAL(10, 2)
)
BEGIN
    -- 添加两个新的薪资详情条目到SalaryDetails表中
    -- 第一个员工的薪资详情
    -- 添加两个新的薪资详情条目
    INSERT INTO SalaryDetails (employee_id, item_id, amount, payment_date)
    VALUES
    (employee_id_1, item_id_1, amount_1, payment_date_1),
    (employee_id_2, item_id_2, amount_2, payment_date_2);

    -- 更新一个员工的薪资标准
    -- 只更新那些已经在SalaryDetails表中存在对应员工和薪资项目条目的薪资标准
    UPDATE SalaryStandards
    SET amount = new_amount
    WHERE item_id = item_id_update AND EXISTS (
        SELECT 1 FROM SalaryDetails
        WHERE SalaryDetails.employee_id = employee_id_update AND SalaryDetails.item_id = item_id_update
    );

    -- 如果需要，这里可以添加错误处理或输出信息
    -- 例如，可以使用SIGNAL语句来抛出异常，或者使用SELECT语句来输出调试信息
    -- 这里暂未添加
END //


-- 结束存储过程定义的定界符
DELIMITER ;

测试语句

CALL create_order_infos(
    6, 1, 5500.00, '2023-09-30', -- 假设添加唐僧的一个新薪资详情条目
    7, 2, 1000.00, '2023-09-30', -- 假设为某个新员工（ID为7）添加奖金
    1, 1, 5200.00               -- 假设更新孙悟空的基本工资为5200.00
);