上海大学数据库实验报告.doc

《上海大学数据库实验报告.doc》由会员分享，可在线阅读，更多相关《上海大学数据库实验报告.doc（12页珍藏版）》请在课桌文档上搜索。

1、-大学数据库实验报告第一组第1（第四章：SQL体系结构、组成、建库建表，索引自学）一、实验课： 1. 建立school数据库2. 在school下建立如下数据库表，根据表中数据选取合适的数据类型及宽度，设置各表的主键及表间外键联系：注意：字段名是对应汉字字段名的汉语拼音第一个字母组合而成l 学生表S：*，性别，出生日期，籍贯，手机，院系号；表1：S*h*m*bcsrqjgsjhmy*h1101明男1993-03-06021102晓明男1992-12-08011103颖女1993-01-05011104晶晶女1994-11-06011105成刚男1991-06-07011106二丽女1993-0

2、5-04011107晓峰男1992-08-1601l 院系表D：院系号，名称，地址，联系；表2：Dy*hmc地址l*dh01计算机学院上大东校区三号楼6534756702通讯学院上大东校区二号楼6534123403材料学院上大东校区四号楼65347890l 教师表T：工号，性别，出生日期，学历，基本工资，院系编号；表3：Tgh*m*bcsrq*lgzy*h0101迪茂男1973-03-06副教授3567.00010102马小红女1972-12-08讲师2845.00010201心颖女1960-01-05教授4200.00020103宝钢男1980-11-06讲师2554.0001l 课程表C：

3、课号，课名，学分，学时，院系号；（默认学分4，学时40）表4：Ckhkm*f*sy*h08305001离散数学4400108305002数据库原理4500108305003数据结构4500108305004系统结构6600108301001分子物理学4400308302001通信学33002l 开课表O：学期，课号，工号，上课时间；表5：O*qkhghsksj2012-2013秋季083050010103星期三5-82012-2013冬季083050020101星期三1-42012-2013冬季083050020102星期三1-42012-2013冬季083050020103星期三1-4201

4、2-2013冬季083050030102星期五5-82013-2014秋季083050040101星期二1-42013-2014秋季083050010102星期一5-82013-2014冬季083020010201星期一5-8l 选课表E：*，学期，课号，工号，平时成绩，考试成绩，总评成绩；（成绩围1-100）表6：E*h*qkhghpscjkscjzpcj11012012-2013秋季08305001010360606011022012-2013秋季08305001010387878711022012-2013冬季08305002010182828211022013-2014秋季083050

5、040101nullnullnull11032012-2013秋季08305001010356565611032012-2013冬季08305002010275757511032012-2013冬季08305003010284848411032013-2014秋季083050010102nullnullnull11032013-2014秋季083050040101nullnullnull11042012-2013秋季08305001010374747411042013-2014冬季083020010201nullnullnull11062012-2013秋季083050010103858585

