數據庫實驗報告冊

1、合肥師范學院 實驗報告冊 2014 / 2015 學年 第1 學期系 別 計算機學院實驗課程 數據庫原理專 業(yè) 班 級 姓 名 學 號 指導教師 李宜兵實驗名稱課時實驗報告成績實驗（一）數據庫基本操作2實驗（二）SQL語句6實驗（三）數據庫完整性與安全性實驗4實驗（四）數據庫編程4備注：數據庫原理實驗 班級： 學號： 姓名： 實驗一 數據庫基本操作一、 實驗目的1. 熟悉MS SQL SERVER運行界面，掌握服務器的基本操作。2. 掌握界面操作方法完成用戶數據庫建立、備份和還原。3. 建立兩個實驗用的數據庫，使用企業(yè)管理器和查詢分析器對數據庫和表進行基本操作。二、 實驗預習內容在認真閱讀教材

2、及實驗指導書的基礎上，上機前請預習以下內容，并在空白處填寫相應的步驟或命令。1. 熟悉SQL SERVER 2000 的運行環(huán)境，練習服務器基本操作：打開、停止、關閉。2. 使用SQL SERVER 2000 中的企業(yè)管理器完成以下任務。數據庫名稱：STC表：STU（sno char(9), sname varchar(50), ssex char(2) , sage int, sdept char(2) ）； COURSE（ cno char(3), cname varchar(50), cpno char(3), credit int ）； SC（sno char(9), cno char

