存儲(chǔ)過(guò)程、觸發(fā)器和用戶自定義函數(shù)存儲(chǔ)過(guò)程試驗(yàn)

1、存儲(chǔ)過(guò)程、觸發(fā)器和用戶自定義函數(shù)實(shí)驗(yàn)實(shí)驗(yàn)內(nèi)容一練習(xí)教材中存儲(chǔ)過(guò)程、觸發(fā)器和用戶自定義函數(shù)的例子。教材中的BookSales數(shù)據(jù)庫(kù)，在群共享中，文件名為BookSales.bak。實(shí)驗(yàn)內(nèi)容二針對(duì)附件1中的教學(xué)活動(dòng)數(shù)據(jù)庫(kù)，完成下面的實(shí)驗(yàn)內(nèi)容。1、存儲(chǔ)過(guò)程(1)創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，該存儲(chǔ)過(guò)程統(tǒng)計(jì)“高等數(shù)學(xué)”的成績(jī)分布情況，即按照各分?jǐn)?shù)段統(tǒng)計(jì)人數(shù)。CREATEProcMATH_NUMMATHCHAR(20)=高等數(shù)學(xué)ASSELECTMATHascanme,count(casewhenscore=90then1end)as90以上,count(casewhenscore=80andscore=70ands

2、core=60andscore70then1end)as60-70,count(casewhenscore=90andscore=80andscore=70andscore=60andscore70thenDwhenscore=15andage=35),sexvarchar(20)check(sexin(男,女)-性別snovarchar(20)PRIMARYKEYnotnull,snamevarchar(20)notnull,-姓名李強(qiáng),20,男）劉麗，21,男）；張兵,20,男）；陳志堅(jiān),22,男）;王穎，21,男）；)insertintostudentvalues(98601,inser

3、tintostudentvalues(98602,insertintostudentvalues(98603,insertintostudentvalues(98604,insertintostudentvalues(98605,學(xué)生表的記錄snosnameagesex98601李強(qiáng)20男98602劉麗21女98603張兵20男98604陳志堅(jiān)22男98605王穎21女(2)課程表(course)課程表的結(jié)構(gòu)列名數(shù)據(jù)類型長(zhǎng)度是否允許為空值說(shuō)明cnochar4NO課程號(hào)cnamechar20NO課程名teacherchar8任課教師說(shuō)明：cno為主鍵。createtablecourse(cnoc

4、har(20)notnullprimarykey,-課程編號(hào)cnamechar(20)notnull,-課程名稱teacherchar(20)insertintocoursevalues(C601,高等數(shù)學(xué),周振興)insertintocoursevalues(C602,數(shù)據(jù)結(jié)構(gòu),劉建平)insertintocoursevalues(C603,操作系統(tǒng),劉建平)insertintocoursevalues(C604,編譯原理,王志偉)課程表的記錄cnocnameteacherC601高等數(shù)學(xué)周振興C602數(shù)據(jù)結(jié)構(gòu)劉建平C603操作系統(tǒng)劉建平C604編譯原理王志偉(3)選課表(study)cre

5、atetablestudy(snovarchar(20)notnull,-學(xué)生學(xué)號(hào)cnochar(20),-上課編號(hào)scoresmallint,primarykey(sno,cno),foreignkey(sno)referencesstudent(sno),foreignkey(cno)referencescourse(cno)insertintostudyvalues(9860T,C60T,90)insertintostudyvalues(98601,C602,90)insertintostudyvalues(98601,C603,85)insertintostudyvalues(9860

6、1,C604,87)insertintostudyvalues(98602,C601,90)insertintostudyvalues(98603,C601,75)insertintostudyvalues(98603,C602,70)insertintostudyvalues(98603,C604,56)insertintostudyvalues(98604,C601,90)insertintostudyvalues(98604,C604,85)insertintostudyvalues(98605,C601,95)insertintostudyvalues(98605,C603,80)選課表的結(jié)構(gòu)列名數(shù)據(jù)類型長(zhǎng)度是否允許為空值說(shuō)明snochar5NO學(xué)號(hào)cnochar4NO課程號(hào)scoresmallint成績(jī)說(shuō)明：sno和cno為主鍵，sno為外鍵（參照student表的sno）,cno為外鍵（參照course表的cno）,score的范圍為0100之間。選課表的記錄snocnoscore986019860198