创建表格sql.txt

数据库pb语言的一些程序示例,给pb学习者参考使用,希望能给大家带来帮助!

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";
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";
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";
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";
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";
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)
);