數(shù)據(jù)庫(kù)如下：
--建表
--student表+注釋
create table student(
sno varchar2(3) not null,
sname varchar2(9) not null,
ssex varchar2(3) not null,
sbirthday date,
sclass varchar2(5),
constraint pk_student primary key(sno)
);
comment on column student.sno is '學(xué)號(hào)（主鍵）';
comment on column student.sname is '學(xué)生姓名';
comment on column student.ssex is '學(xué)生性別';
comment on column student.sbirthday is '學(xué)生出生年月日';
comment on column student.sclass is '學(xué)生所在班級(jí)';
--course表+注釋
create table course(
cno varchar2(5) not null,
cname varchar2(15) not null,
tno varchar2(3) not null,
constraint pk_course primary key(cno)
);
comment on column course.cno is '課程編號(hào)（主鍵）';
comment on column course.cname is '課程名稱';
comment on column course.tno is '教工編號(hào)（外鍵）';
--score表+注釋
create table score(
sno varchar2(3) not null,
cno varchar2(5) not null,
degree number(4,1),
constraint pk_score primary key(sno,cno)
);
comment on column score.sno is '學(xué)號(hào)（主鍵）';
comment on column score.cno is '課程編號(hào)（主鍵）';
comment on column score.degree is '成績(jī)';
--teacher表+注釋
create table teacher(
tno varchar2(3) not null,
tname varchar2(9) not null,
tsex varchar2(3) not null,
tbirthday date,
prof varchar2(9),
depart varchar2(15) not null,
constraint pk_teacher primary key(tno)
);
comment on column teacher.tno is '教工編號(hào)（主鍵）';
comment on column teacher.tname is '教工姓名';
comment on column teacher.tsex is '教工性別';
comment on column teacher.tbirthday is '教工出生年月';
comment on column teacher.prof is '職稱';
comment on column teacher.depart is '教工所在單位';
--添加外鍵
alter table course add constraint fk_tno foreign key(tno) references teacher(tno);
alter table score add constraint fk_sno foreign key(sno) references student(sno);
alter table score add constraint fk_cno foreign key(cno) references course(cno);
--添加數(shù)據(jù)
--Student表
insert into student(sno,sname,ssex,sbirthday,sclass) values(108,'曾華','男',to_date('1977-09-01','yyyy-mm-dd'),95033);
insert into student(sno,sname,ssex,sbirthday,sclass) values(105,'匡明','男',to_date('1975-10-02','yyyy-mm-dd'),95031);
insert into student(sno,sname,ssex,sbirthday,sclass) values(107,'王麗','女',to_date('1976-01-23','yyyy-mm-dd'),95033);
insert into student(sno,sname,ssex,sbirthday,sclass) values(101,'李軍','男',to_date('1976-02-20','yyyy-mm-dd'),95033);
insert into student(sno,sname,ssex,sbirthday,sclass) values(109,'王芳','女',to_date('1975-02-10','yyyy-mm-dd'),95031);
insert into student(sno,sname,ssex,sbirthday,sclass) values(103,'陸君','男',to_date('1974-06-03','yyyy-mm-dd'),95031);
--teacher表
insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(804,'李誠(chéng)','男',to_date('1958/12/02','yyyy-mm-dd'),'副教授','計(jì)算機(jī)系');
insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(856,'張旭','男',to_date('1969/03/12','yyyy-mm-dd'),'講師','電子工程系');
insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(825,'王萍','女',to_date('1972/05/05','yyyy-mm-dd'),'助教','計(jì)算機(jī)系');
insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(831,'劉冰','女',to_date('1977/08/14','yyyy-mm-dd'),'助教','電子工程系');
--course表(添加外鍵后要先填teacher表中數(shù)據(jù)去滿足外鍵約束)
insert into course(cno,cname,tno) values('3-105','計(jì)算機(jī)導(dǎo)論',825);
insert into course(cno,cname,tno) values('3-245','操作系統(tǒng)',804);
insert into course(cno,cname,tno) values('6-166','數(shù)字電路',856);
insert into course(cno,cname,tno) values('9-888','高等數(shù)學(xué)',831);
--score表（添加外鍵后要先填Student，course表中數(shù)據(jù)去滿足外鍵約束）
insert into score(sno,cno,degree) values(103,'3-245',86);
insert into score(sno,cno,degree) values(105,'3-245',75);
insert into score(sno,cno,degree) values(109,'3-245',68);
insert into score(sno,cno,degree) values(103,'3-105',92);
insert into score(sno,cno,degree) values(105,'3-105',88);
insert into score(sno,cno,degree) values(109,'3-105',76);
insert into score(sno,cno,degree) values(101,'3-105',64);
insert into score(sno,cno,degree) values(107,'3-105',91);
insert into score(sno,cno,degree) values(108,'3-105',78);
insert into score(sno,cno,degree) values(101,'6-166',85);
insert into score(sno,cno,degree) values(107,'6-166',79);
insert into score(sno,cno,degree) values(108,'6-166',81);
一次性插入多條數(shù)據(jù)：
insert all
into student1
(s1no,
s1name,
s1sex,
s1birthday,
s1class)values (1,'tom1','男',to_date('2001-1-1', 'yyyy-mm-dd'),'2001')
into student1
(s1no,
s1name,
s1sex,
s1birthday,
s1class)values (2,'tom2','男',to_date('2002-2-2', 'yyyy-mm-dd'),'2002')
into student1
(s1no,
s1name,
s1sex,
s1birthday,
s1class)values (3,'tom3','男',to_date('2003-3-3', 'yyyy-mm-dd'),'2003')
select 1 from dual;
建表時(shí)指定表空間及其參數(shù)：
-- Create table
create table X_SMALL_AREA
(
SMALL_AREA_ID NUMBER(10) not null
)
tablespace TBSL_SDDQ
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
minextents 1
maxextents unlimited
);
pctfree 指定一個(gè)百分比 比如說(shuō)20% 那么當(dāng)某個(gè)數(shù)據(jù)塊使用率超過(guò)百分之80的時(shí)候系統(tǒng)就會(huì)停止往這個(gè)數(shù)據(jù)塊里插入新的數(shù)據(jù) 剩下百分之20空間留給將來(lái)對(duì)數(shù)據(jù)的更新使用 這樣可以防止遷移行和鏈接行的出現(xiàn)
initrans指定一個(gè)數(shù)據(jù)塊上初始有多少個(gè)事務(wù)槽 也就是說(shuō)有多少個(gè)事務(wù)能同時(shí)對(duì)此數(shù)據(jù)塊操作
maxtrans 指定最多有多少個(gè)事務(wù)可以并發(fā)操作此數(shù)據(jù)塊
storage 指定一些表的存儲(chǔ)參數(shù) 就拿你那個(gè)例子來(lái)說(shuō)吧
storage
(
initial 64 --初始大小64
minextents 1 --至少有一個(gè)區(qū)
maxextents unlimited --可分配給該表無(wú)限制個(gè)區(qū)
);
