数据库实验报告.docx
《数据库实验报告.docx》由会员分享,可在线阅读,更多相关《数据库实验报告.docx(19页珍藏版)》请在冰豆网上搜索。
数据库实验报告
数据库原理实验报告
学校:
合肥工业大学
班级:
电子商务09-2班
姓名:
沈万青
学号:
20075056
时间:
2009--10--27
实验语句:
1、创建数据库“学生管理”并导入
createdatabase学生管理
on(
name='学生管理_dat',
filename='D:
\CB\DATA\学生管理dat.mdf',
size=10
)
logon(
name='学生管理_log',
filename='D:
\CB\DATA\学生管理log.ldf',
size=10MB
)
Go
1.1、创建学生表
USE学生管理
createtablestudents
(
snoChar(9)primarykey,
snameChar(10)constraintc1NOTNULL,
sbirthdayDatetime,
ssexChar
(2)constraintc2check(ssexin('男','女')),
sclassChar(20),
sremarkChar(100),
addressChar(40),
zipcodeChar(6),
phoneChar(15),
emailChar(40)
)
1.2、创建学生课程表
USE学生管理
createtablecourse
(
cnochar(6)primarykey,
cnamchar(20),
cpnochar(6),
ctimenumeric(4),
creditnumeric(4),
foreignkey(cpno)referencescourse(cno)
)
1.3、创建学生成绩表
Use学生管理
createtablescore
(
scochar(9),
cnochar(6),
scorechar(4),
primarykey(sco,cno),
foreignkey(sco)referencesstudents(sno),
foreignkey(cno)referencescourse(cno)
)
1.4、创建老师表
use学生管理
createtableteacher(
tnochar(9)primarykey,
tnamechar(10)constraintc3NOTNULL,
tsexchar
(2)constraintc4check(tsexin('男','女')),
tbirthdaydatetime,
positionchar(20),
departmentchar(40),
tamountchar(100),
experiencechar(200)
)
1.5、创建老师从教表
use学生管理
createtableteaching
(
tnochar(9),
cnochar(6),
tdatechar
(2),
classroomchar
(2),
sclasschar
(2),
Primarykey(tno,cno),
foreignkey(tno)referencesteacher(tno),
foreignkey(cno)referencescourse(cno)
)
2.1、往学生表中插入30个学生数据
insertintostudent(sno,sname,sbirthday,sclass)values('011110101','章海潮','1982.02.07','信管系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011111103','王天力','1982.03.04','电商系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011111204','董丞悟','1982.06.06','电商系0102');
insertintostudent(sno,sname,sbirthday,sclass)values('011112110','马丽鹃','1982.04.03','工商系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011113104','杨乾坤','1982.03.23','会计系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011113221','刘抗日','1983.01.16','会计系0102');
insertintostudent(sno,sname,sbirthday,sclass)values('011114111','丘海棠','1983.02.08','旅游系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011115208','齐振国','1982.07.22','旅游系0102');
insertintostudent(sno,sname,sbirthday,sclass)values('001011101','王晓悦','1982.01.12','材料系0001');
insertintostudent(sno,sname,sbirthday,sclass)values('001011212','程伊莲','1982.03.21','材料系0002');
insertintostudent(sno,sname,sbirthday,sclass)values('011110141','章一','1982.03.07','信管系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011114101','刘江','1982.02.08','信管系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011210101','刘涛','1981.02.07','旅游系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011100101','李海','1982.04.07','旅游系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011115101','李丽','1984.08.07','旅游系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('010110101','王朝','1982.12.08','工商系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011113101','王超','1982.12.17','工商系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011110501','马汉','1982.06.05','信管系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011110141','公函','1981.04.07','信管系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011110111','高告','1982.10.07','工商系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011110161','陈芸','1982.05.03','电商系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011110171','陈芸,'1982.01.17','电商系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011111101','陈云','1982.12.27','电商系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011112101','陈运','1982.12.05','信管系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011113101','陈裕,'1982.11.07','信管系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011114101','陈玉','1982.04.07','电商系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011112222','将江','1982.11.07','材料系0001');
insertintostudent(sno,sname,sbirthday,sclass)values('0111104321','蒋飞','1982.02.07','材料系0001');
insertintostudent(sno,sname,sbirthday,sclass)values('011111234','姜文','1984.08.07','材料系0001');
insertintostudent(sno,sname,sbirthday,sclass)values('011114111','江文','1981.09.09','信管系0101');
2.2、往课程表中添加课程数据
insertintocourse(cno,cname,cpno,credit,ctime)values('C001','数据库原理','C005','4','64');
insertintocourse(cno,cname,credit,ctime)values('C002','高等数学','4','72');
insertintocourse(cno,cname,cpno,credit,ctime)values('C003','管理信息系统','C001','4','54');
insertintocourse(cno,cname,cpno,credit,ctime)values('C004','操作系统','C006','3','32');
insertintocourse(cno,cname,cpno,credit,ctime)values('C005','数据结构','C007','4','32');
insertintocourse(cno,cname,credit,ctime)values('C006','计算机文化基础','2','32');
insertintocourse(cno,cname,cpno,credit,ctime)values('C007','C语言程序设计','C006','3','32');
2.3、往成绩表中添加学生成绩
insertintoscore(sno,cno,score)values('011110101','C001','90');
insertintoscore(sno,cno,score)values('011111103','C001','85');
insertintoscore(sno,cno,score)values('011111204','C001','73');
insertintoscore(sno,cno,score)values('011112110','C001','98');
insertintoscore(sno,cno,score)values('011113104','C001','96');
insertintoscore(sno,cno,score)values('011113221','C001','97');
insertintoscore(sno,cno,score)values('011114111','C001','83');
insertintoscore(sno,cno,score)values('011115208','C001','85');
insertintoscore(sno,cno,score)values('011110101','C007','92');
insertintoscore(sno,cno,score)values('011110141','C002','65');
insertintoscore(sno,cno,score)values('011114101','C002','67');
insertintoscore(sno,cno,score)values('011210101','C002','97');
insertintoscore(sno,cno,score)values('011100101','C002','87');
insertintoscore(sno,cno,score)values('011115101','C002','58');
insertintoscore(sno,cno,score)values('010110101','C001','89');
insertintoscore(sno,cno,score)values('011113101','C003','77');
insertintoscore(sno,cno,score)values('011110501','C004','99');
insertintoscore(sno,cno,score)values('011110141','C004','87');
insertintoscore(sno,cno,score)values('011110111','C004','86');
insertintoscore(sno,cno,score)values('011110161','C003','85');
insertintoscore(sno,cno,score)values('011110171','C003','85');
insertintoscore(sno,cno,score)values('011111101','C003','84');
insertintoscore(sno,cno,score)values('011112101','C002','76');
insertintoscore(sno,cno,score)values('011113101','C006','77');
insertintoscore(sno,cno,score)values('011114101','C006','97');
insertintoscore(sno,cno,score)values('011112222','C006','88');
insertintoscore(sno,cno,score)values('0111104321','C006','66');
insertintoscore(sno,cno,score)values('011111234','C006','60');
insertintoscore(sno,cno,score)values('011114111','C001','98');
2.4、往老师表中添加老师数据
insertintoteacher(tno,tname,tsex,department)values('T001','江承基','男','信息管理系');
insertintoteacher(tno,tname,tsex,department)values('T002','梁其征','男','信息管理系');
insertintoteacher(tno,tname,tsex,department)values('T003','徐茉莉','女','信息管理系');
insertintoteacher(tno,tname,tsex,department)values('T004','吴雅云','女','信息管理系');
insertintoteacher(tno,tname,tsex,department)values('T005','杨运铎','男','电子商务系');
insertintoteacher(tno,tname,tsex,department)values('T006','张昌盛','男','电子商务系');
insertintoteacher(tno,tname,tsex,department)values('T007','程力衡','男','电子商务系');
2.5、老师从教状况
insertintoteaching(tno,cno,tdate,classroom,sclass)values('T001','C005','1988-01-09','西二405','信管系0101');
insertintoteaching(tno,cno,tdate,classroom,sclass)values('T002','C007','1990-02-01','西二406','电商系0101');
insertintoteaching(tno,cno,tdate,classroom,sclass)values('T003','C001','1985-09-10','西二504','电商系0102');
insertintoteaching(tno,cno,tdate,classroom,sclass)values('T004','C006','1985-09-10','西二504','电商系0102');
insertintoteaching(tno,cno,tdate,classroom,sclass)values('T005','C004','1989-11-12','主402','会计系0101');
insertintoteaching(tno,cno,tdate,classroom,sclass)values('T006','C002','1991-05-21','西二506','会计系0102');
3、求每门课的平均成绩,并把结果存入average表
insertintoaverage(cno,grade)selectcno,avg(score)fromscoregroupbycno;
4、将学生“马丽鹃”的出生日期改为“1982.8.20”
Updatestudentsetsbirthday='1982.8.20'wheresname='马丽鹃';
5、将所有学生的zipcode属性列值填补上
updatestudentsetzipcode='230000'wheresno='011110101';
updatestudentsetzipcode='230001'wheresno='011111103';
updatestudentsetzipcode='230002'wheresno='011111204';
updatestudentsetzipcode='230003'wheresno='011112110';
updatestudentsetzipcode='230004'wheresno='011113104';
updatestudentsetzipcode='230005'wheresno='011113221';
updatestudentsetzipcode='230006'wheresno='011114111';
updatestudentsetzipcode='230007'wheresno='011115208';
updatestudentsetzipcode='230008'wheresno='001011101';
updatestudentsetzipcode='230009'wheresno='001011212';
6、将average表中的所有课程的平均成绩置零
Updateaveragesetgrade='0';
7、删除average表中的课程号为‘c007’的平均成绩记录
Deletefromaveragewherecno='C007';
8、删除所有average表中平均成绩记录
Deletefromaverage;
9、建立一个临时学生信息表(tstudent),删除该表中的学号前六位为‘001011’的所有学生记录
createtabletstudent(
snochar(9)primarykey,
snamechar(10)constraintc1notnull,
sbirthdayDateTime,
ssexchar
(2)constraintc2check(ssexin('男','女')),
sclasschar(20),
sremarkchar(100),
addresschar(40),
zipcodechar(6),
phonechar(15),
emailchar(40),
)
insertintostudent(sno,sname,sbirthday,sclass)values('011110101','章海潮','1982.02.07','信管系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011111103','王天力','1982.03.04','电商系0101');
insertintostudent(sno,sname,sbirthday,sclass)values('011111204','董丞悟','1982.06.06','电商系0102');
insertintostudent(sno,sname,sbirthday,sclass)values('011112110','马丽鹃','1982.04.03','工商系0101');
insertintostudent(sno,