SQL入門
テーブルの作成/削除(CREATE TABLE,DROP TABLE)

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

テーブルの作成/削除(CREATE TABLE,DROP TABLE)


エクセルVBAでデータベースを扱うためのSQL入門です。
前回までにデータベースを作成し、ADOで接続できるようにしましたので、
今回から実際にSQLを発行して、データベースを操作していきます。


テーブルを作成したり削除する場合のSQLについて解説します。
テーブルの作成/削除は、SQLの種類としてはデータ定義言語:DDL(data description language)になります。
テーブルの作成/削除には、ADOではなくADOX(Microsoft ADO Ext.x.x for DDL and Security)も使いますが、本稿ではADOで統一します。

データベースで使う用語や記号について

テーブル(table)
エクセルのシートに相当するものがテーブルです。
エクセルではブックに複数のシートが持てるように、データベースには複数のテーブルを持つことができます。

カラム(column)
Excelシートの列に相当するものがカラムです。
囲み記事はコラム、データベースではカラムと呼ばれることが多いです。
カラムは、列の名称・属性を指すもので、具体的なデータを指しては使いません。

カラムごとに、文字列、数値等の属性を決めておくことができます。
エクセルの表でも、一つの列には同種のデータのみを入れるようにするべきですが、
データベースでは、よりカラムごとのデータ型を意識する必要があります。

レコード(record)
Excelシートの行に相当するものがレコードです。
カラムとは違い、レコードはデータそのものを指す用語になります。
行を意味するロウ(row)と同じ使い方をすることもあります。

フィールド(field)
Excelシートのセルに相当するものがフィールドです。
レコードを構成する1つ1つの要素、つまり1レコードの1カラムがフィールドになります。

特殊文字の囲み文字
テーブル名や列名については、特殊文字が無ければ囲み文字なしでそのまま記述できます。
特殊文字が含まれる場合は、囲み文字が必要になります。
SQLiteでは、"ダブルクオーテーション、'シングルクオーテーション、`バッククオーテーション等も可
MySQLであれば`バッククォート、SQL ServerやAccessは[]角括弧で囲みます。
その他のDBでは概ねダブルクオーテーションで囲みます。

SQLの記述の大文字
SQLに出てくるキーワード(SQLで決められた単語)は大文字で記述する習慣があります。
あくまで、そういう習慣があるという事で特に気にしない場合もあります。
このSQL入門では、WEB掲載した時の見やすさもあり、大文字で記述するようにしました。
ただし、なるべく大文字で記述していくということであり、小文字になってしまう場合もあるかもしれません。
SQL実行においては、キーワードの大文字小文字は区別されませんので、実行結果に影響はありません。
キーワード以外では、大文字小文字を区別するかどうかはDBの設定によりますが、通常はキーワード以外では大文字小文字を意識して記述します。

カラムのデータ型

データ型を簡単に分類すると、
・数値型
・文字列型
・バイナリ型
・日付/時刻型
・その他
DBごとに、これらがさらに細分化されたデータ型として用意されています。
使えるデータ型は、データベース(DB)ごとに違います。
数値型:INT、FLOAT、DOUBLE・・・
文字列型:CHAR、VARCHAR、TEXT・・・
各DBにおいて使えるデータ型については、それぞれの資料を参照してください。

SQLiteでは、以下のようなデータ型が用意されています。
INTEGER:符号付整数
REAL:浮動小数点数
NUMERIC:上記以外の数値
TEXT:テキスト
NONE:全て
「DB Browser for SQLite」で選択できる型はNONEは無くBLOBがありました。
BLOB(Binary Large OBject)は、エンコードされないそのままのデータが格納されるものです。

SQLiteのデータ型は、DBの中でも特殊なものとなっています。
データ型を定義しても、他のデータ型も自動で型変換されて格納されるようです。
この仕様はかなり特殊なものになりますので、一般的なDB学習にはそぐわない気がします。
他の多くのDBでは、違うデータ型のデータは入れることができません。

したがって、本シリーズではデータ型についてあまり細かい使い方をしません。
文字列(日付含む)はTEXT、整数はINTEGER、整数以外の数値はNUMERIC、
このくらいの区別で使っていく予定です。

テーブル作成:CREATE TABLE

CREATE TABLE table_name ( column_definition[ , column_definition ・・・] )

CREATE TABLEに続けてテーブル名、括弧()の中に複数の列定義を記述します。

テーブル名
・先頭は、半角アルファベットまたは_アンダースコア
・以降は、半角アルファベット、数字、_アンダースコア

全角文字や他の記号を使う場合は、"ダブルクオート、'シングルクオートまたは`バッククオートで囲みます。

