上海大学数据库实验报告_计算机-数据库.pdf

《上海大学数据库实验报告_计算机-数据库.pdf》由会员分享，可在线阅读，更多相关《上海大学数据库实验报告_计算机-数据库.pdf（17页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、-.优质专业.大学 数据库实验报告 第一组 第 1（第四章：SQL 体系结构、组成、建库建表，索引自学）一、实验课：1.建立 school 数据库 2.在 school 下建立如下数据库表，根据表中数据选取合适的数据类型及宽度，设置各表的主键及表间外键联系：注意：字段名是对应汉字字段名的汉语拼音第一个字母组合而成 学生表 S：学号，性别，出生日期，籍贯，手机，院系号；表 1：S xh xm xb csrq jg sjhm yxh 1101 明 男 1993-03-06 02 1102 晓明 男 1992-12-08 01 1103 颖 女 1993-01-05 01 1104 晶晶 女 199

2、4-11-06 01 1105 成刚 男 1991-06-07 01 1106 二丽 女 1993-05-04 01 1107 晓峰 男 1992-08-16 01 院系表 D：院系号，名称，地址，联系；表 2：D yxh mc 地址 lxdh 01 计算机学院 上大东校区三号楼 65347567 02 通讯学院 上大东校区二号楼 65341234 03 材料学院 上大东校区四号楼 65347890 教师表 T：工号，性别，出生日期，学历，基本工资，院系编号；表 3：T gh xm xb csrq xl jbgz yxh 0101 迪茂 男 1973-03-06 副教授 3567.00 01

3、0102 马小红 女 1972-12-08 讲师 2845.00 01 0201 心颖 女 1960-01-05 教授 4200.00 02 0103 宝钢 男 1980-11-06 讲师 2554.00 01 课程表 C：课号，课名，学分，学时，院系号；（默认学分 4，学时 40）表 4：C kh km xf xs yxh-.优质专业.08305001 离散数学 4 40 01 08305002 数据库原理 4 50 01 08305003 数据结构 4 50 01 08305004 系统结构 6 60 01 08301001 分子物理学 4 40 03 08302001 通信学 3 30

4、02 开课表 O：学期，课号，工号，上课时间；表 5：O xq kh gh sksj 2012-2013 秋季 08305001 0103 星期三 5-8 2012-2013 冬季 08305002 0101 星期三 1-4 2012-2013 冬季 08305002 0102 星期三 1-4 2012-2013 冬季 08305002 0103 星期三 1-4 2012-2013 冬季 08305003 0102 星期五 5-8 2013-2014 秋季 08305004 0101 星期二 1-4 2013-2014 秋季 08305001 0102 星期一 5-8 2013-2014 冬季

5、 08302001 0201 星期一 5-8 选课表 E：学号，学期，课号，工号，平时成绩，考试成绩，总评成绩；（成绩围 1-100）表 6：E xh xq kh gh pscj kscj zpcj 1101 2012-2013 秋季 08305001 0103 60 60 60 1102 2012-2013 秋季 08305001 0103 87 87 87 1102 2012-2013 冬季 08305002 0101 82 82 82 1102 2013-2014 秋季 08305004 0101 null null null 1103 2012-2013 秋季 08305001 010

6、3 56 56 56 1103 2012-2013 冬季 08305002 0102 75 75 75 1103 2012-2013 冬季 08305003 0102 84 84 84 1103 2013-2014 秋季 08305001 0102 null null null 1103 2013-2014 秋季 08305004 0101 null null null 1104 2012-2013 秋季 08305001 0103 74 74 74 1104 2013-2014 冬季 08302001 0201 null null null 1106 2012-2013 秋季 0830500

7、1 0103 85 85 85 1106 2012-2013 冬季 08305002 0103 66 66 66 1107 2012-2013 秋季 08305001 0103 90 90 90 1107 2012-2013 冬季 08305003 0102 79 79 79 1107 2013-2014 秋季 08305004 0101 null null null 3.在学生表中建立索引 idx1：院系号升序，降序 在课程表中建立索引 idx2：课名 号上课时间表秋季冬季冬季冬季冬季秋季秋季冬季星期三星期三星期三星期三星期五星期二星期一星期一选课表学号学期课号工号平时成绩考试成绩总评成绩成

