新SQL——SERVER实验练习答案

实验SQL-Server答案表数据的输入3SCSno CnoGrade30021081128三参照完整性约束•掌握表之间建立外码后，对被参照表的如下操作会有何影响:修改主码、插入新行、删除新行？•对参照表添加新行、删除行、修改外码值有何影响？•掌握级联修改、级联删除的概念注意:表SC的Sno是外不表SC的Cno是外不参照student的sno参照course的cno1输入实验前的数据学生表StudentSno SnameSsex SageSdept赵尹男400120SX4002杨开女20JSJ课程表courseCno CnameCpno Ccredit1088Java51089数学3学生选课SCSno CnoGrade400110889040021088862试验过程）在表中添加新行:1SCSno CnoGrade4001106676记录试验结果.，写出出现此结果的原因.不能添加，因为在是外码，参照的但在中没有课程cno course cno,course1066）在表中添加新行2studentSno SnameSsex SageSdept4003赵辉男21SX记录试验结果.，写出出现此结果的原因.可以输入）删除表的学生3student4001,4002记录试验结果.，写出出现此结果的原因.两个学生不能被删除，因为的外码参照的中已经有学生的数据，因sc snostudent sno,sc4001,4002此不能删除思考•删除表的记录有限制吗？SC没有•采取什么技术能使不能成功执行的命令变得可以执行，且使数据库保持数据完整性级联删除把表的学号改为改为4student40034018,40014021记录试验结果.，写出出现此结果的原因.可以改为不能改为40034018,40014021因为的外码参照的中已经有的数据，但没有的选课数据sc snostudent sno,sc40014003思考采取什么技术能使本题不能执行的命令可以执行，且使数据库保持数据完整性级联修改把表中的如下记录的学号从改为5sc40014011Sno CnoGrade4001108890记录试验结果.，写出出现此结果的原因.不能修改,因为的外码参照的在中不存在sc snostudent sno,4011student如不成功，则可以采取什么方法来实现此要求需要在表中添加学生student4011•如不成功，那么把修改为能成功吗？40014003,能成功！思考参照完整性规则中,外码可以为空，但表中的外码可以为空吗为什么？SC举一个外码可以为空的例子不可以，因为表的主码为即为主属性，所以不能为空sc sno+cno,sno,eno试验五索引目的掌握索引的建立、删除的方法一创建索引建的索引1student为姓名建立索引，索引名Ix_student_sname为系科建立索引，索引名Ix_student_sdeptcreate index ix__student_sname ONstudentsnamecreate indexix_student_sdept ONstudentsdept的索引2SC为课程号建立索引ix_sc_cnocreate indexix_sc_cno ONsccno的索引3Course为课程名建立唯一性索引Ix_course_cnamecreate uniqueindexix_course_cname ONcourse cname如何查看索引刚才建立的索引？如何在企业管理器中查看索引？4SP_HELP二册除索弓表的索引U Icourse IX_course_cnameDROP INDEXcourse.ix_course_cname三思考如何把索引修改为唯一性索引？可以使用企业管理器或先删除索引，再重IX_student_sname新建立*四思考建立索引的目的输入下列存储过程，该程序生成大量数据供测试1create procedureusp makedataas一计数器declare@nCnt int,@sNo varchar6,@sname varchar8set@nCnt=12000while@nCnt999999beginset@nCnt=@nCnt+1张set@sNo=convertvarchar6,@nCnt set@sName=+@sno酝男，insert into student sno,sname,ssex,sage values@sno,name,20endreturn--生成测试数据输入下述测试程序2exec usp_makedata3create procedureusp_test asdeclare@nCount int,©data intset@nCount=0while@nCount100begin二〈张〉张select@data count*from student where sname3800or sname8800set@nCount=@nCount+1end测试4建立姓名的索引，查看运行时间秒.18--建立索弓create indexix_student_sname on student snameI execusp test删除姓名索引，查看运行时间分秒，比较与的时间长短22111drop indexstudent,ix_studcnt_sname-一册除索弓U Iexecusp_test试验六更新数据目的掌握语句的使用insert,update,delete一insert写出把下述学生的信息添加到表中的命令1student学号姓名性别年龄系科4001赵茵男20SX4002杨华女21赵茵、男Insert intostudent sno,sname,ssex,sage,sdept values4001,20,SX了杨华丁女Insert intostudent sno,sname,ssex,sage values4002\21批量插入数据2建立一个新表，有属性1sc_name sno,sname,ssex,eno,grade CREATETABLE sc_name Sno char6,Sname varchar20,Ssex char2,eno char4,grade int把系学生的插入到表中2SX sno,sname,ssex,eno,grade sc_nameInsert intosc_name sno,sname,ssex,eno,gradeselect student.sno,sname,ssex,eno,grade from student,sc where student.sno=sc.sno and sdept=SX察看表的数据3sc_name select*from sc_name二Update修改学生的系科为10001JSJUpdate student set sdept=,JSJ,where sno=0001把陈小明的年龄加岁，性别改为女21女二陈小明修改李文庆的课程的成绩Update student set sage=sage+l,ssex=where sname31001为分93update scset grade=93where cno=,1001and sno inselect sno from student where sname二’李文庆把“数据库原理”课的成绩减去分41update scset grade=grade-1where eno in数据库原理select eno from course where cname=三Delete删除所有系的男生1JSJdelete from student where sdept=JSJ删除“数据库原理”的课的选课纪录2（数据库原理’）Delete from sc where eno in select enofrom course where cname=思考修改数据的命令与修改表结构的命令有何区别？试验七查询语句Sql目的掌握查询语句Select一单表查询年龄在至岁之间的女生的学号,姓名，年龄,按年龄从大到小排列11921select sno,sname,sage from student女where sagebetween19and21and ssex=order bysage desc2查询姓名中第戎2个字为“明”字的学生学号、性别［明％’select sname,ssex from student where sname like查询课程没有成绩的学生学号、课程号31001select sno,enofrom sc where grade isnull andcno=,1001,查询、系的学生学号，姓名，结果按系及学号排列4JSJ SX.WLselect sno,sname from student where sdept inJSJ,SX,WLorder by sdept,sno按分制查询学生的分制成绩510sno,eno,10分为分为分为为1-101,11-202,30-393,90-10010select sno,eno,grade/

