SQLServer試驗(yàn)三分解

1、實(shí)驗(yàn)七(1)創(chuàng)建并運(yùn)行存儲(chǔ)過程 student_grade ,要求實(shí)現(xiàn)如下功能：查詢 studb數(shù)據(jù)庫中每 個(gè)學(xué)生各門課的成績，其中包括每個(gè)學(xué)生的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運(yùn)行結(jié)果代碼：use Studb go execute student_grade gosnocname筮01日1數(shù)據(jù)率原理6620709010103程就計(jì)H幗配683O7D5mO1C5侏家具計(jì)苴機(jī)網(wǎng)格的0705010106郝宙計(jì)直機(jī)網(wǎng)絡(luò)NULLcr?050noios郝旭東軟件工程S66(POST 0115任盾板離的數(shù)學(xué)so70705010119微機(jī)原理88B7050101125朱牌律機(jī)翩7790709010127胡玉滓國的繳學(xué)76100705®10127胡玉萍瞰機(jī)原理061110705010131劃招否計(jì)苴機(jī)差礎(chǔ)戲12

3、0709m 0131劉腕C*記序設(shè)計(jì)88120705(10 0121劉相哲離色韻字84140709010131劉指a數(shù)據(jù)結(jié)構(gòu)國1507050101131劉磁獨(dú)作親場76IB705010131刈招哲渤機(jī)翩博170709010131劉相百圖寵學(xué)SS1B0705010131劉磁金需限原理出IBD7D5010131劃招否計(jì)苴機(jī)網(wǎng)珞eo3D07050101131劉楠軟件工程00JI0705010215國擇數(shù)將結(jié)科MULL220705010215息拴數(shù)據(jù)庫原理卻230709010226計(jì)宜機(jī)基礎(chǔ)k240705010303刈高盛除柞系茄75(2)創(chuàng)建并運(yùn)行名為proc_exp的存儲(chǔ)過程，要求實(shí)現(xiàn)如下功能：從

4、 student_course 表中查詢某一學(xué)生考試的平均成績。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運(yùn)行結(jié)果代碼:use Studbgoexecute proc_expsname = '劉招香go尸結(jié)果| Ea消息I.更疣列名(3)修改存儲(chǔ)過程pr

5、oc_exp ,要求實(shí)現(xiàn)如下功能：輸入學(xué)生學(xué)號(hào)，根據(jù)該學(xué)生所選課程 的平均成績給出提示信息，即如果平均成績在60分以上，顯示“成績合格，成績?yōu)閄X分”，否則顯示“成績不合格，成績?yōu)?XX分”；然后調(diào)用存儲(chǔ)過程proc_exp ,輸入學(xué)號(hào)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 '成績合格，成績?yōu)?avg+'分 else print '成績不合格，成績?yōu)?avg + '分 運(yùn)行結(jié)果代碼： use Studb go declare student_sno varchar (20) select student_sno ='0705010131' exec proc_exp student_sno3消息成績合格,成績?yōu)椋旱姆?4)創(chuàng)建名為proc_add 的存儲(chǔ)過程，要求實(shí)現(xiàn)以下功能：向 student_course 表中添加 學(xué)生記錄；然后調(diào)用存儲(chǔ)過程proc_add ,向student_co

7、urse表中添加學(xué)生成績記錄。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運(yùn)行結(jié)果代碼：use studb goexec pr

8、oc_add '0705010102', '0208', '80'go執(zhí)行前: -*/ 322-59.h astudb 一 冊我 EludesnocnoSCOTS07X)50103020212購07D501D1030 101期0705010105030165O7D5O1D1O60301Q705Q101P60302郭07050101190206so7050101190210se0705010125021U7707Q501D127020676070501012702106607D501D131Q1ID17607050101310102ee07D501

