Transact-SQL語言課件

1、Transact-SQL語言第第3 3章章 Transact-SQLTransact-SQL語言語言 Transact-SQL語言3.1 數(shù)據(jù)定義語言 數(shù)據(jù)定義語言（DDL）是指用來定義和管理數(shù)據(jù)庫以及數(shù)據(jù)庫中各種對象的語句，這些語句包括CREATE、ALTER和DROP等。在SQL Server 2005中，數(shù)據(jù)庫對象包括表、視圖、觸發(fā)器、存儲過程、規(guī)則、默認(rèn)、用戶自定義的數(shù)據(jù)類型等。這些對象的創(chuàng)建、修改和刪除等都可以通過使用CREATE，ALTER，DROP等語句來完成。Transact-SQL語言3.1 數(shù)據(jù)定義語言例3-1 創(chuàng)建數(shù)據(jù)庫表/*下面的例子將創(chuàng)建表S。*/CREATE TAB

2、LE S( sno char(10) NOT NULL /*學(xué)號字段*/ CONSTRAINT 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/*系別字段*/ )Transact-SQL語言3.1 數(shù)據(jù)定義語言例3-2 修改S表，增加一個班號列程序清單如下：ALTER TABLE S AD

3、DCLASS_NO CHAR(6) Transact-SQL語言3.1 數(shù)據(jù)定義語言例3-3刪除S表程序清單如下： DROP table STransact-SQL語言3.2 數(shù)據(jù)操縱語言（DML） 數(shù)據(jù)操縱語言是指用來查詢、添加、修改和刪除數(shù)據(jù)庫中數(shù)據(jù)的語句，這些語句包括SELECT、INSERT、UPDATE、DELETE等。 將在第6章詳細(xì)講解SELECT語句的語法及實例。 Transact-SQL語言3.2.2INSERT語句 INSERT語句用于向數(shù)據(jù)庫表或者視圖中加入一行數(shù)據(jù)。INSERT語句的語法形式如下： INSERT INTO table_or_view (column_li

4、st) VALUES(data_values) 其中，table_or_view是指要插入新記錄的表或視圖；column_list是可選項，指定待添加數(shù)據(jù)的列； VALUES子句指定待添加數(shù)據(jù)的具體值。列名的排列順序不一定要和表定義時的順序一致。但當(dāng)指定列名表時VALUES子句值的排列順序必須和列名表中的列名排列順序一致，個數(shù)相等，數(shù)據(jù)類型一一對應(yīng)。 Transact-SQL語言3.2.2INSERT語句在進(jìn)行數(shù)據(jù)插入操作時須注意以下幾點：（1）必須用逗號將各個數(shù)據(jù)分開，字符型數(shù)據(jù)要用單引號括起來。（2）INTO子句中沒有指定列名，則新插入的記錄必須在每個屬性列上均有值，且VALUES子句中值

