SQLite 3の主要機能

【概要】

SQLite 3はリレーショナルデータベース管理システム。本ページでは、データベース環境の構築、操作、データ整合性の確保、クエリの実行計画の確認までを解説する。

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

【目次】

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

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

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

本ページでは、以下の設定を前提とする。

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

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

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

  1. コマンドプロンプトで、データベースファイルを配置するディレクトリC:\SQLiteに移動する。 
    cd /d C:\SQLite
    コマンドプロンプトでC:\SQLiteディレクトリに移動した画面
  2. sqlite3.exeの起動

    起動時に、データベースファイル名（半角英数字のみ）を指定する。mydbとする場合は次のとおり。

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

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

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

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

  1. 端末で、データベースファイルを配置するディレクトリ/tmpに移動する。 
    cd /tmp
  2. SQLite 3の起動

    起動時に、データベースファイル名（半角英数字のみ）を指定する。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コマンドを実行して終了した画面

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

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

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

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

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

テーブルのスキーマ定義: order_records(id, year, month, day, customer_name, product_name, unit_price, qty, created_at, updated_at)

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データベースファイル

5. データの登録

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

SQLでデータを登録する。複数のSQL文をトランザクション（複数の操作を一つの処理単位にまとめる仕組み）として実行するため、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が設定された主キー列を省略するカラム名指定の記法を用いる。

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

データ整合性制約に違反する操作を試み、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値と重複するため、主キー制約に違反し、エラーが発生する。

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

エラー発生時の対処

エラーが発生した場合、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 )制約に違反するため、エラーが発生する。

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

制約違反の例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 )制約に違反するため、エラーが発生する。

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

7. データの検索

全データの取得

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のデータを検索した結果画面

8. データの更新

SQLでデータを更新する。構文はupdate ＜テーブル名＞ set ＜属性名＞=＜式＞ where ＜条件＞である。begin transactionとcommitで処理全体を囲む。

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

9. データの削除

SQLでデータを削除する。構文はdelete from ＜テーブル名＞ where ＜条件＞;である。

begin transaction;
delete from order_records
where id = 2;
commit;
order_recordsテーブルのデータを削除するDELETE文を実行した画面
データ削除後の結果を確認した画面

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

  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ファイルを開いた画面

出力形式の設定

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

実行結果のファイル出力

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

SQLクエリの前にEXPLAIN QUERY PLANを付けると、クエリの実行計画（SQLite 3がどのようにデータを探索するかの計画）を確認できる。.eqp onと.eqp offで、実行計画の自動表示を切り替えられる。

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

12. SQLスクリプトの実行

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

.read ＜ファイル名＞

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

テーブル一覧の確認

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

select * from sqlite_master;
select * from sqlite_temp_master;

これらのシステムテーブルは読み取り専用で、drop table・update・insert・deleteは実行できない。

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

インデックス一覧の確認

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

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

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

.dumpコマンドにより、データベースの内容をSQL文の形式で出力する。特定のテーブルのみを対象とする場合は.dump テーブル名を実行する。デフォルトでは標準出力（画面）に表示される。

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

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

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

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

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