數(shù)據(jù)庫-實驗二

1、數(shù)據(jù)庫原理實驗 班級： 學號： 姓名： 實驗報告冊 2014 / 2015 學年 第2 學期系 別 計算機科學與技術系實驗課程 數(shù)據(jù)庫原理專 業(yè) 計算機科學與技術班 級 姓 名 學 號 指導教師 實驗二SQL語句一、 實驗目的1. 熟悉SQL的數(shù)據(jù)查詢語言，能使用SQL進行單表查詢、連接查詢、嵌套查詢、集合查詢和統(tǒng)計查詢，能理解空值的處理；2. 熟悉數(shù)據(jù)庫的數(shù)據(jù)更新操作，能使用SQL語句對數(shù)據(jù)庫進行數(shù)據(jù)的插入、更新、刪除操作；3. 熟悉SQL支持的有關視圖的操作，能創(chuàng)建、查詢及取消視圖；4. 了解NULL在數(shù)據(jù)庫中的特殊含義，掌握使用SQL進行與空值相關的操作；二、 實驗預習內容在認真閱讀教材

2、及實驗指導書【實驗1.2 數(shù)據(jù)查詢】、【實驗1.3 數(shù)據(jù)更新】、【實驗1.4 視圖】和【實驗1.6 空值和空集的處理】的基礎上，上機前請預習以下內容，并在空白處填寫相應的步驟或命令。1. 使用SCHOOL數(shù)據(jù)庫，在SQL SERVER 2000 的查詢分析器中使用SQL語言完成以下操作。請在空白處填寫相應的SQL命令。1） 查詢年級為2001的所有學生的名稱，按編號順序排列；2） 查詢所有課程名稱中含有data的課程編號；3） 統(tǒng)計所有老師的平均工資；4） 查詢至少選了3門課的學生編號；5） 查詢學號為80009026的學生的姓名、所選課名及成績；6） 查詢沒有學生選的課程編號；7） 查詢既選

3、了C+又選了Java課程的學生編號；8） 查詢選了C+但沒選Java課程的學生編號；9） 向STUDENTS表中插入“LiMing”的個人信息（編號：700045678，名字：LiMing，Email：LX，年級：1992）；10） 將“LiMing”的年級改為2002；11） 刪除所有選了Java課程的學生選課記錄；12） 求出每門課的課程號、選課人數(shù)，結果存入數(shù)據(jù)庫表T1中。13） 查詢所有選課記錄的成績并換算為五分制（注意NULL的情況）；14） 查詢成績小于0的選課記錄，統(tǒng)計總數(shù)、平均分、最大值和最小值（注意查詢結果中NULL的情況）；15） 按成績順序排序顯示CHOICES表中所有記

4、錄。（使用ORDER BY排序，注意NULL的情況）；16） 創(chuàng)建視圖V1，顯示學生姓名、所選課名稱、任課教師名；17） 取消V1視圖；2. 使用STC數(shù)據(jù)庫，在SQL SERVER 2000 的查詢分析器中使用SQL語言完成以下操作。請在空白處填寫相應的SQL命令或其它內容。1） 創(chuàng)建視圖V2，顯示計算機（CS）系學生信息；（結合下面的b)小題，V2視圖創(chuàng)建2次，分別使用/不使用WITH CHECK OPTION選項）（1）不加WITH CHECK OPTION（2）加WITH CHECK OPTIONcreate view V2as select * from studentswhere

5、sdept=cscreate view V2as select * from studentswhere sdept=cswith check option2） 插入元組（99999，張三，20歲，男，PH）到V2中，運行結果如何？（觀察WITH CHECK OPTION對結果的影響）；（1）不加WITH CHECK OPTION的結果（2）加WITH CHECK OPTION的結果insert into V2value(99999,張三,20,男,PH)不加with check option時，命令執(zhí)行成功。加了這個后，在執(zhí)行命令，會出現(xiàn)錯誤。原因是目標視圖或者目標視圖所跨越的某一視圖指定了

