REM user: HXHWB 建立用户
create user HXHWB
identified by ok
default tablespace USER_DATA
temporary tablespace temp_USER_DATA
quota unlimited on USER_DATA
quota unlimited on Temp_USER_DATA
quota unlimited on Index_USER_DATA;
REM 授给用户HXHWB 的权限
grant connect,resource,DBA to HXHWB ;
REM 3.3.1 创建员工基本状况表格EMPLOYEE_BASIC
CREATE TABLE "HXHWB".EMPLOYEE_BASIC
(
EMP_NO NUMBER(6) NOT NULL,
EMP_NAME VARCHAR2(10) NULL,
DEPT_ID NUMBER(3) NULL,
EMP_GENDER VARCHAR2(2) NULL,
EMP_BIRTHDAY DATE NULL,
EMP_HOMETOWN VARCHAR2(8) NULL,
EMP_COUNTRY VARCHAR2(10) NULL,
EMP_NATION VARCHAR2(10) NULL,
EMP_ID NUMBER(20) NULL,
EMP_MARRIAGE VARCHAR2(2) NULL,
EMP_HEALTH VARCHAR2(20) NULL,
EMP_ZZMM VARCHAR2(4) NULL,
EMP_ZZMM_DATE DATE NULL,
EMP_BLOOD VARCHAR2(2) NULL,
EMP_STARTWORK DATE NULL,
EMP_STATE VARCHAR2(10) NULL,
EMP_STATE_DATE DATE NULL,
EMP_HOMEADRESS VARCHAR2(10) NULL,
EMP_TELENO NUMBER(10) NULL,
EMP_EMAIL VARCHAR2(20) NULL,
JOB_ID NUMBER(3 ) NULL,
CONSTRAINT EMP_NO_PK PRIMARY KEY (EMP_NO)
)
TABLESPACE "USER_DATA";
REM 3.3.2 创建员工婚姻状况的表格EMPLOYEE_MARRIAGE
CREATE TABLE "HXHWB".EMPLOYEE_MARRIAGE
(
EMP_NO NUMBER(6) NOT NULL,
LOVER_NO NUMBER(6) NOT NULL,
LOVER_NAME VARCHAR2(10) NULL,
LOVER_BIRTHDAY DATE NULL,
MARRIAGE_DATE DATE NULL,
LOVER_COMPANY VARCHAR2(20) NULL,
LOVER_ZZMM VARCHAR2(4) NULL,
LOVER_JOB VARCHAR2(10) NULL,
CONSTRAINT EMP_NO_FK FOREIGN KEY (EMP_NO)
REFERENCES "HXHWB"."EMPLOYEE_BASIC"(EMP_NO),
CONSTRAINT LOVER_NO_UNQ UNIQUE (LOVER_NO)
)
TABLESPACE "USER_DATA";
REM 3.3.3 创建员工学历状况表格EMPLOYEE_ SCHOOL
CREATE TABLE "HXHWB".EMPLOYEE_SCHOOL
(
EMP_NO NUMBER(6) NOT NULL,
EMP_XL VARCHAR2(10) NULL,
EMP_MAJOR VARCHAR2(10) NULL,
BY_DATE DATE NULL,
BY_SCHOOL VARCHAR2(20) NULL,
SCHOOL_CLASS VARCHAR2(6) NULL,
FOREIGN_LANGUAGE1 VARCHAR2(6) NULL,
FOREIGN_LANGUAGE1_CLASS VARCHAR2(6) NULL,
FOREIGN_LANGUAGE2 VARCHAR2(6) NULL,
FOREIGN_LANGUAGE2_CLASS VARCHAR2(6) NULL,
CONSTRAINT SCHOOL_EMP_NO_FK FOREIGN KEY (EMP_NO)
REFERENCES "HXHWB"."EMPLOYEE_BASIC"(EMP_NO)
)
TABLESPACE "USER_DATA";
REM 3.3.4 创建工作岗位情况表JOB
CREATE TABLE "HXHWB". JOB
(
JOB_ID NUMBER(3) NOT NULL,
JOB_NAME VARCHAR2(10) NULL,
JOB_POWER VARCHAR2(20) NULL,
JOB_TASK VARCHAR2(20) NULL,
CONSTRAINT JOB_ID_PK PRIMARY KEY (JOB_ID)
)
TABLESPACE "USER_DATA";
REM 3.3.5 创建部门信息表DEPT
CREATE TABLE "HXHWB".DEPT
(
DEPT_ID NUMBER(3) NOT NULL,
DEPT_NAME VARCHAR2(10) NULL,
DEPT_DESC VARCHAR2(30) NULL,
DEPT_MANAGER NUMBER(6) NULL,
DEPT_VICEMANAGER NUMBER(6) NULL,
CONSTRAINT DEPT_ID_PK PRIMARY KEY (DEPT_ID),
CONSTRAINT DEPT_MANAGER_FK FOREIGN KEY (DEPT_MANAGER)
REFERENCES "HXHWB"."EMPLOYEE_BASIC"(EMP_NO),
CONSTRAINT DEPT_VICEMANAGER_FK FOREIGN KEY
(DEPT_VICEMANAGER)
REFERENCES "HXHWB"."EMPLOYEE_BASIC"(EMP_NO)
)
TABLESPACE "USER_DATA";
REM 3.3.6 增加EMPLOYEE_BASIC表格的外部键
ALTER TABLE "HXHWB"."EMPLOYEE_BASIC" ADD
(
CONSTRAINT DEPT_ID_FK FOREIGN KEY (DEPT_ID)
REFERENCES "HXHWB"."DEPT"(DEPT_ID),
CONSTRAINT JOB_ID_FK FOREIGN KEY (JOB_ID)
REFERENCES "HXHWB"."JOB"(JOB_ID)
);