SQL_Server__學習筆記費下載

1、SQL Server 2005 學習筆記第1章 SQL Server 2005關(guān)系數(shù)據(jù)庫簡介1.1 關(guān)系數(shù)據(jù)庫最常用的數(shù)據(jù)模型有層次模型Hierarchical Model、網(wǎng)狀模型Network Model和關(guān)系模型Relational Model。關(guān)系模型就是用二維表格結(jié)構(gòu)來表示實體及實體之間聯(lián)系的模型。 1.1.1 關(guān)系模型的根本概念關(guān)系Relation：一個關(guān)系對應一張二維表，每個關(guān)系有一個關(guān)系名。在SQL Server中，一個關(guān)系就是一個表文件。元組Tuple：二維表中水平方向的一行，有時也叫做一條記錄。屬性Attribute：表格中的一列，相當于記錄中的一個字段。關(guān)鍵字Key：可唯

2、一標識元組的屬性或?qū)傩约?，也稱為關(guān)系鍵或主碼。域Domain：屬性的取值范圍，如性別的域是男，女。分量：每一行對應的列的屬性值，即元組中的一個屬性值。關(guān)系模式：對關(guān)系的描述，一般表示為：關(guān)系名屬性1，屬性2，屬性n。1.1.2 關(guān)系模型的性質(zhì)1關(guān)系中不允許出現(xiàn)相同的元組。因為數(shù)學上集合中沒有相同的元素，而關(guān)系是元組的集合，所以作為集合元素的元組應該是唯一的。2關(guān)系中元組的順序即行序是無關(guān)緊要的，在一個關(guān)系中可以任意交換兩行的次序。因為集合中的元素是無序的，所以作為集合元素的元組也是無序的。根據(jù)關(guān)系的這個性質(zhì)，可以改變元組的順序使其具有某種排序，然后按照順序查詢數(shù)據(jù)，可以提高查詢速度。3關(guān)系中屬

3、性的順序是無關(guān)緊要的，即列的順序可以任意交換。交換時，應連同屬性名一起交換，否那么將得到不同的關(guān)系。4同一屬性名下的各個屬性值必須來自同一個域，是同一類型的數(shù)據(jù)。5關(guān)系中各個屬性必須有不同的名字，不同的屬性可來自同一個域，即它們的分量可以取自同一個域。6關(guān)系中每一分量必須是不可分的數(shù)據(jù)項，或者說所有屬性值都是原子的，是一個確定的值，而不是值的集合。 1.1.3 關(guān)系數(shù)據(jù)庫完整性1、實體完整性(Entity Integrity)實體完整性是指主關(guān)系鍵的值不能為空或局部為空。 2、參照完整性(Referential integrity)如果關(guān)系R2的外部關(guān)系鍵X與關(guān)系R1的主關(guān)系鍵相符,那么X的每

4、個值或者等于R1中主關(guān)系鍵的某一個值，或者取空值。 3、域完整性域完整性是針對某一具體關(guān)系數(shù)據(jù)庫的約束條件。它反映某一具體應用所涉及的數(shù)據(jù)必須滿足的語義要求。 1.1.4 關(guān)系數(shù)據(jù)庫的標準化 第一范式1NF：元組中每一個分量都必須是不可分割的數(shù)據(jù)項第二范式2NF：不僅滿足第一范式，而且所有非主屬性完全依賴于其主碼第三范式3NF：不僅滿足第二范式，而且它的任何一個非主屬性都不傳遞于任何主關(guān)鍵字1.2 SQL Server 2005的新特點 1、增強的通知效勞。 2、增強的報表效勞。 3、新增Service Broker技術(shù)。 4、增強的數(shù)據(jù)引擎。5、增強的數(shù)據(jù)訪問接口。 6、增強的分析效勞。7、

5、增強的集成效勞。8、增強的數(shù)據(jù)復制效勞。9、改良的開發(fā)工具。1.3 SQL Server2005環(huán)境需求 企業(yè)版 開發(fā)版 標準版 工作組版 簡化版 Windows 2000不支持不支持不支持不支持不支持Windows 2000 Professional Edition SP4不支持支持支持支持支持Windows 2000 Server SP4 支持支持支持支持支持Windows 2000 Advanced Server SP4 支持支持支持支持支持Windows 2000 Datacenter Edition SP4支持支持支持支持支持Windows XP Home Edition SP2不支

6、持支持不支持不支持支持Windows XP Professional Edition SP2不支持支持支持支持支持Windows 2003 Server SP1支持支持支持支持支持Windows 2003 Enterprise Edition SP1支持支持支持支持支持1.4 SQL Server2005系統(tǒng)數(shù)據(jù)庫簡介SQL Server 2005有4個系統(tǒng)數(shù)據(jù)庫，它們分別為Master、Model、Msdb、Tempdb。1Master數(shù)據(jù)庫是SQL Server系統(tǒng)最重要的數(shù)據(jù)庫，它記錄了SQL Server系統(tǒng)的所有系統(tǒng)信息。這些系統(tǒng)信息包括所有的登錄信息、系統(tǒng)設置信息、SQL Serv

7、er的初始化信息和其他系統(tǒng)數(shù)據(jù)庫及用戶數(shù)據(jù)庫的相關(guān)信息。因此，如果 master 數(shù)據(jù)庫不可用，那么 SQL Server 無法啟動。在 SQL Server 2005 中，系統(tǒng)對象不再存儲在 master 數(shù)據(jù)庫中，而是存儲在 HYPERLINK ms-help:/MS.SQLCC.v9/MS.SQLSVR.v9.zh-CHS/udb9/html/d592b2b4-bc36-4eb9-9385-8fe4dff0dced.htm t _parent Resource 數(shù)據(jù)庫中。2model 數(shù)據(jù)庫用作在 SQL Server 實例上創(chuàng)立的所有數(shù)據(jù)庫的模板。因為每次啟動 SQL Server 時

