--建表
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);
Student表 Course表
Score表
Teacher表
Oracle数据库建表+添加数据练习
标签：
