数据库系统概论第三章示例数据库表建立与插入代码

学生表（包括学号，姓名，性别，年龄，所在系）

create table student(
Sno char(9) primary key,
Sname char(20) unique,
Ssex char(2),
Sage smallint,
Sdept char(20)
);

数值插入:

insert into STUDENT(Sno,Sname,Ssex,Sage,Sdept) values('201215121','李勇','男',20,'CS');
insert into STUDENT(Sno,Sname,Ssex,Sage,Sdept) values('201215122','刘晨','女',19,'CS');
insert into STUDENT(Sno,Sname,Ssex,Sage,Sdept) values('201215123','王敏','女',19,'MA');
insert into STUDENT(Sno,Sname,Ssex,Sage,Sdept) values('201215125','张立','男',19,'IS');

---

课程表（包括课程号，课程名，先行课，学分）

create table course(
Cno char(4)primary key,
Cname char(40),
Cpno char(4), 
Ccredit SMALLINT,
foreign key(Cpno) references course(Cno)
);

数值插入：

insert into COURSE(Cno,Cname)values('1','数据库');
insert into COURSE(Cno,Cname)values('2','数学');
insert into COURSE(Cno,Cname)values('3','信息系统');
insert into COURSE(Cno,Cname)values('4','操作系统');
insert into COURSE(Cno,Cname)values('5','数据结构');
insert into COURSE(Cno,Cname)values('6','数据处理');
insert into COURSE(Cno,Cname)values('7','PASCAL语言');

---

选课表（包括学号，课程号，成绩）

create table SC(    
Sno char(9),
Cno char(4),
Grade smallint,
primary key(Sno,Cno),//主键是两个外键
foreign key(Sno) references student(Sno),                                    
foreign key(Cno) references course(Cno)
);

数值插入:

insert into SC(Sno,Cno,Grade) values('201215121','1',92);
insert into SC(Sno,Cno,Grade) values('201215121','2',85);
insert into SC(Sno,Cno,Grade) values('201215121','3',88);
insert into SC(Sno,Cno,Grade) values('201215122','2',90);
insert into SC(Sno,Cno,Grade) values('201215122','3',80);