Oracle資料庫建表+添加資料練習

標籤：arc   key   ima   ons   名稱   student   操作   har   null   

1 --建表 2 --student表+注釋 3 create table student( 4        sno   varchar2(3) not null, 5        sname varchar2(9) not null, 6        ssex  varchar2(3) not null, 7        sbirthday date, 8        sclass varchar2(5), 9        constraint pk_student primary key(sno)10 );11 comment on column student.sno is ‘學號（主鍵）‘;12 comment on column student.sname is ‘學生姓名‘;13 comment on column student.ssex is ‘學生性別‘;14 comment on column student.sbirthday is ‘學生出生年月日‘;15 comment on column student.sclass is ‘學生所在班級‘;16 --course表+注釋17 create table course(18        cno       varchar2(5) not null,19        cname     varchar2(15) not null,20        tno       varchar2(3) not null,21        constraint pk_course primary key(cno)22 );23 comment on column course.cno is ‘課程編號（主鍵）‘;24 comment on column course.cname is ‘課程名稱‘;25 comment on column course.tno is ‘教工編號（外鍵）‘;26 --score表+注釋27 create table score(28         sno   varchar2(3) not null,29         cno   varchar2(5) not null,30         degree   number(4,1),31         constraint pk_score primary key(sno,cno)32 );33 comment on column score.sno is ‘學號（主鍵）‘;34 comment on column score.cno is ‘課程編號（主鍵）‘;35 comment on column score.degree is ‘成績‘;36 --teacher表+注釋37 create table teacher(38        tno   varchar2(3) not null,39        tname varchar2(9) not null,40        tsex  varchar2(3) not null,41        tbirthday date,42        prof  varchar2(9),43        depart varchar2(15) not null,44        constraint pk_teacher primary key(tno)45 );46 comment on column teacher.tno is ‘教工編號（主鍵）‘;47 comment on column teacher.tname is ‘教工姓名‘;48 comment on column teacher.tsex is ‘教工性別‘;49 comment on column teacher.tbirthday is ‘教工出生年月‘;50 comment on column teacher.prof is ‘職稱‘;51 comment on column teacher.depart is ‘教工所在單位‘;52 --添加外鍵53 alter table course add constraint fk_tno foreign key(tno) references teacher(tno);54 alter table score add constraint fk_sno foreign key(sno) references student(sno);55 alter table score add constraint fk_cno foreign key(cno) references course(cno); 56 --添加資料57 --Student表58 insert into student(sno,sname,ssex,sbirthday,sclass) values(108,‘曾華‘,‘男‘,to_date(‘1977-09-01‘,‘yyyy-mm-dd‘),95033);59 insert into student(sno,sname,ssex,sbirthday,sclass) values(105,‘匡明‘,‘男‘,to_date(‘1975-10-02‘,‘yyyy-mm-dd‘),95031);60 insert into student(sno,sname,ssex,sbirthday,sclass) values(107,‘王麗‘,‘女‘,to_date(‘1976-01-23‘,‘yyyy-mm-dd‘),95033);61 insert into student(sno,sname,ssex,sbirthday,sclass) values(101,‘李軍‘,‘男‘,to_date(‘1976-02-20‘,‘yyyy-mm-dd‘),95033);62 insert into student(sno,sname,ssex,sbirthday,sclass) values(109,‘王芳‘,‘女‘,to_date(‘1975-02-10‘,‘yyyy-mm-dd‘),95031);63 insert into student(sno,sname,ssex,sbirthday,sclass) values(103,‘陸君‘,‘男‘,to_date(‘1974-06-03‘,‘yyyy-mm-dd‘),95031);64 --teacher表65 insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(804,‘李誠‘,‘男‘,to_date(‘1958/12/02‘,‘yyyy-mm-dd‘),‘副教授‘,‘電腦系‘);66 insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(856,‘張旭‘,‘男‘,to_date(‘1969/03/12‘,‘yyyy-mm-dd‘),‘講師‘,‘電子工程系‘);67 insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(825,‘王萍‘,‘女‘,to_date(‘1972/05/05‘,‘yyyy-mm-dd‘),‘助教‘,‘電腦系‘);68 insert into teacher(tno,tname,tsex,tbirthday,prof,depart) values(831,‘劉冰‘,‘女‘,to_date(‘1977/08/14‘,‘yyyy-mm-dd‘),‘助教‘,‘電子工程系‘);69 --course表(添加外鍵後要先填teacher表中資料去滿足外鍵約束)70 insert into course(cno,cname,tno) values(‘3-105‘,‘電腦導論‘,825);71 insert into course(cno,cname,tno) values(‘3-245‘,‘作業系統‘,804);72 insert into course(cno,cname,tno) values(‘6-166‘,‘數字電路‘,856);73 insert into course(cno,cname,tno) values(‘9-888‘,‘高等數學‘,831);74 --score表（添加外鍵後要先填Student，course表中資料去滿足外鍵約束）75 insert into score(sno,cno,degree) values(103,‘3-245‘,86);76 insert into score(sno,cno,degree) values(105,‘3-245‘,75);77 insert into score(sno,cno,degree) values(109,‘3-245‘,68);78 insert into score(sno,cno,degree) values(103,‘3-105‘,92);79 insert into score(sno,cno,degree) values(105,‘3-105‘,88);80 insert into score(sno,cno,degree) values(109,‘3-105‘,76);81 insert into score(sno,cno,degree) values(101,‘3-105‘,64);82 insert into score(sno,cno,degree) values(107,‘3-105‘,91);83 insert into score(sno,cno,degree) values(108,‘3-105‘,78);84 insert into score(sno,cno,degree) values(101,‘6-166‘,85);85 insert into score(sno,cno,degree) values(107,‘6-166‘,79);86 insert into score(sno,cno,degree) values(108,‘6-166‘,81);

Oracle資料庫建表+添加資料練習