工资管理系统mysql代码

工资管理系统是一个复杂的数据库应用，通常涉及到员工信息、薪资结构、考勤记录等多个模块的数据存储和处理。在MySQL中编写这样的代码涉及到创建表、插入数据、查询操作等。以下是一个简化版的示例，展示了如何创建几个基本表和执行一些基础操作：

-- 创建员工表 (Employees)
CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    Name VARCHAR(50),
    Department VARCHAR(50),
    Position VARCHAR(50),
    HireDate DATE
);

-- 创建薪资结构表 (SalaryStructures)
CREATE TABLE SalaryStructures (
    ID INT PRIMARY KEY,
    Title VARCHAR(50),
    BaseSalary DECIMAL(10, 2),
    BonusPercentage DECIMAL(3, 2)
);

-- 创建工资记录表 (SalaryRecords)
CREATE TABLE SalaryRecords (
    RecordID INT PRIMARY KEY,
    EmployeeID INT,
    SalaryStructureID INT,
    GrossSalary DECIMAL(10, 2),
    PayDate DATE,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(ID),
    FOREIGN KEY (SalaryStructureID) REFERENCES SalaryStructures(ID)
);

-- 插入示例数据
INSERT INTO Employees (ID, Name, Department, Position, HireDate) VALUES
(1, 'John Doe', 'IT', 'Developer', '2022-01-01'),
(2, 'Jane Smith', 'Finance', 'Analyst', '2022-02-01');

INSERT INTO SalaryStructures (ID, Title, BaseSalary, BonusPercentage) VALUES
(1, 'Basic', 5000, 0.1),
(2, 'Senior', 8000, 0.15);

INSERT INTO SalaryRecords (RecordID, EmployeeID, SalaryStructureID, GrossSalary, PayDate) VALUES
(1, 1, 1, 5000, '2022-03-01'),
(2, 2, 2, 9000, '2022-04-01');

-- 查询示例
-- 查询某个员工的所有工资记录
SELECT * FROM SalaryRecords WHERE EmployeeID = (SELECT ID FROM Employees WHERE Name = 'John Doe');

-- 查询某个职位的平均薪资
SELECT AVG(GrossSalary) FROM SalaryRecords WHERE SalaryStructureID IN (
    SELECT ID FROM SalaryStructures WHERE Title = 'Developer'
);