数据库原理实验报告1~.doc

评分：_ SHANGHAI UNIVERSITY课程论文COURSE PAPER数据库原理实验报告(14)学 院 计算机学院 专 业 计算机工程与技术学 号 学生姓名 课 程 数据库原理 打印日期 第1周（第四章：SQL体系结构、组成、建库建表，索引自学）一、实验课： 1. 建立school数据库2. 在school下建立如下数据库表，根据表中数据选取合适的数据类型及宽度，设置各表的主键及表间外键联系：注意：字段名是对应汉字字段名的汉语拼音第一个字母组合而成l 学生表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浙江1391234107801l 院系表D：院系号，名称，地址，联系电话；表2：Dyxhmcdzlxdh01计算机学院上大东校区三号楼6534756702通讯学院上大东校区二号楼6534123403材料学院上大东校区四号楼65347890l 教师表T：工号，姓名，性别，出生日期，学历，基本工资，院系编号；表3：Tghxmxbcsrqxljbgzyxh0101陈迪茂男1973-03-06副教授356700010102马小红女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：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-8l 选课表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：课名实验代码：create database schoolgouse schoolcreate table s( xh char(4) not null primary key, xm char(20) not null, xb char(2), csrq char(10), jg char(10), sjhm char(11), yxh char(2) insert into s values(1101,李明,男,1993-03-06,上海,13613005486,02)insert into s values(1102,刘晓明,男,1992-12-08,安徽,18913457890,01)insert into s values(1103,张颖,女,1993-01-05,江苏,18826490423,01)insert into s values(1104,刘晶晶,女,1994-11-06,上海,13331934111,01)insert into s values(1105,刘成刚,男,1991-06-07,上海,18015872567,01)insert into s values(1106,李二丽,女,1993-05-04,江苏,18107620945,01)insert into s values(1107,张晓峰,男,1992-08-16,浙江,13912341078,01)create table d( yxh char(2) not null primary key, mc char(10), dz char(20), lxdh char(10)insert into d values(01,计算机学院,上大东校区三号楼,65347567)insert into d values(02,通讯学院,上大东校区二号楼,65341234)insert into d values(03,材料学院,上大东校区四号楼,65347890)create table t( gh char(4) not null primary key, xm char(10), xb char(2), csrq char(10), xl char(10), jbgz char(10), yxh char(2)insert into t values(0101,陈迪茂,男,1973-03-06,副教授,356700,01)insert into t values(0102,马小红,女,1972-12-08,讲师,2845.00,01)insert into t values(0201,张心颖,女,1960-01-05,教授,4200.00,02)insert into t values(0103,吴宝钢,男,1980-11-06,讲师,2554.00,01) create table c( kh char(8) not null primary key, km char(10), xf char(1)default 4, xs char(2)default 40, yxh char(2)insert into c values(08305001,离散数学,4,40,01)insert into c values(08305002,数据库原理,4,50,01)insert into c values(08305003,数据结构,4,50,01)insert into c values(08305004,系统结构,6,60,01) insert into c values(08301001,分子物理学,4,40,03)insert into c values(08302001,通信学,3,30,02) create table o( xq char(15) not null, kh char(10), gh char(4), sksj char(10),primary key(xq,kh,gh)insert into o values(2012-2013秋季,08305001,0103,星期三5-8)insert into o values(2012-2013冬季,08305002,0101,星期三1-4)insert into o values(2012-2013冬季,08305002,0102,星期三1-4)insert into o values(2012-2013冬季,08305002,0103,星期三1-4) insert into o values(2012-2013冬季,08305003,0102,星期五5-8)insert into o values(2013-2014秋季,08305004,0101,星期二1-4) insert into o values(2013-2014秋季,08305001,0102,星期一5-8)insert into o values(2013-2014冬季,08302001,0201,星期一5-8) create table e( xh char(4) not null , xq char(20) not null, kh char(8)not null, gh char(4)not null, pscj int check(pscj between 0 and 100), kscj int check(kscj between 0 and 100), zpcj int check(zpcj between 0 and 100), primary key(xh,xq,kh,gh) insert into e values(1101,2012-2013秋季,08305001,0103,60,60,60)insert into e values(1102,2012-2013秋季,08305001,0103,87,87,87)insert into e values(1102,2012-2013冬季,08305002,0101,82,82,82)insert into e values(1102,2013-2014秋季,08305004,0101,null,null,null)insert into e values(1103,2012-2013秋季,08305001,0103,56,56,56)insert into e values(1103,2012-2013冬季,08305002,0102,75,75,75)insert into e values(1103,2012-2013冬季,08305003,0102,84,84,84)insert into e values(1103,2013-2014秋季,08305001,0102,null,null,null)insert into e values(1103,2013-2014秋季,08305004,0101,null,null,null)insert into e values(1104,2012-2013秋季,08305001,0103,74,74,74)insert into e values(1104,2013-2014冬季,08302001,0201,null,null,null)insert into e values(1106,2012-2013秋季,08305001,0103,85,85,85)insert into e values(1106,2012-2013冬季,08305002,0103,66,66,66)insert into e values(1107,2012-2013秋季,08305001,0103,90,90,90)insert into e values(1107,2012-2013冬季,08305003,0102,79,79,79)insert into e values(1107,2013-2014秋季,08305004,0101,null,null,null)create index idx1 on s(yxh asc,xm desc);create index idx2 on c(kh);实验结果截图：心得体会：本次实验是第一次学习数据库后的第一个实验，这学期的学习使用的是SQL server ,实验代码也给了模板，完成比较轻松。第2周（第四章：投影、选择、多表连接和嵌套，排序自学）一、实验课：1. 查询2011年进校年龄大于20岁的男学生的学号与姓名。 select xh,xmfrom swhere xb=男and csrq= 60 and t.xl=教授 and c.kh=e.kh and e.kh=o.kh and s.xb=男 and t.gh=o.gh and d.mc=计算机学院 and s.yxh=d.yxh and e.xh=s.xhorder by t.xm asc ,c.kh desc 5. 检索学号比张颖同学大，年龄比张颖同学小的同学学号、姓名。 select xh,xmfrom swhere xh (select xh from s where xm=张颖) and csrq (select csrq from s where xm=张颖)6. 检索同时选修了“08305001”和“08305002”的学生学号和姓名。select distinct s.xh ,xmfrom s,e as a,e as bwhere s.xh=a.xh and a.xh=b.xh and a.kh=08305001 and b.kh=08305002心得体会：这次老师上课的内容是关于select用法的初步学习。第3周（第四章：除法、聚合函数、分组、集合操作，外连接自学）一、实验课：1. 验证在1000万个以上记录时在索引和不索引时的查询时间区别。2. 查询每个学生选课情况（包括没有选修课程的学生）。3. 检索所有课程都选修的的学生的学号与姓名。4. 检索选修课程包含1106同学所学全部课程的学生学号和姓名。5. 查询每门课程中分数最高的学生学号和学生姓名。6. 查询年龄小于本学院平均年龄，所有课程总评成绩都高于所选课程平均总评成绩的学生学号、姓名和平均总评成绩，按年龄排序。实验代码及结果：1.declarei intset i=1while i300000begin insert into Table_1values(i)set i=i+1end未建立索引前set statistics time onselect idfrom Table_1where id=100000set statistics time off建立索引后set statistics time onselect idfrom Table_1where id=100000set statistics time off2.select s.xh,e.xq,e.kh,e.ghfrom sfull outer join(select *from e )eON s.xh=e.xh 3.select xh,xmfrom s xwhere not exists(select *from cwhere not exists(select *from ewhere xh=x.xh and kh=c.kh)4.select xh,xmfrom swhere not exists(select*from e e1where xh=1106 and not exists(select *from e e2where s.xh=e2.xh and kh=e1.kh)5.select e.kh,s.xh,s.xm,e.zpcjfrom s,ewhere s.xh=e.xh and e.zpcj IN (select max(zpcj)from egroup by kh)6.select s.xh学号，s.xm姓名，e.zpcj总评成绩from S,E,(select kh,avg(zpcj)pjfrom E group by kh)tempwhere s.xh=e.xh and temp.kh=e.khand year(getdate()year(s.csrq)temp.pjorder by year(getdate()-year(s,csrq)select s.xh,s.xm,e.zpcjfrom S s,E,e(select kh,avg(zpcj)pj from E group by kh)awhere s.xh=e.xh and a.kh=e.khand year(getdate()-year(s.csrq)a.pjorder by year(getdate()-year(s.csrq)心得体会：第六题不会做第4周（第四章：数据更新、视图、嵌入式SQL部分自学）一、实验课：1. 建立计算机学院总评不及格成绩学生的视图，包括学生学号、姓名、性别、手机、所选课程和成绩。2. 在E表中插入记录，把每个学生没学过的课程都插入到E表中，使得每个学生都选修每门课。3. 求年龄大于所有女同学年龄的男学生姓名和年龄。4. 在E表中修改08305001课程的平时成绩，若成绩小于等于75分时提高5%，若成绩大于75分时提高4%。5. 删除没有开课的学院。6. 查询优、良、中、及格、不及格学生人数实验代码及结果：1.create view zpcj_bjg(sno,sname,sex,phone,course,grade)as (select s.xh, s.xm, s.xb, s.sjhm, e.kh, e.zpcjfrom s ,e where s.xh=e.xh and e.zpcj(select avg(2014-year(csrq) as nj from s s1 where s1.xb=女)4.update eset zpcj=zpcj*(1+0.04)where zpcj75update eset zpcj=zpcj*(1+0.05)where zpcj=90 and e2.kh=e1.kh)as 优,(select count(e3.zpcj) from e e3 where e3.zpcj=80 and e3.zpcj=70 and e4.zpcj=60 and e5.zpcj70 and e5.kh=e1.kh)as 及格,(select count(e6.zpcj) from e e6 where e6.zpcj60 and e6.kh=e1.kh)as 不及格from e e1group by e1.kh心得体会：这次的实验主要是学习了如何建立视图，从而更有效的找到自己想要寻找的数据，然后就是学习了如何更新和删除数据表中的内容，总的来说比上次简单。