5、的排列順序要和表中各屬性列的排列順序一致。（3）將VALUES子句中的值按照INTO子句中指定列名的順序插入到表中。（4）對于INTO子句中沒有出現(xiàn)的列，則新插入的記錄在這些列上將取空值，如上例的SCORE即賦空值。但在表定義時有NOT NULL約束的屬性列不能取空值。 Transact-SQL語言3.2.2INSERT語句例3-5 創(chuàng)建SC表（學(xué)生選課表），并向SC表中插入一條選課記錄（S7,C1）。程序清單如下：CREATE TABLE SC( sno char(10) NOT NULL, cno char(2) NULL, /*課程編號字段*/ score numerical(4,1)

6、NULL /*成績字段*/ ) GoINSERT INTO SC (sno,cno) VALUES (3130050101, c1)GoTransact-SQL語言3.2.2INSERT語句下面是插入與列順序不同的數(shù)據(jù)的例子。例3-6 使用 column_list 及 VALUES 列表顯式地指定將被插入每個列的值。程序清單如下：CREATE TABLE T1 ( column_1 int, column_2 varchar(30) GoINSERT T1 (column_2, column_1) VALUES (This is a test,1) Transact-SQL語言3.2.2INS

7、ERT語句插入多行數(shù)據(jù)的語法格式為：INSERT INTO table_or_view (column_list) 子查詢例3-7 求出各位學(xué)生的平均成績，把結(jié)果存放在新表AVGSCORE中。程序清單如下：/*首先建立新表AVGSCORE，用來存放學(xué)號和學(xué)生的平均成績。*/CREATE TABLE AVGSCORE(SNO CHAR(10),AVGSCORE SMALLINT) Go/*利用子查詢求出SC表中各位學(xué)生的平均成績，把結(jié)果存放在新表AVGSCORE中。*/INSERT INTO AVGSCORESELECT SNO,AVG(SCORE) FROM SCGROUP BY SNO Tr

8、ansact-SQL語言3.2.3UPDATE語句 UPDATE語句用于修改數(shù)據(jù)庫表或視圖中特定記錄或者字段的數(shù)據(jù)，其語法形式如下： UPDATE table_or_view SET =,= WHERE 其中：table_or_view是指要修改的表或視圖；SET子句給出要修改的列及其修改后的值，其中column為要修改的列名，expression為其修改后的值；WHERE子句指定待修改的記錄應(yīng)當(dāng)滿足的條件，WHERE子句省略時，則修改表中的所有記錄。 Transact-SQL語言3.2.3UPDATE語句 例3-8一個帶有WHERE條件的修改語句。程序清單如下：use adventurewo

9、rksgoupdate person.address set city=Boston where addressid=1 例3-9 將所有學(xué)生年齡增加1歲程序清單如下：UPDATE S SET AGE=AGE+1 Transact-SQL語言3.2.3UPDATE語句 例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

10、SET SAL=SAL+100 WHERE TNO IN (SELECT T.TNO FROM T,TC WHERE T.TNO=TC.TNO AND TC.CNO=C5) /*通過連接查詢找到講授C5課程的教師編號。*/Transact-SQL語言3.2.4DELETE語句 使用DELETE語句可以刪除表中的一行或多行記錄，其語法格式為： DELETE FROM table_or_view WHERE 其中：table_or_view是指要刪除數(shù)據(jù)的表或視圖；WHERE子句指定待刪除的記錄應(yīng)當(dāng)滿足的條件，WHERE子句省略時，則刪除表中的所有記錄。 下面是刪除一行記錄的例子。 例3-14 刪

11、除張益琳教師的記錄。 程序清單如下： DELETE FROM T WHERE TN= 張益琳Transact-SQL語言3.2.4DELETE語句 下面是刪除多行記錄的例子。 例 3-15 刪除所有教師的授課記錄。 程序清單如下： DELETE FROM TC 執(zhí)行此語句后，TC表即為一個空表，但其定義仍存在數(shù)據(jù)字典中。 例3-16 刪除李明同學(xué)選課的記錄。 DELETE FROM SC WHERE SNO= (SELECT SNO FROM S WHERE SN= 李明)Transact-SQL語言3.3 數(shù)據(jù)控制語言 數(shù)據(jù)控制語言（DCL）是用來設(shè)置或更改數(shù)據(jù)庫用戶或角色權(quán)限的語句，包括G

12、RANT，DENY，REVOKE等語句。在默認(rèn)狀態(tài)下，只有sysadmin，dbcreator，db_owner或db_securityadmin等人員才有權(quán)力執(zhí)行數(shù)據(jù)控制語言。Transact-SQL語言3.3.1GRANT語句1、語句權(quán)限與角色的授予 SQL語言使用GRANT語句為用戶授予語句權(quán)限的語法格式為： GRANT | ,| TO |PUBLIC,| WITH ADMIN OPTION 其語義為：將指定的語句權(quán)限授予指定的用戶或角色。其中：（1）PULBIC代表數(shù)據(jù)庫中的全部用戶；（2）WITH ADMIN OPTION為可選項，指定后則允許被授權(quán)的用戶將指定的系統(tǒng)特權(quán)或角色再授予

13、其他用戶或角色。Transact-SQL語言3.3.1GRANT語句1、語句權(quán)限與角色的授予 例3-17給用戶 Mary 和 John 以及 Windows NT 組 CorporateBobJ 授予多個語句權(quán)限。 程序清單如下： GRANT CREATE DATABASE, CREATE TABLE TO Mary, John, CorporateBobJ 例3-18 為用戶ZhangYiLin授予CREATE TABLE的語句權(quán)限。 程序清單如下： GRANT CREATE TABLE TO ZhangYiLinTransact-SQL語言3.3.1GRANT語句2、對象權(quán)限與角色的授予數(shù)

14、據(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,| WITH ADMIN OPTION 其語義為：將指定的操作對象的對象權(quán)限授予指定的用戶或角色。其中： （1）ALL代表所有的對象權(quán)限。 （2）列名用于指定要授權(quán)的數(shù)據(jù)庫對象的一列或多列。如果不指定列名，被授權(quán)的用戶將在數(shù)據(jù)庫對象的所有列上均擁有指定的特權(quán)。實際上，只有當(dāng)授予INSERT、U

15、PDATE權(quán)限時才需指定列名。 （3）ON子句用于指定要授予對象權(quán)限的數(shù)據(jù)庫對象名，可以是基本表名、視圖名等。 （4）WITH ADMIN OPTION為可選項，指定后則允許被授權(quán)的用戶將權(quán)限再授予其他用戶或角色。Transact-SQL語言3.3.1GRANT語句2、對象權(quán)限與角色的授予例3-19在權(quán)限層次中授予對象權(quán)限。首先，給所有用戶授予 SELECT 權(quán)限，然后，將特定的權(quán)限授予用戶 Mary，John 和 Tom。 程序清單如下： GRANT SELECT ON s TO public GO GRANT INSERT, UPDATE, DELETE ON s TO Mary, Joh

16、n, Tom GOTransact-SQL語言3.3.1GRANT語句2、對象權(quán)限與角色的授予 例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) ON T TO USER4Transact-SQL語言3.3.2REVOKE語句 REVOKE語句是與GRANT語句

17、相反的語句，它能夠?qū)⒁郧霸诋?dāng)前數(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)者收回，收回對象權(quán)限仍然使用REVOKE語句，其語法格式為： REVOKE | ,| FR

