NO IMAGE
http://club.excelhome.net/thread-1061487-1-1.html

學習SQL一步一步開始,跟著本文的腳印帶你走向SQL方向。
本分先分別介紹Microsoft Query 方法書寫SQL語句,和VBA方法書寫SQL語句。(注:VBA語句中只需修改其中的SQL語句即可,其他只需要複製貼上即可)。
下面先上圖:

進入VBA介面
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

2.插入模組
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

3.雙擊模組
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
4.在程式碼區碼 從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

寫入VBA程式碼
程式碼如下:

  1. Sub Test4()
  2.     Dim Conn As Object, Rst As Object
  3.     Dim strConn As String, strSQL As String
  4.     Dim i As Integer, PathStr As String
  5.     Set Conn = CreateObject(“ADODB.Connection”)
  6.     Set Rst = CreateObject(“ADODB.Recordset”)
  7.     PathStr = ThisWorkbook.FullName   ‘設定工作簿的完整路徑和名稱
  8.     Select Case Application.Version * 1    ‘設定連線字串,根據版本建立連線
  9.     Case Is <= 11
  10.         strConn = “Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=” & PathStr
  11.     Case Is >= 12
  12.         strConn = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & PathStr & “;Extended Properties=””Excel 12.0;HDR=YES””;”””
  13.     End Select
  14.     ‘設定SQL查詢語句
  15.     strSQL = “請寫入SQL語句”
  16.     Conn.Open strConn    ‘開啟資料庫連結
  17.     Set Rst = Conn.Execute(strSQL)    ‘執行查詢,並將結果輸出到記錄集物件
  18.     With Sheet3
  19.         .Cells.Clear
  20.         For i = 0 To Rst.Fields.Count – 1    ‘填寫標題
  21.             .Cells(1, i 1) = Rst.Fields(i).Name
  22.         Next i
  23.         .Range(“A2”).CopyFromRecordset Rst
  24.         .Cells.EntireColumn.AutoFit  ‘自動調整列寬
  25.         .Cells.EntireColumn.AutoFit  ‘自動調整列寬
  26.     End With
  27.     Rst.Close    ‘關閉資料庫連線
  28.     Conn.Close
  29.     Set Conn = Nothing
  30.     Set Rst = Nothing
  31. End Sub

複製程式碼

介紹Microsoft Query的方法
1.從資料—自其他來源–來自Microsoft Query
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
2.選擇Excel型別
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
3.選擇工作簿(檔案)
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
4.選擇工作表
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
5.選擇需求資料的列
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
6.篩選條件,本例只接選下一步
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
7.排序方法,本教程也直接下一步
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
8.返回工作表或Microsoft Query編輯器。(本例選 Microsoft Query,以便以編寫SQL語句)
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
9.編寫SQL語句視窗。
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

通過Microsoft Query學習SQL 9.jpg (146.92 KB, 下載次數: 26)

下載附件  儲存到相簿

2013-10-9 13:29 上傳

通過以上二種方法,能知道SQL書寫到什麼地方。
接下來,可以開始學習SQL語句語法。


1.基於一張工作表的查詢語法                                        
格式:        select[DISTINCT] [TOP<數值> [PERCENT] <列標題>[[as]]<別名列標題>]                                                
            from <表或查詢1>[AS]<別名1>],<表或查詢2>[AS]<別名2>],                                                
           [where<篩選條件>][order by<排序項>[asc ▏desc]]                                
                                                        
說明:        1、<>表示必選項,”[]”表示可選項,”▏”表示多選一。                                
        2、        DISTINCT:消除取重複的行                        
        3、        TOP 數值:顯示前幾條記錄                        
        4、        TOP 數值 percent:顯示前面分之多少條記錄                        
        5、        <列標題>[[as]]<別名列標題>:給標題列重新命一個新名稱                        
        6、        where<篩選條件>:條件語句                        
        7、排序,如果要按兩個或兩個以上欄位,那麼欄位與欄位之間用豆號隔開,asc升序,為預設值,desc降序。        

顯示所有欄位的記錄(全部列的內容)
語句:
語句1:

  1. select * from [sheet1$] 

