北京聯(lián)合大學(xué)數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告

1、精選優(yōu)質(zhì)文檔-傾情為你奉上精選優(yōu)質(zhì)文檔-傾情為你奉上專心-專注-專業(yè)專心-專注-專業(yè)精選優(yōu)質(zhì)文檔-傾情為你奉上專心-專注-專業(yè)北京聯(lián)合大學(xué) 數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告題 目： * 姓 名： * 學(xué) 號(hào): * 專 業(yè)： 計(jì)算機(jī)工程 編 制 時(shí) 間： * 北京聯(lián)合大學(xué)編制實(shí)驗(yàn)一 一、實(shí)驗(yàn)?zāi)康膶W(xué)習(xí)MS SQL Server 2005的安裝、啟動(dòng)方法，注冊(cè)SQL Server服務(wù)器的方法。熟悉SQL Server的工作環(huán)境掌握使用向?qū)Ш兔罱?shù)據(jù)庫(kù)的方法掌握數(shù)據(jù)庫(kù)常用選項(xiàng)的設(shè)置方法掌握建立數(shù)據(jù)表的方法。二、實(shí)驗(yàn)內(nèi)容采用SQL語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)CREATE DATABASE OrderMag2ONPRIMARY(NA

2、ME=OrderMag2_Data,FILENAME = C:Program FilesMicrosoft SQL ServerMSSQLdataOrderMag2_Data.ndf, SIZE=10MB, MAXSIZE=200MB, FILEGROWTH=10%)LOG ON(NAME=OrderMag2_Log, FILENAME = C:Program FilesMicrosoft SQL ServerMSSQLdataOrderMag2_Log.LD, SIZE=3MB, MAXSIZE=100MB, FILEGROWTH=10%)GO三 回答問題創(chuàng)建Store、Customer 、

3、Order1三個(gè)表use OrderMag2create table Store(Pno char(6) primary key not null,Pname varchar(20) not null,Ptype char(2),Pnum int)create table Customer(Cno char(6) primary key not null,Cname varchar(20) not null,Ctel varchar(12) not null,Caddr varchar(50),Czip varchar(6)create table Order1(Ono char(6) pri

4、mary key not null,Cno char(6),Pno char(6),Onum int,Osum int,Odate datetime)alter table Order1 add constraint fk_customer_id foreign key(Cno) references Customer(Cno);alter table Order1 add constraint fk_Store_id foreign key(Pno) references Store(Pno);alter table Order1 add constraint check_Onum chec

5、k(Onum=0 and Onum 0)操作過程中遇到的問題是“order”是數(shù)據(jù)庫(kù)中的一個(gè)關(guān)鍵字，不能把它作為表的名字，所以在創(chuàng)建order表的時(shí)候在其后面加上數(shù)字或其他字母來以示區(qū)別。四 實(shí)驗(yàn)小結(jié)通過本次實(shí)驗(yàn)學(xué)會(huì)了MS SQL Server 2005的安裝、啟動(dòng)方法，注冊(cè)SQL Server服務(wù)器的方法；和如何使用向?qū)Ш兔罱?shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)常用選項(xiàng)的設(shè)置方法及建立數(shù)據(jù)表的方法。實(shí)驗(yàn)二、數(shù)據(jù)維護(hù)及簡(jiǎn)單查詢一、實(shí)驗(yàn)?zāi)康模簩W(xué)習(xí)使用查詢分析器掌握使用Insert、Update、Delete命令維護(hù)數(shù)據(jù)表的數(shù)據(jù)的方法熟練掌握簡(jiǎn)單SQL命令的使用二、實(shí)驗(yàn)內(nèi)容： 1. INSERT命令輸入數(shù)據(jù)使用In

6、sert命令分別向OrderMag數(shù)據(jù)庫(kù)中的三個(gè)表中輸入如下5條數(shù)據(jù)。1) Store（Pno, Pname, Ptype， Pnum）庫(kù)存（零件號(hào)，零件名稱，零件類別，零件數(shù)量）PnoPnamePtypePnumP1齒輪傳動(dòng)200P2蝸桿傳動(dòng)30P3螺栓標(biāo)準(zhǔn)120P4墊圈標(biāo)準(zhǔn)500P5螺母標(biāo)準(zhǔn)10202) Order(Ono, Cno, Pno, Onum, Osum, Odate)訂單（訂單號(hào)，顧客號(hào)，定購(gòu)零件號(hào)，定購(gòu)數(shù)量，訂單金額，簽訂日期）OnoCnoPnoOnumOsumOsumO1C2P210010002006-4-8O2C3P3101002008-5-2O3C1P22020020

7、07-4-5O4C1P42202009-2-4O5C3P51102009-3-13) Customer(Cno, Cname, Ctel, Caddr, Czip)顧客（顧客號(hào)，顧客名稱，電話，地址，郵編） CnoCnameCtelCaddrCzipC1北京聯(lián)合大學(xué)010-北京 C2 上海貝爾021-上海C3 LG0755-深圳 C4華為（北京）北京C5香港航空852-香港 先修改Store表的列Ptype的長(zhǎng)度,再進(jìn)行插入操作,如下ALTER TABLE StoreALTER COLUMN Ptype char(10) INSERT INTO Store (Pno, Pname, Ptype

8、, Pnum) VALUES (P1,齒輪,傳動(dòng),200)INSERT INTO Store (Pno, Pname, Ptype, Pnum) VALUES (P2,蝸桿,傳動(dòng),30)INSERT INTO Store (Pno, Pname, Ptype, Pnum) VALUES (P3,螺栓,標(biāo)準(zhǔn),120)INSERT INTO Store (Pno, Pname, Ptype, Pnum) VALUES (P4,墊圈,標(biāo)準(zhǔn),500)INSERT INTO Store (Pno, Pname, Ptype, Pnum) VALUES (p5,螺母,標(biāo)準(zhǔn),1020)INSERT INTO

