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