SQLite 3の利用

【概要】SQLite 3はリレーショナルデータベース管理システムである。本ガイドでは、データベース環境の構築、操作方法、データ整合性の確保、クエリの実行計画の確認までを解説する。

【目次】

  1. データベース環境の構築と前準備
  2. データベース操作の項目一覧
  3. データベースの作成手順
  4. 既存データベースへの接続
  5. テーブル定義とデータ整合性制約
  6. データの登録
  7. データ整合性制約の検証
  8. データの検索
  9. データの更新
  10. データの削除
  11. データのエクスポート
  12. クエリの実行計画の確認
  13. SQLスクリプトの実行
  14. データベース構造の確認
  15. データベースのバックアップと復元

Windows環境におけるSQLite 3のインストール手順は、セットアップガイドで解説している。

1. データベース環境の構築と前準備

SQLite 3の詳細情報: SQLite 3技術解説 »

データベース設定の基本事項

本ガイドでは、以下の設定を前提とする。

2. データベース操作の項目一覧

3. データベースの作成手順

Windows環境でのSQLite 3の起動手順

Windows環境での操作手順を示す。以下の手順で、新規のSQLite 3データベースC:\SQLite\mydbを作成する。

  1. Windowsのコマンドプロンプトで以下を実行する。

    データベースファイルを配置するディレクトリC:\SQLiteに移動する。

    cd /d C:\SQLite
    コマンドプロンプトでC:\SQLiteディレクトリに移動した画面
  2. sqlite3.exeの起動

    sqlite3.exe起動時に、データベースファイル名を指定する。データベースファイル名には半角英数字のみを使用する。

    データベースファイル名をmydbとする場合の操作を示す。

    .\sqlite3.exe mydb
    sqlite3.exeを起動してmydbデータベースを指定した画面

    指定したデータベースファイルが存在しない場合、SQLite 3はそのファイル名を新規データベースとして扱う。

    データベースファイルは、テーブル定義などのデータベース更新操作時に生成される。そのため、起動直後にファイルが存在しなくても問題ない。

Ubuntu環境でのSQLite 3の起動手順

Ubuntu環境での操作手順を示す。以下の手順で、新規のSQLite 3データベース/tmp/mydbを作成する。

  1. Ubuntuの端末で以下を実行する。

    データベースファイルを配置するディレクトリ/tmpに移動する。

    cd /tmp
  2. SQLite 3の起動

    sqlite3の起動時に、データベースファイル名を指定する。データベースファイル名には半角英数字のみを使用する。

    データベースファイル名をmydbとする場合の操作を示す。

    sqlite3 /tmp/mydb
    Ubuntuの端末でsqlite3を起動してmydbデータベースを指定した画面

指定したデータベースファイルが存在しない場合、SQLite 3はそのファイル名を新規データベースとして扱う。データベースファイルは、テーブル定義などのデータベース更新操作時に生成される。そのため、起動直後にファイルが存在しなくても問題ない。

SQLite 3のヘルプ

.helpコマンドを実行すると、利用可能なコマンドの一覧が表示される。

SQLite 3の.helpコマンドを実行した結果画面

文字エンコーディングの確認

現在のデータベースの文字エンコーディングを確認するには、PRAGMA encoding;コマンドを実行する。

データベースの削除

データベースを削除する場合は、対応するデータベースファイルをファイルシステム上で削除する。

SQLite 3の終了

.exitコマンドを実行することで、SQLite 3を終了する。

SQLite 3の.exitコマンドを実行して終了した画面

4. 既存データベースへの接続

以下の手順で、既存のデータベースファイルC:\SQLite\mydbに接続する。

  1. Windowsのコマンドプロンプトで以下を実行する。

    データベースファイルが配置されたディレクトリC:\SQLiteに移動する。

    cd /d C:\SQLite
    コマンドプロンプトでC:\SQLiteディレクトリに移動した画面
  2. sqlite3.exeの起動とデータベース接続

    sqlite3.exe起動時に、接続するデータベースファイル名を指定する。mydbに接続する場合は、以下を実行する。

    .\sqlite3.exe mydb
    sqlite3.exeを起動して既存のmydbデータベースに接続した画面

5. テーブル定義とデータ整合性制約

SQLを使用して、order_recordsテーブルを定義し、データ整合性制約を設定する。

