这个是具体要求:系统概述:通过对人事管理管理部门中的职称、工资、员工、部门、工资类别等相关内容 进行分析,完成具有人员管理、工资管理、部门管理等相关功能的小型数据库管理应用系统, 系统需要具备增减工资中应发、应扣类别的灵活性。
时间: 2023-11-27 22:04:00 浏览: 52
好的,根据具体要求,以下是一个更详细的工资管理系统的数据库设计和操作语句:
1. 员工表(employee)
- id:员工编号(主键)
- name:员工姓名
- gender:员工性别
- hire_date:入职日期
- department_id:所属部门编号(外键)
- position:职位
- basic_salary:基本工资
2. 部门表(department)
- id:部门编号(主键)
- name:部门名称
3. 工资类别表(salary_category)
- id:工资类别编号(主键)
- name:工资类别名称
- is_deduction:是否为扣除项(0为应发项,1为应扣项)
- is_default:是否为默认工资类别(0为非默认项,1为默认项)
4. 工资表(salary)
- id:工资编号(主键)
- employee_id:员工编号(外键)
- salary_date:发放日期
- basic_salary:基本工资
- total_salary:实际发放工资
- deduction_salary:应扣工资
- payment_salary:应发工资
- remark:备注
5. 员工工资类别表(employee_salary_category)
- id:关系编号(主键)
- employee_id:员工编号(外键)
- salary_category_id:工资类别编号(外键)
- is_default:是否为默认项(0为非默认项,1为默认项)
根据上述设计,可以进行以下操作:
1. 添加员工信息
```sql
INSERT INTO employee (id, name, gender, hire_date, department_id, position, basic_salary) VALUES ('001', '张三', '男', '2021-01-01', '001', '经理', 10000);
```
2. 修改员工信息
```sql
UPDATE employee SET name='李四', position='部门经理' WHERE id='001';
```
3. 删除员工信息
```sql
DELETE FROM employee WHERE id='001';
```
4. 添加部门信息
```sql
INSERT INTO department (id, name) VALUES ('001', '销售部');
```
5. 修改部门信息
```sql
UPDATE department SET name='市场部' WHERE id='001';
```
6. 删除部门信息
```sql
DELETE FROM department WHERE id='001';
```
7. 添加工资类别
```sql
INSERT INTO salary_category (id, name, is_deduction, is_default) VALUES ('001', '基本工资', 0, 1);
```
8. 修改工资类别
```sql
UPDATE salary_category SET name='福利补贴', is_deduction=0 WHERE id='001';
```
9. 删除工资类别
```sql
DELETE FROM salary_category WHERE id='001';
```
10. 发放工资
```sql
INSERT INTO salary (id, employee_id, salary_date, basic_salary, total_salary, deduction_salary, payment_salary, remark) VALUES ('001', '001', '2021-01-31', 10000, 9000, 1000, 10000, '正常发放');
```
11. 查看员工工资信息
```sql
SELECT employee.id, employee.name, salary.salary_date, salary.basic_salary, salary.total_salary, salary.deduction_salary, salary.payment_salary, salary.remark FROM employee INNER JOIN salary ON employee.id=salary.employee_id WHERE employee.id='001';
```
12. 查看部门工资信息
```sql
SELECT department.name, SUM(salary.payment_salary) AS total_salary FROM employee INNER JOIN department ON employee.department_id=department.id INNER JOIN salary ON employee.id=salary.employee_id GROUP BY department.id;
```
13. 添加员工工资类别
```sql
INSERT INTO employee_salary_category (id, employee_id, salary_category_id, is_default) VALUES ('001', '001', '001', 1);
```
14. 修改员工工资类别
```sql
UPDATE employee_salary_category SET is_default=0 WHERE id='001';
```
15. 删除员工工资类别
```sql
DELETE FROM employee_salary_category WHERE id='001';
```
16. 添加工资类别
```sql
INSERT INTO salary_category (id, name, is_deduction, is_default) VALUES ('002', '奖金', 0, 0);
```
17. 修改工资类别
```sql
UPDATE salary_category SET name='绩效奖金', is_deduction=0, is_default=0 WHERE id='002';
```
18. 删除工资类别
```sql
DELETE FROM salary_category WHERE id='002';
```
以上为示例操作语句,实际使用时需要根据具体需求进行修改。
相关推荐
![ppt](https://img-home.csdnimg.cn/images/20210720083527.png)
![doc](https://img-home.csdnimg.cn/images/20210720083327.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)
![](https://csdnimg.cn/download_wenku/file_type_ask_c1.png)