1、 實驗十 函數一、實驗目的與要求理解什么是標量函數、內嵌表值函數及多語句表值函數。熟練掌握標量函數、內嵌表值函數、多語句表值函數的定義和調用。基表如下所示：二、實驗內容和步驟1、函數的定義對于CPXS數據庫，定義完成如下功能的函數：（1）據產品名稱，查詢該產品的相關信息；（函數名為FU_CP）use cpxs1gocreate function FU_CP(productname char(30)returns tableasreturn(select * from CPXSB right join XSS on CPXSB.客戶編號=XSS.客戶編號 right join CP on CPX

2、SB.產品編號=CP.產品編號where 產品名稱=productname)Gouse cpxs1goselect * from FU_CP(彩色電視機)go（2）按某年某季度統(tǒng)計給定產品名稱的銷售數量及銷售金額；分別用名為FU1_CPXS內嵌表值函數和名為FU2_CPXS的多語句表值函數。use cpxs1gocreate function FU12_CPXS(year int ,quarter int, cpmc char(10)returns tableasreturn (select 產品名稱, sum(數量) as 銷售數量, sum(銷售額) as 銷售總額 from cpxsb,

3、cpwhere cpxsb.產品編號=cp.產品編號 and 產品名稱=cpmc and datepart(year ,銷售日期)=year and datepart(quarter,銷售日期)=quarter) Gouse cpxs1gocreate function FU2_SPXS(year int ,quarter int ,cpmc char(10)returns xsqk table (產品名稱 char (10), 銷售數量 int , 銷售金額 int)asbegin insert into xsqkselect 產品名稱,sum(銷售額)as 銷售總額,sum(數量)as 銷

4、售數量from cpxsb,cpwhere cpxsb.產品編號=cp.產品編號 and 產品名稱=cpmc and datepart (year ,銷售日期)=year and datepart (quarter, 銷售日期)=quartergroup by 產品名稱return endgo（3）根據銷售商名稱，統(tǒng)計其在某年某季度內銷售商品名稱、數量及金額。（函數名為FU3_CPXS）use cpxs1gocreate function FU3_CPXS(xssmc char(10),year int,quarter int) returns tableasreturn(select 產品名

5、稱, sum(數量) as 銷售數量, sum(銷售額) as 銷售總額 from CPXSB,CP,XSS where CPXSB.產品編號=CP.產品編號 and CPXSB.客戶編號=XSS.客戶編號 and 客戶名稱=xssmc and datepart(year,銷售日期)=year and datepart(quarter,銷售日期)=quarter group by 產品名稱)Go函數的調用（1）對函數FU_CP，查詢產品名稱為“mp3”的產品情況；use cpxs1goselect * from FU_CP(mp3)g對函數FU1_CPXS，查詢2004年第1季度彩色電視機的銷

6、售數量和銷售金額；use cpxs1goselect * from FU321_CPXS (2004,1,彩電電視機)（3）對函數FU2_CPXS，查詢2004年第1季度洗衣機的銷售數量和銷售金額；select * from FU1_CPXS(2004,1,洗衣機)（4）對函數FU3_CPXS，查詢廣電公司2004年第1季度銷售的產品名稱、銷售數量和銷售金額。三、分析與回答試說明內嵌表值函數、多語句表值函數的聯系與區(qū)別答：內嵌表值函數返回的是一個表；而多語句表值函數的功能是試圖與存儲過程的組合，可以利用多語句表值函數返回一個表，表中的內容可由復雜的邏輯和多條SQL語句構建，可以在select語

7、句中句的from子句中使用多語句表值函數。實驗十一 索引、默認值約束和默認值對象實驗內容和步驟索引的創(chuàng)建和刪除對CP表，在產品名稱上定義一個唯一非聚簇的索引ind_cp。use cpxs1gocreate unique index ind_cp6 on cp (產品名稱)Go（2）先創(chuàng)建各客戶購買產品的情況VIEW1視圖，包括客戶編號、客戶名稱、產品編號、產品名稱、價格，購買日期、購買數量，然后在客戶編號+產品編號+購買日期定義一個唯一聚簇索引ind_view1。（請注意7個SET選項設置）。創(chuàng)建視圖如下：create view cp_gmqkselect xss.客戶編號,客戶名稱 , cp

8、.產品編號,產品名稱,價格 , cpxsb.銷售日期,數量from cp join cpxsb on cp.產品編號=cpxsb.產品編號 join xss on xss.客戶編號=cpxsb.客戶編號定義一個唯一聚簇索引ind_view1：use cpxs1gocreate unique clustered index ind_viewon cpxsb(客戶編號,產品編號,銷售日期)go2、索引的刪除刪除ind_view1索引use cpxs1godrop index cpxsb.ind_viewgo3、默認值約束的定義對庫存量字段添加默認值為0的約束def_kcluse cpxs1goal

9、ter table cpadd constraint def_kcldefault 0 for 庫存量go4、默認值約束的刪除刪除def_kcl默認值約束use cpxs1goalter table cpdrop constraint def_kclgo5、默認值對象的定義、使用和刪除（1）定義一值為0的默認值對象kcl_def，并將其綁定到CP表的庫存量字段；然后再刪除此默認值對象，請敘述該過程，并寫出相關SQL語句。create default kcl_defas 0goexec sp_bindefault kcl_def ,cp.庫存量go drop default kcl_defgo

10、sp_unbindefault cp.庫存量go （2） 用SQL命令定義一名為city的用戶自定義數據類型，要求char(6)，NULL，再定義一值為“北京”的默認值對象city_def，然后將city_def默認值對象綁定到city自定義數據類型，最后刪除city_def默認值對象，請敘述該過程，并寫出相關SQL語句。自定義數據類型：use cpxs1gosp_addtype city,char(6),nullgo綁定到自定義數據類型：use cpxs1gocreate default city_defas 北京goexec sp_bindefault city_def,citygo刪除c

11、ity_def默認對象：use cpxs1godrop default city_defgosp_unbindefault citygo三、分析與回答說明索引的概念和作用。索引的概念:數據庫中的索引是一個表中包含列表的列值，其中注明了表中包含值的行數據所在的存儲位置；其作用是加快數據的查詢。說明聚簇索引和非聚簇索引的含義和區(qū)別。聚集索引的概念：聚集索引定義了數據在表中存儲的物理順序，它是指表中數據行的的物理存儲順序與索引順序完全相同。聚集索引有上下兩層構成，上層為索引頁，包含表中的索引頁面，下層為數據頁。 非聚集索引的概念：非聚集索引不改變表中的物理存儲順序，數據與索引分開存儲。非聚集索引中僅

12、包含索引值和指向數據存儲位置的指針。索引中的項目按索引鍵值的順序存儲，而表中的信息按另一種順序存儲。區(qū)別在于：1：數據不按非聚集索引中的關鍵字值的順序排列和存儲；2：非聚集索引的葉級節(jié)點不是存放數據的數據頁實驗十二 數據完整性的實現1、實驗內容和步驟1、實體完整性的實現（1）對CP表、CPXSB表、XSS表，定義主鍵約束（用企業(yè)管理器和SQL語句分別實現，寫出相應過程和語句）。對cp表：use cpxs1goalter table CPadd constraint pk_cpprimary key (產品編號)go對xss表：use cpxs1goalter table XSSadd cons

13、traint pk_xssprimary key (客戶編號)go對cpxsb表: use cpxs1goalter table cpxsbadd constraint pk_cpxsbprimary key (產品編號)go（2）在CP表的產品名稱列定義一個唯一約束（用企業(yè)管理器和SQL語句分別實現，寫出相應過程和語句）。use cpxs1goalter table cpadd constraint uk_cpunique (產品名稱)go2、參照完整性的實現（1）利用企業(yè)管理器建立CP表與CPXSB之間的參照關系，當對主表CP表進行更新和刪除操作時，從表CPXSB采用NO ACTION方式

14、，寫出其過程。（2）利用SQL語句建立CPXSB與XSS表之間的參照關系，當對主表XSS表進行更新和刪除操作時，從表CPXSB采用CASCADE（級聯）方式，寫出其過程。3、域完整性的實現（1）在CP表的價格列上定義大于等于0的檢查（CHE約束。use cpxs1goalter table cpadd constraint ck_cpcheck(價格=0)go（2）定義一個大于等于0的規(guī)則check_kcl，并將其綁定到CP表的庫存量列，然后解除綁定，刪除該規(guī)則，請寫出相關SQL語句。use cpxs1gocreate rule check_kclas range like k=0 gosp_

15、bindrule check_kcl,cp.庫存量go先解除綁定use cpxs1gosp_unbindrule cp.庫存量go刪除該規(guī)則：use cpxs1godrop rule check_kclgo4、綜合訓練創(chuàng)建一個“學生檔案”數據庫，并用CREATE TABLE在該數據庫中創(chuàng)建“人事”表和“成績”兩個表，其中“人事”表包括學號、姓名、性別、電話號碼、家庭住址字段組成。“成績”表包括學號、選修課程名稱、成績三個字段。分別在“人事”表的學號字段設置主鍵約束，在“成績”表的學號字段設置外鍵約束，在成績字段設置大于等于0小于等于100的檢查約束，在家庭住址字段設置惟一約束，在性別字段設置默

16、認值為“男”。use cpxs1gocreate database 學生檔案go use 學生檔案create table 人事(學號 char(6) not null,姓名 varchar(10),性別 char(2),電話號碼 char(20),家庭住址 char(30),)use 學生檔案create table 成績(學號 char(6) not null,選修課程名稱 varchar(20),成績 int,)在“人事”表的學號字段設置主鍵約束：alter table 人事add constraint pk_rsprimary key (學號)在“成績”表的學號字段設置外鍵約束：use

17、 學生檔案alter table 成績add constraint fk_cjforeign key (學號) references 成績(學號)在成績字段設置大于等于0小于等于100的檢查約束use 學生檔案alter table 成績add constraint ck_rscheck (成績=0)在家庭住址字段設置惟一約束：use 學生檔案use 學生檔案alter table 人事add constraint uq_rsunique (家庭住址)go在性別字段設置默認值為“男”：use 學生檔案alter table 人事add constraint dt_rsdefault 男for

18、性別三、分析與回答在SQL Server中討論參照完整性，請體會如下含義：插入從表中記錄時：有限制和忽略兩種方式；刪除主表中的記錄時：有限制、忽略和級聯三種方式；修改主表中的主鍵時：有限制、忽略和級聯三種方式。試根據自身學習，分別說明其含義。實驗十三 存儲過程一、實驗目的與要求熟練掌握存儲過程的創(chuàng)建、調用和刪除。二、實驗內容和步驟對于CPXS數據庫，創(chuàng)建如下存儲過程： 1、無參存儲過程編寫一無參存儲過程用于查詢每個客戶購買產品的情況（包括客戶編號、產品編號、客戶名稱、產品名稱、價格、購買日期、購買數量），然后調用該存儲過程。use cpxs1gocreate procedure custome

19、r123 asselect CPXSB.客戶編號,CPXSB.產品編號,客戶名稱,產品名稱,價格,銷售日期,數量 from CPXSB join CP on CPXSB.產品編號=CP.產品編號 join XSS on CPXSB.客戶編號=XSS.客戶編號 gouse cpxs1goexecute customer123 go2、帶有參數的存儲過程編寫一加密存儲過程，查詢指定客戶購買產品的情況。并調用該存儲過程查詢客戶編號為“000002”的客戶購買情況。create procedure specialcus123456 cusnum char(20)asselect CPXSB.客戶編號,

20、CPXSB.產品編號,客戶名稱,產品名稱,價格,銷售日期,數量 from CPXSB left join CP on CPXSB.產品編號=CP.產品編號 left join XSS on CPXSB.客戶編號=XSS.客戶編號 where CPXSB.客戶編號=cusnumGouse cpxs1exec specialcus123456 000002go3、帶有通配符參數的存儲過程編寫一存儲過程，查詢指定產品的銷售情況。如果沒有提供參數，則查詢產品名稱中包含有“冰箱”的產品銷售情況。use cpxs1gocreate procedure cp_xsqk123產品名稱 char(10)=冰箱a

21、sselect * from CPXSBgo4、帶有OUTPUT參數的存儲過程編寫一存儲過程，查詢指定客戶在指定時間段內購買指定產品的數量，存儲過程中使用了輸入和輸出參數。并調用該存儲過程查詢名稱為“家電市場”的客戶在2004年購買“洗衣機”的數量。use cpxs1gocreate procedure special cusnum char(20), time datetime, produce char(16), num int outputAsselect num=數量 from CPXSB left join XSS on CPXSB.客戶編號=XSS.客戶編號 left join C

22、P on CPXSB.產品編號=CP.產品編號 where CPXSB.產品編號=time and 銷售日期=time and 產品名稱=produce Go查詢指定客戶在指定時間段內購買指定產品的數量，存儲過程中使用了輸入和輸出參數。并調用該存儲過程查詢名稱為“家電市場”的客戶在2004年購買“洗衣機”的數量。use cpxs1goExecute special 100001, 2004-03-18 00:00:00.000, 彩色電視機Go三、分析與回答試說明存儲過程的分類和特點。SQLsever支持5種形式的存儲過程： 1系統(tǒng)存儲過程 2本地存儲過程 3臨時存儲過程 4遠程存儲過程 5擴

23、展存儲過程存儲過程的特點是： 1：存儲過程在服務器端運行，實行速度快； 2：存儲過程執(zhí)行一次后，其執(zhí)行規(guī)劃就駐留在高速緩沖存儲器中，在以后的操作中，只需要從高速緩沖存儲器中調用已經編好的二進制代碼執(zhí)行即可，提高了系統(tǒng)的性能； 3：確保數據庫的安全，使用存儲過程可以完成所有數據庫的操作，并通過編程方式控制上述操作對數據庫信息的訪問權限； 4：自動完成需要與執(zhí)行的任務。存儲過程可以在系統(tǒng)啟動時自動執(zhí)行，完成一些需要與執(zhí)行的任務，而不必在系統(tǒng)啟動后在進行手工操作，大大方便了用戶的使用實驗十四 觸發(fā)器一、實驗目的與要求熟練掌握后觸發(fā)器和替代觸發(fā)器的區(qū)別；熟練掌握后觸發(fā)器和替代觸發(fā)器的創(chuàng)建。二、實驗內容

24、和步驟對于CPXS數據庫，完成如下各項： 1、編寫存儲過程，對產品銷售表進行插入操作，并通過觸發(fā)器保證插入時，產品編號與CP表中的對應字段一致，銷售商編號與銷售商表中對應字段一致。其中觸發(fā)器分別用后觸發(fā)器和替代觸發(fā)器實現。2、在CPXSB上創(chuàng)建一后觸發(fā)器，若對產品編號列和客戶編號列修改，則給出提示信息，并取消修改操作，用兩種方法實現。use cpxs1gocreate trigger tri_remindon CPXSBfor updateasif(columns_updated()&3)0 begin raiserror(正在對產品編號列和客戶編號列修改,19,1) rollback tra

25、nsaction endgo三、分析與回答試說明后觸發(fā)器和替代觸發(fā)器的區(qū)別。Aafter型觸發(fā)器和instead of型觸發(fā)器的區(qū)別： 對于after型觸發(fā)器在一種操作上可以建立多個觸發(fā)器；而instead of 型的觸發(fā)器在一種操作上只能創(chuàng)建一個觸發(fā)器；實驗十五 系統(tǒng)安全管理一、實驗目的與要求理解SQL Server中的安全管理機制。理解帳戶、用戶、服務器角色和數據庫角色的含義。熟練掌握Windows NT登錄帳戶和SQL Server登錄帳戶的建立與刪除。熟練掌握添加與刪除服務器角色成員。熟練掌握添加和刪除一個登錄帳戶為某個數據庫的用戶。熟練掌握添加和刪除數據庫角色。熟練掌握添加和刪除數據

26、庫角色成員。熟練掌握用戶、角色的授權、收回權限和拒絕權限。二、實驗內容和步驟1、創(chuàng)建Windows NT登錄帳戶在SQL Server中添加一個帳戶名為“WANG”的Windows NT登錄帳戶。第一步：創(chuàng)建windows2003的用戶”WANG”,截圖如下所示：第二步：將windowsNT用戶添加到sqlsever中：use cpxs1goexecute sp_grantlogin MAWEIFEIWANGgo2、創(chuàng)建SQL Server登錄帳戶在SQL Server中添加一個帳戶名為“LIU”，密碼為“123456”的SQL Server登錄帳戶。第一步:首先應將SQLsever的認證模式設置為混合模式，其截圖如下所示：第二步：通過命令創(chuàng)建SQLsever的登錄賬戶：use cpxs1goexecute sp_addlogin LIU,123456go 運行后進行更新，且結果如下所示：3、添加服務器角色成員將“WAN