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

SQLの初心者向け入門解説、VBAからデータベースを扱うためのSQLを解説
最終更新日:2019-12-08

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


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


これからは、INSERTしたデータをいろいろな方法で取得していきます。
まずは、取得する条件を指定して(データを絞り込んで)テーブルからデータを取得します。
これは、エクセルのワークシートでフィルタをかけてからコピーすることに相応します。

テスト用のテーブル作成

前回の「データの挿入:バルクインサート」で作成したデータを使用します。
エクセルVBAでデータベースを扱うためのSQL入門です。前回でデータを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
);

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)と全削除」この中には、データ取得用のメソッドを入れてあったのですが、
エクセルVBAでデータベースを扱うためのSQL入門です。前回までにテーブルを自由に作れるようになり、ワークシートからの自動作成も出来上がりました。今回は作成したテーブルにデータを追加(INSERT)していきます。
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)).Clear
  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 任意 取得したカラム名を先頭行に出力

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

全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入門」の記事

テーブル名変更と列追加(ALTER TABLE)とテーブル自動作成
データの挿入(INSERT)と全削除
データの挿入:バルクインサート
データの取得:条件指定(SELECT,WHERE)
データの取得:集約集計、並べ替え(DISTINKT,GROUP,HAVING,ORDER)
SQL関数と演算子
データベースにおけるNULLの扱い方
データベースの正規化とマスタの作成
テーブルを結合して取得(INNER JOIN,OUTER JOIN)
複数のSELECT結果を統合(UNION,UNION ALL)
データの更新(UPDATE)


新着記事NEW ・・・新着記事一覧を見る

他のテーブルのデータで追加/更新/削除|SQL入門(12月8日)
データの削除(DELETE)|SQL入門(12月7日)
データの更新(UPDATE)|SQL入門(12月6日)
複数のSELECT結果を統合(UNION,UNION ALL)|SQL入門(12月5日)
テーブルを結合して取得(INNER JOIN,OUTER JOIN)|SQL入門(12月4日)
データベースの正規化とマスタの作成|SQL入門(12月3日)
データベースにおけるNULLの扱い方|SQL入門(12月2日)
オブジェクト変数とは何か|VBA技術解説(12月2日)
SQL関数と演算子|SQL入門(12月1日)
データの取得:集約集計、並べ替え(DISTINKT,GROUP,HAVING,ORDER)|SQL入門(11月30日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.マクロって何?VBAって何?|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • データの取得:条件指定(SELECT,WHERE)

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


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
    掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



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