9、 Customer(Cno, Cname, Ctel, Caddr, Czip) VALUES (C1,北京聯(lián)合大學(xué),010-,北京,)INSERT INTO Customer(Cno, Cname, Ctel, Caddr, Czip) VALUES (C2,上海貝爾,021-,上海,) INSERT INTO Customer(Cno, Cname, Ctel, Caddr, Czip) VALUES (C3,LG,0755-,深圳,) INSERT INTO Customer(Cno, Cname, Ctel, Caddr, Czip) VALUES (C4,華為（北京）, null,北

10、京,) INSERT INTO Customer(Cno, Cname, Ctel, Caddr, Czip) VALUES (C5,香港航空,852-,香港,) INSERT INTO Order1(Ono, Cno, Pno, Onum, Osum, Odate) VALUES (O1,C2,P2,100,1000,2006-4-8)INSERT INTO Order1(Ono, Cno, Pno, Onum, Osum, Odate) VALUES (O2,C3,P3,10,100,2008-5-2)INSERT INTO Order1(Ono, Cno, Pno, Onum, Osum

11、, Odate) VALUES (O3,C1,P2,20,200,2007-4-5)INSERT INTO Order1(Ono, Cno, Pno, Onum, Osum, Odate) VALUES (O4,C1,P4,2,20,2009-2-4)INSERT INTO Order1(Ono, Cno, Pno, Onum, Osum, Odate) VALUES (O5,C3,P5,1,10,2009-3-1)2把store表中所有零件的數(shù)量增長(zhǎng)一倍UPDATE Store SET Pnum=Pnum*23把order表中簽訂日期在2005年之前的數(shù)據(jù)刪除。delete from ord

12、er1 where Odate20054向store表中添加一列：零件顏色（Pcolour）；ALTER TABLE Store ADD Pcolour varchar(20)5查詢?nèi)齻€(gè)表中的內(nèi)容SELECT * FROM StoreSELECT * FROM CustomerSELECT * FROM Order16查詢所有訂單的金額，并按照金額的降序排列SELECT Osum FROM Order1 ORDER BY Osum DESC7查詢簽訂日期在2009年的所有訂單的信息。SELECT * FROM Order1 WHERE Odate20098查詢顧客姓名中含有“北京”的顧客姓名和

13、電話。SELECT * FROM Customer WHERE Cname LIKE %北京%9查詢庫(kù)存數(shù)量少于100的零件號(hào)和名稱。SELECT Pno ,Pname FROM Store WHERE Pnum1002.查詢所有簽訂訂單的顧客的名稱和郵編；select Cname,Czip from Customer where Cno in(select distinct Cno from Order1)3.統(tǒng)計(jì)每類零件的數(shù)量分別為多少；select Pno,sum(Onum) from Order1 group by Pno 4.統(tǒng)計(jì)每個(gè)顧客簽訂訂單的次數(shù)；select Cno,coun

14、t(*) from Order1 group by Cno5.查詢所有顧客簽訂訂單的情況（包括沒有簽訂訂單的顧客）；select * from Customer left outer join Order1 on(Customer.Cno=Order1.Cno)6.查詢沒有賣過一次的零件號(hào)（沒有訂單）；select Pno from Store where Pno not in(select distinct Pno from Order1)7.查詢每個(gè)顧客簽訂訂單的金額總數(shù)；select Cno,sum(Osum) from Order1 group by Cno8.查詢所有訂單金額的平均值

15、；select avg(Osum) from Order19.查詢至少簽訂過兩次訂單的顧客信息。select Cno,count(*) times from Order1 group by Cno having count(*)=2三、完成實(shí)驗(yàn)報(bào)告并回答問題1) 外連接與內(nèi)聯(lián)接的區(qū)別在哪里答：外連接是指把舍棄的元組也保存在結(jié)果關(guān)系中，而在其他屬性上填空值。內(nèi)連接是指只有滿足連接條件的元組才能作為結(jié)果輸出。2）使用子查詢需要注意哪些事項(xiàng)答：a. 一個(gè)子查詢必須放在圓括號(hào)中。b.子查詢的select語(yǔ)句中不能使用order by子句，order by 子句只能對(duì)最終查詢結(jié)果排序。 c.帶有In謂詞

