上海大学数据库实验报告

1、上海大学数据库实验报告第一组第1周（第四章：SQL体系结构、组成、建库建表，索引自学）一、实验课： 1. 建立school数据库2. 在school下建立如下数据库表，根据表中数据选取合适的数据类型及宽度，设置各表的主键及表间外键联系：注意：字段名是对应汉字字段名的汉语拼音第一个字母组合而成l 学生表S：学号，姓名，性别，出生日期，籍贯，手机号码，院系号；表1：Sxhxmxbcsrqjgsjhmyxh1101李明男1993-03-06上海021102刘晓明男1992-12-08安徽011103张颖女1993-01-05江苏011104刘晶晶女1994-11-06上海011105刘成刚男1991

2、-06-07上海011106李二丽女1993-05-04江苏011107张晓峰男1992-08-16浙江01l 院系表D：院系号，名称，地址，联系电话；表2：Dyxhmc地址lxdh01计算机学院上大东校区三号楼02通讯学院上大东校区二号楼03材料学院上大东校区四号楼l 教师表T：工号，姓名，性别，出生日期，学历，基本工资，院系编号；表3：Tghxmxbcsrqxljbgzyxh0101陈迪茂男1973-03-06副教授3567.00010102马小红女1972-12-08讲师2845.00010201张心颖女1960-01-05教授4200.00020103吴宝钢男1980-11-06讲师2

3、554.0001l 课程表C：课号，课名，学分，学时，院系号；（默认学分4，学时40）表4：Ckhkmxfxsyxh离散数学44001数据库原理45001数据结构45001系统结构66001分子物理学44003通信学33002l 开课表O：学期，课号，工号，上课时间；表5：Oxqkhghsksj2012-2013秋季0103星期三5-82012-2013冬季0101星期三1-42012-2013冬季0102星期三1-42012-2013冬季0103星期三1-42012-2013冬季0102星期五5-82013-2014秋季0101星期二1-42013-2014秋季0102星期一5-82013-

4、2014冬季0201星期一5-8l 选课表E：学号，学期，课号，工号，平时成绩，考试成绩，总评成绩；（成绩范围1-100）表6：Exhxqkhghpscjkscjzpcj11012012-2013秋季010360606011022012-2013秋季010387878711022012-2013冬季010182828211022013-2014秋季0101nullnullnull11032012-2013秋季010356565611032012-2013冬季010275757511032012-2013冬季010284848411032013-2014秋季0102nullnullnull110

5、32013-2014秋季0101nullnullnull11042012-2013秋季010374747411042013-2014冬季0201nullnullnull11062012-2013秋季010385858511062012-2013冬季010366666611072012-2013秋季010390909011072012-2013冬季010279797911072013-2014秋季0101nullnullnull3. 在学生表中建立索引idx1：院系号升序，姓名降序在课程表中建立索引idx2：课名create database schoolgouse schoolcreate t

6、able S(xh int,xm 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)

7、,yxh char(2),primary key (gh),foreign key (yxh) references D(yxh)create table C(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)

8、,foreign key (gh) references 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)

