數(shù)據(jù)庫實驗報告

1、課 程 設 計 報 告題目： 數(shù)據(jù)庫實驗上機實驗報告 專業(yè)班級： 計算機科學與技術1210班 學 號： U201215079 姓 名： 候寶峰 指導教師： 報告日期： 2015-06-04 計算機科學與技術學院目錄一、基本SQL操作（部分選做）11）數(shù)據(jù)定義12）數(shù)據(jù)更新33）用SQL語句完成下述查詢需求：4二、DBMS綜合運用（部分選做）81）學習sqlserver的兩種完全備份方式：數(shù)據(jù)和日志文件的脫機備份、系統(tǒng)的備份功能（選做）。82）學習系統(tǒng)的身份、權限配置操作93）了解SQLSERVER的存儲過程、觸發(fā)器、函數(shù)實現(xiàn)過程14三、實驗總結171）實驗問題及解決172）實驗心得18一、基本

2、SQL操作（部分選做）1）數(shù)據(jù)定義參照下面的內容建立自己實驗所需的關系數(shù)據(jù)創(chuàng)建三個關系：商品表【商品名稱、商品類型】GOODS【GNAMEchar（20）,GTYPEchar（10）】主關鍵字為（商品名稱）。商品類型為（電器、文具、服裝。）商場【商場名稱,所在地區(qū)】PLAZA【PNAMEchar（20），PAREAchar（20）】主關鍵字為商場名稱。所在地區(qū)為（洪山、漢口、漢陽、武昌。）銷售價格表【商品名稱、商場名稱、當前銷售價格、目前舉辦活動類型】SALE【GNAMEchar（20），PNAMEchar（20），PRICEFLOAT，ATYPEchar（10）】主關鍵字為（商品名稱、商場名

