数据库实验8报告

1、数据库原理及应用实验报告实验名称： 数据检索（2 -多表查询学院：计算机学院班级：计算机应用技术大专班学号：201113131121姓名：黄锡杏实验日期： 2013年4月19日目录一、 实验准备21、设备22、知识点2二、实验目的3三、实验内容3（1） .查询所有班级的期末成绩平均分，并按照平均分降序排序3（2）.查询教师基本信息和教授课程信息，其中包括未分配课程的教师信息 5（3）.查询090501 '班级中选修了韩晋升老师讲授的课程的学生的学号、姓名、课程名和期末成绩6（4）.查询每门课程的课程号、课程名和选修该课程的学生人数，并按所选人数升序排序7（5） .查询两门及以上课程的期

2、末成绩超过80分的学生的姓名及其平均成绩 8（6） .查询入学考试成绩最高的学生的学号、姓名和入学成绩 9（7）.查询所有教授C05127'号课程的教师信息 9（8） .查询同时教授C05127'号和'C05109'号课程的教师信息 10（9） .查询至少选修了姓名为韩吟秋的学生所选课程中一门课的学生的学号和姓名.11（10）.查询没有被任何学生选修的课程编号、课程名和学分 11（11）.查询“ C语言”课程期末成绩比“电子技术”课程期末成绩高的所有学生的学号和姓名12（12）.查询所有班级期末平均成绩的最高分，并将其赋值给变量，通过PRINT语句输出./彳（1

3、3 ）实用游标输出学生姓名、选修课程名称和期末考试成绩 14四、总结15一、实验准备1、设备PC 机一台、SQL Server2005（企业版）2、知识点回顾教材主要知识点，主要在教材的第六章（1）、利用多表连接方式查询数据（JOIN函数）(2) 、利用子查询方式查询数据(嵌套查询)(3) 、利用游标处理结果集的基本过程(4) 、上网搜索相关的只是来补充课本的缺陷 (如JOIN函数的不 同用法)二、实验目的1、重点掌握JOINON语句的关系及应用，并懂得与子句查询的转换;2、掌握连接查询、子查询、游标、大对象类型数据的基本概念；3、掌握多表连接的各种方法，包括内连接、外连接、交叉连接等；4、掌

4、握子查询的方法，包括相关子查询和不相关子查询5、掌握 游标处理结果集的基本过程三、实验内容要根据提供的实验内容资料，如实填写实验的操作步骤步骤、 操作语句、临时或最终结果等，主要内容见下；(1) .查询所有班级的期末成绩平均分，并按照平均分降序排序代码：use teachi nggoselect class no , avg (final )from score inner joinstude nton ( score . studentno=student. studentno)where final is not n ull group by class noorder by avg (f

5、inal ) desc结果：CXH-YELLOW.teaching - SQLQueryl.sqh 摘要 实验S-3aS 期末平均分，一jbs 匸eachinggoselect, classno f avg final :from score 二亡二 noir, studentn score.5tudentno=student.studentno:where final is naz -11group by classnoorder by avg finali desc二拮果 J消息cl assn o氏列名1j090801 94SS3333209080294 0000003090502S3 0

6、00000408060131 333333509050178 000000608050177500000©萱询已成功执行。对比下面VCXH-YELLOW.teaching 亠 SQLQueryLsql* 授要实验A3 use teachinggoselect classno avg i final as 貝冃末平均分 frcir. score-二二二 studenton score studentno-student ” studentnowhere final i? not nullgroup by classnoorder by avg final desc申査洵已成功机行*1一

7、曲结果_J消息dassriQ期末平均分1j 0M301 M33333S2030302M 0000003090502B3 0000004080601ai .833333509050173.0DWW608050177 500000(2) .查询教师基本信息和教授课程信息，其中包括未分配课程的教师信息代码：use teachi nggoselect teacher .*, course no , class nofrom teacher left join teach_classon (teacher . teacher no=teach_class . teacher no)结果：-2gftele