16、的子查詢中，當(dāng)In中為單一值時(shí)可用“=”代替，否則不可。 d.帶有比較運(yùn)算符的子查詢，將子查詢放在比較條件的右邊以增加可讀性。 f.子查詢返回單值時(shí)可以用比較運(yùn)算符，但返回多值時(shí)要用Any或All謂詞修飾，而使用Any或All時(shí)必須同時(shí)使用比較運(yùn)算符。例Any,!=Allg.帶有Exists謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值True或邏輯假值False。使用存在量詞Exists后，若內(nèi)層查詢結(jié)果非空，則外層的where子句返回真值，否則返回假值；與Exists相對(duì)應(yīng)的是Not Exists，使用Not Exists時(shí)，若內(nèi)層查詢結(jié)果為空，則外層的where子句返回真值，否則返回假值。由

17、Exists引出的子查詢，其目標(biāo)列表達(dá)式通常用*，因?yàn)閹xists的子查詢只返回真值或假值，給出列名無實(shí)際意義。一些帶Exists或Not Exists的子查詢不能被其他形式的子查詢等價(jià)替換，但所有帶In謂詞，比較運(yùn)算符，Any和All謂詞的子查詢都能用帶Exists謂詞的子查詢等價(jià)替換。四、 實(shí)驗(yàn)小結(jié)通過本實(shí)驗(yàn)學(xué)會(huì)了如何使用分組與匯總函數(shù)，對(duì)連接查詢和子查詢有了深入的了解。實(shí)驗(yàn)四視圖和索引一、實(shí)驗(yàn)?zāi)康模海?）掌握建立、維護(hù)索引的方法（2）掌握建立、使用視圖的方法二、實(shí)驗(yàn)內(nèi)容：1使用Create View Wizard創(chuàng)建視圖通過企業(yè)管理器的Create View Wizard創(chuàng)建視圖1）在

18、企業(yè)管理器中，單擊菜單“工具”下面的“向?qū)А?）展開“數(shù)據(jù)庫(kù)”后，雙擊下面的“創(chuàng)建視圖向?qū)А?）單擊“下一步”后，選擇數(shù)據(jù)庫(kù)OrderMag，選擇表order4）選擇字段Ono, Cno, Pno, Onum。5）輸入條件語(yǔ)句WHERE Onum 1000，6）輸入視圖名稱v_order8）在企業(yè)管理器中的“數(shù)據(jù)庫(kù)” OrderMag視圖下查看視圖v_order。9）在查詢分析器中輸入并執(zhí)行語(yǔ)句SELECT * FROM v_order10）結(jié)果如何？顯示的字段是否為前面自己定義的字段？答，顯示出滿足條件的結(jié)果，顯示的字段為當(dāng)前自己定義的字段。11）刪除視圖v_order。Drop View

19、v_order2. 在查詢分析器中創(chuàng)建視圖1) 建立一個(gè)視圖，包括訂單號(hào)、零件名稱、顧客名稱、訂單金額等信息create view is_osc(Ono,Pname,Cname,Osum) as select Order1.Ono,Store.Pname,Customer.Cname,Order1.Osum from Order1,Customer,Storewhere Order1.Pno=Store.Pno and Order1.Cno=Customer.Cno2）建立一個(gè)視圖，查詢訂單金額大于10000元的大客戶信息。create view is_c as SELECT Cname, O

20、sumFROM is_oscWHERE (Osum 10000)3）建立一個(gè)視圖，查詢每個(gè)顧客簽訂訂單的總金額create view is_sumas select Cno,sum(Osum)Ssum from Order1 group by Cno3. 察看系統(tǒng)提供的示例數(shù)據(jù)庫(kù)pubs上有哪些索引，是什么類型的，列表記錄下來。表名索引名索引類型authorsaunmind唯一employeeemployee_ind唯一royschedtitleidind唯一salestitleidind唯一titleauthorauidind唯一titleidind唯一titlestitleind唯一4為O

21、rderMag數(shù)據(jù)庫(kù)設(shè)計(jì)索引，并建立到表上。Create unique index cnoind on Customer(Cno);Create unique index onoind on Orders(Ono);Create unique index pnoind on Store(pno);三、完成實(shí)驗(yàn)報(bào)告并回答問題1) 視圖的建立有哪些需注意的問題答：A在CREATE VIEW語(yǔ)句中，不能包括ORDER BY，COMPUTE或者COMPUTE BY 子句也不能出現(xiàn)INTO關(guān)鍵字 B創(chuàng)建視圖所參考基表的列數(shù)最多為1024列C創(chuàng)建視圖不能參考臨時(shí)表 D盡量避免使用外連接創(chuàng)建視圖 E在一個(gè)批

22、處理語(yǔ)句中，CREATE VIEW 語(yǔ)句不能和其他TRANSACT-SQL語(yǔ)句混合使用2）索引的作用是什么？有什么分類？每類有什么特點(diǎn)？答：1）創(chuàng)建索引可以大大提高系統(tǒng)的性能。第一，通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫(kù)表中每一行數(shù)據(jù)的唯一性。第二，可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。第三，可以加速表和表之間的連接，特別是在實(shí)現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。第四，在使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)，同樣可以顯著減少查詢中分組和排序的時(shí)間。第五，通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。 2）根據(jù)索引的順序與數(shù)據(jù)表的物理順序是否相同，可以把索引分成兩種

