SQL授予用戶權(quán)限的語(yǔ)句

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

2、建把講授C5課程的教師的工資增加100元。 程序清單如下： /*T表（教師基本情況表）的結(jié)構(gòu)為T(TNO,TN,SEX,AGE,PROF,SAL,DEPT)分別表示教師的編號(hào)，姓名，性別，年齡，職稱，工資，系別。TC表（教師授課表）的結(jié)構(gòu)為TC(TNO,CNO)分別表示教師的編號(hào)，課程編號(hào)。*/ UPDATE T SET SAL=SAL+100 WHERE TNO IN (SELECT T.TNO FROM T,TC WHERE T.TNO=TC.TNO AND TC.CNO='C5') /*通過(guò)連接查詢找到講授C5課程的教師編號(hào)。*/ 下面是刪除一行記錄的例子。 例3-14

3、刪除張益琳教師的記錄。 程序清單如下： DELETE FROM T WHERE TN= 張益琳 例3-16 刪除李明同學(xué)選課的記錄。 DELETE FROM SC WHERE SNO= (SELECT SNO FROM S WHERE SN= 李明)三、數(shù)據(jù)控制語(yǔ)言 數(shù)據(jù)控制語(yǔ)言（DCL）是用來(lái)設(shè)置或更改數(shù)據(jù)庫(kù)用戶或角色權(quán)限的語(yǔ)句，包括GRANT，DENY，REVOKE等語(yǔ)句。在默認(rèn)狀態(tài)下，只有sysadmin，dbcreator，db_owner或db_securityadmin等人員才有權(quán)力執(zhí)行數(shù)據(jù)控制語(yǔ)言。3.1 語(yǔ)句權(quán)限與角色的授予 SQL語(yǔ)言使用GRANT語(yǔ)句為用戶授予語(yǔ)句權(quán)限的語(yǔ)法

4、格式為： GRANT <語(yǔ)句權(quán)限>|<角色> ,<語(yǔ)句權(quán)限>|<角色> TO <用戶名>|<角色>|PUBLIC,<用戶名>|<角色> WITH ADMIN OPTION 其語(yǔ)義為：將指定的語(yǔ)句權(quán)限授予指定的用戶或角色。其中：（1）PULBIC代表數(shù)據(jù)庫(kù)中的全部用戶；（2）WITH ADMIN OPTION為可選項(xiàng)，指定后則允許被授權(quán)的用戶將指定的系統(tǒng)特權(quán)或角色再授予其他用戶或角色。 例3-17給用戶 Mary 和 John 以及 Windows NT 組 CorporateBobJ 授予多個(gè)語(yǔ)句權(quán)

5、限。 程序清單如下： GRANT CREATE DATABASE, CREATE TABLE TO Mary, John, CorporateBobJ 例3-18 為用戶ZhangYiLin授予CREATE TABLE的語(yǔ)句權(quán)限。 程序清單如下： GRANT CREATE TABLE TO ZhangYiLin 同語(yǔ)句權(quán)限的授予類似，SQL語(yǔ)言使用GRANT語(yǔ)句為用戶授予對(duì)象權(quán)限，其語(yǔ)法格式為： GRANT ALL|<對(duì)象權(quán)限>(列名,列名),<對(duì)象權(quán)限>ON <對(duì)象名> TO <用戶名>|<角色>|PUBLIC,<用戶名>

6、;|<角色> WITH ADMIN OPTION 其語(yǔ)義為：將指定的操作對(duì)象的對(duì)象權(quán)限授予指定的用戶或角色。其中： （1）ALL代表所有的對(duì)象權(quán)限。 （2）列名用于指定要授權(quán)的數(shù)據(jù)庫(kù)對(duì)象的一列或多列。如果不指定列名，被授權(quán)的用戶將在數(shù)據(jù)庫(kù)對(duì)象的所有列上均擁有指定的特權(quán)。實(shí)際上，只有當(dāng)授予INSERT、UPDATE權(quán)限時(shí)才需指定列名。 （3）ON子句用于指定要授予對(duì)象權(quán)限的數(shù)據(jù)庫(kù)對(duì)象名，可以是基本表名、視圖名等。 （4）WITH ADMIN OPTION為可選項(xiàng)，指定后則允許被授權(quán)的用戶將權(quán)限再授予其他用戶或角色。 例3-19在權(quán)限層次中授予對(duì)象權(quán)限。首先，給所有用戶授予 SELEC

7、T 權(quán)限，然后，將特定的權(quán)限授予用戶 Mary，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具有此對(duì)象權(quán)限，并可使用GRANT命令給其他用戶授權(quán)，如下例，USER3將此權(quán)限授予USER4： G

