VB2005序列 資料庫處理指令

Transcription

1 5.1 VB200X 序列資料庫物件關聯圖 引入資料處理類別 建立連線 資料處理 執行 SQL 指令 讀取資料表紀錄 使用 ADODB 類別與方法讀取紀錄 表列紀錄 DataGridView 常用屬性 動態設定 DataGridView 表格及加入資料 取出 DataGridView 表格資料 排序 過濾紀錄 關聯查詢 逐筆處理資料 動態產生記憶體資料表 匯出檔案 資料表紀錄匯出為 XML 格式 常用 SQL 指令格式 選取查詢 基本敘述 篩選資料 資料排序 在 VB 環境執行 SQL 查詢之三種方法 動作查詢 更新查詢 刪除查詢 新增查詢 產生資料表查詢 其他 SQL 指令 資料統計 顯示唯一資料 選取前數筆資料 資料小計處理 結合兩個資料表 結合 VB 函數 21 1

2 本章介紹以 VB 程式語言來開發 Access 資料庫應用系統之技巧, 此外也將詳細解說各種資料處理物件之相關屬性和方法 5.1 VB200X 序列資料庫物件關聯圖 ADODB DataReader Windows 控制項 TextBox Database ODBC Driver SQL Command Connection Command DataAdapter DataSet DataTable DataView Windows 控制項 TextBox BindingSource BindingNavigator CommandBuilder DataGridView 圖 5.1 資料庫物件關聯圖上圖說明資料庫處理使用到的類別及相關物件之關係, 引用物件時, 須按照上圖連線之箭頭方向依序引用 類別 :System.Data.OleDB 物件 : OleDBConnection: 資料庫連接器, 用途為與伺服端資料庫之連線 OleDBCommand:SQL 指令處理器, 將主控端 SQL 指令送往伺服端處理 OleDBDataReader: 前導型資料讀取器, 逐筆讀取開啟之資料表紀錄 OleDBDataAdapter: 資料表橋接器, 透過開啟之連線, 建立資料表在伺服端與主控端之橋接機制, 並將取得的資料表加入記憶體資料庫 OleDBCommandBuilder: 資料表更新處理器, 建立橋接資料表資料更新機制, 諸如新增 修改及刪除等作業 ADODB: 使用 ADO 類別與方法處理紀錄集 通用型資料處理物件 : DataSet: 記憶體資料庫, 用於存放以橋接器連接之資料表 DataTable: 記憶體資料表, 將記憶體資料表轉成可前後移動紀錄指標之資料表, 此資料表可用於新增 修改及刪除等用途 DataView: 視界型資料表, 將 DataTable 轉成視界, 視界型資料表可排 2

3 序及設定過濾條件, 並與 DataGridView 控制項結合, 使得資料瀏 覽更加方便 BindingSource: 資料來源, 將記憶體資料庫轉成可與其它控制項結合之資料 來源 BindingNavigator: 紀錄導覽器, 建立來源資料之紀錄導覽按鈕 OLEDB Driver: 作為應用系統在 Windows 平台與 MySQL 間之溝通驅動程式 5.2 引入資料處理類別 在表單程序區前端加入下述指令, 引入資料處理類別後, 才可以在表單程序 內叫用相關物件及方法 Imports System.Data.OleDB Imports System Imports Microsoft.VisualBasic 5.3 建立連線 可在模組區建立公用連線方法, 開啟表單時, 可叫用此方法來開啟連線 本書將其命名為 opencon, 程式內容如下 : Public Sub opencon(byval WDB As String) Try Dim opvalue As Long Dim str As String opvalue = , 說明 : 通用型選項參數值 str = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & WDB //WDB 表欲開啟之資料庫全名 ( 含路徑名 ) accon= New OleDBConnection(str) If Not accon.state = ConnectionState.Open Then accon.open() Catch ex As Exception MsgBox(ex.Message) End Try End Sub 5.4 資料處理 執行 SQL 指令使用 OleDBCommand 物件來執行 SQL 指令, 此物件可與伺服端紀錄集連線, 但是不取出結果 accmd = New OleDBCommand(strSQL, accon) accmd.executenonquery() 3