9、,foreign key (xh) references S(xh)create unique index idx1 on S(yxh asc,xm desc);create unique index idx2 on C(km);第2周（第四章：投影、选择、多表连接和嵌套，排序自学）一、实验课：1. 查询2011年进校年龄大于20岁的男学生的学号与姓名。-1.查询2011年进校年龄大于20岁的男学生的学号与姓名。SELECT XH,XMFROM SWHERE YEAR(2011-YEAR(CSRQ)20 AND XB=男2. 检索刘晓明不学的课程的课程号。SELECT KHFROM OEXCE

10、PTSELECT KH FROM S,EWHERE XM=刘晓明 AND S.XH=E.XH3. 检索马小红老师所授课程的学年，学期，课程号，上课时间。SELECT XQ,KH,SKSJFROM O,TWHERE T.XM=马小红 AND T.GH=O.GH4. 查询计算机学院男生总评成绩及格、教授开设的课程的课程号、课名、开课教师姓名，按开课教师升序，课程号降序排序。SELECT E.KH,C.KM,T.XMFROM E,C,TWHERE E.ZPCJ=60 AND E.XH IN(SELECT S.XH FROM S JOIND ON S.YXH=D.YXH WHERE D.MC=计算机学

11、院 ANDS.XB=男)INTERSECTSELECT E.KH,C.KM,T.XMFROM T,E,CWHERE T.XL=教授 AND T.GH=E.GH AND E.KH=C.KHORDER BY T.XM,E.KH DESC5. 检索学号比张颖同学大，年龄比张颖同学小的同学学号、姓名。SELECT B.XH,B.XMFROM S AS A,S AS BWHERE A.XM=张颖 AND B.XHA.XH AND B.CSRQA.CSRQ6. 检索同时选修了“”和“”的学生学号和姓名。SELECT DISTINCT S.XH,S.XMFROM E AS A ,E AS B,SWHERE

12、A.KH= AND B.KH= AND A.XH=B.XH AND A.XH=S.XH第3周（第四章：除法、聚合函数、分组、集合操作，外连接自学）一、实验课：1. 验证在1000万个以上记录时在索引和不索引时的查询时间区别。-1.验证在1000万个以上记录时在索引和不索引时的查询时间区别。declare i intset i=1while i = begininsert into A values(i)set i=i+1endSELECT SFROM AWHERE S=CREATE TABLE A(S INT)2. 查询每个学生选课情况（包括没有选修课程的学生）。SELECT S.XH,S.X

13、M,XQ,E.KH,PSCJ,KSCJ,ZPCJFROM S LEFT JOIN E ON E.XH=S.XHORDER BY S.XH3. 检索所有课程都选修的的学生的学号与姓名。SELECT XH,XMFROM SWHERE NOT EXISTS(SELECT *FROM CWHERE NOT EXISTS(SELECT *FROM EWHERE E.XH=S.XH AND C.KH=E.KH)4. 检索选修课程包含1106同学所学全部课程的学生学号和姓名。SELECT DISTINCT XH,XMFROM SWHERE NOT EXISTS(SELECT *FROM E AS E1WHE

14、RE E1.XH=1106 AND NOT EXISTS(SELECT *FROM E AS E2WHERE E2.XH=S.XH AND E1.KH=E2.KH)ORDER BY XH5. 查询每门课程中分数最高的学生学号和学生姓名。SELECT S.XM,S.XH,C.KM,A.ZPCJFROM S,C,E AS AWHERE S.XH=A.XH AND A.KH=C.KH AND A.ZPCJ=(SELECT MAX(ZPCJ) from E WHERE E.KH=A.KH )6. 查询年龄小于本学院平均年龄，所有课程总评成绩都高于所选课程平均总评成绩的学生学号、姓名和平均总评成绩，按年

15、龄排序。SELECT X.XH,X.XM,AVG(ZPCJ) AS 平均成绩,DateDiff(YYYY,CSRQ,2013-12-22) AS 年龄FROM S,E AS E3,(SELECT S1.XH,S1.XMFROM(SELECT XH,XM,YXH,DateDiff(YYYY,CSRQ,2013-12-22) AS 年龄FROM S) AS S1,(SELECT YXH,avg(DateDiff(YYYY,CSRQ,2013-12-22) as avg_age FROM SGROUP BY YXH) AS S2WHERE S1.YXH=S2.YXH AND S1.年龄=平均成绩 O

16、R E2.ZPCJ=NULL)AS YWHERE X.XH=Y.XH AND X.XH=E3.XH AND S.XH=X.XHGROUP BY X.XH,X.XM,DateDiff(YYYY,CSRQ,2013-12-22)ORDER BY 年龄第4周（第四章：数据更新、视图、嵌入式SQL部分自学）一、实验课：1. 建立计算机学院总评不及格成绩学生的视图，包括学生学号、姓名、性别、手机、所选课程和成绩。CREATE VIEW AAS SELECT S.XH,XM,XB,SJHM,KH,PSCJ,KSCJ,ZPCJFROM S,E,DWHERE D.MC=计算机学院 AND E.ZPCJALL(SELECT DateDiff(YYYY,CSRQ,2013-12-22) AS 年龄 FROM SWHERE XB=女)4. 在E表中修改课程的平时成绩，若成绩小于等于75分时提高5%，若成绩大于75分时提高4%。-先都提高4