18、OM |PUBLIC,| Transact-SQL語言3.3.2REVOKE語句 例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)限。 Transact-SQL語言3.3.2REVOKE語句 例3-23首先從 public 角色中收回 SELECT 權(quán)限

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

20、| Transact-SQL語言3.3.3DENY語句 例3-24首先給 public 角色授予 SELECT 權(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 Transact-SQL語言3.4 系統(tǒng)存儲過程 系統(tǒng)存儲過程是SQL Server系統(tǒng)創(chuàng)建的存儲過程，它的目的在于能夠方便地從系統(tǒng)表中查詢信息，或者完成與更新數(shù)據(jù)庫表相關(guān)的管理任務(wù)或其他的系統(tǒng)管理任務(wù)

21、。系統(tǒng)存儲過程可以在任意一個數(shù)據(jù)庫中執(zhí)行。系統(tǒng)存儲過程創(chuàng)建并存放于系統(tǒng)數(shù)據(jù)庫master中，并且名稱以sp_或者xp_開頭。一些系統(tǒng)過程只能由系統(tǒng)管理員使用，而有些系統(tǒng)過程通過授權(quán)可以被其他用戶使用。 Transact-SQL語言3.4 系統(tǒng)存儲過程 系統(tǒng)存儲過程的部分示例如下： sp_addtype：用于定義一個用戶定義數(shù)據(jù)類型； sp_configure：用于管理服務(wù)器配置選項設(shè)置； xp_sendmail：用于發(fā)送電子郵件或?qū)ず粜畔ⅲ?sp_stored_procedures：用于返回當(dāng)前數(shù)據(jù)庫中的存儲過程的清單； sp_help：用于顯示參數(shù)清單和其數(shù)據(jù)類型； sp_depends：用

22、于顯示存儲過程依據(jù)的對象或者依據(jù)存儲過程的對象； sp_helptext：用于顯示存儲過程的定義文本； sp_rename：用于修改當(dāng)前數(shù)據(jù)庫中用戶對象的名稱。Transact-SQL語言3.5 其他語言元素 3.5.1 注釋3.5.2 變量3.5.3 運算符3.5.4 函數(shù)3.5.5 流程控制語句 Transact-SQL語言3.5.1 注釋 注釋是程序代碼中不執(zhí)行的文本字符串（也稱為注解）。使用注釋對代碼進(jìn)行說明，不僅能使程序易讀易懂，而且有助于日后的管理和維護。注釋通常用于記錄程序名稱、作者姓名和主要代碼更改的日期。注釋還可以用于描述復(fù)雜的計算或者解釋編程的方法。 在SQL Server

23、中，可以使用兩種類型的注釋字符：一種是ANSI標(biāo)準(zhǔn)的注釋符“-”，它用于單行注釋；另一種是與C語言相同的程序注釋符號，即“/* */”?！?*”用于注釋文字的開頭，“*/”用于注釋文字的結(jié)尾，利用它們可以在程序中標(biāo)識多行文字為注釋。當(dāng)然，單行注釋也可以使用“/* */”，我們只需將注釋行以“/*”開頭并以“*/”結(jié)尾即可。反之，段落注釋也可以使用“-”，只需使段落注釋的每一行都以“-”開頭即可。 Transact-SQL語言3.5.1 注釋 例3-25使用兩種注釋類型的例子。 程序清單如下： USE AdventureWorks GO - First line of a multiple-li