4 相關語法 建立處理物件 :New OleDBCommand(strSQL, accon) 引數一 :SQL 指令, 範例 : Select * From actable; 引數二 : 連線物件說明 : 以下 actable 代表要處理之資料表名稱執行 SQL 指令 :SQL 指令物件.ExecuteNonQuery() 讀取資料表紀錄 使用 OleDBDataReader 物件來讀取紀錄 建立 OleDBDataReader 物件 Dim tblreader As OleDBDataReader 建立 SQL 指令物件 strsql = "Select * From actable;" accmd = New OleDBCommand(strSQL, accon) 建立紀錄讀取物件 tblreader = accmd.executereader() 逐筆讀取紀錄 While tblreader.read For i = 0 To tblreader.fieldcount -1 vl = tblreader.item(i).tostring, 說明 : 取出第 i 欄資料 Nest i End While 關閉物件 tblreader.close() 說明 :OleDBDataReade.Read 方法只能往前讀取紀錄 取出之 vl 可轉存至其它控制項或與記憶體變數進行運算, 例如 : txtname.text = tblreader.item(1).tostring OleDBDataReader 可使用 HasRows 屬性來判斷取得的資料表有無紀錄, 若包含紀錄則傳回 True 使用 ADODB 類別與方法讀取紀錄 ADODB 類別與方法可使用程式碼來處理資料表之新增 修改 刪除及轉檔工作 下述程式示範連線 開啟紀錄集, 以及逐筆處理紀錄 宣告連線物件 Dim con As New ADODB.Connection 4

5 宣告紀錄集物件 Dim rs As New ADODB.Recordset 定義連線屬性 Dim acsqlstr As String acsqlstr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & WDB 設定連線屬性 accon.connectionstring = acsqlstr 開啟連線 accon.open() 設定 SQL 指令 Dim strsql As String strsql = "Select * from actable" 開啟紀錄集 rs.open(strsql, accon, 2, 4) 逐筆讀取全部紀錄 Do While Not rs.eof vl = rs(0).value, 說明 : 取出欄位資料 rs.movenext() Loop rs.close 說明 : ADODB 類別事前須先引入專案才可使用, 引入步驟如下 : 啟動表單在設計模式 下拉專案功能表, 然後點擊 加入參考 功能, 當開啟 加入參考 對話表單後, 點選 COM 及捲動清單至 MicroSoft Active Data Objects 2.8 Library, 按下 確定, 即可加入此類別 RecordSet 相關方法與屬性使用說明 : Open 方法傳入引數說明引數一 :SQL 指令, 引數二 : 開啟之 OLEDB 連線, 引數三 : 紀錄集開啟模式, 代表值如下 :, 0: adopenforwardonly, 1: adopenkeyset, 2: adopendynamic, 3: adopenstatic 5

6 引數四 : 紀錄集鎖定模式, 代表值如下 :, 1: adlockreadonly, 2: adlockpessimistic, 3: adlockoptimistic, 4: adlockbatchoptimistic EOF: 判斷是否已至檔案末端, BOF: 判斷是否已至檔案頂端, Close: 關閉紀錄集, AddNew: 新增一筆空紀錄, Delete: 刪除紀錄, Update: 更新紀錄, 使用時機為修改紀錄或新增紀錄後 ;Delete 方法不須搭配 Update 方法 取值 : 可使用下列方法 : 傳入欄位註標 ( 從 0 開始起算 ):rs(i).value 傳入欄位名稱 : rs( 欄名 ).Value 給值 : 可使用下列方法 rs(i).value = 值 rs( 欄名 ).Value = 值 移動紀錄指標 : 首筆 :rs.movefirst 次筆 :rs.movenext 前筆 :rs.moveprevious 次筆 :rs.movelast 過濾紀錄 :rs.filter = 欄名 = 欄值 [And Or.] 釋放過濾條件 :rs.filter = 表列紀錄 下述範例是以視界形式顯示紀錄, 可搭配 DaGridView 來展示多筆紀錄 Dim ds As New DataSet Dim da As New OleDBDataAdapter(SQL, accon) Dim daview As DataView Dim tbl As DataTable da.fill(ds, "qrytable") 說明 : 將連線資料表加入記憶體資料庫, 並將資廖表取明為 qrytable tbl = ds.tables("qrytable") 說明 : 將記憶體資料表轉為 DataTable 物件 6

