SQL入門
データの取得:条件指定(SELECT,WHERE)

SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説
公開日: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
);

VBA マクロ SQL ADO

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

VBA マクロ SQL ADO

'シートの見出しからカラム名リスト作成
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

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

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

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

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

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



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

データの挿入(INSERT)と全削除

・テスト用のテーブル作成 ・データ挿入: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コードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



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