database_of_hrms_mysql.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='leon';

insert into employee(emp_id,emp_name,job,psw) values(100,'admin','管理员','admin');