建表语句.txt

系统开发,vb配合access数据库开发的一个部门人事管理的一个小软件的源码

CREATE TABLE EMPLOYEE_BASIC
(
 EMP_NO INT NOT NULL,
 EMP_NAME VARCHAR(10) NULL,
 DEPT_ID INT NULL,
 EMP_GENDER VARCHAR(2) NULL,
 EMP_BIRTHDAY DATETIME NULL,
 EMP_HOMETOWN VARCHAR(8) NULL,
 EMP_COUNTRY VARCHAR(10) NULL,
 EMP_NATION VARCHAR(10) NULL,                     
 EMP_ID VARCHAR(18) NULL,
 EMP_MARRIAGE VARCHAR(4) NULL,
 EMP_HEALTH VARCHAR(10) NULL,
 EMP_ZZMM VARCHAR(4) NULL,                    
 EMP_ZZMM_DATE  DATETIME NULL,                 
 EMP_BLOOD VARCHAR(2) NULL,
 EMP_STARTWORK DATETIME NULL,
 EMP_STATE VARCHAR(10) NULL,
 EMP_STATE_DATE DATETIME NULL,
 EMP_HOMEADDRESS VARCHAR(10) NULL,
 EMP_TELENO VARCHAR(12) NULL,
 EMP_EMAIL VARCHAR(20) NULL,
 JOB_ID INT NULL,
 
 PRIMARY KEY (EMP_NO)

);

CREATE TABLE EMPLOYEE_MARRIAGE
(
 EMP_NO INT NOT NULL,
 LOVER_NO INT NOT NULL,
 LOVER_NAME VARCHAR(10) NULL,
 LOVER_BIRTHDAY DATETIME NULL,
 MARRIAGE_DATE DATETIME NULL,
 LOVER_COMPANY VARCHAR(20) NULL,
 LOVER_ZZMM VARCHAR(4) NULL,
 LOVER_JOB VARCHAR(10) NULL,
 FOREIGN KEY(EMP_NO) REFERENCES EMPLOYEE_BASIC,


);

CREATE TABLE EMPLOYEE_SCHOOL
(
 EMP_NO INT NOT NULL,
 EMP_XL VARCHAR(10) NULL,                     
 EMP_MAJOR VARCHAR(10) NULL,                  
 BY_DATE DATETIME NULL,                       
 BY_SCHOOL VARCHAR(20) NULL,
 SCHOOL_CLASS VARCHAR(6) NULL,
 FOREIGN_LANGUAGE1 VARCHAR(6) NULL,
 FOREIGN_LANGUAGE1_CLASS VARCHAR(6) NULL,
 FOREIGN_LANGUAGE2 VARCHAR(6) NULL,
 FOREIGN_LANGUAGE2_CLASS VARCHAR(6) NULL,
 FOREIGN KEY(EMP_NO) REFERENCES EMPLOYEE_BASIC,
 
);

CREATE TABLE JOB
(
 JOB_ID INT NOT NULL,
 JOB_NAME VARCHAR(10) NULL,
 JOB_POWER VARCHAR(20) NULL,
 JOB_TASK  VARCHAR(20) NULL,
 PRIMARY KEY (JOB_ID) 
);

CREATE TABLE DEPT
(
 DEPT_ID INT NOT NULL,
 DEPT_NAME VARCHAR(10) NULL,
 DEPT_DESC VARCHAR(30) NULL,
 DEPT_MANAGER INT NULL,
 DEPT_VICEMANAGER INT NULL,
 PRIMARY KEY (DEPT_ID),
 FOREIGN KEY (DEPT_MANAGER) REFERENCES EMPLOYEE_BASIC(EMP_NO),
 FOREIGN KEY (DEPT_VICEMANAGER) REFERENCES EMPLOYEE_BASIC(EMP_NO)
);

ALTER TABLE EMPLOYEE_BASIC ADD             
 FOREIGN KEY(DEPT_ID) REFERENCES DEPT(DEPT_ID),
 FOREIGN KEY(JOB_ID) REFERENCES JOB(JOB_ID)