8、都會創(chuàng)立 tempdb，所以 model 數(shù)據(jù)庫必須始終存在于 SQL Server 系統(tǒng)中。當發(fā)出 CREATE DATABASE創(chuàng)立數(shù)據(jù)庫語句時，將通過復制 model 數(shù)據(jù)庫中的內(nèi)容來創(chuàng)立數(shù)據(jù)庫的第一局部，然后用空頁填充新數(shù)據(jù)庫的剩余局部。 如果修改 model 數(shù)據(jù)庫，之后創(chuàng)立的所有數(shù)據(jù)庫都將繼承這些修改。例如，可以設置權(quán)限或數(shù)據(jù)庫選項或者添加對象，例如，表、函數(shù)或存儲過程。 3Msdb數(shù)據(jù)庫是代理效勞數(shù)據(jù)庫，為其報警、任務調(diào)度和記錄操作員的操作提供存儲空間。4Tempdb是一個臨時數(shù)據(jù)庫，它為所有的臨時表、臨時存儲過程及其他臨時操作提供存儲空間。Tempdb數(shù)據(jù)庫由整個系統(tǒng)的所有數(shù)據(jù)

9、庫使用，不管用戶使用哪個數(shù)據(jù)庫，他們所建立的所有臨時表和存儲過程都存儲在tempdb上。SQL Server每次啟動時，tempdb數(shù)據(jù)庫被重新建立。當用戶與SQL Server斷開連接時，其臨時表和存儲過程自動被刪除。 1.4.1 SQL Server 2005管理平臺SQL Server 2005管理平臺SQL Server Management Studio包含了SQL Server 2000企業(yè)管理器Enterprise Manager，以及查詢分析器Query Analyzer等方面的功能。此外，SQL Server 2005管理平臺還提供了一種環(huán)境，用于管理 Analysis Se

10、rvices分析效勞、Integration Services集成效勞、Reporting Services報表效勞和 XQuery。1.4.2 商業(yè)智能開發(fā)平臺SQL Server商業(yè)智能開發(fā)平臺(Business Intelligence Development Studio)是一個集成的環(huán)境，用于開發(fā)商業(yè)智能構(gòu)造如多維數(shù)據(jù)集、數(shù)據(jù)源、報告和Integration Services 軟件包，如以下圖所示。 1.4.3 SQL Server分析器 SQL Server分析器Profiler是一個圖形化的管理工具，用于監(jiān)督、記錄和檢查SQL Server 數(shù)據(jù)庫的使用情況。對系統(tǒng)管理員來說，它

11、是一個連續(xù)實時地捕獲用戶活動情況的間諜。可以通過多種方法啟動 SQL Server Profiler，以支持在各種情況下收集跟蹤輸出。如以下圖所示。 1.4.4 數(shù)據(jù)庫引擎優(yōu)化參謀企業(yè)數(shù)據(jù)庫系統(tǒng)的性能依賴于組成這些系統(tǒng)的數(shù)據(jù)庫中物理設計結(jié)構(gòu)的有效配置。這些物理設計結(jié)構(gòu)包括索引、聚集索引、索引視圖和分區(qū)，其目的在于提高數(shù)據(jù)庫的性能和可管理性。SQL Server 2005 提供了數(shù)據(jù)庫引擎優(yōu)化參謀，這是分析一個或多個數(shù)據(jù)庫上工作負荷的性能效果的工具。如以下圖所示。 1.4.5 分析效勞Microsoft SQL Server 2005 Analysis Services (SSAS) 為商業(yè)智能

12、應用程序提供聯(lián)機分析處理 (OLAP) 和數(shù)據(jù)挖掘功能。Analysis Services 允許設計、創(chuàng)立和管理包含從其他數(shù)據(jù)源如關(guān)系數(shù)據(jù)庫聚合的數(shù)據(jù)的多維結(jié)構(gòu)，以實現(xiàn)對 OLAP 的支持。對于數(shù)據(jù)挖掘應用程序，分析效勞允許設計、創(chuàng)立和可視化處理那些通過使用各種行業(yè)標準數(shù)據(jù)挖掘算法，并根據(jù)其他數(shù)據(jù)源構(gòu)造出來的數(shù)據(jù)挖掘模型。 1.4.6 SQL Server配置管理器SQL Server 配置管理器是一種工具，用于管理與 SQL Server 相關(guān)聯(lián)的效勞、配置 SQL Server 使用的網(wǎng)絡協(xié)議以及從 SQL Server 客戶端計算機管理網(wǎng)絡連接配置。如以下圖所示。 1.4.7 SQL S

13、erver文檔和教程SQL Server 2005提供了大量的聯(lián)機幫助文檔Books Online，它具有索引和全文搜索能力，可根據(jù)關(guān)鍵詞來快速查找用戶所需信息。SQL Server 2005 中提供的教程可以幫助了解 SQL Server 技術(shù)和開始工程，如以下圖所示。 第2章 效勞器管理2.1 效勞器注冊 .1 創(chuàng)立效勞器組在一個網(wǎng)絡系統(tǒng)中，可能有多個SQL Server效勞器，可以對這些SQL Server效勞器進行分組管理。分組的原那么往往是依據(jù)組織結(jié)構(gòu)原那么，如將公司內(nèi)一個部門的幾個SQL Server效勞器分為一組。SQL Server分組管理由SQL Server管理平臺來進行。