テーブルのスキーマ定義: order_records(id, year, month, day, customer_name, product_name, unit_price, qty)
  1. テーブル定義用SQLの実行 
    create table order_records (
    id            INTEGER  PRIMARY KEY autoincrement not null,
    year          INTEGER  not null CHECK ( year > 2008 ),
    month         INTEGER  not null CHECK ( month >= 1 AND month <= 12 ),
    day           INTEGER  not null CHECK ( day >= 1 AND day <= 31 ),
    customer_name TEXT     not null,
    product_name  TEXT     not null,
    unit_price    REAL     not null CHECK ( unit_price > 0 ),
    qty           INTEGER  not null DEFAULT 1 CHECK ( qty > 0 ),
    created_at    DATETIME not null,
    updated_at    DATETIME,
    CHECK ( ( unit_price * qty ) < 200000 ) );
    order_recordsテーブルを作成するCREATE TABLE文を実行した画面

    この操作により、C:\SQLiteディレクトリ内にデータベースファイルmydbが生成される。

    C:\SQLiteディレクトリ内に生成されたmydbデータベースファイル

    SQLite 3では、初回のデータベース更新操作時にデータベースファイルが生成される(ファイル名は起動時に指定したデータベース名と同一になる)。そのため、起動直後にファイルが存在しなくても問題ない。

6. データの登録

以下の構造を持つorder_recordsテーブルを作成する。

order_recordsテーブルの構造を示す図

以下の手順で、SQLを使用したデータ登録を実行する。

  1. データ登録用SQLの実行

    insert into文でデータを登録する。複数のSQL文をトランザクション(複数の操作を一つの処理単位にまとめる仕組み)として実行するため、begin transactionとcommitで処理全体を囲む。

    登録処理の開始前にbegin transaction;を実行し、すべての登録が完了後にcommit;を実行する。トランザクション内のすべての操作が成功した場合のみ、変更がデータベースに反映される。途中でエラーが発生した場合は、すべての変更が取り消される。

    begin transaction;
insert into order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2019, 10, 26,  'kaneko', 'orange A', 1.2, 10, datetime('now', 'localtime') );
insert into order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2019, 10, 26,  'miyamoto', 'Apple M',  2.5, 2, datetime('now', 'localtime') );
insert into order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2019, 10, 27,  'kaneko',   'orange B', 1.2, 8, datetime('now', 'localtime') );
insert into order_records (year, month, day, customer_name, product_name, unit_price, created_at) values( 2019, 10, 28,  'miyamoto',   'Apple L', 3, datetime('now', 'localtime') );
commit;

    datetime('now', 'localtime')関数は現在のローカル日時を取得し、YYYY-MM-DD HH:MM:SS形式の文字列として返す。

    order_recordsテーブルにデータを登録するINSERT文を実行した画面

    insert into文には、以下の2つの記法がある。

    1. 全カラム指定: テーブル定義の順序に従い、すべてのカラムの値を指定する。

    insert into order_records values( 1, 2019, 10, 26,  'kaneko', 'orange A', 1.2, 10, datetime('now', 'localtime'), NULL );

    2. カラム名指定: 値を設定するカラムを明示的に指定する。

    insert into order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 2019, 10, 26,  'miyamoto', 'Apple M',  2.5, 2, datetime('now', 'localtime') );

    カラム名指定の記法では、id列を省略することでautoincrementにより連番が割り当てられる。指定されていないカラムにはデフォルト値が適用される。本ガイドでは、autoincrementが設定された主キー列を省略するカラム名指定の記法を用いる。

7. データ整合性制約の検証

ここでは、データ整合性制約に違反する更新操作を試行し、SQLite 3による制約違反の検出を確認する。以下の例では、制約の動作を確認するため、id列を明示的に指定している。

主キー制約の検証(PRIMARY KEY)

begin transaction;
insert into order_records (id, year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 3, 2019, 10, 30, 'kaneko', 'banana', 10, 3, datetime('now', 'localtime') );
ROLLBACK;

既存のid値と重複するため、主キー制約(PRIMARY KEY)に違反し、エラーが発生する。

主キー制約違反によりエラーが発生した画面

エラー発生時の対処

データベース操作でエラーが発生した場合、ROLLBACKコマンドにより、トランザクション開始以降のすべての変更を取り消す。ROLLBACKは、データベースをトランザクション開始前の状態に戻す操作である。

NOT NULL制約の検証(not null)

begin transaction;
insert into order_records (id, year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 5, 2019, 10, 30,  NULL, 'melon', 10, 3, datetime('now', 'localtime') );
ROLLBACK;

customer_name列にはnot null制約が設定されているため、NULL値の挿入はできない。エラー発生後、ROLLBACKによりトランザクションを取り消す。

NOT NULL制約違反によりエラーが発生した画面

CHECK制約の検証

制約違反の例1: 年の範囲制約

begin transaction;
insert into order_records (id, year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 6, 1009, 10, 30,  'kaneko', 'melon', 10, 3, datetime('now', 'localtime') );
ROLLBACK;

CHECK ( year > 2008 )制約に違反するため、エラーが発生する。ROLLBACKにより変更を取り消す。

年の範囲制約違反によりエラーが発生した画面

制約違反の例2: 取引金額の制約