3、(3), grade int ）；說明：以上為表結構，以sno char（9）為例，說明sno屬性設置為字符類型，寬度為9，int指整型數據。 1） 建立數據庫STC，分別建立以上三張表，并完成數據錄入。（表結構及數據參見教材）2） 分析并建立各表的主碼，用下劃線在上面表結構中標出主碼。create table stu(sno char(9) primary key,sname varchar(50),ssex char(2),sage int,sdept char(2) );select* from coursecreate table course( cno char(3) primary

4、 key,cname varchar(50),cpno char(3),credit int ,foreign key (cpno) references course(cno);create table sc(sno char(9),cno char(3),grade int,primary key(sno,cno),foreign key (sno) references stu(sno),foreign key (cno) references course(cno);insertinto stuvalues('95001','李勇','男'

5、;,'20','cs');Stu表：Course表：Sc表：3） 建立各表之間的關聯(lián)，請簡述操作步驟。建立關聯(lián)：右擊SC表，設計表-在任意一個空白處右擊-關系-表和列規(guī)范-選擇主鍵表course對應的主碼cno和外鍵表sc對應的cno以及主鍵表stu對應的主碼sno和外鍵表sc對應的sno確定即可 4） 參考實驗指導書的【第5章 數據庫的備份和還原】，使用企業(yè)管理器對數據庫STC進行備份，并嘗試在個人電腦與機房電腦上進行還原，請簡述備份、還原操作的步驟。 右擊STC數據庫-任務備份備份到(添加到你要備份到的地方)-確定。 右擊STC數據庫-任務還原-數據庫-（從

6、原數據庫或源設備）-選擇路徑-確定。3. 在SQL SERVER 2000 的查詢分析器中使用SQL語言完成以下任務。參考實驗指導書數據庫系統(tǒng)實驗指導教程【實驗1.1 數據定義】，建立數據庫SCHOOL，分別建立students、teachers、courses、choices四張表，表結構見實驗指導書P236 【附錄A】。1） 用SQL語言創(chuàng)建數據庫、建表。建表時為各表建立關鍵字、設置外碼，數據暫不錄入，請寫出對應的SQL命令。create table students(sid char(10) primary key,sname char(30) not null,email char(3

7、0),grade int);create table teachers(tid char (10) primary key,tname char(30) not null,email char(30),salary int,);create table courses(cid char(10) primary key,cname char(30) not null,hour int,);create table choices(no int primary key,sid char(10) not null,tid char(10) not null,cid char(10) not null

8、,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 s on students(sid asc) create index c on courses(cid asc)3） 刪除course上的索引，請寫出相應的SQL命令。 drop index c

9、 on courses4） 在SCHOOL數據庫中的students表中增加一個“出生日期”字段，類型為日期時間型，請寫出對應的SQL命令。 Alter table students add “出生日期” datetime5） 刪除students中的“出生日期”字段，請寫出對應的SQL命令。Alter table students drop clumn 出生日期6） 刪除SCHOOL數據庫中的students表，請寫出對應的SQL命令。 drop table students cascade4. 使用企業(yè)管理器創(chuàng)建數據庫SCHOOL，實現內容與實驗預習內容3完全相同。5. 使用企業(yè)管理器，將

10、SCHOOL數據庫分離出MS SQL SERVER，請簡述步驟。點開數據庫-找到school數據庫-(右擊)所有任務-分離數據庫-確定。6. 使用企業(yè)管理器，使用【數據庫/所有任務/附加數據庫】將SCHOOL數據庫添加進SQL SERVER，并查看數據。7. 使用查詢分析器，刪除數據庫STC，請寫出對應的SQL命令。Drop database stc8. 結束本次實驗三、 實驗課后訓練1. 使用企業(yè)管理器練習數據的導入導出。1） 將SCHOOL數據庫students表中的數據導出到STC數據庫對應的stu表中；2） 向STC數據庫courses表導入SCHOOL數據庫courses表中的數據；

11、3） 自行練習實驗指導書【實驗5.3 SQL SERVER數據庫的導入與導出】部分；2. 自行練習實驗指導書【實驗1.1數據定義】部分。4、 實驗報告1. 簡述本次實驗所用到的SQL命令及其功能。命令：creat database <數據庫名> 功能：建立數據庫命令： Creat table <表名> （<列名> <數據類型> 列級完整性約束條件），<表級完整性約束條件> 功能：定義基本表命令：alter table <表名> add<新列名> <數據類型> 完整性約束-增加新列和新的完整性約束條件

12、 Drop <完整性約束名>-刪除指定的完整性約束條件 alter column <列名> <數據類型> ；-修改原有的列定義，包括列名和數據類型。功能：修改基本表2. 在MS SQL SERVER中數據庫的“分離|添加”與“備份|還原”是相同的功能嗎？答：在MS SQL SERVER中數據庫的“分離|添加”與“備份|還原”的功能是不相同的。分離之后的數據庫在用戶不更改路徑的情況下會分離在系統(tǒng)默認的路徑上，且分離之后在數據庫的下屬欄中找不到分離的數據庫，除非重新按指定路徑添加。而備份的數據庫還會顯示在數據庫的下屬欄中。3. 實驗總結（實驗過程中出現的問題、解

13、決方法、結果如何或其它） 本次試驗過程中遇到了很多問題，因為是第一次使用這個軟件，有好多東西都不知道在哪，比如，不知道如何建立關聯(lián)，如何備份還原數據庫等，經過詢問同學才知道。對書上的知識不是很了解，不知道如何建立索引和刪除索引，在看書之后才知道如何做。經過這次實驗，以后做實驗要好好預習。實驗二SQL語句一、 實驗目的1. 熟悉SQL的數據查詢語言，能使用SQL進行單表查詢、連接查詢、嵌套查詢、集合查詢和統(tǒng)計查詢，能理解空值的處理；2. 熟悉數據庫的數據更新操作，能使用SQL語句對數據庫進行數據的插入、更新、刪除操作；3. 熟悉SQL支持的有關視圖的操作，能創(chuàng)建、查詢及取消視圖；4. 了解NUL

14、L在數據庫中的特殊含義，掌握使用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 si

15、d2） 查詢所有課程名稱中含有data的課程編號；select cid from courses where cname like '%data%'3） 統(tǒng)計所有老師的平均工資；select AVG(salary) 平均工資from teachers4） 查詢至少選了3門課的學生編號；select sidfrom choicesgroup by sid having count(*)>=35） 查詢學號為80009026的學生的姓名、所選課名及成績；select sname,cname,scorefrom students,courses,choiceswhere stu

16、dents.sid=80009026 and choices.sid=students.sidand choices.cid =courses.cid6） 查詢沒有學生選的課程編號；select cid from courseswhere cid not in (select cid from choices )7） 查詢既選了C+又選了Java課程的學生編號；select sidfrom choices,courseswhere choices.cid=courses.cid and cname='c+'and sid in(select sidfrom choices,co

17、urseswhere choices.cid=courses.cid and cname='java')8） 查詢選了C+但沒選Java課程的學生編號；select sidfrom choices,courseswhere choices.cid=courses.cid and cname='c+'and sid not in(select sidfrom choices,courseswhere choices.cid=courses.cid and cname='java')9） 向STUDENTS表中插入“LiMing”的個人信息（編號：7

18、00045678，名字：LiMing，Email：LX，年級：1992）；insert into studentsvalues ('700045678','liming','LX','1992')10） 將“LiMing”的年級改為2002；update studentsset grade='2002'where sname='liming'11） 刪除所有選了Java課程的學生選課記錄；delete from choices where cid=(select cid from courseswh

19、ere cname='java')12） 求出每門課的課程號、選課人數，結果存入數據庫表T1中。create table T1(cid char(10) primary key,renshu int)insertinto t1select cid,count(distinct sid)from choicesgroup by cid;13） 查詢所有選課記錄的成績并換算為五分制（注意NULL的情況）；select distinct score,score/20 五分制from choices;14） 查詢成績小于0的選課記錄，統(tǒng)計總數、平均分、最大值和最小值（注意查詢結果中NU

20、LL的情況）；select sid, cidfrom choiceswhere score<0select distinct count(*)總數,avg(score) 平均值,max(score)最大值,min(score)最小值from choices15） 按成績順序排序顯示CHOICES表中所有記錄。（使用ORDER BY排序，注意NULL的情況）；select *from choicesorder by score;16） 創(chuàng)建視圖V1，顯示學生姓名、所選課名稱、任課教師名；create view v1 (sname,cname,tname)asselect sname,cna

21、me,tnamefrom teachers,choices ,students,courseswhere choices.cid=courses.cid and choices.sid=students.sid and choices.tid=teachers.tid17） 取消V1視圖；drop view v12. 使用STC數據庫，在SQL SERVER 2000 的查詢分析器中使用SQL語言完成以下操作。請在空白處填寫相應的SQL命令或其它內容。1） 創(chuàng)建視圖V2，顯示計算機（CS）系學生信息；（結合下面的b)小題，V2視圖創(chuàng)建2次，分別使用/不使用WITH CHECK OPTION選項

22、）（1）不加WITH CHECK OPTION（2）加WITH CHECK OPTIONcreate view v2asselect *from stuwhere sdept='cs'create view v2asselect *from stuwhere sdept='cs'WITH CHECK OPTION2） 插入元組（99999，張三，20歲，男，PH）到V2中，運行結果如何？（觀察WITH CHECK OPTION對結果的影響）；（1）不加WITH CHECK OPTION的結果（2）加WITH CHECK OPTION的結果insertinto v

23、2values('99999','張三','男',20,'PH')insertinto v2values('99999','張三','男',20,'PH')3） 在V2基礎上，查詢所有計算機系女生信息;select * from V2where ssex='女'3. 結束本次實驗三、 實驗課后訓練1. 掌握SQL SERVER中流控制語句及其它常用編程語句；1） 使用查詢分析器，編寫代碼，輸出乘法口訣表。2. 自行練習實驗指導書P24【實驗1.2 數據

24、查詢】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. SQL SERVER中變量聲明的命令是什么？輸出命令是什么？Declare variable_name /局部變量名稱Datatype /數據類型2. SQL SERVER中實現分支和循環(huán)的語句分別是什么？分支：case when 條件1 then 結果1 when 條件2 then 結果2 else 其他結果

25、end循環(huán)： while (條件-只能是表達式) 語句或語句塊 break-強制退出3. 在SQL SERVER里使用ORDER BY排序時，NULL值如何處理？使用GROUP BY分組時，NULL值又如何處理的？在使用ORDER BY 進行排序時，有ASC（升序）和DESC（降序）兩種方式。無論采用哪種方式，NULL值總被當作最小值處理。即在按升序排列時，取空值的元組將最先顯示，按降序排時，取空值的元組將會最后顯示。 使用GROUP BY 進行排序，取空值的項不是被忽略，而是將NULL看成一個取值，在處理的時候把所有取空值的項都看成一樣，因而形成一個分組。4. 實驗總結（實驗過程中出現的問題

26、、解決方法、結果如何或其它）有好多次運行都是有檢查完成但是執(zhí)行錯誤，經檢查才知道是上面的數據庫默認是master，而不是要執(zhí)行的數據庫。由于粗心導致的字母拼寫錯誤，導致執(zhí)行結果出錯。不會使用嵌套語句，經過分析書上的程序段，略微懂了一點。向表中插入記錄，應該用insert into 表名 values（列屬性）.創(chuàng)建視圖用create view (視圖名) as from 表名 。經過試驗驗證，明白with check option 可以自動篩選不符合條件的記錄。實驗三數據庫完整性與安全性一、 實驗目的1. 理解實體完整性、參照完整性、用戶自定義完整性的概念，學習三類完整性的實現，了解違反完整性

27、的結果；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數據庫1） 創(chuàng)建一張新表CLASS，包括Class_id（varchar（4

28、），Name（varchar（10），Department（varchar（20）三個屬性列，其中Class_id為主碼； create table class(Class_id varchar(4) primary key,Name varchar(10),Department varchar(20)2） 執(zhí)行兩次相同的插入操作（0001，01csc,cs），結果如何；insert into CLASSvalues('0001','01CSC','CS')第一次插入：第二次插入：2. 使用SQL命令創(chuàng)建一張學生互助表，要求：包括學生編號、學生姓

29、名、學生幫助對象的編號，每個學生有且僅有一個幫助對象，幫助的對象必須是已存在的學生。create table 學生互助表(sid varchar(10) primary key, sname varchar(30), s_id varchar(10) unique, foreign key (s_id) references 學生互助表(sid);3. 使用STC數據庫，修改設置，完成以下2種不同的要求，請寫出對應的語句或簡述操作過程：1） 當更新、刪除STU表中記錄時，若SC表中有此學生的記錄，則拒絕；Alter table scAdd constraint c1 foreign key(s

30、no) references stu(sno)On update no actionOn delete no action2） 當更新、刪除STU表中記錄時，若SC表中有此學生的記錄，則自動更新或刪除；Alter table scAdd constraint c2 foreign key(sno) references stu(sno)On update cascadeOn delete cascade4. 使用SQL命令完成以下任務：1） 創(chuàng)建Worker表（表結構見指導書P73）create table worker(number char(5),name char(8) constrai

31、nt U1 unique,sex char (1),sage int constraint U2 check (sage<=28),deapartment char (20),constraint PK_worker primary key(number)2） 定義約束U1、U2，其中U1規(guī)定Name字段取值唯一，U2規(guī)定sage字段上限是28；3） 插入一條合法記錄；insertinto workervalues('123','小明','m',20,'cs')4） 插入一條違反U2約束的記錄，簡述觀察到的結果如何？并分析原

32、因；insertinto workervalues('123','小明','m',29,'cs')原因：sage的值必須<=28。5） 去除U1約束；alter table workerdrop constraint U1;6） 修改約束U2，令sage的值大于等于0；alter table workerdrop constraint U2;alter table workeradd constraint U2 check (sage>=0);7） 創(chuàng)建規(guī)則rule_sex，規(guī)定更新或插入的值只能是M或F，并綁定到Wo

33、rker的sex字段；Go Create Rule rule_sex as value in('F','M')Goexec sp_bindrule rule_sex,'Worker.sex'8） 插入2條記錄，一條滿足規(guī)則rule_sex，一條違反規(guī)則，觀察結果。Insert into Workervalues ('00005','小李','m',15,'科技部')Insert into Workervalues ('00005','小李','

34、a',15,'科技部')5. 使用查詢分析器創(chuàng)建觸發(fā)器并測試，請寫出相應的語句：1） 為Worker表創(chuàng)建觸發(fā)器T1，當插入或更新表中數據時，保證所操作記錄的sage大于0；create trigger t1on workerfor insert,updateasif(select sage from inserted)<1rollback transaction2） 為Worker表創(chuàng)建觸發(fā)器T2，禁止刪除編號為00001的記錄；create trigger t2on workerfor deleteasif(select number from deleted

35、)= '00001'rollback transaction3） 為Worker表創(chuàng)建觸發(fā)器T3，要求更新一個記錄時，表中記錄的sage要比老記錄的sage的值大。create trigger T3on worker for updateas if(select sage from inserted)<(select sage from deleted) rollback Transaction6. 分別用企業(yè)管理器和查詢分析器實現用戶的標識并測試，請寫出相應的操作過程或對應的語句：1） 設置SQL SERVER的安全認證模式；本地數據庫“屬性”->安全性2） 建立

36、名為“LiYong”的登錄用戶、數據庫用戶，默認訪問SCHOOL數據庫，密碼為“abcd”；exec sp_addlogin 'liyong','abcd','school'exec sp_grantdbaccess 'liyong'3） 取消“LiYong”用戶；exec sp_revokedbaccess 'liyong'exec sp_droplogin 'liyong'7. 先以sa帳號登錄，要實現如下表的權限管理，請按先后順序寫出相應的SQL語句（假設所有用戶尚未定義，其密碼均為123），

37、并加以必要的說明。編號授權用戶名被授權用戶名數據庫對象名允許的操作類型能否轉授權1SAU1StudentsSELECT不能2SAU2StudentsALL不能3SAPUBLICChoicesSELECT不能4SAU3StudentsSELECT能5U3U4StudentsSELECT能6U4U5StudentsSELECT不能7SAU6ChoicesINSERT能8SAU7Students.snameUPDATE不能exec sp_addlogin 'u1','123','school'exec sp_grantdbaccess 'u1&

38、#39;把select 權限授給U1Grant selectOn studentsTo u1;把所有權限授給U2Grant all privilegesOn studentsTo U2把choices表的select 權限授給publicGrant selectOn choicesTo public把select 權限授給U3Grant selectOn studentsTo u3With grant option;把select 權限授給U4,以u3登陸Grant selectOn studentsTo u4With grant option;把select 權限授給U5,以u4登陸Gran

39、t selectOn studentsTo u5With grant option;把choices表的insert 權限授給u6Grant insertOn choicesTo u6With grant option;把Students.sname的權限授給u7Grant update(sname),selectOn studentsTo u7驗證：update studentsset sname='asd'where sid='700045678'8. 創(chuàng)建在SCHOOL數據庫中choices上的視圖CS_View，授權給計算機系講授課程號為10010的數據

40、庫用戶“LiYong”，讓其具有視圖上的SELECT權限；create view cs_viewasselect * from choiceswhere cid='10010'grant select on cs_viewto liyong9. 對視圖CS_View上的score屬性列的UPDATE權限授予用戶“LiYong”，可以修改學生成績，但不能修改其它屬性列的值；grant update on dbo.cs_view(score)to liyongselect * from cs_viewupdate cs_view set score=90where no=50002

41、494010. 假設SCHOOL數據庫中，每個學生的登錄名都為自己在STUDENTS表中的sid，實現每個學生只能查詢自己選課信息，不能查詢別人的成績，也不能修改自己的成績。create view v1asselect * from choiceswhere sid=current_userwith check optiongrant select on v1 to public11. 結束本次實驗三、 實驗課后訓練1. 自行練習實驗指導書P81【實驗2.5 綜合案例】綜合案例1；2. 自行練習實驗指導書P122【實驗3.8 綜合案例】綜合案例；四、實驗報告1. 授權給public與授權給指定

42、用戶有什么區(qū)別？實際應用中，哪個更安全些？ 授權給public是授給本地數據庫中的所有用戶，實際應用中授權給指定用戶更安全些。 用戶名public 指系統(tǒng)的所有當前用戶和將來的用戶，因此，對pubilc的授權隱含著對所有當前用戶和將來用戶的授權。在默認情況下，被授權的用戶無權把此權限授予其他用戶，但SQL允許使用授予權限來指定權限的接受者可以進一步把權限授予其他用戶，如果被收回權限的用戶已經把權限授予了其他用戶，權限的收回會更加復雜 數據庫中的每個用戶都屬于 public 數據庫角色。如果想讓數據庫中的每個用戶都能有某個特定的權限，則將該權限指派給 public 角色。如果沒有給用戶

43、專門授予對某個對象的權限，他們就使用指派給 public 角色的權限2. SQL SERVER中的角色有什么作用？ 數據庫角色是被命名的一組與數據庫操作相關的權限，角色是權限的集合。使用角色來管理數據庫權限可以簡化授權的過程。SQL Server 提供了兩種類型的數據庫角色：固定的數據庫角色和用戶自定義的數據庫角色。服務器角色是指根據SQL Server的管理任務，以及這些任務相對的重要性等級來把具有SQl Server管理職能的用戶劃分為不同的用戶組，每一組所具有的管理SQL Server 的權限都是Sql Server內置的，即不能對其進行添加、修改和刪除，只能向其中加入用戶或者其他角色。

44、數據庫角色是為某一用戶或某一組用戶授予不同級別的管理或訪問數據庫以及數據庫對象的權限，這些權限是數據庫專有的，并且還可以使一個用戶具有屬于同一數據庫的多個角色。3. 實驗總結（實驗過程中出現的問題、解決方法、結果如何或其它） 在本次實驗中，遇到了不少問題，在本次試驗中主要講述了數據庫的安全性與完整性，數據庫的安全性和完整性對數據庫至關重要！其中數據庫的安全性是指保護數據庫以防止不合法的使用所造成的數據泄露、更改或破壞。數據庫的完整性是指數據的正確性和相容性。數據庫的安全性主要通過數據庫的安全控制實現，安全控制的方法和手段有很多，最重要的是存取控制技術、視圖技術和審計技術。實驗四數據庫編程一、 實驗目的1. 掌握ODBC的配置；2. 能編寫簡單的存儲過程和函數，并調用；二、 實驗預習內容上機前請預習以下內容，并在空白處填寫相應的步驟或命令。1. 配置ODBC，使用VFP中的表單，查詢SCHOOL數據庫中STUDENTS表中所有記錄。2. 編寫存儲過程并調用，請寫出相應命令；1） 在查詢分析器中，編寫存儲過程usp_get_stuinfo ，使用一個