14、 2.1.2 效勞器注冊與連接在SQL Server管理平臺中注冊效勞器可以存儲效勞器連接信息，以供將來連接時使用。有三種方法可以在SQL Server管理平臺中注冊效勞器： 1在安裝管理平臺之后首次啟動它時，將自動注冊 SQL Server 的本地實例； 2可以隨時啟動自動注冊過程來復原本地效勞器實例的注冊； 3可以使用 SQL Server管理平臺的“已注冊的效勞器工具注冊效勞器。 在注冊效勞器時必須指定以下選項，如圖2-3所示：1效勞器的類型。2效勞器的名稱。3登錄到效勞器時使用的身份驗證的類型，以及登錄名和密碼如果需要。4注冊了效勞器后要將該效勞器參加到其中的組的名稱。 選擇效勞器連接

15、屬性頁框，還可以指定以下連接選項 ，如圖2-4所示：1效勞器默認情況下連接到的數(shù)據(jù)庫。2連接到效勞器時所使用的網(wǎng)絡協(xié)議，要使用的網(wǎng)絡數(shù)據(jù)包大小。3連接超時值，執(zhí)行超時值等。要和已注冊的效勞器實現(xiàn)“連接，那么需要使用右鍵單擊一個效勞器，指向“連接，然后單擊“對象資源管理器，如圖2-5所示。與連接效勞器相反的是斷開效勞器，只要在所要斷開的效勞器上單擊右鍵，選擇“斷開即可。注意斷開效勞器并不是從計算機中將效勞器刪除，而只是從SQL Server管理平臺中刪除了對該效勞器的引用。需要再次使用該效勞器時，只需在SQL Server管理平臺中重新連接即可。 2.2 效勞器啟動、暫停和停止 在SQL Ser

16、ver管理平臺中，在所要啟動的效勞器上單擊右鍵，從彈出的快捷菜單中選擇“啟動選項，即可啟動效勞器。暫停和關(guān)閉效勞器的方法與啟動效勞器的方法類似，只需在相應的快捷菜單中選擇“暫停Pause或“停止Stop選項即可，如圖2-6所示。 2.3 效勞器配置選項設置 使用SQL Server管理平臺配置效勞器的操作方法為：在SQL Server管理平臺中用右鍵單擊所要進行配置的效勞器，從快捷菜單中選擇“屬性Properties選項，就會出現(xiàn)如圖2-7所示的對話框，其中可以進行效勞器的屬性配置選項的設置。在如圖2-7所示的效勞器屬性對話框中共有7個選項。這7個選項分別是：常規(guī)選項、內(nèi)存選項、處理器選項、平

17、安性選項、連接選項、數(shù)據(jù)庫設置選項、高級選項。 第3章 Transact-SQL語言 3.1 數(shù)據(jù)定義語言 數(shù)據(jù)定義語言DDL是指用來定義和管理數(shù)據(jù)庫以及數(shù)據(jù)庫中各種對象的語句，這些語句包括CREATE、ALTER和DROP等。在SQL Server 2005中，數(shù)據(jù)庫對象包括表、視圖、觸發(fā)器、存儲過程、規(guī)那么、默認、用戶自定義的數(shù)據(jù)類型等。這些對象的創(chuàng)立、修改和刪除等都可以通過使用CREATE，ALTER，DROP等語句來完成。例3-1 創(chuàng)立數(shù)據(jù)庫表/*下面的例子將創(chuàng)立表S。*/CREATE TABLE S( sno char(10) NOT NULL /*學號字段*/ CONSTRAINT

18、 PK_sno PRIMARY KEY CLUSTERED/*主鍵約束*/ CHECK (sno like 313005010-90-9)/*檢查約束*/, sname char(8) NULL, /*姓名字段*/ sex char(2) NULL, /*性別字段*/ age int NULL, /*年齡字段*/ dept varchar(20) NULL/*系別字段*/ )例3-2 修改S表，增加一個班號列程序清單如下：ALTER TABLE S ADDCLASS_NO CHAR(6) 例3-3刪除S表程序清單如下： DROP table S3.2 數(shù)據(jù)操縱語言DML數(shù)據(jù)操縱語言是指用來查詢

19、、添加、修改和刪除數(shù)據(jù)庫中數(shù)據(jù)的語句，這些語句包括SELECT、INSERT、UPDATE、DELETE等。 將在第6章詳細講解SELECT語句的語法及實例。 3.2.2INSERT語句INSERT語句用于向數(shù)據(jù)庫表或者視圖中參加一行數(shù)據(jù)。INSERT語句的語法形式如下： INSERT INTO table_or_view (column_list) VALUES(data_values)其中，table_or_view是指要插入新記錄的表或視圖；column_list是可選項，指定待添加數(shù)據(jù)的列； VALUES子句指定待添加數(shù)據(jù)的具體值。列名的排列順序不一定要和表定義時的順序一致。但當指定列

20、名表時VALUES子句值的排列順序必須和列名表中的列名排列順序一致，個數(shù)相等，數(shù)據(jù)類型一一對應。 在進行數(shù)據(jù)插入操作時須注意以下幾點：1必須用逗號將各個數(shù)據(jù)分開，字符型數(shù)據(jù)要用單引號括起來。2INTO子句中沒有指定列名，那么新插入的記錄必須在每個屬性列上均有值，且VALUES子句中值的排列順序要和表中各屬性列的排列順序一致。3將VALUES子句中的值按照INTO子句中指定列名的順序插入到表中。4對于INTO子句中沒有出現(xiàn)的列，那么新插入的記錄在這些列上將取空值，如上例的SCORE即賦空值。但在表定義時有NOT NULL約束的屬性列不能取空值。 例3-5 創(chuàng)立SC表學生選課表，并向SC表中插入一