8、RANT SELECT,UPDATE(PROF) ON T TO USER4 數(shù)據(jù)庫(kù)管理員可以使用REVOKE語(yǔ)句收回語(yǔ)句權(quán)限，其語(yǔ)法格式為： REVOKE <語(yǔ)句權(quán)限>|<角色> ,<語(yǔ)句權(quán)限>|<角色> FROM <用戶名>|<角色>|PUBLIC,<用戶名>|<角色> 例：收回用戶ZHANGYILIN所擁有的CREATE TABLE的語(yǔ)句權(quán)限。 REVOKE CREATE TABLE FROM ZHANGYILIN所有授予出去的權(quán)力在必要時(shí)都可以由數(shù)據(jù)庫(kù)管理員和授權(quán)者收回，收回對(duì)象權(quán)限仍然使用

9、REVOKE語(yǔ)句，其語(yǔ)法格式為： REVOKE <對(duì)象權(quán)限>|<角色> ,<對(duì)象權(quán)限>|<角色> FROM <用戶名>|<角色>|PUBLIC,<用戶名>|<角色> 例3-21 收回用戶USER1對(duì)C表的查詢權(quán)限。程序清單如下：REVOKE SELECT ON CFROM USER1 例3-22 收回用戶USER3查詢T表和修改教師職稱的權(quán)限。程序清單如下：REVOKE SELECT,UPDATE(PROF)ON TFROM USER3 在上例中，USER3將對(duì)T表的權(quán)限授予了USER4，在收回US

10、ER3對(duì)T表的權(quán)限的同時(shí)，系統(tǒng)會(huì)自動(dòng)收回USER4對(duì)T表的權(quán)限。 例3-23首先從 public 角色中收回 SELECT 權(quán)限，然后，收回用戶 Mary，John 和 Tom 的特定權(quán)限。程序清單如下：USE pubs GOREVOKE SELECT ON s FROM publicGOREVOKE INSERT, UPDATE, DELETE ON s FROM Mary, John, Tom DENY語(yǔ)句用于拒絕給當(dāng)前數(shù)據(jù)庫(kù)內(nèi)的用戶或者角色授予權(quán)限，并防止用戶或角色通過(guò)其組或角色成員繼承權(quán)限。 否定語(yǔ)句權(quán)限的語(yǔ)法形式為： DENY ALL|<語(yǔ)句權(quán)限>|<角色>

11、,<語(yǔ)句權(quán)限>|<角色> TO <用戶名>|<角色>|PUBLIC,<用戶名>|<角色> 否定對(duì)象權(quán)限的語(yǔ)法形式為： DENY ALL|<對(duì)象權(quán)限>(列名,列名),<對(duì)象權(quán)限>ON <對(duì)象名> TO <用戶名>|<角色>|PUBLIC,<用戶名>|<角色> 例3-24首先給 public 角色授予 SELECT 權(quán)限，然后，拒絕用戶 Mary，John 和 Tom 的特定權(quán)限。 程序清單如下： USE pubs GO GRANT SELEC

12、T ON s TO public GO DENY SELECT, INSERT, UPDATE, DELETE ON s TO Mary,John,Tom四、系統(tǒng)存儲(chǔ)過(guò)程系統(tǒng)存儲(chǔ)過(guò)程的部分示例如下： sp_addtype：用于定義一個(gè)用戶定義數(shù)據(jù)類型； sp_configure：用于管理服務(wù)器配置選項(xiàng)設(shè)置； xp_sendmail：用于發(fā)送電子郵件或?qū)ず粜畔ⅲ?sp_stored_procedures：用于返回當(dāng)前數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程的清單； sp_help：用于顯示參數(shù)清單和其數(shù)據(jù)類型； sp_depends：用于顯示存儲(chǔ)過(guò)程依據(jù)的對(duì)象或者依據(jù)存儲(chǔ)過(guò)程的對(duì)象； sp_helptext：用于顯示

13、存儲(chǔ)過(guò)程的定義文本； sp_rename：用于修改當(dāng)前數(shù)據(jù)庫(kù)中用戶對(duì)象的名稱。五、變量 變量是一種語(yǔ)言中必不可少的組成部分。Transact-SQL語(yǔ)言中有兩種形式的變量，一種是用戶自己定義的局部變量，另外一種是系統(tǒng)提供的全局變量。 1. 局部變量 局部變量是一個(gè)能夠擁有特定數(shù)據(jù)類型的對(duì)象，它的作用范圍僅限制在程序內(nèi)部。局部變量被引用時(shí)要在其名稱前加上標(biāo)志“”，而且必須先用DECLARE命令定義后才可以使用。 定義局部變量的語(yǔ)法形式如下： DECLAER local_variable data_type n 其中，參數(shù)local_variable用于指定局部變量的名稱，變量名必須以符號(hào)開(kāi)頭，并