6、with check option，而該操作的一個或多個結果行又不符合check option約束的條件。3） 在V2基礎上，查詢所有計算機系女生信息；select * from V2where ssex=女 3. 結束本次實驗三、 實驗課后訓練1. 掌握SQL SERVER中流控制語句及其它常用編程語句；1） 使用查詢分析器，編寫代碼，輸出乘法口訣表。2. 自行練習實驗指導書P24【實驗1.2 數(shù)據(jù)查詢】1.2.5自我實踐部分；3. 自行練習實驗指導書P30【實驗1.3 數(shù)據(jù)更新】1.3.5自我實踐部分；4. 自行練習實驗指導書P36【實驗1.4 視圖】1.4.5自我實踐部分；5. 自行練習

7、實驗指導書P49【實驗1.6 空值和空集的處理】1.6.5自我實踐部分；四、 實驗報告1.SQL SERVER中變量聲明的命令是什么？輸出命令是什么？變量聲明：局部變量需要聲明，declare 變量名，空格，數(shù)據(jù)類型（聲明變量）。 全局變量不需要聲明。輸出命令：select （查詢命令）2.SQL SERVER中實現(xiàn)分支和循環(huán)的語句分別是什么？分支：用case 測試表達式when 測試值1 then 結果表達式1when 測試值2 then 結果表達式2else 結果表達式n+1end循環(huán)：用while 布爾表達式begin 語句序列1break 語句序列2continue 語句序列3end3

8、.在SQL SERVER里使用ORDER BY排序時，NULL值如何處理？使用GROUP BY分組時，NULL值又如何處理的？null 默認為最大值（即：asc 升序<小->大>，null值排在最后；desc 降序<大->小>，null值排在最前面4.實驗總結（實驗過程中出現(xiàn)的問題、解決方法、結果如何或其它）有些較為復雜的嵌套查詢，剛開始不知道如何寫，然后問同學，理清了思路，并完成了編程。在這次實驗中，有好多因為中英字符問題而出現(xiàn)的命令錯誤，進行更改后就可以正常執(zhí)行命令了實驗三數(shù)據(jù)庫完整性與安全性一、 實驗目的1. 理解實體完整性、參照完整性、用戶自定義完整

9、性的概念，學習三類完整性的實現(xiàn)，了解違反完整性的結果；2. 掌握MS SQL SERVER中的有關用戶登錄認證及管理方法，熟練使用自主存取控制進行權限管理；二、 實驗預習內容在認真閱讀教材及實驗指導書【2.1 實體完整性】、【2.2 參照完整性】、【2.3 用戶自定義完整性】、【2.4 觸發(fā)器】、【2.5 綜合案例】和【3.1 用戶標識與鑒別】、【3.2 自主存取控制】、【3.3 視圖機制在自主存取控制上的應用】、【3.4 Public角色在安全性中的應用】、【3.8 綜合案例】的基礎上，上機前請預習以下內容，并在空白處填寫相應的步驟或命令。1. 使用SCHOOL數(shù)據(jù)庫1） 創(chuàng)建一張新表CLA

10、SS，包括Class_id（varchar（4），Name（varchar（10），Department（varchar（20）三個屬性列，其中Class_id為主碼； 2） 執(zhí)行兩次相同的插入操作（0001，01csc,cs），結果如何；2. 使用SQL命令創(chuàng)建一張學生互助表，要求：包括學生編號、學生姓名、學生幫助對象的編號，每個學生有且僅有一個幫助對象，幫助的對象必須是已存在的學生。3. 使用STC數(shù)據(jù)庫，修改設置，完成以下2種不同的要求，請寫出對應的語句或簡述操作過程：1） 當更新、刪除STU表中記錄時，若SC表中有此學生的記錄，則拒絕；2） 當更新、刪除STU表中記錄時，若SC表中有此

11、學生的記錄，則自動更新或刪除；4. 使用SQL命令完成以下任務：1） 創(chuàng)建Worker表（表結構見指導書P73）2） 定義約束U1、U2，其中U1規(guī)定Name字段取值唯一，U2規(guī)定sage字段上限是28；3） 插入一條合法記錄；4） 插入一條違反U2約束的記錄，簡述觀察到的結果如何？并分析原因；5） 去除U1約束；6） 修改約束U2，令sage的值大于等于0；7） 創(chuàng)建規(guī)則rule_sex，規(guī)定更新或插入的值只能是M或F，并綁定到Worker的sex字段；8） 插入2條記錄，一條滿足規(guī)則rule_sex，一條違反規(guī)則，觀察結果。5. 使用查詢分析器創(chuàng)建觸發(fā)器并測試，請寫出相應的語句：1） 為W

