實驗二數據的查詢、更新一、實驗目的1、掌握用戶自定義數據類型的方法2、掌握用T-SQL語句進行數據的插入、修改、刪除的方法3、熟練掌握SELEC期句，能夠運用該語句完成各種查詢二、實驗要求1、實驗前做好上機實驗的準備，針對實驗內容，認真復習與本次實驗有關的知識，完成實驗內容的預習準備工作；2、能認真獨立完成實訓內容；3、實驗后做好實驗總結，根據實驗情況完成總結報告。三、實驗內容1、用T-SQL語句，創建一用戶自定義數據類型：名稱為“char20”,數據類型為varchar,長度為20,允許為空。sp_addtypechar20,'varchar(20)',null&QLQueryl.sql-NW...mini￡trator<S3})"6口工工0f'yq=匚t;口二120)?.4n國:有息命令已成功完成。提示：sp_addtype[@typename=]用戶自定義類型的名字[,@phystype=]系統類型名[,[@nulltype=]'notnull|null'][,[@owner=]' 擁有該類型的用戶名,]例：自定義一個名為address的類型，其所屬系統類型為 varchar,長度為80,不能為

空。sp_addtypeaddress,'varchar(80)','notnull'2、用T-SQL語句，建立一個“學生課程數據庫”，在此基礎上建立該數據庫包含的學生表,課程表，學生選修表，并向各表插入如下相應的數據。createdatabase學生課程數據庫SQLQueryLsql-NW^ministrator(53^)*spaddtypechai20,1varciiar(20)1,n^.11createdatabase字BScreatedatabase字BS課程數據島消息 命令已成功完成。學生表：Student(Sno,Sname,Ssex,Sage,Sdept) 其中Sno為主鍵、Ssex取值為男或女、Sage在15至ij30之間：SnoSnameSsexSageSdept95001李敏勇［男20CS95002劉晨女19IS95003王敏女18MA95004張立男18IScreatetablestudent(snointprimarykey,snamechar(10),ssexchar(2)constraintssex_chcheck(ssexin('男‘，’女’)),sageintconstraintsage_chcheck(sage>=15andsage<=30),sdeptchar(10))insert intostudentvalues(95001,'李敏勇','男'，20,'CS')insert intostudent

values（95002,'劉晨'，'女'，19,'IS'）insert intostudentvalues（95003,‘王敏'，'女'，18,'MA'）insert intostudentvalues（95004,'張立'，'男'，18,'IS'）NWO口X9GMV1PYSA“.庫-dbo.studentSQLQueryl.sql-I 列名數腰型允許Nul值忠33int□sriainecharClO）回ssexcha「⑷同sageintfflsdeptcharflO）回BNWODXSCMYIPV&A...^-dbo.student -dbo.studentJQLQu，snosnamessexsagesdept?頻李墩勇罵■20cs95002劃晨女19E95003王勵女18MAgsotM張立里1SE米MJLLNUUMULLNL11課程表：Course(Cno,Cname,Cpno,Credeit,remarks) 其中Cno為主鍵、Teacher的類型為char20即為用戶定義的數據類型；CnoCnameCpnoCreditTeacher1數據庫54±7712數學NULL2劉新3信息系統14劉新4操作系統63高升5數據結構74宋明6數據處理NULL2張彬7Pascal語百64李磊createtablecourse（cnointprimarykey,cnamechar（15）,cpnointnull,creditint,teacherchar20）insertintocoursevalues（1,'數據庫',5,4,'王芳'）insert into coursevalues（2,'數學'，NULL,2,'劉新'）insert into coursevalues（3,‘信息系統’，1,4,‘劉新'）insert into coursevalues（4,'操作系統’,6,3,‘高升’）insert into coursevalues（5,'數據3吉構',7,4,‘宋明’）insert into coursevalues（6,'數據處理',NULL,2,'張彬’）insert into coursevalues（7,'PASCAL語言',6,4,'李磊'）NWODXQGM￥IPV￡A￡ -dbo.courfie列名 數據類型SQLQueryl.sc誦加1值intcname diar（15）回cpno Intcredit intg]fflteacher 小自「20曲自r+自r國)回“據庫-db?,courseNW0DX9GMY1PY6AS,?據庫-dbo.ccurse$QcnocnamecpnocreditbeadnerD勤據庫54土石2數字NULL1劉新3信息系藐1劉新4操作系統S3高升5數提鰭構74東明6孰據處理f^JLL2張彬7PASCAL1§=5阡李磊蟠NULL皿上AUJNULLALE1學生選修表：SC(Sno,Cno,Grade)其中Sno,Cno為主鍵同時又為外鍵、 Grade值在0到100;SnoCnoGrade950011929500128595001388950022909500325595004270createtablesc（snointcnointgradeintconstraintgrade_chcheck(gradebetween0and100),primarykey(sno,cno),constraint fk_sno foreign key(sno) references student(sno),constraint fk_cno foreign key(cno) references course(cno),)insert intoscvalues(95001,1,92)insert intoscvalues(95001,2,85)insert intoscvalues(95001,3,88)insert intoscvalues(95002,2,90)insert intoscvalues(95003,2,55)insert intoscvalues(95004,2,70)列名數據類型允許Nullfl?Vsintocnogradeintintn回□NWODX9GM￥1P￥6A”.課程頻庫-oibo.SCsno010gr3de19295001235950013湍^500229095003a55站004270來MJLLNUS.L3、用T-SQL語句，修改上面所建學生課程數據庫中數據 ：1）向學生表：Student中加入一條記錄：（95030,謝非，男,22,CS）并保存insertintostudentvalues（95030,‘謝非','男'，22,'CS'）2）將李敏勇的數據庫的成績改為 98分updatescsetgrade=98

