实验二数据的查询、更新

实验二数据的查询、更新一、实验目的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