数据库_财务管理系统任务

一、任务介绍

财务管理系统：为公司的财务部门进行财务管理的。下面是项目所需的数据表和功能需求；

数据表:

• 雇员表(employee):
  • 雇员编号(empid,主键)，
  • 姓名(name)，
  • 性别(sex)，
  • 职称(title)，
  • 出生日期(birthday)，
  • 所属部门(depid)
• 部门(department):
  • 部门编号(depid,主键)，
  • 部门名称(depname)
• 工资表(salary):
  • 雇员编号(empid)，
  • 基本工资(basesalary)，
  • 职务工资(titlesalary)，
  • 扣除(deduction)

需求:
1. 修改表结构，在部门表中添加部门简介字段
2. 将李四的职称改为“工程师”,并将她的基本工资改成 2000，职务工资
为 700
3. 删除人事部门的部门记录
4. 查询出每个雇员的雇员编号，实发工资，应发工资
5. 查询姓张且年龄小于 40 的员工记录
6. 查询雇员的雇员编号，姓名，职称，部门名称，实发工资
7. 查询销售部门的雇员姓名，工资
8. 统计各职称的人数
9. 统计各部门的部门名称，实发工资总和，平均工资
10. 查询比销售部门所有员工基本工资都高的雇员姓名

二、创建表

# 创建1：雇员表
create table employee (
      empid varchar(5) PRIMARY KEY,
      name  varchar(10),
      sex   char(3),
      title  varchar(20),
      birthday date,
      depid  varchar(4) );

# 添加1：员工信息
insert into employee values
	('1001','张三','男','高级工程师','1975-1-1','111'),
	('1002','李四','女','助工','1985-1-1','111'),
	('1003','王五','男','工程师','1978-11-11','222'),
	('1004','赵六','男','工程师','1979-1-1','222');

# 创建2：部门表
create table department (
	depid varchar(4),
	depname varchar(20));
# 添加2：部门信息
insert into department values
	('111','生产部'),
	('222','销售部'),
	('333','人事部');

# 创建3：工资表
create table salary(
	empid varchar(5),
	basesalary  int ,
	titlesalary int,
	deduction  int);
# 添加3：工资信息
insert into salary values
	('1001',2200,1100,200),
	('1002',1200,200,100),  
	('1003',1900,700,200),
	('1004',1950,700,150);

通过自然外连查询查看结果：

SELECT * FROM department d NATURAL LEFT JOIN employee e NATURAL LEFT JOIN salary;

三、任务查询

1. 修改表结构，在部门表中添加部门简介字段：

ALTER TABLE department ADD dpintroduce VARCHAR(50) DEFAULT '部门简介';

查询结果：

2.将李四的职称改为“工程师”,并将她的基本工资改成 2000，职务工资为 700：

多表更新：

UPDATE employee e,salary s SET title='工程师',basesalary=2000,titlesalary=700;
# 查询结果
SELECT * FROM department d NATURAL LEFT JOIN employee e NATURAL LEFT JOIN salary;

查询结果：

3.删除人事部门的部门记录：

先添加一条人事记录：

nsert into employee values('1005','前妻','男','工程师','1999-1-2','333');
insert into salary values ('1005',1850,600,100);

进行多表删除：

DELETE e,d,s FROM employee e,department d,salary s WHERE e.empid=s.empid AND e.depid=d.depid AND d.depname='人事部';

查询结果：

4. 查询出每个雇员的雇员编号，实发工资，应发工资：

多表查询（内联查询）：

SELECT e.name '姓名',s.basesalary+s.titlesalary '应发工资',s.basesalary+titlesalary-deduction '实发工资' FROM employee e,salary s WHERE e.empid=s.empid;

结果：

5. 查询姓张且年龄小于 40 的员工记录：

多表查询：

SELECT * FROM department d NATURAL LEFT JOIN employee e NATURAL LEFT JOIN salary WHERE (YEAR(CURDATE())-YEAR(e.birthday))<40;

6.查询雇员的雇员编号，姓名，职称，部门名称，实发工资：

多表查询：

SELECT e.empid '编号',e.name '姓名',e.title '职称',d.depname '部门名称',s.basesalary+titlesalary-deduction '实发工资' 
FROM employee e,salary s,department d 
WHERE e.empid=s.empid AND e.depid=d.depid;

打印：

7.查询销售部门的雇员姓名，工资：

多表查询-外连查询：

SELECT e.name,s.* FROM employee e LEFT JOIN salary s ON e.empid=s.empid;

8. 统计各职称的人数：

SELECT title,COUNT(*) FROM employee GROUP BY title;

9. 统计各部门的部门名称，实发工资总和，平均工资：

SELECT d.depname,SUM(s.basesalary+s.titlesalary-s.deduction) '实发工资总和',AVG(s.basesalary+s.titlesalary-s.deduction) '平均工资'
FROM employee e,salary s,department d WHERE e.empid=s.empid AND e.depid=d.depid GROUP BY d.depname;

结果：

10.查询比销售部门所有员工基本工资都高的雇员姓名

只需要和最高基本员工的工资比较即可（利用子查询）：

SELECT e.name FROM employee e,salary s WHERE e.empid=s.empid AND s.basesalary>
(SELECT MAX(basesalary) FROM employee e,salary s,department d WHERE e.empid=s.empid AND e.depid=d.depid AND d.depname='销售部');