o=(ame='數據庫andsno=(selectsnofromstudentwheresname='李敏勇updatesc'setgrade=9Swheresccno=(ofrcmcourseIHHHIwhexe#coTam.e=waT-.d三門匚=｛三尸二mu-三匚廣fromatudentnrwhereanaiae=nr島結果|命令已成功完成#3）刪除學生表Student中謝非的記錄并保存deletefromstudentwheresname='謝非fromstudentwheresname='謝非4）能不能從Student表中刪除李敏勇學生的記錄 ，為什么？能不能刪除王敏，張立兩個學生的記錄？不能，sc表中列sno是外碼，參照student表的sno歹U。4、用T-SQL語句，完成下面簡單的查詢1）查詢全體學生的學號、姓名及年齡fromscfromscuse學生課程數據庫select*fromstudentzS^LQueryl.sql-N...mini?trator(54))*田懾學生課程數據庫nselect*Ifromstudent口結果j消息sn?利第0￡ 爵ex=gwsdept1!"95(?11李勘勇勇20CS2劉晨S19IS395M3王勵女15MA4S5W4張立再15IS2）查詢全體學生的姓名，年齡及所在系（要用小寫字母表示系名，并用“系名”來表示列名）。selectsname,sage,lower（sdept）'系名'fromstudentselectsname,sage,lower(adeptfrom口l11e口nrselectdistinctsnoElselectdiatincEsnoframse|□結果|由消Bll_sno「而f]TOC\o"1-5"\h\z9W023 95D03d 9M044）查詢信息系全體學生的姓名selectsnamefromstudentwheresdept='IS'frcrr. ,wheresdept=1IS口結果門消息snameii劉晨2張立5）查詢所有年齡在20歲以下的學生姓名及其年齡selectsname,sagefromstudentwheresage<20selectsnamesa"ge1L_IT..?:,1：七二之UW=嚏.&一....□結果再融snarrngsageTOC\o"1-5"\h\z-j19zS is張立 106）查詢年齡在20至ij18間的學生的姓名，系別及年齡selectsname,sdept,sagefromstudentwheresagebetween18and20FAselectsname.adeptssagefrom自匕重也史nt:-w?ier& betweenISand20selectsname,sdept,sagefromstudentwheresagenotbetween19and23==--="：一三二日廠口，=■二=-二，==1T口卜.:from4次電口匚wheresagenotfe&twe&n19and23國結果/消息 snamesdeptsage丁■,=王敏|MA18f張立IS1S8）查詢不是信息系（IS）和計算機系（CS）學生的姓名和性別selectsname,ssexfromstudentwheresdeptnotin（'IS','CS'）selectSTiaifie.fromstudenTwheresrieptnotin（*ISssexi""?,""*?'""issexi""?,""*?'""ii王地：女liawtiBiiraiiiBnaBiir^snamesno1 「迪海…！95001WmiirHnu■■■■inraviHiF2王敏95OT312）查詢所有不姓劉的學生姓名selectsnamefromstudentwheresnamenotlike'劉％圓結果總消息|9）查詢所有姓劉的學生的姓名 ，學號和性別selectsname,sno,ssexfromstudentwheresnamelike'張％'山selectsnajr.e.3nc,ssexfxon:student-wh?reahailslike,張『10）查詢姓“張”且名為一個漢字的學生的姓名selectsname,sno,ssexfromstudentwheresnamelike'張'11）查詢名字中第2個字為“敏”字的學生姓名和學號selectsname,snofromstudentwheresnamelike'敏％二結果I」消息orderbygradedescorderbygradedesc□electsuaraefromstudenttfhere且El息置是 liJes13）查詢全體學生情況，結果按所在系升序排列select*safromstudent13）查詢全體學生情況，結果按所在系升序排列select*safromstudent，同一系中的學生按年齡降序fromstudentorderbysdeptasc,sagedescselectorderbysdeptase,sagedes結果.」消息14）查詢學生表中所有學生信息，要求只顯示前 結果.」消息14）查詢學生表中所有學生信息，要求只顯示前 10%行數據selecttop10percent*fromstudentselecttop10percent;",…j--2'--.......*sno sriwi鼻 驊田＜sagesdept[1「§切而]李敬勇里20cs15）按成績降序查詢輸出 SC表中成績大于等于70分的所有學生的學號，要求只顯示前行數據，若第3行后的數據也等于70分也要顯示。selecttop2withtiessnofromscwheregrade>=7017）17）查詢每個學生的各科分數、最高分、最低分、總分、平均分山二七二二!；「叩士withViessueXrcinsowheregrade^vo-orderbygradedesc口結果U消息sns11刑01J2 9500216）查詢每個學生的學號、課程號及分數，同時統計每個學生的總分selectsno,cno,gradefromscorderbysnoselectsno,cno,gradefromscorderbysnocomputemaXgrade),min(grade),sum(grade),avg(grade)bysno白selectmnu,二二口.giadefrcmscorderbysxioI-cornputerr.exI'giede.r.二二，口工己de；r5tnL7rade■f /grade)b中sno結果,J消息snocnograds?，TOC\o"1-5"\h\z1 : 95001 ] 1 082 95001 2 aS3 95001 3 曲1 |"70一"1汨70 705、用T-SQL語句完成下面的查詢1）查詢學生的總人數」結果［應省息人致1『彳一…口selectCOUNTsno)'人數'fromstudent