24、ne comment. - Second line of a multiple-line comment. SELECT * FROM person.address GO /* 注釋語句的第一行. 注釋語句的第二行. */ SELECT * FROM Production.Product GO - 在Transact-SQL語言調(diào)試過程中使用注釋語句。 Transact-SQL語言3.5.2 變量 變量是一種語言中必不可少的組成部分。Transact-SQL語言中有兩種形式的變量，一種是用戶自己定義的局部變量，另外一種是系統(tǒng)提供的全局變量。1. 局部變量 局部變量是一個能夠擁有特定數(shù)據(jù)類型的對

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

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

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

28、SQL Server的配置設(shè)定值和統(tǒng)計數(shù)據(jù)。用戶可以在程序中用全局變量來測試系統(tǒng)的設(shè)定值或者是Transact-SQL命令執(zhí)行后的狀態(tài)值。在使用全局變量時應(yīng)該注意以下幾點： （1）全局變量不是由用戶的程序定義的，它們是在服務(wù)器級定義的。 （2）用戶只能使用預(yù)先定義的全局變量。 （3）引用全局變量時，必須以標(biāo)記符“”開頭。 （4）局部變量的名稱不能與全局變量的名稱相同，否則會在應(yīng)用程序中出現(xiàn)不可預(yù)測的結(jié)果。Transact-SQL語言3.5.2 變量 2. 全局變量 例3-29顯示到當(dāng)前日期和時間為止試圖登錄SQL Server的次數(shù)。 程序清單如下： SELECT GETDATE( ) AS

29、當(dāng)前的時期和時間, CONNECTIONS AS 試圖登錄的次數(shù)Transact-SQL語言3.5.3 運算符 運算符是一些符號，它們能夠用來執(zhí)行算術(shù)運算、字符串連接、賦值以及在字段、常量和變量之間進(jìn)行比較。在SQL Server 2005中，運算符主要有以下六大類：算術(shù)運算符、賦值運算符、位運算符、比較運算符、邏輯運算符和字符串串聯(lián)運算符。 1算術(shù)運算符 算術(shù)運算符可以在兩個表達(dá)式上執(zhí)行數(shù)學(xué)運算，這兩個表達(dá)式可以是數(shù)字?jǐn)?shù)據(jù)類型分類的任何數(shù)據(jù)類型。算術(shù)運算符包括加（+）、減（）、乘（*）、除（/）和取模（%）。 2賦值運算符 Transact-SQL 中只有一個賦值運算符，即（=）。賦值運算符

30、使我們能夠?qū)?shù)據(jù)值指派給特定的對象。另外，還可以使用賦值運算符在列標(biāo)題和為列定義值的表達(dá)式之間建立關(guān)系。 Transact-SQL語言3.5.3 運算符3位運算符 位運算符使我們能夠在整型數(shù)據(jù)或者二進(jìn)制數(shù)據(jù)（image 數(shù)據(jù)類型除外）之間執(zhí)行位操作。此外，在位運算符左右兩側(cè)的操作數(shù)不能同時是二進(jìn)制數(shù)據(jù)。表3-1列出了所有的位運算符及其含義。 運算符含義&（按位 AND）按位 AND（兩個操作數(shù)）|（按位 OR）按位 OR（兩個操作數(shù)）（按位互斥 OR）按位互斥 OR（兩個操作數(shù)）表3-1 位運算符 Transact-SQL語言3.5.3 運算符4比較運算符 比較運算符亦稱為關(guān)系運算符，用于比較

31、兩個表達(dá)式的大小或是否相同，其比較的結(jié)果是布爾值，即TRUE（表示表達(dá)式的結(jié)果為真）、FALSE（表示表達(dá)式的結(jié)果為假）以及UNKNOWN。除了 text，ntext 或 image 數(shù)據(jù)類型的表達(dá)式外，比較運算符可以用于所有的表達(dá)式。5邏輯運算符 邏輯運算符可以把多個邏輯表達(dá)式連接起來。邏輯運算符包括AND、OR和NOT等運算符。邏輯運算符和比較運算符一樣，返回帶有 TRUE 或 FALSE 值的布爾數(shù)據(jù)類型。 三個運算符的優(yōu)先級別為：NOT，AND，OR。6字符串串聯(lián)運算符 字符串串聯(lián)運算符允許通過加號（+）進(jìn)行字符串串聯(lián)，這個加號即被稱為字符串串聯(lián)運算符。例如對于語句SELECT abc

