上海大学数据库实验报告.doc
-大学数据库实验报告第一组第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-05-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：课号，课名，学分，学时，院系号；（默认学分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-42012-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秋季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. 在学生表中建立索引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(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(kh 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 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);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,TWHERET.*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. 检索*比颖同学大，年龄比颖同学小的同学*、。SELECTB.*H,B.*MFROMSASA,SASBWHEREA.*M='颖'ANDB.*H>A.*HANDB.CSRQ>A.CSRQ6. 检索同时选修了"08305001”和"08305002”的学生*和。SELECTDISTINCTS.*H,S.*MFROMEASA,EASB,SWHEREA.KH='08305001'ANDB.KH='08305002'ANDA.*H=B.*HANDA.*H=S.*H第3（第四章：除法、聚合函数、分组、集合操作，外连接自学）一、实验课：1. 验证在1000万个以上记录时在索引和不索引时的查询时间区别。-1.验证在1000万个以上记录时在索引和不索引时的查询时间区别。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(SELECT *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,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,DateDiff(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.年龄<=S2.avg_age)AS *, (SELECT S3.*H,*M,E2.KH FROM(SELECT KH,AVG(ZPCJ) AS 平均成绩FROM EGROUP BY KH) AS E1,E AS E2,S AS S3WHERE E1.KH=E2.KH AND E2.*H=S3.*H AND E2.ZPCJ>=平均成绩 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（第四章：数据更新、视图、嵌入式SQL部分自学）一、实验课：1. 建立计算机学院总评不及格成绩学生的视图，包括学生*、性别、手机、所选课程和成绩。CREATE VIEW AAS SELECT S.*H,*M,*B,SJHM,KH,PSCJ,KSCJ,ZPCJFROM S,E,DWHERE D.MC='计算机学院' AND E.ZPCJ<60 AND E.*H=S.*H AND S.Y*H=D.Y*HSELECT *FROM A2. 在E表中插入记录，把每个学生没学过的课程都插入到E表中，使得每个学生都选修每门课。-插之前先把工号也设为主键。INSERT INTO E(*H,*Q,KH,GH)SELECT E1.*H,O.*Q,O.KH,O.GHFROM O,E AS E1 E*CEPT(SELECT E2.*H,E2.*Q,E2.KH,E2.GHFROM E AS E2)SELECT *FROM E3. 求年龄大于所有女同学年龄的男学生和年龄。SELECT *M,DateDiff(YYYY,CSRQ,'2013-12-22') AS 年龄FROM SWHERE *B='男'GROUP BY *M,DateDiff(YYYY,CSRQ,'2013-12-22')HAVING DateDiff(YYYY,CSRQ,'2013-12-22')>ALL(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='08305001'UPDATE ESET ZPCJ=ZPCJ/(1+0.04)*(1+0.05)WHERE KH='08305001' AND ZPCJ<=75*(1+0.04)SELECT *FROM E5. 删除没有开课的学院。-先设置主键Y*H可以级联删除DELETE FROM DWHERE D.Y*H NOT IN(SELECT DISTINCT Y*HFROM C,OWHERE C.KH=O.KH)SELECT *FROM D6. 查询优、良、中、及格、不及格学生人数SELECT(SELECT COUNT(*) FROM E WHERE ZPCJ>=90) 优,(SELECT COUNT(*) FROM E WHERE ZPCJ>=80 AND ZPCJ<90) 良,(SELECT COUNT(*) FROM E WHERE ZPCJ>=70 AND ZPCJ<80) 中,(SELECT COUNT(*) FROM E WHERE ZPCJ>=60 AND ZPCJ<70) 及格,(SELECT COUNT(*) FROM E WHERE ZPCJ<60) 不及格. z.