7 daview = tbl.defaultview 說明 : 將 DataTable 物件轉為視界型資料表 DataGridView1.DataSource = daview1 說明 : 將視界型資料表連結至表格物件 語法說明 OleDBDataAdapter( 資料表橋接器 ) 引數說明 : 引數一 :SQL 指令 引數二 : 連線物件 OleDBDataAdapter 須使用 Fill 方法將所取得的伺服端資料表加入記憶體資料庫, 語法如下 : da.fill(ds, "qrytable") 記憶體資料庫可使用下述指令轉成記憶體資料表 : tbl = ds.tables("qrytable") 記憶體資料表可使用下述指令取得相關資料 : 取得紀錄筆數 : rn = tbl.rows.count 取得欄位個數 : fln = tbl.columns.count 取得欄位名稱 : na = tbl.columns(i).columnname 取得第 i 筆第 j 欄值 :vl = tbl.rows(i).item(j) 若使用 ADODB 之 RecordSet 取得來源資料, 可使用下述指令加入記憶體資料庫 da.fill(ds, myrs, "qrytable"), 說明 :myrs 為 ADODB 之 RecordSet 取出資料錄 下述程式示範如何取出資料表紀錄 Dim drow As DataRow drow = tbl.rows(n) 說明 :n 表第幾筆紀錄, 從 0 起算 Me.BK_BLNO.Text = drow("bk_blno") 說明 : 取值並存入文字方塊,dRow 引數可使用註標變數或使用欄名 vl = drow.item(0).tostring() 說明 : 取出欄值並存入記憶體變數,0 表第一欄 取出視界資料表紀錄 視界資料表可使用下述指令取得相關資料 : daview(n).item(j).tostring 說明 : 傳入欄位註標,n 為紀錄序,j 為欄序 daview(n).item( 欄名 ).ToString 7

8 說明 : 傳入欄位名稱 取得欄位資料型態 下述程式示範如何取出資料表欄位型態, 以 SQL 指令新增或修改資料時, 必 須依欄位型態來組合 SQL datype = tbl.rows(0).item(i).gettype.tostring 傳回結果 : System.Char/ System.String: 字元型 System.Boolean: 布林型 System.Byte/ System.SByte: 位元組型 System.DateTime: 日期時間 System.Decimal/ System.Double/ System.Single: 實數型 System.Int16/ System.Int32/ System.Int64: 整數型 System.Uint16/ System.Uint32/ System.Uint64: 無號整數型 組合 SQL 時, 日期及字元型欄位必須在取得的控制項資料前後加上引號, 製 作過濾條件時亦同 範例 :strfilter = "BK_BLNO = " & txtno.text & " " DataGridView 常用屬性 下述程式示範 DataGridView 控制項之用法 DataGridView1.DataSource = Nothing, 說明 : 釋放原資料來源 DataGridView1.Refresh() DataGridView1.DataSource = daview, 說明 : 設定連結資料來源 語法說明 ColumnIndex: 取得目前游標位置欄註標 curci = DataGridView1.CurrentCell.ColumnIndex CurrentRow.Index: 取得目前游標位置列註標 curri = DataGridView1.CurrentRow.Index Cells(ci).Value: 取得目前游標位置欄值 VL =DataGridView1.CurrentRow.Cells(ci).Value Name: 取得目前游標位置之欄名 NA = DataGridView1.Columns(ci).Name Frozen: 凍結欄位 DataGridView1.Columns(1).Frozen = True 8

9 DefaultCellStyle.Format: 格式化資料 DataGridView1.Columns(i).DefaultCellStyle.Format = "yyyy/m/d H:mm:ss" HeaderText: 設定欄位抬頭 DataGridView1.Columns(i).HeaderText = 欄位抬頭 ValueType.: 取得欄位型態 ftp = DataGridView1.Columns(i).ValueType.ToString DefaultCellStyle.Alignment: 設定欄位對齊方式 DataGridView1.Columns(i).DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight 應用範例 DataGridView 控制項應用範例如下圖 圖 5.2 DataGridView 控制項應用範例 動態設定 DataGridView 表格及加入資料 下述程式示範以動態方式 ( 在執行階段 ) 將資料加入 DataGridView 控制項, 請預先在表單內加入 DataGridView 控制項並取名為 datagridview1 Private Sub CreateDataGridView() datagridview1.columncount = 4, 說明 : 設定欄數 datagridview1.columnheadersvisible = True, 說明 : 設為可見 設定欄位抬頭樣式. Dim columnheaderstyle As New DataGridViewCellStyle() columnheaderstyle.backcolor = Color.Beige columnheaderstyle.font = New Font("Verdana", 10, FontStyle.Bold) datagridview1.columnheadersdefaultcellstyle = columnheaderstyle 設定欄名. datagridview1.columns(0).name = "Recipe datagridview1.columns(1).name = "Category" datagridview1.columns(2).name = "Main Ingredients" 9

