一個(gè)完整的數(shù)據(jù)庫(kù)示例

1、表的結(jié)構(gòu)及完整性約束新建一個(gè)數(shù)據(jù)庫(kù)jxsk,包括S C、SC T、TC五個(gè)表，結(jié)構(gòu)如下:C表:Id dbo.c列？Eueno (PK, charfS), not null) cn (chartW), not null) 闔 ct (tinyintr null) f epno (FKd char(5)r null) iTl ccredit (tinyintd null)為束B LJm 匚K_c_cc red it_2 76E DE B3 向 CK_c_ct_286302E匚S表:dbo.sE； 口列/ sno (PK, ldnum(char(6J, not null) 圍 sn (char(8)

2、, not null)D sex (charfSJj null)1 age (tinyintj nullJ dept (charflOL null匕CJ釣束口 CK_s_age_267ABA7A_z resume (text, null)J native (char(201, null；SC表:日 dboc日 的束HI C K_sc_s co re_1 B FD 2C 075no PK, FK.:drum char(6R rot null ? eno (PK, FK, char5), not null 3 score (tmyint, null)T 表:日313 列日丄釣東J CK_Oge_2

3、A4B4S5EJ CK_t_comm_2C3393DOj CK_t_5al_2B3F6F97f tno (PKd Id num(charefill not null) ?! tn (char(8)p not null) sex (chr(2). null)2 age (tinyint, null)SI prof (char10, nulln sal (smallint, null)_1 comm (smallint, null；71 dept ichar(lO), null)TC表:dbo.tc日M” tno (PKj FK, Id num(charfb)! not null) ? eno (

4、PKr FKj char(5), not null)!1 evaluation (char(20), null)二、安全性控制及視圖機(jī)制1、三類角色： depart、teacher、studentdepart的權(quán)限：姻FT角色JE性depart- 幫肋* fi'FEn廿詼用邑戟田）去幻揀坦aifti£»-14售齒吞岸笹EE件址雄teacher的權(quán)限:3| Jboa dbo£口 dbo二I時(shí)tfee逵加燉1.出琮垃;協(xié)RAlt«fControldboIa54rt血Qdbodho丁*屯(nrarxLi pdbaUpdAtAdboS'a

5、71;v&j kj gadbaIW團(tuán)用櫃予具有槓予.柜迪" .- > u V5 3 廉走取聘翼普-rr雲(yún)音淳*MiiJV9PIkjttrChatred.1+1444Iniart lri-hfMi4*lStlliTt l4ca ahij> tfpduATa*w iJitanilj an<h回墮回圖口口口氓專審邑:性-tclwr |1血t 4U血麗甬邑綁F；UP 竝褲：tbuhirffllE：1適越炯1«知山* « J!JtWffl IE：1Wt:當(dāng)student的權(quán)限:姻屋抱色*性-iBdent號(hào)楓上匡空I口j* ir.Rxtt計(jì)血色宕特i

6、jti.z” 口 口 Mana融時(shí)算用tai1 «乩CFs"春1 *»fa14新劇用翼） M<Ct>*帀日邀式1?規(guī)：衣囲frfs*oiklttrdb;Cwir*ld亦DdbiJb?XmarE-Jl-3uilliSk!£-TtJsv- HUTcljjdbVH<tt4h¥i*r 以岀!iddh2、有2個(gè)院系用戶：d_jsj, d_xx，同屬于depart角色。送掃頁(yè)r銀蘭用EM扌 蘭妄全悶倉(cāng)口口口射到出超丸甜舸戶瞋射針桶障用戶lhA雖構(gòu)口Z0| jask<Jii»wt«r*C-JjkL 3-ltRaG&

7、gt;eoll!5ib,>afc,Rttw tSarritfTHjJBTTtMpcLO已曰用伽估轄?wèi)鬸rtk曲JlWfl!吹W悄炯jsiikdadidlalj. M_iuuMnDUAyuniltt：XL有1個(gè)教師用戶：t，屬于teacher角色。* Dwjh>基科冒出合抵屯曲ffl戶也)wtjBPii訕衲血匚比t匚 AEtMT屈“BlrflhLtfiir tarririMir 已dFVdrTaaipnETvieIife-Mjl3鈣-彌* 律一*1n；rt:x:4j'r= g «J:1Euhl應(yīng)InhFln 爲(wèi)s包E E 4*.孔*此孔視皿3HanEifa|J音比卓

8、1J£tn有一個(gè)學(xué)生用戶：s,屬于student角色。| | -it.dhtu-BHdjir .dhEhiricir| <L_JmifJbEbj-baX+-刖一Easr| | Jb_Eir«ri kxfli-an3*49住浙£険-(0毗A即針氓圧抽鋤RM巳碼jEftffAJ：1 H4.F=VAfn|IEIflF由口 網(wǎng)w五訃E9134Fm44，叩盯 凸 5= “ r (t<r » 囂 TfJHITbBkt泄un ax屆沁li *h：m3、創(chuàng)建計(jì)算機(jī)系教師視圖t_viewsj、計(jì)算機(jī)系學(xué)生視圖s_view_jsj,并授予d_jsj 用戶在這兩個(gè)

9、視圖上的 select、delete、update、insert權(quán)限。計(jì)算機(jī)系教師視圖t_view_jsj：create view t_view_jsjasselect tno , tn , sex , age , prof , sal , comm, dept from twhere dept ='計(jì)算機(jī) with check opti on 授予 d_jsj 用戶在計(jì)算機(jī)系教師視圖 t_view_jsj 上的 select、delete、update、insert 權(quán)限：grant select , update , delete , insert on t_view_jsj to

10、 d_jsj 計(jì)算機(jī)系學(xué)生視圖 t_view_jsj：create view s_view_jsjasselect sno , sn, sex , age , dept , resume , nativefrom swhere dept =' 計(jì)算機(jī) 'with check option授予d_jsj用戶在計(jì)算機(jī)系學(xué)生視圖 s_view_jsj上的select delete、update、insert 權(quán)限：grant select , update , delete , insert on s_view_jsj to d_jsj4、創(chuàng)建一個(gè)視圖，顯示學(xué)號(hào) ,姓名,院系,課程名

11、, 成績(jī)。create view score_view (學(xué)號(hào), 姓名, 院系, 課程名, 成績(jī)) asselect s . sno , sn, dept , cn, scorefrom s , sc , cwhere s . sno =sc . sno and sc . cno =c. cno三、完整性控制 -觸發(fā)器、規(guī)則1、要求當(dāng)刪除C表中某課程信息時(shí)，同時(shí)刪除SC和TC中與此課程相關(guān)的記錄。 create trigger c_delete_trigger on cafter deleteasdelete from scwhere cno in( select cno from delet

12、ed )delete from tcwhere cno in( select cno from deleted )go2、為 T 創(chuàng)建一觸發(fā)器，當(dāng)職稱從“講師”晉升為“副教授”時(shí)，崗位津貼自動(dòng) 增加 500 元，從“副教授”晉升為“教授”時(shí)，崗位津貼自動(dòng)增加900 元。create trigger t_update_trigger on tafter updateasif update ( prof )begindeclare prof_old char ( 10), prof_new char ( 10) select prof_old =prof from deleted select p

13、rof_new =prof from insertedif prof_old=' 講師' and prof_new =' 副教授 'update t set comm=comm+500 where tno =( select tno from inserted )if prof_old=' 副教授 ' and prof_new =' 教授 'update t set comm=comm+900 where tno =( select tno from inserted )end 3、創(chuàng)建一個(gè)規(guī)則 sexrule ，指定變量 sex

14、 的取值只能為 ' 男' 或' 女' create rule sexruleas sex in( ' 男' , ' 女 ' )綁定 T 表的 sex 、S 表的 sex 到 sexrule 規(guī)則： exec sp_bindrule'sexrule', 's.sex'exec sp_bindrule'sexrule', 't.sex'四、索引自動(dòng)創(chuàng)建聚集索引1、索引的分類：聚集索引： primary key非聚集索引2、使用索引的準(zhǔn)則： 1）適合建索引的屬性列主碼所在的

15、屬性列外碼所在的列或在連接查詢中經(jīng)常使用的屬性列按關(guān)鍵字的范圍值進(jìn)行搜索的屬性列按關(guān)鍵字的排序順序訪問的屬性列2）不適合建索引的屬性列在查詢中很少涉及的屬性列包含較少的唯一值更新性能比查詢性能更重要的屬性列有text、ntext 、image數(shù)據(jù)類型定義的屬性列3、為s表在dept屬性列上創(chuàng)建索引createindex s_dept_indexon s （dept ）3 口壽引爲(wèi) PK_s_7E6CC92O 陵集）曲s_dept_index込唯一耳藻集）五、自定義數(shù)據(jù)類型、自定義函數(shù)1、自定義數(shù)據(jù)類型Idnum：學(xué)號(hào)、教師編號(hào)都是 char( 6), not null，exec sp_addt

16、ype Idnum , 'char(6)'，not nuII' 2、自定一個(gè)標(biāo)量函數(shù)，用于查詢某個(gè)同學(xué)某門課程的成績(jī)。create functionscore_fun( sname char ( 8), cname char (10)retur nstinyintasbeg indeclare cj tinyintselect cj =scorefrom s , sc , cwhere s . sno =sc . sno and c . eno =sc . eno andsn =s name and cn =c namereturn cj end使用該函數(shù)，查詢 

17、9;李忘'選修'計(jì)算機(jī)基礎(chǔ) '的成績(jī)。select dbo . score_fun ( ' 李忘 ' , ' 計(jì)算機(jī)基礎(chǔ) ' )3、創(chuàng)建一個(gè)單語(yǔ)句表值函數(shù)。create function cname_score ( cname char ( 10) returns tableasreturn( select sn , score from s , sc , c where s . sno =sc . sno and sc . cno =c. cno and cn =cname) go使用該函數(shù)，查詢選修了“計(jì)算機(jī)基礎(chǔ)”的學(xué)生姓名、成績(jī)。s

18、elect sn 姓名 , score 成績(jī)from cname_score ( ' 計(jì)算機(jī)基礎(chǔ) ' )4、創(chuàng)建一個(gè)多語(yǔ)句表值函數(shù)，根據(jù)教師姓名查詢?cè)摻處熕v授課程名、學(xué)生人 end數(shù)、平均成績(jī)、最高成績(jī)、最低成績(jī)。 create returns numerictname_fun ( tname char ( 10)table ( cnfunction tname_score_tab ( 6, 1), zgf numeric ( 6, 1), zdf numericchar ( 10), rsint , pjf( 6, 1)asbegininsert into tname_sc

19、ore_tabselect cn , count (*), avg ( score from sc , tc , t , cwhere sc . cno =c. cno and t . tno and tn =tnamegroup by cnreturn), max( score=tc . tno and), min ( score )c . cno =tc . cno使用該函數(shù)，查詢徐紅霞'教師所授課信息。select * from tname_fun ( ' 徐紅霞 ' )六、存儲(chǔ)過程和游標(biāo)1、利用課程名查詢選修該課程的學(xué)生姓名、系別、成績(jī)。 create proc

20、edure cn_score_procname char ( 10) asselect sn 姓名 , dept 系別, score 成績(jī)from s , sc , cwhere s . sno =sc . sno and sc . cno =c. cno and cn =cname go使用該存儲(chǔ)過程，查詢選修“計(jì)算機(jī)基礎(chǔ)”的相關(guān)信息。exec cn_score_pro ' 計(jì)算機(jī)基礎(chǔ) '2、統(tǒng)計(jì)某門課程成績(jī)?yōu)?0以下，6080 (含60、80分)，80以上各有多少人 create procedure cn_tjscore_procname char ( 10), n1 in

21、t output , n2 int output , n3 int output asdeclare cj tinyintselect n1=0, n2=0, n3=0declare c cursor forselect scorefrom sc , cwhere sc . cno=c. cno and cn =cnameopen cfetch next from c into cjwhile fetch_status =0beginif cj <60set n1 = n1+ 1elseif cj <=80set n2=n2+1elseset n3 =n3+1fetch next

22、from c into cjendclose cdeallocate cgo使用該存儲(chǔ)過程，查看“計(jì)算機(jī)基礎(chǔ)”的相關(guān)成績(jī)統(tǒng)計(jì)信息。 declare n1 tinyint , n2 tinyint , n3 tinyint exec cn_tjscore_pro ' 計(jì)算機(jī)基礎(chǔ) ' , n1 output , n2 output , n3 outputselect n1 '0-60 人數(shù)' , n2 '60-80 人數(shù)' , n3 '80-100 人數(shù) '3、利用課程名查詢選修該課程的學(xué)生姓名、系別、成績(jī)。 (使用輸出參數(shù)) 創(chuàng)建存

23、儲(chǔ)過程create procedure tn_scoretj_cursor_procname char ( 10), c cursor varying output asset c = cursor forselect sn , dept , score from sc , s, cwhere sc . cno =c. cno and sc . sno =s. sno and cn =cname open cgo 使用該存儲(chǔ)過程，查看“計(jì)算機(jī)基礎(chǔ)”的相關(guān)信息 declare myc cursor , sn char ( 10), dept char ( 10), score tinyint-

24、調(diào)用過程 tn_scoretj_cursor_proexec tn_scoretj_cursor_pro ' 計(jì)算機(jī)基礎(chǔ) ' , myc output- 打印表頭print ' 姓名 系別成績(jī) 'fetch next from myc into sn, dept , score while fetch_status =0beginprint sn + dept +' ' +cast ( score as char ( 5) fetch next from myc into sn, dept , score endclose myc dealloc

25、ate myc七、事務(wù)設(shè)計(jì)1、創(chuàng)建一個(gè)事務(wù)程序，要求：學(xué)生“王蕾”打算選修“圖像處理”課程，根據(jù) 規(guī)定，此門課程選修的人數(shù)最多為 30 人，該生是否可以選修此門課程，給出結(jié) 果提示。begin transactiondeclare num tinyint , cno char ( 5), sno char ( 6)select cno =cno from c where cn =' 圖像處理 'select sno =sno from s where sn =' 王蕾 'select num=count (*) from sc where cno =cnoif num<30begininsert into sc ( sno , cno ) values ( sno, cno)commitprint ' 王蕾同學(xué)選修圖像處理課程注冊(cè)成功！ 'endelsebeginrollback transactionprint ' 選修圖像處理課程的人數(shù)已滿，王蕾同學(xué)不能再選修此課程 ' endgo2、將教師“王平”講授的“計(jì)算機(jī)網(wǎng)絡(luò) ”轉(zhuǎn)給“徐紅霞”