32、+def，其結(jié)果為abcdef。 Transact-SQL語言3.5.3 運算符 在SQL Server 2005中，運算符的優(yōu)先等級從高到低如下所示，如果優(yōu)先等級相同，則按照從左到右的順序進(jìn)行運算。（1）括號：（）；（2）乘、除、求模運算符：*，/，%；（3）加減運算符：，；（4）比較運算符：=，=，=，!=，!，!；（5）位運算符：，&，|；（6）邏輯運算符：NOT；（7）邏輯運算符：AND；（8）邏輯運算符：OR。 Transact-SQL語言3.5.4 函數(shù) 在Transact-SQL語言中，函數(shù)被用來執(zhí)行一些特殊的運算以支持SQL Server的標(biāo)準(zhǔn)命令。SQL Server包含多種

33、不同的函數(shù)用以完成各種工作，每一個函數(shù)都有一個名稱，在名稱之后有一對小括號，如：gettime( )。大部分的函數(shù)在小括號中需要一個或者多個參數(shù)。 Transact-SQL 編程語言提供了四種函數(shù)：行集函數(shù)、聚合函數(shù)、Ranking函數(shù)、標(biāo)量函數(shù)。 Transact-SQL語言3.5.4 函數(shù)1. 行集函數(shù) 行集函數(shù)可以在Transact-SQL語句中當(dāng)作表引用。 例3-33通過行集函數(shù)OPENQUERY()執(zhí)行一個分布式查詢，以便從服務(wù)器local中提取表department中的記錄。 程序清單如下： select * from openquery(local,select * from d

34、epartment) Transact-SQL語言3.5.4 函數(shù)2. 聚合函數(shù) 聚合函數(shù)用于對一組值進(jìn)行計算并返回一個單一的值。除COUNT 函數(shù)之外，聚合函數(shù)忽略空值。聚合函數(shù)經(jīng)常與 SELECT 語句的 GROUP BY 子句一同使用。僅在下列項中聚合函數(shù)允許作為表達(dá)式使用：SELECT 語句的選擇列表（子查詢或外部查詢）；COMPUTE 或 COMPUTE BY 子句；HAVING 子句。 Transact-SQL語言3.5.4 函數(shù)2. 聚合函數(shù)計算 Adventure Works Cycles 的副總所用的平均休假小時數(shù)以及總的病假小時數(shù)。對檢索到的所有行，每個聚合函數(shù)都生成一個單

35、獨的匯總值。程序清單如下。USE AdventureWorks;GOSELECT AVG(VacationHours)as Average vacation hours, SUM (SickLeaveHours) as Total sick leave hoursFROM HumanResources.EmployeeWHERE Title LIKE Vice President% Transact-SQL語言3.5.4 函數(shù)3. Ranking函數(shù) Ranking函數(shù)為查詢結(jié)果數(shù)據(jù)集分區(qū)中的每一行返回一個序列值。依據(jù)此函數(shù)，一些行可能取得和其他行一樣的序列值。Transact-SQL提供以下

36、一些Ranking函數(shù)：RANK；DENSE_RANK；NTILE；ROW_NUMBER。 Transact-SQL語言3.5.4 函數(shù)4. 標(biāo)量函數(shù)標(biāo)量函數(shù)用于對傳遞給它的一個或者多個參數(shù)值進(jìn)行處理和計算，并返回一個單一的值。標(biāo)量函數(shù)可以應(yīng)用在任何一個有效的表達(dá)式中。標(biāo)量函數(shù)可分為如表3-4所示的幾大類 函數(shù)分類解釋配置函數(shù)返回當(dāng)前的配置信息游標(biāo)函數(shù)返回有關(guān)游標(biāo)的信息日期和時間函數(shù)對日期和時間輸入值進(jìn)行處理數(shù)學(xué)函數(shù)對作為函數(shù)參數(shù)提供的輸入值執(zhí)行計算元數(shù)據(jù)函數(shù)返回有關(guān)數(shù)據(jù)庫和數(shù)據(jù)庫對象的信息安全函數(shù)返回有關(guān)用戶和角色的信息字符串函數(shù)對字符串（char 或 varchar）輸入值執(zhí)行操作系統(tǒng)函數(shù)

