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

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 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)分別表示教師的編號，姓名，性別，年齡，職稱，工資，系別。TC表（教師授課表）的結(jié)構(gòu)為TC(TNO,CNO)分別表示教師的編號，課程編號。*/ UPDATE T SET SAL=SAL+100 WHERE TNO IN (SELECT T.TNO FROM T,TC WHERE T.TNO=TC.TNO AND TC.CNO='C5') /*通過連接查詢找到講授C5課程的教師編號。*/ 下面是刪除一行記錄的例子。 例3-14

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

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

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

6、;|<角色> WITH ADMIN OPTION 其語義為：將指定的操作對象的對象權(quán)限授予指定的用戶或角色。其中： （1）ALL代表所有的對象權(quán)限。 （2）列名用于指定要授權(quán)的數(shù)據(jù)庫對象的一列或多列。如果不指定列名，被授權(quán)的用戶將在數(shù)據(jù)庫對象的所有列上均擁有指定的特權(quán)。實際上，只有當授予INSERT、UPDATE權(quán)限時才需指定列名。 （3）ON子句用于指定要授予對象權(quán)限的數(shù)據(jù)庫對象名，可以是基本表名、視圖名等。 （4）WITH ADMIN OPTION為可選項，指定后則允許被授權(quán)的用戶將權(quán)限再授予其他用戶或角色。 例3-19在權(quán)限層次中授予對象權(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具有此對象權(quán)限，并可使用GRANT命令給其他用戶授權(quán)，如下例，USER3將此權(quán)限授予USER4： G

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

9、REVOKE語句，其語法格式為： REVOKE <對象權(quán)限>|<角色> ,<對象權(quán)限>|<角色> 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，在收回US

10、ER3對T表的權(quán)限的同時，系統(tǒng)會自動收回USER4對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語句用于拒絕給當前數(shù)據(jù)庫內(nèi)的用戶或者角色授予權(quán)限，并防止用戶或角色通過其組或角色成員繼承權(quán)限。 否定語句權(quán)限的語法形式為： DENY ALL|<語句權(quán)限>|<角色>

11、,<語句權(quán)限>|<角色> TO <用戶名>|<角色>|PUBLIC,<用戶名>|<角色> 否定對象權(quán)限的語法形式為： DENY ALL|<對象權(quán)限>(列名,列名),<對象權(quán)限>ON <對象名> 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)存儲過程系統(tǒng)存儲過程的部分示例如下： sp_addtype：用于定義一個用戶定義數(shù)據(jù)類型； sp_configure：用于管理服務(wù)器配置選項設(shè)置； xp_sendmail：用于發(fā)送電子郵件或?qū)ず粜畔ⅲ?sp_stored_procedures：用于返回當前數(shù)據(jù)庫中的存儲過程的清單； sp_help：用于顯示參數(shù)清單和其數(shù)據(jù)類型； sp_depends：用于顯示存儲過程依據(jù)的對象或者依據(jù)存儲過程的對象； sp_helptext：用于顯示

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

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

15、的局部變量，參數(shù)expression是任何有效的SQL Server表達式。 例3-26創(chuàng)建一個myvar 變量，然后將一個字符串值放在變量中，最后輸出 myvar 變量的值。 程序清單如下： DECLARE myvar char(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) 2.

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

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

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

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

20、被稱為字符串串聯(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。 七、函數(shù) 在Transact-SQL語言中，函數(shù)被用來執(zhí)行一些特殊的運算以支持SQL S

21、erver的標準命令。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í)行一個分布式查詢，以便從服務(wù)器local中提取表department中的記錄。 程序清單如下： select * from openquery(local,select * fro

22、m 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ù)都生成一個單獨的匯總值。 程序清單如下。 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ū)中的每一行返回一個序列值。依據(jù)此函數(shù)，一些行可能取得和其他行一樣的序列值。Transact-SQL提供以下一些Ranking函數(shù)：RANK；DENSE_RANK；NTILE；ROW_NUMBER。 表