9、D131020684070501013102oe6807D501D1310209760705010131021078070501D13102116070501DJ31Q2123401705010131030J6007050101310302SO(J7O5O1O21502MWIL07D501D21502129 口07050102260101620705010303020975來的AHL執(zhí)行后:/江一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)刪除存儲(chǔ)過程 proc_exp 和proc_addIF OBJECT_ID ( 'proc_exp') IS NOT NULLDROP PROCEDURE proc_expIF OBJECT_ID ( 'proc_add') IS NOT NULLDROP PROCEDURE proc_add實(shí)驗(yàn)八(1)創(chuàng)建觸發(fā)器student_trg,當(dāng)刪除st

12、udent表中的數(shù)據(jù)時(shí)，同時(shí)刪除 student_course表中相同 的數(shù)據(jù)；然后通過刪除student表中的某個(gè)學(xué)生記錄來驗(yàn)證該觸發(fā)器。create trigger student_trg on student after deleteas begin delete from student_course where sno IN( select sno from deleted ) end運(yùn)行結(jié)果代碼：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信計(jì)聚0607107U5QL0103程明龍男1933-03-12網(wǎng)絡(luò)系皿07207Q50L0105。黑男L933-05-ZL確系如0717010106和電布男LW33S-I3信計(jì)系oe071D7O5QLO119任煒祝男L-04-IZ計(jì)苴機(jī)系99U737U50L0125耨男L939-D8-0B計(jì)埠機(jī)系9gD7O5DLO127捐玉萍女1939-01-12計(jì)苴機(jī)系D97107CM5DLO13I劉招香女i

14、gaa-ii-oa計(jì)苴機(jī)系的071口心口皿】S*男1顆Tll白啕系帕0710706010226高玉骷男l(wèi)$8a-D-Z3計(jì)苴機(jī)系英0720706010303 劉周龍男183-06-li計(jì)苴機(jī)感的072出上期上他皿1他出11?既-59m2-4b v.另 I dent |1. r ttcir1B7)* 3EJ-59.5tul. efi4_corse*S810aery4 sC55)+snosnanebirthdaydept取口dassroc卜LCOEulOlOll金譯更值計(jì)系060710705010103辱颯另198-0342網(wǎng)珞系090720705010105陡京具S19BM5-Z1網(wǎng)招系W071

15、0709310106和祖京男19SS-11=13信計(jì)系06Q71O70S0I01I9任植權(quán)男1966-04-12計(jì)豆機(jī)浜的073O7DEOH0125本臻男igBMB-DS過苴機(jī)茶用073070SQID127胡玉萍烹19094)142計(jì)篁機(jī)耒0'710705010131*195-11-IS討豆機(jī)系旭07107100010215禺拄男19呢”lb網(wǎng)絡(luò)系典071D70S0l'Ce26高王網(wǎng)用19諭1023計(jì)算機(jī)鬃072*AL81MSI械士(2)修改觸發(fā)器 student_trg ,當(dāng)更新 student 表中sno 的值時(shí)，同時(shí)更新student_course 表中相同的sno的值；

16、然后通過修改 student表中的某個(gè)學(xué)生的學(xué)號(hào)(sno )來驗(yàn)證該觸發(fā)器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運(yùn)行結(jié)果代碼：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刪除觸發(fā)

20、器student_trgDROP TRIGGER student_trg創(chuàng)建一個(gè)新的觸發(fā)器，要求實(shí)現(xiàn)“計(jì)算機(jī)系的學(xué)生選課不能超過三門”這一完整性約束，并驗(yàn)證該觸發(fā)器。CREATE TRIGGER student_cho ON student_course AFTER INSERTASBEGINIF EXISTS ( SELECT sno FROM inserted WHERE sno in (SELECT sno FROM student WHEREdept ='計(jì)算機(jī)系')AND ( SELECT COUNT(*) FROMstudent_courseWHEREstudent

21、_course . sno =inserted . sno )> 3)BEGINPRINT '計(jì)算機(jī)系的學(xué)生選課不能超過三門ROLLBACK TRANSACTION ENDEND8678888468767886848080實(shí)驗(yàn)九(1)利用游標(biāo)逐行顯示所查詢的數(shù)據(jù)塊的內(nèi)容：在student表中定義一個(gè)包含sno、sname>sex和dept的只讀游標(biāo)，游標(biāo)名為c_cursor,并將游標(biāo)中的數(shù)據(jù)逐條顯示出來。1 .在數(shù)據(jù)庫引擎上查詢文檔中輸入如下代碼：declare c_cursor scroll cursor forselect sno , sname , sex , dep

