數(shù)據(jù)庫應用（SQL Server 2008）課件 趙增敏 項目6-9 創(chuàng)建索引和視圖-管理數(shù)據(jù)安全

趙增敏等主編數(shù)據(jù)庫應用

（SQLServer2008）1項目1初識SQLServer20082項目2創(chuàng)建和管理數(shù)據(jù)庫3項目3創(chuàng)建和管理表4項目4操作數(shù)據(jù)庫數(shù)據(jù)5項目5檢索數(shù)據(jù)庫數(shù)據(jù)6項目6創(chuàng)建索引和視圖7項目7Transact-SQL程序設計8項目8創(chuàng)建存儲過程和觸發(fā)器9項目9管理數(shù)據(jù)安全CONTENTS目錄項目6

創(chuàng)建索引和視圖CONTENTS

目錄任務1認識索引任務2設計索引任務3在表中創(chuàng)建索引任務4在表中查看索引信息任務5從表中刪除索引任務6認識視圖任務7使用視圖設計器創(chuàng)建視圖任務8使用CREATEVIEW語句創(chuàng)建視圖任務9修改視圖定義任務10重命名視圖任務11查看視圖相關信息項目12通過視圖修改數(shù)據(jù)任務13刪除視圖任務1認識索引相關知識一、索引的基本概念索引是與表或視圖關聯(lián)的磁盤上的結構，可以加快從表或視圖中檢索行的速度。索引包含由表或視圖中的一列或多列生成的鍵，使SQLServer可以快速有效地查找與鍵值關聯(lián)的行。二、索引的類型聚集索引：根據(jù)數(shù)據(jù)行的鍵值在表或視圖中排序和存儲這些數(shù)據(jù)行。非聚集索引：具有獨立于數(shù)據(jù)行的結構。非聚集索引包含非聚集索引鍵值，并且每個鍵值項都有指向包含該鍵值的數(shù)據(jù)行的指針。其他索引類型：唯一索引；包含性列索引；索引視圖；全文索引；XML索引任務2設計索引相關知識一、索引設計準則數(shù)據(jù)庫準則；查詢準則；列準則二、設計聚集索引聚集索引基于數(shù)據(jù)行的鍵值在表內排序和存儲這些數(shù)據(jù)行。每個表只能有一個聚集索引，因為數(shù)據(jù)行本身只能按一個順序存儲。每個表幾乎都對列定義聚集索引來實現(xiàn)下列功能：可用于經(jīng)常使用的查詢；提供高度唯一性；可用于范圍查詢。三、設計非聚集索引非聚集索引包含索引鍵值和指向表數(shù)據(jù)存儲位置的行定位器。通常情況下，設計非聚集索引是為了改善經(jīng)常使用的、沒有建立聚集索引的查詢的性能。可以對表或索引視圖創(chuàng)建多個非聚集索引。四、設計唯一索引唯一索引能夠保證索引鍵中不包含重復的值，從而使表中的每一行從某種方式上具有唯一性。只有當唯一性是數(shù)據(jù)本身的特征時，指定唯一索引才有意義。使用多列唯一索引，索引能夠保證索引鍵中值的每個組合都是唯一的。任務3在表中創(chuàng)建索引任務實現(xiàn)USE學生成績;GOCREATENONCLUSTEREDINDEXix_student_snameON學生(姓名);GO相關知識在SQLServer2008中，當使用CREATETABLE或ALTERTABLE對列定義PRIMARYKEY或UNIQUE約束時，數(shù)據(jù)庫引擎將自動創(chuàng)建唯一索引，以強制PRIMARYKEY或UNIQUE約束的唯一性要求。也可以使用SQLServerManagementStudio對象資源管理器中的“新建索引”對話框或CREATEINDEX語句來創(chuàng)建獨立于約束的索引。使用表設計器創(chuàng)建索引使用CREATEINDEX語句創(chuàng)建索引任務4在表中查看索引信息任務實現(xiàn)EXECsp_helpindex學生;GOEXECsp_spaceused學生;GOSELECTINDEXPROPERTY(OBJECT_ID('學生'),'PK_學生_學號','IsPadIndex')ASIsPadIndex,INDEXPROPERTY(OBJECT_ID('學生'),'PK_學生_學號','IndexFillFactor')ASIndexFillFactor,INDEXPROPERTY(OBJECT_ID('學生'),'PK_學生_學號','IsClustered')ASIsClustered;GO相關知識使用sp_helpindex查看索引信息使用sp_spaceused查看索引使用的空間使用INDEXPROPERTY函數(shù)查看索引屬性任務5從表中刪除索引任務實現(xiàn)USEsample;。。。CREATENONCLUSTEREDINDEXindex1ONtable1(cola);CREATENONCLUSTEREDINDEXindex1ONtable2(cola);GODROPINDEXtable1.index1,table2.index1;相關知識使用對象資源管理器來刪除索引使用DROPINDEX語句從當前數(shù)據(jù)庫中刪除一個或多個索引任務6認識視圖相關知識一、視圖的基本概念視圖是一個虛擬表，其內容由選擇查詢定義。與真實的表一樣，視圖也包含一系列帶有名稱的列和行數(shù)據(jù)，但這些列和行數(shù)據(jù)來自由定義視圖的查詢所引用的表，并且是在引用視圖時動態(tài)生成的，而不是以數(shù)據(jù)值存儲集形式存在于數(shù)據(jù)庫中（索引視圖除外）。視圖的類型：標準視圖；索引視圖；分區(qū)視圖。二、視圖的用途和限制視圖通常用在以下3種場合。簡化數(shù)據(jù)操作。自定義數(shù)據(jù)。提高數(shù)據(jù)庫的安全性。任務7使用視圖設計器創(chuàng)建視圖任務實現(xiàn)展開數(shù)據(jù)庫，右擊“視圖”結點，選擇“新建視圖”命令。在“添加表”對話框中，依次選擇各個表，單擊“添加”，單擊“關閉”。在視圖設計器上部的“關系圖”窗格中，依次選擇各列。此時“SQL”窗格顯示出用于定義視圖的SQL語句。相關知識如果想使用視圖來簡化數(shù)據(jù)操作或提高數(shù)據(jù)庫的安全性，首先要按照需要在數(shù)據(jù)庫中創(chuàng)建視圖。在SQLServer2008中，可以使用SQLServerManagementStudio可視化數(shù)據(jù)庫工具創(chuàng)建視圖，也可以使用CREATEVIEW語句創(chuàng)建視圖。任務8使用CREATEVIEW語句創(chuàng)建視圖任務實現(xiàn)CREATEVIEW學生視圖ASSELECT系別名稱,專業(yè)名稱,學生.班級編號,學號,姓名,性別,入學日期