21、條選課記錄S7,C1。程序清單如下：CREATE TABLE SC( sno char(10) NOT NULL, cno char(2) NULL, /*課程編號字段*/ score numerical(4,1) NULL /*成績字段*/ ) GoINSERT INTO SC (sno,cno) VALUES (3130050101, c1)Go下面是插入與列順序不同的數(shù)據(jù)的例子。例3-6 使用 column_list 及 VALUES 列表顯式地指定將被插入每個列的值。程序清單如下：CREATE TABLE T1 ( column_1 int, column_2 varchar(30)

22、GoINSERT T1 (column_2, column_1) VALUES (This is a test,1) 插入多行數(shù)據(jù)的語法格式為：INSERT INTO table_or_view (column_list) 子查詢例3-7 求出各位學生的平均成績，把結(jié)果存放在新表AVGSCORE中。程序清單如下：/*首先建立新表AVGSCORE，用來存放學號和學生的平均成績。*/CREATE TABLE AVGSCORE(SNO CHAR(10),AVGSCORE SMALLINT) Go/*利用子查詢求出SC表中各位學生的平均成績，把結(jié)果存放在新表AVGSCORE中。*/INSERT INT

23、O AVGSCORESELECT SNO,AVG(SCORE) FROM SCGROUP BY SNO 3.2.3UPDATE語句UPDATE語句用于修改數(shù)據(jù)庫表或視圖中特定記錄或者字段的數(shù)據(jù)，其語法形式如下： UPDATE table_or_view SET =,= WHERE 其中：table_or_view是指要修改的表或視圖；SET子句給出要修改的列及其修改后的值，其中column為要修改的列名，expression為其修改后的值；WHERE子句指定待修改的記錄應當滿足的條件，WHERE子句省略時，那么修改表中的所有記錄。 例3-8一個帶有WHERE條件的修改語句。程序清單如下：use

24、 adventureworksgoupdate person.address set city=Boston where addressid=1例3-9 將所有學生年齡增加1歲程序清單如下：UPDATE S SET AGE=AGE+1 例3-12 創(chuàng)立把講授C5課程的教師的工資增加100元。 程序清單如下： /*T表教師根本情況表的結(jié)構(gòu)為T(TNO,TN,SEX,AGE,PROF,SAL,DEPT)分別表示教師的編號，姓名，性別，年齡，職稱，工資，系別。TC表教師授課表的結(jié)構(gòu)為TC(TNO,CNO)分別表示教師的編號，課程編號。*/ UPDATE T SET SAL=SAL+100 WHERE

25、 TNO IN (SELECT T.TNO FROM T,TC WHERE T.TNO=TC.TNO AND TC O=C5) /*通過連接查詢找到講授C5課程的教師編號。*/3.2.4DELETE語句 使用DELETE語句可以刪除表中的一行或多行記錄，其語法格式為： DELETE FROM table_or_view WHERE 其中：table_or_view是指要刪除數(shù)據(jù)的表或視圖；WHERE子句指定待刪除的記錄應當滿足的條件，WHERE子句省略時，那么刪除表中的所有記錄。下面是刪除一行記錄的例子。 例3-14 刪除張益琳教師的記錄。 程序清單如下： DELETE FROM T WHER

26、E TN= 張益琳下面是刪除多行記錄的例子。例 3-15 刪除所有教師的授課記錄。 程序清單如下： DELETE FROM TC執(zhí)行此語句后，TC表即為一個空表，但其定義仍存在數(shù)據(jù)字典中。例3-16 刪除李明同學選課的記錄。 DELETE FROM SC WHERE SNO= (SELECT SNO FROM S WHERE SN= 李明)3.3 數(shù)據(jù)控制語言 數(shù)據(jù)控制語言DCL是用來設置或更改數(shù)據(jù)庫用戶或角色權(quán)限的語句，包括GRANT，DENY，REVOKE等語句。在默認狀態(tài)下，只有sysadmin，dbcreator，db_owner或db_securityadmin等人員才有權(quán)力執(zhí)行數(shù)據(jù)

27、控制語言。3.3.1GRANT語句1、語句權(quán)限與角色的授予 SQL語言使用GRANT語句為用戶授予語句權(quán)限的語法格式為： GRANT | ,| TO |PUBLIC,| WITH ADMIN OPTION其語義為：將指定的語句權(quán)限授予指定的用戶或角色。其中：1PULBIC代表數(shù)據(jù)庫中的全部用戶；2WITH ADMIN OPTION為可選項，指定后那么允許被授權(quán)的用戶將指定的系統(tǒng)特權(quán)或角色再授予其他用戶或角色。1、語句權(quán)限與角色的授予 例3-17給用戶 Mary 和 John 以及 Windows NT 組 CorporateBobJ 授予多個語句權(quán)限。 程序清單如下： GRANT CREATE

28、 DATABASE, CREATE TABLE TO Mary, John, CorporateBobJ例3-18 為用戶ZhangYiLin授予CREATE TABLE的語句權(quán)限。 程序清單如下： GRANT CREATE TABLE TO ZhangYiLin2、對象權(quán)限與角色的授予數(shù)據(jù)庫管理員擁有系統(tǒng)權(quán)限，而作為數(shù)據(jù)庫的普通用戶，只對自己創(chuàng)立的根本表、視圖等數(shù)據(jù)庫對象擁有對象權(quán)限。如果要共享其他的數(shù)據(jù)庫對象，那么必須授予他一定的對象權(quán)限。同語句權(quán)限的授予類似，SQL語言使用GRANT語句為用戶授予對象權(quán)限，其語法格式為： GRANT ALL|(列名,列名),ON TO |PUBLIC,|

29、 WITH ADMIN OPTION 其語義為：將指定的操作對象的對象權(quán)限授予指定的用戶或角色。其中：1ALL代表所有的對象權(quán)限。2列名用于指定要授權(quán)的數(shù)據(jù)庫對象的一列或多列。如果不指定列名，被授權(quán)的用戶將在數(shù)據(jù)庫對象的所有列上均擁有指定的特權(quán)。實際上，只有當授予INSERT、UPDATE權(quán)限時才需指定列名。3ON子句用于指定要授予對象權(quán)限的數(shù)據(jù)庫對象名，可以是根本表名、視圖名等。4WITH ADMIN OPTION為可選項，指定后那么允許被授權(quán)的用戶將權(quán)限再授予其他用戶或角色。例3-19在權(quán)限層次中授予對象權(quán)限。首先，給所有用戶授予 SELECT 權(quán)限，然后，將特定的權(quán)限授予用戶 Mary，

30、John 和 Tom。 程序清單如下： GRANT SELECT ON s TO public GO GRANT INSERT, UPDATE, DELETE ON s TO Mary, John, Tom GO例3-20 將查詢T表和修改教師職稱的權(quán)限授予USER3，并允許將此權(quán)限授予其他用戶。 程序清單如下： GRANT SELECT,UPDATE(PROF) ON T TO USER3 WITH ADMIN OPTION上例中，USER3具有此對象權(quán)限，并可使用GRANT命令給其他用戶授權(quán)，如下例，USER3將此權(quán)限授予USER4： GRANT SELECT,UPDATE(PROF) O

31、N T TO USER43.3.2REVOKE語句 REVOKE語句是與GRANT語句相反的語句，它能夠?qū)⒁郧霸诋斍皵?shù)據(jù)庫內(nèi)的用戶或者角色上授予或拒絕的權(quán)限刪除，但是該語句并不影響用戶或者角色從其他角色中作為成員繼承過來的權(quán)限1、語句權(quán)限與角色的收回 數(shù)據(jù)庫管理員可以使用REVOKE語句收回語句權(quán)限，其語法格式為： REVOKE | ,| FROM |PUBLIC,|例：收回用戶ZHANGYILIN所擁有的CREATE TABLE的語句權(quán)限。 REVOKE CREATE TABLE FROM ZHANGYILIN2、對象權(quán)限與角色的收回 所有授予出去的權(quán)力在必要時都可以由數(shù)據(jù)庫管理員和授權(quán)者收

