《SQL Server 2008從入門到精通》--20180710

標籤：均值   引擎   enter   過程   十進位   按位元運算   方法   螢幕   類型轉換   

目錄

• 1.使用Transact-SQL語言編程
  • 1.1.資料定義語言 (Data Definition Language)DDL
  • 1.2.資料操縱語言DML
  • 1.3.資料控制語言DCL
  • 1.4.Transact-SQL語言基礎
• 2.運算子
  • 2.1.算數運算子
  • 2.2.賦值運算子
  • 2.3.位元運算符
  • 2.4.比較子
  • 2.5.邏輯運算子
  • 2.6.串連運算子
  • 2.7.一元運算子
  • 2.8.運算子的優先順序
• 3.控制語句
  • 3.1.BEGIN END語句塊
  • 3.2.IF ELSE語句塊
  • 3.3.CASE分支語句
  • 3.4.WHILE語句
  • 3.5.WAITFOR延遲語句
  • 3.6.RETURN無條件退出語句
  • 3.7.GOTO跳躍陳述式
  • 3.8.TRY CATCH錯誤處理語句
• 4.常用函數
  • 4.1.資料類型轉換函式

1.使用Transact-SQL語言編程

儘管SQL Server 2008提供了圖形化介面，但只有一種Transact-SQL語言能夠直接與資料庫引擎進行互動。根據執行功能特點可以將Transact-SQL語言分成3大類：資料定義語言 (Data Definition Language)DDL，資料操縱語言DML，資料控制語言DCL。

1.1.資料定義語言 (Data Definition Language)DDL

是最基礎的Transact-SQL語言類型，用來建立資料庫和建立，修改，刪除資料庫中的各種對象，為其他語言的操作提供對象。例如資料庫，表，觸發器，預存程序，視圖，函數，索引，類型及使用者等都是資料庫中的對象。常見的DDL語句包括

CREATE TABLE--建立表DROP TABLE--刪除表ALTER TABLE--修改表

1.2.資料操縱語言DML

是用於操縱表和視圖中的資料的語句，例如查詢資料（SELECT），插入資料（INSERT），更新資料（UPDATE）和刪除資料（DELETE）等。

1.3.資料控制語言DCL

涉及到許可權管理的語言稱為資料控制語言，主要用於執行有關安全管理的操作。如授予許可權（GRANT），收回許可權（REVOKE），拒絕授予主體許可權，並防止主體通過組或角色成員繼承許可權（DENY）

1.4.Transact-SQL語言基礎1.4.1.常量與變數

常量不多說。在SQL Server 2008中，存在兩種變數。一種是系統定義和維護的全域變數，一種是使用者定義用來儲存中間結果的局部變數。

1.4.1.1.系統全域變數

系統全域變數分為兩大類，一類是與當然SQL Server串連或與當前處理有關的全域變數，如@@Rowcount表示最近一個語句影響的行數。@@error表示儲存最近執行操作的錯誤狀態。一類是與整個SQL Server系統有關的全域變數，如@@Version表示當前SQL Server的版本資訊。

SELECT @@VERSION AS 目前的版本;--查看當前SQL Server的版本資訊

結果

1.4.1.2.局部變數

局部變數能夠擁有特定資料類型，有一定的範圍，一般用於充當計數器計算或控制迴圈執行次數，或者用於儲存資料值。局部變數前只有1個@符，用DECLARE語句聲明局部變數。

USE testDECLARE @StudentId varchar(20)SET @StudentId=(SELECT Student.stu_noFROM StudentWHERE stu_enter_score=‘603‘)SELECT @StudentId AS 入學分數為603的學生學號GO

結果

2.運算子2.1.算數運算子

在SQL Server 2008中，算數運算包括加（+）減（-）乘（*）除（/）模數（%）。舉一個簡單的例子。
樣本1：在Student表中添加一列，列名為stu_age，根據Student表的stu_birthday列計算stu_age列並插入資料。（示範插入整列資料的方法）
Student表資料

執行下面的語句

ALTER TABLE StudentADD stu_age int;--在Student表中添加stu_age列CREATE TABLE #agetemp(stu_no varchar(8),age int);--建立一個暫存資料表INSERT INTO #agetemp(stu_no,age)--在暫存資料表中插入學號和計算出來的年齡SELECT Student.stu_no,YEAR(GETDATE())-YEAR(stu_birthday)--利用函數和運算子計算年齡FROM Student;UPDATE StudentSET Student.stu_age=#agetemp.age--將暫存資料表中的age列資料整個複製到Student表的stu_age列FROM #agetempWHERE Student.stu_no=#agetemp.stu_no--條件是兩個表的stu_no列值相等GOSELECT * FROM Student

結果

2.2.賦值運算子

即等號（=），將運算式的值賦予另一個變數。舉一個簡單的例子。
樣本2：計算Student表中學生的平均入學成績並列印。
Student表的資料，stu_enter_score列存放了學生的入學成績

執行下面的語句

DECLARE @average int--聲明@average變數SET @average=(--將計算出的平均值賦值給@averageSELECT AVG(stu_enter_score)FROM Student)PRINT @average--列印@average的值

結果

2.3.位元運算符

位元運算符包括與運算（&），或運算（|）和異或運算（^），可以對兩個運算式進行位操作，這兩個運算式可以是整型資料或位元據。Transact-SQL首先把整型資料轉換為位元據，然後按位元運算。舉個簡單的例子。
樣本3：聲明2個int型變數@num1，@num2，對這兩個賦值且做與或異或運算。
執行下面的語句

DECLARE @num1 int,@num2 intSET @num1=5 SET @num2=6SELECT @num1&@num2 AS 與,@num1|@num2 AS 或,@num1^@num2 AS 異或

結果

擴充樣本4：寫一個十進位轉換為二進位的函數

CREATE FUNCTION Bin_con_dec(@dec int)--定義十進位轉換為二進位函數RETURNS varchar(20)ASBEGINDECLARE @quo int,@remainder varchar(20),@quo1 intSET @[email protected]SET @remainder=‘‘WHILE @quo<>0BEGINSET @[email protected]/2SET @remainder=CAST(@quo%2 AS varchar(20))[email protected]SET @[email protected]ENDRETURN @remainderEND

執行上面的函數後，運行下列語句驗證函式正確性

PRINT dbo.Bin_con_dec(42)

結果為101010，函數定義正確。

2.4.比較子

也稱關係運算子，用於比較兩個值的關係，常見的有等於（=），大於（>），小於（<），大於等於（>=），小於等於（<=），不等於（<>或！=）
樣本5：從Student表中查詢入學成績在平均分以上的學生資訊
Student表的資料

執行下列語句

DECLARE @ave intSET @ave=(SELECT AVG(stu_enter_score) FROM Student)SELECT *FROM StudentWHERE stu_enter_score>[email protected];

結果如所示

註：不能直接把代碼寫成下面的形式

SELECT * FROM StudentWHERE stu_enter_score>=AVG(stu_enter_score)

訊息147，層級15，狀態1，第2 行
彙總不應出現在WHERE 子句中，除非該彙總位於HAVING 子句或挑選清單所包含的子查詢中，並且要對其進行彙總的列是外部參考。

因為AVG是彙總函式。

2.5.邏輯運算子

邏輯運算子的作用是對條件進行測試。ALL,AND,ANY,BETWEEN,EXISTS,IN,LIKE,NOT,ALL,SOME。下面用SOME來舉例。SOME的作用是如果在一組比較中，有些為true那就為true。
樣本6：查詢Student表中是否存在入學成績高於平均分的學生，如果存在，輸出true，不存在輸出false。
Student表的stu_enter_score列（入學成績）資料

執行下面的語句

USE testIF (SELECT AVG(stu_enter_score) FROM Student)<=SOME(SELECT stu_enter_score FROM Student)PRINT ‘true‘ELSEPRINT ‘false‘GO

結果

2.6.串連運算子

加號（+）是字串串連運算子，可以用它把字串串聯起來，在樣本4的十進位轉二進位函數中，就用上了加號。
樣本7：將Student表的stu_name列和stu_enter_score列放在同一列顯示，列名為score
Student表的資料

執行下列語句

SELECT stu_name+CAST(stu_enter_score AS VARCHAR(3)) AS score FROM Student

執行結果

註：stu_enter_score列資料類型為int，加號只對字串類型資料有效，因此要用CAST函數將stu_enter_score的資料類型轉換為varchar(3)，這樣才能實現字串拼接。

2.7.一元運算子

一元運算子只對一個運算式執行操作，該運算式可以是數字資料類型中的任何一種資料類型。SQL Server 2008提供的一元運算子包含正（+），負（-），位反（~）。
樣本8：聲明一個int資料類型變數@num並賦值，對該變數做正負位反操作。
執行下列語句

DECLARE @num INTSET @num=45SELECT [email protected] AS 正,[email protected] AS 負,[email protected] AS 位反GO

結果

註：位反操作符用於取一個數的補數，只能用於整數。

2.8.運算子的優先順序

優先順序	運算子
1	~（位反）
2	*（乘），/（除），%（模數）
3	+（正），-（負），+（加），+（串連），-（減），&（位與）
4	=，>，<，>=，<=，<>，!=，!>，!<（比較子）
5	^（位異或），位或(符號打不出來，前面有，自己翻)
6	NOT
7	AND
8	ALL，ANY，BETWEEN，IN，LIKE，ALL，SOME
9	=（賦值）

當運算式中的運算子有相同的優先順序時，按照它們在運算式中的位置，一元運算子按從右往左運算，二元運算子（對兩個運算式作用的運算子）按從左往右運算。
樣本9：驗證運算子優先順序
執行下列語句

DECLARE @result INT,@num INTSET @num=45SET @[email protected]+([email protected])*[email protected]/([email protected])SELECT @result AS resultGO

結果

計算代碼中的運算式
@[email protected]+([email protected])[email protected]/([email protected])
[email protected]+(-46)[email protected]/(-46)
=45+(-46)4-45/(-46)
=45+(-46)4
=-139

3.控制語句3.1.BEGIN END語句塊

BEGIN END可以定義SQL Server語句塊，使這些語句作為一組語句執行，允許語句嵌套。舉例請見樣本4

3.2.IF ELSE語句塊

用於指定T-SQL語句的執行條件，若條件為真，則執行條件運算式後面的語句，條件為假時，可以試用ELSE關鍵字指定要執行的T-SQL語句。舉例請見樣本4。

3.3.CASE分支語句

樣本10：將Student表的學生，性別和籍貫列印出來，要求籍貫只能顯示省內，省外或自治區。
Student表的資料

執行下列語句

SELECT stu_name AS 姓名,stu_sex AS 性別,(CASE stu_native_placeWHEN ‘浙江‘ THEN ‘省內‘WHEN ‘內蒙古‘ THEN ‘自治區‘WHEN ‘西藏‘ THEN ‘自治區‘WHEN ‘寧夏‘ THEN ‘自治區‘WHEN ‘新疆‘ THEN ‘自治區‘WHEN ‘廣西‘ THEN ‘自治區‘ELSE ‘省外‘END) AS 籍貫 FROM Student

結果

3.4.WHILE語句

用於設定重複執行T-SQL語句或語句塊的條件。
樣本11：用“*”在螢幕上輸出一個寬度為9的菱形。
執行下列語句

DECLARE @width int,@j intSET @[email protected]為菱形的最大寬度SET @[email protected]表示每行列印的“*”符號的個數WHILE @j<[email protected]BEGINPRINT SPACE((@[email protected])/2)+REPLICATE(‘*‘,@j)--SPACE函數列印n個Null 字元，REPLICATE列印n個特定字串SET @[email protected]+2ENDSET @[email protected]WHILE @j>0BEGINPRINT SPACE((@[email protected])/2)+REPLICATE(‘*‘,@j)SET @[email protected]END

結果

3.5.WAITFOR延遲語句

WAITFOR延遲語句可以讓在它之後的語句在一個指定的時刻或是時間間隔後執行，可以懸掛起批處理，預存程序或事務的執行。
樣本12：在某個時間點查詢Student表學號為20180101的學生資訊

BEGINWAITFOR TIME ‘15:03‘--在15點03分查詢SELECT * FROM StudentWHERE stu_no=‘20180101‘END

樣本13：在3分鐘後查詢Student表學號為20180102的學生資訊

BEGINWAITFOR DELAY ‘00:03‘--在3分鐘後查詢SELECT * FROM StudentWHERE stu_no=‘20180102‘END

3.6.RETURN無條件退出語句

該語句表示無條件終止查詢，批處理或預存程序的執行。預存程序和批處理RETURN語句後面的語句都不再執行。當在預存程序中使用該語句時，可以指定返回給調用應用程式、批處理或過程的整數值。如果RETURN未指定傳回值，則預存程序的傳回值是0

3.7.GOTO跳躍陳述式

該語句使T-SQL批處理的執行跳轉至指定標籤。由於該語句破壞結構化語句的結構，盡量少用
樣本13：將GOTO作為分支機制
執行下面語句

DECLARE @Counter int;  SET @Counter = 1;  WHILE @Counter < 10  BEGIN       SELECT @Counter      SET @Counter = @Counter + 1      IF @Counter = 4 GOTO Branch_One --Jumps to the first branch.      IF @Counter = 5 GOTO Branch_Two  --This will never execute.  END  Branch_One:      SELECT ‘Jumping To Branch One.‘      GOTO Branch_Three; --This will prevent Branch_Two from executing.  Branch_Two:      SELECT ‘Jumping To Branch Two.‘  Branch_Three:  SELECT ‘Jumping To Branch Three.‘;

結果

當Counter=4時，執行GOTO語句輸出Branch One，執行完這個語句之後就打破了WHILE迴圈，接著執行Branch_One語句中的GOTO，輸出Branch Three，結束。

註：在WHILE迴圈中使用GOTO會打破迴圈。

樣本14：用GOTO語句實現樣本11中列印菱形的功能
執行下列語句

DECLARE @width int,@j int,@i intSET @[email protected]為菱形的最大寬度SET @[email protected]表示每行列印的“*”符號的個數SET @[email protected]表示下一行列印第i行Set3:PRINT SPACE((@[email protected])/2)+REPLICATE(‘*‘,@j)--SPACE函數列印n個Null 字元，REPLICATE列印n個特定字串SET @[email protected]+1IF @i<=(@width+1)/2GOTO Set1ELSEGOTO Set2Set1:SET @[email protected]+2GOTO Set3Set2:SET @[email protected]IF @j>=1GOTO Set3

結果

3.8.TRY CATCH錯誤處理語句

如果TRY塊內部發生錯誤，會將控制傳遞給CATCH塊內的語句組。TRY CATCH構造捕捉所有嚴重層級大於10但不會終止資料庫連接的錯誤。
樣本15：TRY CATCH的樣本
執行下列語句

BEGIN TRYSELECT * FROM StudentSELECT 120/0 FROM StudentEND TRYBEGIN CATCHSELECT ERROR_MESSAGE() AS ‘錯誤資訊‘END CATCH

執行結果

語句中3個select語句全部都執行了。如果把報錯的select語句放到正常的select語句前面，正常的select語句還能不能執行呢？執行下列語句

BEGIN TRYSELECT 120/0 FROM StudentSELECT * FROM StudentEND TRYBEGIN CATCHSELECT ERROR_MESSAGE() AS ‘錯誤資訊‘END CATCH

結果

正常select語句無法執行。TRY CATCH語句的邏輯是，一旦TRY語句塊中出現問題語句，立刻跳轉到CATCH語句塊，TRY語句塊接下去的語句不再執行。

4.常用函數4.1.資料類型轉換函式

預設情況下SQL Server會對一些資料類型進行自動轉換，這種轉換稱為隱式轉換。遇到無法自動轉換，則需要用CAST()函數和CONVERT()函數轉換，這種轉換稱為顯式轉換。CAST()函數和CONVERT()函數的功能是相同的，CAST函數更容易使用，CONVERT函數的優點是可以指定日期和數值格式。
樣本16：將Student表中的學號轉換為日期格式
下面兩句語句的功能是一樣的，執行下列語句

SELECT stu_name,CAST(stu_no AS DATE) AS 學號轉換成日期,stu_enter_score,stu_birthday FROM StudentSELECT stu_name,CONVERT(DATE,stu_no) AS 學號轉換成日期,stu_enter_score,stu_birthday FROM Student

結果

樣本17：用CONVERT()函數將stu_birthday轉化成指定格式的日期
執行下列語句

SELECT stu_name,CONVERT(VARCHAR(20),stu_birthday,101) FROM Student--CONVERT函數將DATE類型的stu_birthday欄位轉化為字串，並限定了樣式，代碼101

結果

註：在上述代碼中，CONVERT(DATE,stu_birthday,101)這麼寫是沒用的。101格式碼只對日期格式轉化為字串有效，其他格式轉化為日期格式是無效的。

其他常用函數太簡單了這邊不寫了，略。

《SQL Server 2008從入門到精通》--20180710