數據庫原理與技術》上機實驗安排實驗1SQLserver2000的安裝和常用工具的使用【實驗內容】練習安裝SQLSERVER2000及卸載SQLSERVER2000。（注意WINDOWS登錄模式和混合登錄模式的區別）在企業管理器中創建一個新的SQLServer服務器組，作為MicrosoftSQLServers的子對象，名稱為“NewGroup”在計算機上再安裝一個SQLServer命名實例，實例名為“people”。使用企業管理器在NewGroup服務器組下創建一個新的SQLServer注冊，使它連接people實例.實驗內容】c.實驗2數據庫及表的創建和管理1、使用企業管理器創建名為student的數據庫，并設置數據庫主文件名為syudent_data，大小為10MB;日志文件為student」og，大小為2MB.其他參數取默認值。步驟：ad.使用Transact—SQL語言創建一個studentl數據庫，主文件邏輯名為studentl_data，物理文件名為studentl.mdf,為10MB,增長速度為10%;數據庫的日志文件邏輯名為syudent1」og,物理文件名為studentl.ldf,初始化大小為1MB,最大尺寸為5MB，增長速度為1MB；文件存放路徑為c:\data文件夾下。Createdatabasestudent1Onprimry(name=student1_data,filename=c:\data\student1.mdf,size=10,maxsize=unlimited,filegrowth=10%)logon(name=student1_log,filename=c:\data\student1.ldf,size=1,maxsize=5,filegrowth=1)在student數據庫中創建一個名為pupil的表，要求：(snochar(6)notnull,snamechar(10)notnull,ssexchar(2)notnull,birthdaydatetimenotnull,politychar(20))步驟：++++-+~|—1—1—1. s-:[±l-:[±]-:[±]-:?一muaeimsdbNorthwindpubsStud已「it垢關系圖ter新連表⑹…所有任務（幻查看（町從這里創連窗口（凹幫助凹Hjsysfilesl詞sysforeigrikeys詞sysfulltextcatalogs詞sysfulltextriutify詞sysindexes詞sysinde:：■::keyssetsTiissicinsjerties:setssrences3Srsdbodbodbodbodbodbodbodbodbodbodbodbodbodbo丟統丟統丟統丟統系統系統系統系統垂統垂統垂統垂統垂統@1簾1葩警邙融|卻孕叵］列名數據類型長度允許空Enocihar6ErL：dinechar10eeexchar2hirthdaydatetimm8?politychar20*b.c.保存并命名為pupil即可。4.將pupil表的sno設置為主鍵。為sname字段設置惟一性約束。步驟：a1&U髦禺丨簾|葩警⑷融丨埠孕叵］列名數據類型長度允許空Enu char6設置主鍵（笛插入列（密刪除列（蟲ar10ar2t旦tim旦8ar20任務（D ?全選兇保存⑸素引腿兇…關系』旦）...匚HE□（約束㈣…雇性回廠

6.為ssex和birthday設置檢查性約束，要求ssex只能為'男'或'女'，birthday應該大于‘1987-1-1'。步驟：

7?為polity字段設置默認約束，值為'群眾'8再創建一個學生選課表sc(snochar(6)notnull,cnochar(10)notnull,gradereal).為sc表創建外鍵約束，把sc表的sno和pupil表的sno關聯起來，在這兩個表之間創建一種制約關系。步驟：

pupil+ErL：iiTieeeexbirthdaypulitySC+snudLUgrade創建關系I創建關系兇關系名迅）：|FK_sc_pupil主鍵表（1） 外鍵表（JQ）pupil EC 3STluJk兩創連中檢查現存數據匱）兩對復制強制關系0）磺對INSERT和UPDATE強制關系迢）廠級聯更新相關的字段QJ）廠級聯刪除相關的記錄0C）確定|取消|幫助?I

最后保存該關系圖即可。實驗3數據查詢及維護【實驗內容】1.已知一個名為pupil的表：(snochar(6)notnull,snamechar(10)notnull,ssexchar(2)notnull,birthdaydatetimenotnull,politychar(20),sdeptchar(20)notnull)利用insert語句向其中插入一條新的記錄：(‘0007'，‘張三'，‘男'，‘1982-3-21'，‘團員',計算機系)insertintopupilvalues(‘0007'‘張三'‘男'‘1982-3-21'‘團員'‘計算機系')b.利用update語句將編號為'0004，的學生polity改為‘黨員'：updatepupilsetpolity=黨員wheresno=0004a.利用delete語句將‘186-1-1'以后出生的女同學記錄刪除。DeletefrompupilWherepolity>1986-1-1andssex=女2.針對pupil、sc、course三張表完成以下查詢：a.查詢所有學生的基本信息，并按出生日期升序排列select*frompupilorderbypolity查詢女同學的信息和女同學的人數select*frompupilwheressex=女computecount(sno)查詢所有男同學的年齡selectyear(getdate())-year(birthday)as年齡frompupil所有選課學生的姓名、選修課程名及成績selectsname,cname,gradefrompupil,sc,coursewherepupil.sno=o=o不及格學生的姓名selectdistinctsnamefrompupil,sc,coursewherepupil.sno=o=oandgrade<60查詢‘劉'姓學生的信息select*frompupilwheresnamelike?劉%g.查詢polity為團員或黨員的學生信息select*frompupilwherepolityin(?團員,黨員)查詢各門課程的選課人數selectcno,count(sno)fromscgroupbycno查詢缺少成績的學生的學號及課程號selectsno,cnofromscwheregradeisnull查詢與‘劉成'同一個系的學生情況select*frompupilwheresdeptin(selectsdeptfrompupilwheresname=劉成))查詢選修了課程名為‘MIS'的學生的學號和姓名selectsno,snamefrompupilwheresnoin(selectsnofromscwherecnoin(selectcnofromcoursewnerecname=MIS))實驗4Transact—SQL程序設計實驗內容】1? 編寫一個用戶自定義函數fun_avggrade，要求根據輸入的班級號和課程號，求此班級此課程的平均分。Createfunctionfun_avggrade(@classnumberaschar(10),@coursenumberaschar(10))returnsrealbegindeclare@avgresultselect@avgresult=avg(grade)fromscwheresubstring(sno,1,len(sno)-2)=@classnumberandcno=@coursenumberreturn@avgresultend編寫一個用戶自定義函數,完成以下功能：根據兩個輸入參數(成績上限和成績下限)，求sc數據表中滿足輸入條件的學生人數。Createfunctionfun_sumren(@maxlinereal,@minlinereal)returnsintbegindeclare@personcountasintselect@personcount=(selectcount(distinctsno)fromsc)fromscwheregrade<=@maxlinerealandgrade>=@minlinereturn@personcountend3.如何通過企業管理器和Transact_SQL語句對視圖進行創建、修改和刪除？通過企業管理器：創建步驟：扁SQLServerEnterpriseManager-「全一人‘沖（在％loel）"上）的新視國]裔文件（E）窗口㈤幫助（出盤⑥|西置匝置!徐野歐醫％運行?添加表同…全選過分爼回顯示宙格?隱藏窗格（蟲保存⑸另存為辿J-LI 管理索引①…列JI性（③輸出排序類型 1排序順!SELECTFROM'血文件（日窗口（創幫助?|[nns?■?微學歌醫％@pupil*（所有列）￡IWsrL：iJTiH1/birthhy列別名表輸出排序類型ErLupupilErL：diTiHpupilbirthdaypupi17SELECTsno.,sn^ie,birthd：ayFROMdbu.pupil最后保存該視圖即可。修改步驟：進入該界面重新修改該視圖即可。刪除步驟：通過Transact_SQL語句：創建：createview視圖名[withencryption]asselect_statement[withcheckoption]修改：利用alterview視圖名刪除：利用dropview視圖名4、通過Transact_SQL語句創建一個視圖，計算各個班級的各門課程的平均分。Createviewv_avggradeAsSelectsubstring(pupil.sno,1,len(pupil.sno)-2)as班級，Cnameas課程名稱，Avg(grade)as平均分Frompupil,sc,gradeWherepupil.sno=o=oGroupbysubstring(pupil.sno,1,len(pupil.sno)-2),Cname5.通過Transact_SQL語句創建一個視圖，顯示‘高等數學'未過的學生的信息。Createviewv_gradenotpassAsSelectpupil.sno,sname,cname,gradeFrompupil,sc,courseWherepupil.sno=o=oandgrade<60Andcname=高等數學6.通過Transact_SQL語句創建一個視圖,查詢的數據為99521班學生的考試成績。Createviewv_grade99521AsSelectpupil.sno,sname,cname,gradeFrompupil,sc,courseWherepupil.sno=o=oandleft(pupil.sno,5)=99521實驗5存貯過程實驗內容】創建一個存儲過程stugradeinfo,查詢班級、學號、姓名、性另I」、課程名稱、分數createprocedurestugradeinfoasselect班級=substring(pupil.sno,l,len(pupil.sno)-2),pupil.snoas學號，snameas姓名，ssexas性另，cnameas課程名稱,gradeas分數frompupil,sc,coursewherepupil.sno=o=o利用企業管理器創建一個存儲過程stu_info,根據傳入的編號,查詢某學生的基本信息。白?豳數據庫+白?豳數據庫+jyt+master+model+msdb+Northwind+pubs+tempdb-金一人垢關泵圖二|表_|控制臺根目錄-每］Microsoft5QLServers-d5QLServer組-令(local)(WindowsNT)窗文件（日操作迦查看泅工具①窗口（創幫助（出X曹血鳧1包:、Q^B?ra視圖3于項目名稱i^cTstuviewl陽sysconstraints知syssegments新逢存體過程（3…從這里創逹窗口（翌）刷新（日幫助凹ia數據嬴嘉

代碼如下：createprocedurestu_info@stunumchar(10)asifnotexists(select*frompupilwheresno=@stunum)print?查無此人！！！！！，elsebeginselectsnoas編號，snameas姓名，ssexas性別，birthdayas出生日期，polityas政治面貌frompupilwheresno=@stunumend3.創建一個存儲過程stu_age，根據輸入的學生姓名，計算該學生的年齡。Createprocedurestu_age@stunamechar(10)asifnotexists(select*frompupilwheresname=@stuname)print?查無此人！！！！！，elsebegindeclare@ageintselect@age=floor(datediff(day,birthday,getdate())/365)frompupilwheresname=@stunameend4.創建一個存儲過程stu_grade,根據輸入的學生編號，返回其選課及其成績。Createprocedurestu_grade@stunumchar(10)ifnotexists(select*frompupilwheresname=@stuname)print?查無此人！！！！！，elsebeginifnotexists(select*frompupil,scwherepupil.sno=sc.sno)print?無此學生的選課信息!!!！9elseselectsname,cname,gradefrompupil,course,scwherepupil.sno=sc.snoandcourse=cno=oend實驗6觸發器實驗內容】1．創建一個insert觸發器,當在pupil表中插入一條新記錄時給出‘你已經插入了一條新記錄！！！'的提示信息。步驟：話文件（巳操作過查看伯工具①窗口輕幫助（出匕*世］110黑噩XB1 1-F1 -8?控制臺根目錄Microsoft5QLServers白電5QL5已件已F《目日Jq（local）（WindowsNT）

日口數據庫+圜+鞫+觀+謝+觀+圜+謝-圜表29個項目.iyrmasterrnodelrnsdbNorthwindpubsternpdb金一人一”|表鉗視圖幻存儲過程屢用戶產角色J規則二I默認用戶定殳的數攬用戶定義的函藝+_|數據轉換服務+_|管理名稱］所有者1類型］創建日期§1匚ours已dbo用戶2006-4-271圉dtpropertiesdbo系統2006-4-271dbo用戶2006-4-271ZJscZJsyZJsy二印二即ZJsyZJsy二印二即ZJsyZJsyZJsy二印設計表（勺打開表（◎全文索引表（巳?所有任務（燈剪切⑴復制Q刪除◎重命名畫幫助凹二sysp已rmb引口「莊ZJsysprop已fti巳￡ZJsysprotects圉sysreferencesr=adbodbodbodbodbo用戶系統系統系統管理索引（血…管理融發器（D…管理權限?…導入數據①…導出數據⑥…新建發布㈣…生成SQL腳本⑥…顯不相關性IS），..dbodbodbo系統系統系統2006-4-2712000-8-61:2000-8-61：2000-8-61：2000-3-61：2000-8-61：2000-8-61：2000-8-61：2000-3-61：2000-0-61：2000-8-61：2000-8-61：2000-8-61：2000-3-61：2000-0-61：2000-8-61：2000-8-61：2000-8-61：程序代碼如下：createtriggerstuinsertonpupilforinsertasdeclare@msgchar(50)set@msg=你已經插入了一條新記錄！！！print@msg2.創建一個insert觸發器,當在pupil表中插入一條新記錄時，不允許在學號中出現重復的編號或出現空值。程序代碼如下：createtriggerstuinsert1onpupilforinsertasdeclare@stunumchar(10)set@stunum=(selectsnofrominserted)if@stunumisnullbeginprint?學號不能為空！！！！rollbacktransactionendelsebegindeclare@numintset@num=(selectcount(*)frompupilwheresnoin(selectsnofrominserted))if@num>1beginprint?已經有相同的學號，請重新確認！！！！，rollbacktransactionendelseprint?數據錄入成功！！！！，end3.創建一個insert觸發器,當在sc表中插入一條新記錄時，sno和cno必須是已經存在的學號和課程號，且grade應該在0 100之間。程序代碼如下：createtriggerscinsertonscforinsertasdeclare@gradevaluerealset@gradevalue=(selectgradefrominserted)print?觸發器開始工作 ’ifnotexists(selectsnofrominsertedwheresnoin(selectsnofrompupil))beginprint?無此學生的編號，請重新確認！！！，rollbacktransactionendelsebeginifnotexists(selectcnofrominsertedwherecnoin(selectcnofromcourse))beginprint?無此課程的編號，請重新確認!!!9rollbacktransactionendelsebeginif@gradevalue<0or@gradevalue>100beginprint?分數有誤，請重新確認！！！！9rollbacktransactionendelseprint?數據插入成功！！！！endend4.創建一個after觸發器,在pupil表中刪除某學生的記錄時，刪除其相應的選課記錄。程序代碼如下：createtriggerstudeleteonpupilfordeleteasdeclare@stunumchar(10)select@stunum=snofromdeletedifnotexists(select*frompupil,scwherepupil.sno=sc.snoandpupil.sno=@stunum)print?無此學生的選課記錄!!!!9elsebeginprint?開始查找并刪除該學生相映的選課記錄 ‘deletefromscwheresno=@stunumprint?該學生的選課記錄已經刪除！！！！！9end5.創建一個insteadof觸發器，當在course表中上出記錄時，不允許刪除course表中的數據。程序代碼如下：createtriggernotallowdeleteoncourseinsteadofdeleteasprint?insteadof觸發器開始工作 9print?course表中的數據不允許刪除！!!不能執行刪除操作！!9實驗7數據庫的備份及恢復、導入和導出【實驗內容】Q峠1匡］函X會囤Q峠1匡］函X會囤島1金^1:、0^8?C3備份0亍項目名稱二控制臺棍目錄I物理位置口…埴0Microsoft5QLServers白號5QLServer組白??旳(local)(WindowsNT)日口繆據庫+閡jyr+協master+閡model+協msdb+燼Northwind+閡pubs+tempdb-燼金一人對關系閣二|表彌觀圖各存儲過程屋用戶產角色J規則二默認r用戶定岌的數揑毎用戶定交的函婪+」數據轉換服務電5QLServer代理猥制黠如猥制黠如聯U復安支Me+J-m E-.:[±1-:.[±1-:E1-備份數據庫(創…查看㈣從這里創建窗口(世刷新㈢導出列表Q…幫助⑹n2．使用企業管理器對一數據庫做一次完全數據備份。3．使用企業管理器恢復某一數據庫的完全數據備份表新逹數據庫（因…新逹（阻備份數據庫（因…所有任務（幻還原數據庫（E）…錐護計ME）...生成5QL腳本⑥…導入數據①…導出數據同…查看伯從這里創連窗口（也刪除側刷新㈢導出列表◎，，曲1觀團jyr10于項目_|控制臺根目錄-￡]JMicrosoft5QLServers-u5QLServer組-(local)(WindowsNT)"-_J表新逹數據庫（因…新逹（阻備份數據庫（因…所有任務（幻還原數據庫（E）…錐護計ME）...生成5QL腳本⑥…導入數據①…導出數據同…查看伯從這里創連窗口（也刪除側刷新㈢導出列表◎，，曲1觀團jyr10于項目_|控制臺根目錄-￡]JMicrosoft5QLServers-u5QLServer組-(local)(WindowsNT)"-_J數據庫+闕a+樹+樹+B+樹+樹+B-樹X圖闌園|鍛丨靠I宀①超（B烏雇性?幫助?二1規則二I默認r用尸定爻的數拒毎用戶定殳的函輕+_j數據轉換服務-_j管理+5QLServer代理1 7［i匕整沁收縮數據庫（囚…分離數據庫（出…脫機回復制訂閱數據庫（?…查看復制沖突（也…2SJ3332J還原:広數據庫也〕要還原的第1于備份程］:厘性曰I取消|幫助|確定還原數齬庫竝數顯示數據庫備份辺〕：2006-5-916:02:40-jyr備份 !r文件組或文件舊 r從設備膽］常規|選項丨置還原為數據庫（H〕：還原類型備份集日期尢小還原自備份集名稱02006-5-91...913KBC:\dat...iyr備份廠時點還原［Q］：4.導入一個文本文件到SQLServer數據庫當中。」控制臺根目錄一￡]JMicrosoft5QLServers-u5QLServer組-??吊(local)(WindowsNT)

"-」數據庫-jyr昭關系圖—I坤t7 新逹表(或??表25于項目名稱dtpropertiespupilsys匚cilumri^sys匚匚imm已詞^v^rlfipendsgroups所有任務（幻導入數據①…查看（與從這里創逹窗口（翌）導出數據⑥… LItextcatalogs c刷新（巳導出列