32、回，收回對象權(quán)限仍然使用REVOKE語句，其語法格式為： REVOKE | ,| FROM |PUBLIC,| 例3-21 收回用戶USER1對C表的查詢權(quán)限。程序清單如下：REVOKE SELECT ON CFROM USER1例3-22 收回用戶USER3查詢T表和修改教師職稱的權(quán)限。程序清單如下：REVOKE SELECT,UPDATE(PROF)ON TFROM USER3在上例中，USER3將對T表的權(quán)限授予了USER4，在收回USER3對T表的權(quán)限的同時，系統(tǒng)會自動收回USER4對T表的權(quán)限。 例3-23首先從 public 角色中收回 SELECT 權(quán)限，然后，收回用戶 Mary

33、，John 和 Tom 的特定權(quán)限。程序清單如下：USE pubs GOREVOKE SELECT ON s FROM publicGOREVOKE INSERT, UPDATE, DELETE ON s FROM Mary, John, Tom 3.3.3DENY語句 DENY語句用于拒絕給當前數(shù)據(jù)庫內(nèi)的用戶或者角色授予權(quán)限，并防止用戶或角色通過其組或角色成員繼承權(quán)限。否認語句權(quán)限的語法形式為： DENY ALL| ,| TO |PUBLIC,|否認對象權(quán)限的語法形式為： DENY ALL|(列名,列名),ON TO |PUBLIC,| 例3-24首先給 public 角色授予 SELECT

34、 權(quán)限，然后，拒絕用戶 Mary，John 和 Tom 的特定權(quán)限。 程序清單如下： USE pubs GO GRANT SELECT ON s TO public GO DENY SELECT, INSERT, UPDATE, DELETE ON s TO Mary,John,Tom 3.4 系統(tǒng)存儲過程 系統(tǒng)存儲過程是SQL Server系統(tǒng)創(chuàng)立的存儲過程，它的目的在于能夠方便地從系統(tǒng)表中查詢信息，或者完成與更新數(shù)據(jù)庫表相關(guān)的管理任務或其他的系統(tǒng)管理任務。系統(tǒng)存儲過程可以在任意一個數(shù)據(jù)庫中執(zhí)行。系統(tǒng)存儲過程創(chuàng)立并存放于系統(tǒng)數(shù)據(jù)庫master中，并且名稱以sp_或者xp_開頭。一些系統(tǒng)過程只

35、能由系統(tǒng)管理員使用，而有些系統(tǒng)過程通過授權(quán)可以被其他用戶使用。 系統(tǒng)存儲過程的局部例如如下：sp_addtype：用于定義一個用戶定義數(shù)據(jù)類型；sp_configure：用于管理效勞器配置選項設置；xp_sendmail：用于發(fā)送電子郵件或?qū)ず粜畔?；sp_stored_procedures：用于返回當前數(shù)據(jù)庫中的存儲過程的清單；sp_help：用于顯示參數(shù)清單和其數(shù)據(jù)類型；sp_depends：用于顯示存儲過程依據(jù)的對象或者依據(jù)存儲過程的對象；sp_helptext：用于顯示存儲過程的定義文本；sp_rename：用于修改當前數(shù)據(jù)庫中用戶對象的名稱。3.5 其他語言元素 3.5.1 注釋注釋是

36、程序代碼中不執(zhí)行的文本字符串也稱為注解。使用注釋對代碼進行說明，不僅能使程序易讀易懂，而且有助于日后的管理和維護。注釋通常用于記錄程序名稱、作者姓名和主要代碼更改的日期。注釋還可以用于描述復雜的計算或者解釋編程的方法。在SQL Server中，可以使用兩種類型的注釋字符：一種是ANSI標準的注釋符“-，它用于單行注釋；另一種是與C語言相同的程序注釋符號，即“/* */?！?*用于注釋文字的開頭，“*/用于注釋文字的結(jié)尾，利用它們可以在程序中標識多行文字為注釋。當然，單行注釋也可以使用“/* */，我們只需將注釋行以“/*開頭并以“*/結(jié)尾即可。反之，段落注釋也可以使用“-，只需使段落注釋的每一

