Transact-SQL的組成元素

Transact-SQL的組成元素

結構化查詢語言的英文全名是Structured Query Language，簡稱SQL。

由於微軟公司對SQL Server的SQL做了不小程度的擴充，因此特別將SQL Server的SQL稱之為Transact-SQL。

不論是主從架構、多階層式架構，以至於Database Web應用程式，SQL Server皆扮演後端資料庫的角色。此意味著，SQL Server必須搭配一個前端工具來使用，如此可互補不足，達到最完美的境界。

事實上利用各種前端工具(例如：Visual Basic、Access、Visual C++…)所開發的前端程式除了使用其本身的獨特介面來存取SQL Server之外，亦能直接呼叫後端SQL Server的Transact-SQL命令敘述來完成特定作業。

仔細歸納後發現Transact-SQL的組成元素不外乎是下列各項：

(即依命令敘述所扮演的角色、定位、功能效用來加以分門別類)

◆    資料定義語言(Data Definition Language：DDL)

◆    資料處理語言(Data Manipulation Language：DML)

◆    資料控制語言(Data Control Language：DCL)

◆    系統預存程序(System Stored Procedure)

◆    其他的組成元素

※資料定義語言(Data Definition Language：DDL)

係指用來定義與管理資料庫及其所內含之各類物件的命令敘述，例如：建立、更改、刪除資料庫；資料表、檢視表、使用者自訂資料型態…等物件的命令皆屬之。

形式如下：

CREATE object_name

ALTER object_name

DROP object_name

在預設狀態下，只有sysadmin、dbcreator、db_owner或db_ddladmin等角色成員有權執行DDL命令敘述。

如果有不同的使用者帳戶建立物件，可以使用系統預存程序sp_changeobjectowner去改變物件的擁有者。(注意：只有db_owner角色的成員，或同時為db_ddladmin或db_securityadmin兩角色的成員，有權執行系統預存程序sp_changeobjectowner)。

※資料處理語言(Data Manipulation Language：DML)

係指用來查詢、新增、修改、刪除資料庫中之資料的命令敘述。

包括：

SELECT

INSERT

UPDATE

DELETE

在預設狀態下，只有sysadmin、dbcreator、db_owner或db_datawriter等角色的成員有權執行DML命令敘述。

※資料控制語言(Data Control Language：DCL)

係用來設定或變更資料庫使用者或角色之權限的命令敘述。

包括：

GRANT  能夠賦予使用者去存取某資料庫物件或執行特定Transact-SQL命令敘述的權限。

DENY    能夠否決使用者的某項權限，並防止使用者去繼承其群組或角色成員的權限。

REVOKE 能夠將先前所賦予或否決的權限移除。

在預設狀態下，只有sysadmin、dbcreator、db_owner或db_securityadmin等角色的成員有權執行DCL命令敘述。

※系統預存程序(System Stored Procedure)

乃內建的預存程序，其主要功能是用來完成各項管理工作，係存放在系統資料庫master中且名稱會以sp_開頭。

比方說，可以使用系統預存程序sp_addtype去定義一個使用者自訂資料型態、使用系統預存程序sp_rename去更改資料庫物件的名稱、使用系統預存程序sp_configure去設定伺服器的組態選項…等等。

※其他的組成元素

包括：

1.      註解

2.      區域性變數

3.      運算子

4.      函數

5.      流程控制語言

※常數

一個常數(Constant)是一個固定的資料項目、一個純量值、或是一個代表特定資料值的符號，常數的格式依其所代表之資料值的資料型態而有所不同。

★文字串常數

  必須內含在一對單引號中，並且會使用電腦的字碼頁(Code Page)來評量。

  例如：

  ‘章立民’

  ‘處理中，請稍後…’

  ‘輸入值必須介於30000～100000’

  如果將連結選項SET QUOTED_IDENTIFIER設定成OFF，亦可將文字串內含在一對雙引號中(文字串可含單引號)。

  一般如果欲採用將文字串內含一單引號中的格式，例如：文字串It’s long則以：

  ‘It’’s long’ 

將文字串本身所內含的單引號以兩個單引號來表示。

  值得注意的是連續兩個單引號且其中不含任何空格(亦即’’)就是代表空字串。

 

★Unicode字串常數

  Unicode字串常數與文字串常數的格式類似，唯一的差別是必須以大寫字母N作為前導字元，其N代表National Language。

  例如：

  ‘Language’是一個文字串常數，而N‘Language’則是一個Unicode字串常數，其常數會被解釋為Unicode資料，而且不會使用字碼頁來評量。

  Unicode資料的每一個字元使用2個位元組的儲存空間，而一般字元資料則是每一個字元使用1個位元組的儲存空間。

 

★二進位常數

  二進位常數係以0x作為前導再加上後續的16進位數值來表示，而且不需要內含在一對引號中。

  例如：

  0xAE

  0x12Ef

  0x69048AEFDD010E

  0x   此為空二進位常數

 

★Bit常數

  是以數目字0或1來表示，而且不需要內含在一對引號中。如果所使用的數目字大於1，則會被轉換成1。

 

★日期時間常數

  如果只指定日期，時間將預設是12:00AM，如果只指定時間，日期將預設是1900年1月1日。

  日期時間格式必須內含在一對單引號中。

  日期格式的年份還是採用 四位數 較為無虞。

 

