データの取得:条件指定(SELECT,WHERE)
エクセルVBAでデータベースを扱うためのSQL入門です。
前回で100万件のデータも高速にINSERTすることができるようになりました。
まずは、取得する条件を指定して(データを絞り込んで)テーブルからデータを取得します。
これは、エクセルのワークシートでフィルタをかけてからコピーすることに相応します。
テスト用のテーブル作成
CREATE TABLE t_sales (
'id' INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
,'code' TEXT NOT NULL
,'name' TEXT NOT NULL
,'address' TEXT
,'sales_date' TEXT
,'item_code' TEXT
,'item_name' TEXT
,'item_price' INTEGER
,'item_count' INTEGER
,'item_amount' INTEGER
,'comment' TEXT
);

データの取得:SELECTの構文
FROM テーブル名 , テーブル名 ・・・
[ WHERE 論理式]
[ GROUP BY 任意の式 [, 任意の式 ・・・ ]]
[ HAVING 任意の式 [, 任意の式 ・・・ ]]
[ ORDER BY 任意の式 [, 任意の式 ・・・ ]]
関数については、今後すこしずつ紹介していきます。
これらは句という言い方をします。
FROM句、WHERE句、GROUP BY句・・・
日本語では、こういう呼び方がされているので、一応覚えておいた方が良いでしょう。
今回は、SELECT…FROM…WHERE
これだけに絞って、さらに一番基本的な使い方だけを説明します。
FROM テーブル名
[ WHERE 条件 ]
code,name
特殊文字を含む場合は、ダブルクオーテーションで囲みます。
"code","name"
MySQLであれば`バッククォート、SQL ServerやAccessは[]角括弧で囲みます。
データ取得するカラムを指定
SELECT code,name,address,sales_date,item_code
FROM t_sales
WHERE code = '001'
codeが'001'のデータが取得されます。
カラムの指定を省略して全カラムを取得
SELECT * FROM t_sales
WHERE code = '001' OR code = '002'
codeが'001'と'002'のデータが取得されます。
RDBを活用していくためには、カラム変更しても動作するようにSQLを書いておいた方が良いでしょう。
*は慎重に使うようにしてください。
クラスにデータ取得用のメソッドを追加
他のDB(MySql)では取得できているので、このメソッドは残しておきますが、
新たに、SQLite用にメソッドを追加します。
取得したデータは最終的にシートに出力するので、シート出力まで対応したメソッドを作成します。
'列挙
Public Enum enmClear
None
Clear
ClearContents
End Enum
'SQL実行:レコードセットオープン
Public Function RecordsetOpen(sSql As String, _
adoRs As ADODB.Recordset, _
Optional aCursorType As CursorTypeEnum = adOpenKeyset, _
Optional aLockType As LockTypeEnum = adLockReadOnly) _
As Boolean
On Error GoTo Err_Exit
'接続されていない場合は接続
If Not Me.DbOpen Then Exit Function
'SQL指定してレコードセットオープン
Call adoRs.Open(sSql, Me.AdoCon, adOpenKeyset, aCursorType, aLockType)
RecordsetOpen = True
Call resetErr
Exit Function
Err_Exit:
RecordsetOpen = False
Call setErr(Err, "ExecuteRecordset")
End Function
'SQL実行:ワークシートに貼り付け
Public Function SheetFromRecordset(sSql As String, _
aRange As Range, _
Optional aClear As enmClear = enmClear.None, _
Optional isHeader As Boolean = False) _
As Boolean
On Error GoTo Err_Exit
'オプション:シートクリア
Dim ws As Worksheet
Set ws = aRange.Worksheet
Select Case aClear
Case enmClear.Clear
ws.Range(aRange, ws.Cells.SpecialCells(xlCellTypeLastCell)).Clear
Case enmClear.ClearContents
ws.Range(aRange, ws.Cells.SpecialCells(xlCellTypeLastCell)).ClearContents
End Select
'接続状態の退避と接続
Dim isConnect As Boolean
If Not Me.AdoCon Is Nothing Then isConnect = CBool(Me.AdoCon.State)
If Not Me.DbOpen Then Exit Function
Dim adoRs As New ADODB.Recordset
If Not Me.RecordsetOpen(sSql, adoRs) Then Exit Function
'カラム名出力
Dim i As Long
If isHeader Then
For i = 0 To adoRs.Fields.Count - 1
aRange.Item(1, i + 1).Value = adoRs.Fields(i).Name
Next
End If
'指定セルにデータ貼り付け
Call aRange.Offset(IIf(isHeader, 1, 0)).CopyFromRecordset(adoRs)
'当初接続されていなかった時は切断
If Not isConnect Then
If Not Me.DbClose Then Exit Function
End If
SheetFromRecordset = True
Call resetErr
Exit Function
Err_Exit:
SheetFromRecordset = False
Call setErr(Err, "ExecuteRecordset")
End Function
標準モジュールから呼び出すメソッドは、SheetFromRecordsetになります。
sSq | 必須 | SQL文字列 | ||||||
aRange | 必須 | 出力先セルのRangeオブジェクト | ||||||
aClear | 任意 | シートのクリアを列挙で指定
|
||||||
isHeader | 任意 | 取得したカラム名を先頭行に出力 |
シートにデータを出力時は、事前にシートクリアするはずなので、これに対応しています。
取得したデータのカラム名を先頭行に出力するかどうかも選択できるようにしています。
CopyFromRecordset
名前 | 必須/ オプション |
データ型 | 説明 |
Data | 必須 | バリアント型 | セル範囲にコピーする?Recordset?オブジェクトを指定します。 |
MaxRows | オプション | Variant | ワークシートにコピーするレコードの最大数を指定します。 この引数を省略すると、Recordset?オブジェクトのすべてのレコードをコピーします。 |
MaxColumns | オプション | Variant | ワークシートにコピーするフィールドの最大数を指定します。 この引数を省略すると、Recordset?オブジェクトのすべてのフィールドをコピーします。 |
コピーはRecordsetオブジェクトのカレント レコードの位置から行われます。
コピーが完了すると、Recordset オブジェクトのEOFプロパティはTrueになります。
シートにある見出しを使ってカラム名リストを作成
シートの先頭行にカラム名を入れておくと、セル値からカラム名リストを自動で作成できます。

'シートの見出しからカラム名リスト作成
Function getColumns(ByVal aRange As Range) As String
getColumns = ""
Dim i As Long
i = 1
Do While aRange.Item(1, i).Value <> ""
If getColumns <> "" Then getColumns = getColumns & ","
getColumns = getColumns & addQuote(aRange.Item(1, i).Value, """")
i = i + 1
Loop
End Function
'シングルクオーテーションをエスケープ処理
Function addQuote(ByVal str As String, _
Optional ByVal aQuote As String = "'") As String
addQuote = aQuote & Replace(str, "'", "''") & aQuote
End Function
次章では、以上のVBAを使ってのSELECT文の使用例をいくつかのパターンで紹介します。
標準モジュールの使用例
Sub SelectForSheet()
Dim clsDB As New clsSQLite
clsDB.DataBase = "C:\SQLite3\sample.db"
Dim ws As Worksheet
Set ws = ActiveSheet
Dim sSql As String
sSql = ""
sSql = sSql & "SELECT " & getColumns(ws.Range("A1"))
sSql = sSql & " FROM t_sales"
sSql = sSql & " WHERE code = '001'"
If Not clsDB.SheetFromRecordset(sSql, ws.Range("A2"), enmClear.Clear) Then
MsgBox clsDB.ErrMsg
Exit Sub
End If
Set clsDB = Nothing
End Sub
1行目をカラム名として使っています。
したがって、データは2行目以降に出力しています。
WHERE句のいろいろな条件指定方法
比較演算子
= | 等しい |
> | 大きい |
< | 小さい |
>= | 以上 |
<= | 以下 |
<> | 等しくない |
!= | 等しくない |
以下では、上記VBAの
sSql = sSql & " WHERE
この部分の変更として記載します。
AND演算子、OR演算子
sSql = sSql & " WHERE code = '001'"
sSql = sSql & " AND (item_code = '10001'"
sSql = sSql & " OR item_code = '10003')"
codeが'001'
かつ
item_code="10001'または'10003'
IN演算子
sSql = sSql & " WHERE item_code IN('10001','10003','10005')"
item_codeが'10001','10003','10005'のいずれか。
IN演算子は、INの前の項目が()内のいずれかと一致している場合に真を返します。
BETWEEN演算子
sSql = sSql & " WHERE item_count BETWEEN 15 AND 17"
item_countが15から17の範囲
BETWEEN演算子は、ANDの前以上かつANDの後ろ以上の場合に真を返します。
これは、以下と同じになります。
sSql = sSql & " WHERE item_count >= 15 AND item_count <= 17"
LIKE演算子
sSql = sSql & " WHERE item_code LIKE '1000%'"
item_codeが'1000'で始まっている
LIKE演算子は、メタ文字によるパターンマッチングを行います。
% | 任意の文字列 |
_ | アンダースコアです。 任意の1文字 |
使用できるメタ文字はDBにより違いますが、上記はほとんどのDBでサポートされています。
複数条件を指定して絞り込み時間計測
Sub SelectForSheet()
Dim sTimer As Double: sTimer = Timer
Dim clsDB As New clsSQLite
clsDB.DataBase = "C:\SQLite3\sample.db"
Dim ws As Worksheet
Set ws = ActiveSheet
Dim sSql As String
sSql = ""
sSql = sSql & "SELECT " & getColumns(ws.Range("A1"))
sSql = sSql & " FROM t_sales"
sSql = sSql & " WHERE code = '001'"
sSql = sSql & " AND (item_code = '10001'"
sSql = sSql & " OR item_code = '10003')"
sSql = sSql & " AND item_code IN('10001','10003','10005')"
sSql = sSql & " AND item_code LIKE '1000%'"
sSql = sSql & " AND item_count BETWEEN 15 AND 17"
If Not clsDB.SheetFromRecordset(sSql, ws.Range("A2"), enmClear.Clear) Then
MsgBox clsDB.ErrMsg
Exit Sub
End If
Set clsDB = Nothing
Debug.Print Timer - sTimer
End Sub
codeが'001と'003'
item_codeが'1000'で始まっている
item_countが15から17
処理時間は、約0.15秒です。
データの取得:条件指定の最後に
フィルタをかけて絞り込んだ途端にしばらく応答が返ってこなくなります。
エクセルのフィルタは強力な機能ですが、データ量が多いと動きがとても重くなります。
100万件から数十件、数百件、数千件のデータを抽出しても、あっという間に終わります。
データベースの優秀さが如実に感じられるものです。
そして、この優秀なデータベースを自在に扱うための言語がSQLです。
徐々にSQLの深みに入っていきます。
同じテーマ「SQL入門」の記事
データの挿入(INSERT)と全削除
VBAクラスの全コード:データの挿入
データの挿入:バルクインサート
データの取得:条件指定(SELECT,WHERE)
VBAクラスの全コード:データの取得
データの取得:集約集計、並べ替え(DISTINCT,GROUP,HAVING,ORDER)
SQL関数と演算子
データベースにおけるNULLの扱い方
データベースの正規化とマスタの作成
全テーブル定義とテーブル自動作成VBA
テーブルを結合して取得(INNER JOIN,OUTER JOIN)
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数(セル範囲をトリム:端の空白セルを除外)|エクセル入門(2024-08-30)
正規表現関数(REGEXTEST,REGEXREPLACE,REGEXEXTRACT)|エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.繰り返し処理(For Next)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.メッセージボックス(MsgBox関数)|VBA入門
8.セルのクリア(Clear,ClearContents)|VBA入門
9.ブック・シートの選択(Select,Activate)|VBA入門
10.条件分岐(Select Case)|VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。