23、類型。一種是數(shù)據(jù)表的物理順序與索引順序相同的聚簇索引，另一種是數(shù)據(jù)表的物理順序與索引順序不相同的非聚簇索引。3）聚集索引（1）首先指出一個(gè)誤區(qū)，主鍵并不一定是聚集索引，只是在SQL SERVER中，未明確指出的情況下，默認(rèn)將主鍵定義為聚集，而ORACLE中則默認(rèn)是非聚集，因?yàn)镾QL SERVER中的ROWID未開放使用。（2）聚集索引適合用于需要進(jìn)行范圍查找的列，因?yàn)榫奂饕娜~子節(jié)點(diǎn)存放的是有序的數(shù)據(jù)行，查詢引擎可根據(jù)WHERE中給出的范圍，直接定位到兩端的葉子節(jié)點(diǎn)，將這部分節(jié)點(diǎn)頁(yè)的數(shù)據(jù)根據(jù)鏈表順序取出即可；（3）聚集索引盡量建立在值不會(huì)發(fā)生變更的列上，否則會(huì)帶來非聚集索引的維護(hù)；（4）盡量

24、在建立非聚集索引之前建立聚集索引，否則會(huì)導(dǎo)致表上所有非聚集索引的重建；（5）聚集索引應(yīng)該避免建立在數(shù)值單調(diào)的列上，否則可能會(huì)造成IO的競(jìng)爭(zhēng)，以及B樹的不平衡，從而導(dǎo)致數(shù)據(jù)庫(kù)系統(tǒng)頻繁的維護(hù)B樹的平衡性。聚集索引的列值最好能夠在表中均勻分布。2、非聚焦索引（1）非聚集索引適合用于需要進(jìn)行等值查找的列，因?yàn)榉蔷奂饕娜~子節(jié)點(diǎn)存放的是有序的索引列與書簽的映射行，查詢引擎可根據(jù)WHERE中給出的值，得到書簽，繼而定位到數(shù)據(jù)行；（2）覆蓋索引（Covering Index），是非聚集索引的一種特殊且高效的應(yīng)用，就是將需要返回的數(shù)據(jù)列設(shè)計(jì)成組合索引，在SELECT時(shí)只查詢索引中存在的數(shù)據(jù)列，這樣就能形成索

25、引覆蓋，因?yàn)樗饕兄幸呀?jīng)包含了想到的數(shù)據(jù)，不需要再進(jìn)行書簽查找；在SQL SERVER 2005及以上版本中，提供了INCLUDED關(guān)鍵字，可以在非聚集索引中包含更多列，也是覆蓋索引的一個(gè)有效引申；（3）非聚集索引建立在值具有單調(diào)性的列上，比如：自增列（單調(diào)遞增），可以減少索引的外部碎片及索引結(jié)構(gòu)的維護(hù)；四、 實(shí)驗(yàn)小結(jié)通過本實(shí)驗(yàn)，學(xué)會(huì)了如何建立，刪除視圖，并對(duì)索引有了一定的了解和掌握。實(shí)驗(yàn)五用戶自定義函數(shù)（2學(xué)時(shí)）實(shí)驗(yàn)?zāi)康模簩W(xué)習(xí)、掌握用戶自定義函數(shù)的建立和使用實(shí)驗(yàn)內(nèi)容：1、 創(chuàng)建自定義函數(shù)創(chuàng)建一個(gè)用戶自定義函數(shù)，并測(cè)試、查看函數(shù)返回值。1）輸入并執(zhí)行下面語(yǔ)句USE NorthwindGOCRE

26、ATE FUNCTION fn_TaxRate (ProdID INT)RETURNS numeric(5,4)ASBEGINRETURN(SELECT CASE CategoryID WHEN 1 THEN 1.10 WHEN 2 THEN 1 WHEN 3 THEN 1.10 WHEN 4 THEN 1.05 WHEN 5 THEN 1 WHEN 6 THEN 1.05 WHEN 7 THEN 1 WHEN 8 THEN 1.05 ENDFROM Products WHERE ProductID = ProdID)ENDGO2）此函數(shù)中輸入變量是什么？返回值類型？如何定義的返回值？答：此函

27、數(shù)輸入變量是ProdID，返回值類型是numeric(5,4)返回值是這樣定義的SELECT CASE CategoryID WHEN 1 THEN 1.10 WHEN 2 THEN 1 WHEN 3 THEN 1.10 WHEN 4 THEN 1.05 WHEN 5 THEN 1 WHEN 6 THEN 1.05 WHEN 7 THEN 1 WHEN 8 THEN 1.05 ENDFROM Products WHERE ProductID = ProdID3）輸入并執(zhí)行語(yǔ)句測(cè)試函數(shù)SELECT ProductName, UnitPrice,Northwind.dbo.fn_TaxRate(P

28、roductID) AS TaxRate,UnitPrice * Northwind.dbo.fn_TaxRate(ProductID) AS PriceWithTaxFROM Products查看結(jié)果注意： 函數(shù)可以在Select子句后面調(diào)用。2、返回值為多值的自定義函數(shù)創(chuàng)建函數(shù)返回多列多值。1）輸入并執(zhí)行下面語(yǔ)句USE NorthwindGOCREATE FUNCTION fn_LargeFreight (FreightAmt money)RETURNS TABLEAS RETURN( SELECT S.ShipperID, S.CompanyName, O.OrderID, O.Ship

29、pedDate, O.Freight FROM Shippers AS S JOIN Orders AS O ON S.ShipperID = O.ShipVia WHERE O.Freight FreightAmt)函數(shù)中輸入變量是什么？返回值類型？如何定義的返回值？答：函數(shù)中輸入變量是FreightAmt，返回值類型是moneySELECT S.ShipperID, S.CompanyName, O.OrderID, O.ShippedDate, O.Freight FROM Shippers AS S JOIN Orders AS O ON S.ShipperID = O.ShipVia

