实验四复杂查询

1、实验四复杂查询-、实验目的掌握两个表以上的连接查询的应用，包括嵌套查询。二、实验内容1、同一数据库中的多表查询(1)查询比“林红”年纪大的男同学信息。II Iselect 一 from Scudent where yesr1 i -year 5irth； /f select yearitetdate ) n-year Bizrhj frem Studentwnere Sname林虹” Aim sek，男”宜结果金消息Srw Srtame Sex BirthQassno Emracce dart赵Hone add, Sd&pt Postcode1 T密f而jf !时 男 195O1tHl

2、&Q0：M：M Ki 2011-09-01 M DO W T晾计篁机系 201300lr 一(2)查询所以学生的选课信息、包括学号、姓名、课号、课程名、成绩。select Student, 5no, Sraroe . SC . Cno. Cnarae . Gradefrom Student. SC. Conr3e|WHERE Student. SnoSC, Sue ?CID Course . CnoSC , Cno;二I结果有消息SnaSnameCnoCnameGrade1.薪筋丁.|张虹001国数892 就张虹002C语言程序设计7B320110001张虹003hvR语言程序设计89

3、420110001张虹0041su al _ BasicNULL5201100D2林虹001局数NULL620110002林虹002c语言程序设计GO720110002林虹003起,语言程序设计NULLa201100D3的M1高效78920110003的002C语言程序设计901020110003的M3前已语言程序设计071120110003的OT4Visual_Ba3751220110103越者001高数801320110103赴者D02C语言程序设计93查询已成功执行口3FH9IXZWYXISKA2（9.口 RTM） 3FH9IIZKfnXSH(3)查询已选课学生的学号、姓名、课程名、成绩

4、select SC . SnoSnameCname , GradeFROM Student 二口口工与已 SCwere SC . Cno-Course »Cno AiJD SC . Sno=5tudent,Sno;_3姑果_j消息StSnameCnameGrade120110001张虹高数89220110001张虹匚语言程序设计7332Q11Q001张虹Jaw语言程序设计89420110001张虹Visual_BasicNULL520110002林虹高数NULL620110002林虹C语言程序设计60120110002林虹J”日语言程序设计NULL820110003Bly苣I数739

5、20110003ilyC语言程序设计%1020110003i&心稣语言程序设计871120110003的Vlsual_Basic7512201101C3赵青高敬801320110103制香C语言程序设计33(4)查询选修了 “ C语言程序设计”的学生的学号和姓名select SC.Snox Sn&mef rata Sc-Lident, SC, Coursewliere 5C. 5na=5VudeDV. Sno .-3JD 5叶.。口口三匚口口匚与已.匚口口 二.工二 Cn3ne=，d吾言程序设计<. . J百结果直肖息Sn。 Sname>.d->： ： >

6、;- Jll： >-d1 !2*1MO1J 张虹2 20r OM2 林虹3 2011803lib'4 20110103超百> 色查询已成功执行.3FH9IXZWYKXSNA2 fi.O ETIfl) 3JH9mffYIXSHA2i(lihinistritor (52) easier 00行 272 列 32Ch 32(5)查询与“张虹”在同一个班级的学生学号、姓名、家庭住址 select 5no . Sname . Hcime_addr from Student where Classno mselect Classno from Student where Sname。&

7、#39;弓长虹' ； 'r"结果消息Sno Srame Home_addr manriiBivmiBimaiaiHaiBiiri1 12011的0 1：张虹南京2 、i而彳前十,时北京(6)查询其他班级中比“ 051”班所有学生年龄大的学生的学号、姓名select Sno, Sname f Hoine_addr from Scudencwhere year ( ) -yea： Birth'1 >salecL rr.ax (year ' gedate i -vear (Birch：from Studentwhere Cla33HO=,' 0