10 datagridview1.columns(3).name = "Rating" 加入紀錄. Dim row1() As String = {"Meatloaf", "Main Dish", "ground beef", "**"} Dim row2() As String = _ {"Key Lime Pie", "Dessert", "lime juice, evaporated milk", "****"} Dim row3() As String = {"Orange-Salsa Pork Chops", "Main Dish", _ "pork chops, salsa, orange juice", "****"} Dim row4() As String = {"Black Bean and Rice Salad", "Salad", _ "black beans, brown rice", "****"} Dim rows() As Object = {row1, row2, row3, row4 } Dim rowarray As String() For Each rowarray In rows datagridview1.rows.add(rowarray) Next rowarray End Sub 取出 DataGridView 表格資料以下程式為點擊 DataGridView1 控制項時, 取出當筆紀錄內含鍵值欄資料 Private Sub DataGridView1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataGridView1.Click Try Dim fi As Integer If DataGridView1.CurrentRow.Index >= rn Then Exit Sub 說明 :rn, 事先取得之紀錄總數 curci = DataGridView1.CurrentCell.ColumnIndex curri = Me.DataGridView1.CurrentRow.Index If curci = 0 Then For fi = 0 To UBound(tbl.Columns.Count-1) If tbl.columns(fi).name = keyfield1 Then 說明 : 取出第一鍵值 KVL1 = Me.DataGridView1.CurrentRow.Cells(fi).Value End If If tbl.columns(fi).name = keyfield2 Then 說明 : 取出第二鍵值 ( 若有兩個主鍵時 ) KVL2 = Me.DataGridView1.CurrentRow.Cells(fi).Value End If Next 10

11 End If Catch ex As Exception MsgBox(ex.Message) End Try End Sub 此範例程式可應用於全螢幕編修紀錄, 修改紀錄前, 先點擊 DataGridView 鍵值欄控制項來取得鍵值 修改紀錄後, 再搭配下列方法來組合 SQL 指令, 然後執行該指令以修改紀錄 strsql = "Update 資料表名 Set " For fi = 0 To UBound(tbl.Columns.Count-1) strsql= strsql & tbl.columns(fi).name & "= " & DataGridView1.CurrentRow.Cells(fi).Value & "," Next strsql = Mid(strSQL, 1, Len(strSQL)-1) & " Where 過濾條件 ; " accmd = New OleDBCommand(strSQL, accon) accmd.executenonquery() 排序 可搭配視界資料表將紀錄排序 daview1.sort = " 欄名 ASC DESC [, 第二排序欄位 ] " 過濾紀錄 可搭配視界資料表來過濾紀錄 daview.rowfilter = " 欄名 = 欄值 [And Or.] " 關聯查詢 進階應用 - 以關聯方式查詢兩個資料表, 當主資料表紀錄變更時, 可同步顯示子資料表之關聯紀錄 處理方法如下 : 主表以逐筆方式顯示, 當紀錄指標移動時, 同步取出鍵值並置入表單對應之文字方塊內 設定文字方塊資料變更事件程序 (TextChanged 事件 ), 當資料變更時, 將子資料表之 DataView 重設為 daview.rowfilter = " 過濾字串 " 將連結子資料表之 DataGridView.DataSource 重新設為 daview 逐筆處理資料 搭配紀錄導覽器及控制項 下述程式示範以紀錄導覽器來移動紀錄指標, 須在表單內加入紀錄導 11