37、行都以“-開頭即可。 例3-25使用兩種注釋類型的例子。 程序清單如下： USE AdventureWorks GO - First line of a multiple-line comment. - Second line of a multiple-line comment. GO /* 注釋語句的第一行. 注釋語句的第二行. */ GO - 在Transact-SQL語言調(diào)試過程中使用注釋語句。 3.5.2 變量 變量是一種語言中必不可少的組成局部。Transact-SQL語言中有兩種形式的變量，一種是用戶自己定義的局部變量，另外一種是系統(tǒng)提供的全局變量。1. 局部變量局部變量是一個能

38、夠擁有特定數(shù)據(jù)類型的對象，它的作用范圍僅限制在程序內(nèi)部。局部變量被引用時要在其名稱前加上標志“，而且必須先用DECLARE命令定義后才可以使用。 定義局部變量的語法形式如下： DECLAER local_variable data_type n其中，參數(shù)local_variable用于指定局部變量的名稱，變量名必須以符號開頭，并且局部變量名必須符合SQL Server的命名規(guī)那么。參數(shù)data_type用于設置局部變量的數(shù)據(jù)類型及其大小。data_type可以是任何由系統(tǒng)提供的或用戶定義的數(shù)據(jù)類型。但是，局部變量不能是 text，ntext 或 image 數(shù)據(jù)類型。使用DECLARE命令聲明

39、并創(chuàng)立局部變量之后，會將其初始值設為NULL，如果想要設定局部變量的值，必須使用SELECT命令或者SET命令。其語法形式為： SET local_variable = expression 或者 SELECT local_variable = expression ,.n 其中，參數(shù)local_variable是給其賦值并聲明的局部變量，參數(shù)expression是任何有效的SQL Server表達式。 1. 局部變量例3-26創(chuàng)立一個myvar 變量，然后將一個字符串值放在變量中，最后輸出 myvar 變量的值。 程序清單如下： DECLARE myvar char(20) select m

40、yvar = This is a test SELECT myvar GO例3-27通過查詢給變量賦值。 程序清單如下： USE adventureworks GO DECLARE rows int SET rows = (SELECT COUNT(*) FROM humanresources.employee)2. 全局變量 除了局部變量之外，SQL Server系統(tǒng)本身還提供了一些全局變量。全局變量是SQL Server系統(tǒng)內(nèi)部使用的變量，其作用范圍并不僅僅局限于某一程序，而是任何程序均可以隨時調(diào)用。全局變量通常存儲一些SQL Server的配置設定值和統(tǒng)計數(shù)據(jù)。用戶可以在程序中用全局變量

41、來測試系統(tǒng)的設定值或者是Transact-SQL命令執(zhí)行后的狀態(tài)值。在使用全局變量時應該注意以下幾點： 1全局變量不是由用戶的程序定義的，它們是在效勞器級定義的。 2用戶只能使用預先定義的全局變量。 3引用全局變量時，必須以標記符“開頭。 4局部變量的名稱不能與全局變量的名稱相同，否那么會在應用程序中出現(xiàn)不可預測的結(jié)果。例3-29顯示到當前日期和時間為止試圖登錄SQL Server的次數(shù)。 程序清單如下： SELECT GETDATE( ) AS 當前的時期和時間, CONNECTIONS AS 試圖登錄的次數(shù)3.5.3 運算符 運算符是一些符號，它們能夠用來執(zhí)行算術(shù)運算、字符串連接、賦值以及

42、在字段、常量和變量之間進行比擬。在SQL Server 2005中，運算符主要有以下六大類：算術(shù)運算符、賦值運算符、位運算符、比擬運算符、邏輯運算符和字符串串聯(lián)運算符。 1算術(shù)運算符 算術(shù)運算符可以在兩個表達式上執(zhí)行數(shù)學運算，這兩個表達式可以是數(shù)字數(shù)據(jù)類型分類的任何數(shù)據(jù)類型。算術(shù)運算符包括加+、減、乘*、除/和取模%。 2賦值運算符 Transact-SQL 中只有一個賦值運算符，即=。賦值運算符使我們能夠?qū)?shù)據(jù)值指派給特定的對象。另外，還可以使用賦值運算符在列標題和為列定義值的表達式之間建立關(guān)系。 3位運算符位運算符使我們能夠在整型數(shù)據(jù)或者二進制數(shù)據(jù)image 數(shù)據(jù)類型除外之間執(zhí)行位操作。此

43、外，在位運算符左右兩側(cè)的操作數(shù)不能同時是二進制數(shù)據(jù)。表3-1列出了所有的位運算符及其含義。 運算符含義&按位 AND按位 AND兩個操作數(shù)|按位 OR按位 OR兩個操作數(shù)按位互斥 OR按位互斥 OR兩個操作數(shù)4比擬運算符比擬運算符亦稱為關(guān)系運算符，用于比擬兩個表達式的大小或是否相同，其比擬的結(jié)果是布爾值，即TRUE表示表達式的結(jié)果為真、FALSE表示表達式的結(jié)果為假以及UNKNOWN。除了 text，ntext 或 image 數(shù)據(jù)類型的表達式外，比擬運算符可以用于所有的表達式。5邏輯運算符邏輯運算符可以把多個邏輯表達式連接起來。邏輯運算符包括AND、OR和NOT等運算符。邏輯運算符和比擬運算

