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

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

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

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

4、錄。（使用ORDER BY排序，注意NULL的情況）；16） 創(chuàng)建視圖V1，顯示學(xué)生姓名、所選課名稱、任課教師名；17） 取消V1視圖；2. 使用STC數(shù)據(jù)庫，在SQL SERVER 2000 的查詢分析器中使用SQL語言完成以下操作。請在空白處填寫相應(yīng)的SQL命令或其它內(nèi)容。1） 創(chuàng)建視圖V2，顯示計算機（CS）系學(xué)生信息；（結(jié)合下面的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中，運行結(jié)果如何？（觀察WITH CHECK OPTION對結(jié)果的影響）；（1）不加WITH CHECK OPTION的結(jié)果（2）加WITH CHECK OPTION的結(jié)果insert into V2value(99999,張三,20,男,PH)不加with check option時，命令執(zhí)行成功。加了這個后，在執(zhí)行命令，會出現(xiàn)錯誤。原因是目標視圖或者目標視圖所跨越的某一視圖指定了

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

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

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

9、性的概念，學(xué)習(xí)三類完整性的實現(xiàn)，了解違反完整性的結(jié)果；2. 掌握MS SQL SERVER中的有關(guān)用戶登錄認證及管理方法，熟練使用自主存取控制進行權(quán)限管理；二、 實驗預(yù)習(xí)內(nèi)容在認真閱讀教材及實驗指導(dǎo)書【2.1 實體完整性】、【2.2 參照完整性】、【2.3 用戶自定義完整性】、【2.4 觸發(fā)器】、【2.5 綜合案例】和【3.1 用戶標識與鑒別】、【3.2 自主存取控制】、【3.3 視圖機制在自主存取控制上的應(yīng)用】、【3.4 Public角色在安全性中的應(yīng)用】、【3.8 綜合案例】的基礎(chǔ)上，上機前請預(yù)習(xí)以下內(nèi)容，并在空白處填寫相應(yīng)的步驟或命令。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），結(jié)果如何；2. 使用SQL命令創(chuàng)建一張學(xué)生互助表，要求：包括學(xué)生編號、學(xué)生姓名、學(xué)生幫助對象的編號，每個學(xué)生有且僅有一個幫助對象，幫助的對象必須是已存在的學(xué)生。3. 使用STC數(shù)據(jù)庫，修改設(shè)置，完成以下2種不同的要求，請寫出對應(yīng)的語句或簡述操作過程：1） 當更新、刪除STU表中記錄時，若SC表中有此學(xué)生的記錄，則拒絕；2） 當更新、刪除STU表中記錄時，若SC表中有此

11、學(xué)生的記錄，則自動更新或刪除；4. 使用SQL命令完成以下任務(wù)：1） 創(chuàng)建Worker表（表結(jié)構(gòu)見指導(dǎo)書P73）2） 定義約束U1、U2，其中U1規(guī)定Name字段取值唯一，U2規(guī)定sage字段上限是28；3） 插入一條合法記錄；4） 插入一條違反U2約束的記錄，簡述觀察到的結(jié)果如何？并分析原因；5） 去除U1約束；6） 修改約束U2，令sage的值大于等于0；7） 創(chuàng)建規(guī)則rule_sex，規(guī)定更新或插入的值只能是M或F，并綁定到Worker的sex字段；8） 插入2條記錄，一條滿足規(guī)則rule_sex，一條違反規(guī)則，觀察結(jié)果。5. 使用查詢分析器創(chuàng)建觸發(fā)器并測試，請寫出相應(yīng)的語句：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)用戶的標識并測試，請寫出相應(yīng)的操作過程或?qū)?yīng)的語句：1） 設(shè)置SQL SERVER的安全認證模式；2） 建立名為“LiYong”的登錄用戶、數(shù)據(jù)庫用戶，默認訪問SCHOOL數(shù)據(jù)庫，密碼為“abcd”；3） 取消“LiYong”用戶；7. 先以sa帳號登錄，要實現(xiàn)如下表的權(quán)限管理，請按先后順序?qū)懗鱿鄳?yīng)的S

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

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

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