12 覽器控制項, 並取名為 BindingNavigator1 宣告物件 Dim bindsrc As BindingSource Dim bindadp As OleDBDataAdapter Dim bindtbl As DataTable Dim ds As DataSet Dim builder As OleDBCommandBuilder Dim daview1 As DataView Dim tbl As DataTable, mkey(0) as DataColumn ds = New DataSet("ds") 定義 SQL strsql = 以 Select 前導之 SQL 指令 mkey(0) = 主鍵 建立資料物件 bindadp = New OleDBDataAdapter(strSQL, accon) 說明 : 建立資料庫橋接器 bindsrc = New BindingSource 說明 : 建立表單連結資料來源物件 bindadp.fill(ds, 給定資料表名 ) 說明 : 資料表填入資料庫橋接器 bindtbl = ds.tables( 給定資料表名 ) 說明 : 建立連結資料表物件 bindsrc.datasource = ds 說明 : 建立資料來源物件 bindsrc.datamember = 給定資料表名 說明 : 設定表單連結資料來源 bindtbl.primarykey = mkey 說明 : 設定連結資料表主鍵 BindingNavigator1.BindingSource = bindsrc 說明 : 設定紀錄導覽器資料來源 tbl = ds.tables( 給定資料表名 ) fln = tbl.columns.count 說明 : 取得連結資料表之欄位數 定義輸入法 Dim ime1, ime2, ime3 As ImeMode ime1 = Windows.Forms.ImeMode.Alpha 說明 : 英數 12

