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.xueh 13 is '学号'; 14 comment on column T_HQ_XS.xingm 15 is '姓名'; 16 comment on column T_HQ_XS.xingb 17 is '性别 1-男,2-女'; 18 comment on column T_HQ_XS.nianl 19 is '年龄'; 20 comment on column T_HQ_XS.zhuany 21 is '专业'; 22 -- Create/Recreate primary, unique and foreign key constraints 23 alter table T_HQ_XS 24 add constraint PK_T_HQ_XS primary key (XUEH) 25 26 -- Create/Recreate check constraints 27 alter table T_HQ_XS 28 add constraint CHECK_T_HQ_XS_NIANL 29 check (NIANL > 8 AND NIANL < 50); 30 alter table T_HQ_XS 31 add constraint CHECK_T_HQ_XS_XINGB 32 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.xueh 12 is '学号'; 13 comment on column T_HQ_CJ.java 14 is 'java课成绩'; 15 comment on column T_HQ_CJ.oracle 16 is 'Oracle课成绩'; 17 comment on column T_HQ_CJ.android 18 is '安卓课成绩'; 19 -- Create/Recreate primary, unique and foreign key constraints 20 alter table T_HQ_CJ 21 add constraint PK_T_HQ_CJ primary key (XUEH) 22 -- Create/Recreate check constraints 23 alter table T_HQ_CJ 24 add constraint CHECK_T_HQ_CJ_ANDROID 25 check (ANDROID >= 0 AND ANDROID <=100); 26 alter table T_HQ_CJ 27 add constraint CHECK_T_HQ_CJ_JAVA 28 check (JAVA >= 0 AND JAVA <=100); 29 alter table T_HQ_CJ 30 add constraint CHECK_T_HQ_CJ_ORACLE 31 check (ORACLE >= 0 AND ORACLE <=100);
3,课本领用表
-- Create table create 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');