3、稱）。舉辦活動類型為（送券、打折），也可為空值，表示當前未舉辦任何活動。表中記錄如（哈森皮靴，亞貿廣場，200，打折），同一商場針對不同的商品可能采取不同的促銷活動。create table goods(gname char(20) primary key,gtype char(10);create table plaza(pname char(20) primary key,parea char(20);create table sale(gname char(20),pname char(20),price FLOAT,atype char(10)check (atype in('

4、送券','打折',''),primary key(gname,pname),foreign key(gname)references goods(gname),foreign key(pname)references plaza(pname);圖1 goods表圖2 plaza表圖3 sale表 2）數(shù)據(jù)更新（1）向上述表格中用sql語句完成增、刪、個、改的操作；增加記錄： insert into goods（gname，gtype） values（anta,服裝）；刪除記錄： delete from goods where gname=南孚and gt

5、ype=電池;更新記錄： update goods set gtype=電器 where gname=飛科;（2）編寫一個觸發(fā)器，并測試該觸發(fā)器；當插入一個“anta”貨物時不能把它售價低于350 ，如果低于350 就修改成350create trigger dbo.Chang_Update_sale on dbo.saleafter insert asif(select count(*)from sale,inserted where sale.gname='anta'and sale.price<350)=1begin update sale set sale.pri

6、ce=350 where sale.gname='anta'and sale.pname='家樂福' end圖4 觸發(fā)器執(zhí)行圖如圖，將原有的記錄刪掉后插入一條新紀錄，應該是倆行受影響，由于觸發(fā)器修改最小值，則會顯示三行受影響，查表知，是觸發(fā)器有效執(zhí)行了。（3）將SALE表中的打折記錄插入到新表SALE_CHEAP中，并基于SALE_CHEAP表創(chuàng)建一個統(tǒng)計每個商場各自打折商品平均價格的視圖。create view SALE_CHEAP as select sale.pname,sale.gname,sale.price from sale where atype

7、='打折'select pname,avg(price) avg from SALE_CHEAP group by(pname) 圖5 視圖3）用SQL語句完成下述查詢需求：（1）查詢所有以“打折”方式銷售的商品的當前銷售情況，并按照價格的降序排列；select GNAME,PNAME,PRICE from sale where ATYPE='打折' order by price desc;圖6 查詢12）查詢所有沒有任何活動的商品及其所在的商場，結果按照商品排序；select GNAME,PNAME from sale where ATYPE is null

8、order by gname;圖7 查詢23）查詢價格在200500元之間的商品名稱、所在的商場名稱、價格，結果按照商場名稱排序；select GNAME,PNAME,price from sale where price between 200 and 500 order by pname;圖8 查詢34）查詢每種商品的最低價格、商品名稱；select GNAME,min(price) min_price from sale group by gname;圖9 查詢45）查詢以“送券”方式銷售的商品總數(shù)超過30種的商場名稱；select distinct pNAME from salewhe

9、re atype='送券' and gname in(select gNAME from sale group by gname having count(*)>30 );圖10 查詢56）查詢以“送券”方式銷售的商品總數(shù)超過30種的商場所在地區(qū)；select pname, PAREAfrom plazawhere pname in(select distinct pNAME from salewhere atype='送券' and gname in(select gNAME from sale group by gname having count(*

10、)>30 );圖11 查詢67）查詢價格為下列取值之一的商品名稱、所在商場名稱、目前舉辦活動的類型，（88、188、288、388、488、588、888）；select gname,pname,atypefrom salewhere price in(88,188,288,388,488,588,888);圖12 查詢78）查詢以“老”字開頭的所有商品的名稱；select * from goods where gname like '老%'圖13 查詢89）查詢同時銷售“剃須刀”和“電池”的商場名稱；select pname from salewhere gname=&

11、#39;南孚'and pname in(select pname from salewhere gname=飛科');圖14 查詢910）查詢不舉辦任何活動的商場；select distinct plaza.pname,PAREA from sale,plazawhere atype is null and sale.pname=plaza.pname;圖15 查詢1011）查詢所銷售的商品包含了“校園超市”所銷售的所有商品的商場名稱。select distinct pname from salewhere gname in(select gname from salewher

12、e pname='家樂福');圖16 查詢11二、DBMS綜合運用（部分選做）1）學習sqlserver的兩種完全備份方式：數(shù)據(jù)和日志文件的脫機備份、系統(tǒng)的備份功能（選做）。利用企業(yè)管理器：選擇工具下的備份數(shù)據(jù)庫：還以利用企業(yè)管理器設置自動備份計劃等；直接拷貝數(shù)據(jù)文件。把數(shù)據(jù)庫的數(shù)據(jù)文件（*.mdf）和日志文件（*.ldf）都拷貝到目的服務器，在SQL Server Query Analyzer中用語句進行恢復：RESTORE DATABASE 數(shù)據(jù)庫名 | 數(shù)據(jù)庫名變量 FROM <備份設備> ,.n WITH RESTRICTED_USER , NORECOVE

13、RY | RECOVERY | STANDBY = undo_file_name RESTORE LOG 數(shù)據(jù)庫名| 數(shù)據(jù)庫名變量 FROM <備份設備> ,.n WITH RESTRICTED_USER , NORECOVERY | RECOVERY | STANDBY = undo_file_name 使得數(shù)據(jù)庫恢復到備份狀態(tài)。2）學習系統(tǒng)的身份、權限配置操作 圖17 sql server安全性決策當 SQL Server 2014 在 Windows 上運行時，sysadmin 固定服務器角色成員可以指定下面兩種身份驗證模式之一：Windows 身份驗證模式只進行 Windo

14、ws 身份驗證。用戶不能指定 SQL Server 2014 登錄 ID。這是 SQL Server 2014 的默認身份驗證模式。不能為在 Windows 98 上運行的 SQL Server 實例指定 Windows 身份驗證模式，因為此操作系統(tǒng)不支持 Windows 身份驗證。當用戶通過 Windows NT 4.0 或 Windows用戶帳戶進行連接時，SQL Server 通過回叫 Windows NT 4.0 或 Windows以獲得信息，重新驗證帳戶名和密碼。SQL Server 通過使用網絡用戶的安全特性控制登錄訪問，以實現(xiàn)與 Windows NT 4.0 或 Windows的

15、登錄安全集成。用戶的網絡安全特性在網絡登錄時建立，并通過 Windows 域控制器進行驗證。當網絡用戶嘗試連接時，SQL Server 使用基于 Windows 的功能確定經過驗證的網絡用戶名。SQL Server 于是驗證此人是否是如其所說的那個人，然后只基于網絡用戶名允許或拒絕登錄訪問，而不要求單獨的登錄名和密碼。說明 如果用戶試圖通過提供空白登錄名稱連接到 SQL Server 的實例，SQL Server 將使用 Windows 身份驗證。此外，如果用戶試圖使用特定的登錄連接到配置為 Windows 身份驗證模式的 SQL Server 實例，則將忽略該登錄并使用 Windows 身份

16、驗證。與 SQL Server 身份驗證相比，Windows 身份驗證有某些優(yōu)點，主要是由于它與 Windows NT 4.0 和 Windows安全系統(tǒng)的集成。Windows NT 4.0 和 Windows安全系統(tǒng)提供更多的功能，如安全驗證和密碼加密、審核、密碼過期、最短密碼長度，以及在多次登錄請求無效后鎖定帳戶。由于 Windows NT 4.0 和 Windows用戶和組只由 Windows NT 4.0 或 Windows維護，因此當用戶進行連接時，SQL Server 將讀取有關該用戶在組中的成員資格信息。如果對已連接用戶的可訪問權限進行更改，則當用戶下次連接到 SQL Serve

17、r 實例或登錄到 Windows NT 4.0 或 Windows時（取決于更改的類型），這些更改會生效?；旌夏Ｊ饺绻脩粼诘卿洉r提供了 SQL Server 2014 登錄 ID，則系統(tǒng)將使用 SQL Server 身份驗證對其進行驗證。如果沒有提供 SQL Server 2014 登錄 ID 或請求 Windows 身份驗證，則使用 Windows 身份驗證對其進行身份驗證。當用戶用指定的登錄名稱和密碼從非信任連接進行連接時，SQL Server 通過檢查是否已設置 SQL Server 登錄帳戶，以及指定的密碼是否與以前記錄的密碼匹配，自己進行身份驗證。如果 SQL Server 未設置

18、登錄帳戶，則身份驗證將失敗，而且用戶收到錯誤信息。提供 SQL Server 身份驗證是為了向后兼容性，因為為 SQL Server 7.0 版或更早的版本編寫的應用程序可能要求使用 SQL Server 登錄和密碼。另外，當 SQL Server 實例在 Windows 98 上運行時，必須使用 SQL Server 身份驗證，因為在 Windows 98 上不支持 Windows 身份驗證模式。因此，SQL Server 在 Windows 98 上運行時使用混合模式（但只支持 SQL Server 身份驗證）。盡管建議使用 Windows 身份驗證，但對于 Windows NT 4.0

19、和 Windows客戶端以外的其它客戶端連接，可能需要使用 SQL Server 身份驗證。說明 當使用命名管道連接到在 Windows NT 4.0 或 Windows上運行的 SQL Server 實例時，用戶必須有連接到 Windows NT 命名管道 IPC IPC$ 的權限。如果用戶沒有連接權限，則不能使用命名管道連接到 SQL Server 實例，除非計算機上的 Windows NT 4.0 或 Windowsguest 帳戶已啟用（默認情況下禁用），或者給用戶帳戶授予"從網絡訪問該計算機"的權限。設置 SQL Server 2014 身份驗證模式SQL Ser

20、ver 2014 身份驗證模式可以在安裝過程中指定或使用 SQL Server 企業(yè)管理器指定，如下圖。 圖18 安全設置也可以通過修改注冊表的方式來改變，如下圖，它保存在HKEY_LOCAL_MACHINESOFTWAREMicrosoftMSSQLServerMSSQLServer 節(jié)的 LoginMode 中。LoginMode的鍵值：1 表示Windows 身份驗證模式，2 表示混合模式。 圖19 注冊表更改注：改變模式后，必須重新啟動 SQL Server 2014 才會生效。SQL Server 2014 聯(lián)機叢書：若要連接到 Microsoft© SQL Server

21、2000 實例，只需給應用程序提供下面中的兩條信息：運行 SQL Server 實例的計算機所在的網絡名稱。實例名（可選，只有在連接到命名實例時才需要）。登錄標識符 (ID)。登錄 ID 是帳戶標識符，用來控制對任何 SQL Server 2014 系統(tǒng)的訪問權限。SQL Server 2014 只有在首先驗證了指定的登錄 ID 有效后，才完成連接。這種登錄驗證稱為身份驗證。在登錄屬性中，有一個是默認數(shù)據(jù)庫。當一個登錄連接到 SQL Server 時，這個默認數(shù)據(jù)庫就變成該連接的當前數(shù)據(jù)庫，除非該連接請求指定另一個數(shù)據(jù)庫作為當前數(shù)據(jù)庫。登錄 ID 僅能使您連接到 SQL Server 實例。特

22、定數(shù)據(jù)庫內的權限由用戶帳戶控制。數(shù)據(jù)庫管理員將您的登錄帳戶映射到您有權訪問的任何數(shù)據(jù)庫中的用戶帳戶。Windows 身份驗證SQL Server 2014 sysadmin 固定服務器角色成員必須首先向 SQL Server 2014 指定所有允許連接到 SQL Server 2014 的 Microsoft Windows NT® 或 Microsoft Windows® 2000 帳戶或組。當使用 Windows 身份驗證時，在連接到 SQL Server 2014 時不必指定登錄 ID 或密碼。用戶對 SQL Server 2014 的訪問權限由 Windows NT

23、 或 Windows帳戶或組控制，當?shù)卿浀娇蛻舳松系?Windows 操作系統(tǒng)時需接受身份驗證。當連接到 SQL Server 2014 時，SQL Server 2014 客戶端軟件向 SQL Server 2014 請求 Windows 信任連接。直到客戶端使用有效的 Windows 帳戶成功登錄后，Windows 才打開信任連接。信任連接的屬性包括打開連接的客戶端的 Windows NT 和 Windows組及用戶帳戶。SQL Server 2014 從信任連接屬性中得到用戶的帳戶信息，并將它們與定義為有效 SQL Server 2014 登錄的 Windows 帳戶相匹配。如果 SQL

24、 Server 2014 找到匹配的項，則接受這個連接。當使用 Windows身份驗證連接到 SQL Server 2014 時，用戶標識即是 Windows NT 或 Windows組或用戶帳戶。Microsoft Windows Me 和 Windows 98 操作系統(tǒng)不支持服務器端的信任連接 API。SQL Server 在 Windows Me 或 Windows 98 上運行時不支持 Windows 身份驗證。用戶在連接時必須提供 SQL Server 登錄帳戶。當 SQL Server 在 Windows NT 或 Windows上運行時，Windows Me、Windows 98

25、 和 Windows 95 客戶端可以使用 Windows身份驗證與其連接。sysadmin 固定服務器角色成員首先向 SQL Server 2014 指定所有有效的 SQL Server 2014 登錄帳戶和密碼。這些登錄帳戶和密碼與用戶的 Microsoft Windows 帳戶或網絡帳戶無關。當連接到 SQL Server 2014 時，用戶必須提供 SQL Server 2014 登錄帳戶和密碼。系統(tǒng)將通過用戶的 SQL Server 2014 登錄帳戶在 SQL Server 2014 中標識用戶。由此可以看出，SQL Server 2014 安全控制是由登錄>>用戶&g

26、t;>權限來得到控制的。因此要連接 SQL Server 2014，首先要經過身份驗證，要通過身份驗證，就必須擁有登錄 ID。新建用戶 ：ceshi 圖20 建立新用戶分配選擇goods表權限給用戶ceshi ：grant select on goods to ceshi； 圖21 分配select權限分配插入刪除權限給ceshi： grant insert and delete on plaza to ceshi；圖22 分配insert delete權限使用sql server登陸驗證方式登陸ceshi，展開數(shù)據(jù)庫 ，只有goods表 和plaza表，圖23 ceshi用戶所有表收回

27、ceshi用戶所有的權限：Revoke all on goods from ceshiRevoke all on plaza from ceshi圖24 回收所有的權限 3）了解SQLSERVER的存儲過程、觸發(fā)器、函數(shù)實現(xiàn)過程通過查看SQLSERVER的聯(lián)機幫助文檔和示例，體會存儲過程、函數(shù)和觸發(fā)器的原理，嘗試編制具備基本功能的存儲過程、觸發(fā)器和函數(shù)的實際例子，查看其執(zhí)行效果。創(chuàng)建一個存儲過程，其中包含插入和刪除操作，經驗證存儲結果，過程生效。 Create Procedure MyProcedure AS Begin Set NOCOUNT ON; Set XACT_ABORT ON; B

28、egin Tran Delete from goods where gname='anta' Insert into plaza values('校園超市','武廣'); Commit Tran End創(chuàng)建一個級聯(lián)刪除觸發(fā)器，使得在sale中刪除一個貨物時刪除goods表中同種貨物。create trigger Chang_Update_sale on dbo.salefor deleteasdelete goodsfrom goods,salewhere sale.gname=goods.gname;圖25 級聯(lián)刪除結果1圖26 級聯(lián)刪除結果2

29、創(chuàng)建一個函數(shù)選擇goods表所有內容;SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO- =- Author:<Author,Name>- Create date: <Create Date,>- Description:<Description,>- =CREATE FUNCTION typoints()RETURNS TABLE ASRETURN (SELECT *from goods);Select 選擇該函數(shù)，由于是表值函數(shù)所以用 select * fromdbo.typoints()調用，選擇goods表內

30、容，有結果可以看出，該函數(shù)執(zhí)行成功。與直接執(zhí)行函數(shù)體SELECT *from goods的結果是一樣的。圖27 函數(shù)執(zhí)行結果三、實驗總結1）實驗問題及解決1、由于安裝的是較新版的數(shù)據(jù)庫（sql server 2014）使得有些語句與書上不符，比如建觸發(fā)器語句：create trigger dbo.Chang_Update_sale on dbo.saleafter insert asif(select count(*)from sale,inserted where sale.gname='anta'and sale.price<350)=1begin update sa

31、le set sale.price=350 where sale.gname='anta'and sale.pname='家樂福' end 書上有before 語句，2014 版中不支持before語句但有instead of 語句，同時也不支持or連接兩個操作的說明，還是要查找一些資料才能理解語句的內涵。才能知道雖然變了部分語法，但是還是換湯不換藥的方式。2、建立視圖是，總是建立失敗，第一次未能插入到新視圖里，第二次是想直接建立一個平均后的視圖，經過多次嘗試失敗，還使用了group語句去完善自己的視圖，雖然實驗簡單，但是同樣體會到數(shù)據(jù)庫的重要性，同樣操作不容馬

32、虎，不能有一點一點的失誤。 3、所學標準語句與實際所用的數(shù)據(jù)庫產品標準的差異問題。   雖然很多時候核心的語句是一致的，但是有一些地方不一樣。比如grant時，標準語句需要在表明前加上table，但是在使用SQL Server 2014時，發(fā)現(xiàn)語句有問題，和同學討論了很久，改了很多關鍵字都不起效，后來在網上找到實際的grant語句，進行仔細地比較才發(fā)現(xiàn)在SQL Server 2014里面是不需要“table”這個關鍵字的。  4、 創(chuàng)建外碼的問題；  一開始，隨手就先創(chuàng)建sale關系，然