6、11062012-2013冬季08305002010366666611072012-2013秋季08305001010390909011072012-2013冬季08305003010279797911072013-2014秋季083050040101nullnullnull3. 在学生表中建立索引id*1：院系号升序，降序在课程表中建立索引id*2：课名create database schoolgouse schoolcreate table S(*h int,*m char(10),*b char(2),csrq date,jg char(20),sjhm bigint,y*h char

7、(2),primary key (*h),foreign key (y*h) references D(y*h)create table D(y*h char(2),mc char(20),地址 char(50),l*dh int,primary key (y*h)create table T(gh char(4),*m char(10),*b char(2),csrq date,*l char(10),gz numeric(6,2),y*h char(2),primary key (gh),foreign key (y*h) references D(y*h)create table C(k

8、h char(8),km char(20),*f int,*s int,y*h char(2),primary key (kh),foreign key (y*h) references D(y*h)create table O(*q char(20),kh char(8),gh char(4),sksj char(20),primary key (*q,kh,gh),foreign key (kh) references C(kh),foreign key (gh) references T(gh)create table E(*h int,*q char(20),kh char(8),gh

9、 char(4),pscj int CHECK(pscj BETWEEN 1 AND 100),kscj int CHECK(kscj BETWEEN 1 AND 100),zpcj int CHECK(zpcj BETWEEN 1 AND 100),primary key (*h,*q,kh,gh),foreign key (gh) references T(gh),foreign key (kh) references C(kh),foreign key (*h) references S(*h)create unique inde* id*1 on S(y*h asc,*m desc);

10、create unique inde* id*2 on C(km);第2（第四章：投影、选择、多表连接和嵌套，排序自学）一、实验课：1. 查询2011年进校年龄大于20岁的男学生的*与。-1.查询2011年进校年龄大于20岁的男学生的*与。SELECT*H,*MFROMSWHEREYEAR(2011-YEAR(CSRQ)20 AND*B=男2. 检索晓明不学的课程的课程号。SELECTKHFROMOE*CEPTSELECTKHFROMS,EWHERE*M=晓明ANDS.*H=E.*H3. 检索马小红老师所授课程的学年，学期，课程号，上课时间。SELECT*Q,KH,SKSJFROMO,TWHE

11、RET.*M=马小红ANDT.GH=O.GH4. 查询计算机学院男生总评成绩及格、教授开设的课程的课程号、课名、开课教师，按开课教师升序，课程号降序排序。SELECTE.KH,C.KM,T.*MFROME,C,TWHEREE.ZPCJ=60 ANDE.*HIN(SELECTS.*HFROMSJOINDONS.Y*H=D.Y*HWHERED.MC=计算机学院ANDS.*B=男)INTERSECTSELECTE.KH,C.KM,T.*MFROMT,E,CWHERET.*L=教授ANDT.GH=E.GHANDE.KH=C.KHORDERBYT.*M,E.KHDESC5. 检索*比颖同学大，年龄比颖同

12、学小的同学*、。SELECTB.*H,B.*MFROMSASA,SASBWHEREA.*M=颖ANDB.*HA.*HANDB.CSRQA.CSRQ6. 检索同时选修了08305001”和08305002”的学生*和。SELECTDISTINCTS.*H,S.*MFROMEASA,EASB,SWHEREA.KH=08305001ANDB.KH=08305002ANDA.*H=B.*HANDA.*H=S.*H第3（第四章：除法、聚合函数、分组、集合操作，外连接自学）一、实验课：1. 验证在1000万个以上记录时在索引和不索引时的查询时间区别。-1.验证在1000万个以上记录时在索引和不索引时的查询

13、时间区别。declare i intset i=1while i = 1000000begininsert into A values(i)set i=i+1endSELECT SFROM AWHERE S=1000000CREATE TABLE A(S INT)2. 查询每个学生选课情况（包括没有选修课程的学生）。SELECT S.*H,S.*M,*Q,E.KH,PSCJ,KSCJ,ZPCJFROM S LEFT JOIN E ON E.*H=S.*HORDER BY S.*H3. 检索所有课程都选修的的学生的*与。SELECT *H,*MFROM SWHERE NOT E*ISTS(SEL

14、ECT *FROM CWHERE NOT E*ISTS(SELECT *FROM EWHERE E.*H=S.*H AND C.KH=E.KH)4. 检索选修课程包含1106同学所学全部课程的学生*和。SELECT DISTINCT *H,*MFROM SWHERE NOT E*ISTS(SELECT *FROM E AS E1WHERE E1.*H=1106 AND NOT E*ISTS(SELECT *FROM E AS E2WHERE E2.*H=S.*H AND E1.KH=E2.KH)ORDER BY *H5. 查询每门课程中分数最高的学生*和学生。SELECT S.*M,S.*H,

15、C.KM,A.ZPCJFROM S,C,E AS AWHERE S.*H=A.*H AND A.KH=C.KH AND A.ZPCJ=(SELECT MA*(ZPCJ) from E WHERE E.KH=A.KH )6. 查询年龄小于本学院平均年龄，所有课程总评成绩都高于所选课程平均总评成绩的学生*、和平均总评成绩，按年龄排序。SELECT *.*H,*.*M,AVG(ZPCJ) AS 平均成绩,DateDiff(YYYY,CSRQ,2013-12-22) AS 年龄FROM S,E AS E3,(SELECT S1.*H,S1.*MFROM(SELECT *H,*M,Y*H,DateDif

16、f(YYYY,CSRQ,2013-12-22) AS 年龄FROM S) AS S1,(SELECT Y*H,avg(DateDiff(YYYY,CSRQ,2013-12-22) as avg_age FROM SGROUP BY Y*H) AS S2WHERE S1.Y*H=S2.Y*H AND S1.年龄=平均成绩 OR E2.ZPCJ=NULL)AS YWHERE *.*H=Y.*H AND *.*H=E3.*H AND S.*H=*.*HGROUP BY *.*H,*.*M,DateDiff(YYYY,CSRQ,2013-12-22)ORDER BY 年龄第4（第四章：数据更新、视图、

17、嵌入式SQL部分自学）一、实验课：1. 建立计算机学院总评不及格成绩学生的视图，包括学生*、性别、手机、所选课程和成绩。CREATE VIEW AAS SELECT S.*H,*M,*B,SJHM,KH,PSCJ,KSCJ,ZPCJFROM S,E,DWHERE D.MC=计算机学院 AND E.ZPCJALL(SELECT DateDiff(YYYY,CSRQ,2013-12-22) AS 年龄 FROM SWHERE *B=女)4. 在E表中修改08305001课程的平时成绩，若成绩小于等于75分时提高5%，若成绩大于75分时提高4%。-先都提高4%，然后成绩小于等于75*（1+0.04）的先除以1.04，然后乘1.05UPDATE ESET ZPCJ=ZPCJ*(1+0.04)WHERE KH=08305001UPDATE ESET ZPCJ=ZPCJ/(1+0.04)*(1+0.05)WHERE KH=08305001 AND ZPCJ=90) 优,(SELECT COUNT(*) FROM E WHERE ZPCJ=80 AND ZPCJ=70 AND ZPCJ=60 AND ZPCJ70) 及格,(SELECT COUNT(*) FROM E WHERE ZPCJ60) 不及格. z.