hrms.sql

人力资源管理系统

create database hrms;
use hrms;

create table ypinfo(
  id varchar(50),
  y_name varchar(50),
  y_sex varchar(50),
  y_jg varchar(50),
  y_school varchar(50),
  y_xueli varchar(50),
  y_job varchar(50),
  self_intro varchar(100),
  y_email varchar(50),
  y_phone varchar(50),
  tab_state varchar(50));

create table employee(
  emp_id int,
  emp_name varchar(50) ,
  psw varchar(50),
  id_card varchar(50),  
  sex varchar(50),
  dept_name varchar(50),
  job varchar(50),
  hire_date varchar(50),
  birthday varchar(50));

create table change_job(
  id varchar(50),
  emp_name varchar(50),
  old_dept varchar(50),
  old_job varchar(50),
  new_dept varchar(50),
  new_job varchar(50),
  reason varchar(50),
  tab_state varchar(50));

create table emp_train(
  id varchar(50),
  emp_name varchar(50),
  dept_name varchar(50),
  attitude varchar(500));

create table train_info(
  id varchar(50),
  people varchar(50),
  start_date varchar(50),
  end_date varchar(50),
  descript varchar(50));

create table train_sum(
  id varchar(50),
  emp_name varchar(50),
  dept_name varchar(50),
  summary varchar(500));
 
create table kq_info(
  id varchar(50),
  emp_name varchar(50),
  times int,
  kq_degree int,
  month varchar(50));

create table kq_leave(
  id varchar(50),
  emp_name varchar(50),
  start_date varchar(50),
  end_date varchar(50),
  reason varchar(50),
  tab_state varchar(50));

create table evaluate(
  id varchar(50),
  emp_name varchar(50),
  first_mark int,
  sec_mark int,
  thi_mark int,
  for_mark int,
  fif_mark int,
  month varchar(50));

create table jxd(
  id varchar(50),
  emp_name varchar(50),
  mark int,
  month varchar(50));

create table jfd(
  id varchar(50),
  emp_name varchar(50),
  jf_name varchar(50),
  jf_money int,
  month varchar(50));

create table salary_info(
  id varchar(50),
  emp_name varchar(50),
  month varchar(50),
  basic_sal int,
  tax_rate int,
  jintie int,
  bonus int,
  sum_sal int);


alter table ypinfo add CONSTRAINT ypinfo_primary_key primary key (id);
alter table employee add CONSTRAINT employee_primary_key primary key (emp_name);
alter table change_job add CONSTRAINT change_job_primary_key primary key (id);
alter table emp_train add CONSTRAINT emp_train_primary_key primary key (id);
alter table train_sum add CONSTRAINT train_sum_primary_key primary key (id);
alter table train_info add CONSTRAINT train_info_primary_key primary key (id);
alter table kq_leave add CONSTRAINT kq_leave_primary_key primary key (id);
alter table kq_info add CONSTRAINT kq_info_primary_key primary key (id);
alter table evaluate add CONSTRAINT evaluate_primary_key primary key (id);
alter table jxd add CONSTRAINT jxd_primary_key primary key (id);
alter table jfd add CONSTRAINT jfd_primary_key primary key (id);
alter table salary_info add CONSTRAINT salary_info_primary_key primary key (id);


alter table change_job add CONSTRAINT change_job_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);
alter table emp_train add CONSTRAINT emp_train_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);
alter table kq_leave add CONSTRAINT kq_leave_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);
alter table train_sum add CONSTRAINT train_sum_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);
alter table kq_info add CONSTRAINT kq_info_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);
alter table evaluate add CONSTRAINT evaluate_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);
alter table jxd add CONSTRAINT jxd_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);
alter table jfd add CONSTRAINT jfd_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);
alter table salary_info add CONSTRAINT salary_info_foreign_key 
  foreign key (emp_name) REFERENCES employee(emp_name);


alter table ypinfo add CONSTRAINT check_ypinfo check (tab_state='等待' or tab_state='批准' or tab_state='拒绝');
alter table kq_leave add CONSTRAINT check_kq_leave check (tab_state='等待' or tab_state='批准' or tab_state='拒绝');
alter table change_job add CONSTRAINT check_change_job check (tab_state='等待' or tab_state='批准' or tab_state='拒绝');
  