12、orker表創(chuàng)建觸發(fā)器T1，當插入或更新表中數(shù)據(jù)時，保證所操作記錄的sage大于0；2） 為Worker表創(chuàng)建觸發(fā)器T2，禁止刪除編號為00001的記錄；3） 為Worker表創(chuàng)建觸發(fā)器T3，要求更新一個記錄時，表中記錄的sage要比老記錄的sage的值大。6. 分別用企業(yè)管理器和查詢分析器實現(xiàn)用戶的標識并測試，請寫出相應的操作過程或對應的語句：1） 設置SQL SERVER的安全認證模式；2） 建立名為“LiYong”的登錄用戶、數(shù)據(jù)庫用戶，默認訪問SCHOOL數(shù)據(jù)庫，密碼為“abcd”；3） 取消“LiYong”用戶；7. 先以sa帳號登錄，要實現(xiàn)如下表的權限管理，請按先后順序寫出相應的S

13、QL語句（假設所有用戶尚未定義，其密碼均為123），并加以必要的說明。編號授權用戶名被授權用戶名數(shù)據(jù)庫對象名允許的操作類型能否轉授權1SAU1StudentsSELECT不能2SAU2StudentsALL不能3SAPUBLICChoicesSELECT不能4SAU3StudentsSELECT能5U3U4StudentsSELECT能6U4U5StudentsSELECT不能7SAU6ChoicesINSERT能8SAU7Students.snameUPDATE不能8. 創(chuàng)建在SCHOOL數(shù)據(jù)庫中choices上的視圖CS_View，授權給計算機系講授課程號為10010的數(shù)據(jù)庫用戶“LiYo

14、ng”，讓其具有視圖上的SELECT權限；9. 對視圖CS_View上的score屬性列的UPDATE權限授予用戶“LiYong”，可以修改學生成績，但不能修改其它屬性列的值；10. 假設SCHOOL數(shù)據(jù)庫中，每個學生的登錄名都為自己在STUDENTS表中的sid，實現(xiàn)每個學生只能查詢自己選課信息，不能查詢別人的成績，也不能修改自己的成績。11. 結束本次實驗三、 實驗課后訓練1. 自行練習實驗指導書P81【實驗2.5 綜合案例】綜合案例1；2. 自行練習實驗指導書P122【實驗3.8 綜合案例】綜合案例；四、 實驗報告1. 授權給public與授權給指定用戶有什么區(qū)別？實際應用中，哪個更安全

15、些？2. SQL SERVER中的角色有什么作用？3. 實驗總結（實驗過程中出現(xiàn)的問題、解決方法、結果如何或其它）實驗四數(shù)據(jù)庫編程一、 實驗目的1. 掌握ODBC的配置；2. 能編寫簡單的存儲過程和函數(shù)，并調用；二、 實驗預習內容上機前請預習以下內容，并在空白處填寫相應的步驟或命令。1. 配置ODBC，使用VFP中的表單，查詢SCHOOL數(shù)據(jù)庫中STUDENTS表中所有記錄。2. 編寫存儲過程并調用，請寫出相應命令；1） 在查詢分析器中，編寫存儲過程usp_get_stuinfo ，使用一個名為xm能夠傳送進存儲過程的參數(shù)。允許以學生的姓名查詢該生的基本信息；2） 在查詢分析器中調用存儲過程查詢“趙飛”同學的基本信息的語句；3. 編寫函數(shù)并調用，請寫出相應命令；1） 在查詢分析器中，定義內聯(lián)表值函數(shù)Fun，用于返回各職稱的教師基本信息；2） 在查詢分析器中，使用Fun進行選擇以獲得“教授”職稱的教師基本信息；3） 在查詢分析器中，編寫標量函數(shù)Func，用于返回兩個整數(shù)中的最大值；4） 在查詢分析器中，調用此函數(shù)，輸出兩個數(shù)中的最大值；5） 在SQL SERVER中定義函數(shù)FUNS，返回指定參數(shù)的平方，配置好ODBC后，