8、 c E. c亡凸i_LT£<En口cl a 斗“fx*dta 匚亡.口n芒e Le£t J Qi.m t e-ach._=clas son > tt*c.Fie,!£ . te-acctac_el*i reacheEFio一inJ«* la姑吉ISR=XWprtrf1105001V*汨计 muiw*uDErl 阳D&05Q12lQ5OQfH升lirffXW!计 MtflWHEoa5127D935O2Ji1O5CO3S'lftw网S31#齐：计 MtnwtcK127De&5Q141O5O11讨»机诅计计 Mi

9、nw*c<S13S59050251054317itw计 wmwtc i36137030501£1O6C11AR遢悅皿1"DSOfiCI710WIJ3匪国远齊工誉mtwttlttTP*0WQ1直轻:栉厘WIFTFS仪叫MUkL$10S017mtffWIFTIS(maoig研惘已IS功趴冇 »CX3t%ELLLltf CEl. D B.TNJ CXH-lfELLOA VAdna ill Etr !«- (S3)*L« »cIi.lti< 00. OO. 00 B 行(3) .查询090501 '班级中选修了韩晋升老师讲

10、授的课程的学生的学号、姓名、课程名和期末成绩代码：use teachi nggoselectstudent. studentno, sname , cname , finalfrom scorejoinstudenton ( score . studentno=student. studentno )join course on (score . course no =course . course no )join teach_class on ( score . course no =teach_class . course no join teacher on (teach_class

11、. teacher no =teacher . teacher no where tn ame ='韩晋升' and stude nt . class no ='090501'结果：-3libs te actilTiggoselect- Budenno> snarr.e . cname f finalfrom sGQie 2 _7： student: on score .3uudenmo： student, studentnoj oi course on (score - ocurseno :oouxBe couxseno二 u二n teach class

12、 cn score . couizseno=teacti_class . couirseno - 二-. teacher on t:each_class . r皀岂uh牡Hno=匸亡uti亡上.匚亡&uh亡工no where 匸nam昔：'韩背升' ad 3tuden,t»cLas5no =090&01 * i m目结果血消息Studentno snam-ecnamefinal1 '09222lSiOT''j 许海冰匚语盲91002 "0922221324 f可影匚语言77.00憐少 杳御口 FJGrhHif乎rifw

13、-¥ft t nw g n rtmi(4) .查询每门课程的课程号、课程名和选修该课程的学生人数，并按所选人数升序排序代码：use teachi nggoselect course . course no , cn ame , cou nt (*)from score joinstudenton (score . studentno=student. studentno )join course on (score . course no =course . course no)group by course . course no, cn ameorder by count (*)

14、结果：use teachingselect course covzsenQ unaKje, unr二t，f rair. score ： z r. 3工口dent cn i care . studentno=student studentno:二二n ccurse on score . cour 3erLD=cauise . coursenagroup by course.courseno5 cnaneorder by caant *)nr口结果J MBooursenocname妩列名)1c061271机械设计12cOBTOfi经济学23C08123金融学24dKlUB机械制圏25CQ3171会

15、计软件36cQ51O3电子技术G7cO5109C语言7(5) .查询两门及以上课程的期末成绩超过80分的学生的姓名及其平均成绩代码:use teachi ngselectstude nt.studentno,sname , avg (finalfromstude ntinnerjoinscoreon ( student . studentno=score . studentno)wherefinalis notnulland final>=80groupby student. studentno,snamehav ingcou nt(*)>= 2结果：go)as avgerageu

16、se teachingselect sncdenr suud&nrno anarte. avc final a.s avgerage from student 二二二e._ J cin scoreon student.studentnc score,studentno:where final i3 - ： rll -rnd final? SOgroup ty student.3匸口且亡ntno.勻隗冠m亡having cour:t ( T >=2fit二结累丄消息stLideftriosnameavgierage12? &32211120S0922210009丰韩吟球许海

17、冰93 00000036.5000003092511110934 33333340935222201夏文妻8G 0000005093722150S94 000000(6) .查询入学考试成绩最高的学生的学号、姓名和入学成绩代码：use teachi nggoselectstudentno, sname , pointfrom stude ntwhere point =(select max( point )from stude nt)结果：(7).查询所有教授'C05127 '号课程的教师信息4use teachinggoselect stdentno snaice . poi

18、nt from student where point=select max pont from student)和二结果消息studertnowarne point1! 0922221324 f何影 STS代码：use teachi nggoselect *from teacherwhere teacher no in(selectteacher nofrom teach_classwhere course no='c05127')结果：use Leachinggoselect ”from teacherwhere 匸eachemQ _. iselect teachernof

19、rom teach classwhere courseao=* cQ512nr週结果昼消息 teachefno triame mjor prof departmentT |S5dO3'J乃1氏朝网谿技术教樓计尊机学院2't05ii'i7“点明处软件测试副戟攪计算机学院(8) .查询同时教授C05127'号和C05109 '号课程的教师信息代码:use teachi nggoselect *in(select a . teacher nofrom teach_class ainneron (a. teacher no= b. teacher nowhere

20、 a . course no='c05127'join teach_class b)and b . course no ='c05109')from teacher where teacher no结果:use techingigo5皂lect. *froin teacherwhere ueacherno m i吕皂lect 注匸eacher口口from teach class a inner join teach clasis bona.tea che rn=b.tea che rnjwhere 日.coujr3eno= p 0 0512 1 and b r