37、執(zhí)行操作并返回有關(guān)SQL Server中的值、對象和設(shè)置的信息系統(tǒng)統(tǒng)計函數(shù)返回系統(tǒng)的統(tǒng)計信息文本和圖像函數(shù)對文本或圖像輸入值或列執(zhí)行操作，返回有關(guān)這些值的信息表3-4 標(biāo)量函數(shù)的分類 Transact-SQL語言3.5.4 函數(shù)4. 標(biāo)量函數(shù)（1）字符串函數(shù)l字符串函數(shù)可以對二進(jìn)制數(shù)據(jù)、字符串和表達(dá)式執(zhí)行不同的運算，大多數(shù)字符串函數(shù)只能用于char和varchar數(shù)據(jù)類型以及明確轉(zhuǎn)換成char和varchar的數(shù)據(jù)類型，少數(shù)幾個字符串函數(shù)也可以用于binary和varbinary數(shù)據(jù)類型。 l字符串函數(shù)可以分為以下幾大類：基本字符串函數(shù)：UPPER，LOWER，SPACE，REPLICATE，

38、STUFF，REVERSE，LTRIM，RTRIM。字符串查找函數(shù)：CHARINDEX，PATINDEX。長度和分析函數(shù)：DATALENGTH，SUBSTRING，RIGHT。轉(zhuǎn)換函數(shù)：ASCH，CHAR，STR，SOUNDEX，DIFFERENCE。Transact-SQL語言3.5.4 函數(shù)4. 標(biāo)量函數(shù)（1）字符串函數(shù)例3-38使用 LTRIM 函數(shù)刪除字符變量中的起始空格。程序清單如下：DECLARE string_to_trim varchar(60)SET string_to_trim = Five spaces are at the beginning of this strin

39、g.SELECT Here is the string without the leading spaces: + LTRIM(string_to_trim) Transact-SQL語言3.5.4 函數(shù)4. 標(biāo)量函數(shù)（1）字符串函數(shù)例3-39使用可選的start_location參數(shù)從addressline1列的第2個字符開始查找“shoe”程序清單如下。USE adventureworksSELECT CHARINDEX(shoe, addressline1,2) FROM person.addressWHERE addressid = 5 Transact-SQL語言3.5.4 函數(shù)4.

40、 標(biāo)量函數(shù)（1）字符串函數(shù)例3-40顯示如何只返回字符串的一部分。該查詢在一列中返回person.contact表中的姓氏，在另一列中返回person.contact表中的名字首字母。程序清單如下：USE adventureworksSELECT lastname, SUBSTRING(firstname, 1, 1)FROM person.contactORDER BY lastname 例3-41在第一個字符串（abcdef）中刪除從第二個位置（字符 b）開始的三個字符，然后在刪除的起始位置插入第二個字符串，創(chuàng)建并返回一個字符串。程序清單如下：SELECT STUFF(abcdef, 2,

41、 3, ijklmn) Transact-SQL語言3.5.4 函數(shù)4. 標(biāo)量函數(shù)（2）日期和時間函數(shù) 日期和時間函數(shù)用于對日期和時間數(shù)據(jù)進(jìn)行各種不同的處理和運算，并返回一個字符串、數(shù)字值或日期和時間值。與其他函數(shù)一樣，可以在SELECT語句的SELECT和WHERE子句以及表達(dá)式中使用日期和時間函數(shù)。 函數(shù)參數(shù)功能DATEADD（datepart,number,date）以datepart指定的方式，返回date加上number之和DATEDIFF（datepart,date1,date2）以datepart指定的方式，返回date2與date1之差DATENAME（datepart,dat

42、e）返回日期date中datepart指定部分所對應(yīng)的字符串DATEPART（datepart,date）返回日期date中datepart指定部分所對應(yīng)的整數(shù)值DAY（date）返回指定日期的天數(shù)GETDATE（）返回當(dāng)前的日期和時間MONTH（date）返回指定日期的月份數(shù)YEAR（date）返回指定日期的年份數(shù)表3-6 日期和時間函數(shù)的類型 Transact-SQL語言3.5.4 函數(shù)4. 標(biāo)量函數(shù)（2）日期和時間函數(shù) 例3-42顯示在humanresources.employee 表中雇用日期到當(dāng)前日期間的天數(shù)。程序清單如下。USE adventureworksSELECT DATED

