实验二数据的查询、更新

徐龙琴设计制作徐龙琴设计制作徐龙琴设计制作徐龙琴设计制作实验二数据的查询、更新实验二数据的查询、更新一、 实验目的1、 掌握用户自定义数据类型的方法2、 掌握用T-SQL语句进行数据的插入、修改、删除的方法3、 熟练掌握SELECT语句，能够运用该语句完成各种查询二、 实验要求1、实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；能认真独立完成实训内容；实验后做好实验总结，根据实验情况完成总结报告。三、实验内容2、3、1、用T-SQL语句，创建一用户自定义数据类型：名称为“ char20”，数据类型为varchar，长度为20,允许为空。sp_addtype char20,'varchar(20)',nullZSQLQueiyl.sql-NW^.ministrator(53))*|spaddtypechar20,*varchar[20Jnullri!內消息 命令已成功完成。提示：sp_addtype[@typename=]用户自定义类型的名字[,@phystype=]系统类型名[,[@nulltype=]'notnull|null'] [,[@owner=]'拥有该类型的用户名 ']例：自定义一个名为address的类型，其所属系统类型为 varchar，长度为80，不能为空。sp_addtypeaddress,'varchar(80)','notnull'char(10),ssexchar(2)constraintssex_chcheck(ssexin('男','sage_chcheck(sage>=15andsage<=30),char(10),ssexchar(2)constraintssex_chcheck(ssexin('男','sage_chcheck(sage>=15andsage<=30),sdept char(10))insertintostudentvalues(95001,'李敏勇','男’，20,'CS')insertintostudentvalues(95002,'刘晨','女’，19,'IS')女')),sageint constraint实验二数据的查询、更新2、用T-SQL语句，建立一个“学生课程数据库”，在此基础上建立该数据库包含的学生表,课程表，学生选修表，并向各表插入如下相应的数据。createdatabase 学生课程数据库EQLQu亡-NW^minifrtrator(53))*|spaddtypechar20,・varchar(20)*,二口！！c"Nt：gda匸abn日g学生i果朿呈数扌居库消息 命令已成功完成&学生表：Student(Sno,Sname,Ssex,Sage,Sdept其中Sno为主键、Ssex取值为男或女、Sage在15到30之间：SnoSnameSsexSageSdept95001李敏勇男20CS95002刘晨女19IS95003王敏女18MA95004张立男18ISprimary key,createtablestudent (snointsname实验二数据的查询、更新insertintostudentvalues(95003,'王敏','女’,18,'MA')insertintostudentvalues(95004,'张立','男’,18,'IS')列名数据类型允诈Null值■HIint□snamechar(10)Essexchar©0sageint0sdeptcbar(lO)0NWODX9G1VIV1PV6A..^-dbo.studentSQLQueirylsql-□snosnarressexsagesdept►3^李敏勇s20CS95003划晨女19K95003王敏女18MA95004张立13IS来ALtZNULLAtAZAt必...NW0DX9GMYIPV6A.,.库-dbo.studentNWODX9GMY1PY5A.»；^-dbo-studentSQLQu.其中Cno为主键、Teacher其中Cno为主键、Teacher的类型为char20即为用户定义的数据类型；CnoCnameCpno 1CreditTeacher1数据库54王芳2数学NULL2刘新3信息系统14刘新4操作系统63高升5数据结构74宋明6数据处理NULL2张彬7Pascal语言64李磊createtablecourse(cnointprimarykey,cnamechar(15),cpnoint null,creditint,teacherchar20insertintocoursevaluesinsert(1,'into数据库',5,4,'王芳')course实验二数据的查询、更新 实验二数据的查询、更新 © 徐龙琴设计制作实验二数据的查询、更新 实验二数据的查询、更新 © 徐龙琴设计制作values(2,'数学’,NULL,2,'刘新')insertintocoursevalues(3,'信息系统’，1,4,'刘新')insertintocoursevalues(4,'操作系统',6,3,'高升')insertintocoursevalues(5,'数据结构',7,4,'宋明')insertintocoursevalues(6,'数据处理’，NULL,2,'张彬insertintocoursevalues(7,'PASCAL语言',6,4,'李磊杉')►?数拥类型咒许Nul值int□Bamechaf(lS)scpnointscreditirtj/iteacherc±iar20^farch3rp£))MlNW0DX9GMVlPY6A£„.jg^-dboxourscjSQLQueryl.sc')NW0DX9GMylP¥6A£...gg库-dbo.courseNW0DX9GMlY1PY6AS..库-dbo.courseSQIaioIDcnanwcpnocrecfitteacher数摇库5斗王芳数学2划新信息系统14刘8f换作菜统63高升数拥结构7斗宋明数揺处理2PAS匚AL语言64李磊MjllALLt学生选修表:SC(Sno,Cno,Grade)Grade值在0到100;SnoCnoGrade950011929500128595001388950022909500325595004270其中Sno,Cno为主键同时又为外键、createtablesc(snoint,cnoint,gradeint constraintgrade_chcheck(grade between0and100),student(sno),primaryconstraintkey(sno,eno),foreignfksnokey(sno)referencesconstraintstudent(sno),primaryconstraintkey(sno,eno),foreignfksnokey(sno)referencesconstraintfkenoforeignkey(eno)references列名数据类型int$tnograde列名数据类型int$tnogradeintint□□HDcourse(eno),)insertintoscvalues(95001,1,92)insertintoscvalues(95001,2,85)insertintoscvalues(95001,3,88)insertintoscvalues(95002,2,90)insertintoscvalues(95003,2,55)insertintoscvalues(95004,2,70)zNWQDX9<iMYlPY$A.„^l|gg-dbo-tcNWODX9GMY1PV^3、用1)insertvalues2)snomoqrade192950012359500133S950022909500325595004270NULLNULLNULLT-SQL语句，修改上面所建学生课程数据库中数据 ：向学生表:Student中加入一条记录：（95030,谢非，男,22,CS）并保存intostudent(95030,'谢非','男',22,'CS')将李敏勇的数据库的成绩改为 98分徐龙琴设计制作徐龙琴设计制作徐龙琴设计制作徐龙琴设计制作实验二数据的查询、更新update scsetgrade=98setgrade=98ind3no=(selectsnefromstLidentwhereccuzse.ciiaiEe=where3nar.e=ind3no=(selectsnefromstLidentwhereccuzse.ciiaiEe=where3nar.e=wheresc.eno=(ame='数据库'andsno=(selectsnofromstudentwheresname='李敏勇'update3CseLgrad亡=93c=(selectc□:irse.cuefrorrcoursenrgj结果 命令已成功完成。3)delete删除学生表Student中谢非的记录并保存fromstudent3)delete删除学生表Student中谢非的记录并保存fromstudentwheresname='谢非'■4行受影响）4）能不能从Student表中删除李敏勇学生的记录 ，为什么？能不能删除王敏，张立两个学生的记录？不能，sc表中列sno是外码，参照student表的sno列。实验二数据的查询、更新4、用T-SQL语句，完成下面简单的查询1）查询全体学生的学号、姓名及年龄use学生课程数据库select*from studentSQLQueryLsql-N^ministratorfS4))*[-e学主课程数据库nselect”年龄及所在系（要用小写字母表示系名，并用“系名”来表示I-fromstudenE年龄及所在系（要用小写字母表示系名，并用“系名”来表示snosnamessexsagesdept195001i李敏勇s20CS2"刘晨女19IS395003王敏女18MA495004张立畀18ISH结果自消息2）查询全体学生的姓名列名）。selectsname,sage,fromstudentlower(sdept)'系名'selectsname,sage,Icwer<adeptJ■糸a结果b消息snamesage系容11李魏里i202剋晨19is3王啟iarra4张立ISis3）查询选修了课程的学生学号selectdistinetsnoselectdistinetsno实验二数据的查询、更新 实验二数据的查询、更新 ◎ 徐龙琴设计制作实验二数据的查询、更新 实验二数据的查询、更新 ◎ 徐龙琴设计制作fromscAselectdistinctsncLfromsd3结果止肖息［sno95003fromscAselectdistinctsncLfromsd3结果止肖息［sno95003950044）查询信息系全体学生的姓名selectsnamefromstudentwheresdept='IS'3亡亠亡CtsnaitefroET,SbUdentwriereadepts'15*Q结果肖息sname1丽貳2张立5）查询所有年龄在20岁以下的学生姓名及其年龄selectsname,sagefrom studentwheresage<20selectsziaraefsagefroxs匸udmnxuierezaae<2QS结果LJ1消息sage191310snamesage191310「頑王敏強立

06）查询年龄在20到18间的学生的姓名，系别及年龄select sname,sdept,sagefrom studentwheresagebetween18and20右selectsnaiLe,adept,sagefromstudent=wheresagetEtwee二16and20Q结果E消息snamesdep4sage1i李»里jCS■2D2划晨IS193王敏MAIS4张立IS137）查询年龄不在23到19间的学生的姓名，系别及年龄select sname,sdept,sagefrom studentwheresagenotbetween19and23selectsname,sdepc,sagefromstudentwHe工msagenotbetween19and23\3结果由消息18snamesdeptsage1813j王敏丨MA13张立IS8）查询不是信息系8）查询不是信息系（IS）和计算机系（CS）学生的姓名和性别select sname,ssexfrom studentwhere sdept notin('IS','CS')selectsnszae,fromstridentwheresdeptno匸in('IS",'C5*)a结果匕消息snanie 甜ex1fllJ女徐龙琴设计制作实验二数据的查询、更新徐龙琴设计制作实验二数据的查询、更新徐龙琴设计制作实验二数据的查询、更新徐龙琴设计制作实验二数据的查询、更新9）查询所有姓刘的学生的姓名 ，学号和性别selectsnameselectsname,sno,ssexfrom studentwheresnamelike'张%'[iselectaname,ano,saexfromMudent-wheresnair.eiiJce'弓长毘'10)查询姓“张”且名为一个汉字的学生的姓名selectsname,10)查询姓“张”且名为一个汉字的学生的姓名selectsname,sno,ssexfromstudenta结果匕消兄snamesnossex■•— ■—1丨张立i35004胃WheresnaiceWheresnaicewheresnamelikeselectsnaniesnc,ssexfrorr.scudent查询名字中第2个字为”敏”字的学生姓名和学号selectsname,snoselectsname,snofromstudentwheresnamelike敏%'wheresnamelike敏%'selectsnaine,snofrom3匸gen©wTiereanamelike載玉'□结果消息i孚敏勇I95W1王掇95003查询所有不姓刘的学生姓名selectsnamefromstudentwheresnamenotlike'文刘%'日亡丄ec匸saartefzGinstudentwheresnamenetlike'文Isname住敷雲张立查询全体学生情况，结果按所在系升序排列，同一系中的学生按年龄降序selectfromstudentorderbysdept asc,sagedesc■select*frorcstudentorderbyadeptasc,sagedescl14）查询学生表中所有学生信息，要求只显示前14）查询学生表中所有学生信息，要求只显示前10%行数据s结果岂消息snosnarrtessexsageadept1i95001i李敏里20CS295002划晨女13IS395004张立TS'IS495003王锁女TB'MAselecttop10 percentfromstudentselectcep10percent*fromstudent3结果b消息1snosnaiTie 酩ex sage 0已pt1I95001!李敏勇男 20 CS按成绩降序查询输出SC表中成绩大于等于70分的所有学生的学号，要求只显示前行数据，若第3行后的数据也等于70分也要显示。selecttop2withtiessnofromscwheregrade>=70orderbygradedesc实验二数据的查询、更新 实验二数据的查询、更新 0 徐龙琴设计制作实验二数据的查询、更新 实验二数据的查询、更新 0 徐龙琴设计制作fromsefromse占selecttop2withtiessnofrom3Cwheregrade>=?0-orderbygradedescsnoI55(W1i9500216）查询每个学生的学号、课程号及分数，同时统计每个学生的总分sno,sno,eno,gradeorderbysnoordercompUte sum(grade)bysnoNW0DXgGM¥lP¥6A“課理站萍-dbo-scAselectmno”cnc.gradefromscorderbyano-ccitpuES3；JX[(grade：itbysne3结果.冷消息snoenograde1!95001I1982950012853950013&8sumI27]\snocnagrade亙矿I2Msum!90Isnoenograde1 [9500312 55sum汕5isnomogr^deH/iSn2 70sumI70i17）查询每个学生的各科分数、最高分、最低分、总分、平均分徐龙琴设计制作实验二数据的查询、更新徐龙琴设计制作实验二数据的查询、更新徐龙琴设计制作实验二数据的查询、更新徐龙琴设计制作实验二数据的查询、更新selectfromsno,eno,gradescorderbysnoordercompute max(grade),min(grade),sum(grade),avg(grade)bysnonselectsno,eno,gradefromscorderbysno=computemax(grade),jri.j.r.gzadej gradei,avgiigrade}bysnenr3结果Itj消鳥grade開35assnoenograde開35as「扁而…]1—-l-lj9won2TOC\o"1-5"\h\z\o"CurrentDocument"95001 3maxminsumavg「號"]S5 271 90snoenograde「融厂i2 90maxmin\W"\和sumavgM90maxmin\W"\和sumavgM90snoeno2grade551）查询学生的总人数fxsetCOUNT(SDQ)'人数'froir.3Uden人数selectfromCOUNT(sno)'人数'student2)查询选修了课程的学生人数selectCOUNT(distinctsno)'选课人数'fromscselectCOUNT(dis匸inc二ano}鼻选课人数里3n结果自消息"瘗△数”、1『213)计算选2号课程的学生平均成绩selectAVG(grade)'平均分'fromscwhereeno=2selectAVG{gradeJ''3Crecn□E3结果匕消息平均分查询选修2号课程的学生最高分数selectMAX(grade)'最高分'fromscwhereeno=2慮懿Lm''"'"求各个课程号及相应的选课人数selectcno,COUNT(sno)'人数'fromscgrouPbyeno徐龙琴设计制作实验二数据的查询、更新徐龙琴设计制作实验二数据的查询、更新实验二数据的查询、更新 实验二数据的查询、更新 ◎ 徐龙琴设计制作0结果肉消息eno人数6）查询选修了2门以上的课程的学生学号selectsnofromscgroupbysnohaving(COUNT(cno)>2)from3CgxcupbysuerHaving[COUNT[eno)>2)I囲结果I自消息IsnoRSioi"]7）查询每个学生及其选修课程的情况select student.sno,select student.sno,sc.eno,cnamefrom studentsc,coursewherestudent.sno=sc.snoandsc.eno=course.enowherestudentstudent.sno,3C.cnc,cnair.eseleststudent.sno,3C.cnc,cnair.efXomstudent,3C,coursewherest-udent.3nc=3c.snoandsc.ciic=ccTirse.enolrrr3结果曲1消息snoenocnane1j95001i1数据库2350012数学3950013信息系统4950022数学5950032数学€950042数学8）查询每一门课的间接先修课 （即先修课的先修课）

selectc1.enoselectc1.eno,c2.epnofromcoursec1,coursec2wherec1.eno=c2.enoselectcl.eno,c2,epno■fromccLirsecl,o=c2.erwerwepno5NULLNULL9）查询选修NULLNULL9）查询选修2号课程且成绩在90分以上（包括90分）的所有学生。selectstudent.sno,snamefromstudentwherestudentselectstudent.sno,snamefromstudentwherestudent.snoin(selectse.snofromscselectfrciffistudentwherecn.c=zandgrade>=90}whereeno=2andgrade>=90)sxudsni:・sne,snetmeselectfrciffistudentwherecn.c=zandgrade>=90}whereeno=2andgrade>=90)sxudsni:・sne,snetme□结果snosnamer'062'l刘晨6.用T-SQL语句完成下面的查询1）查询与“刘晨”在同一个系学习的学生selectsnoselectsno,snamefrom studentwheresname<>'刘晨'andselect3nc,snamefrorrscudentwnereanaiLeo'wheresname='wheresname<>'刘晨'andselect3nc,snamefrorrscudentwnereanaiLeo'wheresname='文刘晨')PT"——3sdept=(select sdeptfrom studentand3ciepr=(selectadeptfromszdencwhere3ndire='^1snosname:95001I张立2）查询选修了课程名为“数学”的学生学号和姓名selectsno,snamefromstudentwheresnoin(selectsnoselectsno,snamefromstudentwheresnoin(selectsnofromscwhereeno=(selectenowhereeno=(selectenofromcoursewherecname='数学'))0selectsnOrSnaiEefromst池dentwrieresne二二斗selectanof工o比scwhereeno=(selectsnofromcoursewherecname二'数学’Htir9003nan>e1!35001235002划晨335003王锁435004张立3）查询其它系中比信息系中某一学生年龄小的学生姓名和年龄selectsname,sagefromstudentwhereselectsname,sagefromstudentwheresdept<>'IS'andsage<any(selectsagefromstudentselectsnaine,sagefromstudentwhereadepto*IS'□(R!厲曉"gigsnarne sage至nIB4）查询其它系中比计算机系所有学生年龄都小的学生姓名及年龄selectfromselectsnaine,sagefromstudentwhereadepto*IS'□(R!厲曉"gigsnarne sage至nIB4）查询其它系中比计算机系所有学生年龄都小的学生姓名及年龄selectfromsname,sagestudentwheresdept<>'IS'andsage<all(selectfromsagestudentwheresdept='IS'|iselectsname,sagefromstudentwhere^depto*IS*and3age<allIselectsagefromstudentwheresd柱pp='IS'where sdept='IS')andBage<iselectsagefromstudentwheieBdept-*IS'SnameSage5）查询所有选修了2号课程的学生姓名select snamefromstudent,scwherestudent.sno=sc.snoandeno=2selectsnamewzudenZf3Cwherestudent.sno-se.sueandeno=23结果ILj消息sname-晨ai

議王张6）查询没有选修3号课程的学生姓名-晨ai

議王张6）查询没有选修3号课程的学生姓名selectdistinctsnamefrom studentscwherestudent.sno=sc.snoandeno匸35eleccdistinctsselectsnamefromstudentwhereexistswherestudent.sno=sc.snoandeno匸35eleccdistinctsselectsnamefromstudentwhereexists(selectsnofrcrcstudent,sc吋herestudent.sno=5C,snoandccct=3□结果凸消息Isname刘晨王敏张立7、用T-SQL7、用1）至少选修刘老师所授课程中一门课程的女学生姓名fromscwhereexists(selectenofromcoursewhereteacherlike'刘%'andssex='女'))whereteacher实验二数据的查询、更新 实验二数据的查询、更新 ◎ 徐龙琴设计制作实验二数据的查询、更新 实验二数据的查询、更新 ◎ 徐龙琴设计制作nr2）检索王同学不学的课程的课程号selectenofromcoursewhereenonotwhereenonotin(selectsc.enofromsc,studentwheresnamelike王%'andstudent.sno=sc.snoandcourseeno=sc.enofromcoursewhereenonotin叩Tfromcoursewhereenonotin叩Tsc.enofromsc,studentwheresnamelike'王％'andstudent.sno=sc.snoandcourse>cno=sc<enoeno3）检索全部学生都选修的课程的课程号与课程名。selectfromeno,cnamecoursewherestudent*sno=sc*o=course<eno))4）检索选修课程包含刘老师所授课的学生学号。selectfromwherewherestudent*sno=sc*o=course<eno))4）检索选修课程包含刘老师所授课的学生学号。selectfromwheredistinetsnoscexists(select*fromcoursewheresc.eno=course.enowherenotexists(select*fromstudentwherenotexists(select*fromscwherestudent.sno=sc.snoandsc.eno=course.eno))selecteno,cnamefromcoursewherenotexists(select*fromstudent.wherenotexists(select*fromscenocnamei2ii藪学andteacherlike'刘%')selectdistifromscwhereexists(select*fromcoursewheresc*cno=course.enol andteacherlike■京insno95003950045）求选修课程号为2的学生的平均年龄。selectAVG(sage)'平均年龄'fromstudent,scQIwherestudent.sno=o=2QIselectAVGCsage)，平均年龄，fromstudent,scwherestudent,sno=sc,snoandsc,cno=2国结果応消al龄—1rW…—16）求刘老师所授课程的每门课程的学生平均成绩。selectteacher,cname,AVG(grade)'平均分'selectfromwhereandstudentsc,course,studentsc.eno=course.sno=sc..enosnoandcourse.teacherlike'fromwhereandstudentsc,course,studentsc.eno=course.sno=sc..enosnoandcourse.teacherlike'刘％'groupbyteacher,course.eno,cnamegroupbyselectteachercname,AVG(grade)「平均分'fromsc,courseFo=course.enoandstudent・sno=sc.snoandcourse.teacherlike'丈U%'groupbyteacher,course.eno,cname□结果匕消息平均分75平均分75薪数学刘新 信息系统assnamestudent7）检索学号比刘同学大，而年龄比他小的学生姓名。selectsnamestudentfromwheresno＞（selectfromsnostudentwheresnamelike'刘％')andsage<(selectsagefromstudentlike'刘%')studentlikeXII%'}sname,sagestudentlike'刘%')studentlikeXII%'}sname,sagestudentsage>(selectavg(sage)fromstudentwheresname'selectsnamefromstudentwheresno>sefromwheresnameandsage<(selectsagefromstudentwheresnameiir罔结果消息snanrieris1张立8）求年龄大于女同学平均年龄的男同学姓名和年龄。selectfromwherewheressex='女')andssex='男'sname,sagestudentwheresname,sagestudentwheressex='男'selectsna^me,sagefromstudentwheresage>(selectavg(sage)fromstudentwheresseK='女'}andssex='男'霆结果暑消息IsnamesagefiSI'n209）求年龄大于所有女同学年龄的男学生姓名和年龄。selectfromandsage>all(selectsagefromstudentwheressex='女')selectsname,fromst口dentwhere5sex="andsage>all(setsagefromstudentwheresseK='女、\sname$age2010）检索每一门课程成绩都大于等于 80分的学生学号、姓名和性别，并把检索到的值送往另sno,sname,ssexsstudentsnoin(selectsno

fromscwheregrade>=80)一个已存在的基本表sno,sname,ssexsstudentsnoin(selectsno

fromscwheregrade>=80)selectintofromwhere

selectsno,sname,ssexintosfromstudentwheresnoin(selectsnofromscwheregrade>=B0)总行受影响）把选课数学课不及格的成绩全改为空值。update scsetgrade=''wheresnoin(selectsnofromscwheregrade<60)andeno=(selectenofromcourseupdatescsetgrade='wheresnoin(selectsnofromsccoursewherecname='数学')whereenmine二firtl行受影响）12coursewherecname='数学')whereenmine二firtl行受影响）12肥王同学的选课信息全部删去。deletefromscwheresno=(selectsnofromstudentwheresnamelike'王%')实验二数据的查询、更新 实验二数据的查询、更新 ◎ 徐龙琴设计制作实验二数据的查询、更新 实验二数据的查询、更新 ◎ 徐龙琴设计制作徐龙琴设计制作徐龙琴设计制作实验二数据的查询、更新deletefromscwheresno,scandstudent.snosc.sno)实验二数据的查询、更新deletefromscwheresno,scandstudent.snosc.sno)snofromstudentwhetesnamelike'王％'}n行受影响）13肥低于总平均成绩的男同学成绩提高 5%。update scsetgrade=grade*1.05wheregrade<(selectAVG(grade)fromsc)andenoin(selectsc.enofromstudentwheressex占updatescsetgra