10.0+1as levelfrom sc查询表中的学生共分布在那几个系中6student distinctselectdistinct sdeptfrom student查询号学生课程的成绩700011001,1002Select enofrom sc where sno=0001and eno=1001or eno=1002二统计1查询姓名中有“明”字的学生人数％明select count*from student where snamelike%’2计算js「系的平均年龄及最大年龄Select avgsage,maxsage from student Wheresdept=JSr计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列3select eno,sumgrade,avggrade,maxgrade,mingrade from sc group by enoorder by avggrade desc计算课程的平均分41001,1002Select eno,avggrade from sc where enoin1001’,1002Group by eno查询平均分大于分的学生学号及平均分580select sc.sno,avggrade from scgroup bysc.sno having avggrade80统计选修课程超过门的学生学号62select sno from sc group by sno havingcount*2统计有位成绩大于分以上的课程号71085Select enofrom scwhere grade85group byeno havingcount*=10统计平均分不及格的学生学号8select sno from sc group by sno havingavggrade60统计有大于两门课不及格的学生学号9select sno from sc where grade60group by sno havingcount*2三连接查询系的学生选修的课程号1JSJselect enofrom student,sc wherestudent.sno=sc.sno andsdept=,JSJ查询选修课程的学生的学生姓名不用嵌套及嵌套种方法210022a:select sname from student,sc wherestudent,sno=sc.sno andcno=,1002’b:select sname from student where snoin select sno from sc where cno=,1002查询数据库原理不及格的学生学号及成绩3select sno,grade from sc,course数据库原理where sc・eno=course,eno and cname=查询选修“数据库原理”课且成绩以上的学生姓名不用嵌套及嵌套种方法4802a:select sname from student,sc,course数据库原理，wherestudent.sno=sc.sno and sc.eno=course,eno and grade80andcname=b:select sname from student where snoinselect snofrom sc wheregrade80and enoinselect enofrom coursewherecname=,数据库原理，查询平均分不及格的学生的学号，姓名，平均分5select sno,maxsname,avggrade asavggrade from sc,studentwhere student.sno=sc.snogroup bystudent,snohaving avggrade60查询女学生平均分高于分的学生姓名675A:Select sname from student wheressex=,女and snoinSelect snofrom sc group by sno havingavggrade75B:Select maxsnamefrom sc,student wherestudent.sno=sc.sno andSsex=,女查询男学生学号、姓名、课程号、成绩（一门课程也没有Group bystudent.sno havingavggrade757选修的男学生也要列出，不能遗漏）select student,sno,sname,eno,grade from student leftjoin scON男student.sno=sc.sno and ssex=’四嵌套、相关及其他查询平均分不及格的学生人数1select count*from student where snoinselect snofrom sc group bysno havingavggrade60查询没有选修课程的学生的学生姓名21002select sname from student where snonot inselect snofrom sc where eno=1002student0001aa X0002bb今0003cc Xsc00011001000110020002100100031002select sname from student where notexists select*from sc where eno=1002andsc.sno=student.sno查询平均分最高的学生学号及平均分种方法32TOP,any,alla:select top1sno,avggrade from sc group bysnoorder byavggrade descB:select sno,avggrade from sc group by snohavingavggrade=select top1avggrade fromsc groupbysnoorder byavggrade descc:select sno,avggrade fromscgroupby snohavingavggrade=allselect avggrade fromscgroupbysno查询没有选修课程的学生姓名*41001,1002Select snamefrom student where notexists Select*from coursewhere enoin1001，1002and二Not existsselect*fromsc where sno=student,sno and eno course,eno查询课程第一名的学生学号种方法510022a:select top1snofromsc eno=1002order bygrade descb:select snofromsc whereeno=1002andgrade=all selectgrade fromsc whereeno=1002查询平均分前三名的学生学号6select top3snofromscgroupbysnoorderbyavggradedesc查询系的学生与年龄不大于岁的学生的差集7JSJ19a:select*from student where sdept=,JSJ andsage19b:select*from student wheresdept=JSJexcept select*from studentwhere sage19查询号课程大于分的学生学号、姓名及平均分大于分的学生学号、姓名810019085select student.sno,snamefrom student,sc whereeno=1001andgrade90unionselect sno,snamefrom studentwhere snoinselect snofromscgroupbysnohavingavggrade85查询每门课程成绩都高于该门课程平均分的学生学号9select snofrom studentwhere snonot inselect snofromscX wheregrade二select avggrade fromscY whereY.sno X.snoselect snofrom studentwhere snonot inselect snofromscX wheregradeselect avggrade fromsc whereeno=X.eno查询大于本系科平均年龄的学生姓名10select snamefrom studentX where sageselect avgsage from studenty wheresdept=x.sdept试验八视图目的掌握视图的建立、使用建立学生学号、姓名、性别、课程号、成绩的视图查看中的数据1v_sc V_scCreate viewv_sc sno,sname,ssex,eno,gradeasSelect student.sno,sname,ssex,eno,grade from student,scWhere student.sno=sc.snoSelect*from v_sc建立学生学号、姓名、出生年月的视图1v_age查看中的数据V_ageCreate viewv_age sno,sname,sbirth asSelect sno,sname,2008-sage from studentSelect*from v_age2建立6JSJ9系的学生学号、姓名、性别、年龄的视图V_JSJCreate viewvjsj sno,sname,ssex,sage asSelectsno,sname,ssex,sage from studentwheresdept=,JSJ,建立每门课程的平均分的视图3V.avggradeCreate viewv_avgGradecno,grade1asSelect eno,avggrade fromscgroupbyeno将视图中李文庆的年龄改为岁4vjsj21二李文庆Update vjsjset sage=sage+l wheresname察看中李文庆的年龄5student查看中李文庆的出生年月v_age李文庆Select*from studentwheresname=李文庆’Select*from v_age wheresname=查询每门课程的及格率6Create viewvl eno,cntl asSelecteno,count*fromscgroupbyenoCreate viewv2eno,cntl asSelecteno,count*fromsc wheregrade=60groupbyenoSelect vLeno,cnt2*