2）查詢選修了課程的學生人數selectCOUNTdistinctsno）'選課人數fromscseiactCOUNT（discinct月力0）fconn3）計算選2號課程的學生平均成績selectAVqgrade）'平均分'fromscwherecno=24）查詢選修2號課程的學生最高分數selectMAXgrade）'最高分'fromscwherecno=2select皿（爐邑建），最高分L二一:.二,一■----□結吐3消息最高分I■■Mil■■■!■■■■■iKJ5）求各個課程號及相應的選課人數selectcno,COUNTsno）'人數'fromscgroupbycno8）8）查詢每一門課的間接先修課 （即先修課的先修課）6）查詢選修了2門以上的課程的學生學號selectsnofromscgroupbysnohaving(COUNgno)>2)口結果.u海1 \%D01j7)查詢每個學生及其選修課程的情況selectstudent.sno,o,cnamefromstudent,sc,coursewherestudent.sno=o=oselectstLd€ht.3口。,su.crq,Gamefran:studenr,3c,coursewhezrestudeiit.sno=sc：.snoandsc.czno=cou.i?5s州o,c2.cpnofromcoursecl,o=oselect,cl,cncrc2.cpncfromaaursealfcourse方三E二es工,u二二=二三.二二二二I鰭果9）查詢選修2號課程且成績在90分以上（包括90分）的所有學生。selectstudent.sno,snamefromstudentwherestudent.snoin(selectsc.snofromscwherecno=2andgrade>=90)froir.auudenE’.?力三二三m二二？1三二二.m二u二三，三三二三二二mcj.m二二fror.scwherecno?2:andgrade>?30}口結果F-j消息snosname■MilIIMIIIIHHaillMI|1 注翌2」劉JI6.用T-SQL語句完成下面的查詢1）查詢與“劉晨”在同一個系學習的學生selectsno,snamefromstudentfromstudentfromstudentwheresname<>'劉晨'andsdept=(selectsdeptfromstudentwheresname='劉晨')口結果.J消息sname:95M4i張立2)查詢選修了課程名為“數學”的學生學號和姓名selectsno,snamefromstudentwheresnoin(selectsnofromscwherecno=(selectcnofromcoursewherecname='數學'))□selectsnaitefromsfaderL^wheresnairiselectsnofrorr.scwherecna=.selectcnafzcmcourseL whexeunani乏n,數學，))口結果lUi消息srasnameI55001!李敏勇'而（5'劃晨湖硬王敏95M4 張立3）查詢其它系中比信息系中某一學生年齡小的學生姓名和年齡selectsname,sagefromstudentwheresdept<>'IS'andsage<any(selectsagewherestudent.sno=sc.snoandcno=2wherestudent.sno=sc.snoandcno=2wheresdept='IS')由自€1e9(snair.s；3agofromstudenx；wheresdepto1IS1and any(selectsagefionstudentwhexesdept^-15*r .Qi結果近消息snariw0aggj—"，1，，wri，，，，，，1，—r[1i王敏i18ajiiiiLajiinuir4)查詢其它系中比計算機系所有學生年齡都小的學生姓名及年齡selectsname,sagefromstudentwheresdept<>'IS'andsage<all(selectsagefromstudentwheresdept='IS')fromstudent;wheresdepc<^'IS1and dllselectsajcfromstudentwhere3d.ep-t=1ISTL )4 寸廠理1果降尊息SnameSmge5)查詢所有選修了2號課程的學生姓名selectsnamefromstudent,scErcms匚udene*vrfh&rescudenc.sno=ac.anoandcno-2口培果%消息snarne■■■■mi■■■■■■■?■■■iimmi1 !李耿勇！2劉昌王敏鐳立6)查詢沒有選修3號課程的學生姓名selectdistinctsnamefromstudent,scwherestudent.sno=sc.snoandcno!=3：-■■■：■■■：■.■■■■■■■■金二5§：.月之n：jm:wherestudent■3ho=？g.anoandeno!=3sname■IIbaiB!ii?ii|giBaBi|iB—ii!!李勘勇IJ劉晨王敏張立7、用T-SQL語句完成下面的復雜查詢1)至少選修劉老師所授課程中一門課程的女學生姓名selectsnamefromstudentwhereexists(selectsnofromscwhereexists(selectcnofromcoursewhereteacherlike'劉％'andssex='女'))3elecvsnamefromstudentrr^rrfhereexistswhezeexistsiselectcnofromcourseand且且金義=|whereteacherNile牡and且且金義=|snamei期晨！2）檢索王同學不學的課程的課程號fromcoursewherecnonotselectcnoin(ofromsc,studentwheresnamelike'王％'fromcoursewherecnonotandstudent.sno=o=o3二二.一rn1fromcoursewher-ecnonotin(select,ofromsc.studentwlieiresn3nnelx'王9andstudent,sno=sc.snoandcourss.ctlo=sc_cnoIrrr□結果J消息cno3）檢索全部學生都選修的課程的課程號與課程名。selectcno,cname

fromcoursewherenotexists(select*fromstudentwherenotexists(select*fromscwherestudent.sno=o=o))selecteno,cnamefromcourseherenotexiststselectherenotexiststselectwherestudent.sno=sc.sno

andsc.ctiq-course.eno})4)檢索選修課程包含劉老師所授課的學生學號。selectdistinctsnofromscwhereexists(select*o=oandteacherlike'劉％')5)求選修課程號為2的學生的平均年齡。selectAVGsage)'平均年齡’fromstudent,scwherestudent.sno=o=2■?-rt黑直二t；'二~v，不行手空，rroznzf1__identsc.wherestudent*snc=sc.snoand.sc.eno-2口結果度消息平均年聆1i'ie]6)求劉老師所授課程的每門課程的學生平均成績。selectteacher,cnameAVGgrade)'平均分fromsc,course,o=oandstudent.sno=sc.snoandcourse.teacherlike'劉％'groupbyteacher,o,cname7）檢索學號比劉同學大，而年齡比他小的學生姓名。selectsnamefromstudentwheresno>(selectsnofromstudentwheresnamelike'劉％')andsage<(selectsagefromstudentwheresnamelike'劉％')