begin transaction;
insert into order_records (id, year, month, day, customer_name, product_name, unit_price, qty, created_at) values( 7, 2019, 10, 31,  'kaneko', 'strawberry', 4.6, 100000, datetime('now', 'localtime') );
ROLLBACK;

CHECK ( ( unit_price * qty ) < 200000 )制約に違反するため、エラーが発生する。ROLLBACKにより変更を取り消す。

取引金額の制約違反によりエラーが発生した画面

8. データの検索

ここでは、SQLを使用したデータ検索の例を示す。

全データの取得

SELECT * FROM order_records;
order_recordsテーブルの全データを取得した結果画面

条件付きデータ検索

SELECT * FROM order_records WHERE product_name = 'orange A';
product_nameがorange Aのデータを検索した結果画面 
SELECT * FROM order_records WHERE product_name LIKE 'orange%';

LIKE演算子は、文字列のパターン一致検索を行う。%は0文字以上の任意の文字列に一致するワイルドカードである。

product_nameがorangeで始まるデータを検索した結果画面 
SELECT * FROM order_records WHERE unit_price > 2;
unit_priceが2より大きいデータを検索した結果画面 
SELECT * FROM order_records WHERE qty > 9 AND customer_name = 'kaneko';
qtyが9より大きくcustomer_nameがkanekoのデータを検索した結果画面

9. データの更新

SQLを使用したデータ更新(update)の方法を示す。構文はUPDATE <table-name> SET <attribute-name>=<expression> WHERE <expression>である。

  1. 更新用SQLの実行

    UPDATE ... SET ...構文でデータを更新する。begin transactionとcommitで処理全体を囲む。

    begin transaction;
UPDATE order_records SET qty=12, updated_at=datetime('now', 'localtime')
WHERE id = 1;
commit;
    order_recordsテーブルのデータを更新するUPDATE文を実行した画面
  2. 更新結果の確認
    データ更新後の結果を確認した画面

10. データの削除

SQLを使用したデータ削除(delete)の方法を示す。構文はDELETE FROM <table-name> WHERE <expression>;である。

  1. 削除用SQLの実行 
    begin transaction;
DELETE FROM order_records
WHERE id = 2;
commit;
    order_recordsテーブルのデータを削除するDELETE文を実行した画面
  2. 削除結果の確認
    データ削除後の結果を確認した画面

11. データのエクスポート

  1. CSV形式に設定し、出力先ファイルを指定したうえで、SELECT文を実行する。 
    .mode csv
.output order_records.csv
SELECT * FROM order_records;
    データをCSV形式でorder_records.csvファイルに出力した画面
  2. 出力先を標準出力に戻す。 
    .output stdout
    出力先を標準出力に戻した画面
  3. エクスポートされたCSVファイルをMicrosoft Excelで開いて内容を確認する。
    ExcelでCSVファイルを開いた画面

出力形式の設定

SQLite 3では、以下の出力形式を選択できる。

異なる出力形式でデータを表示した画面

実行結果のファイル出力

SQLの実行結果を外部ファイルに保存する場合は、.outputコマンドを使用する。

12. クエリの実行計画の確認

SQLクエリの前にEXPLAINキーワードを付加することで、クエリの実行計画を確認できる。.explain onと.explain offコマンドで、実行計画の表示形式を切り替えることができる。

EXPLAINキーワードを使用してクエリの実行計画を表示した画面

13. SQLスクリプトの実行

.readコマンドは、指定したファイルに記述されたSQL文を順次実行する。

.read ＜ファイル名＞

14. データベース構造の確認

テーブル一覧の確認

データベース内のテーブル定義を確認するには、システムテーブルsqlite_masterとsqlite_temp_masterを使用する。

以下のコマンドで内容を確認する。

SELECT * FROM sqlite_master;
SELECT * FROM sqlite_temp_master;

これらのシステムテーブルに対しては、DROP TABLE、UPDATE、INSERT、DELETE操作は実行できない(読み取り専用)。

sqlite_masterテーブルの内容を表示した画面

インデックス一覧の確認

SQLite 3の.indicesコマンドにより、インデックスの一覧を確認できる。

CREATE INDEX idx1 ON order_records(customer_name);
.indices
インデックスを作成して.indicesコマンドで確認した画面

15. データベースのバックアップと復元

.dumpコマンドにより、データベースの内容をSQL文の形式で出力する。特定のテーブルのみを対象とする場合は、.dump テーブル名を実行する。

デフォルトでは、出力内容は標準出力(画面)に表示される。

.dumpコマンドを実行してデータベースの内容を画面に表示した画面

出力内容をファイルに保存する場合は、.dumpの実行前に.output ファイル名を実行する。

.dumpコマンドを実行してデータベースの内容をファイルに保存した画面

保存したファイルからデータを復元する場合は、.read ファイル名を実行する。

.readコマンドを実行してバックアップファイルからデータを復元した画面