FROM學生INNERJOIN班級ON學生.班級編號=班級.班級編號

INNERJOIN系別ON班級.系別編號=系別.系別編號;GO相關知識CREATEVIEW語句用于創(chuàng)建一個視圖，可將該視圖作為一個虛擬表并以一種備用方式提供一個或多個表中的數(shù)據(jù)。語法格式語法說明使用須知任務9修改視圖定義任務實現(xiàn)CREATEVIEW教師視圖ASSELECT教師編號,姓名,性別,出生日期FROM教師;GOALTERVIEW教師視圖WITHENCRYPTIONASSELECT教師編號,姓名,性別,出生日期,學歷,職稱FROM教師;GO相關知識視圖的內容是由SELECT語句來定義的。對于數(shù)據(jù)庫中的現(xiàn)有視圖，可以使用視圖設計器或ALTERVIEW語句對其定義進行修改。使用視圖設計器修改視圖使用ALTERVIEW語句修改視圖任務10重命名視圖任務實現(xiàn)USE學生成績;GOEXECsp_rename'教師視圖','TeacherView','OBJECT';GO相關知識在數(shù)據(jù)庫中創(chuàng)建一個視圖后，不僅可以對其定義進行修改，也可以對其名稱進行修改。使用對象資源管理器或sp_rename系統(tǒng)存儲過程都可以對視圖進行重命名。使用對象資源管理器重命名視圖使用sp_rename重命名視圖任務11查看視圖相關信息任務實現(xiàn)USE學生成績;GOEXECsp_helptext'學生成績視圖';GOEXECsp_depends'學生成績視圖';GO相關知識查看當前數(shù)據(jù)庫中包含哪些視圖：查詢sys.views目錄視圖；查看指定視圖中包含哪些列：查詢sys.columns目錄視圖；查看指定視圖的定義文本：調用sp_helptext系統(tǒng)存儲過程；顯示視圖引用了哪些表以及哪些列：調用sp_depends系統(tǒng)存儲過程。任務12通過視圖修改數(shù)據(jù)任務實現(xiàn)USE學生成績;GOUPDATE學生成績視圖SET成績=85WHERE姓名='王曉燕'AND課程名稱='網(wǎng)頁設計';GO相關知識通過視圖不僅可以從一個或多個基礎表中查詢數(shù)據(jù)，還可以修改基礎表的數(shù)據(jù)，修改方式與通過UPDATE、INSERT和DELETE語句或使用bcp實用工具和BULKINSERT語句修改表中數(shù)據(jù)的方式一樣。但是，通過視圖更新數(shù)據(jù)時有一些限制。任務13刪除視圖任務實現(xiàn)USE學生成績;GOIFOBJECT_ID('學生成績視圖,'view')ISNOTNULLDROPVIEW學生成績視圖;GO相關知識在創(chuàng)建視圖后，如果不再需要該視圖，或想清除視圖定義及與之相關聯(lián)的權限，可以刪除該視圖。刪除視圖后，表和視圖所基于的數(shù)據(jù)并不受到影響。使用對象資源管理器刪除視圖使用DROPVIEW從當前數(shù)據(jù)庫中刪除一個或多個視圖1項目1初識SQLServer20082項目2創(chuàng)建和管理數(shù)據(jù)庫3項目3創(chuàng)建和管理表4項目4操作數(shù)據(jù)庫數(shù)據(jù)5項目5檢索數(shù)據(jù)庫數(shù)據(jù)6項目6創(chuàng)建索引和視圖7項目7Transact-SQL程序設計8項目8創(chuàng)建存儲過程和觸發(fā)器9項目9管理數(shù)據(jù)安全CONTENTS目錄項目7

Transact-SQL程序設計CONTENTS

目錄任務1認識Transact-SQL語言任務2使用條件控制流程任務3使用CASE函數(shù)進行查詢任務4使用GOTO語句實現(xiàn)流程跳轉任務5使用WAITFOR語句定時執(zhí)行操作任務6使用WHILE語句統(tǒng)計記錄行數(shù)任務7在查詢中使用字符串函數(shù)任務8在查詢中使用數(shù)學函數(shù)任務9在查詢中使用日期函數(shù)任務10在查詢中使用轉換函數(shù)任務11在查詢中使用系統(tǒng)函數(shù)項目12在查詢中使用用戶定義函數(shù)任務13使用游標計算記錄行數(shù)任務14通過游標提取數(shù)據(jù)任務15使用游標更新數(shù)據(jù)任務16處理事務任務17回滾事務任務1認識Transact-SQL語言（一）任務實現(xiàn)DECLARE@nowsmalldatetime,@msgvarchar(50);SET@now=GETDATE();SELECT@msg='歡迎您使用SQLServer2008！';PRINT'顯示局部變量的值';PRINT'---------------------------';PRINT@now;PRINT@msg;SELECT@nowAS現(xiàn)在時間,@msgAS歡迎信息;GO任務1認識Transact-SQL語言（二）相關知識一、Transact-SQL的組成數(shù)據(jù)定義語言；數(shù)據(jù)操作語言；數(shù)據(jù)控制語言；事務管理語言；附加語言元素二、批處理與腳本批處理就是包含一個或多個Transact-SQL語句，從應用程序一次性地發(fā)送到SQLServer進行執(zhí)行。腳本是存儲在文件中的一系列Transact-SQL語句，腳本文件的擴展名通常為.sql。三、標識符創(chuàng)建數(shù)據(jù)庫對象時需要使用標識符對其進行命名。在SQLServer中，所有內容都可以有標識符，如服務器、數(shù)據(jù)庫以及表、視圖、列、索引、觸發(fā)器、過程、約束和規(guī)則等數(shù)據(jù)庫對象都可以有標識符。大多數(shù)對象要求有標識符，但對有些對象（如約束），標識符是可選的。任務1認識Transact-SQL語言（三）相關知識四、常量常量也稱為字面量，是表示特定數(shù)據(jù)值的符號。常量的類型：字符串常量；Unicode字符串；二進制常量；bit常量；datetime常量；integer常量；decimal常量；float和real常量；money常量；uniqueidentifier常量五、局部變量局部變量（localvariable）是可保存特定類型的單個數(shù)據(jù)值的對象，用于在SQL語句之間傳遞數(shù)據(jù)。1．聲明局部變量；2．設置局部變量的值；3．顯示局部變量的值六、表達式表達式是標識符、值和運算符的組合，SQLServer可以對其求值以獲取結果。七、空值空值（NULL）表示值未知?？罩挡煌诳瞻谆蛄阒?。沒有兩個相等的空值。八、注釋語句1．行內注釋；2．塊注釋任務2使用條件語句控制流程（一）任務實現(xiàn)USE學生成績;GODECLARE@cnamevarchar(50),@chourint;SET@cname='網(wǎng)頁設計';IFNOTEXISTS(SELECT*FROM課程WHERE課程名稱=@cname)BEGIN

。。。

ENDELSEBEGIN

。。。END任務2使用條件語句控制流程（二）相關知識一、BEGIN…END語句BEGIN{sql_statement|statement_block}END二、IF…ELSE語句IFBooleanboolean_expression{sql_statement|statement_block}[ELSE{sql_statement|statement_block}]任務3使用CASE函數(shù)進行查詢（一）任務實現(xiàn)USE學生成績;GOSELECT班級編號,學號,姓名,SUM(CASE課程名稱WHEN'計算機應用基礎'THEN成績ELSE0END)AS[計算機應用基礎],SUM(CASE課程名稱WHEN'辦公軟件'THEN成績ELSE0END)AS[辦公軟件],SUM(CASE課程名稱WHEN'網(wǎng)頁設計'THEN成績ELSE0END)AS[網(wǎng)頁設計],SUM(CASE課程名稱WHEN'數(shù)據(jù)庫應用'THEN成績ELSE0END)AS[數(shù)據(jù)庫應用]FROM學生成績視圖GROUPBY班級編號,學號,姓名HAVING班級編號='1201'OR班級編號='1202';GO任務3使用CASE函數(shù)進行查詢（二）相關知識簡單CASE函數(shù)：CASEinput_expressionWHENwhen_expressionTHENresult_expression[...n][ELSEelse_result_expression]ENDCASE搜索函數(shù)：CASEWHENboolean_expressionTHENresult_expression[...n][ELSEelse_result_expression]END任務4使用GOTO語句實現(xiàn)流程跳轉任務實現(xiàn)DECLARE@iint,@sumint;SET@i=1;SET@sum=0;add_loop:SET@sum=@sum+@i;SET@i=@i+1;IF(@i<=100)GOTOadd_loop;SELECT@sumAS'1+2+3+...+100=';GO相關知識GOTO語句用于實現(xiàn)執(zhí)行流程的跳轉。label:[sql_statement|statement_block]。。。GOTOlabel任務5使用WAITFOR語句定時執(zhí)行操作任務實現(xiàn)WAITFORDELAY'03:00';EXECUTEsp_helpdb;GOUSEmsdb;EXECUTEsp_add_job@job_name='TestJob';BEGINWAITFORTIME'23:20';EXECUTEsp_update_job@job_name='TestJob',@new_name='UpdatedJob';END;GO相關知識WAITFOR語句在達到指定時間或時間間隔之前，或者指定語句至少修改或返回一行之前，阻止執(zhí)行批處理、存儲過程或事務。語法格式如下：WAITFOR{DELAY'time_to_pass'|TIME'time_to_execute'}其中DELAY指定可以繼續(xù)執(zhí)行批處理、存儲過程或事務之前必須經(jīng)過的指定時段，最長可為24小時。time_to_pass指定等待的時段。TIME指定的運行批處理、存儲過程或事務的時間。time_to_execute指定WAITFOR語句完成的時間。任務6使用WHILE語句統(tǒng)計記錄行數(shù)任務實現(xiàn)USE學生成績;GODECLARE@tidint,@countint;SET@tid=1;SET@count=0;WHILEEXISTS(SELECT*FROM教師WHERE教師編號=@tid)BEGINSET@count=@count+1;SET@tid=@tid+1;ENDPRINT'一共有'+CAST(@countASvarchar(2))+'名教師。';GO相關知識WHILE語句設置重復執(zhí)行SQL語句或語句塊的條件，只要指定的條件為真，就重復執(zhí)行語句；也可以使用BREAK和CONTINUE關鍵字在循環(huán)內部控制WHILE循環(huán)中語句的執(zhí)行。語法格式如下：WHILEboolean_expression{sql_statement|statement_block}[BREAK]{sql_statement|statement_block}[CONTINUE]{sql_statement|statement_block}任務7在查詢中使用字符串函數(shù)任務實現(xiàn)SELECTcol1=SUBSTRING('張三豐',2,2),col2=CHARINDEX('是','這是一本書。'),col3=PATINDEX('%si%','Expression'),col4=STR(123.456,6,3),col5=STUFF('SQL2008',4,0,'Server'),col6=REVERSE('Microsoft'),col7=REPLACE('abc','b','tt');GO相關知識一、函數(shù)概述SQLServer2008提供了大量的內置函數(shù)，可以用于執(zhí)行特定操作。除了內置函數(shù)外，SQLServer2008還允許用戶定義自己所需要的函數(shù)。使用內置函數(shù)或用戶自定義函數(shù)可以方便快捷地完成各種常見任務。二、字符串函數(shù)所有內置字符串函數(shù)都是具有確定性的函數(shù)，每次用一組特定的輸入值調用它們時都會返回相同的值。字符串函數(shù)是標量值函數(shù)，它們對字符串輸入值執(zhí)行操作，并且返回一個字符串或數(shù)值。任務8在查詢中使用數(shù)學函數(shù)任務實現(xiàn)SELECTSIN(PI()/3)AS正弦值,COS(PI()/3)AS余弦值,TAN(PI()/3)AS正切值,COT(PI()/3)AS余切值;SELECTCEILING(12.9273)AS[CEILING(12.9273)],FLOOR(12.9273)AS[FLOOR(12.9273)],LOG(1.75)AS[LOG(1.75)],LOG10(1.75)AS[LOG10(1.75)];SELECTRAND((DATEPART(mm,GETDATE())*100000)+(DATEPART(ss,GETDATE())*1000)+DATEPART(ms,GETDATE()))AS隨機數(shù);GO相關知識數(shù)學函數(shù)都是標量值函數(shù)，它們通?；谧鳛閰?shù)提供的輸入值執(zhí)行計算，并返回一個數(shù)值。任務9在查詢中使用日期函數(shù)任務實現(xiàn)SELECTGETDATE()AS[當前日期和時間],DATEPART(year,GETDATE())AS[當前年份],DATEPART(month,GETDATE())AS[當前月份],DATEPART(day,GETDATE())AS[當前天數(shù)],DATEPART(hour,GETDATE())AS[時],DATEPART(minute,GETDATE())AS[分],DATEPART(second,GETDATE())AS[分];SELECTDATEADD(day,1,GETDATE())AS[一天之后],DATEADD(month,1,GETDATE())AS[一月之后],DATEADD(year,1,GETDATE())AS[一年之后];SELECTCAST(DATEDIFF(day,GETDATE(),'2016-10-01')ASvarchar(4))+'天'AS[離2016年國慶節(jié)還有];GO相關知識日期函數(shù)用于顯示關于日期和時間的信息。使用這些函數(shù)可更改datetime和smalldatetime值，還可以對它們執(zhí)行算術運算。日期函數(shù)可以用于任何使用表達式的地方。任務10在查詢中使用轉換函數(shù)任務實現(xiàn)DECLARE@nowdatetime,@ffloat;SET@now=GETDATE();SET@f=123.456;PRINTCAST(@nowASvarchar(26));PRINTCONVERT(char(8),@now,11);PRINTCONVERT(char(8),@now,8);PRINTCONVERT(varchar(22),@now,120);PRINTCAST(@fASvarchar(10));SET@f=@f*100000;PRINTCONVERT(varchar(22),@f,0);PRINTCONVERT(varchar(22),@f,1);PRINTCONVERT(varchar(22),@f,2);GO相關知識在SQLServer中，數(shù)據(jù)類型轉換分為隱式轉換和顯式轉換。隱式轉換對用戶不可見，SQLServer會自動將數(shù)據(jù)從一種數(shù)據(jù)類型轉換為另一種數(shù)據(jù)類型，例如，將smallint與int進行比較時smallint會被隱式轉換為int。顯式轉換使用CAST或CONVERT函數(shù)實現(xiàn)，這兩個函數(shù)可以將局部變量、列或其他表達式從一種數(shù)據(jù)類型轉換為另一種數(shù)據(jù)類型。一、CAST函數(shù)CAST函數(shù)用于將某種數(shù)據(jù)類型的表達式顯式地轉換為另一種數(shù)據(jù)類型。二、CONVERT函數(shù)若要指定轉換后數(shù)據(jù)的樣式，可使用CONVERT函數(shù)進行數(shù)據(jù)類型轉換。任務11在查詢中使用系統(tǒng)函數(shù)任務實現(xiàn)PRINT'當前SQLServer的版本：'+@@VERSION;PRINT'運行SQLServer的本地服務器名稱：'+@@SERVERNAME;PRINT'當前所用服務名稱：'+@@SERVICENAME;PRINT'當前數(shù)據(jù)庫標識號：'+CAST(DB_ID()ASchar(1));PRINT'當前數(shù)據(jù)庫名稱：'+DB_NAME();PRINT'當前用戶的登錄標識名：'+SUSER_NAME();PRINT'當前數(shù)據(jù)庫用戶名：'+USER_NAME();GO相關知識系統(tǒng)函數(shù)對SQLServer2008中的值、對象和設置進行操作并返回有關信息。有一些系統(tǒng)函數(shù)的名稱以@@開頭，而且不需要使用圓括號。任務12在查詢中使用用戶定義函數(shù)（一）任務實現(xiàn)USE學生成績;GO--檢查用戶定義函數(shù)DateInterval是否存在，若已存在，則刪除之IFOBJECT_ID('dbo.DateInterval','FN')ISNOTNULLDROPFUNCTIONdbo.DateInterval;GO--創(chuàng)建用戶定義函數(shù)DateInterval，接受兩個日期參數(shù)，返回一個整數(shù)CREATEFUNCTIONdbo.DateInterval(@date1ASdate,@date2ASdate)RETURNSintASBEGINDECLARE@ResultVarintSET@ResultVar=DATEDIFF(year,@date1,@date2);RETURN(@ResultVar);ENDGO--在SELECT語句中調用用戶定義函數(shù)DateIntervalSELECT教師編號,姓名,dbo.DateInterval(出生日期,GETDATE())AS年齡,dbo.DateInterval(參加工作時間,GETDATE())AS工齡FROM教師;GO任務12在查詢中使用用戶定義函數(shù)（二）相關知識一、用戶定義函數(shù)概述用戶定義函數(shù)是接受參數(shù)、執(zhí)行操作（如復雜計算）并將操作結果以值形式返回的例程。返回值可為單個標量值或結果集。使用用戶定義函數(shù)有以下優(yōu)點：允許模塊化程序設計，執(zhí)行速度更快，減少網(wǎng)絡流量。SQLServer2008支持以下4種類型的用戶定義函數(shù)：標量值函數(shù)；內聯(lián)表值函數(shù)；多語句表值函數(shù)；CLR函數(shù)。二、創(chuàng)建用戶定義函數(shù)用戶定義函數(shù)可以使用CREATEFUNCTION語句來創(chuàng)建。三、調用用戶定義函數(shù)當調用標量值用戶定義函數(shù)時，必須至少提供由架構名稱和函數(shù)名稱兩部分組成的名稱。四、修改用戶定義函數(shù)若要對用戶定義函數(shù)進行修改，可以使用ALTERFUNCTION語句來實現(xiàn)。任務13使用游標計算記錄行數(shù)任務實現(xiàn)USE學生成績;GODECLAREteacher_cursorCURSORKEYSETFORSELECT*FROM教師;OPENteacher_cursor IF@@ERROR=0AND@@CURSOR_ROWS>0PRINT'教師人數(shù)為：

