合肥師范學院 實驗報告冊 2013 / 2014 學年 第2 學期系 別 計算機科學與技術系實驗課程 數據庫原理專 業 計算機軟件班 級 軟件一班姓 名 羅曉薇學 號 1211431015指導教師 潘潔珠實驗名稱課時實驗報告成績實驗（一）數據庫基本操作2實驗（二）SQL語句6實驗（三）數據庫完整性與安全性實驗4實驗（四）數據庫編程4備注：數據庫原理實驗 班級： 學號： 姓名： 實驗一 數據庫基本操作一、 實驗目的1. 熟悉MS SQL SERVER運行界面，掌握服務器的基本操作。2. 掌握界面操作方法完成用戶數據庫建立、備份和還原。3. 建立兩個實驗用的數據庫，使用企業管理器和查詢分析器對數據庫和表進行基本操作。二、 實驗預習內容在認真閱讀教材及實驗指導書的基礎上，上機前請預習以下內容，并在空白處填寫相應的步驟或命令。1. 熟悉SQL SERVER 2000 的運行環境，練習服務器基本操作：打開、停止、關閉。2. 使用SQL SERVER 2000 中的企業管理器完成以下任務。數據庫名稱：STC表：STU（sno char(9), sname varchar(50), ssex char(2) , sage int, sdept char(2) ）； COUTSES（ cno char(3), cname varchar(50), cpno char(3), credit int ）； SC（sno char(9), cno char(3), grade int ）；說明：以上為表結構，以sno char（9）為例，說明sno屬性設置為字符類型，寬度為9，int指整型數據。 1） 建立數據庫STC，分別建立以上三張表，并完成數據錄入。（表結構及數據參見教材）2） 分析并建立各表的主碼，用下劃線在上面表結構中標出主碼。3） 建立各表之間的關聯，請簡述操作步驟。 答：1)2) 3) 4） 參考實驗指導書的【第5章 數據庫的備份和還原】，使用企業管理器對數據庫STC進行備份，并嘗試在個人電腦與機房電腦上進行還原，請簡述備份、還原操作的步驟。答：備份：右擊STC數據庫出現菜單，點擊任務，選擇還原操作，出現對話框后，輸入備份名稱backstc,在目標中選擇添加，選擇備份數據庫所在位置，再點擊確定即可備份。還原：右擊數據庫，在所有任務中選擇還原數據庫，再新的對話框內輸入所要還原數據庫的名稱，再選擇從設備還原，按添加鍵，選擇備份所在磁盤，選中備份，點擊確定還原數據庫。3. 在SQL SERVER 2000 的查詢分析器中使用SQL語言完成以下任務。參考實驗指導書數據庫系統實驗指導教程【實驗1.1 數據定義】，建立數據庫SCHOOL，分別建立students、teachers、courses、choices四張表，表結構見實驗指導書P236 【附錄A】。1） 用SQL語言創建數據庫、建表。建表時為各表建立關鍵字、設置外碼，數據暫不錄入，請寫出對應的SQL命令。創建數據庫Create database stc;Create table students (sid char(10) unique primary key,Sname char(30) not null,Email char(30),Grade int,);創建教師表Create table teachers(tid char(10) unique primary key,Tname char(30) not null,Email char(30),Salary int,);創建課程表Create table courses(cid char(10) unique primary key,Cname char(30) not null,Hour int,);創建選課表Create table choices(no char(10) unique primary key,Sid char(10) not null,Tid char(10),Cid char(10),Score int,Foreign key (sid) references students(sid),Foreign key (tid) references teachers(tid),Foreign key (cid) references courses(cid),);2） 為students表、courses建立按主鍵増序排列的索引，請寫出相應的SQL命令。Create index stusno on students (sno asc);Create index coucno on courses (cno asc);3） 刪除course上的索引，請寫出相應的SQL命令。 Drop index coucno;4） 在SCHOOL數據庫中的students表中增加一個“出生日期”字段，類型為日期時間型，請寫出對應的SQL命令。Alter table students add 出生日期 date;5） 刪除students中的“出生日期”字段，請寫出對應的SQL命令。Alter table students drop column 出生日期；6） 刪除SCHOOL數據庫中的students表，請寫出對應的SQL命令。Drop table students cascade;4. 使用企業管理器創建數據庫SCHOOL，實現內容與實驗預習內容3完全相同。5. 使用企業管理器，將SCHOOL數據庫分離出MS SQL SERVER，請簡述步驟。答:選中需要分離的數據庫右擊，所有任務菜單里有選項分離數據庫，直接選擇確定.6. 使用企業管理器，使用【數據庫/所有任務/附加數據庫】將SCHOOL數據庫添加進SQL SERVER，并查看數據。 圖 7. 使用查詢分析器，刪除數據庫STC，請寫出對應的SQL命令。Delete database stc;8. 結束本次實驗三、 實驗課后訓練1. 使用企業管理器練習數據的導入導出。1） 將SCHOOL數據庫students表中的數據導出到STC數據庫對應的stu表中；2） 向STC數據庫courses表導入SCHOOL數據庫courses表中的數據；3） 自行練習實驗指導書【實驗5.3 SQL SERVER數據庫的導入與導出】部分；2. 自行練習實驗指導書【實驗1.1數據定義】部分。答:1.1) 2) 實驗報告1. 簡述本次實驗所用到的SQL命令及其功能。 答：1.創建數據庫create database 2.創建基本表 create table ( 列級完整性約束) 3.建立表的索引create index on ( 次序) 4.修改基本表alter table add 完整性約束 5.刪除索引drop index 6.刪除表中某一屬性alter table drop column 7.刪除表drop table 8.刪除數據庫delete database 2. 在MS SQL SERVER中數據庫的“分離|添加”與“備份|還原”是相同的功能嗎？ 答：數據庫的備份是數據庫結構，對象和數據的副本，使得數據庫系統發生事故時能還原和恢復數據庫中的數據。實現快速的移動數據庫。將數據庫的附加的逆過程，就是把完整地數據庫文件和日志文件從服務器上分離下來，分離后的數據庫文件在拷貝和移動后可以附加在其他的計算機上，附加是分離的逆過程。 3. 實驗總結（實驗過程中出現的問題、解決方法、結果如何或其它） 答：問題：在附加數據庫時數據不是sql server能用的數據頭，沒有認清字符型的概念，即字符出生日期應輸入出生日期。刪除數據庫時應用delete語句，而不是drop語句，當導入數據時，要注意兩個對話框的用處不同。 解決方法：兩個對話框一個是需導入的數據庫，另一個是從哪兒導入數據庫，和是否是在需導入的數據庫還是在要導出的數據庫庫中操作無關。實驗二SQL語句一、 實驗目的1. 熟悉SQL的數據查詢語言，能使用SQL進行單表查詢、連接查詢、嵌套查詢、集合查詢和統計查詢，能理解空值的處理；2. 熟悉數據庫的數據更新操作，能使用SQL語句對數據庫進行數據的插入、更新、刪除操作；3. 熟悉SQL支持的有關視圖的操作，能創建、查詢及取消視圖；4. 了解NULL在數據庫中的特殊含義，掌握使用SQL進行與空值相關的操作；二、 實驗預習內容在認真閱讀教材及實驗指導書【實驗1.2 數據查詢】、【實驗1.3 數據更新】、【實驗1.4 視圖】和【實驗1.6 空值和空集的處理】的基礎上，上機前請預習以下內容，并在空白處填寫相應的步驟或命令。1. 使用SCHOOL數據庫，在SQL SERVER 2000 的查詢分析器中使用SQL語言完成以下操作。請在空白處填寫相應的SQL命令。1） 查詢年級為2001的所有學生的名稱，按編號升序排列； select sname from students where grade=2001 order by sid asc2） 查詢所有課程名稱中含有data的課程編號； select cid from courses where cname like %data%3） 統計所有老師的平均工資； select avg(salary) from teachers4） 查詢至少選了3門課的學生編號； select sid from choices group by sid having count(*)=3;5） 查詢學號為80009026的學生的姓名、所選課名及成績； select choices.sname,cname,score from students,choices,courses where choices.cid=courses.cid and choices.sid=students.sid and sid=800090266） 查詢沒有學生選的課程編號； select courses.cid from choices,courses where choices.cid=courses.cid and sid is null;7） 查詢既選了C+又選了Java課程的學生編號； select sid from choices,courses where courses.cid=choices.cid and ame=c+ and sid in (select sid from choices,courses where courses.cid=choices.cid and cname=java)8） 查詢選了C+但沒選Java課程的學生編號； select sid from choices,courses where courses.cid=choices.cid and cname=c+ and sid not in (select sid from choices,courses where choices.cid=courses.cid and cname=java) 9） 向STUDENTS表中插入“LiMing”的個人信息（編號：700045678，名字：LiMing，Email：LX，年級：1992）； select sid from choices,courses where courses.cid=choices.cid and cname=c+ and sid not in (select sid from choices,courses where choices.cid=courses.cid and cname=java) 10） 將“LiMing”的年級改為2002； update students set grade=2002 where sname=liming11） 刪除所有選了Java課程的學生選課記錄； delete from choices where java=(select cname from courses where courses.cid=choices.cid)12） 求出每門課的課程號、選課人數，結果存入數據庫表T1中。 create table t1 (cid char(10) primary key,rs int) insert into t1 select cid,count(sid) from choices group by cidi. 查詢所有選課記錄的成績并換算為五分制（注意NULL的情況）； select score,score/20 from choices 13） 查詢成績小于0的選課記錄，統計總數、平均分、最大值和最小值（注意查詢結果中NULL的情況）； select count(*),avg(score),max(score),min(score) from choices where score0 14） 按成績順序排序顯示CHOICES表中所有記錄。（使用ORDER BY排序，注意NULL的情況）； select * from choices order by score asc15） 創建視圖V1，顯示學生姓名、所選課名稱、任課教師名； create view v1 (sname,cname,tname) as select students.sname,ame,teachers.tname from choices,students,teachers,courses where choices.tid=teachers.tid and choices.cid=courses.cid and choices.sid=students.sid16） 取消V1視圖； drop view v12. 使用STC數據庫，在SQL SERVER 2000 的查詢分析器中使用SQL語言完成以下操作。請在空白處填寫相應的SQL命令或其它內容。1） 創建視圖V2，顯示計算機（CS）系學生信息；（結合下面的b)小題，V2視圖創建2次，分別使用/不使用WITH CHECK OPTION選項）（1）不加WITH CHECK OPTION（2）加WITH CHECK OPTIONCreate view v2(sno,sname,ssex,sage,sdept) as select * from stu where sdept=csCreate view v2(sno,sname,ssex,sage,sdept) as select * from stu where sdept=csWith check option2） 插入元組（99999，張三，20歲，男，PH）到V2中，運行結果如何？（觀察WITH CHECK OPTION對結果的影響）；（1）不加WITH CHECK OPTION的結果（2）加WITH CHECK OPTION的結果Insert into v2 values(99999,張三,男,20,ph)Insert into v2 values(99999,張三,男,20,ph)with check option違反了PRIMARY KEY 約束 PK_STU_76CBA758。3） 在V2基礎上，查詢所有計算機系女生信息； select * from view v2 where ssex= 女3. 結束本次實驗三、 實驗課后訓練1. 掌握SQL SERVER中流控制語句及其它常用編程語句；1） 使用查詢分析器，編寫代碼，輸出乘法口訣表。2. 自行練習實驗指導書P24【實驗1.2 數據查詢】1.2.5自我實踐部分；3. 自行練習實驗指導書P30【實驗1.3 數據更新】1.3.5自我實踐部分；4. 自行練習實驗指導書P36【實驗1.4 視圖】1.4.5自我實踐部分；5. 自行練習實驗指導書P49【實驗1.6 空值和空集的處理】1.6.5自我實踐部分； 答：1.Select * from courses2. select sid from choices3. Select cid from courses where hour88 實驗報告1SQL SERVER中變量聲明的命令是什么？輸出命令是什么？ 答：變量聲明的命令：declare 變量名 類型，局部變量的聲明命令為declare ；而全局變量的聲明命令，必須以標記符開頭。輸出命令：print局部變量或字符串；select 局部變量as 自定義列名 2.SQL SERVER中實現分支和循環的語句分別是什么？ 答：實現分支語句： if-else語句和case-end語句；if-else語句屬于分支結構，它與C編程語句的if語句類似，也是根據條件是否成立來確定程序的執行方向。語法：If（條件） 語句或語句塊Elsecase-end語句計算一組條件表達式，并返回其中一個符合條件的結果。語法：Case When條件1 then結果1 When條件2 then結果2 else 其他結果EndCase語句表達結果“條件1”成立，則執行“結果1”，其余類推。如果when的條件都不匹配，則執行else后面的結果。Else可以省略。如果省略else并且when的條件表達式的結果都不為TRUE，則case-end語句返回null實現循環的語句：while循環語句：while循環語句可以根據某些條件重復執行一條SQL語句或一個語句塊。通過使用while關鍵字，可以確保只要制定的條件為TRUE，就會重復執行語句，直至指定條件為FALSE為止:語法：While（條件） Begin 語句或語句塊 break| continue End3.在SQL SERVER里使用ORDER BY排序時，NULL值如何處理？使用GROUP BY分組時，NULL值又如何處理的？ 答：使用ORDER BY排序，此時null值往前排，null被看做最小值處理。 使用GROUP BY排序，此時取null的看做一組，是同一個值。4.實驗總結（實驗過程中出現的問題、解決方法、結果如何或其它） 問題：1.進行聯接操作時，連接條件，及各表同屬性分不大清。 2.對多個條件，多張表的查詢，聯接時連接條件的先后順序總是弄錯。 3.對兩個條件選其一的情況，不是很能掌握。 4.當查詢具有某一特征的復雜的元組的信息時只會最基礎的按條件來，不靈活。 解決辦法：先將要查的信息及其有關的表列出來，再考慮他們之間的關系，先做什么后做什么理清楚，兩個情況選一種滿足可以用in或其他形式表示，與運算用union，分類可以用group by或者having語句來表示條件。 結果：很有效，不過在通常在查詢中需要做聯接運算的列名沒有加表名前綴，導致查詢沒有意義。實驗三數據庫完整性與安全性一、 實驗目的1. 理解實體完整性、參照完整性、用戶自定義完整性的概念，學習三類完整性的實現，了解違反完整性的結果；2. 掌握MS SQL SERVER中的有關用戶登錄認證及管理方法，熟練使用自主存取控制進行權限管理；二、 實驗預習內容在認真閱讀教材及實驗指導書【2.1 實體完整性】、【2.2 參照完整性】、【2.3 用戶自定義完整性】、【2.4 觸發器】、【2.5 綜合案例】和【3.1 用戶標識與鑒別】、【3.2 自主存取控制】、【3.3 視圖機制在自主存取控制上的應用】、【3.4 Public角色在安全性中的應用】、【3.8 綜合案例】的基礎上，上機前請預習以下內容，并在空白處填寫相應的步驟或命令。1. 使用SCHOOL數據庫1） 創建一張新表CLASS，包括Class_id（varchar（4），Name（varchar（10），Department（varchar（20）三個屬性列，其中Class_id為主碼； 2） 執行兩次相同的插入操作（0001，01csc,cs），結果如何；2. 使用SQL命令創建一張學生互助表，要求：包括學生編號、學生姓名、學生幫助對象的編號，每個學生有且僅有一個幫助對象，幫助的對象必須是已存在的學生。3. 使用STC數據庫，修改設置，完成以下2種不同的要求，請寫出對應的語句或簡述操作過程：1） 當更新、刪除STU表中記錄時，若SC表中有此學生的記錄，則拒絕；2） 當更新、刪除STU表中記錄時，若SC表中有此學生的記錄，則自動更新或刪除；4. 使用SQL命令完成以下任務：1） 創建Worker表（表結構見指導書P73）2） 定義約束U1、U2，其中U1規定Name字段取值唯一，U2規定sage字段上限是28；3） 插入一條合法記錄；4） 插入一條違反U2約束的記錄，簡述觀察到的結果如何？并分析原因；5） 去除U1約束；6） 修改約束U2，令sage的值大于等于0；7） 創建規則rule_sex，規定更新或插入的值只能是M或F，并綁定到Worker的sex字段；8） 插入2條記錄，一條滿足規則rule_sex，一條違反規則，觀察結果。5. 使用查詢分析器創建觸發器并測試，請寫出相應的語句：1） 為Worker表創建觸發器T1，當插入或更新表中數據時，保證所操作記錄的sage大于0；2） 為Worker表創建觸發器T2，禁止刪除編號為00001的記錄；3） 為Worker表創建觸發器T3，要求更新一個記錄時，表中記錄的sage要比老記錄的sage的值大。6. 分別用企業管理器和查詢分析器實現用戶的標識并測試，請寫出相應的操作過程或對應的語句：1） 設置SQL SERVER的安全認證模式；2） 建立名為“LiYong”的登錄用戶、數據庫用戶，默認訪問SCHOOL數據庫，密碼為“abcd”；3） 取消“LiYong”用戶；7. 先以sa帳號登錄，要實現如下表的權限管理，請按先后順序寫出相應的SQL語句（假設所有用戶尚未定義，其密碼均為123），并加以必要的說明。編號授權用戶名被授權用戶名數據庫對象名允許的操作類型能否轉授權1SAU1StudentsSELECT不能2SAU2StudentsALL不能3SAPUBLICChoicesSELECT不能4SAU3StudentsSELECT能5U3U4StudentsSELECT能6U4U5StudentsSELECT不能7SAU6ChoicesINSERT能8SAU7Students.snameUPDATE不能8. 創建在SCHOOL數據庫中choices上的視圖CS_View，授權給計算機系講授課程號為10010的數據庫用戶“LiYong”，讓其具有視圖上的SELECT權限；9. 對