データの取得:条件指定(SELECT,WHERE)
エクセルVBAでデータベースを扱うためのSQL入門です。
前回で100万件のデータも高速にINSERTすることができるようになりました。
まずは、取得する条件を指定して(データを絞り込んで)テーブルからデータを取得します。
これは、エクセルのワークシートでフィルタをかけてからコピーすることに相応します。
テスト用のテーブル作成
前回の「データの挿入:バルクインサート」で作成したデータを使用します。
100万件のデータを作成しテーブルにINSERTしておきます。
・テスト用のテーブル作成 ・VBAの改善点の概要 ・バルクインサートのSQL解説 ・バルクインサートのVBAコードと動作検証 ・文字列操作を改善する方法とVBAコード ・標準モジュールでの使い方と、100万行の実行結果 ・バルクインサートの最後に
テーブル定義とワークシートは以下になります。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の構文
SELECTの全構文はかなり複雑なものになります。
SELECT [ DISTINCT ] 任意の式 [ , 任意の式 ・・・]
FROM テーブル名 , テーブル名 ・・・
[ WHERE 論理式]
[ GROUP BY 任意の式 [, 任意の式 ・・・ ]]
[ HAVING 任意の式 [, 任意の式 ・・・ ]]
[ ORDER BY 任意の式 [, 任意の式 ・・・ ]]
任意の式には、列名または各種の式(関数等)を指定できます。
関数については、今後すこしずつ紹介していきます。
FROM テーブル名 , テーブル名 ・・・
[ WHERE 論理式]
[ GROUP BY 任意の式 [, 任意の式 ・・・ ]]
[ HAVING 任意の式 [, 任意の式 ・・・ ]]
[ ORDER BY 任意の式 [, 任意の式 ・・・ ]]
関数については、今後すこしずつ紹介していきます。
FROM、WHERE、GROUP BY・・・、
これらは句という言い方をします。
FROM句、WHERE句、GROUP BY句・・・
日本語では、こういう呼び方がされているので、一応覚えておいた方が良いでしょう。
いきなり全てを説明するのは困難です。
今回は、SELECT…FROM…WHERE
これだけに絞って、さらに一番基本的な使い方だけを説明します。
SELECT 列名 [ , 列名 ・・・]
FROM テーブル名
[ WHERE 条件 ]
これらは句という言い方をします。
FROM句、WHERE句、GROUP BY句・・・
日本語では、こういう呼び方がされているので、一応覚えておいた方が良いでしょう。
今回は、SELECT…FROM…WHERE
これだけに絞って、さらに一番基本的な使い方だけを説明します。
FROM テーブル名
[ WHERE 条件 ]
特殊文字を含まない列名は、列名だけの記述でも構いません。
code,name
特殊文字を含む場合は、ダブルクオーテーションで囲みます。
"code","name"
囲み文字はDBにより違いがあります。
MySQLであれば`バッククォート、SQL ServerやAccessは[]角括弧で囲みます。
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を書いておいた方が良いでしょう。
*は慎重に使うようにしてください。
クラスにデータ取得用のメソッドを追加
前回までに作成した「データの挿入(INSERT)と全削除」この中には、データ取得用のメソッドを入れてあったのですが、
他のDB(MySql)では取得できているので、このメソッドは残しておきますが、
新たに、SQLite用にメソッドを追加します。
取得したデータは最終的にシートに出力するので、シート出力まで対応したメソッドを作成します。
クラスの追加部分:clsSQLite
・テスト用のテーブル作成 ・データ挿入:INSERT INTO ・テストデータをシートで作成 ・INSERT INTOのサンプルVBA ・より速くINSERT INTOを処理するために ・テーブルの全削除 ・データを挿入の最後に
SQLite+ADOの場合、正しく動作しないことが分かりました。他の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
指定された範囲の左上隅を始点として、ADOのRecordsetオブジェクトの内容をワークシートにコピーします。
Rangeオブジェクト.Copyfromrecordset(Data, MaxRows, MaxColumns)
コピーはRecordsetオブジェクトのカレント レコードの位置から行われます。
コピーが完了すると、Recordset オブジェクトのEOFプロパティはTrueになります。
名前 | 必須/ オプション |
データ型 | 説明 |
Data | 必須 | バリアント型 | セル範囲にコピーする?Recordset?オブジェクトを指定します。 |
MaxRows | オプション | Variant | ワークシートにコピーするレコードの最大数を指定します。 この引数を省略すると、Recordset?オブジェクトのすべてのレコードをコピーします。 |
MaxColumns | オプション | Variant | ワークシートにコピーするフィールドの最大数を指定します。 この引数を省略すると、Recordset?オブジェクトのすべてのフィールドをコピーします。 |
コピーはRecordsetオブジェクトのカレント レコードの位置から行われます。
コピーが完了すると、Recordset オブジェクトのEOFプロパティはTrueになります。
全VBAコードは以下を参照してください。
VBAクラスの全コード:データの取得
SQL入門の「データの取得:条件指定(SELECT,WHERE)」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コード クラスモジュール:clsSQLite
シートにある見出しを使ってカラム名リストを作成
カラム名リストをVBAで固定文字で指定するのは何かと不便です。
シートの先頭行にカラム名を入れておくと、セル値からカラム名リストを自動で作成できます。
次章では、以上のVBAを使ってのSELECT文の使用例をいくつかのパターンで紹介します。
シートの先頭行にカラム名を入れておくと、セル値からカラム名リストを自動で作成できます。
'シートの見出しからカラム名リスト作成
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万行がある場合、
フィルタをかけて絞り込んだ途端にしばらく応答が返ってこなくなります。
エクセルのフィルタは強力な機能ですが、データ量が多いと動きがとても重くなります。
上記VBA実行では、ほんの一瞬でデータが取得できます。
100万件から数十件、数百件、数千件のデータを抽出しても、あっという間に終わります。
データベースの優秀さが如実に感じられるものです。
そして、この優秀なデータベースを自在に扱うための言語がSQLです。
次回は、キーを指定して集約・集計したり、並べ替えて取得する方法の説明を行います。
徐々にSQLの深みに入っていきます。
フィルタをかけて絞り込んだ途端にしばらく応答が返ってこなくなります。
エクセルのフィルタは強力な機能ですが、データ量が多いと動きがとても重くなります。
100万件から数十件、数百件、数千件のデータを抽出しても、あっという間に終わります。
データベースの優秀さが如実に感じられるものです。
そして、この優秀なデータベースを自在に扱うための言語がSQLです。
徐々にSQLの深みに入っていきます。
同じテーマ「SQL入門」の記事
・テスト用のテーブル作成 ・データ挿入:INSERT INTO ・テストデータをシートで作成 ・INSERT INTOのサンプルVBA ・より速くINSERT INTOを処理するために ・テーブルの全削除 ・データを挿入の最後に
VBAクラスの全コード:データの挿入SQL入門の「データの挿入(INSERT)と全削除」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コード クラスモジュール:clsSQLite
データの挿入:バルクインサート・テスト用のテーブル作成 ・VBAの改善点の概要 ・バルクインサートのSQL解説 ・バルクインサートのVBAコードと動作検証 ・文字列操作を改善する方法とVBAコード ・標準モジュールでの使い方と、100万行の実行結果 ・バルクインサートの最後に
データの取得:条件指定(SELECT,WHERE)VBAクラスの全コード:データの取得
SQL入門の「データの取得:条件指定(SELECT,WHERE)」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コード クラスモジュール:clsSQLite
データの取得:集約集計、並べ替え(DISTINCT,GROUP,HAVING,ORDER)・データの取得:SELECTの構文 ・テスト用のテーブルとVBAコード ・DISTINCT句 ・GROUP BY句 ・HAVING句 ・ORDER BY句 ・VBAでのSQL使用例 ・列名(カラム名)の別名(エイリアス)をつけるAS句 ・データの取得:条件指定の最後に
SQL関数と演算子・集計/数値関数 ・文字列関数 ・日付時刻関数 ・その他の関数 ・演算子 ・CASE演算子 ・SQL関数と演算子の最後に
データベースにおけるNULLの扱い方・NULLデータのINSERT ・NULLに関する演算子とSQL関数 ・文字列結合におけるNULLの挙動 ・集計関数におけるNULLの挙動 ・NULLの扱い方の最後に
データベースの正規化とマスタの作成・データベース正規化とは ・マスタデータとトランザクションデータ ・正規化したテーブル定義 ・非正規化から正規化したテーブルを作成 ・データベース正規化の最後に
全テーブル定義とテーブル自動作成VBA・テーブル:m_customer ・テーブル:m_item ・テーブル:t_sales ・テーブル自動作成
テーブルを結合して取得(INNER JOIN,OUTER JOIN)・テーブルの結合方法 ・列名修飾とエイリアス ・内部結合:INNER JOIN ・左外部結合:LEFT OUTER JOIN ・右外部結合:RIGHT OUTER JOIN ・外部結合(OUTER JOIN)で結合条件に一致する行が無い場合 ・複数のJOINを組み合わせた使用例 ・JOINを使わない内部結合 ・テーブルを結合して取得の最後に
新着記事NEW ・・・新着記事一覧を見る
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。