'

+CAST(@@CURSOR_ROWSASvarchar(3));CLOSEteacher_cursor; DEALLOCATEteacher_cursor; GO相關知識一、游標概述游標通過以下方式來擴展結果處理：允許定位在結果集的特定行；從結果集的當前位置檢索一行或一部分行；支持對結果集中當前位置的行進行數(shù)據(jù)修改；為由其他用戶對顯示在結果集中的數(shù)據(jù)庫數(shù)據(jù)所做的更改提供不同級別的可見性支持；提供腳本、存儲過程和觸發(fā)器中使用的Transact-SQL語句，以訪問結果集中的數(shù)據(jù)。二、定義游標SQL-92語法Transact-SQL擴展語法三、打開游標OPEN語句用于打開Transact-SQL服務器游標。任務14通過游標提取數(shù)據(jù)任務實現(xiàn)USE學生成績;GODECLAREstudent_cursorCURSORFORSELECT學號,姓名,性別,出生日期FROM學生

WHERE班級編號='1206';OPENstudent_cursor;FETCHNEXTFROMstudent_cursor;WHILE@@FETCH_STATUS=0BEGIN

FETCHNEXTFROMstudent_cursor;ENDCLOSEstudent_cursor;DEALLOCATEstudent_cursor;GO相關知識一、使用OPEN語句打開游標二、使用FETCH語句從該游標中檢索特定的行FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{{[GLOBAL]cursor_name}|@cursor_variable_name}[INTO@variable_name[,...n]]三、關閉和釋放游標1.用CLOSE語句關閉游標2.用DEALLOCATE語句釋放游標任務15通過游標更新數(shù)據(jù)任務實現(xiàn)DECLARE@gradeint;DECLAREgrade_cursorCURSORKEYSETFORSELECT成績FROM學生成績視圖WHERE學號='120802'AND課程名稱='高頻電路'FORUPDATE;OPENgrade_cursor;UPDATE學生成績視圖SET成績=成績+5WHERECURRENTOFgrade_cursor;相關知識若要通過Transcact-SQL服務器游標提取某行后修改或刪除該行，可先定義一個可更新的游標，即在游標定義語句中指定FORUPDATE子句。如果需要，還可以指定要更新哪些列。定義可更新游標后，可以在UPDATE或DELETE語句中使用一個WHERECURRENTOF<游標>子句，從而對游標當前所指向的數(shù)據(jù)行進行修改或刪除。任務16處理事務（一）任務實現(xiàn)USE學生成績;GODECLARE@n1int,@n2int,@n3int;SET@n1=@@TRANCOUNT;BEGINTRANSACTION;SET@n2=@@TRANCOUNT;SELECT*FROM學生成績視圖WHERE姓名='吳天昊'AND課程名稱='網(wǎng)頁設計';UPDATE學生成績視圖SET成績=成績+2WHERE姓名='吳天昊'AND課程名稱='網(wǎng)頁設計';SELECT*FROM學生成績視圖WHERE姓名='吳天昊'AND課程名稱='網(wǎng)頁設計';COMMITTRANSACTION;SET@n3=@@TRANCOUNT;SELECT*FROM學生成績視圖WHERE姓名='吳天昊'AND課程名稱='網(wǎng)頁設計';SELECT@n1AS活動事務數(shù)1,@n2AS活動事務數(shù)2,@n3AS活動事務數(shù)3;GO任務16處理事務（二）相關知識一、事務概述一個邏輯工作單元要成為一個事務，必須具有4個屬性，即原子性、一致性、隔離性和持久性（ACID）屬性。二、編寫有效的事務三、啟動事務使用BEGINTRANSACTION語句標記事務開始四、設置事務保存點使用SAVETRANSACTION語句設置事務保存點五、提交事務使用COMMITTRANSCTION標志事務結束任務17回滾事務（一）任務實現(xiàn)DECLARE@n1int,@n2int,@n3int;SET@n1=@@TRANCOUNT;BEGINTRANSACTIONSET@n2=@@TRANCOUNT;SELECT*FROM學生成績視圖WHERE姓名='何曉燕'AND課程名稱='會計基礎';UPDATE學生成績視圖SET成績=成績+2WHERE姓名='何曉燕'AND課程='會計基礎';SELECT*FROM學生成績視圖WHERE姓名='何曉燕'AND課程名稱='會計基礎';ROLLBACKTRANSACTION;SET@n3=@@TRANCOUNT;SELECT*FROM學生成績視圖WHERE姓名='何曉燕'AND課程名稱='會計基礎';SELECT@n1AS活動事務數(shù)1,@n2AS活動事務數(shù)2,@n3AS活動事務數(shù)3;GO任務17回滾事務（二）相關知識如果在事務中出現(xiàn)錯誤或用戶決定取消事務，則可以使用ROLLBACKTRANSACTION語句將顯式事務或隱式事務回滾到事務的起點或事務內的某個保存點。語法格式如下：ROLLBACK{TRAN|TRANSACTION}[transaction_name|@tran_name_variable|savepoint_name|@savepoint_variable][;]其中參數(shù)transaction_name是在BEGINTRANSACTION語句中為事務分配的名稱。嵌套事務時，transaction_name必須是最外面的BEGINTRANSACTION語句中的名稱。@tran_name_variable是用戶定義的、包含有效事務名稱的變量的名稱。必須使用char、varchar、nchar或nvarchar數(shù)據(jù)類型來聲明變量。savepoint_name是SAVETRANSACTION語句中指定的保存點名稱。當條件回滾應只影響事務的一部分時，可使用savepoint_name。@savepoint_variable是用戶定義的、包含有效保存點名稱的變量的名稱。必須使用char、varchar、nchar或nvarchar數(shù)據(jù)類型來聲明變量。ROLLBACKTRANSACTION將顯式事務或隱式事務回滾到事務的起點或事務內的某個保存點，清除自事務的起點或到某個保存點所做的所有數(shù)據(jù)修改，并釋放由事務控制的資源。1項目1初識SQLServer20082項目2創(chuàng)建和管理數(shù)據(jù)庫3項目3創(chuàng)建和管理表4項目4操作數(shù)據(jù)庫數(shù)據(jù)5項目5檢索數(shù)據(jù)庫數(shù)據(jù)6項目6創(chuàng)建索引和視圖7項目7Transact-SQL程序設計8項目8創(chuàng)建存儲過程和觸發(fā)器9項目9管理數(shù)據(jù)安全CONTENTS目錄項目8創(chuàng)建存儲過程和觸發(fā)器CONTENTS