44、符一樣，返回帶有 TRUE 或 FALSE 值的布爾數(shù)據(jù)類型。 三個運算符的優(yōu)先級別為：NOT，AND，OR。6字符串串聯(lián)運算符字符串串聯(lián)運算符允許通過加號+進行字符串串聯(lián)，這個加號即被稱為字符串串聯(lián)運算符。例如對于語句SELECT abc+def，其結(jié)果為abcdef。 在SQL Server 2005中，運算符的優(yōu)先等級從高到低如下所示，如果優(yōu)先等級相同，那么按照從左到右的順序進行運算。1括號：；2乘、除、求模運算符：*，/，%；3加減運算符：，；4比擬運算符：=，=，=，!=，!，!；5位運算符：，&，|；6邏輯運算符：NOT；7邏輯運算符：AND；8邏輯運算符：OR。 3.5.4 函數(shù)

45、 在Transact-SQL語言中，函數(shù)被用來執(zhí)行一些特殊的運算以支持SQL Server的標準命令。SQL Server包含多種不同的函數(shù)用以完成各種工作，每一個函數(shù)都有一個名稱，在名稱之后有一對小括號，如：gettime( )。大局部的函數(shù)在小括號中需要一個或者多個參數(shù)。Transact-SQL 編程語言提供了四種函數(shù)：行集函數(shù)、聚合函數(shù)、Ranking函數(shù)、標量函數(shù)。 1. 行集函數(shù)行集函數(shù)可以在Transact-SQL語句中當作表引用。例3-33通過行集函數(shù)OPENQUERY()執(zhí)行一個分布式查詢，以便從效勞器local中提取表department中的記錄。 程序清單如下： selec

46、t * from openquery(local,select * from department) 2. 聚合函數(shù)聚合函數(shù)用于對一組值進行計算并返回一個單一的值。除COUNT 函數(shù)之外，聚合函數(shù)忽略空值。聚合函數(shù)經(jīng)常與 SELECT 語句的 GROUP BY 子句一同使用。僅在以下項中聚合函數(shù)允許作為表達式使用：SELECT 語句的選擇列表子查詢或外部查詢；COMPUTE 或 COMPUTE BY 子句；HAVING 子句。 計算 Adventure Works Cycles 的副總所用的平均休假小時數(shù)以及總的病假小時數(shù)。對檢索到的所有行，每個聚合函數(shù)都生成一個單獨的匯總值。程序清單如下。U

47、SE AdventureWorks;GOSELECT AVG(VacationHours)as Average vacation hours, SUM (SickLeaveHours) as Total sick leave hoursWHERE Title LIKE Vice President% 3. Ranking函數(shù)Ranking函數(shù)為查詢結(jié)果數(shù)據(jù)集分區(qū)中的每一行返回一個序列值。依據(jù)此函數(shù)，一些行可能取得和其他行一樣的序列值。Transact-SQL提供以下一些Ranking函數(shù)：RANK；DENSE_RANK；NTILE；ROW_NUMBER。 4. 標量函數(shù)標量函數(shù)用于對傳遞給它的

48、一個或者多個參數(shù)值進行處理和計算，并返回一個單一的值。標量函數(shù)可以應用在任何一個有效的表達式中。標量函數(shù)可分為如表3-4所示的幾大類 函數(shù)分類解釋配置函數(shù)返回當前的配置信息游標函數(shù)返回有關(guān)游標的信息日期和時間函數(shù)對日期和時間輸入值進行處理數(shù)學函數(shù)對作為函數(shù)參數(shù)提供的輸入值執(zhí)行計算元數(shù)據(jù)函數(shù)返回有關(guān)數(shù)據(jù)庫和數(shù)據(jù)庫對象的信息平安函數(shù)返回有關(guān)用戶和角色的信息字符串函數(shù)對字符串char 或 varchar輸入值執(zhí)行操作系統(tǒng)函數(shù)執(zhí)行操作并返回有關(guān)SQL Server中的值、對象和設置的信息系統(tǒng)統(tǒng)計函數(shù)返回系統(tǒng)的統(tǒng)計信息文本和圖像函數(shù)對文本或圖像輸入值或列執(zhí)行操作，返回有關(guān)這些值的信息1字符串函數(shù)字符串函

49、數(shù)可以對二進制數(shù)據(jù)、字符串和表達式執(zhí)行不同的運算，大多數(shù)字符串函數(shù)只能用于char和varchar數(shù)據(jù)類型以及明確轉(zhuǎn)換成char和varchar的數(shù)據(jù)類型，少數(shù)幾個字符串函數(shù)也可以用于binary和varbinary數(shù)據(jù)類型。 字符串函數(shù)可以分為以下幾大類：根本字符串函數(shù)：UPPER，LOWER，SPACE，REPLICATE，STUFF，REVERSE，LTRIM，RTRIM。字符串查找函數(shù)：CHARINDEX，PATINDEX。長度和分析函數(shù)：DATALENGTH，SUBSTRING，RIGHT。轉(zhuǎn)換函數(shù)：ASCH，CHAR，STR，SOUNDEX，DIFFERENCE。例3-38使用 L

50、TRIM 函數(shù)刪除字符變量中的起始空格。程序清單如下：DECLARE string_to_trim varchar(60)SET string_to_trim = Five spaces are at the beginning of this string.SELECT Here is the string without the leading spaces: + LTRIM(string_to_trim) 例3-39使用可選的start_location參數(shù)從addressline1列的第2個字符開始查找“shoe程序清單如下。USE adventureworksWHERE addres

51、sid = 5 例3-40顯示如何只返回字符串的一局部。該查詢在一列中返回person.contact表中的姓氏，在另一列中返回person.contact表中的名字首字母。程序清單如下：USE adventureworksSELECT lastname, SUBSTRING(firstname, 1, 1)ORDER BY lastname 例3-41在第一個字符串a(chǎn)bcdef中刪除從第二個位置字符 b開始的三個字符，然后在刪除的起始位置插入第二個字符串，創(chuàng)立并返回一個字符串。程序清單如下：SELECT STUFF(abcdef, 2, 3, ijklmn) 2日期和時間函數(shù) 日期和時間函數(shù)