43、IFF(day, hiredate, getdate() AS diffdaysFROM humanresources.employee例3-43從GETDATE函數(shù)返回的日期中提取月份名。程序清單如下：SELECT DATENAME(month, getdate() AS Month Name Transact-SQL語言3.5.4 函數(shù)4. 標(biāo)量函數(shù)（3）數(shù)學(xué)函數(shù) 數(shù)學(xué)函數(shù)用于對數(shù)字表達(dá)式進(jìn)行數(shù)學(xué)運算并返回運算結(jié)果。數(shù)學(xué)函數(shù)可以對SQL Server提供的數(shù)字?jǐn)?shù)據(jù)（decimal、integer、float、real、money、smallmoney、smallint 和 tinyint）

44、進(jìn)行處理。在SQL Server中，常用的數(shù)學(xué)函數(shù)如表3-8所示。 Transact-SQL語言3.5.4 函數(shù)4. 標(biāo)量函數(shù)（3）數(shù)學(xué)函數(shù)例3-45在同一表達(dá)式中使用CEILING()，F(xiàn)LOOR()，ROUND()函數(shù)。程序清單如下：select ceiling(13.4), floor(13.4), round(13.4567,3) Transact-SQL語言3.5.4 函數(shù)4. 標(biāo)量函數(shù)（4）系統(tǒng)函數(shù) 系統(tǒng)函數(shù)用于返回有關(guān)SQL Server系統(tǒng)、用戶、數(shù)據(jù)庫和數(shù)據(jù)庫對象的信息。系統(tǒng)函數(shù)可以讓用戶在得到信息后，使用條件語句，根據(jù)返回的信息進(jìn)行不同的操作。與其他函數(shù)一樣，可以在SELE

45、CT語句的SELECT和WHERE子句以及表達(dá)式中使用系統(tǒng)函數(shù)。Transact-SQL語言3.5.4 函數(shù)4. 標(biāo)量函數(shù)（4）系統(tǒng)函數(shù) 轉(zhuǎn)換函數(shù)有兩個：CONVERT和CAST。 CAST函數(shù)允許把一個數(shù)據(jù)類型強制轉(zhuǎn)換為另一種數(shù)據(jù)類型，其語法形式為： CAST( expression AS data_type ) CONVERT函數(shù)允許用戶把表達(dá)式從一種數(shù)據(jù)類型轉(zhuǎn)換成另一種數(shù)據(jù)類型，還允許把日期轉(zhuǎn)換成不同的樣式，其語法形式為： CONVERT (data_type(length),expression ,style) Transact-SQL語言3.5.4 函數(shù)4. 標(biāo)量函數(shù)（4）系統(tǒng)函數(shù)

46、轉(zhuǎn)換函數(shù)有兩個：CONVERT和CAST。 CAST函數(shù)允許把一個數(shù)據(jù)類型強制轉(zhuǎn)換為另一種數(shù)據(jù)類型，其語法形式為： CAST( expression AS data_type ) CONVERT函數(shù)允許用戶把表達(dá)式從一種數(shù)據(jù)類型轉(zhuǎn)換成另一種數(shù)據(jù)類型，還允許把日期轉(zhuǎn)換成不同的樣式，其語法形式為： CONVERT (data_type(length),expression ,style) Transact-SQL語言3.5.4 函數(shù)4. 標(biāo)量函數(shù)（4）系統(tǒng)函數(shù)例3-46 示例檢索列表價格的第一位是 3 的產(chǎn)品的名稱，并將ListPrice轉(zhuǎn)換為 int。程序清單如下。USE AdventureWo

47、rksGOSELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPriceFROM Production.ProductWHERE CAST(ListPrice AS int) LIKE 3%GOTransact-SQL語言3.5.4 函數(shù)4. 標(biāo)量函數(shù)（4）系統(tǒng)函數(shù)例3-47用style 參數(shù)將當(dāng)前日期轉(zhuǎn)換為不同格式的字符串。程序清單如下：SELECT 101=CONVERT(char, GETDATE(), 101),1=CONVERT(char, GETDATE(), 1),112=CONVERT(char, GETDATE(), 112)

48、Transact-SQL語言3.5.4 函數(shù)4. 標(biāo)量函數(shù)（4）系統(tǒng)函數(shù)例3-48從adventureworks數(shù)據(jù)庫中返回person.contact表的首列名稱程序清單如下。USE adventureworksSELECT COL_NAME(OBJECT_ID(person.contact), 1) 例3-49檢查 sysdatabases 中的每一個數(shù)據(jù)庫，使用數(shù)據(jù)庫標(biāo)識號來確定數(shù)據(jù)庫名稱。程序清單如下：USE masterSELECT dbid, DB_NAME(dbid) AS DB_NAMEFROM sysdatabasesORDER BY dbid Transact-SQL語言3