21、coursenQ=1 cQ5109 '4iii3结果區消息teachemD tname majotr prof dsartment(9) .查询至少选修了姓名为韩吟秋的学生所选课程中一门课的学生的学号和姓名代码：use teachi nggoselectdisti netstude ntstude ntno,snamefrom stude ntjoi nscoreon ( student.studentno=score.studentnowheresname <> '韩吟秋'andcourse noin(selectcourse nofromscorejoi

22、n stude nton ( score.studentno=student.studentno)where sname ='韩吟秋')结果：use teachinggo吕巴丄ect distinct; student stude口匕口口 snanr；efrom student j 口:izi score an i student 皂現匸口口二曰匚口工皂.studentncwhere snarneo1 韩吟秋 1 and courseno in(select coursenofrom score join student on score r studentHQ=5T；uden

23、t 日匸udeiii匸:nowhe I e snartLe =1 韩吟秋74结果 J消息studentnD sname(10) .查询没有被任何学生选修的课程编号、课程名和学分代码：use teachi nggoselectcourse no, cn ame , creditfrom coursewhere not exists( select *from scorewhere score . course no=course . course no )结果：亡 teachinggoselect couxseno cusje 亡，ui:便 d 二匸from coursewhere 匸二二 jX

24、2_ =5 select ”from scarewrier e sccre . cours已n一匸ourm已，ccursenc4 |nr1结杲由消息coureeno cname credit1 迹迈J数据结构4.02 "ctKISB '软件工程 3.03 cO6172 铸直工艺 2.5(11) .查询“ C语言”课程期末成绩比“电子技术”课程期末成绩高的所有学生的学号和姓名代码:use teachi nggoselectstude nt.studentno ,sn amefrom score sc1joi nstude nton ( sc1.studentno= stude

25、ntstude ntno)join course c1on (sc1 .course no=c1 . course no)wherec1 . cname ='C 语言'andexists(select*fromscore sc2join course c2on ( sc2 . course no =c2 . course no )where c2 . cname =' 电 子技 术'and sc1 . studentno=sc2 . studentno andsc1 . final >sc2 .final )结果：ESj9c±ece ；9匸口de

26、ne. s'Cuden.'cno P anameilHdJEi score jscI -J c an 且匸udm匸 on f scl - stude-nr no-sfudEmc . E.udeontno >Jioin cou.z'Sie si on f.scl. - C0u.x2enja-"d . ccureriiawhere cl . cn-anije" p11 and exise.3(1 e cc *fz* cm a core sc2 oX n couEae uNon : ac 2 - ccijiirjaena-a2 . couE-aeno

27、wiriere- enuwe°口凸 31.d口口匸“一勺匚童 fleuden匸no 耳nd. 33- “ fXnal*jbcJ . CXn«.X)二姑主 j :wai studerSno sname1 Sf綁顾口2 'dSmiImd?3 OT222t<XKS4 05222213245 OT35Z22201CXH-ItELLDtf 0 RM)CKH-MELLtPRINT语句输出(12) .查询所有班级期末平均成绩的最高分，并将其赋值给变量，通过代码：use teachi nggodeclare max numeric (6,2)select max = max