14、且局部變量名必須符合SQL Server的命名規(guī)則。參數(shù)data_type用于設(shè)置局部變量的數(shù)據(jù)類型及其大小。data_type可以是任何由系統(tǒng)提供的或用戶定義的數(shù)據(jù)類型。但是，局部變量不能是 text，ntext 或 image 數(shù)據(jù)類型。 使用DECLARE命令聲明并創(chuàng)建局部變量之后，會(huì)將其初始值設(shè)為NULL，如果想要設(shè)定局部變量的值，必須使用SELECT命令或者SET命令。其語(yǔ)法形式為： SET local_variable = expression 或者 SELECT local_variable = expression ,.n 其中，參數(shù)local_variable是給其賦值并聲明

15、的局部變量，參數(shù)expression是任何有效的SQL Server表達(dá)式。 例3-26創(chuàng)建一個(gè)myvar 變量，然后將一個(gè)字符串值放在變量中，最后輸出 myvar 變量的值。 程序清單如下： DECLARE myvar char(20) select myvar = 'This is a test' SELECT myvar GO 例3-27通過(guò)查詢給變量賦值。 程序清單如下： USE adventureworks GO DECLARE rows int SET rows = (SELECT COUNT(*) FROM humanresources.employee) 2.

16、全局變量 除了局部變量之外，SQL Server系統(tǒng)本身還提供了一些全局變量。全局變量是SQL Server系統(tǒng)內(nèi)部使用的變量，其作用范圍并不僅僅局限于某一程序，而是任何程序均可以隨時(shí)調(diào)用。全局變量通常存儲(chǔ)一些SQL Server的配置設(shè)定值和統(tǒng)計(jì)數(shù)據(jù)。用戶可以在程序中用全局變量來(lái)測(cè)試系統(tǒng)的設(shè)定值或者是Transact-SQL命令執(zhí)行后的狀態(tài)值。在使用全局變量時(shí)應(yīng)該注意以下幾點(diǎn)： （1）全局變量不是由用戶的程序定義的，它們是在服務(wù)器級(jí)定義的。 （2）用戶只能使用預(yù)先定義的全局變量。 （3）引用全局變量時(shí)，必須以標(biāo)記符“”開(kāi)頭。 （4）局部變量的名稱不能與全局變量的名稱相同，否則會(huì)在應(yīng)用程序中出

17、現(xiàn)不可預(yù)測(cè)的結(jié)果。 例3-29顯示到當(dāng)前日期和時(shí)間為止試圖登錄SQL Server的次數(shù)。 程序清單如下： SELECT GETDATE( ) AS '當(dāng)前的時(shí)期和時(shí)間', CONNECTIONS AS '試圖登錄的次數(shù)'六、運(yùn)算符 運(yùn)算符是一些符號(hào)，它們能夠用來(lái)執(zhí)行算術(shù)運(yùn)算、字符串連接、賦值以及在字段、常量和變量之間進(jìn)行比較。在SQL Server 2005中，運(yùn)算符主要有以下六大類：算術(shù)運(yùn)算符、賦值運(yùn)算符、位運(yùn)算符、比較運(yùn)算符、邏輯運(yùn)算符和字符串串聯(lián)運(yùn)算符。 1算術(shù)運(yùn)算符 算術(shù)運(yùn)算符可以在兩個(gè)表達(dá)式上執(zhí)行數(shù)學(xué)運(yùn)算，這兩個(gè)表達(dá)式可以是數(shù)字?jǐn)?shù)據(jù)類型分類的任何數(shù)據(jù)

18、類型。算術(shù)運(yùn)算符包括加（+）、減（）、乘（*）、除（/）和取模（%）。 2賦值運(yùn)算符 Transact-SQL 中只有一個(gè)賦值運(yùn)算符，即（=）。賦值運(yùn)算符使我們能夠?qū)?shù)據(jù)值指派給特定的對(duì)象。另外，還可以使用賦值運(yùn)算符在列標(biāo)題和為列定義值的表達(dá)式之間建立關(guān)系。3. 位運(yùn)算符 使我們能夠在整型數(shù)據(jù)或者二進(jìn)制數(shù)據(jù)（image 數(shù)據(jù)類型除外）之間執(zhí)行位操作。此外，在位運(yùn)算符左右兩側(cè)的操作數(shù)不能同時(shí)是二進(jìn)制數(shù)據(jù)。表3-1列出了所有的位運(yùn)算符及其含義。 運(yùn)算符含義&（按位 AND） 按位 AND（兩個(gè)操作數(shù)） |（按位 OR） 按位 OR（兩個(gè)操作數(shù)） （按位互斥 OR） 按位互斥 OR（兩個(gè)操作

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

