SQLServer试验三分解

1、实验七(1)创建并运行存储过程 student_grade ,要求实现如下功能：查询 studb数据库中每 个学生各门课的成绩，其中包括每个学生的sno、sname、cname和score 。create procedure student_grade as select student . sno , student . sname , course . cname , student_course . score from student join student_course on student . sno =student_course . sno join course on co

2、urse . cno =student_course . cno运行结果代码：use Studb go execute student_grade gosnocname筮01日1数据率原理6620709010103程就计H帼配683O7D5mO1C5侏家具计苴机网格的0705010106郝宙计直机网络NULLcr?050noios郝旭东软件工程S66(POST 0115任盾板离的数学so70705010119微机原理88B7050101125朱牌律机翩7790709010127胡玉滓国的缴学76100705®10127胡玉萍瞰机原理061110705010131划招否计苴机差础戏12

3、0709m 0131刘腕C*记序设计88120705(10 0121刘相哲离色韵字84140709010131刘指a数据结构国1507050101131刘磁独作亲场76IB705010131刈招哲渤机翩博170709010131刘相百图宠学SS1B0705010131刘磁金需限原理出IBD7D5010131划招否计苴机网珞eo3D07050101131刘楠软件工程00JI0705010215国择数将结科MULL220705010215息拴数据库原理却230709010226计宜机基础k240705010303刈高盛除柞系茄75(2)创建并运行名为proc_exp的存储过程，要求实现如下功能：从

4、 student_course 表中查询某一学生考试的平均成绩。create procedure proc_exp sname varchar (8) as begin select sname , AVG( score ) from student join student_course on student . sno =student_course . sno where sname =sname group by sname end运行结果代码:use Studbgoexecute proc_expsname = '刘招香go尸结果| Ea消息I.更疣列名(3)修改存储过程pr

5、oc_exp ,要求实现如下功能：输入学生学号，根据该学生所选课程 的平均成绩给出提示信息，即如果平均成绩在60分以上，显示“成绩合格，成绩为XX分”，否则显示“成绩不合格，成绩为 XX分”；然后调用存储过程proc_exp ,输入学号0705010131,显示成绩是否合格。alter procedure proc_exp student_sno varchar (20) as declare avg varchar ( 20) set avg =( select AVG( score ) from student_course where sno = student_sno )if avg

6、>= 60 print '成绩合格，成绩为+avg+'分 else print '成绩不合格，成绩为+avg + '分 运行结果代码： use Studb go declare student_sno varchar (20) select student_sno ='0705010131' exec proc_exp student_sno3消息成绩合格,成绩为：的分(4)创建名为proc_add 的存储过程，要求实现以下功能：向 student_course 表中添加 学生记录；然后调用存储过程proc_add ,向student_co

7、urse表中添加学生成绩记录。create procedure proc_addsno char (10),cno char (10),score tinyintas begin set nocount on if not exists(select * from student_course where sno =sno and cno =cno and score = score ) insert into student_course (sno , cno , score ) values ( sno, cno, score ) end运行结果代码：use studb goexec pr

8、oc_add '0705010102', '0208', '80'go执行前: -*/ 322-59.h astudb 一 册我 EludesnocnoSCOTS07X)50103020212购07D501D1030 101期0705010105030165O7D5O1D1O60301Q705Q101P60302郭07050101190206so7050101190210se0705010125021U7707Q501D127020676070501012702106607D501D131Q1ID17607050101310102ee07D501

9、D131020684070501013102oe6807D501D1310209760705010131021078070501D13102116070501DJ31Q2123401705010131030J6007050101310302SO(J7O5O1O21502MWIL07D501D21502129 口07050102260101620705010303020975来的AHL执行后:/江一AH. stud. B s udcDt_c>oiLrs<r EWru. £t<Ldt> - dbo. vtudj*sno50scoreD70S0101020206羽

10、nD70ED1010202126607000101030101S30706OI01O503016507090101060301ALfiD70501D106030295070501011902068007050101190210朋0705010125021077070601012702067&07050I&127Q之IQ就07050I01310IQ170D70S0101310102SB070S01D1310206840705010131020666D7050iai310Z097&D7(H)1D13102107B07QE010131021186070501013102128

11、%D70S01Q131030180D70501D13103028Q0706010215Q2Q6必上0709010215021290070501022601016ZD705010S03020975米fyui帆Z1W(5)删除存储过程 proc_exp 和proc_addIF OBJECT_ID ( 'proc_exp') IS NOT NULLDROP PROCEDURE proc_expIF OBJECT_ID ( 'proc_add') IS NOT NULLDROP PROCEDURE proc_add实验八(1)创建触发器student_trg,当删除st

12、udent表中的数据时，同时删除 student_course表中相同 的数据；然后通过删除student表中的某个学生记录来验证该触发器。create trigger student_trg on student after deleteas begin delete from student_course where sno IN( select sno from deleted ) end运行结果代码：DELETE FROM studentWHERE sno ='0705010303'SQXJQuoryB s. . rttar 占T)*322-59. stud . .如E

13、udL _ 般，/无口工微色“ SQLQuAr4. e . . rft.tar C55)>snosnamesexbkthdfydept川noCliAfWUTKOLOiOZ金漳男IW9-07-09信计聚0607107U5QL0103程明龙男1933-03-12网络系皿07207Q50L0105。黑男L933-05-ZL确系如0717010106和电布男LW33S-I3信计系oe071D7O5QLO119任炜祝男L-04-IZ计苴机系99U737U50L0125耨男L939-D8-0B计埠机系9gD7O5DLO127捐玉萍女1939-01-12计苴机系D97107CM5DLO13I刘招香女i

