上海大学数据库实验报告1

上海大学数据库实验报告第一组\第1周（第四章：SQL体系结构、组成、建库建表，索引自学）一、实验课：1.建立school数据库2.在school下建立如下数据库表，根据表中数据选取合适的数据类型及宽度，设置各表的主键及表间外键联系：注意：字段名是对应汉字字段名的汉语拼音第一个字母组合而成学生表S：学号，姓名，性别，出生日期，籍贯，手机号码，院系号；表1：Sxhxmxbcsrqjgsjhmyxh1101李明男1993-03-06上海13613005486021102刘晓明男1992-12-08安徽18913457890011103张颖女1993-01-05江苏18826490423011104刘晶晶女1994-11-06上海13331934111011105刘成刚男1991-06-07上海18015872567011106李二丽女1993-05-04江苏18107620945011107张晓峰男1992-08-16浙江1391234107801院系表D：院系号，名称，地址，联系电话；表2：Dyxhmc地址lxdh01计算机学院上大东校区三号楼6534756702通讯学院上大东校区二号楼6534123403材料学院上大东校区四号楼65347890教师表T：工号，姓名，性别，出生日期，学历，基本工资，院系编号；表3：Tghxmxbcsrqxljbgzyxh0101陈迪茂男1973-03-06副教授3567.00010102马小红女1972-12-08讲师2845.00010201张心颖女1960-01-05教授4200.00020103吴宝钢男1980-11-06讲师2554.0001课程表C：课号，课名，学分，学时，院系号；（默认学分4，学时40）表4：Ckhkmxfxsyxh08305001离散数学4400108305002数据库原理4500108305003数据结构4500108305004系统结构6600108301001分子物理学4400308302001通信学33002开课表O：学期，课号，工号，上课时间；表5：Oxqkhghsksj2012-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-8选课表E：学号，学期，课号，工号，平时成绩，考试成绩，总评成绩；（成绩范围1-100）表6：Exhxqkhghpscjkscjzpcj11012012-2013秋季08305001010360606011022012-2013秋季08305001010387878711022012-2013冬季08305002010182828211022013-2014秋季083050040101nullnullnull11032012-2013秋季08305001010356565611032012-2013冬季08305002010275757511032012-2013冬季08305003010284848411032013-2014秋季083050010102nullnullnull11032013-2014秋季083050040101nullnullnull11042012-2013秋季08305001010374747411042013-2014冬季083020010201nullnullnull11062012-2013秋季08305001010385858511062012-2013冬季08305002010366666611072012-2013秋季08305001010390909011072012-2013冬季08305003010279797911072013-2014秋季083050040101nullnullnull3.在学生表中建立索引idx1：院系号升序，姓名降序在课程表中建立索引idx2：课名createdatabaseschoolgouseschoolcreatetableS(xhint,xmchar(10),xbchar(2),csrqdate,jgchar(20),sjhmbigint,yxhchar(2),primarykey(xh),foreignkey(yxh)referencesD(yxh))createtableD(yxhchar(2),mcchar(20),地址char(50),lxdhint,primarykey(yxh))createtableT(ghchar(4),xmchar(10),xbchar(2),csrqdate,xlchar(10),jbgznumeric(6,2),yxhchar(2),primarykey(gh),foreignkey(yxh)referencesD(yxh))createtableC(khchar(8),kmchar(20),xfint,xsint,yxhchar(2),primarykey(kh),foreignkey(yxh)referencesD(yxh))createtableO(xqchar(20),khchar(8),ghchar(4),sksjchar(20),primarykey(xq,kh,gh),foreignkey(kh)referencesC(kh),foreignkey(gh)referencesT(gh))createtableE(xhint,xqchar(20),khchar(8),ghchar(4),pscjintCHECK(pscjBETWEEN1AND100),kscjintCHECK(kscjBETWEEN1AND100),zpcjintCHECK(zpcjBETWEEN1AND100),primarykey(xh,xq,kh,gh),foreignkey(gh)referencesT(gh),foreignkey(kh)referencesC(kh),foreignkey(xh)referencesS(xh))createuniqueindexidx1onS(yxhasc,xmdesc);createuniqueindexidx2onC(km);第2周（第四章：投影、选择、多表连接和嵌套，排序自学）一、实验课：1.查询2011年进校年龄大于20岁的男学生的学号与姓名。--1.查询2011年进校年龄大于20岁的男学生的学号与姓名。SELECTXH,XMFROMSWHEREYEAR(2011-YEAR(CSRQ))20ANDXB='男'2.检索刘晓明不学的课程的课程号。SELECTKHFROMOEXCEPTSELECTKHFROMS,EWHEREXM='刘晓明'ANDS.XH=E.XH3.检索马小红老师所授课程的学年，学期，课程号，上课时间。SELECTXQ,KH,SKSJFROMO,TWHERET.XM='马小红'ANDT.GH=O.GH4.查询计算机学院男生总评成绩及格、教授开设的课程的课程号、课名、开课教师姓名，按开课教师升序，课程号降序排序。SELECTE.KH,C.KM,T.XMFROME,C,TWHEREE.ZPCJ=60ANDE.XHIN(SELECTS.XHFROMSJOINDONS.YXH=D.YXHWHERED.MC='计算机学院'ANDS.XB='男')INTERSECTSELECTE.KH,C.KM,T.XMFROMT,E,CWHERET.XL='教授'ANDT.GH=E.GHANDE.KH=C.KHORDERBYT.XM,E.KHDESC5.检索学号比张颖同学大，年龄比张颖同学小的同学学号、姓名。SELECTB.XH,B.XMFROMSASA,SASBWHEREA.XM='张颖'ANDB.XHA.XHANDB.CSRQA.CSRQ6.检索同时选修了“08305001”和“08305002”的学生学号和姓名。SELECTDISTINCTS.XH,S.XMFROMEASA,EASB,SWHEREA.KH='08305001'ANDB.KH='08305002'ANDA.XH=B.XHANDA.XH=S.XH第3周（第四章：除法、聚合函数、分组、集合操作，外连接自学）一、实验课：1.验证在1000万个以上记录时在索引和不索引时的查询时间区别。--1.验证在1000万个以上记录时在索引和不索引时的查询时间区别。declare@iintset@i=1while@i=1000000begininsertintoAvalues(@i)set@i=@i+1endSELECTSFROMAWHERES=1000000CREATETABLEA(SINT)2.查询每个学生选课情况（包括没有选修课程的学生）。SELECTS.XH,S.XM,XQ,E.KH,PSCJ,KSCJ,ZPCJFROMSLEFTJOINEONE.XH=S.XHORDERBYS.XH3.检索所有课程都选修的的学生的学号与姓名。SELECTXH,XMFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS(SELECT*FROMEWHEREE.XH=S.XHANDC.KH=E.KH))4.检索选修课程包含1106同学所学全部课程的学生学号和姓名。SELECTDISTINCTXH,XMFROMSWHERENOTEXISTS(SELECT*FROMEASE1WHEREE1.XH=1106ANDNOTEXISTS(SELECT*FROMEASE2WHEREE2.XH=S.XHANDE1.KH=E2.KH))ORDERBYXH5.查询每门课程中分数最高的学生学号和学生姓名。SELECTS.XM,S.XH,C.KM,A.ZPCJFROMS,C,EASAWHERES.XH=A.XHANDA.KH=C.KHANDA.ZPCJ=(SELECTMAX(ZPCJ)fromEWHEREE.KH=A.KH)6.查询年龄小于本学院平均年龄，所有课程总评成绩都高于所选课程平均总评成绩的学生学号、姓名和平均总评成绩，按年龄排序。SELECTX.XH,X.XM,AVG(ZPCJ)AS平均成绩,DateDiff(YYYY,CSRQ,'2013-12-22')AS年龄FROMS,EASE3,(SELECTS1.XH,S1.XMFROM(SELECTXH,XM,YXH,DateDiff(YYYY,CSRQ,'2013-12-22')AS年龄FROMS)ASS1,(SELECTYXH,avg(DateDiff(YYYY,CSRQ,'2013-12-22'))asavg_ageFROMSGROUPBYYXH)ASS2WHERES1.YXH=S2.YXHANDS1.年龄=S2.avg_age)ASX,(SELECTS3.XH,XM,E2.KHFROM(SELECTKH,AVG(ZPCJ)AS平均成绩FROMEGROUPBYKH)ASE1,EASE2,SASS3WHEREE1.KH=E2.KHANDE2.XH=S3.XHANDE2.ZPCJ=平均成绩ORE2.ZPCJ=NULL)ASYWHEREX.XH=Y.XHANDX.XH=E3.XHANDS.XH=X.XHGROUPBYX.XH,X.XM,DateDiff(YYYY,CSRQ,'2013-12-22')ORDERBY年龄第4周（第四章：数据更新、视图、嵌入式SQL部分自学）一、实验课：1.建立计算机学院总评不及格成绩学生的视图，包括学生学号、姓名、性别、手机、所选课程和成绩。CREATEVIEWAASSELECTS.XH,XM,XB,SJHM,KH,PSCJ,KSCJ