同济大学数据库实验三

操作过程：

1、创建数据库university,在该数据库中创建student,course,sc,section四张表，代码

为：

create table student

(snum char(4)not null primary key check(substring(snum,1,1)='s'),

sname char(10),

sex char(2)check(sex in('男','女')),

dept char(20),

birthday datetime,

telephone char(12)check(isnumeric(left(telephone,3))=1 and

isnumeric(right(telephone,8))=1))

create table course

(cnum char(4)not null primary key check(substring(cnum,1,1)='c'),

cname char(20),

credits smallint check(credits>=0 and credits<=8),

descr char(10),

dept char(20),

textbook char(40))

create table sc

(snum char(4)foreign key references student,

secnum char(5)foreign key references section,

score int check(score>=0 and score<=100))

create table section

(secnum char(5)primary key check(secnum like'[0-9][0-9][0-9][0-9][0-9]'),

cnum char(4)foreign key references course,

pnum char(6)check(substring(pnum,1,1)='p'))

小结：

1)字段级完整性约束时容易把“'”疏漏掉，还有容易把“'”跟VB中要加的“''”

混淆，例如：sex char(2)check(sex in('男','女'))会写成sex char(2)check(sex in(男,

女))或者sex char(2)check(sex in(''男'', ''女''))

2)在创建表sc和表section时，两个表不能一起执行，因为secnum为section

表的主键，且为sc表的外键。所以建立表的时候要先执行section表，再执行

sc表。

2、在创建的四张表中输入数据，代码和视图分别为：

1)Student表：

insert into student values ('s001','赵剑','男','计算机','1987-3-25','010-********') insert into student values ('s002','王谦','男','交通工程','1986-1-1','027-********') insert into student values ('s003','孙启明','男','土木工程','1987-4-1','021-********') insert into student values ('s004','宇帆','男','机械工程','1987-7-17','021-********') insert into student values ('s005','李晓静','女','生物工程','1988-6-21','030-22222222') insert into student values ('s006','金之林','女','计算机','1988-9-11','040-66666666') insert into student values ('s007','张东晓','男','城市规划','1987-8-2','050-77777777') insert into student values ('s008','海琳','女','城市规划','1988-5-23','070-88888888')

2）course 表：

insert into course values ('c116','大学英语

','6',

'必修课','外语系','《大学英语》，同济大学出版社') insert into course values (

'c120',

'高等数学','6','必修课','数学系','《高等数学》，复旦大学出版社') insert into course values ('c126','大学物理','3','必修课','物理系','《大学物理》，高等教育出版社') insert into course values ('c130','数据库技术','3','限选课','计算机系',

'《数据库技术与应用》，高等教育出版社')

insert into course values ('c132','多媒体技术','3','限选课','计算机系',

'《多媒体技术与应用》，清华大学出版社')

insert into course values ('c135','VB 程序设计','3','限选课','计算机系',

'《https://www.360docs.net/doc/578612859.html, 程序设计》，高等教育出版社')

3）sc 表：

insert into sc values ('s001','11601','77')

insert into sc values ('s001','12001','80')

insert into sc values ('s001','12601','89')

insert into sc values ('s001','13002','90')

insert into sc values ('s001','13201','92')

insert into sc values ('s001','13501','94')

insert into sc values ('s002','11602','90')

insert into sc values ('s002','12601','88')

insert into sc values ('s002','13201','98')

insert into sc values ('s003','11601','90')

insert into sc values ('s003','12002','94')

insert into sc values('s003','12601','88')

insert into sc values('s004','11601','89')

insert into sc values('s004','13001','90')

insert into sc values('s004','13201','92')

insert into sc values('s004','13501','89')

insert into sc values('s005','11602','56')

insert into sc values('s006','11601','88')

insert into sc values('s006','12601','78')

insert into sc values('s007','11602','90')

insert into sc values('s007','13201','95')

insert into sc values('s007','13501','50')

insert into sc values('s008','11601','89')

insert into sc values('s008','12001','90')

insert into sc values('s008','12601','93')

4）section表：

insert into section values('11601','c116','p001') insert into section values('11602','c116','p002') insert into section values('12001','c120','p003') insert into section values('12002','c120','p003') insert into section values('12601','c126','p004') insert into section values('13001','c130','p005') insert into section values('13002','c130','p006') insert into section values('13201','c132','p007') insert into section values('13501','c135','p007')