SpringMVC+Spring+Mybatis+Oracle 实现HR管理(CRUD)一数据库设计
来源:互联网 发布:理查德杰弗森生涯数据 编辑:程序博客网 时间:2024/06/06 20:06
数据库设计以及有关操作:
--部门表
--部门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;
阅读全文
1 0
- SpringMVC+Spring+Mybatis+Oracle 实现HR管理(CRUD)一数据库设计
- SpringMVC+Spring+Mybatis+Oracle 实现HR管理(CRUD)二
- SpringMVC+Spring+Hibernate+Oracle 实现图书管理(CRUD)
- Spring+SpringMVC+MyBatis+EasyUI实现CRUD与分页
- 原 Spring + SpringMVC + Mybatis 整合,实现 CRUD (增删改查)
- mybatis(一)—-实现crud操作
- Spring+SpringMVC+mybatis入门(环境搭建+crud)
- MyBatis实现数据库的CRUD
- Mybatis实现对数据(oracle数据库)的增删改查(crud)
- 用maven项目mybatis集成spring+springmvc无刷新实现crud
- 用maven项目mybatis集成spring+springmvc无刷新实现crud
- springMVC+mybatis+spring security<三>:使用数据库管理资源
- spring+springmvc+mybatis shiro权限管理系统demo mysql数据库
- Spring SpringMVC Mybatis(SSM+Oracle整合)
- 使用IDEA和Mybatis实现CRUD(一)
- 搭建Spring SpringMVC Mybatis 框架(一)
- Spring+Springmvc+mybatis学习(一)
- Spring+SpringMVC+mybatis+redis(一)
- Mac sublime 编辑 html 快捷键没用的解决方法
- 考题篇(5.4) NSE4 16. 高级IPsec VPN ❀ 飞塔 (Fortinet) 网络安全专家
- ZOJ3209 Treasure Map(DLX精确覆盖)
- Java 入门第三季
- 网秦发布2017年第一季度、第二季度及第三季度财报
- SpringMVC+Spring+Mybatis+Oracle 实现HR管理(CRUD)一数据库设计
- Linux Journal (Linux学报) 宣布停刊
- 漫画:PHP女程序员自救的技巧
- 世界上的程序员只分为俩种
- LeetCode小白菜笔记[10]:Implement strStr()
- 考题篇(5.4) NSE4 17. 入侵防御 ❀ 飞塔 (Fortinet) 网络安全专家
- Python第二课 字符串(1)
- 纪念写挂的T4--Day2017.12.14
- centos6.8源码安装php7.0