複製程式碼

語句2:

  1. select 編號,姓名,性別,出生年月,職稱,部門,基本工資,婚否,獎金 from [sheet1$] 

複製程式碼

這二個語句具有同樣的功能,出來的結果都是一樣的。
語句1中的*號是代表全部列,語句 2中,是寫上全部列欄位的名稱。
如果工作表沒有列標題,用F1,F2,F3,F4…..這樣代替
先看原表
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格                       
使用SQL語句出來的結果:
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

Microsoft Query方法:
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

將結果返回工作表:
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

返回工作表後樣式:
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

Microsoft Query方法返回工作表後修改SQL語句方法:
“資料”選項卡—屬性—連線屬性–定義—命令文字
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

補充說明vba啟用巨集方法:
1.調出開“發工具欄”
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
2.在自定義功能區,勾選“開發工具欄”
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
3.設定巨集安全性
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
4.啟用巨集
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

5.進入VBA介面2
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

6.執行子過程,把游標放於程式碼中間任意行,按F5執行,或綠色小三角執行。
按F8是逐步執行,是學VBA中重要的除錯技術。
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 




本帖最後由 335081548 於 2013-10-9 20:52 編輯

使用SQL語句獲取多列資料,及別名的使用
在Excel中所使用的SQL語句具體語法結構如下:

  1. SELECT 列欄位名1,列欄位名2,列欄位名3 FROM [工作表名稱$]

複製程式碼

在本例 中SELECT子句後列出查詢的欄位即可查詢出結果,如輸入以下的SQL語句:

  1. SELECT 姓名,  性別,  職稱,部門 FROM [Sheet1$]

複製程式碼

從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
使用SQL語句後結果如下圖:
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

使用別名
1.欄位別名

  1. SELECT 欄位名1 AS 重新命名欄位1,欄位名2 AS 重新命名欄位2 FROM [Sheet1$]

複製程式碼

本例中SQL語句如下:

  1. SELECT 姓名 AS Name FROM [Sheet1$]

複製程式碼

2.工作表別名

  1. SELECT 工作表別名.* FROM [工作表名$] 工作表別名

複製程式碼

本例中SQL語句如下:

  1. SELECT A.*  FROM [Sheet1$] A

複製程式碼

所有欄位列表
可能到這裡就有人會問,你這不是多此一舉嗎?
2樓不是介紹了

  1. SELECT * FROM [Sheet1$]

複製程式碼

就行了嗎?還整這麼複雜幹什麼?
其實(原來)SQL的程式碼是應該這樣寫的,上面的是簡寫。或者說只有一個表的情況,以後介紹多表的情況會用到表的別名。所以就在此一次把別名介紹了。

  1. SELECT [工作表名].欄位名 FROM [工作表名]

複製程式碼

實際中SQL語句應該如下:

  1. SELCT [Sheet1$].*  FROM [Sheet1$]

複製程式碼

這樣的使用環境中,使用表名,就太大縮短SQL語句的字元和書寫方便。