8、51 * );而结果遹消息Sno Snanne Home _addri !i57i66o31 的 北京 1_J（选做）查询选修了全部课程的学生姓名select Sname from student where egc exists 月已二uct . from course where nzt xz_5ta( select * from 昌c where Snostudenu.sno -nd cno-course.cue姑果皇消息Sname1 Wfif n(8)(选做)查询至少选修了学生“20110002'选修的全部课程的学生的学号、 姓名。(9)查询学生的学号、姓名、学习课程名及课程成

9、绩。select Student, Sno . Snauie . Cname . Gradeflora SC；Student,Coursewhere SC. Sno=Student. Sno ar. 1 SC . CHQ=CQUi3e . Cno;口靖果、消息1Sno 20110001SnameCnameGrade张虹高数89220110001张虹(:语言程序设计78| 320110001张虹语言程序设计89420110001张虹NULL52C110002林虹高数NULL62D110002林虹C语言程序设计601 72C11DD02林虹hvm语言程序设计MULL1 8况 11txmlily高数

10、781 92C11DD03lilyC语言程序设计9S102D11DD03lilyhvm语言程序设计871112D110M3lily7isual_Basic7512ZD1101D3超音高数%132D1101D3迪音C语言程序设计%(10)查询选修了 “高数”课且成绩至少高于选修课程号为“002”课程的学生的学号、课程号、成绩，并按成绩从高到底次序排列。select Sno.SC.Cno.Gradefrain SC . CourseWrfERE Cuainei 高数'-rd Grade > select 21A>1 Grade |from SCWHERE Cno-h002*：O

11、RDER BY Grade descSno2QlT0M2Cno001Grade1002Q110K12D110K 12011D003520110103K1 003 003D01&9B987&0(11)查询选修3门以上课程的学生的学号、总成绩(不统计不及格的课程)，并 要求按总成绩的降序排列出来。select Sno,s1 Grede from SCWHERE Sno in( select Sno frcm 5C grcup by Sno having cc jr；c iCno >=3 ard Grade -=60 group by Sno order by k二rr. Sr

12、ade desc<h结果无列名)i 20110003 j 3M201100012011OOC2(12)查询多于3名学生选修的并以3结尾的课程号的平均成绩select avc i Grade 平均成绩 from SCwhere Cna in ( select Cno fram SCWHER5： Cno like,%3, grcjp by CnoHAVING COUNTSno 三3：二结果消息平均成筑(13)5分的学生的学号、姓名、最高分、最低分select SC . Sno Snar.e 2-1ZM i Grade . rr.ax Grade from Student SC WHERE S

13、TUDENT.5N0=SJ5N0 group fcy SC,Sno Sname having MAX Gracie 一工二：】Gracie 511 20110001张虹7889220110002林虹60100320110003附6087420110103赵春6080；无列名)优列名)SnoSname(14)创建一个表student_other,结构同Student ,输入若干记录，部分记录和 Student表中的相同。A.查询同时出现在Student表和student_other 表中的记录。select Tficm Student,r3：;dei:c_ociierere 5tude:t JE

14、C=3t'jdent otiier.Sno;Sno Sname Sex Birth Oassno &irance_dateHomejd巾 Sdept Poll! + !IIIH II II H II ll-H II ll-M II IF1"明吼心脓II1532-11-12 30：O:DD0512。1忖切的M南京计拶住2c22011 眦楸t1991-11-1200:00:00051ffllMIDOM北京iflOIC320110103 O$1MMM1OW051MiiD0M辘口第2DB.查询Student表和student_other 表中的全部记录。select Tfro

15、m Studentunionselect *from 3Vudent_other:口球场舰Sno Sname Sex BirthCassno &trance_date Homejddr Sdept PoScodeIrlHisIHHIrlHHiHI-iielHiHl1120110001佛虹另1S321M2 00：B:M)0612clM湖册册M南京i博机系20M132而而林虹女1S9M1-12D0:K:M05120100:00:00北京计期系10D010320110003lily美1S9MM8M:«):M061期同湖 00册M北京H联系201300420110103能勇13911

16、'W:K:M0512011W1 00:00:00上海软件工程20D013520113286胡佳敏美19期(M500册M)2512clM湖00册册拙|计篁机123789620113291壬丽君女1S3MM2M:M:M2522011W1 00:00:00重庆信息受全123455720113254壬梦欣女忸2*力1100仰优2522C1WM100:M安微信息安全2334422、多个数据库间的多表查询(选做)创建一个数据库 student_info_other ,参数自定。(1)当前数 据库为 Student_info ,将 student_info 数据库 中的表 student_other

17、 复制至U student_info_other 中。create database szudent_info_other; /'1建立新数据库*，select - into 3Cdent_irLf other . dbo . studenCith&r frem student_info.dbo.student_other;w行受影响(2)查询同时出现在 Student 表和 student_info_other 数据库 student_other 表中的记录。select 'from 3tudent_intQ.dto student writer sect selec

