SpringMVC+Spring+Mybatis+Oracle 实现HR管理（CRUD）一数据库设计

数据库设计以及有关操作：

--部门表

--部门id 名称 简介 上级部门 部门经理
create table departments(
d_id number(10) primary key not null ,
d_name varchar2(20) not null ,
d_introduction varchar2(100) not null ,
d_higherOffice varchar2(20),
d_departmentManager varchar2(20) not null 
);
drop table departments;
select * from departments;


insert into departments(d_id,d_name,d_introduction,d_higherOffice,d_departmentmanager) values(seq_departments.nextval,'总经理','公司最高层',' ','张润东');
insert into departments(d_id,d_name,d_introduction,d_higherOffice,d_departmentmanager) values(seq_departments.nextval,'市场部','市场部负责销售产品','总经理','朱彬');
insert into departments(d_id,d_name,d_introduction,d_higherOffice,d_departmentmanager) values(seq_departments.nextval,'研发部','研发部负责研发产品','总经理','丁宁乐');




select d.d_id, d.d_name, d.d_introduction,
d.d_higherOffice,d.d_departmentManager,
    p.p_id,p.p_name, p.p_introduction,p.p_minsalary, p.p_maxsalary
from departments d 
    left join positions p on p.p_d_id = d.d_id
where d.d_id = 1002


--部门序列
create sequence seq_departments
increment by 1
start with 1001
maxvalue 1999;


drop sequence seq_departments;




--职位表
--职位id 名称 简介 最低薪水 最高薪水 所属部门id 
create table positions(
p_id number(10) primary key not null ,
p_name varchar2(20) not null ,
p_introduction varchar2(100) not null ,
p_minSalary number(20,2) not null ,
p_maxSalary number(20,2) not null ,
p_d_id number(10) not null 
);
drop table positions;
select * from positions;


insert into positions(p_id,p_name,p_introduction,p_minsalary,p_maxsalary,p_d_id) values(seq_positions.nextval,'市场部经理','市场部总负责人',2000.00,10000.00,1002);
insert into positions(p_id,p_name,p_introduction,p_minsalary,p_maxsalary,p_d_id) values(seq_positions.nextval,'市场部人员','市场部营销人员',2000.00,8000.00,1002);


insert into positions(p_id,p_name,p_introduction,p_minsalary,p_maxsalary,p_d_id) values(seq_positions.nextval,'研发部经理','研发部总负责人',4500.00,20000.00,1003);
insert into positions(p_id,p_name,p_introduction,p_minsalary,p_maxsalary,p_d_id) values(seq_positions.nextval,'Java开发工程师','负责Java开发的技术人员',4500.00,15000.00,1003);


--职位序列
create sequence seq_positions
increment by 1
start with 2001
maxvalue 2999;


drop sequence seq_positions;




--员工表
--员工id 姓名 性别 学历 电话 所属部门id 职位id 入职时间 薪资  
create table employees(
e_id number(10) primary key not null,
e_name varchar2(20) not null,
e_sex varchar2(10) not null,
e_education varchar2(20) not null,
e_phone varchar2(22) not null,
e_d_id number(10) not null,
e_p_id number(10) not null,
e_entryTime varchar2(40) not null,
e_salary number(20,2) not null
);
drop table employees;
select * from employees;


--市场部
insert into employees(e_id,e_name,e_sex,e_education,e_phone,e_d_id,e_p_id,e_entrytime,e_salary) values(
seq_employees.nextval,'朱彬','男','全日制本科','15802561801',1002,2001,'2016-12-01',10000.00
);
insert into employees(e_id,e_name,e_sex,e_education,e_phone,e_d_id,e_p_id,e_entrytime,e_salary) values(
seq_employees.nextval,'沈世武','男','全日制本科','15802561802',1002,2002,'2017-10-01',6000.00
);
insert into employees(e_id,e_name,e_sex,e_education,e_phone,e_d_id,e_p_id,e_entrytime,e_salary) values(
seq_employees.nextval,'张虎','男','全日制本科','15802561805',1002,2002,'2017-11-01',6500.00
);
--研发部
insert into employees(e_id,e_name,e_sex,e_education,e_phone,e_d_id,e_p_id,e_entrytime,e_salary) values(
seq_employees.nextval,'丁宁乐','男','全日制本科','15802561803',1003,2003,'2016-12-05',20000.00
);
insert into employees(e_id,e_name,e_sex,e_education,e_phone,e_d_id,e_p_id,e_entrytime,e_salary) values(
seq_employees.nextval,'谌敦伟','男','全日制本科','15802561804',1003,2004,'2017-10-05',15000.00
);
insert into employees(e_id,e_name,e_sex,e_education,e_phone,e_d_id,e_p_id,e_entrytime,e_salary) values(
seq_employees.nextval,'王永顺','男','全日制本科','15802561806',1003,2004,'2017-11-05',14000.00
);


--员工序列
create sequence seq_employees
increment by 1
start with 3001
maxvalue 3999;


drop sequence seq_employees;




--职位历史表
--开始时间(从员工表获得)  结束时间 职位id 和名称(从员工表获得) 员工id  职位历史id 
create table jobHistorys(
j_id number(10) primary key not null,
j_e_id number(10) not null,
j_outTime varchar2(40)not null
);
drop table jobHistorys;
select * from jobHistorys;


insert into jobHistorys(j_id,j_e_Id,j_outTime) values (seq_jobHistorys.Nextval,3006,'2017-12-14');


--职位历史序列
create sequence seq_jobHistorys
increment by 1
start with 4001
maxvalue 4999;


drop sequence seq_jobHistorys;






--通过职位id 查找员工信息
select  e.e_id,
                  e.e_name,
                  e.e_sex,
                  e.e_education,
                  e.e_phone,
                  e.e_d_id,
                  e.e_p_id,
                  e.e_entrytime,
                  e.e_salary,
                  p.p_name , 
                  d.d_name from positions p left join employees e on p.p_id = e.e_p_id 
                  left join departments d on p.p_d_id = d.d_id
                  where p.p_id = 2004;