52、用于對日期和時間數(shù)據(jù)進行各種不同的處理和運算，并返回一個字符串、數(shù)字值或日期和時間值。與其他函數(shù)一樣，可以在SELECT語句的SELECT和WHERE子句以及表達式中使用日期和時間函數(shù)。 函數(shù)參數(shù)功能DATEADDdatepart,number,date以datepart指定的方式，返回date加上number之和DATEDIFFdatepart,date1,date2以datepart指定的方式，返回date2與date1之差DATENAMEdatepart,date返回日期date中datepart指定局部所對應的字符串DATEPARTdatepart,date返回日期date中datep

53、art指定局部所對應的整數(shù)值DAYdate返回指定日期的天數(shù)GETDATE返回當前的日期和時間MONTHdate返回指定日期的月份數(shù)YEARdate返回指定日期的年份數(shù)例3-42顯示在humanresources.employee 表中雇用日期到當前日期間的天數(shù)。程序清單如下。USE adventureworksSELECT DATEDIFF(day, hiredate, getdate() AS diffdays例3-43從GETDATE函數(shù)返回的日期中提取月份名。程序清單如下：SELECT DATENAME(month, getdate() AS Month Name 3數(shù)學函數(shù)數(shù)學函數(shù)用

54、于對數(shù)字表達式進行數(shù)學運算并返回運算結(jié)果。數(shù)學函數(shù)可以對SQL Server提供的數(shù)字數(shù)據(jù)decimal、integer、float、real、money、smallmoney、smallint 和 tinyint進行處理。在SQL Server中，常用的數(shù)學函數(shù)如表3-8所示。 例3-45在同一表達式中使用CEILING()，F(xiàn)LOOR()，ROUND()函數(shù)。程序清單如下：select ceiling(13.4), floor(13.4), round(13.4567,3) 4系統(tǒng)函數(shù)系統(tǒng)函數(shù)用于返回有關(guān)SQL Server系統(tǒng)、用戶、數(shù)據(jù)庫和數(shù)據(jù)庫對象的信息。系統(tǒng)函數(shù)可以讓用戶在得到信息

55、后，使用條件語句，根據(jù)返回的信息進行不同的操作。與其他函數(shù)一樣，可以在SELECT語句的SELECT和WHERE子句以及表達式中使用系統(tǒng)函數(shù)。轉(zhuǎn)換函數(shù)有兩個：CONVERT和CAST。CAST函數(shù)允許把一個數(shù)據(jù)類型強制轉(zhuǎn)換為另一種數(shù)據(jù)類型，其語法形式為： CAST( expression AS data_type )CONVERT函數(shù)允許用戶把表達式從一種數(shù)據(jù)類型轉(zhuǎn)換成另一種數(shù)據(jù)類型，還允許把日期轉(zhuǎn)換成不同的樣式，其語法形式為： CONVERT (data_type(length),expression ,style) 例3-46 例如檢索列表價格的第一位是 3 的產(chǎn)品的名稱，并將ListPr

56、ice轉(zhuǎn)換為 int。程序清單如下。USE AdventureWorksGOSELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPriceWHERE CAST(ListPrice AS int) LIKE 3%GO例3-47用style 參數(shù)將當前日期轉(zhuǎn)換為不同格式的字符串。程序清單如下：SELECT 101=CONVERT(char, GETDATE(), 101),1=CONVERT(char, GETDATE(), 1),112=CONVERT(char, GETDATE(), 112) 程序清單如下。USE adventureworksS

57、ELECT COL_NAME(OBJECT_ID(person.contact), 1) 例3-49檢查 sysdatabases 中的每一個數(shù)據(jù)庫，使用數(shù)據(jù)庫標識號來確定數(shù)據(jù)庫名稱。程序清單如下：USE masterSELECT dbid, DB_NAME(dbid) AS DB_NAMEFROM sysdatabasesORDER BY dbid 3.5.5 流程控制語句 流程控制語句是指那些用來控制程序執(zhí)行和流程分支的語句，在SQL Server 2005中，流程控制語句主要用來控制SQL語句、語句塊或者存儲過程的執(zhí)行流程。 1 IFELSE語句IFELSE語句是條件判斷語句，其中，EL

58、SE子句是可選的，最簡單的IF語句沒有ELSE子句局部。IFELSE語句用來判斷當某一條件成立時執(zhí)行某段程序，條件不成立時執(zhí)行另一段程序。SQL Server允許嵌套使用IFELSE語句，而且嵌套層數(shù)沒有限制。IFELSE語句的語法形式為： IF Boolean_expression sql_statement | statement_block ELSE sql_statement | statement_block 例3-51顯示帶有語句塊的IF條件。如果產(chǎn)品的平均價格不低于$15，那么就顯示文本：Average price is more than $15程序清單如下。USE adven

59、tureworksIF (SELECT AVG(listprice) FROM duct WHERE color = blue) $15BEGIN PRINT The following product are excellent: PRINT SELECT SUBSTRING(name, 1, 15) AS name WHERE color = blue ENDELSE PRINT Average price is more than $15.2 BEGINEND語句 BEGINEND語句能夠?qū)⒍鄠€Transact-SQL語句組合成一個語句塊，并將它們視為一個單元處理。在條件語句和循環(huán)等控制

60、流程語句中，當符合特定條件便要執(zhí)行兩個或者多個語句時，就需要使用BEGINEND語句。 BEGINEND語句的語法形式為：BEGIN sql_statement| statement_block END 例3-52利用 BEGIN 和 END 語句使得 IF 語句在取值為 FALSE 時跳過語句塊。程序清單如下：IF (ERROR 0)BEGIN SET ErrorSaveVariable = ERROR PRINT Error encountered, + CAST(ErrorSaveVariable AS VARCHAR(10)END 3GO 語句 Go 語句是批的結(jié)束語句。批是一起提交并