データの取得：条件指定（SELECT,WHERE）

公開日：2019-11-29　最終更新日：2022-11-02

データの取得：条件指定（SELECT,WHERE）

エクセルVBAでデータベースを扱うためのSQL入門です。
前回で100万件のデータも高速にINSERTすることができるようになりました。

これからは、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
);

100万件のデータを作成しテーブルにINSERTしておきます。

データの取得：SELECTの構文

SELECTの全構文はかなり複雑なものになります。

SELECT [ DISTINCT ] 任意の式 [ , 任意の式・・・]
FROM テーブル名 , テーブル名・・・
[ WHERE 論理式]
[ GROUP BY 任意の式 [, 任意の式・・・ ]]
[ HAVING 任意の式 [, 任意の式・・・ ]]
[ ORDER BY 任意の式 [, 任意の式・・・ ]]

任意の式には、列名または各種の式（関数等）を指定できます。
関数については、今後すこしずつ紹介していきます。

FROM、WHERE、GROUP BY・・・、
これらは句という言い方をします。
FROM句、WHERE句、GROUP BY句・・・
日本語では、こういう呼び方がされているので、一応覚えておいた方が良いでしょう。

いきなり全てを説明するのは困難です。
今回は、SELECT…FROM…WHERE
これだけに絞って、さらに一番基本的な使い方だけを説明します。

SELECT 列名 [ , 列名・・・]
FROM テーブル名
[ WHERE 条件 ]

特殊文字を含まない列名は、列名だけの記述でも構いません。
code,name
特殊文字を含む場合は、ダブルクオーテーションで囲みます。
"code","name"

囲み文字はDBにより違いがあります。
MySQLであれば`バッククォート、SQL ServerやAccessは[]角括弧で囲みます。

WHERE句を省略すると全行のデータが対象となります。

データ取得するカラムを指定

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）と全削除」この中には、データ取得用のメソッドを入れてあったのですが、

・テスト用のテーブル作成・データ挿入：INSERT INTO ・テストデータをシートで作成・INSERT INTOのサンプルVBA ・より速くINSERT INTOを処理するために・テーブルの全削除・データを挿入の最後に

SQLite+ADOの場合、正しく動作しないことが分かりました。
他のDB（MySql）では取得できているので、このメソッドは残しておきますが、
新たに、SQLite用にメソッドを追加します。
取得したデータは最終的にシートに出力するので、シート出力まで対応したメソッドを作成します。

クラスの追加部分：clsSQLite


  
    
      '列挙

      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

任意

シートのクリアを列挙で指定

None	クリアしない
Clear	書式も含めてクリア
ClearContents	データのみクリア

isHeader

任意

取得したカラム名を先頭行に出力

シートにデータを出力時は、事前にシートクリアするはずなので、これに対応しています。
取得したデータのカラム名を先頭行に出力するかどうかも選択できるようにしています。

CopyFromRecordset

指定された範囲の左上隅を始点として、ADOのRecordsetオブジェクトの内容をワークシートにコピーします。

Rangeオブジェクト.Copyfromrecordset(Data, MaxRows, MaxColumns)

名前	必須/ オプション	データ型	説明
Data	必須	バリアント型	セル範囲にコピーする?Recordset?オブジェクトを指定します。
MaxRows	オプション	Variant	ワークシートにコピーするレコードの最大数を指定します。この引数を省略すると、Recordset?オブジェクトのすべてのレコードをコピーします。
MaxColumns	オプション	Variant	ワークシートにコピーするフィールドの最大数を指定します。この引数を省略すると、Recordset?オブジェクトのすべてのフィールドをコピーします。

コピーはRecordsetオブジェクトのカレントレコードの位置から行われます。
コピーが完了すると、Recordset オブジェクトのEOFプロパティはTrueになります。

全VBAコードは以下を参照してください。

VBAクラスの全コード：データの取得

SQL入門の「データの取得：条件指定（SELECT,WHERE）」時点のVBAクラスの全コードです。ADOを使ったDB接続のVBAクラスの全コードクラスモジュール：clsSQLite

シートにある見出しを使ってカラム名リストを作成

カラム名リストをVBAで固定文字で指定するのは何かと不便です。
シートの先頭行にカラム名を入れておくと、セル値からカラム名リストを自動で作成できます。


  
    
      'シートの見出しからカラム名リスト作成

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
この部分の変更として記載します。

ＡＮＤ演算子、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

手元のテストデータでは100万件から43件取得されました。
処理時間は、約0.15秒です。

データの取得：条件指定の最後に

エクセルのワークシートに100万行がある場合、
フィルタをかけて絞り込んだ途端にしばらく応答が返ってこなくなります。
エクセルのフィルタは強力な機能ですが、データ量が多いと動きがとても重くなります。

上記VBA実行では、ほんの一瞬でデータが取得できます。
100万件から数十件、数百件、数千件のデータを抽出しても、あっという間に終わります。
データベースの優秀さが如実に感じられるものです。
そして、この優秀なデータベースを自在に扱うための言語がSQLです。

次回は、キーを指定して集約・集計したり、並べ替えて取得する方法の説明を行います。
徐々にSQLの深みに入っていきます。

同じテーマ「SQL入門」の記事

データの挿入（INSERT）と全削除
 VBAクラスの全コード：データの挿入
 データの挿入：バルクインサート
データの取得：条件指定（SELECT,WHERE）
VBAクラスの全コード：データの取得
 データの取得：集約集計、並べ替え（DISTINCT,GROUP,HAVING,ORDER）
SQL関数と演算子
 データベースにおけるNULLの扱い方
 データベースの正規化とマスタの作成
 全テーブル定義とテーブル自動作成VBA
テーブルを結合して取得（INNER JOIN,OUTER JOIN）

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
その他
SQL入門
データの取得：条件指定（SELECT,WHERE）

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

SQL入門
データの取得：条件指定（SELECT,WHERE）