18、t from studen't inf other. dbo . sttideiLt other;,一.uh结果_J消息SnoSnameSexBirthCla$noEntrance_dateHome _addrSdeptPostcode1；2Q11WX)1张虹男1992-11-12 00:00W阳201131 00:00:00南京计算机系2W413220110002林虹女1991-111-1200:00:000512D11-09-01 00:00:00北京讨篁机系100010320110103赵吉男1993-05-11 000:000512011-09-01 00:00:00上海软件工

19、程2000133、外连接查询(1)查询所有课程信息及其选课信息，包含未被学生选修的课程。select SC.- 5 Course."from Course left, 0二tur 二二二门 SC an SC. Cno=Course rCnai ;4国结果4消息SooCnoGradeCho CnarrieTotal_pefiorCredit1j 20110001；00189001高数%6220110002001100001高数%32011000300173W1高数%642011010300180Ml高数%e52011000100273002 C语言程序设讨644620110002002

20、60M2 C语言程序设讨64412011000300260002 C语言程序设计644820110103OT260002 C语言程序设计64492D1100010Q389M3日语言程序设计483102D1100020Q3NULLM3启丫石语言程序设计4S3112D11000300387M3日语言程序设计4S3122D110M1OT4NULL004 Visual _Basic484132D11DD0300475004 Visual _Basic48414NULLNULLNULLMS英语64415NULLNULLNULL006数据库酬3(2)查询所有学生信息，所有课程信息及其选课信息，包含未选课课

21、程的学生 及未被学生选修的课程。select Suder.t. SC. Course JFRCH SC 二二二 m ;nr. Student cn iSC.Sno=Suden".SEa 二二：二二M 二二.ccurse cn iSC.Cnc=coi3e.i：iic :<,3P1Sno Sname Sex firthCessna Enbanoejate HonBjddr Sdept Postcode Sno Cho 品de Cho Cname Tctal_p1阿而做SISM 1-12 MCCOfil和呐皿加端献计营解2购32ST10DC1册1的孙剧562,万砺丁麴月1期中上加那比

22、061201M100:M:M前计蒯系20G413M110DC1嘘耨 QC2二箭随装计£。3mm麴I1992-11-12O0:W:CC0612011W1C0:M:W竦计聊系2财3加咖 100333D03揄噬 1程痔蒯48420110M1张虹易1992-1 M2 M:Q0：N061201M1D0M南京计苴隘20m刎0001唯NULL呻强咫皿45520110002就虹安199M1-12 W00051初 1砌 1册的gj 版计篁肌杲1D0010&11D002001NUI1001高教%E2叩做林虹女脚外 12脚O0C051201W1QO：W:t»北京计BO.100010M11

23、D002册26DQC2OIWU殿计80I20110002林虹女1991-11-12n：00：)GC51201M10C:M:M北京计置解100010和咖2DOGNULL003小赢筋晶4S8201TOWI1 那1卸8册瞰0CC61201W1C0:M:W北京计篁机系301300M11D003001忍网高数%32011M取J?MM'MOC啕201M1M#W才版计苴睚独邂25110003002聊皿05懿翩计8。10加1岷3 liy I WMOO« 061111帆1耻於附北京计菖机系201301 2110003 003 87 舶加哨靛序设计4311201 ira出gwramcocsi和帆

24、iomw北京计篁麻m加咖3匹石限收蜕加481220110103踞另19911 00:00:000612011W1WM上海殷件工程20001320110B孙80001高数9G132011Q1OJ诵I1 渊切於帆C0512011W1D0:W:W上海蟒工程200叫2AT101C3CC2/W2CSffiffffit SD14 2叩酬至四 矍啕 201M100» 上簿 睇工程 201301 NULL k NULL H. NULL NULL1520110005李四NV19mK1 MO蚓2011W100：M:M携期工程201301NULLN.,NULLN.,NULLNULL16201 W李WS19

25、92-11-12MQC0612C1W1D0:W:W就计聊系20W1JNULLkNULLN.NULLHULL实验五、视图的创建与使用一、实验目的(1)理解视图的概念。(2)掌握创建视图、测试、加密视图的方法。(3)掌握更改视图的方法。(4)掌握用视图管理数据的方法。二、实验内容1、创建视图(1)创建一个名为stuviewl的水平视图，从 Student_info 数据库的student 表中查询出所有男生的资料。并在创建视图时使用with check option 。CREATE VIEW sttiviewlselect I from studentwre工e 三匕x = ' _3曰&#

