【六祎-Java】数据库设计；人事管理系统表设计；设计数据库表（仅供参考）

1、创建job_inf

CREATE TABLE job_inf(
ID INT(11) NOT NULL AUTO_INCREMENT,
NAME varchar(50) NOT NULL,
PEMARK VARCHAR(500) default NULL,
primary key(ID)
)ENGINE=INNODB auto_increment=10 DEFAULT CHARSET=utf8;
#插入数据
INSERT INTO job_inf(ID,NAME,PEMARK) values(1,'职员','职员'),(2,'JAVA开发工程师','JAVA开发工程师')
,(3,'JAVA中级开发工程师','JAVA中级开发工程师'),(4,'JAVA高级开发工程师','JAVA高级开发工程师'),(5,'系统管理员','系统管理员'),(6,'架构师','架构师'),(7,'主管','主管'),(8,'经理','经理'),(9,'总经理','总经理');

2、创建user_inf

CREATE TABLE user_inf(
ID INT(11) NOT NULL AUTO_INCREMENT,
loginname varchar(20) NOT NULL,
PASSWORD VARCHAR(16) NOT NULL,
USERSTATUS INT(11) NOT NULL DEFAULT '1',
createdate TIMESTAMP NOT NULL  DEFAULT CURRENT_TIMESTAMP,
username VARCHAR(20) DEFAULT NULL,
primary key(ID)

)ENGINE=INNODB auto_increment=2 DEFAULT CHARSET=utf8;
#插入数据
INSERT INTO user_inf(ID,loginname,PASSWORD,USERSTATUS,createdate,username) values(1,'admin','123456',2,'2019-08-24 09:20:59','超级管理员');

3、创建employee_inf表

CREATE TABLE employee_inf(
ID INT(11) NOT NULL AUTO_INCREMENT,
DEPT_ID INT(11) NOT NULL,
JOB_ID INT(11) NOT NULL,
NAME VARCHAR(20) NOT NULL,
CARD_ID VARCHAR(18) NOT NULL,
ADDRESS VARCHAR(50) NOT NULL,
POST_CODE VARCHAR(50) DEFAULT NULL,
TEL VARCHAR(16) DEFAULT NULL,
PHONE VARCHAR(11) NOT NULL,
QQ_NUM VARCHAR(10) DEFAULT NULL,
EMALL VARCHAR(50) NOT NULL,
SEX INT(11) NOT NULL DEFAULT '1',
PARTY VARCHAR(10) DEFAULT NULL,
BIRTHDAY DATETIME DEFAULT NULL,
RACE VARCHAR(100)  DEFAULT NULL,
EDUCATION VARCHAR(10) DEFAULT NULL,
SPECIALITY VARCHAR(20) DEFAULT NULL,
HOBBY VARCHAR(100) DEFAULT NULL,
REMARK VARCHAR(500) DEFAULT NULL,
CREATE_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
primary key(ID),
KEY FK_EMP_DEPT(DEPT_ID),
KEY FK_EMP_JOB(JOB_ID),
CONSTRAINT FK_EMP_DEPT foreign key(DEPT_ID) REFERENCES dept_inf(ID),
CONSTRAINT FK_JOB foreign key(JOB_ID) REFERENCES job_inf (ID)
)ENGINE=INNODB auto_increment=21 DEFAULT CHARSET=utf8;
#插入数据
INSERT INTO employee_inf
(ID,DEPT_ID,JOB_ID,NAME,CARD_ID,ADDRESS,POST_CODE,TEL,PHONE,QQ_NUM,EMALL,SEX,PARTY,BIRTHDAY,RACE,EDUCATION,SPECIALITY,HOBBY,REMARK,CREATE_DATE) values
(2,2,1,'杰克','22623','广州白云','510011','020-77777788','13902002222','36758866','251525887@qq.com',1,'非党员','1898-01-02 00:00:00','满','硕士','计算机','跑步','跑步达人','2019-08-25 09:32:59'),
(3,1,2,'美的要死','4328015556','四川成都','510444','020-77775555','13902005555','36000066','561425887@qq.com',0,'党员','1998-01-01 00:00:01','汉','博士','舞蹈','跳舞','四大美女','2019-08-25 09:35:59');

4、创建notice_inf

CREATE TABLE notice_inf(
ID INT(11) NOT NULL AUTO_INCREMENT,
TITLE VARCHAR(50) NOT NULL,
CONTENT TEXT NOT NULL,
CREATE_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
USER_ID INT(11) DEFAULT NULL,
primary key(ID),
KEY FK_NOTICE_USER (USER_ID),
CONSTRAINT FK_NOTICE_USER FOREIGN KEY (USER_ID) REFERENCES user_inf(ID)
)ENGINE=INNODB auto_increment=19 DEFAULT CHARSET=utf8;

5、创建表document_inf

CREATE TABLE document_inf(
ID INT(11) NOT NULL AUTO_INCREMENT,
TITLE VARCHAR(50) NOT NULL,
filename VARCHAR(300) NOT NULL,
REMARK VARCHAR(300) DEFAULT NULL,
CREATE_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
USER_ID INT(11) DEFAULT NULL,
primary key(ID),
KEY FK_DOCUMENT_USER (USER_ID),
CONSTRAINT FK_DOCUMENT_USER foreign key(USER_ID) REFERENCES user_inf(ID)
)ENGINE=INNODB auto_increment=7 DEFAULT CHARSET=utf8;