ORACLE建表練習

標籤：

1,學生表

 1 -- Create table 2 create table T_HQ_XS 3 ( 4   xueh   VARCHAR2(10) not null, 5   xingm  VARCHAR2(20) not null, 6   xingb  CHAR(1) default ‘1‘, 7   nianl  NUMBER, 8   zhuany VARCHAR2(20) 9 )10 11 -- Add comments to the columns 12 comment on column T_HQ_XS.xueh13   is ‘學號‘;14 comment on column T_HQ_XS.xingm15   is ‘姓名‘;16 comment on column T_HQ_XS.xingb17   is ‘性別 1-男,2-女‘;18 comment on column T_HQ_XS.nianl19   is ‘年齡‘;20 comment on column T_HQ_XS.zhuany21   is ‘專業‘;22 -- Create/Recreate primary, unique and foreign key constraints 23 alter table T_HQ_XS24   add constraint PK_T_HQ_XS primary key (XUEH)25 26 -- Create/Recreate check constraints 27 alter table T_HQ_XS28   add constraint CHECK_T_HQ_XS_NIANL29   check (NIANL > 8 AND NIANL < 50);30 alter table T_HQ_XS31   add constraint CHECK_T_HQ_XS_XINGB32   check (XINGB = ‘1‘ OR XINGB = ‘2‘);

2,成績表

 1 -- Create table 2 create table T_HQ_CJ 3 ( 4   xueh    VARCHAR2(10) not null, 5   java    NUMBER(4,1), 6   oracle  NUMBER(4,1), 7   android NUMBER(4,1) 8 ) 9 10 -- Add comments to the columns 11 comment on column T_HQ_CJ.xueh12   is ‘學號‘;13 comment on column T_HQ_CJ.java14   is ‘java課成績‘;15 comment on column T_HQ_CJ.oracle16   is ‘Oracle課成績‘;17 comment on column T_HQ_CJ.android18   is ‘安卓課成績‘;19 -- Create/Recreate primary, unique and foreign key constraints 20 alter table T_HQ_CJ21   add constraint PK_T_HQ_CJ primary key (XUEH)22 -- Create/Recreate check constraints 23 alter table T_HQ_CJ24   add constraint CHECK_T_HQ_CJ_ANDROID25   check (ANDROID >= 0 AND ANDROID <=100);26 alter table T_HQ_CJ27   add constraint CHECK_T_HQ_CJ_JAVA28   check (JAVA >= 0 AND JAVA <=100);29 alter table T_HQ_CJ30   add constraint CHECK_T_HQ_CJ_ORACLE31   check (ORACLE >= 0 AND ORACLE <=100);

3,課本領用表

-- Create tablecreate table T_HQ_KB(  xueh     varchar2(10) not null,  java     char(1) default ‘0‘,  javarq   date,  oracle   char(1) default ‘0‘,  oraclerq date);-- Add comments to the columns comment on column T_HQ_KB.xueh  is ‘學號‘;comment on column T_HQ_KB.java  is ‘java課本是否已領用0-否,1-是‘;comment on column T_HQ_KB.javarq  is ‘java課本領用日期‘;comment on column T_HQ_KB.oracle  is ‘oracle課本是否已領用0-否,1-是‘;comment on column T_HQ_KB.oraclerq  is ‘oracle課本領用日期‘;-- Create/Recreate primary, unique and foreign key constraints alter table T_HQ_KB  add constraint PK_T_HQ_KB primary key (XUEH);-- Create/Recreate check constraints alter table T_HQ_KB  add constraint CHECK_T_HQ_KB_JAVA  check (JAVA = ‘0‘ OR JAVA = ‘1‘);alter table T_HQ_KB  add constraint CHECK_T_HQ_KB_ORACLE  check (ORACLE = ‘0‘ OR ORACLE = ‘1‘);

 

ORACLE建表練習