Jiang Ying-Fu的部落格

Select [Top 正整數] [Distinct | All][ 欄位,... | * ]
From [資料表,...]
[Where 條件式 ]
[Group by [欄位,....] ]
[Having 條件式 ]
[Order by [ 欄位 desc | asc ,...] ]
[Limit [Start,] Count ]

[ ] 內的代表不一定要有，但在Select之後的欄位顯示，如果沒有設定欄位，則一定要填上*。
*代表全部。
強烈建議，一定要下Where語法，在一般的小資料庫(例：北風資料庫)中，不下Where不會有問題
但在資料破萬、十萬以上的資料庫中，會影響到伺服器效能。
嚴重時，會造成伺服器系統當機，需重新開機。
如果真的有需要執行會影響到效能的指令，可在離峰時期執行，或將資料COPY至另一台電腦，於該電腦執行。亦可將SQL指令分解拆散，縮小範圍，改由程式一句一句慢慢執行，將伺服器的CPU使用量分散開來。
一個完整SQL指令文字長度，盡量避免超過255個字元。
若欄位名稱為SQL的關鍵字，可以使用 [ ] 將欄位名稱放在其中。
舉凡資料表，大致上都會有一個隱性的欄位，只要不對資料表做排序，一般都會先以該欄位做排序。
該欄位是以採用先進先出的方法做處理。除了特定資料庫可以呼叫出來，一般資料庫都無法顯現出來。

 

查詢資料時，盡量在最少的查詢次數中，查到最大且最有效的資料，除非特殊情況，否則盡量使用這個原則。

善用Case...When...Then語法，可以增加查詢的效率，亦同時減少查詢的次數。

查詢資料表，以PK為第一優先，再來是index，再來是FK，其餘放最後。

當Where的條件式很多時，善用 ( ) ，可以提高查詢的效率。

盡量避免null值，null值在查詢時，容易產生一些不明的錯誤，或讓資料產生偏差。

All：取得全部記錄(預設)
Distinct：去除重覆記錄

條件式：
Having 通常是配合 Group by ，在Group by後，找出符合的條件內容，專門用統計函式的結果值作為條件
Where 則只是單純的在 Group by 前，找出符合條件的內容，在這不能使用統計函數
下條件式時，盡量以Index欄位為主，提高效率
同時避免對該欄位做函式變化
例：
設定Name欄位為Index，並對該欄位做Where
1.A.Name Like '一%'
2.Left(A.Name,2) Like '一%'
此兩種方法，第一種會比第二種有效率，且省時間。
使用第二種方法時，資料庫會將該欄位不當做Index，反而會增加Select的負擔

排序：
Desc：由大排到小
Asc：由小排到大(預設)

Limit語法，只限定在Postgre SQL和MySQL中使用，類似Top
但可設定起始記錄
Select * from A Limit 2, 3
意即為由第二筆記錄開始，往下抓三筆，只會顯示出此三筆記錄
Select * from A Order by A.ID Limit 3
意即只會抓排序後的前三筆記錄

 

結合(合併)資料表

合併資料表有兩種方法，一種使用Where，一種使用Join。
Where的方法又稱為外結合，會受到資料庫不同，而受限無法使用。
Join因為通用語法(ANSI/SQL92)，且可定義合併時的先後順序和比重，以便達成最佳化，所以一般較推薦使用此方法。

Where無法像Join一般，可以定義合併時的權重、先後順序，但Where並不受限於資料庫。
不過依舊要注意，有些資料庫，並無法使用完整的Where合併語法。

 

語法一：
Select Table1.ID,Table1.Name,Table2.ID, Table2.Name
From Table1 , Table2
WHere Table1.ID=Table2.ID

語法二：
Select Table1.ID,Table1.Name,Table2.ID, Table2.Name
From Table1 Inner Join Table2 On Table1.ID=Table2.ID

在合併資料表中，有五種合併結構。

1.Inner Join
合併時，以左右兩側都相等，才結合取出，取得的筆數為最少。
Select A.ID , B.Name From A Inner Join B on A.ID=B.ID
Select A.ID , B.Name From A , B Where A.ID=B.ID

2.Left Join
以左邊的資料表為主，可查出右邊的資料表為null的記錄
Select A.ID , B.ID From A Left Join B On A.ID=B.IDZ
Select A.ID , B.ID From A , B Where A.ID*=B.ID

3.Right Join
以左邊的資料表為主，可查出左邊的資料表為null的記錄
Select * From A Right Join B On A.ID=B.IDZ
Select A.ID , B.ID From A , B Where A.ID=*B.ID

4.Full Outer Join
同時取得左右側資料表的資料，需指定做連結的欄位，左右兩側都有可能有Null記錄。
Select * From A Full Outer Join B On A.ID=B.IDZ

(Where無此方法)

5.Cross Join(不建議使用，此方法為挑戰電腦極限，取出來的資料也較無意義)
同時取得左右側資料表的資料，不需指定連結的欄位，取得總筆數=A筆數*B筆數。
Select * From A Cross Join B
Select A.ID , B.ID From A , B


三個資料表做結合：
Select * From (A Left Join B On A.ID=B.ID) Right Join C On B.Kind=C.KindID

