案例所需表结构
Oracle技术圈-Oracle基础教程所有案例所需的表结构是利用Oracle技术圈自己设计的一套简单版的学生信息系统的表结构。相关表结构的关系图如下：
表结构执行脚本：
-- Create table create table STUINFO ( stuid VARCHAR2(11) not null, stuname VARCHAR2(50) not null, sex CHAR(1) not null, age NUMBER(2) not null, classno VARCHAR2(7) not null, stuaddress VARCHAR2(100) default '地址未录入', grade CHAR(4) not null, enroldate DATE, idnumber VARCHAR2(18) default '身份证未采集' not null ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); -- Add comments to the table comment on table STUINFO is '学生信息表'; -- Add comments to the columns comment on column STUINFO.stuid is '学号'; comment on column STUINFO.stuname is '学生姓名'; comment on column STUINFO.sex is '学生性别'; comment on column STUINFO.age is '学生年龄'; comment on column STUINFO.classno is '学生班级号'; comment on column STUINFO.stuaddress is '学生住址'; comment on column STUINFO.grade is '年级'; comment on column STUINFO.enroldate is '入学时间'; comment on column STUINFO.idnumber is '身份证号'; -- Create/Recreate primary, unique and foreign key constraints alter table STUINFO add constraint PK_STUINFO primary key (STUID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); -- Create table create table CLASS ( classno VARCHAR2(7) not null, classname VARCHAR2(50), monitorid VARCHAR2(11), monitorname VARCHAR2(50), headmasterid VARCHAR2(8), headmastername VARCHAR2(50), classaddress VARCHAR2(50), enterdate DATE ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); -- Add comments to the table comment on table CLASS is '班级信息表'; -- Add comments to the columns comment on column CLASS.classno is '班级号'; comment on column CLASS.classname is '班级名称'; comment on column CLASS.monitorid is '班长学号'; comment on column CLASS.monitorname is '班长姓名'; comment on column CLASS.headmasterid is '班主任教师号'; comment on column CLASS.headmastername is '班主任姓名'; comment on column CLASS.classaddress is '班级地址'; comment on column CLASS.enterdate is '录入时间'; -- Create/Recreate primary, unique and foreign key constraints alter table CLASS add constraint PK_CLASS primary key (CLASSNO) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255; -- Create table create table COURSE ( courseid VARCHAR2(9) not null, schyear VARCHAR2(4), term VARCHAR2(4), coursename VARCHAR2(100) ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); -- Add comments to the table comment on table COURSE is '课程表'; -- Add comments to the columns comment on column COURSE.courseid is '课程id'; comment on column COURSE.schyear is '学年'; comment on column COURSE.term is '学期'; comment on column COURSE.coursename is '课程名称'; -- Create/Recreate primary, unique and foreign key constraints alter table COURSE add constraint PK_COURSE primary key (COURSEID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); -- Create table create table STUCOURSE ( selectid VARCHAR2(18) not null, stuid VARCHAR2(11), courseid VARCHAR2(9), schyear VARCHAR2(4), term VARCHAR2(4), redo VARCHAR2(1), selectdate DATE ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K minextents 1 maxextents unlimited ); -- Add comments to the table comment on table STUCOURSE is '学生选课表'; -- Add comments to the columns comment on column STUCOURSE.selectid is '选课id'; comment on column STUCOURSE.stuid is '学号'; comment on column STUCOURSE.courseid is '课程id'; comment on column STUCOURSE.schyear is '年度'; comment on column STUCOURSE.term is '学期'; comment on column STUCOURSE.redo is '是否重修'; comment on column STUCOURSE.selectdate is '选课时间'; -- Create/Recreate primary, unique and foreign key constraints alter table STUCOURSE add constraint PK_STUCOURSE primary key (SELECTID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255; -- Create table create table SCORE ( scoreid VARCHAR2(18) not null, stuid VARCHAR2(11), courseid VARCHAR2(9), score NUMBER, scdate DATE ) tablespace USERS pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited ); -- Add comments to the table comment on table SCORE is '学生成绩表'; -- Add comments to the columns comment on column SCORE.scoreid is '学生成绩id'; comment on column SCORE.stuid is '学生学号'; comment on column SCORE.courseid is '课程id(年度+上下学期+课程序列)'; comment on column SCORE.score is '成绩'; comment on column SCORE.scdate is '成绩录入时间'; -- Create/Recreate primary, unique and foreign key constraints alter table SCORE add constraint PK_SCORE primary key (SCOREID) using index tablespace USERS pctfree 10 initrans 2 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited );
