根据需求,写出适应本系统的10个sql语句
时间: 2023-08-11 07:41:12 浏览: 45
根据人力资源管理系统的需求,以下是适用于该系统的10个 SQL 语句:
1. 查询所有员工信息
```
SELECT * FROM employee_info;
```
2. 查询所有部门信息
```
SELECT * FROM department_info;
```
3. 查询所有员工的薪资信息
```
SELECT e.name AS employee_name, s.salary
FROM employee_info e
JOIN salary_info s ON e.id = s.employee_id;
```
4. 查询某个部门中所有员工的薪资信息
```
SELECT e.name AS employee_name, s.salary
FROM employee_info e
JOIN salary_info s ON e.id = s.employee_id
WHERE e.department_id = 1; -- 1 为部门 ID
```
5. 查询某个员工的薪资信息
```
SELECT e.name AS employee_name, s.salary
FROM employee_info e
JOIN salary_info s ON e.id = s.employee_id
WHERE e.id = 1; -- 1 为员工 ID
```
6. 查询某个员工的所属部门信息
```
SELECT e.name AS employee_name, d.name AS department_name
FROM employee_info e
JOIN department_info d ON e.department_id = d.id
WHERE e.id = 1; -- 1 为员工 ID
```
7. 查询某个部门的所有员工及其薪资信息
```
SELECT e.name AS employee_name, s.salary
FROM employee_info e
JOIN salary_info s ON e.id = s.employee_id
WHERE e.department_id = 1; -- 1 为部门 ID
```
8. 查询某个部门的平均薪资
```
SELECT AVG(salary) AS avg_salary
FROM salary_info
WHERE employee_id IN (
SELECT id
FROM employee_info
WHERE department_id = 1 -- 1 为部门 ID
);
```
9. 插入一条员工信息
```
INSERT INTO employee_info (name, gender, birthday, department_id, position, hire_date)
VALUES ('张三', '男', '1990-01-01', 1, '经理', '2020-01-01');
```
10. 更新某个员工的薪资信息
```
UPDATE salary_info
SET salary = 8000
WHERE employee_id = 1 AND start_date = '2021-01-01'; -- 1 为员工 ID
```