30、 WHERE O.Freight FreightAmt2）輸入并執(zhí)行語(yǔ)句測(cè)試函數(shù)SELECT * FROM fn_LargeFreight(600)查看結(jié)果3、返回值為多值的自定義函數(shù)本實(shí)驗(yàn)創(chuàng)建的函數(shù)也是返回多列多值，注意與上面實(shí)驗(yàn)的差別。1）輸入并執(zhí)行下面語(yǔ)句USE NorthwindGOCREATE FUNCTION fn_FindReports (InEmployeeID char(5)RETURNS reports TABLE(EmployeeID char(5) PRIMARY KEY,Name nvarchar(40) NOT NULL,Title nvarchar(30),Mgr

31、EmployeeID int,processed tinyint default 0)ASBEGININSERT reportsSELECT EmployeeID, Name = FirstName + + LastName, Title, ReportsTo, 0FROM EMPLOYEES WHERE ReportsTo = InEmployeeIDRETURN ENDGO此函數(shù)中輸入變量是什么？返回值類型是什么？如何定義的返回值？答：此函數(shù)中輸入變量是InEmployeeID，返回值類型是TABLE，這樣定義的返回值INSERT reportsSELECT EmployeeID, Nam

32、e = FirstName + + LastName, Title, ReportsTo, 0FROM EMPLOYEES WHERE ReportsTo = InEmployeeID3）輸入并執(zhí)行語(yǔ)句測(cè)試函數(shù)SELECT EmployeeID, Name, Title, MgrEmployeeID FROM dbo.fn_FindReports(5)查看結(jié)果。4設(shè)計(jì)一個(gè)函數(shù)，在OrderMag數(shù)據(jù)庫(kù)中，輸入零件類別，返回該類別零件的平均存量、最高存量和該類零件的總數(shù)量。CREATE FUNCTION fund_Pno (Pptype char(10)RETURNS TABLEAS RETUR

33、N( SELECT avg(Pnum) Avgp,max(Pnum) Maxp,sum(Pnum) Sump FROM Store WHERE Ptype= Pptype)select * from fund_Pno(傳動(dòng))5設(shè)計(jì)一個(gè)函數(shù)，在OrderMag數(shù)據(jù)庫(kù)中，輸入訂單號(hào)，返回該訂單所涉及的零件名稱和類別。USE OrderMagGOCREATE FUNCTION fpname (fOno varchar(60)RETURNS TABLEAS RETURN( select O.Ono,S.Pname,S.Ptype from Store AS S join Orders As O On

34、O.Pno=S.Pno where O.Ono= fOno)select * from fpname(O1)6設(shè)計(jì)一個(gè)函數(shù)，在OrderMag數(shù)據(jù)庫(kù)中，根據(jù)零件庫(kù)存量的大小，大于500的認(rèn)為是充足，在100-500之間的是均衡，小于100的為面臨缺貨。USE OrderMagGOCREATE FUNCTION fpnum4()RETURNS TableASRETURN(SELECT Pnum Range= CASE WHEN Pnum500 THEN 充足 WHEN Pnum BETWEEN 100 and 500 THEN 均衡 WHEN Pnum100 THEN 面臨缺貨 ENDFROM

35、Store )select * from fpnum4()7設(shè)計(jì)一個(gè)函數(shù)，根據(jù)輸入的數(shù)值，計(jì)算從1加到該數(shù)的和（如輸入5，則計(jì)算1+2+3+4+5=15,輸出為15）。create function sumn(num int)returns intasbegin declare i int; declare s int;set i=1;set s=0; while (i=num) begin set s=s+i; set i=i+1; end return sendprint 從1加到該數(shù)的和是：+cast(dbo.sumn(5) as varchar)8完成實(shí)驗(yàn)報(bào)告（1）用戶自定義函數(shù)在定義

36、與使用上有何需要注意的問題？與存儲(chǔ)過程有何不同？答：在SQL SERVER中調(diào)用自定義函數(shù)時(shí)，必須在自定義函數(shù)前加上創(chuàng)建此函數(shù)的用戶存儲(chǔ)過程:存儲(chǔ)過程可以使得對(duì)數(shù)據(jù)庫(kù)的管理、以及顯示關(guān)于數(shù)據(jù)庫(kù)及其用戶信息的工作容易得多。存儲(chǔ)過程是 SQL 語(yǔ)句和可選控制流語(yǔ)句的預(yù)編譯集合，以一個(gè)名稱存儲(chǔ)并作為一個(gè)單元處理。存儲(chǔ)過程存儲(chǔ)在數(shù)據(jù)庫(kù)內(nèi)，可由應(yīng)用程序通過一個(gè)調(diào)用執(zhí)行，而且允許用戶聲明變量、有條件執(zhí)行以及其它強(qiáng)大的編程功能。存儲(chǔ)過程可包含程序流、邏輯以及對(duì)數(shù)據(jù)庫(kù)的查詢。它們可以接受參數(shù)、輸出參數(shù)、返回單個(gè)或多個(gè)結(jié)果集以及返回值。 可以出于任何使用 SQL 語(yǔ)句的目的來使用存儲(chǔ)過程，它具有以下優(yōu)點(diǎn)： 1、

37、可以在單個(gè)存儲(chǔ)過程中執(zhí)行一系列 SQL 語(yǔ)句。2、可以從自己的存儲(chǔ)過程內(nèi)引用其它存儲(chǔ)過程，這可以簡(jiǎn)化一系列復(fù)雜語(yǔ)句。3、存儲(chǔ)過程在創(chuàng)建時(shí)即在服務(wù)器上進(jìn)行編譯，所以執(zhí)行起來比單個(gè) SQL 語(yǔ)句快。用戶定義函數(shù)：Microsoft SQL Server 2000 允許創(chuàng)建用戶定義函數(shù)。與任何函數(shù)一樣，用戶定義函數(shù)是可返回值的例程。根據(jù)所返回值的類型，每個(gè)用戶定義函數(shù)可分成以下三個(gè)類別： 1、返回可更新數(shù)據(jù)表的函數(shù) 如果用戶定義函數(shù)包含單個(gè) SELECT 語(yǔ)句且該語(yǔ)句可更新，則該函數(shù)返回的表格格式結(jié)果也可以更新。2、返回不可更新數(shù)據(jù)表的函數(shù) 如果用戶定義函數(shù)包含不止一個(gè) SELECT 語(yǔ)句，或包含一

38、個(gè)不可更新的 SELECT 語(yǔ)句，則該函數(shù)返回的表格格式結(jié)果也不可更新。3、返回標(biāo)量值的函數(shù) 用戶定義函數(shù)可以返回標(biāo)量值。四、 實(shí)驗(yàn)小結(jié)通過本實(shí)驗(yàn)掌握了自定義函數(shù)的建立及用法實(shí)驗(yàn)六安全性管理（2學(xué)時(shí)） 一、實(shí)驗(yàn)?zāi)康模?1) 了解SQL Server 的認(rèn)證模式，安全管理的主要內(nèi)容和方法(2) 掌握用戶管理、訪問權(quán)限管理的基本方法二、實(shí)驗(yàn)內(nèi)容：SQL Server、數(shù)據(jù)庫(kù)、對(duì)象等不同級(jí)別的安全性設(shè)置與管理。1設(shè)置身份驗(yàn)證模式1）在SSMS中，選擇服務(wù)器實(shí)例2）單擊右鍵，選擇“屬性”，在屬性對(duì)話框中選擇“安全性”選項(xiàng)卡3）將身份驗(yàn)證模式設(shè)定為“僅Windows ”4）單擊“確定”，SQL SERV

39、ER重新啟動(dòng)服務(wù)后更改驗(yàn)證模式5）重新啟動(dòng)服務(wù)，登陸SSMS時(shí)選擇SQL SERVER驗(yàn)證，然后輸入sa用戶登錄6）能否登錄？為什么？不能，因?yàn)榇藭r(shí)的訪問權(quán)限僅限于Windows7）重復(fù)1-3步驟，將身份驗(yàn)證模式設(shè)定為混合模式。注意：實(shí)驗(yàn)完成后應(yīng)該將其重新設(shè)定為混合認(rèn)證模式；只有重新啟動(dòng)服務(wù)，驗(yàn)證模式才生效。2添加SQL SERVER登錄帳號(hào)添加SQL SERVER登錄帳號(hào)TestSQL。1）在SSMS中，展開“安全性”，選擇“登錄名”，選擇“新建登錄名”2）選擇SQL Server身份驗(yàn)證，3）輸入用戶名TestSQL及密碼TestSQL，單擊“確定”4）打開數(shù)據(jù)庫(kù)引擎查詢頁(yè)，輸入登錄帳號(hào)T