24、3-4 標量函數(shù)的分類 函數(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中的值、對象和設(shè)置的信息 系統(tǒng)統(tǒng)計函數(shù) 返回系統(tǒng)的統(tǒng)計信息 文本和圖像函數(shù) 對文本或圖像輸入值或列執(zhí)行操作，返回有關(guān)這些值的信息 （1）字符串函數(shù)l 字符串函數(shù)可以對二進制數(shù)據(jù)、字符串和表達式執(zhí)行不同的運算，大多數(shù)字

25、符串函數(shù)只能用于char和varchar數(shù)據(jù)類型以及明確轉(zhuǎn)換成char和varchar的數(shù)據(jù)類型，少數(shù)幾個字符串函數(shù)也可以用于binary和varbinary數(shù)據(jù)類型。 l 字符串函數(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。 Ø

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個字符開始查找“shoe”Ø 程序清單如下。Ø USE adventureworks Ø SELECT CHARINDEX('shoe', addressline1,2) FROM person.address Ø WHERE addressid = '5'（2）日期和時間函數(shù) 函數(shù)參數(shù)功能DATEADD（datepart,number,date） 以datepart指定的方式，返回date加上number之和 DATEDIFF（datepart,date1,date2） 以

28、datepart指定的方式，返回date2與date1之差 DATENAME（datepart,date） 返回日期date中datepart指定部分所對應的字符串 DATEPART（datepart,date） 返回日期date中datepart指定部分所對應的整數(shù)值 DAY（date） 返回指定日期的天數(shù) GETDATE（） 返回當前的日期和時間 MONTH（date） 返回指定日期的月份數(shù) YEAR（date） 返回指定日期的年份數(shù) 例3-42顯示在humanresources.employee 表中雇用日期到當前日期間的天數(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ù)學函數(shù)數(shù)學函數(shù)用于對數(shù)字表達式進行數(shù)學運算并返回運算結(jié)果。數(shù)學函數(shù)可以對SQL Server提供的數(shù)字數(shù)據(jù)（decimal、integer、float、real、money、smallmoney、smallint 和 tinyint）進行處理。例3-45在同一

30、表達式中使用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ù)庫和數(shù)據(jù)庫對象的信息。系統(tǒng)函數(shù)可以讓用戶在得到信息后，使用條件語句，根據(jù)返回的信息進行不同的操作。與其他函數(shù)一樣，可以在SELECT語句的SELECT和WHERE子句以及表達式中使用系統(tǒng)函數(shù)。 轉(zhuǎn)換函數(shù)有兩個：CONVERT和CAST。 CAST函數(shù)允許把一個數(shù)據(jù)類型強制轉(zhuǎn)換為另一種數(shù)據(jù)類型，其語法形式為： CAST( expression AS

31、 data_type ) CONVERT函數(shù)允許用戶把表達式從一種數(shù)據(jù)類型轉(zhuǎn)換成另一種數(shù)據(jù)類型，還允許把日期轉(zhuǎn)換成不同的樣式，其語法形式為： CONVERT (data_type(length),expression ,style) 例3-46 示例檢索列表價格的第一位是 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ù)將當前日期轉(zhuǎn)換為不同格式的字符串。程序清單如下：SELECT '101'=CONVERT(char, GETDATE(), 101),'1'=CONVERT(char, GETDATE(), 1),'112'=CONVERT(char, GETDATE(), 112)例3-48從adventureworks數(shù)據(jù)庫中返回person.contact表的首列名稱程序清單如下。USE adventureworks SELECT COL_NAME(OBJECT_ID('pers

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

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

35、ession為返回的整型值。存儲過程可以給調(diào)用過程或應用程序返回整型值。 例3-59顯示如果在執(zhí)行 findjobs 時沒有給出用戶名作為參數(shù)，RETURN 則將一條消息發(fā)送到用戶的屏幕上然后從過程中退出。如果給出用戶名，將從適當?shù)南到y(tǒng)表中檢索由該用戶在當前數(shù)據(jù)庫內(nèi)創(chuàng)建的所有對象名。 程序清單如下： 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ù)庫表。*/*程序清單如下：*/*下面的例子將創(chuàng)建表S。*/CREATE TABLE S( sno char(10) NOT NULL /*學號字段*/ 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表，增加一個班號列。*/*程序清單如下：*/ALTER TABLE S ADDCLASS_NO CHAR(6)/*例3-3刪除S表。*/*程序清單如下：*/ DROP table S/*再執(zhí)行例3-1*/*例3-4在S表中插入一條學生記錄（學號：S7；姓名：鄭冬；性別：女；年齡：21；系別：計算機）。*/*程序清單如下：*/INSERT INTO S VALUES ('3130050107&

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

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

40、SC表中各位學生的平均成績，把結(jié)果存放在新表AVGSCORE中。*/INSERT INTO AVGSCORESELECT SNO,AVG(SCORE) FROM SCGROUP BY SNO/*例3-8一個帶有WHERE條件的修改語句。*/*程序清單如下：*/use adventureworksgoupdate person.address set city='Boston' where addressid=1/*例3-9 將所有學生年齡增加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語句與TOP子句一起使用，更新Employee 表中10個隨機行的VacationHours 列，使之變成原來的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)分別表示教師的編號，

43、姓名，性別，年齡，職稱，工資，系別。TC表（教師授課表）的結(jié)構(gòu)為TC(TNO,CNO)分別表示教師的編號，課程編號。*/USE exampleGOCREATE TABLE t(TNO char(10) NOT NULLCONSTRAINT PK_TNO PRIMARY KEY,/*教師編號字段*/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,'動力系')GOCREATE TABLE TC(TNO char(10) NOT NULL,/*教師編號字段*/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') /*通過連接查詢找到講授C5課程的教師編號。*/*例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 刪除李明同學選課的記錄。*/DELETE FROM SC WHERE SNO= (SELECT SNO FROM S WHERE sname='李明')/*例3-17給用戶 Mary 授予多個語句權(quán)限。*/*程序清單如下：*/GRANT CREATE DATABASE, CREATE TABLETO Mary/*例3-18 為用戶ZhangYiLin授

47、予CREATE TABLE的語句權(quán)限。*/*程序清單如下：*/GRANT CREATE TABLE TO ZhangYiLin/*例3-19在權(quán)限層次中授予對象權(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具有此對象權(quán)限，并可使用GRANT命令給其他用戶授權(quán)，如下例，USER3將此權(quán)限授予USER4：*/GRANT SELECT,UPDATE(PROF)ON TTO USER4/*例3-21 收回用戶USER1對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/* 注釋語句的第一行. 注釋語句的第二行. */SELECT * FROM Production.ProductGO- 在Transact-SQL語言調(diào)試過程中使用注釋語句。SELECT title, /* birthdate, *

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

52、值。*/*程序清單如下：*/USE adventureworksGODECLARE rows intSET rows = (SELECT COUNT(*) FROM humanresources.employee)/*例3-28在SELECT語句中使用由 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顯示到當前日期和時間為止試圖登錄SQL Server的次數(shù)。*/*程序清單如下：*/SELECT GETDATE() AS '當前的時期和時間', CONNECTIONS AS '試圖登錄的次數(shù)'/*例3-31 創(chuàng)建一個具有 int 數(shù)據(jù)類型的表以顯示值，并將該表放入一行中。*/*程序清單如下：*/CREATE TABLE t1( a int NOT NULL, b int NOT NULL ) INSERT t1 VALUES (170, 75) /*下面的查詢在 a列和

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