注意事項:
1.在SQL語句中SQL語句英文不區分大小寫,但標點符號必須是英文半形狀態下輸入,欄位名也必須跟原來的一樣。
2.使用SQL語句的時候,必須避免列欄位中使用下面的特殊字元:
空格、雙引號(”)、撇(’)、數字標記(#)、百分號(%)、大於號(>)、小於號(<)、歎號(!)、句號(.)、方括號([或])、星號(*)、美元符號($)、分號(;)、脫字號(^)、圓括號((或))、加號( )、反斜槓(\或/)。
如果在源資料表的列欄位使用了這些特殊字元,那麼在使用SQL語句列出各欄位的資料時,就會發生錯誤。為了規範使用SQL語句,在對資料來源欄位命名時,儘量避免使用這些特殊字元。

本帖最後由 335081548 於 2013-10-10 21:52 編輯

SQL語句中的關鍵詞DISTINCT的使用,功能是去重複值只保留一條記錄。語法結構為:

  1. SELECT DISTINCT 要去重複值的欄位1,要去重複值的欄位2 FROM [工作表名$]

複製程式碼

例項中使用語法

  1. Select Distinct 使用者姓名,使用者卡號 From [sheet1$]

複製程式碼

資料原圖:


執行SQL語句後的效果圖:
按條件篩選 使用SQL關鍵詞 WHERE查詢中的條件指定要滿足什麼標準資訊,去掉不滿足條件的資料(刪除使用者不要的資料)。
WHERE語句中可以有多個條件,條件之間可以用操作符AND 或者OR進行連線。
WHERE語句的語法結構如下:

  1. SELECT 列欄位名稱 FROM [表名稱$] WHERE 列欄位名 運算子 值

複製程式碼


運算子包含大於、小於、等於、不等於、大於或等於、小於或等於、IN、 BETWEEN、AND等 。應用例項進行說明:
提取消費金額等於100的資料。(是不是比函式公式和VBA簡單,語句也容易懂。)
本例使用的SQL語句如下:

  1. SELECT * FROM [Sheet1$] where 消費金額=100

複製程式碼

從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
ORDER BY的語句使用對於ORDER BY 語句而言,預設值是升序排列,通常是不指定它。但升序的關鍵詞為ASC,降序為DESC
語法結構如下:

  1. SELECT 列欄位名 FROM [工作表名稱$] ORDER BY 指定列欄位名 升序(降序)

複製程式碼

下面用具體的例項進行說明
按成績進行升序排序本例中SQL語句如下: 

  1. Select * FROM [Sheet1$] ORDER BY 成績 ASC

複製程式碼

執行後效果如圖
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

按成績進行降序排序
本例中SQL語句如下:

  1. Select * FROM [Sheet1$] ORDER BY 成績 DESC

複製程式碼

執行後效果如圖:
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 


二列資料按降序排序:SQL語句如下:

  1. Select * FROM [Sheet1$] ORDER BY 英語 DESC,數學 DESC

複製程式碼

效果如圖:
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
TOP在SQL語句中的作用
TOP的應用按順序提取記錄

  1. SELECT TOP 3 * FROM [工作表名$]

複製程式碼

提取前面3條記錄.改變3為想要提取的數字,就可以實現想得到的記錄
本例中使用的SQL語句如下:

  1. SELECT TOP 3 * FROM [Sheet1$]

複製程式碼

結果如圖:
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

按欄位名排序提取前幾名的語法
升序

  1. SELECT TOP 3 欄位名 FROM [工作表名] ORDER BY 欄位名

複製程式碼

降序

  1. SELECT TOP 3 欄位名 FROM [工作表名] ORDER BY 欄位名 DESC

複製程式碼

以下用例項進行說明
按成績降序排列提取前三名:
SQL語句如下:

  1. Select top 3 * FROM [Sheet1$A1:C17]  ORDER BY 成績 DESC

複製程式碼

結果如圖所示:
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
升序:

  1. Select top 3 * FROM [Sheet1$A1:C17]  ORDER BY 成績

複製程式碼

結果如圖:
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
補充說明:
SQL的VBA語句如下:

  1. Sub Test4()
  2.     Dim Conn As Object, Rst As Object
  3.     Dim strConn As String, strSQL As String
  4.     Dim i As Integer, PathStr As String
  5.     Set Conn = CreateObject(“ADODB.Connection”)
  6.     Set Rst = CreateObject(“ADODB.Recordset”)
  7.     PathStr = ThisWorkbook.FullName   ‘設定工作簿的完整路徑和名稱
  8.     Select Case Application.Version * 1    ‘設定連線字串,根據版本建立連線
  9.     Case Is <= 11
  10.         strConn = “Provider=Microsoft.Jet.Oledb.4.0;Extended Properties=excel 8.0;Data source=” & PathStr
  11.     Case Is >= 12
  12.         strConn = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & PathStr & “;Extended Properties=””Excel 12.0;HDR=YES””;”””
  13.     End Select
  14.     ‘設定SQL查詢語句
  15.     ‘strSQL = “Select top 3 * FROM [Sheet1$] ” ‘按順序提取資料
  16.     strSQL = “Select top 3 * FROM [Sheet1$A1:C17]  ORDER BY 成績 DESC”
  17.     Conn.Open strConn    ‘開啟資料庫連結
  18.     Set Rst = Conn.Execute(strSQL)    ‘執行查詢,並將結果輸出到記錄集物件
  19.     With Sheet1.Range(“E:G”)
  20.         .Cells.Clear
  21.         For i = 0 To Rst.Fields.Count – 1    ‘填寫標題
  22.             .Cells(1, i 1) = Rst.Fields(i).Name
  23.         Next i
  24.         .Range(“A2”).CopyFromRecordset Rst
  25.         .Cells.EntireColumn.AutoFit  ‘自動調整列寬
  26.         .Cells.EntireColumn.AutoFit  ‘自動調整列寬
  27.     End With
  28.     Rst.Close    ‘關閉資料庫連線
  29.     Conn.Close
  30.     Set Conn = Nothing
  31.     Set Rst = Nothing
  32. End Sub

複製程式碼


其中 [Sheet1$A1:C17]這個表示工作表名Sheet1的工作表A1:C17的這個單元格區域,這個上面介紹的區別就是突出結果在同一個工作表上。所以加上了指定的單元格區域為資料。可以根據自己的實際情況,來改變.
不在同一張表上顯示結果,而且資料來源規範,就可以直接用[工作表名$]。或者書寫的時候寫上列名不用星號(”*”)
本例也可以用如下的語句達到同樣的效果:

  1. Select top 3 姓名,班級,成績 FROM [Sheet1$]  ORDER BY 成績 DESC

複製程式碼

TOP 與 PERCENT 組合在SQL語句中的使用
功能:是按百分比提取
用例項進行說明
按成績降序排列提取前30%
SQL語句如下:

  1. Select TOP 30 PERCENT * FROM [Sheet1$A1:C17]  ORDER BY 成績 DESC

複製程式碼

結果如圖所示:
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 
注:
資料來源16條資料,16*0.3=4.8 四捨五入等於 5 資料提取是整數所以結果是提取5條資料。
SQL語句中的一些簡單計算函式:
COUNT函式,統計函式
語法如下:

  1. SELECT COUNT(列欄位) AS 別名1 FROM [工作表名$]

複製程式碼

本示例SQL語句如下:

  1. Select count(姓名) as 人數  FROM [Sheet1$]

複製程式碼

按姓名統計人數,結果如圖:

從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

SUM函式,求和函式。SQL語句語法如下:

  1. Select Sum(列欄位) as 別名 FROM [工作表名$]

複製程式碼

求總分,本例 SQL語句如下:

  1. Select sum(成績) as 總分  FROM [Sheet1$]

複製程式碼

結果如圖:
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

AVG函式,求平均。SQL語句語法如下:

  1. Select AVG(列欄位) as 別名 FROM [工作表名$]

複製程式碼

本例中求平均分,SQL語句如下:

  1. Select AVG(成績) as 平均分  FROM [Sheet1$]

複製程式碼

結果如圖:
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

現對上面的語句來個小變通,求成績大於60的人數、總分、平均分
SQL語句如下:

  1. Select AVG(成績) as 大於60的平均分,SUM(成績) as 大於60的總分,COUNT(成績) as 大於60的人數  FROM [Sheet1$] WHERE 成績>60

複製程式碼

結果如圖所示:
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

MAX函式,求最大值。SQL語句語法如下:

  1. Select MAX(列欄位) as 別名 FROM [工作表名$]

複製程式碼

本例中SQL語句如下:

  1. Select MAX(成績) as 最高分  FROM [Sheet1$]

複製程式碼

結果如圖所示:
從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格 

MIN,求最小值函式,SQL語句語法如下:

  1. Select MIN(列欄位) as 別名 FROM [工作表名$]

複製程式碼

本例中使用的SQL語句如下:

  1. Select MIN(成績) as 最低分  FROM [Sheet1$]

複製程式碼

結果如圖所示:

從零開始學習SQL(圖文教程) - CMMI - CMMI的部落格