《上海大学数据库实验报告1.doc》由会员分享，可在线阅读，更多相关《上海大学数据库实验报告1.doc（18页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、上海大学数据库实验报告第一组第1周（第四章：SQL体系结构、组成、建库建表，索引自学）一、实验课： 1. 建立school数据库2. 在school下建立如下数据库表，根据表中数据选取合适的数据类型及宽度，设置各表的主键及表间外键联系：注意：字段名是对应汉字字段名的汉语拼音第一个字母组合而成l 学生表S：学号，姓名，性别，出生日期，籍贯，手机号码，院系号；表1：Sxhxmxbcsrqjgsjhmyxh1101李明男1993-03-06上海13613005486021102刘晓明男1992-12-08安徽18913457890011103张颖女1993-01-05江苏18826490423011
2、104刘晶晶女1994-11-06上海13331934111011105刘成刚男1991-06-07上海18015872567011106李二丽女1993-05-04江苏18107620945011107张晓峰男1992-08-16浙江1391234107801l 院系表D：院系号，名称，地址，联系电话；表2：Dyxhmc地址lxdh01计算机学院上大东校区三号楼6534756702通讯学院上大东校区二号楼6534123403材料学院上大东校区四号楼65347890l 教师表T：工号，姓名，性别，出生日期，学历，基本工资，院系编号；表3：Tghxmxbcsrqxljbgzyxh0101陈迪茂男
3、1973-03-06副教授3567.00010102马小红女1972-12-08讲师2845.00010201张心颖女1960-01-05教授4200.00020103吴宝钢男1980-11-06讲师2554.0001l 课程表C：课号，课名，学分，学时，院系号；（默认学分4，学时40）表4：Ckhkmxfxsyxh08305001离散数学4400108305002数据库原理4500108305003数据结构4500108305004系统结构6600108301001分子物理学4400308302001通信学33002l 开课表O：学期，课号，工号，上课时间；表5：Oxqkhghsksj201
4、2-2013秋季083050010103星期三5-82012-2013冬季083050020101星期三1-42012-2013冬季083050020102星期三1-42012-2013冬季083050020103星期三1-42012-2013冬季083050030102星期五5-82013-2014秋季083050040101星期二1-42013-2014秋季083050010102星期一5-82013-2014冬季083020010201星期一5-8l 选课表E：学号，学期，课号，工号，平时成绩，考试成绩，总评成绩；（成绩范围1-100）表6：Exhxqkhghpscjkscjzpcj11
5、012012-2013秋季08305001010360606011022012-2013秋季08305001010387878711022012-2013冬季08305002010182828211022013-2014秋季083050040101nullnullnull11032012-2013秋季08305001010356565611032012-2013冬季08305002010275757511032012-2013冬季08305003010284848411032013-2014秋季083050010102nullnullnull11032013-2014秋季08305004010
6、1nullnullnull11042012-2013秋季08305001010374747411042013-2014冬季083020010201nullnullnull11062012-2013秋季08305001010385858511062012-2013冬季08305002010366666611072012-2013秋季08305001010390909011072012-2013冬季08305003010279797911072013-2014秋季083050040101nullnullnull3. 在学生表中建立索引idx1：院系号升序，姓名降序在课程表中建立索引idx2：课名c
7、reate database schoolgouse schoolcreate table 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
8、),csrq date,xl char(10),jbgz numeric(6,2),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
9、,kh,gh),foreign key (kh) references C(kh),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) reference
10、s T(gh),foreign key (kh) references C(kh),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
11、XB=男2. 检索刘晓明不学的课程的课程号。SELECT KHFROM OEXCEPTSELECT 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 FR
12、OM S JOIND ON S.YXH=D.YXH WHERE D.MC=计算机学院 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. 检索同时选修了“08305001”和“08305002”的学生学
13、号和姓名。SELECT DISTINCT S.XH,S.XMFROM E AS A ,E AS B,SWHERE A.KH=08305001 AND B.KH=08305002 AND A.XH=B.XH AND A.XH=S.XH第3周（第四章：除法、聚合函数、分组、集合操作，外连接自学）一、实验课：1. 验证在1000万个以上记录时在索引和不索引时的查询时间区别。-1.验证在1000万个以上记录时在索引和不索引时的查询时间区别。declare i intset i=1while i = 1000000begininsert into A values(i)set i=i+1endSELEC
14、T SFROM AWHERE S=1000000CREATE TABLE A(S INT)2. 查询每个学生选课情况（包括没有选修课程的学生）。SELECT S.XH,S.XM,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. 检索选修课程包含1
15、106同学所学全部课程的学生学号和姓名。SELECT DISTINCT XH,XMFROM SWHERE NOT EXISTS(SELECT *FROM E AS E1WHERE 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
16、(ZPCJ) from E WHERE E.KH=A.KH )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.XMFROM(SELECT XH,XM,YXH,DateDiff(YYYY,CSRQ,2013-12-22) AS 年龄FROM S) AS S1,(SELECT YXH,avg(DateDiff(YYYY,CS
17、RQ,2013-12-22) as avg_age FROM SGROUP BY YXH) AS S2WHERE S1.YXH=S2.YXH AND S1.年龄=平均成绩 OR 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 VIE
18、W 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表中修改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) 不及格