目錄任務1創(chuàng)建和調用存儲過程任務2執(zhí)行字符串任務3管理存儲過程任務4設計和實現(xiàn)DML觸發(fā)器任務5設計和實現(xiàn)DDL觸發(fā)器任務6

管理觸發(fā)器任務1創(chuàng)建和調用存儲過程任務實現(xiàn)CREATEPROCEDUREuspGetGrade@student_namevarchar(10),@course_namevarchar(50)=''ASBEGINIF@course_name!=''SELECT*FROM學生成績視圖

WHERE姓名=@student_nameAND課程名稱=@course_name;ELSESELECT*FROM學生成績視圖

WHERE姓名=@student_name;ENDGO--調用存儲過程uspGetGradeEXECUTEdbo.uspGetGrade'劉春明','網(wǎng)頁設計';相關知識一、創(chuàng)建存儲過程使用CREATEPROCEDURE語句創(chuàng)建存儲過程CREATE{PROC|PROCEDURE}[schema_name.]procedure_name[;number][{@parameter[type_schema_name.]data_type}[VARYING][=default][[OUT[PUT]][,...n][WITH{[ENCRYPTION][RECOMPILE]}[,...n]][FORREPLICATION]AS{<sql_statement>[;][...n]}[;]二、執(zhí)行存儲過程使用EXECUTE語句執(zhí)行存儲過程：[{EXEC|EXECUTE}]{[@return_status=]{module_name[;number]|@module_name_var}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}][,...n][WITHRECOMPILE]

}[;]任務2執(zhí)行字符串任務實現(xiàn)DECLARE@s1varchar(50),@s2varchar(50),@s3varchar(50);SET@s1='SELECT*';SET@s2='FROM學生成績視圖';SET@s3='WHERE班級編號=''1208''AND課程名稱=';EXECUTE(@s1+@s2+@s3+'''電子測量技術''ORDERBY成績DESC;');GO相關知識預先將Transact-SQL語句放在字符串變量中，然后使用EXECUTE語句來執(zhí)行這個字符串，語法如下：{EXEC|EXECUTE}({@string_variable|[N]'tsql_string'}[+...n])[AS{LOGIN|USER}='name'][;]其中@string_variable是局部變量的名稱，該局部變量可以是任意char、varchar、nchar或nvarchar數(shù)據(jù)類型，其中包括(max)數(shù)據(jù)類型。任務3管理存儲過程相關知識一、查看存儲過程信息查看過程名稱的列表：使用sys.objects目錄視圖。顯示過程定義：使用sys.sql_modules目錄視圖。查看存儲過程的定義：使用sp_helptext系統(tǒng)存儲過程。查看存儲過程包含哪些參數(shù)：使用sp_help系統(tǒng)存儲過程。查看存儲過程的相關性：使用sp_depends系統(tǒng)存儲過程。二、修改存儲過程使用ALTERPROCEDURE語句修改存儲過程三、重命名存儲過程使用系統(tǒng)存儲過程sp_rename對用戶定義存儲過程進行重命名。四、刪除存儲過程使用DROPPROCEDURE語句從當前數(shù)據(jù)庫中刪除一個或多個存儲過程或過程組。任務4設計和實現(xiàn)DML觸發(fā)器（一）任務實現(xiàn)USE學生成績;CREATETRIGGERtrig1ON學生AFTERINSERTAS。。。CREATETRIGGERtrig2ON學生INSTEADOFDELETEAS。。。任務4設計和實現(xiàn)DML觸發(fā)器（二）相關知識一、觸發(fā)器概述觸發(fā)器是一種特殊的存儲過程，它在執(zhí)行語言事件時自動生效。觸發(fā)器分為DML觸發(fā)器和DDL觸發(fā)器兩大類。當數(shù)據(jù)庫中發(fā)生數(shù)據(jù)操作語言（DML）事件時將調用DML觸發(fā)器；當服務器或數(shù)據(jù)庫中發(fā)生數(shù)據(jù)定義語言（DDL）事件時將調用這些觸發(fā)器。二、設計DML觸發(fā)器DML觸發(fā)器的類型：AFTER觸發(fā)器INSTEADOF觸發(fā)器CLR觸發(fā)器三、實現(xiàn)DML觸發(fā)器DML觸發(fā)器可以使用CREATETRIGGER語句來創(chuàng)建任務5設計和實現(xiàn)DDL觸發(fā)器（一）任務實現(xiàn)USE學生成績;CREATETRIGGERsafetyONDATABASEFORDROP_VIEWASBEGINPRINT'不能在“學生成績”數(shù)據(jù)庫中刪除視圖。';PRINT'若要刪除視圖，必須禁用數(shù)據(jù)庫觸發(fā)器safety。';ROLLBACKTRANSACTION;ENDGODROPVIEW學生視圖;GO任務5設計和實現(xiàn)DDL觸發(fā)器（二）相關知識一、設計DLL觸發(fā)器DDL觸發(fā)器和DML觸發(fā)器具有許多相似的行為，但它們的用途不同。DML觸發(fā)器在INSERT、UPDATE和DELETE語句上操作，并且有助于在表或視圖中修改數(shù)據(jù)時強制業(yè)務規(guī)則，擴展數(shù)據(jù)完整性。DDL觸發(fā)器則在CREATE、ALTER、DROP和其他DDL語句上操作，它們用于執(zhí)行管理任務，并強制影響數(shù)據(jù)庫的業(yè)務規(guī)則，可應用于數(shù)據(jù)庫或服務器中某一類型的所有命令。二、實現(xiàn)DDL觸發(fā)器DDL觸發(fā)器像標準觸發(fā)器一樣，在響應事件時執(zhí)行存儲過程。但與標準觸發(fā)器不同的是，它們并不在響應對表或視圖的UPDATE、INSERT或DELETE語句時執(zhí)行存儲過程。它們主要在響應數(shù)據(jù)定義語言（DDL）語句執(zhí)行存儲過程。這些語句包括CREATE、ALTER、DROP、GRANT、DENY、REVOKE和UPDATESTATISTICS等語句。DDL觸發(fā)器可以使用CREATETRIGGER語句來創(chuàng)建。任務6管理觸發(fā)器相關知識一、修改觸發(fā)器使用ALTERTRIGGER可以更改以前使用CREATETRIGGER語句創(chuàng)建的DML或DDL觸發(fā)器的定義。二、重命名觸發(fā)器若要重命名觸發(fā)器，可使用sp_rename系統(tǒng)存儲過程來實現(xiàn)。三、禁用或啟用觸發(fā)器使用DISABLETRIGGER語句可以禁用觸發(fā)器；已禁用的觸發(fā)器可以使用ENABLETRIGGER語句重新啟用，會以最初創(chuàng)建觸發(fā)器時的方式來激發(fā)它。四、查看觸發(fā)器信息獲取有關數(shù)據(jù)庫中的觸發(fā)器的信息：使用sys.triggers目錄視圖；獲取有關服務器范圍內的觸發(fā)器的信息：使用sys.server_triggers目錄視圖。獲取有關激發(fā)觸發(fā)器的事件的信息：使用sys.trigger_events、sys.events與sys.server_trigger_events目錄視圖；查看觸發(fā)器的定義：使用sys.sql_modules目錄視圖或sp_helptext系統(tǒng)存儲過程，不過前提是觸發(fā)器未在創(chuàng)建或修改時加密；查看觸發(fā)器的依賴關系：使用sys.sql_dependencies目錄視圖或sp_depends系統(tǒng)存儲過程。五、刪除觸發(fā)器使用DROPTRIGGER語句可以從當前數(shù)據(jù)庫中刪除一個或多個DML或DDL觸發(fā)器。1項目1初識SQLServer20082項目2創(chuàng)建和管理數(shù)據(jù)庫3項目3創(chuàng)建和管理表4項目4操作數(shù)據(jù)庫數(shù)據(jù)5項目5檢索數(shù)據(jù)庫數(shù)據(jù)6項目6創(chuàng)建索引和視圖7項目7Transact-SQL程序設計8項目8創(chuàng)建存儲過程和觸發(fā)器9項目9管理數(shù)據(jù)安全CONTENTS目錄項目9

管理數(shù)據(jù)安全CONTENTS

目錄任務1設置身份驗證模式任務2創(chuàng)建登錄賬戶任務3查看固定服務器角色及其權限任務4管理固定服務器角色成員任務5創(chuàng)建數(shù)據(jù)庫用戶任務6修改數(shù)據(jù)庫用戶任務7刪除數(shù)據(jù)庫用戶任務8在數(shù)據(jù)庫中創(chuàng)建架構任務9修改現(xiàn)有數(shù)據(jù)庫架構任務10從數(shù)據(jù)庫中刪除架構任務11創(chuàng)建新的數(shù)據(jù)庫角色項目12向數(shù)據(jù)庫角色中添加成員任務13權限管理任務1設置身份驗證模式相關知識一、身份驗證模式身份驗證模式是指SQLServer2008系統(tǒng)驗證客戶端與服務器連接的方式。SQLServer2008提供了兩種身份驗證模式：Windows身份驗證模式混合驗證模式當建立起對SQLServer的成功連接之后，安全機制對于Windows身份驗證模式和混合驗證模式是相同的。二、設置身份驗證模式使用對象資源管理器設置身份驗證模式若要使用sa賬戶，則應執(zhí)行以下帶有ENABLE選項的ALTERLOGIN命令。ALTERLOGINsaENABLE;任務2創(chuàng)建登錄賬戶任務實現(xiàn)CREATELOGINapengWITHPASSWORD='That123abcOK',DEFAULT_DATABASE=學生成績;GOCREATELOGIN[ABC\IUSR_ABC]FROMWINDOWSWITHDEFAULT_DATABASE=學生成績;GOSELECTnameFROMmaster..syslogins;GO相關知識一、創(chuàng)建登錄賬戶在SQLServer2008中有兩類登錄賬戶：一類是由SQLServer2008自身負責身份驗證的登錄賬戶；另一類是基于Windows賬戶創(chuàng)建的登錄賬戶。這兩類登錄名都可以使用CREATELOGIN語句來創(chuàng)建。也可以使用對象資源管理器來創(chuàng)建登錄名。二、修改登錄賬戶創(chuàng)建一個SQLServer登錄賬戶之后，還可以使用ALTERLOGIN語句對該賬戶的屬性進行修改。三、刪除登錄賬戶使用DROPLOGIN語句刪除登錄賬戶。任務3查看固定服務器角色及其權限任務實現(xiàn)USEmaster;EXECsp_helpsrvrole;EXECsp_srvrolepermission'dbcreator';GO相關知識為了方便權限管理，可以將一些安全賬戶集中到一個單元中并對該單元設定權限，這樣的單元稱為角色。權限在安全賬戶成為角色成員時自動生效。固定服務器角色在其作用域內屬于服務器范圍，這些角色具有完成特定服務器管理活動的權限。sysadmin：系統(tǒng)管理員；securityadmin：安全管理員；serveradmin：服務器管理員；setupadmin：安裝程序管理員；processadmin：進程管理員；diskadmin：磁盤管理員；dbcreator：數(shù)據(jù)庫創(chuàng)建者；bulkadmin：該角色已授予ADMINISTERBULKOPERATIONS權限。任務4管理固定服務器角色成員任務實現(xiàn)USEmaster;IF(IS_SRVROLEMEMBER('dbcreator','apeng')=1)EXECsp_dropsrvrolemember'apeng','dbcreator';EXECsp_addsrvrolemember'apeng','dbcreator';EXECsp_helpsrvrolemember'dbcreator';GO相關知識固定服務器角色的權限是固定不變的，不能增加，也不能減少。如果將某個登錄名添加到固定服務器角色后，則該登錄名就擁有了該角色的權限。從固定服務器角色中刪除某個登錄名后，該登錄名就不再是此角色中的成員，它也就失去了該角色的權限。在SQLServer2008中，可以使用系統(tǒng)存儲過程對固定服務器角色成員進行管理，也可以使用對象資源管理器對固定服務器角色成員進行管理。任務5創(chuàng)建數(shù)據(jù)庫用戶任務實現(xiàn)USE學生成績;GOCREATEUSERapengFORLOGINapeng;CREATEUSERsmithFORLOGIN[ABC\Smith]WITHDEFAULT_SCHEMA=Student;GOSELECT*FROMsys.database_principalsWHEREtype='S'ORtype='U'ORDERBYtype;GO相關知識同一個登錄名可以在不同的數(shù)據(jù)庫中映射為不同的數(shù)據(jù)庫用戶。數(shù)據(jù)庫用戶是數(shù)據(jù)庫級別上的主體。在默認情況下，新創(chuàng)建的數(shù)據(jù)庫將有以下兩個用戶：dbo：是數(shù)據(jù)庫的所有者，并擁有在數(shù)據(jù)庫進行所有操作的權限；guest：是一個默認用戶，授予該用戶的權限由在數(shù)據(jù)庫中沒有賬戶的用戶繼承。創(chuàng)建數(shù)據(jù)庫用戶使用CREATEUSER語句向當前數(shù)據(jù)庫添加用戶使用對象資源管理器來創(chuàng)建數(shù)據(jù)庫用戶任務6修改數(shù)據(jù)庫用戶任務實現(xiàn)USE學生成績;ALTERUSERapengWITHNAME=Mary,DEFAULT_SCHEMA=Teacher;SELECT*FROMsys.database_principalsWHEREtype='S';GO相關知識對于現(xiàn)有的數(shù)據(jù)庫用戶，可以使用ALTERUSER對它進行重命名或者更改它的默認架構：ALTERUSERuser_nameWITH<set_item>[,...n]<set_item>::=NAME=new_user_name|DEFAULT_SCHEMA=schema_name|LOGIN=login_name也可以在對象資源管理器中對數(shù)據(jù)庫用戶的默認架構進行修改。任務7刪除數(shù)據(jù)庫用戶任務實現(xiàn)USE學生成績;IFEXISTS(SELECT*FROMsys.database_principalsWHEREname='Jack'ANDtype='S')DROPUSERJack;GO相關知識使用DROPUSER語句可以從當前數(shù)據(jù)庫中刪除用戶：DROPUSERuser_name其中參數(shù)user_name指定在此數(shù)據(jù)庫中用于識別該用戶的名稱。不能從數(shù)據(jù)庫中刪除擁有安全對象的用戶。必須先刪除或轉移安全對象的所有權，才能刪除擁有這些安全對象的數(shù)據(jù)庫用戶。也可以使用對象資源管理器來刪除數(shù)據(jù)庫用戶。任務8在數(shù)據(jù)庫中創(chuàng)建架構任務實現(xiàn)USE學生成績;GOCREATESCHEMAWebAUTHORIZATIONMaryCREATETABLEMember(MemberIDint,MemberNamevarchar(10),passwordvarchar(20));GOSELECT*FROMsys.schemas;GO相關知識每個對象都屬于一個數(shù)據(jù)庫架構。數(shù)據(jù)庫架構是一個獨立于數(shù)據(jù)庫用戶的非重復命名空間。數(shù)據(jù)庫架構是諸如表、視圖、過程和功能等對象的命名空間或容器，可以在sys.object目錄視圖中找到這些對象。架構位于數(shù)據(jù)庫內部，而數(shù)據(jù)庫位于服務器內部。使用CREATESCHEMA語句可以在當前數(shù)據(jù)庫中創(chuàng)建架構。也可以使用對象資源管理器來創(chuàng)建架構。任務9修改現(xiàn)有數(shù)據(jù)庫架構（一）任務實現(xiàn)USE學生成績;GOCREATETABLEArticle(ArticleIDint,Titlevarchar(50),Contentvarchar(max));GOALTERSCHEMAWebTRANSFERdbo.Article;GOSELECTname,object_id,type_descFROMsys.objectsWHEREOBJECTPROPERTY(object_id,'SchemaId')=SCHEMA_ID('Web')ORDERBYtype_desc,name;GO任務9修改現(xiàn)有數(shù)據(jù)庫架構