テーブルの作成/削除（CREATE TABLE,DROP TABLE）

公開日：2019-11-24　最終更新日：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カラムがフィールドになります。

特殊文字の囲み文字

テーブル名や列名については、特殊文字が無ければ囲み文字なしでそのまま記述できます。
特殊文字が含まれる場合は、囲み文字が必要になります。
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を発行してみましょう。
前回作成したクラスを使用します。

・ADOとは・ADOを使う準備・SQLiteに接続/切断・データベースに接続できたかの確認・他のデータベースに接続する場合の指定・今後のためにクラス化しておく・データベースに接続/切断の最後に


  
    
      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」で確認してみましょう。

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

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

当然といえば当然ですね。
テーブルが存在しない場合のみ作成するようにするには、
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が存在しない場合はエラーとなります。

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

ADODB.ConnectionのExecuteメソッド

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

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

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

CommandText

SQL文字列を指定します。
ステートメント、ストアドプロシージャ等も指定できますが、本シリーズでは扱いません。

RecordsAffected

指定は任意です。
操作の影響を受けるレコード数が返されます。

クラスを拡張修正

前回作成したクラスにExecuteメソッドを追加します。

テーブル削除でもあったように、時にエラーを承知で実行する場合もありますので、その場でメッセージが出てしまうと不都合な場合が出てきます。
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）と全削除
 VBAクラスの全コード：データの挿入
 データの挿入：バルクインサート
 データの取得：条件指定（SELECT,WHERE）
VBAクラスの全コード：データの取得
 データの取得：集約集計、並べ替え（DISTINCT,GROUP,HAVING,ORDER）
SQL関数と演算子

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
4.変数宣言のDimとデータ型｜VBA入門
5.RangeとCellsの使い方｜VBA入門
6.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
7.セルのクリア（Clear,ClearContents）｜VBA入門
8.メッセージボックス(MsgBox関数)｜VBA入門
9.条件分岐（Select Case）｜VBA入門
10.ブック・シートの選択（Select,Activate）｜VBA入門

ホーム
その他
SQL入門
テーブルの作成/削除（CREATE TABLE,DROP TABLE）

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

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

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