Transact-SQL具體可以參閱《Transact-SQL參考》(tsql.hlp)(簡寫《T-SQL》)
?
建意：
? 在寫SQL Script時最好能將數(shù)據(jù)操作SQL的保留字用大寫
注：
此處語法格式只是常用格式，并不是SQL標準格式，標準格式請參閱《T-SQL》
（在例子中的SQL無實際意義）
?
選擇
SELECT
SELECT 可以選擇指定的數(shù)據(jù)列
如：
SELECT * FROM sysobjects
SELECT [name] FROM syscolumns
當在SQL中存在系統(tǒng)保留字時應用“[]”引起，或在SQL中存在特殊字符也應用“[]”引起，
如：
?????? SELECT [Object Name] FROM Objects
在使用別名時也應注意以上原則，別名使用可以用以下兩種方法：
?????? Column_name AS alias
?????? Column_name alias
中間的AS可以省略
在SELECT中可以使用條件選擇語法，參見下面的“條件”
?????? 如：
????????????? SELECT [name],xtype,CASE WHEN xtype=’U’ THEN ‘用戶表’ ELSE CASE WHEN xtype=’S’ THEN ‘系統(tǒng)表’ END END AS 類型 FROM sysobjects
返回表：
|
name
|
xtype
|
類型
|
|
syscolumns
|
S
|
系統(tǒng)表
|
|
tabledefine
|
U
|
用戶表
|
?
將兩個查詢合成單獨的返回表：
用UNION關(guān)鍵字
如SELECT A,B FROM Table1
? UNOIN
? SELECT C,D FROM Table2
說明：
?????? 在使用UNION時，若無ALL參數(shù)則默認將過慮相同的記錄，
?????? 如：
|
Table1
|
?
|
Table2
|
|
ID
|
TF1
|
VALUE1
|
?
|
ID
|
TF2
|
VALUE2
|
|
1
|
A
|
10
|
?
|
5
|
A
|
10
|
|
5
|
B
|
20
|
?
|
6
|
D
|
21
|
|
2
|
A
|
30
|
?
|
3
|
C
|
31
|
|
3
|
C
|
40
|
?
|
1
|
B
|
41
|
?????? SELECT TF1,VALUE1 FROM Table1
?????? UNION
?????? SELECT TF2,VALUE2 FROM Table2
?????? 返回表：
?????????????
|
TF1
|
VALUE1
|
|
A
|
10
|
|
B
|
20
|
|
A
|
30
|
|
C
|
40
|
|
D
|
21
|
|
C
|
31
|
|
B
|
41
|
?????? 其中可以看出少了一個”TF2=A ,VALUE2=10”的記錄
?????? 但用以下查詢時
?????? SELECT TF1,VALUE1 FROM Table1
?????? UNION? ALL
?????? SELECT TF2,VALUE2 FROM Table2
?????? 返回表：
?????????????
|
TF1
|
VALUE1
|
|
A
|
10
|
|
B
|
20
|
|
A
|
30
|
|
C
|
40
|
|
A
|
10
|
|
D
|
21
|
|
C
|
31
|
|
B
|
41
|
?????? 剛此查詢將返回所有記錄
?????? 此問題可能會出現(xiàn)在報表統(tǒng)計上，如一個員工在不同日期內(nèi)做了相同的產(chǎn)品與數(shù)據(jù)，但在使用非ALL方式進行合計時將會少合計一條記錄
?
與INTO聯(lián)用
SELECT …. INTO B FROM A
可以將A 表的指定數(shù)據(jù)存入B表中
應用類型：
備份數(shù)據(jù)表：
????????????? SELECT * INTO Table1_bak FROM Table1
?????? 創(chuàng)建新表
????????????? SELECT * INTO New_Table1 FROM Table1 WHERE 1<>1
????????????? SELECT TOP 0 * INTO New_Table1 FROM Table1
?????? 保存查詢結(jié)果
????????????? SELECT Field1,Field2 INTO Result FROM Table1 WHERE ID>1000
?????? 創(chuàng)建新表并在新表中加入自動序號
????????????? 一表有些表需要一個自動編號列來區(qū)別于各行
????????????? SELECT IDENTITY (INT,1,1) AS AutoId,* INTO new_Table1 FROM Table1
????????????? 其中IDENTITY函數(shù)說明：
???????????????????? 格式：
??????????????????????????? IDENTITY (<datatype> [seed,increment])
???????????????????? 參數(shù)說明：
??????????????????????????? Datatype:數(shù)據(jù)類型，視記錄數(shù)定類型，一般可以定INT型，具體可以參考SQL的極限參數(shù)
??????????????????????????? Seed:開始數(shù)值，即開始的基數(shù)，默認為1
??????????????????????????? Increment:增量，步長即數(shù)據(jù)間的間隔，默認為1
????????????? 上面的SQL即表示，自動編號從1開始并每行加1
返回的表為：
|
AutoId
|
Field1
|
Field2
|
|
1
|
Hello
|
Joy
|
|
2
|
Hello
|
Tom
|
|
3
|
Hi
|
Lily
|
|
4
|
Hello
|
Lily
|
????????????? 注：
???????????????????? IDENTITY還可以在創(chuàng)建表時設(shè)置
???????????????????? 格式：
??????????????????????????? IDENTITY ([seed, increment])
???????????????????? 如：
??????????????????????????? 創(chuàng)建表
??????????????????????????? CREATE TABLE Table1 (
?????????????????????????????????? AutoId int IDENTITY(1,1), 或 autoid int identity
?????????????????????????????????? Field1 nvarchar(30),
?????????????????????????????????? Field2 nvarchar(30)
)
??????????????????????????? 修改表
??????????????????????????? ALTER TABLE Table1 ADD AutoId int IDENTITY (1,1)
????????????? 在進行數(shù)據(jù)插入時應注意IDENTITY_INSERT這個屬性的設(shè)置
???????????????????? 當 SET IDENTITY_INSERT <table> ON 時，則不能進行隱式插入
?????? ????????????? 如：
??????????????????????????? SET IDENTITY_INSERT Table1 ON
??????????????????????????? INSERT INTO Table1 SELECT (‘r1c1’,’r1c2’) ??????? --這樣就會出錯
??????????????????????????? 必需使用：
??????????????????????????? INSERT INTO Table1 SELECT (1,’R1C1’,’R1C2’)
???????????????????? 只能在SET IDENTITY_INSERT <table> OFF 時才允許隱式插入
???????????????????? 如：
??????????????????????????? SET IDENTITY_INSERT Table OFF
必需使用：
??????????????????????????? INSERT INTO Table1 SELECT (‘r1c1’,’r1c2’) ???????
??????????????????????????? 否則
??????????????????????????? INSERT INTO Table1 SELECT (1,’R1C1’,’R1C2’) --這樣就會出錯
????????????? 在使用隱式插入后可以用@@IDENTITY這個系統(tǒng)值來返回插入行的編號
???????????????????? INSERT INTO Table1 SELECT(‘R1C1’,’R1C2’)
???????????????????? 返回表：
|
AutoID
|
Field1
|
Field2
|
|
1
|
R1C1
|
R1C2
|
???????????????????? SELECT @@IDENTITY
???????????????????? 返回值：
??????????????????????????? 1
????????????? 在應用程序中可以用以下方法做：
???????????????????? set recs=cnn.execute(“INSERT INTO Table1 SELECT(‘R1C1’,’R1C2’)”)
???????????????????? recordnum=cnn.execute(“SELECT @@IDENTITY”).fields(0).value
???????????????????? 以上語句執(zhí)行后recordnum的值將設(shè)置為最后一個自動編號
?
關(guān)聯(lián)
?????? 用例:
|
Table1
|
?
|
Table2
|
|
ID
|
TF1
|
VALUE1
|
?
|
ID
|
TF2
|
VALUE2
|
|
1
|
TFI1-1
|
10
|
?
|
5
|
TFI2-1
|
11
|
|
5
|
TFI1-2
|
20
|
?
|
6
|
TFI2-2
|
21
|
|
2
|
TFI1-3
|
30
|
?
|
3
|
TFI2-3
|
31
|
|
3
|
TFI1-4
|
40
|
?
|
1
|
TFI2-4
|
41
|
?
Table2
INNER JOIN
只顯示兩表一一對應的記錄
SELECT * FROM Table1 INNER JOIN Table2 ON Table1.ID=Table2.ID ORDER BY Table1.ID
返回表：
|
ID
|
TF1
|
VALUE1
|
ID
|
TF2
|
VALUE2
|
|
1
|
TFI1-1
|
10
|
1
|
TFI2-4
|
41
|
|
3
|
TFI1-4
|
40
|
3
|
TFI2-3
|
31
|
|
5
|
TFI1-2
|
20
|
5
|
TFI2-1
|
11
|
?
LEFT JOIN(LEFT OUTER JOIN)
顯示左表所有記錄與右表對應左表的記錄，當在右表中無記錄，則右表相應字段用NULL填充
SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.ID=Table2.ID ORDER BY Table1.ID
返回表：
|
ID
|
TF1
|
VALUE1
|
ID
|
TF2
|
VALUE2
|
|
1
|
TFI1-1
|
10
|
1
|
TFI2-4
|
41
|
|
2
|
TFI1-3
|
30
|
NULL
|
NULL
|
NULL
|
|
3
|
TFI1-4
|
40
|
3
|
TFI2-3
|
31
|
|
5
|
TFI1-2
|
20
|
5
|
TFI2-1
|
11
|
RIGHT JOIN(LEFT OUTER JOIN)
顯示右表所有記錄與左表對應右表的記錄，當在左表中無記錄，則左表相應字段用NULL填充
SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.ID=Table2.ID ORDER BY Table1.ID
返回表：
|
ID
|
TF1
|
VALUE1
|
ID
|
TF2
|
VALUE2
|
|
NULL
|
NULL
|
NULL
|
6
|
TFI2-2
|
21
|
|
1
|
TFI1-1
|
10
|
1
|
TFI2-4
|
41
|
|
3
|
TFI1-4
|
40
|
3
|
TFI2-3
|
31
|
|
5
|
TFI1-2
|
20
|
5
|
TFI2-1
|
11
|
FULL JOIN(FULL OUTER JOIN)
顯示左右兩表所有記錄，當左表無記錄，則左表相應字段用NULL填充，當右表無記錄則右表相關(guān)字段用NULL填充
SELECT * FROM Table1 LEFT JOIN Table2 ON Table1.ID=Table2.ID ORDER BY Table1.ID
返回表：
|
ID
|
TF1
|
VALUE1
|
ID
|
TF2
|
VALUE2
|
|
1
|
TFI1-1
|
10
|
1
|
TFI2-4
|
41
|
|
2
|
TFI1-3
|
30
|
NULL
|
NULL
|
NULL
|
|
3
|
TFI1-4
|
40
|
3
|
TFI2-3
|
31
|
|
5
|
TFI1-2
|
20
|
5
|
TFI2-1
|
11
|
|
NULL
|
NULL
|
NULL
|
6
|
TFI2-2
|
21
|
說明：
?????? 在進行多級關(guān)聯(lián)的時候應該采用就近關(guān)聯(lián)原則
如：
?????? SELECT * FROM Table1 INNER JOIN Table2 INNER JOIN Table2-1 ON Table2.ID=Table2-1.ID ON Table1.ID=Table2.ID
即Table2與Table2-1關(guān)聯(lián)
? Table1與Table2關(guān)聯(lián)
建意：
?????? 在寫此類關(guān)聯(lián)時，最好將基語句格式結(jié)構(gòu)化
?????? 如：
?????? SELECT *
?????? FROM
?????? Table1
?????? INNER JOIN Table2
????????????? INNER JOIN Table2-1
????????????? ? ON Table2.ID=Table2-1.ID
?????? ON Table1.ID=Table2.ID
?????? WHERE
?????? ID IN (1,2,3)
注：
?????? 在寫完查詢語句后，可以由“企業(yè)管理器”進行SQL語句的格式化，但這一過程出來的語句一定要進行測試，因為在他自動格式化時可能會把某些復雜的關(guān)系搞錯
?
分組
GROUP BY
（沒什么好說！！）
如：
?????? SELECT A,B,SUM(D) FROM Table1 GROUP BY A,B ORDER BY A
注：
?????? 在進行GROUP BY 時應該注意GROUP BY 中字段的使用，
?????? 只要在同一查詢語句中則所有未進行驟合操作的字段都需要被GROUP，
?????? 如上面的SQL中，字段A,與B都未被驟合，并字段A被排序，而字段D被驟合函數(shù)SUM進行匯總統(tǒng)計
?????? 因此字段A,B需要被GROUP 而D則不用
如：
? ??? SELECT A,B,SUM(D) FROM Table1 GROUP BY A,B,C ORDER BY C
在此查詢中，雖然字段C沒有被選擇，但他被ORDER因此字段C也應該在GROUP的字段中
如：
?????? SELECT A,B,SUM(D) FROM Table1 WHERE A IN (SELECT D FROM Table1 T1 WHERE NOT C IS NULL) GROUP BY A,B,C ORDER BY C
?????? 在此查詢中字段A,B為選擇字段，字段C為排序字段，但字段D雖然也在同一張表Table1中，但他在子查詢中因此不用進行對D的GROUP
?
?????? 若要對聚合結(jié)果進行篩選則應該使用HAVING關(guān)鍵字，而不是WHERE關(guān)鍵字，
?????? 如：
?????? SELECT A,B,SUM(D) FROM Table1 WHERE COUNT(*)>2 GROUP BY A,B?? ---這樣將會出錯，因為COUNT為一個聚合函數(shù)，在WHERE子句中不能對聚合函數(shù)進行篩選
?????? 應改為：
?????? SELECT A,B,SUM(D) FROM Table1 GROUP BY A,B HAVING COUNT(*)>2
?
應用GROUP可以進行分類統(tǒng)計
相關(guān)的關(guān)鍵字為CUBE,ROLLUP但不建意使用這兩個關(guān)鍵字，
在一般情況下，如果程序中的GRID有分類匯總功能，那相應的速度會比使用這兩個關(guān)鍵字要快，
與這兩個關(guān)鍵字一起使用的聚合函數(shù)為GROUPING()，即當進行項目分類匯總時GROUPING()將會返回1，反之則為0，為可以寫統(tǒng)計標題時提供參考，
具體說明請參見《T-SQL》
具體實例在《SOMIC人力資源管理》中<部門人員匯總表>中有應用
?
條件
CASE WHEN
此組關(guān)鍵字的功能可以代替IF…THEN….ELSE或SELECT CASE
語法結(jié)構(gòu)：
CASE? [expression]
?? ???WHEN <condition> THEN result
??? ????[ELSE else_result ]
??? END
在查詢中使用此語句時應盡量在END后加別名，
?????? 如：
????????????? SELECT [name],xtype,CASE WHEN xtype=’U’ THEN ‘用戶表’ ELSE CASE WHEN xtype=’S’ THEN ‘系統(tǒng)
返回表：
|
name
|
xtype
|
類型
|
|
syscolumns
|
S
|
系統(tǒng)表
|
|
tabledefine
|
U
|
用戶表
|
?????? 詳細用例請參考《紡織計件工資》中<人員-部門產(chǎn)量匯總表>
?????? 用此語句與SELECT用UNION聯(lián)用能做行列換位
?
?
?
?
過程性語句應用
?
變量定義
?
在SQL中用戶變量是以@打頭的字串,系統(tǒng)變量用@@打頭
如：
?????? @i
?????? @tmpStr
定義方法：
?Declare @i int
?Declare @tmpStr nvarchar(30)
?在完成變量定義后最好進行初始設(shè)置，如
Set @i=0
Set tmpStr=’’
或
Select @i=0,@tmpStr=’’
?在SQL中對變量的賦值應用SET或SELECT進行
?
游標定義
游標，可以將查詢結(jié)果返回為游標類型
定義方法：
Declare cursor <CurName>
? For <SQL SCRIPT>
如:
declare cursor GetName
? for SELECT [name] FROM sysobjects
游標使用方法：
打開游標：
Open <CurName>
如：open GetName
檢索游標：
Fetch [NEXT | PRIOR | FIRST | LAST] form <CurName> [into <valuename>…]
如：
Fetch next from GetName into @tmpName
當取值成功后，相應記錄值會填充在@tmpName變量中，并@@FETCH_STATUS變量置為0，
若失敗則@@FETCH_STATUS變量為-1
關(guān)閉游標
在使用完游標后關(guān)閉他，以便其他進程使用此游標
CLOSE <curname>
如：
?????? Close GetName
刪除游標
在使用完游標后，如不再需要應該刪除已使用游標，
DEALLOCATE <curname>
如：
?Deallocate GetName