40、estSQL和密碼TestSQL，單擊“確定”，此時(shí)數(shù)據(jù)庫(kù)引擎查詢頁(yè)是以TestSQL身份登錄。5）輸入并執(zhí)行查詢語(yǔ)句SELECT * FROM master.dbo.sysDatabases6）在查詢頁(yè)中輸入“Use OrderMag”T-SQL語(yǔ)句，切換當(dāng)前數(shù)據(jù)庫(kù)。7）切換是否成功? 為什么?答：不成功，因?yàn)橄到y(tǒng)沒有對(duì)TestSQL開放訪問數(shù)據(jù)庫(kù)的權(quán)限。3、添加數(shù)據(jù)庫(kù)用戶帳號(hào)授權(quán)SQL SERVER登錄帳號(hào)TestSQL訪問數(shù)據(jù)庫(kù)OrderMag。1）在SSMS中，展開數(shù)據(jù)庫(kù)OrderMag中的“安全性”，選擇右鍵單擊“用戶”，選擇“新建用戶”2）在“登錄名”中選擇TestSQL，然后單擊

41、“確認(rèn)”3）打開數(shù)據(jù)庫(kù)引擎查詢頁(yè)，輸入登錄帳號(hào)TestSQL和密碼TestSQL，單擊“確認(rèn)”。4）此時(shí)查詢就是以TestSQL身份登錄5）在查詢頁(yè)中輸入“OrderMag”T-SQL語(yǔ)句，切換當(dāng)前數(shù)據(jù)庫(kù)。6）切換是否成功？為什么？答，不成功，因?yàn)殡m然對(duì)用戶開放了數(shù)據(jù)庫(kù)，但用戶對(duì)數(shù)據(jù)庫(kù)中的表仍沒有訪問權(quán)限。注意：User Name與Login的名稱可以相同，也可以不同，但是建議采用相同的名稱以方便維護(hù)管理。4、給角色分配語(yǔ)句權(quán)限基于自定義角色分配語(yǔ)句許可權(quán)限。在完成以上練習(xí)的基礎(chǔ)上完成此練習(xí)。1）使用TestSQL帳號(hào)登錄到查詢分析器中2）將當(dāng)前數(shù)據(jù)庫(kù)切換到OrderMag，執(zhí)行語(yǔ)句SELEC