26、39; with ctieck option国消息命令已成功完成»(2)创建一个名为 stuview2 的投影视图，从数据库 Student_info 的Course 表中查询学分大于3的所有课程的课程号、课程名、总学时，不在创建时对该视 图加密。c寸二曰w mt。寸工曰wNwith encryptiona. 3select eno,oname r tota±_exiar f jzem cotizzsewhere credits 3;田消息对象1 9 tuviewS T的文本已加密1n(3)创建一个名为stuview3的视图，能检索出“ 051”班所有女生的学号、课 程号及

27、相应的成绩。create view stuview3asselect SC.5no,Cno,Gradefrom Student, SCwhere 5t.udenc , 5110=50. Sno e,nd Classno=1 051T and. 3曰乂二，女国消息命令已成功完成.姓名、总(4)创建一个名为stuview4的视图，能检索出每位选课学生的学号、 成绩。create view stuview4 sno 5 sname r 总成绩 asselect sc. sno . snair.e . m二ie gradefrom scf studentwhere sc . sno=5Cident.

28、sno|group by 5C- ano 5 sname3消息命令已成功完成口2、查询视图的创建信息及视图中的数据(1)查看视图stuviewl的创建信息。方法1:通过系统存储过程sp_help查看。sp_help sruvxewl；|商结果砥消息Name Owner Type Createdjdatetime1 -S-II-I-IMI -US- ：stuviewl | dboview2013-10-2414:29:11.B1CIColumnjiameTypeComputedLengthPrecScale NullableTnm Trailing BlanksFaced Len Niil In

29、 SourceCollation|Ti3 Sno:charno8nononoChmese_P RC_CI_AS2Snamevardiarno8nononoChinese_P RC_CI_AS1 3Saxcharno2nononoCbiinesG_P RC_CI_AS4BirthsmalL.no4nc朝闾匕闻NULL5Cassnocharno3nononoCbiinese_P RC_C l_AS6Entrance_datesmall.no4no(n/a)心间NULL7Home _addrvarcbiarno40yesnoyesChinese_PRC_CLASSSdeptcharno2Dnonon

30、oCFiinese_F RC_C l_ASIdentitySeed Incneuient Not For Replication：No identity calurn defineti. ： NULL NULL NULLRowGuidColUl-I ml Idl-IMI -b- Sai-l-lldl-IUI-l-IIJI-IUI-l-IUI-IUI-l-lUI-l LI l-l:No nowguidcol column defined。直询已成功执行.3FK9IXZWYXXSNA2 (9.0 RIH) 3FH9mmXSHA2Adniini ztrtor G行434 列方法2：通过查询表syso

31、bjectsselect "Jxomwheie rLfiine= 1 uviewl * A-d yce= f V h ;E结果J消息nameiduidrfostetjsbase_schema_veryephrloparent_s?bjcrdateftca.sdieTia_Yer5taCS_51- stuviwlS6957B13SV10000020134034 14；29;11.B10000(2)查看视图的定义脚本。方法1:通过系统存储过程sp_helptext.spelptext stuviewl;方法 2： 通过查询表 sysobjects 和表 syscomments(提示：视图

32、的名称保存在表sysobjects 的name歹1，定义脚本保存在表syscommments勺 text 歹！J)。3、修改视图的定义修改视图stuview2 ,使其从数据库Student_info 的Course表中查询总学时大于60的所有课程号、课程名、学分。 一05select eno, cname f credit totaleriorj from coursevjhere cotal perior>60:命令已成功完成.4、视图的更名与删除(1)将视图stuview4更名为stuv4.if exists select name froni sysobjects where na

33、me=13tuv4' and cype=1V1 DROP VIEW stuv4GOsparer;aice stuview4 f 3tuv4京消息j警告：更改对象名的任一部分都可能会破坏脚本和存储过程.(2)将视图stuv4删除。DROE VIEW Stuv4'j消息命令已成功完成.5、管理视图中的数据(1)从视图stuviewl查询出班级为“ 051”、姓名为“张虹”的资料select * frein stuviewl where anmne_1* and claa3no='0611;< 一曲 能I昆I洎*- - Sno Sname Sex BiithCessna