四個資料表做結合：
Select * From D Inner Join ((A Left Join B On A.ID=B.ID) Right Join C On B.Kind=C.KindID) On D.ID=A.KindID

 

 

可以修改為下面的句子，會較有效率

在合併時，就同時去除不合的記錄，以提高合併時的效率。

 

合併資料表

合併時有三種結構：聯集(Union)、差集(Difference)、交集(Intersection)

聯集(Union)：
Select A.ID,A.Name From A Union Select B.ID,B.Name From B 此語法會將A和B的資料全秀出來

差集(Difference)：
Select A.ID From A Except Select B.ID From B (DB2,Postgre SQL)
Select A.ID,A.Name From A Minus Select B.ID,B.Name From B (Oracle)
此語法會將A.ID沒在B出現過的全秀出來。
可用子查詢來取代
Select * From A Where A.ID Not In(Select B.ID From B)

交集(Intersection)：
Select A.ID From A Intersect Select B.ID From B
此語法會將A.ID有在B出現過的全秀出來
可用子查詢來取代
Select * From A Where A.ID In(Select B.ID From B)

 

子查詢

子查詢在資料筆數過多的資料庫中，是一個可以提高效率的語法。
同時用法也很多種，需視使用者所要的資料而做變化。

Select * From A.ID In (Select B.ID From B Where B.Price>50)

Select * From (Select A.ID, A.Name , A.Price From A Where A.Price>100) As ATemp
Inner Join (Select B.ID, B.Kind From B Where B.Kind Between 1 And 60) As BTemp On
ATemp.ID=BTemp.ID
先降低記錄數量，縮小範圍，再做結合，會比一開始就做結合還有效率。

 

在Oracle中，子查詢是相當好用的語法，可以減少DB Server的負擔。

在一般DB中，子查詢的條件只能是一個，但在Oracle中，子查詢的條件可以不止一個。

例：

Select * from A Where (A.ID,A.Dept) in (Select B.ID, B.Dept From B)

這個法語可以一次找出在A Table中，ID和Dept同時和B Table相同的記錄。

欄位別名

Select A.ID as [編號] From A
Select [學生資料表].ID as [編號] From A as [學生資料表]
Select [學生資料表].ID [編號] From A [學生資料表]
Select [編號]=[學生資料表].ID From A [學生資料表]
別名不建議使用中文，請盡量使用英文，亦避免使用數字做為開頭。
同時避使用特殊字元，例：~!@#$%^&*()+=|\/><◎∞......
在此使用中文是為了明顯區分。

 

做Group by時，如果有欄位沒有呈現在Group By裡面，但又想要將資料呈現在畫面時

可以使用Max、Min等函數，將欄位呈現出來。

下列三種，只能在Oracle和DB2中使用

依Rollup中的內容順序，列出各小計和總合計
Select 月,商品,Sum(數量) From [Order] Group By Rollup(月,商品)

依Cube中的內容順序，列出各小計和總合計
Select 月,商品,Sum(數量) From [Order] Group By Cube(月,商品)

依Grouping Sets中的內容順序，列出總合計
Select 月,商品,Sum(數量) From [Order] Grouping Sets((月,商品),())

排序

Select A.ID , A.Name , A.Num*A.Price as [AllPrice] From A Order by [AllPrice]
Select A.ID , A.Name , A.Num*A.Price as [AllPrice] From A Order by 3

排序時，欄位名可以整數取代，此整數同欄位的順序。

使用Select將資料COPY至新的資料表或其他儲存媒體
限定Postgre SQL 、 SQL Server 、 MS Access使用

Select * Into [New_Table] From A
Select * Into [Excel 8.0;DATABASE=Excel.xls] From A
Select * Into [驅動程式;DATABASE=檔案名稱.副檔名] From A

 

 

 

bcp匯入匯出備份指令，限定SQL Server

 

bcp {dbtable | query} {in | out | queryout | format} datafile

 

參數	說明	參數	說明	參數	說明
m	最大錯誤數	f	格式檔案	e	錯誤檔案
F	第一列	L	最後一列	b	批次大小
n	原生類型	c	字元類型	w	寬字元類型
N	非文字保持不變	V	檔案格式版本	q	引號識別碼
C	字碼頁規範	t	欄位結束字元	r	資料列結束字元
i	輸入檔	o	輸出檔	a	封包大小
S	伺服器名稱	U	使用者名稱	P	密碼
T	信任連接	v	版本	R	區域啟用
k	保留 Null 值	E	保留識別值
h	"load hints"	x	產生 xml 格式檔案

 

--建立XML格式檔

bcp DataBase.dbo.Table format nul -T -c -x -f filename.xml

--匯出資料

bcp DataBase.dbo.Table out filename.txt -c -T

--匯入資料，資料表必須是存在的實體資料表。

bcp DataBase.dbo.Table in filename.txt -T -t -f filename.xml

 

 

刪除Table裡的所有資料

這個語法不會產生任何的log記錄，所以在刪除時，會比使用Delete快上許多。

但也因為沒有產生log記錄，所以一旦使用此語法，資料無法還原。