14、gaa-ii-oa计苴机系的071口心口皿】S*男1颗Tll白啕系帕0710706010226高玉骷男l$8a-D-Z3计苴机系英0720706010303 刘周龙男183-06-li计苴机感的072出上期上他皿1他出11?既-59m2-4b v.另 I dent |1. r ttcir1B7)* 3EJ-59.5tul. efi4_corse*S810aery4 sC55)+snosnanebirthdaydept取口dassroc卜LCOEulOlOll金译更值计系060710705010103辱飒另198-0342网珞系090720705010105陡京具S19BM5-Z1网招系W071

15、0709310106和祖京男19SS-11=13信计系06Q71O70S0I01I9任植权男1966-04-12计豆机浜的073O7DEOH0125本臻男igBMB-DS过苴机茶用073070SQID127胡玉萍烹19094)142计篁机耒0'710705010131*195-11-IS讨豆机系旭07107100010215禺拄男19呢”lb网络系典071D70S0l'Ce26高王网用19谕1023计算机鬃072*AL81MSI械士(2)修改触发器 student_trg ,当更新 student 表中sno 的值时，同时更新student_course 表中相同的sno的值；

16、然后通过修改 student表中的某个学生的学号(sno )来验证该触发器ALTER TRIGGER student_trgON student AFTER UPDATEASBEGINIF UPDATE( sno )UPDATE student_courseSET sno =( SELECT sno FROM inserted ) WHERE sno IN ( SELECT sno FROM DELETED)END运行结果代码：UPDATE studentSET sno ='0705010217'WHERE sno ='0705010215'Jj消息I<2

17、行受影响）（1行受影响）1 scuery7 z . rator C57)*322T9. studb - db©. s*GOenoscore cC705010102021266C705010103otoi88C705010105030185C7050101060301AWZC7O5O1O1O6030295C705010119020680C705010119021088C705010125021077C705010127020676(705010127021086C7O5O1O131010178C7O5O1O131010288C7OSO1O131020664C70501013102086

18、8C705010131020976C705010131021078C7050101310211%C7050101310212%C7O5O1O131030180C7O5O1O131030280C7050102150208MJLL021290C705010226OlOt62C70S010303020975*fULfULMALL-以Z-58. stud. . udent.course) SULUuery7. 3. ratorJsnoenoscoreC 0212660705010103010188070501010503018507050101060301MAL07060101060302950705

19、0101190206800706010119021088070501012502107707C6010127020676070501012702108607050101310101780705010131010288070501013102068407060101310208680705010131020976070501013!021078070501013102118607050101310212840705010131030180070501013103028007050102170208MAL07050102170212900705010226010162*MALMALMJLL删除触发