column_definition
列定義の基本は、列名とデータ型になりますが、型以外の各種制約をキーワードで指定できます。

列名
・先頭は、半角アルファベットまたは_アンダースコア
・以降は、半角アルファベット、数字、_アンダースコア

全角文字や他の記号を使う場合は、"ダブルクオート、'シングルクオートまたは`バッククオートで囲みます。
列名に漢字を使う事の是非はいろいろ意見が分かれるところです。
一般的にはトラブルのもとではありますが、分かりやすいという点は間違いないでしょう。
特定のDBにおいて、しっかり動作確認できるのであれば問題ないと思います。
本シリーズでは半角英数に限定して使います。


データ型
INTEGER:符号付整数
REAL:浮動小数点数
NUMERIC:上記以外の数値
TEXT:テキスト
NONE:全て

SQLiteでは、存在しないデータ型の文字列を指定してもエラーとなりません。
これはSQLiteの特殊性になり、一般的なDBにあてはまりません。
したがって、本シリーズではSQLiteのデータ型についてあまり細かい解説はしません。

また、SQLiteにはありませんが、他のDBではデータ長を指定します。
INT(11)
VAECHAR(100)
等々の指定が必要になります。

指定できる制約


キーワード 説明
DEFAULT デフォルト値
NOT NULL NULLを許可しない
CHECK 有効な値であるかチェックする条件
UNIQUE 列の値が重複することを許可しない
PRIMARY KEY 主キー、レコードを特定するキー
複数列を主キーにする場合は、各列の定義ではなく、
列定義の後に、
, PRIMARY KEY (列1,列2,・・・) と指定します。
AUTOINCREMENT 自動インクリメント
データベースが自動的に連番値を割り当てます。
複数の列に設定することはできません。
主キーを自動連番にする為に使用します。

DBにより使えるキーワードが変わり、さらに別のキーワードも存在します。
同じ内容でも記述が違う場合もあります。
例えば自動インクリメントは、MySqlではAUTO_INCREMENT、PostgreSqlではSERIAL
各データベースごとにリファレンスを参照してください。

カラム(列)に対するコメント
現時点のSQLiteでは列定義でコメント(説明)を入れることはできません。
他のDBでは、COMMENTキーワードを使ってコメントを記述できるものが多いです。

CREATE TABLE 参考例
CREATE TABLE m_customer(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT)

テーブル名はm_customer
id:整数型、自動インクリメント、主キー
name:テキスト型

CREATE TABLE m_customer(code1,code2,name ,PRIMARY KEY(code1,code2))

テーブル名はm_customer
code1,code2が主キー


VBAからSQLを発行してみましょう。
前回作成したクラスを使用します。
VBAでSQL発行するにあたり、まずはSQLを実行するデータベースに接続する必要があります。このSQL入門では、DB接続にはADOを使用します。SQLの発行とSQLの実行という言い方の違い クライアントでSQLを発行し、それを受け取ったDBサーバーがSQLを実行します。



Sub CreateTable()
  Dim clsDB As New clsSQLite
  
  clsDB.DataBase = "C:\SQLite3\sample.db"
  If Not clsDB.dbOpen Then Exit Sub
  
  Dim sSql As String
  sSql = "CREATE TABLE m_customer(id INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT)"
  clsDB.adoCon.Execute sSql
  
  If Not clsDB.dbClose Then Exit Sub
  
  Set clsDB = Nothing
End Sub
※ADODB.ConnectionのExecuteメソッドの詳細は後述します。

これでテーブルが作成されます。
「DB Browser for SQLite」で確認してみましょう。

vba マクロ SQL

sqlite_sequence
これは、AUTOINCREMENTをSQLiteが管理する為に自動的に作成されるテーブルです。

しかし、このVBAを再度実行するとエラーとなります。

vba マクロ SQL

当然といえば当然ですね。
テーブルが存在しない場合のみ作成するようにするには、
IF NOT EXISTS
このキーワードを追加して、

