岗位实习---工资管理表

一、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` VALUES (1, '张三', '男', '程序员', '2020-1-1');
INSERT INTO `employees` VALUES (2, '李四', '女', '产品经理', '2020-2-15');
INSERT INTO `employees` VALUES (3, '王五', '男', 'UI设计师', '2020-3-8');

INSERT INTO `salarydetails` VALUES (1, 1, 1, 5000.00, '2023-4-30');
INSERT INTO `salarydetails` VALUES (2, 1, 2, 2000.00, '2023-4-30');
INSERT INTO `salarydetails` VALUES (3, 1, 3, 500.00, '2023-4-30');
INSERT INTO `salarydetails` VALUES (4, 2, 1, 5000.00, '2023-4-30');
INSERT INTO `salarydetails` VALUES (5, 2, 3, 500.00, '2023-4-30');
INSERT INTO `salarydetails` VALUES (6, 3, 1, 5000.00, '2023-4-30');
INSERT INTO `salarydetails` VALUES (7, 3, 3, 500.00, '2023-4-30');

INSERT INTO `salaryitems` VALUES (1, '基本工资', '员工的基本薪资');
INSERT INTO `salaryitems` VALUES (2, '奖金', '根据业绩发放的额外薪资');
INSERT INTO `salaryitems` VALUES (3, '交通补贴', '用于员工上下班交通费用的补贴');

INSERT INTO `salarypaymentdetails` VALUES (1, 1, 1, 7500.00);
INSERT INTO `salarypaymentdetails` VALUES (2, 1, 2, 5500.00);
INSERT INTO `salarypaymentdetails` VALUES (3, 1, 3, 5500.00);
INSERT INTO `salarypaymentdetails` VALUES (4, 2, 1, 5500.00);
INSERT INTO `salarypaymentdetails` VALUES (5, 2, 2, 5500.00);
INSERT INTO `salarypaymentdetails` VALUES (6, 2, 3, 5500.00);
INSERT INTO `salarypaymentdetails` VALUES (7, 3, 1, 7500.00);
INSERT INTO `salarypaymentdetails` VALUES (8, 3, 2, 5500.00);

INSERT INTO `salarypayments` VALUES (1, '2023-4-30', 15500.00);
INSERT INTO `salarypayments` VALUES (2, '2023-5-30', 15000.00);
INSERT INTO `salarypayments` VALUES (3, '2023-6-30', 15500.00);

INSERT INTO `salarystandards` VALUES (1, 1, 5000.00);
INSERT INTO `salarystandards` VALUES (2, 2, 2000.00);
INSERT INTO `salarystandards` VALUES (3, 3, 500.00);

三、ER图和模型图

四、DQL

简单查询

复杂查询

触发器

存储过程