20、被稱為字符串串聯(lián)運(yùn)算符。例如對(duì)于語(yǔ)句SELECT abc+def，其結(jié)果為abcdef。 在SQL Server 2005中，運(yùn)算符的優(yōu)先等級(jí)從高到低如下所示，如果優(yōu)先等級(jí)相同，則按照從左到右的順序進(jìn)行運(yùn)算。（1）括號(hào)：（）；（2）乘、除、求模運(yùn)算符：*，/，%；（3）加減運(yùn)算符：，；（4）比較運(yùn)算符：=，>，<，>=，<=，<>，!=，!>，!<；（5）位運(yùn)算符：，&，|；（6）邏輯運(yùn)算符：NOT；（7）邏輯運(yùn)算符：AND；（8）邏輯運(yùn)算符：OR。 七、函數(shù) 在Transact-SQL語(yǔ)言中，函數(shù)被用來(lái)執(zhí)行一些特殊的運(yùn)算以支持SQL S

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

22、m department) 2. 聚合函數(shù) 聚合函數(shù)用于對(duì)一組值進(jìn)行計(jì)算并返回一個(gè)單一的值。除COUNT 函數(shù)之外，聚合函數(shù)忽略空值。聚合函數(shù)經(jīng)常與 SELECT 語(yǔ)句的 GROUP BY 子句一同使用。僅在下列項(xiàng)中聚合函數(shù)允許作為表達(dá)式使用：SELECT 語(yǔ)句的選擇列表（子查詢或外部查詢）；COMPUTE 或 COMPUTE BY 子句；HAVING 子句。 計(jì)算 Adventure Works Cycles 的副總所用的平均休假小時(shí)數(shù)以及總的病假小時(shí)數(shù)。對(duì)檢索到的所有行，每個(gè)聚合函數(shù)都生成一個(gè)單獨(dú)的匯總值。 程序清單如下。 USE AdventureWorks; GO SELECT AVG

23、(VacationHours)as 'Average vacation hours', SUM (SickLeaveHours) as 'Total sick leave hours' FROM HumanResources.Employee WHERE Title LIKE 'Vice President%' Ranking函數(shù)為查詢結(jié)果數(shù)據(jù)集分區(qū)中的每一行返回一個(gè)序列值。依據(jù)此函數(shù)，一些行可能取得和其他行一樣的序列值。Transact-SQL提供以下一些Ranking函數(shù)：RANK；DENSE_RANK；NTILE；ROW_NUMBER。 表

24、3-4 標(biāo)量函數(shù)的分類 函數(shù)分類解釋配置函數(shù) 返回當(dāng)前的配置信息 游標(biāo)函數(shù) 返回有關(guān)游標(biāo)的信息 日期和時(shí)間函數(shù) 對(duì)日期和時(shí)間輸入值進(jìn)行處理 數(shù)學(xué)函數(shù) 對(duì)作為函數(shù)參數(shù)提供的輸入值執(zhí)行計(jì)算 元數(shù)據(jù)函數(shù) 返回有關(guān)數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)對(duì)象的信息 安全函數(shù) 返回有關(guān)用戶和角色的信息 字符串函數(shù) 對(duì)字符串（char 或 varchar）輸入值執(zhí)行操作 系統(tǒng)函數(shù) 執(zhí)行操作并返回有關(guān)SQL Server中的值、對(duì)象和設(shè)置的信息 系統(tǒng)統(tǒng)計(jì)函數(shù) 返回系統(tǒng)的統(tǒng)計(jì)信息 文本和圖像函數(shù) 對(duì)文本或圖像輸入值或列執(zhí)行操作，返回有關(guān)這些值的信息 （1）字符串函數(shù)l 字符串函數(shù)可以對(duì)二進(jìn)制數(shù)據(jù)、字符串和表達(dá)式執(zhí)行不同的運(yùn)算，大多數(shù)字

25、符串函數(shù)只能用于char和varchar數(shù)據(jù)類型以及明確轉(zhuǎn)換成char和varchar的數(shù)據(jù)類型，少數(shù)幾個(gè)字符串函數(shù)也可以用于binary和varbinary數(shù)據(jù)類型。 l 字符串函數(shù)可以分為以下幾大類：Ø 基本字符串函數(shù)：UPPER，LOWER，SPACE，REPLICATE，STUFF，REVERSE，LTRIM，RTRIM。Ø 字符串查找函數(shù)：CHARINDEX，PATINDEX。Ø 長(zhǎng)度和分析函數(shù)：DATALENGTH，SUBSTRING，RIGHT。Ø 轉(zhuǎn)換函數(shù)：ASCH，CHAR，STR，SOUNDEX，DIFFERENCE。 Ø