49、.5.5 流程控制語句 流程控制語句是指那些用來控制程序執(zhí)行和流程分支的語句，在SQL Server 2005中，流程控制語句主要用來控制SQL語句、語句塊或者存儲過程的執(zhí)行流程。 Transact-SQL語言3.5.5 流程控制語句1 IFELSE語句 IFELSE語句是條件判斷語句，其中，ELSE子句是可選的，最簡單的IF語句沒有ELSE子句部分。IFELSE語句用來判斷當(dāng)某一條件成立時執(zhí)行某段程序，條件不成立時執(zhí)行另一段程序。SQL Server允許嵌套使用IFELSE語句，而且嵌套層數(shù)沒有限制。 IFELSE語句的語法形式為： IF Boolean_expression sql_sta

50、tement | statement_block ELSE sql_statement | statement_block Transact-SQL語言3.5.5 流程控制語句1 IFELSE語句例3-51顯示帶有語句塊的IF條件。如果產(chǎn)品的平均價格不低于$15，那么就顯示文本：Average price is more than $15程序清單如下。USE adventureworksIF (SELECT AVG(listprice) FROM duct WHERE color = blue) $15BEGIN PRINT The following produc

51、t are excellent: PRINT SELECT SUBSTRING(name, 1, 15) AS name FROM duct WHERE color = blue ENDELSE PRINT Average price is more than $15. Transact-SQL語言3.5.5 流程控制語句2 BEGINEND語句 BEGINEND語句能夠?qū)⒍鄠€Transact-SQL語句組合成一個語句塊，并將它們視為一個單元處理。在條件語句和循環(huán)等控制流程語句中，當(dāng)符合特定條件便要執(zhí)行兩個或者多個語句時，就需要使用BEGINEND語句。 BEGIN

52、END語句的語法形式為：BEGIN sql_statement | statement_block END Transact-SQL語言3.5.5 流程控制語句2 BEGINEND語句 例3-52利用 BEGIN 和 END 語句使得 IF 語句在取值為 FALSE 時跳過語句塊。程序清單如下：IF (ERROR 0)BEGIN SET ErrorSaveVariable = ERROR PRINT Error encountered, + CAST(ErrorSaveVariable AS VARCHAR(10)END Transact-SQL語言3.5.5 流程控制語句3GO 語句 Go

53、語句是批的結(jié)束語句。批是一起提交并作為一個組執(zhí)行的若干SQL語句。 例3-53 用Go 語句作為批的結(jié)束語句。程序清單如下：USE adventureworksGODECLARE MyMsg VARCHAR(50)SELECT MyMsg = Hello, World.GO - MyMsg 在Go語句后失效。 Transact-SQL語言3.5.5 流程控制語句4CASE語句 CASE語句可以計算多個條件式，并將其中一個符合條件的結(jié)果表達(dá)式返回。CASE語句按照使用形式的不同，可以分為簡單CASE語句和搜索CASE語句。它們的語法形式分別為：CASE input_expression WHEN

54、 when_expression THEN result_expression .n ELSE else_result_expression ENDCASE WHEN Boolean_expression THEN result_expression .n ELSE else_result_expression END Transact-SQL語言3.5.5 流程控制語句4CASE語句 例3-54使用CASE函數(shù)去更改產(chǎn)品的分類顯示，以使其更易于理解程序清單如下。USE adventureworksSELECT Category = CASE color WHEN red THEN red c

55、olor WHEN black THEN black color WHEN silver THEN silver color WHEN yellow THEN yellow color WHEN blue THEN blue color END, CAST(name AS varchar(25) AS Shortened name, listprice AS PriceFROM ductWHERE listprice IS NOT NULLORDER BY color, priceCOMPUTE AVG(listprice) BY colorTransact-SQL

56、語言3.5.5 流程控制語句4CASE語句 例3-55使用搜索CASE函數(shù)，根據(jù)產(chǎn)品的價格范圍將價格顯示為文本注釋程序清單如下。USE adventureworksSELECT Price Category= CASE WHEN listprice IS NULL THEN Not yet priced WHEN listprice = 10 and listprice 20 THEN Coffee Table Title ELSE Expensive! END, CAST(name AS varchar(20) AS Shortened nameFROM ductORDER BY listpriceGOTransact-SQL語言3.5.5 流程控制語句5WHILEC