信息系统开发编写工资管理系统sql
时间: 2023-07-31 10:01:22 浏览: 42
开发工资管理系统的SQL语句可以包括以下内容:
1. 建立员工表(Employee):
CREATE TABLE Employee (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
position VARCHAR(50) NOT NULL,
department VARCHAR(50) NOT NULL
);
2. 建立工资表(Salary):
CREATE TABLE Salary (
id INT PRIMARY KEY AUTO_INCREMENT,
employee_id INT NOT NULL,
month VARCHAR(20) NOT NULL,
wage DECIMAL(10, 2) NOT NULL,
FOREIGN KEY (employee_id) REFERENCES Employee(id)
);
3. 插入员工数据:
INSERT INTO Employee(name, position, department) VALUES('张三', '经理', '销售部');
INSERT INTO Employee(name, position, department) VALUES('李四', '销售员', '销售部');
4. 插入工资数据:
INSERT INTO Salary(employee_id, month, wage) VALUES(1, '2021-01', 5000);
INSERT INTO Salary(employee_id, month, wage) VALUES(2, '2021-01', 3000);
5. 查询某个员工的工资记录:
SELECT e.name, s.month, s.wage
FROM Employee e
INNER JOIN Salary s ON e.id = s.employee_id
WHERE e.name = '张三';
6. 计算某个部门的总工资:
SELECT e.department, SUM(s.wage) AS total_wage
FROM Employee e
INNER JOIN Salary s ON e.id = s.employee_id
GROUP BY e.department;
7. 更新某个员工的工资:
UPDATE Salary
SET wage = 6000
WHERE employee_id = 1 AND month = '2021-01';
以上是一个简单的工资管理系统的SQL示例,根据具体需求和系统设计,还可以进行更多扩展和优化。