28、(平均分)from (selectclass noas 班级号,avg (final ) as 平均分from scorejoi n stude nton (score.studentno= student. studentno)joincourseon ( course .course no =score . course no )wherefinalisnot n ullgroupby class no)tprint '所有班级期末平均成绩的最高分：'+cast ( max as varchar ( 6)结果：use teachinggodeclare jnax niane

29、ric 6f 2seiect SmaK；=T：三x (平均分丨f raw * select classno as 班级号卡已审。2inal as 平均分from score jem studentqh aeore , s l udieneno1- 31udent. a'adenmo oin course cn course.courseno-score.eourseno where final is net null group by clasn口 tPrint，所有班级期末平均成绩的最高分i ' cast虹axvarchar fi*E一消总所有班f囁期末平均成绩的最高分：仏阴

30、(13 )实用游标输出学生姓名、选修课程名称和期末考试成绩代码：use teachi nggodeclaresn amenchar (8), cname nchar (10), finaln umeric (6,2)declare sc cursorcursor forselectsname , cname , finalfrom score joi nstude ntjoin courseon (score . stude ntno =stude nt . stude ntno) on (score . course no =course . course no ) open sc_curs

31、orfetch n ext from sc_cursorin tosn ame , cn ame, finalprint'学生姓名 课程名称期末成绩pr int ''whilefetch_status =0beginprintsname +cname + cast ( finalas nchar ( 6)fetch next from sc_cursor into sname , cname, final endclose sc cursordeallocate结杲：sc_cursor13a毁电匕£題亡h丄ElQgdeclaxe 3jname zichar &

32、#163;. iznojne 匚har 10 gfinaJ. n'jjnBZJ.匚 £ . 2declaxe sc_cu.rsDr clltjsci: f口工select snaniE . c-naiiE . finalfrom score m student on acore.student.atudentnajoin u口urs-e cn. c口uhis巴比口u口urs-a . cn'iir'senncpeEinext frgpsimt-Q学生姓名聽程名称期末超print 1 - - -1wnile & fecctiaT；a_0beginprlni

33、;Hanaroi-aasr irinal as ncharXecch nejfc Craminzo sname,. anairie,- finalendiir学生姓容95.00陵术79.(<92.0433 .C1CI51.3d£9. aa9&.0CI |母彌己咸加行“CJ：H：-ULXj&r 曲血 ON) CXHESrmn讪S3)四、总结1、在这次实验之前，认真查阅书本上相关知识点和代码；2、 通过这次实验基本掌握多表连接查询方法、掌握IN子查询的嵌 套查询、了解EXISTS嵌套查询方法。3、子句HAVING（必须要与GROUP B语句使用，这一点非常重要，由于

34、不小心，在实验中有两三次提示这样的错误，切记切记；4、在使用print语句时：-T,所有班级期末平均成绩的最高分: * -cast n；ax as varchar 6如果把“ +”去掉则不会输出max的值，其结果会变成：/CXH-YELLOWmaster -損要use teachingdeclarenuirerlc 6,2select 珮已x=t-e：平均分，fxck > select classno as 班纟及号&9 final as 平均分froir. score ; cit.巴匸udentun score.曰tudun匸no=3匸口d亡nt studentno;ci r.

35、 course> course. cour3eno=5core . coursenc -wher亡 final is nou nullgroup by classnoj zP工"匸，所有班邸期耒平均成缔的屛高分:u己51 |餉启x " va-char 6* 1川Lj结果艦坏融嚥态E行所以这里必须加上+号，如下:CXH-VELLOW.teathirig -宴強瓷wql"播要uae teachinggodeclare nax numeric 6；2select 电it启x=rr_ax i 平均分：from iselect classno a曰ravgfinali a?平均分'f rcrr. s core j it. srudenton score - studentno' student 却匸u:i电ntno二 n二二 Course 3 course . GQurseno=sccire . courseno where final is not nullgroup by claasno:zprin匸'斥有刊盘及期末平:l$成缔的最高分：' 七殆匸弋皿少 “ YHCbHf石)消息所有班维朗末平均成绩的最高分：94.33出这些问题外，试验过程中我还遇到