42、T * FROM Customer3）執(zhí)行結(jié)果為什么?拒絕了對(duì)對(duì)象 Customer（數(shù)據(jù)庫(kù) OrderMag，所有者 dbo）的 SELECT 權(quán)限，沒有授予TestSQL用戶對(duì)OrderMag數(shù)據(jù)庫(kù)中表的SELECT 權(quán)限。4）在SSMS中，雙擊角色DBRole，單擊“權(quán)限”按鈕5）在表Customer所對(duì)應(yīng)的行中，選擇“Select”6）再使用TestSQL帳號(hào)登錄到查詢分析器中7）將當(dāng)前數(shù)據(jù)庫(kù)切換到OrderMag，執(zhí)行語(yǔ)句SELECT * FROM Customer8）執(zhí)行結(jié)果?為什么?答：獲得Customer表中的信息，因?yàn)橛脩鬞estSQL對(duì)表Customer有查詢的權(quán)限5、給登

43、錄帳號(hào)分配固定服務(wù)器角色給帳號(hào)分配固定服務(wù)器角色，讓帳號(hào)有管理SQL Server權(quán)限。1） 創(chuàng)建一個(gè)新的SecurityAdmin登錄帳號(hào)2）雙擊SecurityAdmin帳號(hào)，選擇“服務(wù)器角色”選項(xiàng)卡3）選擇Security Administrators固定服務(wù)器角色，“確定”4）SecurityAdmin擁有什么權(quán)限？登錄，查看master數(shù)據(jù)庫(kù)6、給用戶帳號(hào)分配固定數(shù)據(jù)庫(kù)角色給用戶帳號(hào)分配固定數(shù)據(jù)庫(kù)角色，讓帳號(hào)有備份數(shù)據(jù)庫(kù)的權(quán)限。1）創(chuàng)建新的登錄帳號(hào)BackupAdmin2）在數(shù)據(jù)庫(kù)OrderMag上創(chuàng)建用戶帳號(hào)BackupAdmin3）BackupAdmin帳號(hào)，4）選擇db_bac

44、kupoperator固定數(shù)據(jù)庫(kù)角色，單擊“確定”。5）BackupAdmin擁有什么權(quán)限？備份數(shù)據(jù)庫(kù)權(quán)限7完成實(shí)驗(yàn)報(bào)告（1）描述系統(tǒng)的安全機(jī)制。答：在計(jì)算機(jī)系統(tǒng)中，安全措施是一級(jí)一級(jí)層層設(shè)置的。例如用戶在進(jìn)入計(jì)算機(jī)系統(tǒng)時(shí)，系統(tǒng)首先根據(jù)輸入的用戶標(biāo)識(shí)進(jìn)行身份鑒定，只有合法的的用戶才準(zhǔn)許進(jìn)入計(jì)算機(jī)系統(tǒng)，對(duì)已進(jìn)入系統(tǒng)的用戶，DBMS還要進(jìn)行存取控制，只允許用戶執(zhí)行合法操作。操作系統(tǒng)一級(jí)也會(huì)有自己的保護(hù)措施，數(shù)據(jù)最后還可以以密碼形式存儲(chǔ)到數(shù)據(jù)庫(kù)中。（2）用戶帳號(hào)的概念，用戶帳號(hào)與登錄帳號(hào)的區(qū)別。答：當(dāng)用戶通過身份驗(yàn)證，以某個(gè)登錄帳號(hào)連接到SQL Server以后，還必須取得相應(yīng)數(shù)據(jù)庫(kù)的“訪問許可”，

45、才能使用該數(shù)據(jù)庫(kù)。這種用戶訪問數(shù)據(jù)庫(kù)權(quán)限的設(shè)置是通過用戶帳號(hào)來實(shí)現(xiàn)的。登錄帳號(hào)是屬于服務(wù)器的層面。而登錄者要使用服務(wù)器中的數(shù)據(jù)庫(kù)數(shù)據(jù)時(shí)，必須要有用戶帳號(hào)。就如同在公司門口先刷卡進(jìn)入大門（登錄服務(wù)器），然后再拿鑰匙打開自己的辦公室門（進(jìn)入數(shù)據(jù)庫(kù)）一樣。（3）固定服務(wù)器角色的權(quán)限、固定數(shù)據(jù)庫(kù)角色的權(quán)限的概念。答：固定服務(wù)器角色的權(quán)限針對(duì)數(shù)據(jù)庫(kù)服務(wù)器，固定數(shù)據(jù)庫(kù)角色的權(quán)限針對(duì)具體某一數(shù)據(jù)庫(kù)（用戶）固定數(shù)據(jù)庫(kù)角色是在SQL Server每個(gè)數(shù)據(jù)庫(kù)中都存在的系統(tǒng)預(yù)定義用戶組。它們提供了對(duì)數(shù)據(jù)庫(kù)常用操作的權(quán)限。系統(tǒng)管理員可以將用戶加入這些角色中，固定數(shù)據(jù)庫(kù)角色的成員也可將其他用戶添加到本角色中。但固定數(shù)據(jù)