1.0/cntl fromvl,v2where vl.cno=v

2.cno思考1利用VJSJ视图，可以更新sx的学生的年龄吗？写出理由如update vjsjset sage=25where sno=,00049号学生为系.0004SX试验九安全性控制实验目的掌握Sql-server的授权机制.）建立新用户,密码1mary1234Sp addLogin4mary,‘1234）授予可以访问数据库的权力2mary School选择数据库schoolSp_grantDBaccess mary）以登录3mary sql-server,执行，记录执行结果,说明原因select*from student无法查到数据，因为没有查询的权限mary student）将的查询、更改权限授予4course mary上海师范大学计算机系Grant select,update oncourse to mary把查询表和修改学生学号的权限授予用户且他能将此权限转授他人5student mary,Grant select,updatesno onstudent tomary withgrant option把对表的更改权限从收回6course maryRevokeupdate oncourse from mary把第小题授予的权限收回75maryrevoke select,update snoonstudentfrommarycascade只能查询号课程的学生成绩,请问如何授权8mary1001Create viewvscl sno,eno,grade asSelectsno,eno,grade fromsc whereeno=’0001Grant selecton vscltomary思考语句的区别.1sp_addlogin,sp_grantdbaccess如有个人需要授权，如何简化授权机制2200SQL-SERVER试验十存储过程目的掌握存储过程的概念、编程及使用编写一个存储过程向客户端返回每个系科的学生平均年龄1usp.avgage,系科平均年龄JSJ21SX20编写存储过程的代码1Create procedureusp avgageasSelect sdept,avgsage from student groupbysdept调试、运行该存储过程2Usp_avgage编写一个存储过程传入一个系科代码，返回该系的平均年龄，人数2usp_sdept,Create procedureusp_sdept@dept char10asSelect avgsage,count*from studentwheresdept=@dept编写存储过程传入参数为课程号，处理逻辑3usp_updateGrade,对传入的这门课,进行如下处理如某学生该门课成绩〉则加分80,2如某学生该门课成绩〉则加分60,1如某学生该门课成绩〈二，则减分601并且返回此门课的每个学生的最新成绩学号成绩.Create procedureusp_updateGrade@cno char4asUpdate scset grade=grade+2where cno=@cno andgrade80Update scset grade=grade+1where cno=@cno andgrade between60and80Update scset grade=grade-1wherecno=@cno andgrade=80Selectsno,grade fromscwherecno=@cno return编写存储过程比较学生的年龄的高低，输出学生的年龄大5usp_comp_age,0001,0002XXXX注意为学生的姓名XXXXCreate procedureusp compage asdeclare@agel int,@age2int一临时存储两个人的姓名declare@namel char10,@name2char10select@agel=sage,@namel=snamefrom studentwhere sno=0001select@age2=sage,@name2=sname fromstudentwhere sno=0002if@agel@age2学生的年龄大print@name1+else，学生的年龄大print@name2+return编写存储过程比较两个学生的年龄的高低，两个学生的学号有参数输入，最后输出7usp_comp_agel,学生的年龄大XXXX注意为学生的姓名XXXXCreate procedureusp_comp_agel@nol char6,@no2char6asdeclare@agel int,@age2int一临时存储两个人的姓名declare@namel char10,@name2char10select@agel=sage,@namel=snamefrom studentwhere sno=@nol select@age2=sage,@name2=sname fromstudentwhere sno=@no2if@agel@age2学生的年龄大print©name1+else，学生的年龄大print@name2+return编写存储过程比较两个学生的年龄的高低，两个学生的学号有参数输入,最后把年龄10usp_comp_age2,大的学生的姓名、性别返回客户端Create procedureusp compagel@no2char6,@no2char6asdeclare@agel int,@age2int一临时存储两个人的姓名declare@namel char10,@name2char10select@agel=sage,@namel=snamefrom studentwhere sno=@nolselect@age2=sage,@name2=snamefrom studentwhere sno=@no2if@agel@age2select sname,ssex from studentwhere sno=@nolelseselect sname,ssex from studentwhere sno=@no2编写存储过程传入参数为学号，把该学号的课程的成绩减到分每次只能减分，return12usp_tl,1001581用循环完成create procedureusp_tl@no char6asdeclare@age intset@age=100while@age58BEGINSELECT@age=sagefrom studentwhere sno=@noIf@age58Update sage=sage-1where sno=@noEND0001100278RETURN~以下不需要编写存储过程传入参数为课程号，处理逻辑返回每个学生的成绩等级成绩〉二为优，4usp_disp,90成绩＞为良，成绩＞二为中，成绩〉二为及格，成绩＜二为不及格=80706060返回结果如下学号课程号成绩等第一读出游标第一行数据优0001100191create procedureudpdisp@cno char4as一建立临时表存储结果create table#tmp sno char4,eno char4,grade int,level一建立某门课程的游标char6declare curl cursor forselectsno,grade fromsc wherecno=@cnodeclare@snochar4,@nGrade int一临时存储某学生的成绩等级declare©sLevel char6open curlfetch next from curl into@sno,@nGrade while@@fetchstatus=0begin一处理一行数据if@nGrade=90values@sno,@cno,@nGrade,@sLevel优set@sLevel=良else if@nGrade=80set@sLevel=中else if@nGrade=70set@sLevel=else if@nGrade=80，及格，set@sLevel=else不及格set@sLevel=一把结果写入临时表insert into#tmpsno,eno,grade,level--读出游标下一行数据fetch nextfrom curlinto@sno,©nGradeend closecurl deallocatecurl--返回结果给客户端select*from#tmp一删除临时表drop table#tmpreturn编写一个存储过程，传入参数为学号，执行后，把该学号的学生按如下格式输出成绩:注意只有一5行学号姓名课程课程课程平均分100110021003编写一个存储过程，传入参数为系科，执行后，把该系科的学生按如下格式输出学生成绩:6学号姓名课程课程课程平均分100110021003create procedureusp_grade@dept char15ascreate table#tmp snochar4,sname char10,gl int null,g2int null,g3intnull,pj intnull declare@nochar4,@name char10,@nGl int,@nG2int,@nG3int declarecurlcursorfor--游标某一个系的学生selectsno,snamefrom studentwheresdept=@deptopen curl、fetch nextfrom curlinto@no,@name while@@fetch statusbeginselect@nGl=gradefromscwhere sno=@no andeno=’100Tselect@nG2=gradefromscwhereselectsno=@no andeno=1002sno二@no andeno=’1003,insert into#tmpsno,sname,gl,g2,g3,pj values@nG3=gradefromscwhere@no,©name,@nGl,@nG2,@nG3,@nGl+@nG2+@nG3/3fetchnextfromcurlinto@no,@name endclosecurldeallocate curlselect*from#tmpdrop table#tmp一执行编写存储过程，统计男女生71001,1002,各自的选修人数，输出格式如下:1003性别人数人数人数小计100110021003男35210女2417合计59317usp_gradeJSJ，数据为示意数据create procedureusp_tj ascreate table#tmp ssexchar2,rsl int,rs2int,rs3int,xj intdeclare@nRsl int,@nRs2int,@nRs3intselect@nRsl=count*from student,scwhereeno=’ioor and ssex=男二select@nRs2count*from student,scwhereeno=1002and ssex=男select@nRs3=count*from student,sc insert into whereeno=1003and ssex=男#tmpssex,rsl,rs2,rs3,xjvalues男,@nRsl,@nRs2,@nRs3,@nRsl+@nRs2+@nRs3select@nRsl=count*from student,scwhereeno=ioor andssex=女二’select@nRs2count*from student,scwherecno=1002andssex=女’select@nRs3=count*from student,sc insert into whereeno=1003andssex=女，#tmpssex,rsl,rs2,rs3,xjvalues女，@nRsl,@nRs2,@nRs3,@nRsl+@nRs2+@nRs3select*from#tmp droptable#tmp return编写一个存储过程，利用存储过程的参数返回数据库服务器上的日期时间8思考何时需要存储过程试验十二触发器目的了解触发器的机制及编程设计、使用一建立学生表的触发器当增加学生时，系的学生不能超过岁usp_addstudent,SX30写出触发器1执行下列语句块2begin traninsert intostudent sno,sname,ssex,sage,sdept，‘刘欢男，values0701，，26J SXif@@error=0commitelserollbackend观察该学生是否加入到student执行下列语句块3begin tran赵欢，男insert intostudent sno,sname,ssex,sage,sdept values0702,,31,‘SXif@@error=0commitelserollbackend观察该学生是否加入到student二实现下列触发器不能删除年龄大于岁的学生记录125create triggerutr_studentl onstudentfor deleteas--存储被删除的大于岁的人数declare@nCnt int25select@nCnt=count*from deletedwheresage25if@nCnt0begin不能删除大于岁的学生;raiserror2516,10rollback transactionend--测试男-不能被删除insert intostudent values8701,aal;,27,JSJ男--能删除insert intostudent valuesC8702bb1,,24，JSTselect*from studentwhere snoin870178702delete from studentwhere sno=8701select*from studentwhere snoin8701787021delete from studentwhere sno-8702建立触发器使课程表中三门课不会被删除注意如何调试2usp delcourse,1001,1002,1003create triggerutrdeleteCourse oncourse fordelete asdeclare@nCnt intselect@nCnt=count*from deletedwhereenoin1001，1002,1003if@nCnt0begin不能删除raiserror16,10rollback transactionendreturn调试一不会被删除Delete fromcoursewhereeno=1001Delete fromcoursewhereeno=1006一能被删除对学生表建立一触发器，使更改后的年龄只能比原值大3create triggerutr_student_updatel onstudent forupdate asif notupdatesagereturndeclare@nCnt intselect@nCnt=count*from inserted,deletedwhere deleted.sno=inserted snoand inserted.sagedeleted.sage if@nCnt0begin更改后的年龄比原值小了raiserror16,10rollback transactionend对表建立触发器，使「系的学生不可选择号课程4sc JS1004,create triggerutr_choose onsc forinsert as一存储被删除的大于岁的人数declare@nCnt int25select@nCnt=count*from inserted,studentwhere student.sno=inserted snoandsdept=JSJ andinserted.eno=1004存储命令添加的数据如--inserted insert0001,1004,90if@nCnt0beginraiserror JSJ不可选择1004,,16,10rollback transactionend一测试男，insertintostudent values8701,‘aal,‘27,‘JSJ--可以insertintosc sno,eno,grade values8701,1001,90--不可以insertintosc sno,eno,grade values87011004,90select*fromscwhere sno=870T对表建触发器，实现级联删除的功能，但某课选修人数大于则不能删除5course3先删除表对的外码sc course*三建立一个触发器，使对表成绩的修改自动记录修改日志sc日志文件表记录如下tablog用户名学号课程号原成绩修改后成绩更改日期四在数据库中建立一个试验用的发票表然后为发票建立触发器实现当输入单School bill,bill utr_money,价和数量后，自动填写金额，即发票金额不输入，由单价、数量相乘后自动填写到金额中Create tablebill一发票编号billlD char8,一开票日期date datetime,一product char10,产品编号一单价price qtycharge--数量primary keybillid--金额思考触发器中表的作用？在触发器中如没有用到此两个表中的任何一个,你认为inserted,deleted触发器还有意义吗？试验十二恢复技术目的掌握数据库的备份及恢复的方法1了解备份方案的设定2一完全备份的建立与恢复建立完全备份1USE schoolGOBACKUPDATABASE school TO DISK=C:\schooldata.bak查看备份文件中的信息2RESTORE FILELISTONLYFROM DISK=c:\schooldata.bakRESTORE HEADERONLYFROM DISK=c:\schooldata.bak恢复完全备份3）先删除数据库1SchoolUSE MasterGODROPDATABASE school）然后恢复.2RESTORE DATABASE school from DISK=c:\schooldata.bak）查看的中的数据3:school student二建立差异备份建立备份1）制作数据文件备份1schoolDiff.bak）把学号姓名王海，性别男，年龄为的学生加入27001,23student）制作的差异备份，存入3school schoolDiff.bakBACKUP DATABASE school TODISK=schoolDiff.bak WITH DIFFERENTIAL）把学号姓名赵燕，性别女,年龄为的学生加入47002,22student）制作的差异备份，存入5school schoolDiff.bakBACKUP DATABASE schoolTODISK^^choolDiff.bak9WITHDIFFERENTIAL查看备份文件中的信息2schoolDiff.bak删除数据库3school恢复数据库到第步状态4school2RESTORE DATABASEschool from DISK=c:\schoolDiff.bak WITH file=l NORECOVERYRESTORE DATABASEschool fromDISK=,c:\schoolDiff.bak WITH file=2Select*fromstudent观察数据student恢复数据库到最新状态5school第一部分企业管理器的使用4试验一注册服务器错误!未定义书签试验二创建数据库错误!未定义书签试验三创建表错误!未定义书签实验四数据输入错误!未定义书签实验五登录到数据库服务器错误!未定义书签第二部分语言SQL4第二部分语言SQL4试验一数据库创建4试验二创建表4试验三创建数据完整性6试验四数据完整性试验8试验五索引12试验六更新数据14试验七查询语句Sql15试验八视图18试验九安全性控制实验19试验十存储过程20试验十二触发器25试验十二恢复技术29试验十三事务30试验十四锁32RESTOREDATABASEschool fromDISK=c:\schoolDiff.bak,WITHfile=l NORECOVERYRESTOREDATABASEschoolfromDISK=c:\schoolDiff.bak,WITHfile=3Select*fromstudent观察数据student思考如果仅执行下述恢复语句,能查看的数据吗？studentRESTORE DATABASEschoolfromDISK=c:\schoolDiff.bak WITHfile=l NORECOVERYSelect*fromstudent三利用日志备份设置故障还原模型为完全1建立备份2）制作数据文件备份1schooldatal.bak）把学号姓名王江，性别男，年龄为的学生加入27003,23student）制作日志备份存入3school log.bak）把学号姓名赵兰，性别女,年龄为的学生加入47004,22student）制作日志备份存入5school log.bak观察中的信息3school log删除数据库4school利用及恢复数据库到最新状态5schooldatal.bak schoollog.bak school四使用企业管理器练习备份调度策略对数据库每天上午时进行一次数据库完全备份1school8对数据库的每隔分钟备份进行一次差异备份2school1手工启动两个备份作业3删除数据库4school利用步的备份进行的恢复51,2school思考如何把备份文件备份到另外一台计算机上五使用企业管理器练习数据库的分离及附加六如何清除日志文件七使用企业管理器练习数据库的压缩八把备份到其他计算机上school试验十三事务目的掌握并理解事务1一理解rollback在查询分析器输入下列语句并执行，记录该学生的年龄1Select*fromstudentwhere sno=’000120执行下列语句序列2A:BEGIN TRANsactionUpdate student set sage=sage+l where sno=,0001Select*fromstudentwhere sno=0002此事务结束了吗？答没有执行3Select*fromstudentwhere sno=’0001记录该学生的年龄思考中的的年龄确实被更改了吗？为什么student000121更改了，因为在事务中执行了语句update4执行下列语句ROLLBACK TRANsaction然后再执行Select*fromstudentwhere sno=0001,观察的年龄，解释发生这种现象的原因000120放弃了事务中所有已执行的语句Rollback二理解commit在查询分析器输入下列语句并执行，记录该学生的年龄1Select*fromstudentwheresno=000120执行下列语句序列2A:BEGIN TRANsactionUpdatestudent setsage=sage+l wheresno=’0001Select*fromstudentwheresno=0002执行3commit transactionSelect*fromstudentwheresno=’0001记录结果，此时更改后的数据被永久保存了吗21永久保存三执行下列语句序列BEGIN TRANsaction二Updatestudentsetsagesage+1wheresno=0001Update scset grade=grade+1wheresno=0002andeno=1001Rollback上述指令执行后，数据库发生了什么变化？没有试验十四锁目的理解锁的概念及锁的作用1一利用帮助系统了解的下列语句的含义Sql-server锁的隔离级别1SET TRANSACTIONISOLATION LEVELSerializable设置锁定超时时间2SET LOCK_TIMEOUT50003SP_LOCK二观察封锁执行语句序列1ABEGIN TRANsactionUpdatestudentsetsage=sage+l wheresno=0001Select*fromstudentwheresno=0002在查询分析器中打开第二个连接（连接）［文件-连接］,输入下列语句:2school1select*fromstudentwheresno=0002记录执行结果，说明原因能看到结果，因为在未结束事务中对的学生加了锁，其他事务还可以对其加student0002SS锁2）select*fromstudentwheresno=0001记录执行结果，说明原因（如上一步没有停止，则强行终止）不能看到结果，出于等待状态因为在未结束事务中对的学生加了锁，其他student0001X事务不可以对其加任何锁3）update studentset sname=,aaa wheresno=0002记录执行结果，说明原因（如上一步没有停止，则强行终止）不能看到结果，，出于等待状态因为在未结束事务中对的学生加了锁，其student0002S他事务不可以对其加锁X）强行终止上一步的命令，然后执行语句:4DBCC opentran记录结果，思考如何知道此事务是那一台计算机发出的？）执行记录执行结果，说明原因等待5select*fromstudentwheresno=0001然后回到第一个连接中，执行语句commit Tran观察并记录第二个连接窗口中的现象，说明原因学生的信息马上被显示出来，因为结束了事务，则其加的锁被释放，因此等待的0001commit操作可以枷锁成功了三了解锁的类型执行下列语句1BEGIN TRANSelect*fromstudentwheresno=’0001（）Print serverprocess IDspid:Print@@spid）然后执行下列语句1exec sp_lock注意根据事务中输出的,观察结果中相应的记录，观察加锁spid spid）然后执行下列语句2commit tranexec sp_lock注意根据事务中输出的,观察结果中相应的记录，观察加锁spid spid执行下列语句2BEGIN TRANUpdatestudentsetsage=sage+1wheresno=,1001Print serverprocess ID（spid）:‘Print@@spid）然后执行下列语句1exec sp_lock注意根据事务中输出的,观察结果中相应的记录，观察加锁spid spid）然后执行下列语句2commit tranexec splock注意根据事务中输出的,观察结果中相应的记录，观察加锁spid spid使用然后重新执行三和四步，观察与原来有3SET TRANSACTIONISOLATION LEVELSerializable何不同了解表级锁查看帮助文件4BEGIN TRANSelect*fromstudentTABLOCKX wheresno=1002Print ServerProcess IDspid:*Print@@spid然后执行execsp_lock注意根据事务中输出的,观察结果中相应的记录，观察加锁类型spid spid了解锁定超时5）执行下列语句，设置锁定超时为a1000msset lock_timeout1000goBEGIN TRANSelect*fromstudent（TABLOCKX）wheresno=,1002）打开第二个连接2执行select*fromstudent记录观察到的现象）在打开的第二个连接中3set lock_timeout10000goselect*fromstudent记录观察到的现象五编程实例编写存储过程传入参数为课程号，处理逻辑:对传入的这门课,进行如下处理1usp_updatel,如某学生该门课成绩》则加分80,2如某学生该门课成绩》则加分60,1如某学生该门课成绩＜二，扣分601要求在存储过程中，要么全部学生的成绩被处理成功，要么全部不处理思考在调试中，采用那些措施，使存储过程运行时执行回滚操作（rollback）编写触发器，2对、语句进行监控，当学生的年龄超过岁时，把该学生的系科改为同时删除该insert update40BAK1学生的所有选课记录（注意，利用事务，使修改系科及删除选课记录要么全做，要么全不做）第一部分企业管理器的使用第二部分语言SQL试验一数据库创建目的掌握利用语言进行数据库的创建、维护1SQL命令2sp_helpdb要求创建数据库修改数据库删除数据库123—建立school数据库使用查询分析器创建数据库1school CreateDataBase school使用查询数据库的信息2SP_helpdb School使用的企业管理器查看数据库的信息3SQL-Server school记录4数据库文件所在的文件夹1school数据库的文件名2school二删除School数据库使用查询分析器删除数据库1school DROPDATABASEschool使用的企业管理器删除数据库2SQL-Server schoolo三create Database深入研究建立数据库，要求数据库存储在文件夹下，初始大小为，增量为1school c:\data5MB1MB CREATEODATABASEschool0NName=school_dat,Filename=c:\sqldata\school.mdf,SIZE=5,FILEGROWTH=1使用的企业管理器，将数据库的每次增量改为2SQL-Server20%试验二创建表目的掌握利用语言创建表的方法1SQL命令2sp_help要求创建表修改表结构删除表123一写出使用Create Table语句创建表student,sc,course的SQL语句学生表、课程表、选课表属于数据库,其各自得数据结构如下School学生Student Sno,Sname,Ssex,Sage,Sdept序号列名含义数据类型长度学号字符型1Sno char6姓名字符型2Sname varchar8性别字符型3Ssex char2年龄整数4Sage smallint系科字符型5sdept varchar15课程表courseCno,Cname,Cpno,Ccredit序号列名含义数据类型长度课程号字符型1Cno char4课程名字符型2cname varchar20先修课字符型3Cpno char4短整数4Ccredit学分tinyint学生选课SCSno,Cno,Grade序号列名含义数据类型长度学号字符型1Sno char4课程名字符型2Cno char6成绩小数3Grade decimal12,1—把创建表的sqI语句的脚本存储到文件schooI.sqI0create table Student Sno char6,Sname char10,Ssex char2,Sage smallint,Sdept char10,create table courseCno char4,Cname char16,Cpno char4,Ccredit int,createtableSCSno char6,Cno char4,Grade int三使用SP.HELP查看表student的表结构利用企业管理器查看表sc的表结构U!利用sql语句表结构修改在表中添加列:1student家庭地址长度为型address60varchar入学日期日期型inDateALTER TABLE student ADDaddress varchar60ALTER TABLEstudent ADDinDate datetime完成后用查看是否成功sp_help将家庭地址长度为2address50ALTER TABLEstudent ALTERCOLUMN varchar50完成后用查看是否成功sp_help删除表的列3student inDateALTER TABLEstudentDROP COLUMNinDate五删除表删除表1sc删除表2student删除表3course试验三创建数据完整性目的掌握创建数据完整性约束的命令1掌握完整性约束的修改、删除2要求能建立完整性约束修改完整性约束删除完整性约束123一写出带有完整性约束的Create Table命令建立表student、course、sc要求:表的主码1Student sno的约束student•姓名不可为空，且唯一•性别不能为空且取值范围为｛男，女｝•年龄大于岁16•默认为系sdept YST表的主码2Course eno的约束course•取值范围｛｝Ccredit0,123,4,5•课程表的每一行的与不可相同Cno cpno表的主码主码名为3Sc sno,cno PK_SC的外码Sc•外码表的参照表的SC snostudentsno•外码表的参照表的sc Cnocoursecno把上述创建表的语句的脚本存储到文件4sql createSchool.sql createtableStudentSnochar6,Sname char10not nullunique,男二女,Ssex char2check ssex=or ssexSagesmallint checksage16,Sdept char10not nulldefault JST,primaiy key sno createtablecourseCno char4,Cname char16,Cpno char4,Ccredit intcheck Ccredit=0and Ccredit=5,〉约束check cnocpno,primaiy keycnocreate tableSCSno char6,Cnochar4,Grade intcheckgrade=100,constraint pk_sc primarykeysno,cno,foreign keysno referencesstudentsno,foreign keycno referencescoursecno,使用SP.HELP查看表student的主码名约束名，并记录使用查SP_HELP看表的主码名，外码名，并记录sc三利用alter table添加、删除完整性约束删除的主码，表的主码名为1SC scpk_scALTER TABLEsc DROPpk_sc复习在企业管理器中如何完成Sql-Server删除表参照表的外码2SC course如何知道表参照表的外码的名字SC course找到外码名，假设为1SP_HELP coursefk_sc_cno_029382ALTER TABLEsc DROPfk_sc_cno_02938添加表的主码主码名为3SC PK_SCALTER TABLEsc ADDCONSTRAINT pk_sc PRIMARYKEY sno,cno添加表的的外码，参照表的4SC CnoCourse Cno.加自定义约束ALTERTABLEsc ADDFOREIGN KEYcno REFERENCEScoursecno5表的成绩只能在分之间SC0-100使用Sql-Server企业管理器完成:UiALTER TABLEsc ADDcheckgrade=0and graded00删除表参照表的外码1SC course建立表的的外码，参照表的2SC CnoCourse Cno.*使该外码具有级联修改的功能删除表的成绩只能在分之间的约束.3SC0-100加自定义约束表的成绩只能在分之间3SC0-100五使用查看信息一查看信息select fromstudent查看信息select*from courseselect*fromsc试验四数据完整性试验目的理解实体完整性、参照完整性、用户自定义完整性的作用特别掌握外码的作用12要求记录试验中遇到的问题，并写出原因实验前需要利用试验三完成的脚本文件重新建立数据库createSchool.sqL schoolo-实体完整性表数据输入1student学号姓名性别年龄系科3001赵达男20SX3002杨丽女21JSJ3001李寅女21SX的主码为因此列的值不能重复Student sno,sno查看你输入了几行数据•select*fromstudent一输入上述数据，记录出现的问题，说明原因第三行不能输入表数据的输入2courseCno CnameCpno Ccredit1081电子商务4表数据的输入3SCSno CnoGrade30011081903001108179输入上述数据，记录出现的问题，说明原因二用户自定义完整性约束表有用户自定义约束student性别不能为空且取值范围为｛男，女｝年龄大于岁16表的自定义约束course取值范围｛｝Ccredit032,3,4,5课程表的每一行的与不可相同Cno cpno表数据输入1student学号姓名性别年龄系科赵达男300514SX3006杨丽南21JSJ输入上述数据，记录出现的问题，说明原因•学生不能输入，年龄问题，有约束3005sage16学生不能输入，性别不对3006查看你输入了那些数据•select*fromstudent表数据的输入2courseCno CnameCpno Ccredit1085C++91086语文10863•输入上述数据，记录出现的问题，说明原因不能输入，因为有约束1086checkcnoocpno查看你输入了那些数据•select*fromstudent•输入上述数据，记录出现的问题，说明原因这条数据不能输入，因为不能大于分3002grade100查看你输入了那些数据•select*fromstudent。