13 ime2 = Windows.Forms.ImeMode.Off 說明 : 關閉輸入法 ime3 = Windows.Forms.ImeMode.On 說明 : 中文半形 設定控制項屬性並連結資料來源 控制項.ImeMode = ime1 說明 : 設定控制項輸入法 控制項.Name = 連結資料表欄名 說明 : 設定控制項名稱 控制項名.DataBindings.Add(New Binding("Text", bindsrc, 欄名, True) 說明 : 連結控制項來源資料, 若為日期型欄位, 引數 "Text" 須改為 "Value" 紀錄導覽器及控制項應用範例 應用範例如下圖 紀錄導覽器 文字方塊 DataGridView: 關聯查詢 圖 5.3 紀錄導覽器控制項 搭配 DataTable 下述程式示範以 DataTable 物件來增修紀錄 宣告及建立物件 ds = New DataSet("ds") strsql : Select 前導之 SQL 指令 bindadp = New OleDBDataAdapter(strSQL, accon) 說明 : 建立資料庫連接器 bindadp.fill(ds, 給定資料表名 ) 說明 : 資料表填入資料庫連接器 bindtbl = ds.tables( 給定資料表名 ) 說明 : 建立連結資料表物件 rn = bindtbl.rows.count 說明 : 取得紀錄筆數 13

14 fn = bindtbl.columns.count 逐筆處理紀錄 For i =0 To rn -1 說明 : 逐筆 For j = 0 To fn -1 說明 : 逐欄 vl = bindtbl.rows(i).item(j).tostring 說明 : 取得欄位資料 Next j Next i 新增 修改及刪除紀錄 以 DataTable 物件來增修紀錄時, 須搭配 OleDBCommandBuilder 物件 builder1 = New OleDBCommandBuilder(da1) bindsrc.datasource = dst bindsrc.datamember = wktbl tbl = dst.tables(wktbl) daview = New DataView(tbl) 過濾紀錄 daview.rowfilter = "" 說明 : 釋放過濾條件 daview.rowfilter = keyvl 說明 : 過濾紀錄 刪除紀錄指令 daview.delete 說明 : 刪除紀錄前, 須使用 RowFilter 過濾指定鍵值之紀錄 新增紀錄指令 Dim row As DataRowView = daview.addnew 說明 : 新增紀錄 row.beginedit() 說明 : 開啟編輯功能 For j = 0 To fn -1 row(j) = 資料 Next j row.endedit() 說明 : 結束編輯功能 14

15 修改紀錄指令 Dim row As DataRowView For Each row In daview row.beginedit() 說明 : 開啟編輯功能 vl = row(fi) 說明 : 取值,fi 表欄位序號 row(fi) = vl 說明 : 給值 row.endedit() 說明 : 結束編輯功能 Next 將修改紀錄存回資料庫 Me.Validate() bindsrc.endedit() bindadp.update(tbl) 設定控制項連結資料來源欄位 設定控制項連結資料來源欄位之語法如下 : 控制項.DataBindings.Add(New Binding("Text", bindsrc, 欄名, True)) 批次更新連結資料表紀錄 開啟表單時, 須先建立紀錄更新物件 OleDBCommandBuilder, 指令如下 : builder = New OleDBCommandBuilder(bindADP) 然後使用指令按鈕來執行下述指令, 也可以將下述指令放在 FormClosed 事件內 Me.Validate() bindsrc.endedit() bindadp.update(bindtbl) 動態產生記憶體資料表 下述程式示範以動態方式產生記憶體資料表 加入資料及顯示 Dim statbl As DataTable = New DataTable("StaTable") Dim daview As DataView Dim column As DataColumn Dim row As DataRow 15

16 定義欄位屬性 column = New DataColumn() column.datatype = System.Type.GetType("System.String") column.columnname = "Item" 'System.Single 將欄位加入資料表 statbl.columns.add(column) column = New DataColumn() column.datatype = System.Type.GetType("System.Single") column.columnname = "Value" statbl.columns.add(column) 新增紀錄 row = statbl.newrow() row(0) = " 筆數 " row(1) = st.recno 紀錄加入資料表 statbl.rows.add(row) 定義第二筆紀錄 row = statbl.newrow() row(0) = " 合計 " row(1) = st.sum 紀錄加入資料表 statbl.rows.add(row) 連結資料表至 DataGridView daview = statbl.defaultview DataGridView1.DataSource = daview 5.5 匯出檔案 資料表紀錄匯出為 XML 格式 下述程式用於匯出資料表紀錄為 XML 格式 宣告及建立物件 Dim ds As New DataSet, datable As DataTable da = New OleDBDataAdapter(strSQL, accon) da.fill(ds, 資料表名 ) datable = ds.tables( 資料表名 L) 16

17 Dim mode As XmlWriteMode = XmlWriteMode.WriteSchema ds = datable.dataset 匯出紀錄 If fld = False Then '// 不寫入欄位結構 ds.writexml( 存檔檔名 ) Else ds.writexml( 存檔檔名, mode) '// 寫入欄位結構 End If 5.6 常用 SQL 指令格式 選取查詢 基本敘述語法 :SELECT [ 欄位串列 ] FROM [ 資料表名 ]; 範例 : SELECT 顧客名冊. 顧客 ID, 顧客名冊. 顧客名稱 FROM 顧客名冊 ; SELECT 顧客 ID, 顧客名稱 FROM 顧客名冊 ; SELECT * FROM 顧客名冊 ; 篩選資料語法 :SELECT [ 欄位串列 ] FROM [ 資料表名 ] WHERE [ 篩選條件 ]; 範例 : SELECT 顧客名冊. 顧客 ID, 顧客名冊. 顧客名稱 FROM 顧客名冊 WHERE 縣市 = 台北市 ; [WHERE 單價 >= 1000;] [WHERE 建檔日期 = #99/03/01#;] [WHERE 顧客名 LIKE % 股份有限 % ;] [WHERE 建檔日期 BETWEEN #99/03/01# AND #99/12/31#;] 資料排序語法 :SELECT [ 欄位串列 ] FROM [ 資料表名 ] ORDER BY [ 排序準則 ]; 範例 : SELECT 顧客名冊. 顧客 ID, 顧客名冊. 顧客名稱 FROM 顧客名冊 17

18 ORDER BY 顧客名 ASC [DESC]; [ORDER BY 顧客名 ASC, 建檔日期 DESC;] 在 VB 環境執行 SQL 查詢之三種方法 使用 Recordset 物件之 Open 方法 使用 Connection 物件的 Execute 方法 使用 Command 物件的 Execute 方法 範例 : Public Sub SQLSelect() Dim cn As ADODB.Connection Dim rs As ADODB.Recordset Dim mysql As String 'SQL 敘述 '// 連接 Set cn = CurrentProject.Connection '// 篩選 mysql = "SELECT * FROM 顧客名冊 " _ &" WHERE 縣市 = ' 台北市 ';" Set rs = New ADODB.Recordset rs.open mysql, cn, adopenkeyset, adlockreadonly '// 確認 Do Until rs.eof Debug.Print rs! 顧客 ID, rs! 顧客名, rs! 縣市 rs.movenext Loop '// 結束 rs.close: Set rs = Nothing cn.close: Set cn = Nothing End Sub 動作查詢 只可使用 Connection 或 Command 物件 更新查詢 語法 :UPDATE [ 資料表名 ] SET [ 欄位名稱 ] = [ 新值 ] 範例 :UPDATE 顧客名冊 SET 顧客名 = 顧客名 & 敬啟, 電話號碼 = TEL & 電話號碼 ; 刪除查詢 18

19 語法 :DELETE [ 資料表名.*] FROM [ 資料表名 ] 範例 :DELETE * FROM 顧客名冊 WHERE 建檔日期 = #99/03/01#; 新增查詢 新增紀錄語法 :INSERT INTO [ 資料表名 ] ( 欄位串列名稱 ) VALUE( 待填入新值串列 ) 範例 :INSERT INTO 員工名冊 ( 員工 ID, 姓名, 起聘日期 ) VALUE (901, 黃大千, #2001/3/1#); 自其他資料表取得資料語法 :INSERT INTO [ 目的資料表名 ] ( 欄位串列名稱 ) SELECT [ 欄位串列名稱 ] FROM [ 來源資料表名 ] 範例 :INSERT INTO [ 商品一覽表 ] ( 商品 ID, 商品名稱, 單價 ) SELECT 商品 ID, 商品名稱, 單價 FROM 新商品 ; 產生資料表查詢語法 :SELECT [ 欄位串列名稱 ] INTO [ 新資料表名 ] FROM [ 來源資料表名 ] WHERE [ 篩選準則 ] 範例 :SELECT * INTO 北區顧客 FROM 顧客名冊 WHERE 區位 = 北區 ; 其他 SQL 指令 資料統計常用指令 : AVG: 傳回某欄位所有值之平均 COUNT: 傳回某欄位個數 ( 計算資料筆數 ) SUM: 傳回某欄位所有值之總和 MAX: 傳回某欄位所有值之最大值 MIN: 傳回某欄位所有值之最小值 範例 :SELECT COUNT(Units) AS UnitCount, AVG(Units) AS UnitAvg, SUM(Units) AS UnitSum, MIN (Units) AS UnitMin, MAX(Units) AS UnitMax, FROM BookSales; 19

20 顯示唯一資料 單一欄位語法 :SELECT DISTINCT AUID FROM Titles; 複合欄位語法 :SELECT DISTINCT Title, Units FROM Titles; 部分欄位不同之紀錄語法 :SELECT DISTINCTROW * FROM Titles; 選取前數筆資料 前 n 筆記錄語法 :SELECT TOP 5 * FROM Titles; 前 n% 記錄語法 :SELECT TOP 5 PERCENT * FROM Titles; 資料小計處理 語法 :SELECT SUM(Units) AS UnitSum FROM BookSales GROUP BY Title; 小計配合篩選條件語法 :SELECT SUM(Units) AS UnitSum FROM BookSales GROUP BY Title HAVING SUM(UNits) > 100; 結合兩個資料表 INNER JOIN 兩個資料表結合欄位資料必須完全吻合語法 :SELECT [ 資料表名 ].[ 欄名 ], FROM [ 表一 ] INNER JOIN [ 表二 ] ON [ 表一 ].[ 鍵值欄一 ] = [ 表二 ].[ 鍵值欄二 ]; LEFT JOIN 左表之全部記錄及右表結合欄位資料吻合之記錄語法 :SELECT [ 資料表名 ].[ 欄名 ], FROM [ 表一 ] LEFT JOIN [ 表二 ] ON [ 表一 ].[ 鍵值欄一 ] = [ 表二 ].[ 鍵值欄二 ]; RIGHT JOIN 右表之全部記錄及左表結合欄位資料吻合之記錄 20

21 語法 :SELECT [ 資料表名 ].[ 欄名 ], FROM [ 表一 ] RIGHT JOIN [ 表二 ] ON [ 表一 ].[ 鍵值欄一 ] = [ 表二 ].[ 鍵值欄二 ]; 兩個查詢表之 UNION 語法 :SELECT [ 欄位串列 ] FROM [ 表一 ] WHERE [ 準則 ] UNION SELECT [ 欄位串列 ] FROM [ 表二 ] WHERE [ 準則 ] ORDER BY [ 排序欄名 ]; 註 : 以表一之欄位為基準, 兩個表必須有相同之欄位數巢狀式查詢語法 :SELECT [ 欄位串列 ] FROM [ 表一 ] WHERE [ 欄名一 ] IN ( SELECT [ 欄名二 ] FROM [ 表二 ] WHERE [ 準則 ]); 結合 VB 函數 應用範例 1.SELECT Left$(Name,3) AS 姓, Name FROM Authors; 2.SELECT Name, City+, +State+ +Zip AS Address FROM Publishers; 3.SELECT Name FROM Publishers WHERE Mid$(Name,2,1) = a ; 註 : 只在 VB 的環境下適用 21