insert into employee(emp_id,emp_name,job) values('1','leon','管理员');
update employee set psw= '123' where emp_name='leonc';
insert into employee values('2','leonc','123','111','male','人事部','管理员','','08,08,08');
insert into employee values('3','cmd','123','123333','female','招聘部','经理','','03,02,08');
insert into employee values('4','leonch','123','111','male','人事部','员工','','08,08,08');
insert into employee values('5','baby','123','111','male','薪酬部','管理员','','08,08,08');
insert into employee values('6','王春来','123','111','male','培训部','经理','','08,08,08');
insert into employee values('7','陈梦蝶','123','111','female','人事部','经理','','08,08,08');
insert into employee values('8','谭伟文','123','111','female','人事部','经理','','08,08,08');
insert into employee values('9','刘奔','123','111','male','人事部','经理','','08,08,08');
insert into employee values('10','赵玉兰','123','111','female','人事部','经理','','08,08,08');
insert into employee values('11','许悦','123','111','female','人事部','经理','','08,08,08');
insert into employee values('12','陈紫千','123','111','male','人事部','经理','','08,08,08');
insert into employee values('13','刘德华','123','111','male','人事部','员工','','08,08,08');
insert into employee values('14','Joson','123','111','female','人事部','员工','','08,08,08');
insert into employee values('15','Michael','123','111','male','人事部','员工','','08,08,08');
insert into employee values('16','Sam','123','111','male','人事部','员工','','08,08,08');
insert into employee values('17','Richard','123','111','female','人事部','员工','','08,08,08');
insert into employee values('18','Harvard','123','111','male','人事部','员工','','08,08,08');
insert into employee values('19','Bridge','123','111','female','人事部','员工','','08,08,08');
insert into employee values('20','Landry','123','111','female','人事部','员工','','08,08,08');
insert into employee values('21','Kidd','123','111','male','人事部','员工','','08,08,08');
insert into employee values('22','Kobe','123','111','male','人事部','员工','','08,08,08');
insert into employee values('23','Mcgrady','123','111','male','人事部','员工','','08,08,08');

insert into change_job values('7','陈梦蝶','人事部','经理','薪酬部','经理','无具体原因','等待');
insert into change_job values('8','谭伟文','人事部','经理','薪酬部','经理','无具体原因','等待');


insert into ypinfo values('6','王春来1','male','北京','北交','本科','110108','我很好','[e-mail]','51684513','等待');
insert into ypinfo values('7','陈梦蝶1','female','北京','北交','本科','110108','我很好','[e-mail]','51684513','等待');
insert into ypinfo values('8','谭伟文1','male','北京','北交','本科','110108','我很好','[e-mail]','51684513','等待');
insert into ypinfo values('9','刘奔1','male','北京','北交','本科','110108','我很好','[e-mail]','51684513','等待');
insert into ypinfo values('10','赵玉兰1','female','北京','北交','本科','110108','我很好','[e-mail]','51684513','等待');
insert into ypinfo values('11','许悦1','female','北京','北交','本科','110108','我很好','[e-mail]','51684513','等待');
insert into ypinfo values('12','陈紫千1','male','北京','北交','本科','110108','我很好','[e-mail]','51684513','等待');


insert into emp_train values('7','陈梦蝶','人事部','很好的培训');
insert into emp_train values('8','谭伟文','人事部','很好的培训');

insert into train_info values('1','陈梦蝶，谭伟文','08,08,01','08,08,07','一次重要的培训');
insert into train_info values('2','王春来，刘奔，陈紫千','07,07,01','07,07,07','一次不太重要的培训');

insert into train_sum values('1','陈梦蝶','人事部','这次培训果然很好！');
insert into train_sum values('2','谭伟文','人事部','什么破培训！');


insert into kq_info values('1','王春来',31,29,'7');
insert into kq_info values('2','陈梦蝶',31,30,'7');
insert into kq_info values('3','谭伟文',31,30,'7');
insert into kq_info values('4','刘奔',31,31,'7');
insert into kq_info values('5','赵玉兰',31,31,'7');
insert into kq_info values('6','许悦',31,30,'7');
insert into kq_info values('7','陈紫千',31,31,'7');


insert into kq_leave values('1','王春来','08,08,08','08,08,08','约会','等待');
insert into kq_leave values('2','王春来','08,08,08','08,08,08','约会','等待');
insert into kq_leave values('3','谭伟文','08,08,08','08,08,08','约会','等待');
insert into kq_leave values('4','陈梦蝶','08,08,08','08,08,08','约会','等待');
insert into kq_leave values('5','许悦','08,08,08','08,08,08','购物','等待');


insert into evaluate values('1','王春来',5,5,5,5,5,'7');
insert into evaluate values('2','陈梦蝶',5,5,5,5,5,'7');
insert into evaluate values('3','谭伟文',5,5,5,5,5,'7');
insert into evaluate values('4','刘奔',5,5,5,5,5,'7');
insert into evaluate values('5','赵玉兰',5,5,5,5,5,'7');
insert into evaluate values('6','许悦',5,5,5,5,5,'7');
insert into evaluate values('7','陈紫千',5,5,5,5,5,'7');


insert into jxd values('1','王春来',25,'7');
insert into jxd values('2','陈梦蝶',25,'7');
insert into jxd values('3','谭伟文',25,'7');
insert into jxd values('4','刘奔',25,'7');
insert into jxd values('5','赵玉兰',25,'7');
insert into jxd values('6','许悦',25,'7');
insert into jxd values('7','陈紫千',25,'7');


insert into jfd values('1','王春来','拾金不昧',100,'7');
insert into jfd values('2','陈梦蝶','助人为乐',100,'7');