26、例3-38使用 LTRIM 函數(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_loc

27、ation參數(shù)從addressline1列的第2個(gè)字符開(kāi)始查找“shoe”Ø 程序清單如下。Ø USE adventureworks Ø SELECT CHARINDEX('shoe', addressline1,2) FROM person.address Ø WHERE addressid = '5'（2）日期和時(shí)間函數(shù) 函數(shù)參數(shù)功能DATEADD（datepart,number,date） 以datepart指定的方式，返回date加上number之和 DATEDIFF（datepart,date1,date2） 以

28、datepart指定的方式，返回date2與date1之差 DATENAME（datepart,date） 返回日期date中datepart指定部分所對(duì)應(yīng)的字符串 DATEPART（datepart,date） 返回日期date中datepart指定部分所對(duì)應(yīng)的整數(shù)值 DAY（date） 返回指定日期的天數(shù) GETDATE（） 返回當(dāng)前的日期和時(shí)間 MONTH（date） 返回指定日期的月份數(shù) YEAR（date） 返回指定日期的年份數(shù) 例3-42顯示在humanresources.employee 表中雇用日期到當(dāng)前日期間的天數(shù)。程序清單如下。USE adventureworks SELE

29、CT DATEDIFF(day, hiredate, getdate() AS diffdays FROM humanresources.employee 例3-43從GETDATE函數(shù)返回的日期中提取月份名。程序清單如下：SELECT DATENAME(month, getdate() AS 'Month Name' 數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)用于對(duì)數(shù)字表達(dá)式進(jìn)行數(shù)學(xué)運(yùn)算并返回運(yùn)算結(jié)果。數(shù)學(xué)函數(shù)可以對(duì)SQL Server提供的數(shù)字?jǐn)?shù)據(jù)（decimal、integer、float、real、money、smallmoney、smallint 和 tinyint）進(jìn)行處理。例3-45在同一

30、表達(dá)式中使用CEILING()，F(xiàn)LOOR()，ROUND()函數(shù)。程序清單如下：select ceiling(13.4), floor(13.4), round(13.4567,3) 系統(tǒng)函數(shù) 系統(tǒng)函數(shù)用于返回有關(guān)SQL Server系統(tǒng)、用戶、數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)對(duì)象的信息。系統(tǒng)函數(shù)可以讓用戶在得到信息后，使用條件語(yǔ)句，根據(jù)返回的信息進(jìn)行不同的操作。與其他函數(shù)一樣，可以在SELECT語(yǔ)句的SELECT和WHERE子句以及表達(dá)式中使用系統(tǒng)函數(shù)。 轉(zhuǎn)換函數(shù)有兩個(gè)：CONVERT和CAST。 CAST函數(shù)允許把一個(gè)數(shù)據(jù)類型強(qiáng)制轉(zhuǎn)換為另一種數(shù)據(jù)類型，其語(yǔ)法形式為： CAST( expression AS

31、 data_type ) CONVERT函數(shù)允許用戶把表達(dá)式從一種數(shù)據(jù)類型轉(zhuǎn)換成另一種數(shù)據(jù)類型，還允許把日期轉(zhuǎn)換成不同的樣式，其語(yǔ)法形式為： CONVERT (data_type(length),expression ,style) 例3-46 示例檢索列表價(jià)格的第一位是 3 的產(chǎn)品的名稱，并將ListPrice轉(zhuǎn)換為 int。程序清單如下。USE AdventureWorks GOSELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice FROM Production.Product WHERE CAST(ListPrice AS int

32、) LIKE '3%'GO例3-47用style 參數(shù)將當(dāng)前日期轉(zhuǎn)換為不同格式的字符串。程序清單如下：SELECT '101'=CONVERT(char, GETDATE(), 101),'1'=CONVERT(char, GETDATE(), 1),'112'=CONVERT(char, GETDATE(), 112)例3-48從adventureworks數(shù)據(jù)庫(kù)中返回person.contact表的首列名稱程序清單如下。USE adventureworks SELECT COL_NAME(OBJECT_ID('pers

33、on.contact'), 1) 例3-49檢查 sysdatabases 中的每一個(gè)數(shù)據(jù)庫(kù)，使用數(shù)據(jù)庫(kù)標(biāo)識(shí)號(hào)來(lái)確定數(shù)據(jù)庫(kù)名稱。程序清單如下：USE masterSELECT dbid, DB_NAME(dbid) AS DB_NAMEFROM sysdatabases ORDER BY dbid7WAITFOR語(yǔ)句 WAITFOR語(yǔ)句用于暫時(shí)停止執(zhí)行SQL語(yǔ)句、語(yǔ)句塊或者存儲(chǔ)過(guò)程等，直到所設(shè)定的時(shí)間已過(guò)或者所設(shè)定的時(shí)間已到才繼續(xù)執(zhí)行。 WAITFOR語(yǔ)句的語(yǔ)法形式為：WAITFOR DELAY 'time' | TIME 'time' 其中，DELAY

34、用于指定時(shí)間間隔，TIME用于指定某一時(shí)刻，其數(shù)據(jù)類型為datetime，格式為hh:mm:ss。 例3-58使用WAITFOR TIME語(yǔ)句，以便在晚上10:20執(zhí)行存儲(chǔ)過(guò)程 update_all_stats。程序清單如下：BEGIN WAITFOR TIME '22:20' EXECUTE update_all_stats END8RETURN語(yǔ)句 RETURN語(yǔ)句用于無(wú)條件地終止一個(gè)查詢、存儲(chǔ)過(guò)程或者批處理，此時(shí)位于RETURN語(yǔ)句之后的程序?qū)⒉粫?huì)被執(zhí)行。 RETURN語(yǔ)句的語(yǔ)法形式為：RETURN integer_expression 其中，參數(shù)integer_expr