字母日期格式有：

  ‘October 15, 2018’

  ‘Oct 15, 2018’

  ’15 Oct 2018’

  ‘2018 Oct 15’

  ‘2018 15 Oct’

 

  數字日期格式

  其年份、月份、日期全部使用數目字來表示，格式如：

  number separator number separator number

  其中separator為一個分隔字元，限為斜線(/)、連字號(-)、英文句號(.)。

  ‘8/17/1999’

  ‘8-17-1999’

  ‘8.17.1999’

  ‘01/21/1999

  可自訂數字日期格式中日期數值的先後順序，使用語法如SET DATEFORMAT命令：

  SET DATEFORMAT {format | @format_var}

  參數format | @format_var用來決定三項日期數值的先後順序，其設定值如下：

  mdy  (例如：’12/31/1999’)

  dmy  (例如：’31/12/1999’)

  ymd  (例如：’1999/12/31’)

  ydm  (例如：’1999/31/12’)

  myd  (例如：’12/1999/31’)

  dym  (例如：’31/1999/12’)

  範例：

  SET DATEFORMAT ydm

  GO

  …

  DECLARE @datevar datetime

  SET @datevar = ‘2001/21/09’

  SELECT @datevar

  GO

 

  未分隔字串格式

  例如：

  ‘19990908’  即代表西元1999年9月8日

 

 

  時間格式

  指定日期時間常數中的時間部分，採用下列格式：

  Hours [：Minutes [：Seconds] [：Milliseconds] ] ] [AM | PM]

  或

  Hours [：Minutes [：Seconds] [﹒Milliseconds] ] ] [AM | PM]

  可以使用12小時制或24小時制，必須知道12:00 AM、12:00與00:00皆代表午夜12點。

  ’14:30:20:559’

  ’14:30:20.5’

  ‘2:30:20.5 PM’

  ‘02:30:20.5 AM’

  ’10:20:50 PM’

  ’16:30’

  ‘6PM’

  ‘6am’

 

  綜合討論

  日期時間常數的日期部分可以採用字母日期格式、數字日期格式、或未分隔字串格式，至於時間的部分則採用時間格式。同一個日期時間常數有非常多種表示方式，如：

  ‘10/15/2018  15:10:45’

  ‘10/15/2018  3:10:45 PM’

  ‘October 15 2018 15:10:45’

  ‘October 15 2018 3:10:45 PM’

  ‘2018 Oct 15 15:10:45’

  ‘10/15/2018 03:10:45 PM’

  ‘20181015 15:10:45’

  雖然日期時間常數有非常多種表示方式，不過最好統一採用某一種格式，如此在開發、維護、與偵錯時，方能降低其複雜度。

 

★整數常數

  係以一連串的數目來表示，不包含小數點，而且不需內含在一對引號中，如：

  2991

  5

  10

 

★Decimal常數

  係以一連串的數字來表示，可以包含小數點，且不需內含在一對引號中，如：

  2902.2231

  1.2

  3.22

 

 

★Float與Real常數

  係以科學記號來表示，如：

  101.5E5

  0.5E-2

 

★貨幣值常數

  係以一連串的數字來表示，可以包含或不包含小數點，以一個貨幣符號($)作為前導，且不需內含在一對引號中，如：

  $234

  $66000.32

 

★全球性獨一識別碼常數

  能夠以文字串常數或二進位常數的形式來表示。

1.      文字串常數：只需將全球性獨一識別碼(Uniqueidentifier)內含在一對單引號中。

2.      二進位常數：以0x作為前導再加上後續的16進位數值。

下例為兩個完全相同的全球性獨一識別碼常數：

  ‘6F9619FF-8B86-D011-B42D-00C04FC964FF’

  0xff19966f868b11d0b42d00c04fc964ff

 

※指定正數與負數

對數字類型的常數而言，可以在其前方加上一個正號(+)或負號(-)來標示其為正值或負值。

※區域性變數(Local Variable)

通常將區域性變數當作一個計數器來使用，以便計算一個迴圈執行的次數，或是控制迴圈執行的次數。

再者，也可以使用區域性變數去存放自預存程序所傳回的資料值。

宣告區域性變數

在能使用一個區域性變數之前，必須先使用語法如下的DECLARE命令來宣告與建立它：

DECLARE {@local_variable data_type} […n]

其執行作業起始化如下：

◆    賦予區域性變數一個名稱

語法中的@local_variable引數用來指定區域性變數的名稱。必須以符號@開頭。

◆    決定區域性變數的資料型態與大小

語法中的data_type引數為設定它的資料型態與大小。惟資料型態不可以是text、ntext或image。

  

◆    DECLARE命令於建立區域性變數後，會將其初值設定成NULL。

DECLARE命令可以同時宣告多個區域性變數，只需以逗號(,)分隔即可。

範例：

1.      宣告一個名為@SearchName且資料型態為varchar(12)的區域性變數：

DECLARE @SearchName varchar(12)

2.      宣告一個名為@Birthday且資料型態為datetime的區域性變數：

DECLARE @Birthday datetime

3.      宣告一個名為@Height且資料型態為decimal(5,2)的區域性變數：

DECLARE @Height decimal(5,2)

4.      使用DECLARE命令敘述宣告三個區域性變數：

DECLARE @Name nvarchar(12) , @Phone nvarchar(11) , @Salary money