selectsnamefromstudent山二匚二■二sr.□>(selectsnefromstudentwher?sname11―修文招1工工二='T"21UT」L三=T[Yfmmstudentwheresnamelike1jtlj%1)■rrr8)求年齡大于女同學平均年齡的男同學姓名和年齡。selectsnamesagefromstudentwheresage>(selectavg(sage)fromstudentwheressex='女')andssex='男'selectsnamersa^e口結果」裁息sramesags1 口結果」裁息sramesags1 「手前重’]劉9)求年齡大于所有女同學年齡的男學生姓名和年齡。selectsnamesagefromstudentwheressex='男'andsage>all(selectsagefromstudentwheressex='女')selectsriainefromEtudentwheressex=,男andsage>all(selectsagewher?ssex=*口結果3消息snamesagesnamesagewheregrade>=80)wheregrade>=80)10)檢索每一門課程成名^都大于等于 80分的學生學號、姓名和性別，并把檢索到的值送往另一個已存在的基本表S(SNO,SNAME,SEXselectsno,snamessexintosfromstudentwheresnoin(selectsnofromscwheregrade>=80)selectsnofsnamersse^intosfromstudentrheresnoinfs&lectsno2行受影響11)把選課數學課不及格的成績全改為空值。updatescsetgrade=''wheresnoin(selectsnofromscwheregrade<60)andcno=(selectcnofromcoursewherecname='數學')

12)把王同學的選課信息全部刪去。deletefromscwheresno=(selectsnofromstudentwheresnamelikedeletefromscvher?sno={selectfromstudent?■■■■—?wheresnamelike13E^1)辦消息1行受影響13)把低于總平均成績的男同學成績提高 5%。updatescsetgrade=grade*1.05wheregrade<(selectAVGgrade)fromsc)andcnoin(ofromstudent,scwheressex='男'andstudent.sno=sc.sno)白updateecsetgrade=grade'i.05wheregrade<(selectA^G(quadefromscjandctloin(select與匚.c:口口fromstudentfscwheresse^c=、金'- andstudent.silo-sc.5no)r iir由消息 .彳亍受影響14）檢索沒有選修'1'課程的學生學號和姓名selectsno,snamefromstudentwhere