20、器student_trgDROP TRIGGER student_trg创建一个新的触发器，要求实现“计算机系的学生选课不能超过三门”这一完整性约束，并验证该触发器。CREATE TRIGGER student_cho ON student_course AFTER INSERTASBEGINIF EXISTS ( SELECT sno FROM inserted WHERE sno in (SELECT sno FROM student WHEREdept ='计算机系')AND ( SELECT COUNT(*) FROMstudent_courseWHEREstudent

21、_course . sno =inserted . sno )> 3)BEGINPRINT '计算机系的学生选课不能超过三门ROLLBACK TRANSACTION ENDEND8678888468767886848080实验九(1)利用游标逐行显示所查询的数据块的内容：在student表中定义一个包含sno、sname>sex和dept的只读游标，游标名为c_cursor,并将游标中的数据逐条显示出来。1 .在数据库引擎上查询文档中输入如下代码：declare c_cursor scroll cursor forselect sno , sname , sex , dep

22、t from student for read onlyopen c_cursorfetch from c_cursor2 .单击执行snosname sex dept11 0705010102 i金举 再 信计系3 .接着读取游标中的第二行，在查询编辑器重输入如下语句:fetch from c_cursor4 .连续单击“执行”按钮，就可以逐条显示记录 e ,，Jsnosname sex dept1 i 0705010103 i程明龙男网络系 l，e rmF m r，e r，e r，e r F国结果;3消息$nosname sex depti； 6而时而而口徐家兴男网络系"fa &

23、gt; Bl Id »& IJalBIUIBIlUialK旦结果肖息snosname sex dept1Q元丽而谑；郝坦东 里 信计系lkllBIBIlalBllfllBIMIBIIBIBIMIBIUIM#而结果力消息snosnane sex deptJIBIKlIBIUIBIUIBIMIBIIdllllllJIlUialU1 g 0705010113 ：任博权 男 计算机系 、r ，e，广，ri ran r«i I e r I e r I e r i e T5.最后关闭游标、释放游标close c_cursor必消息命令已成功完成。(2) 利用游标显示指定行的数据

24、的内容：在studen就中定义一个所在系为“计算机系”包含sno、sname> sex、和dept的游标，游标名为 c_cursor,完成如下操作： declare c_cursor scroll cursorforselect sno , sname , sex , deptfrom student where dept ='计算机系 for read onlyopen c_cursor1 .读取第一行数据，并输出； fetch first from c_cursor国结果I-$疝snosrame sex depti百6而行*河q任镇板里计算机系 、m I e 1 e fe m

25、 m rei m m m m E2 .读取最后一行数据，并输出； fetch last from c_cursor结果_s消息snosname sex dept1叵型>6侬高玉昭 臭 计算机系3 .读取当前行的前一行数据，并输出； fetch prior from c_cursor里结果消息 sncsname sex d 印t1 rb70501013rj划招番女计算机系4 .读取从游标开始的第三行数据，并输出。 fetch absolute 3 from c_cursor|回结果IW标sncsname sex dept1而6坛而万：胡玉萍女计算机系 S-iraiM'iraiB&#

26、39;iniiBiriHirimaiB'iraiBiri'（3） 利用游标修改指定的数据元组：在student表中定义一个所在系为“计算机系”，一个包含sno、sname> sex、和dept的游标，游标名为 c_cursor,将游标中绝对位置为 3的 学生姓名改为“胡平”，性别改为“男”。declare c_cursor scroll cursor for select sno , sname , sex , dept from student where dept ='计算机系' for update of sname , sexopen c_curs

27、orfetch absolute 3 from c_cursorupdate studentset sname ='胡平，sex ='男 where current of c_cursorfetch absolute 3 from c_cursor国结果岛消息 snosname sex dept1 r07DK）-i0127-'l胡平 臭计算机系（4） 编写一个使用游标的存储过程并查看运行结果，要求该存储过程以课程名（ cname）和系（dept）作为输入参数，计算指定系的学生指定课程的成绩分布情况，要求分别输出大于90, 8089, 7079, 6069和60分以下的学生人数。create procedure proc_discname varchar ( 20),asbegindeclarec_cursor cursor select count (*) less60 from student_course join student on student.sno =student_course.snojoincourse on o =student_owheredept =dept andcname =cname andscore <60