34、 bitrancejdate Home_addr Sdept Postcode1 7而1而而7张虹 男 19S21V12 00：00:00 0612011<IS01 00:00 00 南京 计算机系 200413'Shihib'IHI ,« Vai aimra if(2)向视图stuviewl中插入一行数据。学号：20110005,姓名：许华，班级: 054,性别：男，家庭住址：南京，入学时间：2011/09/01 ,出生年月：1983/01/09 原Student表中的内容有何变化？没有插入时的student表seleer 3izczn student ;&l

35、t; 皿向结果办消息5noSnameSeatBirttiCesenaEntRnc&_dateHome_dddSdeptPostcodeh-20110001:张虹1男1992-11-12 00:DO:M0l加 11g01 00:40:00南京计亶机系2004132云雨而应林虹女1551-11-12 COXIOC051201HB-01 HHX100北京计篁机系1DOD10口2C11Q00S卜卜男13SMM8 00:00:000l011-09-31 00:40:00北京计箕视系.301300djrnoooe李四男1 2-11-12 0&：00;W0612011-09-01 MHJOO

36、O南屈计算机系200413520110103超音男11 00：»:M)网和11 电时 1 00:00:00上海软件工程200013插入情况：insertinto stuviewl Sno. Sesi®. Classno 5ex, Horr：a_Bddr. Entramce_clHate . Birth 5depL'甘Ku201工。007 J许华./男 3 .南京-T 2011-05-01", SSB-Ql-OS1 , ，计算机。:H -二 ，州1耳消息：1行受膏响插入后原student表的变化:select ”fram scudenc；二箝果心消息SnoS

37、rianieS&f,MiassnoEntranc&_dateHone _addrSdeptPostcode1团 10M1 张虹男1M2-11-12 0000:000G12fl11-0WH M.W:M南京计算机系5M413220110002林虹女05120114M1 OTJO:OQ北京计真机系100010320110G02的男T加1取)国如DCkOO0G12011-0W11 W.W:M北市计篁机系2O134X)420110006李四男0612011 岫)1 WM:0Q南京计篁机票2O华男1983411-03 00:00:000542011-own Ofl

38、 »:M南京计亶机NULL620110103赴吉男1593)5-11 R：。：工051M11-口+。1 W：K;K上海软件工程2OT13由第一、三个图可知，当向视图中插入数据时，数据会被成功插入表student中。(3)思考：如何向视图stuviewl中插入一行数据。学号:2011006,姓名：赵静，班级：054,性别：女，家庭住址：南京，入学时间： 2011/09/01 ,出生年月：1983/11/09。会出现什么样的结果？原 Student表中的内容有何变化？工止色巨士七ntc stuviewl Snc . orzame Classna . 5ex Mctt= addr . En

39、fciance date 三二工 t上 丁 三己任?七values (" 2 011QDQ8' r .赵静)P3E，女,南京 J ED 二二一口 三一口工'J ；L%3一0二一。3 '计算机;岛消息汨息蔗口，级别工公状态。第。行读图进行的插入或更新已失败，原因是目标视图或者目标视图所踏越的某一视图指定了 fflTH CHECK OPTICS而该操作的一个或 百句已到止.答：不能成功插入，因为视图stuview 定义中有对子查询条件作出限制withcheck option ,而插入的数据性别为女，不满足约束。(4)修改视图stuview1中的数据。将stuvie

40、w1中054班、姓名为“许华”同学的家庭住址改为“扬州市”。原Student表中的内容有何变化？原 student :select -fram scudenc；口第果二SnoSrianieS&f,MiassnoEntrance_dateHone _addrSdeprtPostcode1团10001 张虹男1M2-11-12 0000:000G12fl11-0WH M.W:M南京计驾机系5M412220110002林虹女05120114M1阊 的加北京计篁机泵*00010320110G02的男T加1取）国如DCkOO0G12011-0W11 W.W:M北市计篁机系201300420110

41、006李四男0612011 岫)1 WM:0Q南京计篁机系2IM华男1983411-03 00:00:000542011-0WH Ofl »:M南京计亶机NULL620110103赴吉男1593)5-11 R：。：工051M11-口+。1 W：K;K上海软件工程2TO13修改后的student表:update stuviewlset Horae_addr -1 扬州， where Sname ，许华，；select -tram scudenr；Ui口结果口消息SnoSnameSexSrthaesno&itrance_dateHorr»e_ad(jrSdeptFoetcode1加1丽j张虹男1552-11-12 DD:OC