22、t from student for read onlyopen c_cursorfetch from c_cursor2 .單擊執(zhí)行snosname sex dept11 0705010102 i金舉 再 信計(jì)系3 .接著讀取游標(biāo)中的第二行，在查詢編輯器重輸入如下語句:fetch from c_cursor4 .連續(xù)單擊“執(zhí)行”按鈕，就可以逐條顯示記錄 e ,，Jsnosname sex dept1 i 0705010103 i程明龍男網(wǎng)絡(luò)系 l，e rmF m r，e r，e r，e r F國結(jié)果;3消息$nosname sex depti； 6而時(shí)而而口徐家興男網(wǎng)絡(luò)系"fa &

23、gt; Bl Id »& IJalBIUIBIlUialK旦結(jié)果肖息snosname sex dept1Q元麗而謔；郝坦東 里 信計(jì)系lkllBIBIlalBllfllBIMIBIIBIBIMIBIUIM#而結(jié)果力消息snosnane sex deptJIBIKlIBIUIBIUIBIMIBIIdllllllJIlUialU1 g 0705010113 ：任博權(quán) 男 計(jì)算機(jī)系 、r ，e，廣，ri ran r«i I e r I e r I e r i e T5.最后關(guān)閉游標(biāo)、釋放游標(biāo)close c_cursor必消息命令已成功完成。(2) 利用游標(biāo)顯示指定行的數(shù)據(jù)

24、的內(nèi)容：在studen就中定義一個(gè)所在系為“計(jì)算機(jī)系”包含sno、sname> sex、和dept的游標(biāo)，游標(biāo)名為 c_cursor,完成如下操作： declare c_cursor scroll cursorforselect sno , sname , sex , deptfrom student where dept ='計(jì)算機(jī)系 for read onlyopen c_cursor1 .讀取第一行數(shù)據(jù)，并輸出； fetch first from c_cursor國結(jié)果I-$疝snosrame sex depti百6而行*河q任鎮(zhèn)板里計(jì)算機(jī)系 、m I e 1 e fe m

25、 m rei m m m m E2 .讀取最后一行數(shù)據(jù)，并輸出； fetch last from c_cursor結(jié)果_s消息snosname sex dept1叵型>6儂高玉昭 臭 計(jì)算機(jī)系3 .讀取當(dāng)前行的前一行數(shù)據(jù)，并輸出； fetch prior from c_cursor里結(jié)果消息 sncsname sex d 印t1 rb70501013rj劃招番女計(jì)算機(jī)系4 .讀取從游標(biāo)開始的第三行數(shù)據(jù)，并輸出。 fetch absolute 3 from c_cursor|回結(jié)果IW標(biāo)sncsname sex dept1而6壇而萬：胡玉萍女計(jì)算機(jī)系 S-iraiM'iraiB&#

26、39;iniiBiriHirimaiB'iraiBiri'（3） 利用游標(biāo)修改指定的數(shù)據(jù)元組：在student表中定義一個(gè)所在系為“計(jì)算機(jī)系”，一個(gè)包含sno、sname> sex、和dept的游標(biāo)，游標(biāo)名為 c_cursor,將游標(biāo)中絕對位置為 3的 學(xué)生姓名改為“胡平”，性別改為“男”。declare c_cursor scroll cursor for select sno , sname , sex , dept from student where dept ='計(jì)算機(jī)系' 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國結(jié)果島消息 snosname sex dept1 r07DK）-i0127-'l胡平 臭計(jì)算機(jī)系（4） 編寫一個(gè)使用游標(biāo)的存儲(chǔ)過程并查看運(yùn)行結(jié)果，要求該存儲(chǔ)過程以課程名（ cname）和系（dept）作為輸入?yún)?shù)，計(jì)算指定系的學(xué)生指定課程的成績分布情況，要求分別輸出大于90, 8089, 7079, 6069和60分以下的學(xué)生人數(shù)。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