46、庫(kù)角色本身不能被添加、修改或刪除。四、 實(shí)驗(yàn)小結(jié)通過本實(shí)驗(yàn)知道了SQL Server 的認(rèn)證模式，安全管理的主要內(nèi)容和方法及用戶管理、訪問權(quán)限管理的基本方法實(shí)驗(yàn)七備份與恢復(fù)（2學(xué)時(shí)）一、實(shí)驗(yàn)?zāi)康模?1) 了解MS SQL Server 提供的備份、恢復(fù)功能(2) 熟練掌握數(shù)據(jù)庫(kù)、日志備份的方法(3) 熟練掌握數(shù)據(jù)庫(kù)恢復(fù)的方法二、實(shí)驗(yàn)內(nèi)容：在備份設(shè)備、文件名上進(jìn)行完全、差異數(shù)據(jù)庫(kù)備份；數(shù)據(jù)庫(kù)恢復(fù)。1、創(chuàng)建備份設(shè)備建立備份設(shè)備 nw1,nw2sp_addumpdevice disk,Nw1 ,d:nw1.baksp_addumpdevice disk,Nw2 ,d:nw2.bak2、備份數(shù)據(jù)庫(kù)使用

47、兩種方法進(jìn)行完全數(shù)據(jù)庫(kù)備份。方法一：在SSMS中進(jìn)行全庫(kù)備份1）在SSMS中右擊Northwind數(shù)據(jù)庫(kù)，選擇“任務(wù)” “備份數(shù)據(jù)庫(kù)”。 2）在備份窗口中的“備份”中選擇“數(shù)據(jù)庫(kù)-完全”選項(xiàng)。3）在目標(biāo)中，先單擊“添加”，選擇“備份設(shè)備”中的Nw1備份設(shè)備。4）單擊“確定”開始備份。方法二：使用T-SQL語(yǔ)句進(jìn)行全庫(kù)備份1）在查詢頁(yè)中輸入并執(zhí)行語(yǔ)句backup database northwind to nw2 with init2）在資源管理器中查看D:Nw2.bak是否創(chuàng)建？已創(chuàng)建3、查看備份設(shè)備查看備份設(shè)備中的備份內(nèi)容，了解備份信息。1）在SSMS中展開數(shù)據(jù)庫(kù)2）單擊備份，添加備份設(shè)備N

48、w13）右擊備份設(shè)備Nw14）單擊查看內(nèi)容來查看備份內(nèi)容。5）同樣的方法查看Nw2備份設(shè)備。實(shí)驗(yàn)是否成功？是4、執(zhí)行差異備份在SSMS中進(jìn)行數(shù)據(jù)庫(kù)的 差異備份1）選擇Northwind數(shù)據(jù)庫(kù)，選擇“任務(wù)”下面的“備份”2）在備份窗口中的備份中選擇“數(shù)據(jù)庫(kù)-差異”選項(xiàng)3）在備份目的中，先單擊“添加”，選擇“磁盤上的目標(biāo)中“備份設(shè)備Nw1”4）在選項(xiàng)頁(yè)中選擇“追加到媒體集”選項(xiàng)，單擊“確定”開始進(jìn)行差異備份。5）查看備份設(shè)備中的內(nèi)容5、恢復(fù)完全數(shù)據(jù)庫(kù)備份利用上述實(shí)驗(yàn)建立的備份，使用兩種方法從備份設(shè)備中恢復(fù)完全數(shù)據(jù)庫(kù)備份。方法一：在SSMS中進(jìn)行實(shí)驗(yàn)1）在SSMS中右擊Northwind數(shù)據(jù)庫(kù)創(chuàng)建表

49、table12）在SSMS中右擊Northwind數(shù)據(jù)庫(kù)，選擇“任務(wù)”下面的“還原”3）在還原窗口中的下方列表中顯示兩個(gè)備份信息，第一個(gè)為完全備份，第二個(gè)為差異備份。4）只選擇第一個(gè)完全備份，單擊“確定”。5）表table1是否存在？如果存在，右擊Northwind數(shù)據(jù)庫(kù)選擇“刷新”，這時(shí)表Ttable1是否存在？存在，不存在方法二：使用T-SQL語(yǔ)句進(jìn)行實(shí)驗(yàn)1）在SSMS中右擊Northwind數(shù)據(jù)庫(kù)創(chuàng)建表table22）在查詢分析器中輸入并執(zhí)行語(yǔ)句RESTORE DATABASE Northwind FROM Nw1 WITH FILE=1,RECOVERY,REPLACE3）在SSMS中查看表table2是否存在？如果存在，右擊