CREATE TABLE IF NOT EXISTS m_customer(・・・

これなら、テーブルが存在していれば作成しないのでエラーにはなりません。
しかし、作り直したい場合はそれでは困りますので、テーブルを削除したい場合もあります。

テーブル削除:DROP TABLE

DROP TABLE table_name

DROP TABLEに続けてテーブル名を記述します。

Sub DropTable()
  Dim clsDB As New clsSQLite
  
  clsDB.DataBase = "C:\SQLite3\sample.db"
  If Not clsDB.dbOpen Then Exit Sub
  
  Dim sSql As String
  sSql = "DROP TABLE m_customer"
  clsDB.adoCon.Execute sSql
  
  If Not clsDB.dbClose Then Exit Sub
  
  Set clsDB = Nothing
End Sub

このVBAを実行するとテーブルm_customerが削除されます。
m_customerが存在しない場合はエラーとなります。

vba マクロ SQL

SQLでテーブルの存在を確認することもできますが、
VBAでは単純に、On Error Resume Nextを入れることで対応が可能です。

ADODB.ConnectionのExecuteメソッド

指定されたSQLを実行します。

Set recordset = connection.Execute (CommandText, RecordsAffected, Options)

戻り値は、レコード セット オブジェクトです。

CommandText
SQL文字列を指定します。
ステートメント、ストアド プロシージャ等も指定できますが、本シリーズでは扱いません。
RecordsAffected
指定は任意です。
操作の影響を受けるレコード数が返されます。

クラスを拡張修正

前回作成したクラスにExecuteメソッドを追加します。
VBAでSQL発行するにあたり、まずはSQLを実行するデータベースに接続する必要があります。このSQL入門では、DB接続にはADOを使用します。SQLの発行とSQLの実行という言い方の違い クライアントでSQLを発行し、それを受け取ったDBサーバーがSQLを実行します。
テーブル削除でもあったように、時にエラーを承知で実行する場合もありますので、その場でメッセージが出てしまうと不都合な場合が出てきます。
Excuteメソッドの追加と、このエラー処理の改修を行います。

Excuteメソッドの追加
今後の使い勝手を考慮して、
戻り値を使わず単純にSQLを発行するだけの場合と、戻り値のレコードセットが必要な場合でプロシージャーを分けました。

戻り値を使わない場合は、OptionalのByRefで影響を受けたレコード数を返します。

レコードセットを使う場合は、ByRefでレコードセットを返すようにします。
Functionの戻り値でレコードセットを返しても良いのですが、エラー判定を統一しておきたいのでByRefで戻すようにしてみました。
この場合、呼び出し元でレコードセットを使う事になるので、DBはCloseしないようにします。

エラー処理の改修
エラーメッセージがその場で出てしまうと都合が悪い場合もあるので、
クラスでは、エラー情報を保存しておくことにしました。
呼び出し側で、各プロシージャーの戻り値(Boolean)を判定し適宜メッセージを出力するようにします。

VBAでADOを使う為のクラスの全VBA

クラスモジュール:clsSQLite
Option Explicit

'DB接続情報の定数
Private Const defConStr As String = "DRIVER=【DRIVER】;Database=【DATABASE】"
Private Const defDriver As String = "SQLite3 ODBC Driver"

'DB接続情報
Private pAdoCon As ADODB.Connection
Private pConStr As String
Private pDataBase As String
Private pDriver As String

'エラー情報
Private pErrNum As Long
Private pErrMsg As String
Private pErrPrc As String

'エラー情報プロパティ
Public Property Get ErrNum() As Long
  ErrNum = pErrNum
End Property
Public Property Get ErrMsg() As String
  ErrMsg = pErrMsg
End Property
Public Property Get ErrPrc() As String
  ErrPrc = pErrPrc
End Property

'DB接続設定プロパティ
Public Property Set adoCon(arg As ADODB.Connection)
  Set pAdoCon = arg
End Property
Public Property Get adoCon() As ADODB.Connection
  Set adoCon = pAdoCon
End Property
Public Property Let ConStr(arg As String)
  pConStr = arg
End Property
Public Property Get ConStr() As String
  ConStr = pConStr
End Property
Public Property Let DataBase(arg As String)
  pDataBase = arg
End Property
Public Property Get DataBase() As String
  DataBase = pDataBase
End Property
Public Property Let Driver(arg As String)
  pDriver = arg
End Property
Public Property Get Driver() As String
  Driver = pDriver
End Property

'SQL実行:影響を受けたレコード数を戻す
Public Function ExecuteNonQuery(sSql As String, _
                Optional RecordsAffected As Long) _
                As Boolean
  On Error GoTo Err_Exit
  
  '接続状態の退避と接続
  Dim isConnect As Boolean
  If Not Me.adoCon Is Nothing Then isConnect = CBool(Me.adoCon.State)
  If Not Me.dbOpen Then Exit Function
  
  'SQLの発行
  Call adoCon.Execute(sSql, RecordsAffected)
  
  '当初接続されていなかった時は切断
  If Not isConnect Then
    If Not Me.dbClose Then Exit Function
  End If
  
  ExecuteNonQuery = True
  Call resetErr
  Exit Function
  
Err_Exit:
  ExecuteNonQuery = False
  Call setErr(Err, "ExecuteNonQuery")
End Function

'SQL実行:レコードセットを戻す
Public Function ExecuteRecordset(sSql As String, _
                 adoRs As ADODB.Recordset) _
            As Boolean
  On Error GoTo Err_Exit
  
  '接続されていない場合は接続
  If Not Me.dbOpen Then Exit Function
  
  'SQLの発行
  Set adoRs = adoCon.Execute(sSql)
  
  ExecuteRecordset = True
  Call resetErr
  Exit Function
  
Err_Exit:
  ExecuteRecordset = False
  Call setErr(Err, "ExecuteRecordset")
End Function

'DB接続
Public Function dbOpen() As Boolean
  On Error GoTo Err_Exit
  dbOpen = True
    
  '既に接続してたら無視
  If Not Me.adoCon Is Nothing Then
    If Me.adoCon.State = ObjectStateEnum.adStateOpen Then
      Exit Function
    End If
  End If
  
  'SQLiteに接続
  Set Me.adoCon = New ADODB.Connection
  Me.adoCon.Open getConStr
  
  Call resetErr
  Exit Function
  
Err_Exit:
  dbOpen = False
  Call setErr(Err, "dbOpen")
End Function

'DB切断
Public Function dbClose() As Boolean
  On Error GoTo Err_Exit
  dbClose = True
  
  '既に切断されていたら無視
  If Me.adoCon Is Nothing Then Exit Function
  If Me.adoCon.State = ObjectStateEnum.adStateClosed Then Exit Function
  
  'SQLiteを切断
  Me.adoCon.Close
  
  Call resetErr
  Exit Function
  
Err_Exit:
  dbClose = False
  Call setErr(Err, "dbOpen")
End Function

'初期処理
Private Sub Class_Initialize()
  Me.Driver = defDriver
  Me.ConStr = defConStr
End Sub

'終了処理
Private Sub Class_Terminate()
  Call Me.dbClose
End Sub

'DB接続文字列
Private Function getConStr() As String
  getConStr = Me.ConStr
  getConStr = Replace(getConStr, "【DRIVER】", Me.Driver)
  getConStr = Replace(getConStr, "【DATABASE】", Me.DataBase)
End Function

'エラー情報設定
Private Sub setErr(objErr As ErrObject, _
          ErrPrc As String)
  pErrNum = objErr.Number
  pErrMsg = objErr.Description
  pErrPrc = ErrPrc
End Sub
'エラー情報クリア
Private Sub resetErr()
  pErrNum = 0
  pErrMsg = ""
  pErrPrc = ""
End Sub

まだまだ試作になります。
今後SQL入門シリーズを進め行く中で改良していきます。

標準モジュールでの使い方
Sub DropAndCreate()
  Dim clsDB As New clsSQLite
  clsDB.DataBase = "C:\SQLite3\sample.db"
  
  Dim sSql As String
  
  'テーブル削除
  sSql = "DROP TABLE m_customer"
  Call clsDB.ExecuteNonQuery(sSql)
  
  'テーブル追加
  sSql = "CREATE TABLE m_customer("
  sSql = sSql & " id INTEGER PRIMARY KEY AUTOINCREMENT"
  sSql = sSql & ",name TEXT NOT NULL"
  sSql = sSql & ")"
  If Not clsDB.ExecuteNonQuery(sSql) Then
    MsgBox clsDB.ErrMsg
    Exit Sub
  End If
  
  Set clsDB = Nothing
End Sub

テーブルm_customerを削除して作り直しています。

テーブルの作成/削除の最後に

まだテーブルを作っただけでデータが無いので実感がわかないと思います。
次回にカラム追加の解説とワークシートからテーブル作成するVBAを紹介し、
その後にデータ挿入、そしてデータ取得へと進みます。
VBAからはデータだけ扱う場合の方が多いと思いますが、
一時的にテーブルを作ってから処理するような場合も多々ありますので、テーブルの作成方法もぜひ覚えておいてください。



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

SQLiteのインストール
データベースに接続/切断
テーブルの作成/削除(CREATE TABLE,DROP TABLE)
テーブル名変更と列追加(ALTER TABLE)とテーブル自動作成
データの挿入(INSERT)と全削除
データの挿入:バルクインサート
データの取得:条件指定(SELECT,WHERE)
データの取得:集約集計、並べ替え(DISTINKT,GROUP,HAVING,ORDER)
SQL関数と演算子
データベースにおけるNULLの扱い方
データベースの正規化とマスタの作成


新着記事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.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • テーブルの作成/削除(CREATE TABLE,DROP TABLE)

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


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



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