35、ession為返回的整型值。存儲(chǔ)過(guò)程可以給調(diào)用過(guò)程或應(yīng)用程序返回整型值。 例3-59顯示如果在執(zhí)行 findjobs 時(shí)沒(méi)有給出用戶名作為參數(shù)，RETURN 則將一條消息發(fā)送到用戶的屏幕上然后從過(guò)程中退出。如果給出用戶名，將從適當(dāng)?shù)南到y(tǒng)表中檢索由該用戶在當(dāng)前數(shù)據(jù)庫(kù)內(nèi)創(chuàng)建的所有對(duì)象名。 程序清單如下： CREATE PROCEDURE findjobs nm sysname = NULL AS IF nm IS NULL BEGIN PRINT 'You must give a username' RETURN END ELSE BEGIN SELECT , o.id

36、, o.uid FROM sysobjects o INNER JOIN master.syslogins l ON o.uid = l.sid WHERE = nm END /*例3-1創(chuàng)建數(shù)據(jù)庫(kù)表。*/*程序清單如下：*/*下面的例子將創(chuàng)建表S。*/CREATE TABLE S( sno char(10) NOT NULL /*學(xué)號(hào)字段*/ CONSTRAINT PK_sno PRIMARY KEY CLUSTERED/*主鍵約束*/ CHECK (sno like '313005010-90-9')/*檢查約束*/, sname char(8) NULL,

37、/*姓名字段*/ sex char(2) NULL, /*性別字段*/ age int NULL, /*年齡字段*/ dept varchar(20) NULL/*系別字段*/ )/*例3-2修改S表，增加一個(gè)班號(hào)列。*/*程序清單如下：*/ALTER TABLE S ADDCLASS_NO CHAR(6)/*例3-3刪除S表。*/*程序清單如下：*/ DROP table S/*再執(zhí)行例3-1*/*例3-4在S表中插入一條學(xué)生記錄（學(xué)號(hào)：S7；姓名：鄭冬；性別：女；年齡：21；系別：計(jì)算機(jī)）。*/*程序清單如下：*/INSERT INTO S VALUES ('3130050107&

38、#39;,'鄭冬','女',21,'計(jì)算機(jī)')/*例3-5 創(chuàng)建SC表（學(xué)生選課表），并向SC表中插入一條選課記錄（S7,C1）。*/*程序清單如下：*/CREATE TABLE SC( sno char(10) NOT NULL, cno char(2) NULL, /*課程編號(hào)字段*/ score numeric(4,1) NULL /*成績(jī)字段*/)GoINSERT INTO SC (sno,cno) VALUES ('3130050101', 'c1')Go/*例3-6 使用 column_list 及 V

39、ALUES 列表顯式地指定將被插入每個(gè)列的值。*/*程序清單如下：*/CREATE TABLE T1 ( column_1 int, column_2 varchar(30) GoINSERT T1 (column_2, column_1) VALUES ('This is a test',1)/*例3-7 求出各位學(xué)生的平均成績(jī)，把結(jié)果存放在新表AVGSCORE中。程序清單如下：*/*首先建立新表AVGSCORE，用來(lái)存放學(xué)號(hào)和學(xué)生的平均成績(jī)。*/CREATE TABLE AVGSCORE(SNO CHAR(10),AVGSCORE SMALLINT) Go/*利用子查詢求出

40、SC表中各位學(xué)生的平均成績(jī)，把結(jié)果存放在新表AVGSCORE中。*/INSERT INTO AVGSCORESELECT SNO,AVG(SCORE) FROM SCGROUP BY SNO/*例3-8一個(gè)帶有WHERE條件的修改語(yǔ)句。*/*程序清單如下：*/use adventureworksgoupdate person.address set city='Boston' where addressid=1/*例3-9 將所有學(xué)生年齡增加1歲*/*程序清單如下：*/UPDATE S SET AGE=AGE+1/*例3-10下面的示例修改 SalesPerson 表中的 Sa

41、lesYTD 列，以反映 SalesOrderHeader 表中記錄的最近銷售情況。*/*程序清單如下：*/USE AdventureWorks;GOUPDATE Sales.SalesPerson SET SalesYTD = SalesYTD + SubTotalFROM Sales.SalesPerson AS sp JOIN Sales.SalesOrderHeader AS so ON sp.SalesPersonID = so.SalesPersonID AND so.OrderDate = (SELECT MAX(OrderDate) FROM Sales.SalesOrderH

42、eader WHERE SalesPersonID = sp.SalesPersonID)/*例3-11 將UPDATE語(yǔ)句與TOP子句一起使用，更新Employee 表中10個(gè)隨機(jī)行的VacationHours 列，使之變成原來(lái)的1.25倍。*/*程序清單如下：*/USE AdventureWorks;GOUPDATE TOP (10) HumanResources.EmployeeSET VacationHours = VacationHours * 1.25/*例3-12 創(chuàng)建T表（教師基本情況表）的結(jié)構(gòu)為T(TNO,TN,SEX,AGE,PROF,SAL,DEPT)分別表示教師的編號(hào)，

43、姓名，性別，年齡，職稱，工資，系別。TC表（教師授課表）的結(jié)構(gòu)為TC(TNO,CNO)分別表示教師的編號(hào)，課程編號(hào)。*/USE exampleGOCREATE TABLE t(TNO char(10) NOT NULLCONSTRAINT PK_TNO PRIMARY KEY,/*教師編號(hào)字段*/TN char(10) NULL,/*教師姓名字段*/SEX char(2) NULL,/*教師性別字段*/AGE int NULL,/*教師年齡字段*/PROF char(20) NULL,/*教師職稱字段*/SAL int NULL,/*教師工資字段*/DEPT char(10) NULL,/*教

44、師所在系別字段*/ )GOINSERT INTO t VALUES('0001','張老師','男',41,'副教授',2200,'電力系')GOINSERT INTO t VALUES('0002','張益琳','女',32,'講師',1500,'動(dòng)力系')GOCREATE TABLE TC(TNO char(10) NOT NULL,/*教師編號(hào)字段*/CNO char(10) NULL,/*教師執(zhí)教課程字段*/ )GOINSERT

45、 INTO tc VALUES('0001','C5')GO/*把講授C5課程的教師的工資增加100元。*/*程序清單如下：*/UPDATE T SET SAL=SAL+100WHERE TNO IN (SELECT T.TNO FROM T,TCWHERE T.TNO=TC.TNO AND TC.CNO='C5') /*通過(guò)連接查詢找到講授C5課程的教師編號(hào)。*/*例3-13 把所有教師的工資提高到平均工資的1.2倍*/*程序清單如下：*/UPDATE T SET SAL =(SELECT 1.2*AVG(SAL) FROM T)/*例3-14

46、 刪除張益琳教師的記錄。*/*程序清單如下：*/DELETE FROM T WHERE TN='張益琳'/*例 3-15 刪除所有教師的授課記錄。*/*程序清單如下：*/DELETE FROM TC/*例3-16 刪除李明同學(xué)選課的記錄。*/DELETE FROM SC WHERE SNO= (SELECT SNO FROM S WHERE sname='李明')/*例3-17給用戶 Mary 授予多個(gè)語(yǔ)句權(quán)限。*/*程序清單如下：*/GRANT CREATE DATABASE, CREATE TABLETO Mary/*例3-18 為用戶ZhangYiLin授

47、予CREATE TABLE的語(yǔ)句權(quán)限。*/*程序清單如下：*/GRANT CREATE TABLE TO ZhangYiLin/*例3-19在權(quán)限層次中授予對(duì)象權(quán)限。首先，給所有用戶授予SELECT權(quán)限，然后，將特定的權(quán)限授予用戶Mary、John和Tom*/*程序清單如下：*/GRANT SELECTON s /*需先創(chuàng)建s表*/TO publicGO GRANT INSERT, UPDATE, DELETEON sTO Mary, John, Tom /*需首先創(chuàng)建用戶Mary, John, Tom */GO/*例3-20 將查詢T表和修改教師職稱的權(quán)限授予USER3，并允許將此權(quán)限授予其

48、他用戶。*/*程序清單如下：*/GRANT SELECT,UPDATEON T TO user3WITH grant OPTION/*上例中，USER3具有此對(duì)象權(quán)限，并可使用GRANT命令給其他用戶授權(quán)，如下例，USER3將此權(quán)限授予USER4：*/GRANT SELECT,UPDATE(PROF)ON TTO USER4/*例3-21 收回用戶USER1對(duì)jobs表的查詢權(quán)限。*/*程序清單如下：*/REVOKE SELECT ON jobsFROM mary/*例3-22 收回用戶mary查詢T表和修改教師職稱的權(quán)限。*/*程序清單如下：*/REVOKE SELECT,UPDATE(PR

49、OF)ON TFROM mary/*例3-23首先從 public 角色中收回 SELECT 權(quán)限，然后，收回用戶 Mary,Jhon和Tom的特定權(quán)限。*/*程序清單如下：*/REVOKE SELECTON s FROM publicGOREVOKE INSERT, UPDATE, DELETEON s FROM Mary, John, Tom/*例3-24首先給public角色授予SELECT權(quán)限，然后拒絕用戶Mary、John和Tom的特定權(quán)限*/*程序清單如下：*/GRANT SELECTON sTO publicGODENY SELECT, INSERT, UPDATE, DELET

50、EON sTO Mary,John,Tom/*例3-25使用兩種注釋類型的例子。*/*程序清單如下：*/USE AdventureWorksGO- First line of a multiple-line comment.- Second line of a multiple-line comment.SELECT * FROM person.addressGO/* 注釋語(yǔ)句的第一行. 注釋語(yǔ)句的第二行. */SELECT * FROM Production.ProductGO- 在Transact-SQL語(yǔ)言調(diào)試過(guò)程中使用注釋語(yǔ)句。SELECT title, /* birthdate, *

51、/ hiredateFROM humanresources.employee- 在代碼后面加上注釋。USE AdventureWorks GOUPDATE sales.salesorderdetailSET unitprice = unitprice * .9 - 降低價(jià)格以提高市場(chǎng)份額。GO/*例3-26創(chuàng)建一個(gè)myvar 變量，然后將一個(gè)字符串值放在變量中，最后輸出 myvar 變量的值。*/*程序清單如下：*/DECLARE myvar char(20)select myvar = 'This is a test'SELECT myvarGO/*例3-27通過(guò)查詢給變量賦

52、值。*/*程序清單如下：*/USE adventureworksGODECLARE rows intSET rows = (SELECT COUNT(*) FROM humanresources.employee)/*例3-28在SELECT語(yǔ)句中使用由 SET 賦值的局部變量。*/*程序清單如下：*/USE adventureworksGODECLARE phone char(12)SET phone = '398-555-0132'SELECT RTRIM(firstname) + ' ' + RTRIM(lastname) AS Name, phoneFR

53、OM person.contactWHERE phone = phoneGO/*例3-29顯示到當(dāng)前日期和時(shí)間為止試圖登錄SQL Server的次數(shù)。*/*程序清單如下：*/SELECT GETDATE() AS '當(dāng)前的時(shí)期和時(shí)間', CONNECTIONS AS '試圖登錄的次數(shù)'/*例3-31 創(chuàng)建一個(gè)具有 int 數(shù)據(jù)類型的表以顯示值，并將該表放入一行中。*/*程序清單如下：*/CREATE TABLE t1( a int NOT NULL, b int NOT NULL ) INSERT t1 VALUES (170, 75) /*下面的查詢?cè)?a列和

54、 b列上執(zhí)行按位 AND 運(yùn)算。*/SELECT a& b FROM t1/*例3-32在SET語(yǔ)句使用的表達(dá)式中，括號(hào)使其首先執(zhí)行加法，表達(dá)式的結(jié)果為18。*/*程序清單如下：*/DECLARE MyNumber intSET MyNumber = 2 * (4 + 5)SELECT MyNumber/*例3-33通過(guò)行集函數(shù)OPENQUERY()執(zhí)行一個(gè)分布式查詢，以便從服務(wù)器local中提取表department中的記錄。*/*程序清單如下：*/select * from openquery( SERVER , 'select * from Northwind.customers')/*例3-34 計(jì)算 Adventure Works Cycles 的副總所用的平均休假小時(shí)數(shù)以及總的病假小時(shí)數(shù)。對(duì)檢索到的所有行，每個(gè)聚合函數(shù)都生成一個(gè)單獨(dú)的匯總值。*/*程序清單如下：*/USE AdventureWorks;GOSELECT AVG(VacationHours)as 'Average vacation hours', SUM (SickLeaveHours) as 'Tota