1、上海大学数据库实验报告大学数据库实验报告第一组第1（第四章：SQL体系结构、组成、建库建表，索引自学）一、实验课： 1.建立school数据库2.在school下建立如下数据库表，根据表中数据选取合适的数据类型及宽度，设置各表的主键及表间外键联系：注意：字段名是对应汉字字段名的汉语拼音第一个字母组合而成学生表S：学号，性别，出生日期，籍贯，手机，院系号；表1：Sxhxmxbcsrqjgsjhmyxh1101明男1993-03-06021102晓明男1992-12-08011103颖女1993-01-05011104晶晶女1994-11-06011105成刚男1991-06-07011106二丽
2、女1993-05-04011107晓峰男1992-08-1601院系表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课程
3、表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-420
4、12-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秋季083050
5、040101nullnullnull11032012-2013秋季08305001010356565611032012-2013冬季08305002010275757511032012-2013冬季08305003010284848411032013-2014秋季083050010102nullnullnull11032013-2014秋季083050040101nullnullnull11042012-2013秋季08305001010374747411042013-2014冬季083020010201nullnullnull11062012-2013秋季083050010103858585
6、11062012-2013冬季08305002010366666611072012-2013秋季08305001010390909011072012-2013冬季08305003010279797911072013-2014秋季083050040101nullnullnull3.在学生表中建立索引idx1：院系号升序，降序在课程表中建立索引idx2：课名create database schoolgouse schoolcreate table S(xh int,xm char(10),xb char(2),csrq date,jg char(20),sjhm bigint,yxh char(
7、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),primary key (gh),foreign key (yxh) references D(yxh)create table C(
8、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) references T(gh)create table E(xh int,xq char(20),kh char(8),g
9、h 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) references S(xh)create unique index idx1 on S(yxh asc,xm desc)
10、;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 OEXCEPTSELECT KH FROM S,EWHERE XM=晓明 AND S.XH=E.XH3.检索马小红老师所授课程的学年，学期，课程号，上课时间。SELECT XQ,KH,
11、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=计算机学院 ANDS.XB=男)INTERSECTSELECT E.KH,C.KM,T.XMFROM T,E,CWHERE T.XL=教授 AND T.GH=E.GH AND E.KH=C.K
12、HORDER 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”的学生学号和。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（第四章：除法、聚合函数、分组、集合操作，外连接
13、自学）一、实验课：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.XH,S.XM,XQ,E.KH,PSCJ,KSCJ,ZPCJFROM S LEFT JOIN E ON E.XH=S.XHORD
14、ER 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 E1WHERE E1.XH=1106 AND NOT EXISTS(SELECT *FROM E AS E2WHERE E2.XH=S.X
15、H 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.查询年龄小于本学院平均年龄，所有课程总评成绩都高于所选课程平均总评成绩的学生学号、和平均总评成绩，按年龄排序。SELECT X.XH,X.XM,AVG(ZPCJ) AS 平均成绩,DateDiff(YYYY,CSRQ,2013-12-22)
16、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.年龄=平均成绩 OR E2.ZPCJ=NULL)AS YWHERE X.XH=Y.XH AND X.XH=E3.XH AND S.XH=X.XHGROUP B
17、Y 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表中修改083050
18、01课程的平时成绩，若成绩小于等于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) 不及格