8、绩围表秋季秋季冬季秋季秋季冬季冬季秋季秋季秋季冬季秋季冬季秋季投影选择多表连接和嵌套排序自学一实验课查询年进校年龄大于岁的男学生的学号与查询年进校年龄大于岁的男学生的学号与男检索晓明不学的课程的课程号晓明检索马小红老师所授课程的学年学期课程号上课时间优质专业马小红学院男教授检索学号比颖同学大年龄比颖同学小的同学学号优质专业颖检索同时选修了和的学生学号和第第四章除法聚合函数分组集合操作外连接自学一实验课验证在万个以上记录时在索引和不索引时的查询时间区别验证在万个以-.优质专业.create database school go use school create table S(xh int,x

9、m char(10),xb char(2),csrq date,jg char(20),sjhm bigint,yxh char(2),primary key(xh),foreign key(yxh)references D(yxh)create table D(yxh char(2),mc char(20),地址 char(50),lxdh int,primary key(yxh)create table T(gh char(4),xm char(10),xb char(2),csrq date,xl char(10),jbgz numeric(6,2),yxh char(2),primar

10、y key(gh),foreign key(yxh)references D(yxh)create table C 号上课时间表秋季冬季冬季冬季冬季秋季秋季冬季星期三星期三星期三星期三星期五星期二星期一星期一选课表学号学期课号工号平时成绩考试成绩总评成绩成绩围表秋季秋季冬季秋季秋季冬季冬季秋季秋季秋季冬季秋季冬季秋季投影选择多表连接和嵌套排序自学一实验课查询年进校年龄大于岁的男学生的学号与查询年进校年龄大于岁的男学生的学号与男检索晓明不学的课程的课程号晓明检索马小红老师所授课程的学年学期课程号上课时间优质专业马小红学院男教授检索学号比颖同学大年龄比颖同学小的同学学号优质专业颖检索同时选修了和的

11、学生学号和第第四章除法聚合函数分组集合操作外连接自学一实验课验证在万个以上记录时在索引和不索引时的查询时间区别验证在万个以-.优质专业.(kh char(8),km char(20),xf int,xs int,yxh char(2),primary key(kh),foreign key(yxh)references D(yxh)create table O(xq char(20),kh char(8),gh char(4),sksj char(20),primary key(xq,kh,gh),foreign key(kh)references C(kh),foreign key(gh)r

12、eferences T(gh)create table E(xh int,xq char(20),kh char(8),gh 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(xh,xq,kh,gh),foreign key(gh)references T(gh),foreign key(kh)references C(kh),foreign key(xh)referenc

13、es S(xh)create unique index idx1 on S(yxh asc,xm desc);create unique index idx2 on C(km);号上课时间表秋季冬季冬季冬季冬季秋季秋季冬季星期三星期三星期三星期三星期五星期二星期一星期一选课表学号学期课号工号平时成绩考试成绩总评成绩成绩围表秋季秋季冬季秋季秋季冬季冬季秋季秋季秋季冬季秋季冬季秋季投影选择多表连接和嵌套排序自学一实验课查询年进校年龄大于岁的男学生的学号与查询年进校年龄大于岁的男学生的学号与男检索晓明不学的课程的课程号晓明检索马小红老师所授课程的学年学期课程号上课时间优质专业马小红学院男教授检索学号

14、比颖同学大年龄比颖同学小的同学学号优质专业颖检索同时选修了和的学生学号和第第四章除法聚合函数分组集合操作外连接自学一实验课验证在万个以上记录时在索引和不索引时的查询时间区别验证在万个以-.优质专业.第 2（第四章：投影、选择、多表连接和嵌套，排序自学）一、实验课：1.查询 2011 年进校年龄大于 20 岁的男学生的学号与。-1.查询2011年进校年龄大于20岁的男学生的学号与。SELECT XH,XM FROM S WHERE YEAR(2011-YEAR(CSRQ)20 AND XB=男 2.检索晓明不学的课程的课程号。SELECT KH FROM O EXCEPT SELECT KH F

15、ROM S,E WHERE XM=晓明 AND S.XH=E.XH 3.检索马小红老师所授课程的学年，学期，课程号，上课时间。号上课时间表秋季冬季冬季冬季冬季秋季秋季冬季星期三星期三星期三星期三星期五星期二星期一星期一选课表学号学期课号工号平时成绩考试成绩总评成绩成绩围表秋季秋季冬季秋季秋季冬季冬季秋季秋季秋季冬季秋季冬季秋季投影选择多表连接和嵌套排序自学一实验课查询年进校年龄大于岁的男学生的学号与查询年进校年龄大于岁的男学生的学号与男检索晓明不学的课程的课程号晓明检索马小红老师所授课程的学年学期课程号上课时间优质专业马小红学院男教授检索学号比颖同学大年龄比颖同学小的同学学号优质专业颖检索同时

16、选修了和的学生学号和第第四章除法聚合函数分组集合操作外连接自学一实验课验证在万个以上记录时在索引和不索引时的查询时间区别验证在万个以-.优质专业.SELECT XQ,KH,SKSJ FROM O,T WHERE T.XM=马小红 AND T.GH=O.GH 4.查询计算机学院男生总评成绩及格、教授开设的课程的课程号、课名、开课教师，按开课教师升序，课程号降序排序。SELECT E.KH,C.KM,T.XM FROM E,C,T WHERE E.ZPCJ=60 AND E.XH IN(SELECT S.XH FROM S JOIN D ON S.YXH=D.YXH WHERE D.MC=计算机学

17、院 AND S.XB=男)INTERSECT SELECT E.KH,C.KM,T.XM FROM T,E,C WHERE T.XL=教授 AND T.GH=E.GH AND E.KH=C.KH ORDER BY T.XM,E.KH DESC 5.检索学号比颖同学大，年龄比颖同学小的同学学号、。号上课时间表秋季冬季冬季冬季冬季秋季秋季冬季星期三星期三星期三星期三星期五星期二星期一星期一选课表学号学期课号工号平时成绩考试成绩总评成绩成绩围表秋季秋季冬季秋季秋季冬季冬季秋季秋季秋季冬季秋季冬季秋季投影选择多表连接和嵌套排序自学一实验课查询年进校年龄大于岁的男学生的学号与查询年进校年龄大于岁的男学生

18、的学号与男检索晓明不学的课程的课程号晓明检索马小红老师所授课程的学年学期课程号上课时间优质专业马小红学院男教授检索学号比颖同学大年龄比颖同学小的同学学号优质专业颖检索同时选修了和的学生学号和第第四章除法聚合函数分组集合操作外连接自学一实验课验证在万个以上记录时在索引和不索引时的查询时间区别验证在万个以-.优质专业.SELECT B.XH,B.XM FROM S AS A,S AS B WHERE A.XM=颖 AND B.XHA.XH AND B.CSRQA.CSRQ 6.检索同时选修了“08305001”和“08305002”的学生学号和。SELECT DISTINCT S.XH,S.XM

19、FROM E AS A,E AS B,S WHERE A.KH=08305001 AND B.KH=08305002 AND A.XH=B.XH AND A.XH=S.XH 第 3（第四章：除法、聚合函数、分组、集合操作，外连接自学）一、实验课：1.验证在 1000 万个以上记录时在索引和不索引时的查询时间区别。-1.验证在 1000 万个以上记录时在索引和不索引时的查询时间区别。declare i int set i=1 while i=1000000 begin insert into A values(i)set i=i+1 end 号上课时间表秋季冬季冬季冬季冬季秋季秋季冬季星期三星期

20、三星期三星期三星期五星期二星期一星期一选课表学号学期课号工号平时成绩考试成绩总评成绩成绩围表秋季秋季冬季秋季秋季冬季冬季秋季秋季秋季冬季秋季冬季秋季投影选择多表连接和嵌套排序自学一实验课查询年进校年龄大于岁的男学生的学号与查询年进校年龄大于岁的男学生的学号与男检索晓明不学的课程的课程号晓明检索马小红老师所授课程的学年学期课程号上课时间优质专业马小红学院男教授检索学号比颖同学大年龄比颖同学小的同学学号优质专业颖检索同时选修了和的学生学号和第第四章除法聚合函数分组集合操作外连接自学一实验课验证在万个以上记录时在索引和不索引时的查询时间区别验证在万个以-.优质专业.SELECT S FROM A W

21、HERE S=1000000 CREATE TABLE A(S INT)2.查询每个学生选课情况（包括没有选修课程的学生）。SELECT S.XH,S.XM,XQ,E.KH,PSCJ,KSCJ,ZPCJ FROM S LEFT JOIN E ON E.XH=S.XH ORDER BY S.XH 号上课时间表秋季冬季冬季冬季冬季秋季秋季冬季星期三星期三星期三星期三星期五星期二星期一星期一选课表学号学期课号工号平时成绩考试成绩总评成绩成绩围表秋季秋季冬季秋季秋季冬季冬季秋季秋季秋季冬季秋季冬季秋季投影选择多表连接和嵌套排序自学一实验课查询年进校年龄大于岁的男学生的学号与查询年进校年龄大于岁的男学生

22、的学号与男检索晓明不学的课程的课程号晓明检索马小红老师所授课程的学年学期课程号上课时间优质专业马小红学院男教授检索学号比颖同学大年龄比颖同学小的同学学号优质专业颖检索同时选修了和的学生学号和第第四章除法聚合函数分组集合操作外连接自学一实验课验证在万个以上记录时在索引和不索引时的查询时间区别验证在万个以-.优质专业.3.检索所有课程都选修的的学生的学号与。SELECT XH,XM FROM S WHERE NOT EXISTS(SELECT*FROM C WHERE NOT EXISTS(SELECT*FROM E WHERE E.XH=S.XH AND C.KH=E.KH)号上课时间表秋季冬季

23、冬季冬季冬季秋季秋季冬季星期三星期三星期三星期三星期五星期二星期一星期一选课表学号学期课号工号平时成绩考试成绩总评成绩成绩围表秋季秋季冬季秋季秋季冬季冬季秋季秋季秋季冬季秋季冬季秋季投影选择多表连接和嵌套排序自学一实验课查询年进校年龄大于岁的男学生的学号与查询年进校年龄大于岁的男学生的学号与男检索晓明不学的课程的课程号晓明检索马小红老师所授课程的学年学期课程号上课时间优质专业马小红学院男教授检索学号比颖同学大年龄比颖同学小的同学学号优质专业颖检索同时选修了和的学生学号和第第四章除法聚合函数分组集合操作外连接自学一实验课验证在万个以上记录时在索引和不索引时的查询时间区别验证在万个以-.优质专业.

24、4.检索选修课程包含 1106 同学所学全部课程的学生学号和。SELECT DISTINCT XH,XM FROM S WHERE NOT EXISTS(SELECT*FROM E AS E1 WHERE E1.XH=1106 AND NOT EXISTS(SELECT*FROM E AS E2 WHERE E2.XH=S.XH AND E1.KH=E2.KH)ORDER BY XH 号上课时间表秋季冬季冬季冬季冬季秋季秋季冬季星期三星期三星期三星期三星期五星期二星期一星期一选课表学号学期课号工号平时成绩考试成绩总评成绩成绩围表秋季秋季冬季秋季秋季冬季冬季秋季秋季秋季冬季秋季冬季秋季投影选择多

25、表连接和嵌套排序自学一实验课查询年进校年龄大于岁的男学生的学号与查询年进校年龄大于岁的男学生的学号与男检索晓明不学的课程的课程号晓明检索马小红老师所授课程的学年学期课程号上课时间优质专业马小红学院男教授检索学号比颖同学大年龄比颖同学小的同学学号优质专业颖检索同时选修了和的学生学号和第第四章除法聚合函数分组集合操作外连接自学一实验课验证在万个以上记录时在索引和不索引时的查询时间区别验证在万个以-.优质专业.5.查询每门课程中分数最高的学生学号和学生。SELECT S.XM,S.XH,C.KM,A.ZPCJ FROM S,C,E AS A WHERE S.XH=A.XH AND A.KH=C.KH

26、 AND A.ZPCJ=(SELECT MAX(ZPCJ)from E WHERE E.KH=A.KH)号上课时间表秋季冬季冬季冬季冬季秋季秋季冬季星期三星期三星期三星期三星期五星期二星期一星期一选课表学号学期课号工号平时成绩考试成绩总评成绩成绩围表秋季秋季冬季秋季秋季冬季冬季秋季秋季秋季冬季秋季冬季秋季投影选择多表连接和嵌套排序自学一实验课查询年进校年龄大于岁的男学生的学号与查询年进校年龄大于岁的男学生的学号与男检索晓明不学的课程的课程号晓明检索马小红老师所授课程的学年学期课程号上课时间优质专业马小红学院男教授检索学号比颖同学大年龄比颖同学小的同学学号优质专业颖检索同时选修了和的学生学号和第

27、第四章除法聚合函数分组集合操作外连接自学一实验课验证在万个以上记录时在索引和不索引时的查询时间区别验证在万个以-.优质专业.6.查询年龄小于本学院平均年龄，所有课程总评成绩都高于所选课程平均总评成绩的学生学号、和平均总评成绩，按年龄排序。SELECT X.XH,X.XM,AVG(ZPCJ)AS 平均成绩,DateDiff(YYYY,CSRQ,2013-12-22)AS 年龄 FROM S,E AS E3,(SELECT S1.XH,S1.XM FROM(SELECT XH,XM,YXH,DateDiff(YYYY,CSRQ,2013-12-22)AS 年龄 FROM S)AS S1,(SELE

28、CT YXH,avg(DateDiff(YYYY,CSRQ,2013-12-22)as avg_age FROM S GROUP BY YXH)AS S2 WHERE S1.YXH=S2.YXH AND S1.年龄=平均成绩 OR E2.ZPCJ=NULL)AS Y WHERE X.XH=Y.XH AND X.XH=E3.XH AND S.XH=X.XH GROUP BY X.XH,X.XM,DateDiff(YYYY,CSRQ,2013-12-22)ORDER BY 年龄 号上课时间表秋季冬季冬季冬季冬季秋季秋季冬季星期三星期三星期三星期三星期五星期二星期一星期一选课表学号学期课号工号平时成

29、绩考试成绩总评成绩成绩围表秋季秋季冬季秋季秋季冬季冬季秋季秋季秋季冬季秋季冬季秋季投影选择多表连接和嵌套排序自学一实验课查询年进校年龄大于岁的男学生的学号与查询年进校年龄大于岁的男学生的学号与男检索晓明不学的课程的课程号晓明检索马小红老师所授课程的学年学期课程号上课时间优质专业马小红学院男教授检索学号比颖同学大年龄比颖同学小的同学学号优质专业颖检索同时选修了和的学生学号和第第四章除法聚合函数分组集合操作外连接自学一实验课验证在万个以上记录时在索引和不索引时的查询时间区别验证在万个以-.优质专业.号上课时间表秋季冬季冬季冬季冬季秋季秋季冬季星期三星期三星期三星期三星期五星期二星期一星期一选课表学

30、号学期课号工号平时成绩考试成绩总评成绩成绩围表秋季秋季冬季秋季秋季冬季冬季秋季秋季秋季冬季秋季冬季秋季投影选择多表连接和嵌套排序自学一实验课查询年进校年龄大于岁的男学生的学号与查询年进校年龄大于岁的男学生的学号与男检索晓明不学的课程的课程号晓明检索马小红老师所授课程的学年学期课程号上课时间优质专业马小红学院男教授检索学号比颖同学大年龄比颖同学小的同学学号优质专业颖检索同时选修了和的学生学号和第第四章除法聚合函数分组集合操作外连接自学一实验课验证在万个以上记录时在索引和不索引时的查询时间区别验证在万个以-.优质专业.第 4（第四章：数据更新、视图、嵌入式 SQL 部分自学）一、实验课：1.建立计

31、算机学院总评不及格成绩学生的视图，包括学生学号、性别、手机、所选课程和成绩。CREATE VIEW A AS SELECT S.XH,XM,XB,SJHM,KH,PSCJ,KSCJ,ZPCJ FROM S,E,D WHERE D.MC=计 算 机 学 院 AND E.ZPCJ ALL(SELECT DateDiff(YYYY,CSRQ,2013-12-22)AS 年龄 FROM S WHERE XB=女)4.在 E表中修改 08305001 课程的平时成绩，若成绩小于等于 75 分时提高 5%，若成绩大于 75 分时提高 4%。号上课时间表秋季冬季冬季冬季冬季秋季秋季冬季星期三星期三星期三星期

32、三星期五星期二星期一星期一选课表学号学期课号工号平时成绩考试成绩总评成绩成绩围表秋季秋季冬季秋季秋季冬季冬季秋季秋季秋季冬季秋季冬季秋季投影选择多表连接和嵌套排序自学一实验课查询年进校年龄大于岁的男学生的学号与查询年进校年龄大于岁的男学生的学号与男检索晓明不学的课程的课程号晓明检索马小红老师所授课程的学年学期课程号上课时间优质专业马小红学院男教授检索学号比颖同学大年龄比颖同学小的同学学号优质专业颖检索同时选修了和的学生学号和第第四章除法聚合函数分组集合操作外连接自学一实验课验证在万个以上记录时在索引和不索引时的查询时间区别验证在万个以-.优质专业.-先都提高 4%，然后成绩小于等于 75*（1

33、+0.04）的先除以 1.04，然后乘 1.05 UPDATE E SET ZPCJ=ZPCJ*(1+0.04)WHERE KH=08305001 UPDATE E SET 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)不及格 号上课时间表秋季冬季冬季冬季冬季秋季秋季冬季星期三星期三星期三星期三星期五星期二星期一星期一选课表学号学期课号工号平时成绩考试成绩总评成绩成绩围表秋季秋季冬季秋季秋季冬季冬季秋季秋季秋季冬季秋季冬季秋季投影选择多表连接和嵌套排序自学一实验课查询年进校年龄大于岁的男学生的学号与查询年进校年龄大于岁的男学生的学号与男检索晓明不学的课程的课程号晓明检索马小红老师所授课程的学年学期课程号上课时间优质专业马小红学院男教授检索学号比颖同学大年龄比颖同学小的同学学号优质专业颖检索同时选修了和的学生学号和第第四章除法聚合函数分组集合操作外连接自学一实验课验证在万个以上记录时在索引和不索引时的查询时间区别验证在万个以