金子邦彦研究室情報工学全般SQLite 3 の使い方SQLite 3の主要機能

SQLite 3の主要機能

SQLite 3の主要機能を紹介する.

目次

  1. 前準備
  2. この Web ページで行うこと
  3. SQLite 3 のデータベースの新規作成
  4. 既存のデータベースを開く
  5. テーブル定義と一貫性制約
  6. テーブルへの行の挿入
  7. 一貫性制約に違反する更新ができないことの確認
  8. SQL 問い合わせ
  9. SQL を用いた更新
  10. SQL を用いた行の削除
  11. テーブルの CSV ファイル形式でのエクスポート
  12. .explain による SQL 実行計画の表示
  13. ファイルに入った SQL の実行
  14. データベーススキーマの表示
  15. テーブルのテキストファイル形式でのダンプとリストア

Windows でのSQLite 3インストール別の Web ページ で説明している.

1. 前準備

SQLite 3 について: 別ページ »にまとめ

あらかじめ決めておく事項

このページでは,データベースの生成を行うので, 生成するデータベースのデータベース名を決めておくこと. このページでは,次のように書く.

2. この Web ページで行うこと

3. SQLite 3 のデータベースの新規作成

Windows での sqlite3 の起動手順(例)

以下の手順で,新規の SQLite 3 のデータベースファイル「C:\SQLite\mydb」を作成する

  1. Windows のコマンドプロンプトで,次の操作を行う.

    SQLite 3 のデータベース・ディレクトリ C:\SQLite に移る.

    C:
    cd C:\SQLite 
    

    [image]
  2. sqlite3.exe の起動

    データベースを新規作成したいので, sqlite3.exe の起動で, 新しいデータベースファイル名を指定する(まだ存在しないファイル名を付ける).

    データベースファイル名は自由に付けてよいが、アルファベットのみを使うのが良い.

    例えば,データベースファイル名 mydb のデータベースファイルを扱いたい場合は次のような操作になる.

    .\sqlite3.exe mydb
    

    [image]

上記の操作で,mydb というデータベースファイル名を持つファイルが無いので,自動的にデータベースファイルが生成される.

※ データベースファイルが生成されるタイミングは,テーブルを定義するなど,データベースの更新を行ったときなので,sqlite3 の起動時点で,データベースファイルが出来ていなくても慌てないこと.

Ubuntu での sqlite3 の起動手順(例)

以下の手順で,新規の SQLite 3 のデータベースファイル「/tmp/mydb」を作成する

  1. Ubuntu の端末で,次の操作を行う.

    SQLite 3 のデータベース・ディレクトリ /tmp に移る.

    cd /tmp
    
  2. sqlite3 の起動

    データベースを新規作成したいので, sqlite3 の起動で, 新しいデータベースファイル名を指定する(まだ存在しないファイル名を付ける).

    データベースファイル名は自由に付けてよいが、アルファベットのみを使うのが良い.

    例えば,データベースファイル名 mydb のデータベースファイルを扱いたい場合は次のような操作になる.

    sqlite3 /tmp/mydb
    

    [image]

上記の操作で,mydb というデータベースファイル名を持つファイルが無いので,自動的にデータベースファイルが生成される.

※ データベースファイルが生成されるタイミングは,テーブルを定義するなど,データベースの更新を行ったときなので,sqlite3 の起動時点で,データベースファイルが出来ていなくても慌てないこと.

ヘルプの表示

.help」で,ヘルプが表示される.

[image]

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

現在使用中のデータベースについての,文字のエンコーディングの確認

PRAGMA encoding;」で,エンコーディングが表示される.

データベースの消去

データベースを消したいときは,対応するデータベースファイルを消すだけです.

SQLite 3の終了

.exit」で終了.

[image]

4. 既存のデータベースを開く

以下の手順で,既存のデータベースファイル「C:\SQLite\mydb」を開く

  1. Windowsコマンドプロンプトで,次の操作を行う.

    SQLite 3 のデータベース・ディレクトリ C:\SQLite に移る.

    C:
    cd C:\SQLite 
    

    [image]
  2. sqlite3.exe の起動

    sqlite3.exe の起動時に,使用したいデータベースファイルのファイル名を指定する.例えば,mydbというファイル名のデータベースがすでに生成済みで,これを使いたい場合には,「.\sqlite3 mydb」のようになる.

    .\sqlite3.exe mydb
    

    [image]

5. テーブル定義と一貫性制約

SQL を用いて,order_records テーブルを定義し,一貫性制約を記述する.

リレーショナル・スキーマ (relational schema): 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 ) );
    

    [image]

    このとき C:\SQLite に,データベースファイル mydb ができる.

    [image]

    ※ SQLite では,データベースが始めて使うときに,自動的にデータベースファイルが生成される.データベースファイル名は,データベース名と同じになる.

    ※ データベースファイルが生成されるのは,テーブルを定義するなど,データベースの更新を行ったときなので,最初,sqlite3 を起動したとき,データベースファイルが無くてもあわてないこと.

6. テーブルへの行の挿入

次のような order_records テーブルを作る.

[image]

以下の手順で,SQL を用いてorder records テーブルへの行の挿入を行う

  1. SQL の実行

    「insert into ...」は行の挿入.ここには 4つの SQL 文を書き, 「begin transaction」と「commit」で囲む.

    ※ つまり, 挿入の前に begin transaction; を実行し,一連の挿入が終わったら commit; を実行する.

    begin transaction;
    insert into order_records values( 1, 2019, 10, 26,  'kaneko', 'orange A', 1.2, 10, datetime('now', 'localtime'), NULL );
    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') は現在日時の取得.DATETIME型は、YYYY-MM-DD HH:MM:SS形式.

    [image]

    insert into には 2つの方法がある.

    ■ 属性の値を,テーブル定義の順に全て並べる

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

    ■ 属性の値の並び方を,属性名を使って明示的に指定する

    このとき,属性値を省略すると,テーブル定義のときに指定されたデフォルト値が使われる

    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') );
    

7. 一貫性制約に違反する更新ができないことの確認

ここでは,一貫性制約に違反するような更新を試みる.データベース管理システムソフトウェアが一貫性を維持するので, 一貫性制約に違反するような更新はできない.

主キー制約 (PRIMARY KEY)

begin transaction;
insert into order_records values( 3, 2019, 10, 30, 'kaneko', 'banana', 10, 3, datetime('now', 'localtime'), NULL );
ROLLBACK;

※ すでに属性 id には 3 という値がある. 主キー制約「PRIMARY KEY」に違反.

[image]

非空制約 (not null)

begin transaction;
insert into order_records values( 3, 2019, 10, 30,  NULL, 'melon', 10, 3, datetime('now', 'localtime'), NULL );
ROLLBACK;

※ 非空制約「not null」. 属性 customer_name には NULL を入れることができない.

[image]

その他の一貫性制約

一貫性制約に違反する例

begin transaction;
insert into order_records values( 6, 1009, 10, 30,  'kaneko', 'melon', 10, 3, datetime('now', 'localtime'), NULL );
ROLLBACK;

※ 制約「CHECK ( year > 2008 )」に違反

[image]

一貫性制約に違反する例

begin transaction;
insert into order_records values( 7, 2019, 10, 31,  'kaneko', 'strawberry', 4.6, 100000, datetime('now', 'localtime'), NULL );
ROLLBACK;

※ 制約「CHECK ( ( unit_price * qty ) < 200000 ) );」に違反

[image]

8. SQL 問い合わせ

ここでは,SQL を用いた問い合わせの実行例を示す. SQL 問い合わせの詳細については,別の Web ページで説明する.ここでは,テーブルの中身を確認して欲しい.

テーブルの全ての行の表示

SELECT * FROM order_records;

[image]

条件を満足する行のみの表示

SELECT * FROM order_records WHERE product_name = 'orange A';

[image]

SELECT * FROM order_records WHERE product_name LIKE 'orange%';

LIKE は文字列のパターンマッチ

[image]
SELECT * FROM order_records WHERE unit_price > 2;

[image]
SELECT * FROM order_records WHERE qty > 9 AND customer_name = 'kaneko';

[image]

9. SQL を用いた更新

SQL を用いたデータの更新 (update)の実行例を示す. 「UPDATE <table-name> SET <attribute-name>=<expression> WHERE <expression>」の形をした SQL は,データの更新である. この SQL 文を 「begin transaction」と「commit」で囲む.

  1. SQL の実行 (Execute SQL)

    「UPDATE ... SET ...」は更新.ここには 1つの SQL 文を書き, 「begin transaction」と「commit」で囲む.

    begin transaction;
    UPDATE order_records SET qty=12, updated_at=datetime('now', 'localtime')
    WHERE id = 1;
    commit;
    

    [image]
  2. order_records テーブルの中身を確認

    [image]

10. SQL を用いた行の削除

SQL を用いた行の削除 (delete row(s))の実行例を示す.

DELETE FROM <table-name> WHERE <expression>;」の形をした SQL は行の削除である. この SQL 文を 「begin transaction」と「commit」で囲む.

  1. SQL の実行

    begin transaction;
    DELETE FROM order_records
    WHERE id = 2;
    commit;
    

    [image]
  2. order_records テーブルの中身を確認

    [image]

11. テーブルの CSV ファイル形式でのエクスポート

  1. 出力モードを CSV に設定し,出力先をファイルに指定した後,「SELECT * FROM ...」を実行
    .mode csv
    .output order_records.csv
    SELECT * FROM order_records;
    

    [image]
  2. 出力先をファイルにしたままでは混乱するので,画面(標準出力)に出力するように戻しておく.
    .output stdout
    

    [image]
  3. 念のため Microsoft Excel で開いて確認してみる

    [image]

.mode による出力モードの切り替え

主な出力モードは次の通りです

[image]

.output による実行結果のファイル出力

SQL の実行結果をファイルに出力するには,「.output」を使う.

12. .explain による SQL 実行計画の表示

SQL 文の前に「explain」を付けると,SQL は実行されずに,実行計画が表示される. これと別に「.explain」というコマンドがあります. 「.explain on」と「.explain off」により,表示形式が変わります(表示形式が変わるだけです).

[image]

13. ファイルに入った SQL の実行

.read <ファイル名>

14. データベーススキーマの表示

テーブル一覧の表示

データベース内のテーブル一覧を表示するには,sqlite_master, sqlite_temp_masterという名前が付いた特別なテーブルを使う.

データベーススキーマを見たいときは,次のような操作を行う.

SELECT * FROM sqlite_master;
SELECT * FROM sqlite_temp_master;

※ sqlite_master, sqlite_temp_master に,DROP TABLE, UPDATE, INSERT, DELETE 操作を行うことは許されていません

[image]

インデックス名の一覧表示

SQLite 3の「.indices」コマンドを使い,インデックス名を表示できる.

CREATE INDEX idx1 ON order_records(customer_name);
.indices

[image]

15. テーブルのテキストファイル形式でのダンプとリストア

.dump を使い,データベースのダンプ.特定のテーブルだけをダンプしたいときは,「.dump commodity」のようにする

単にダンプを実行すると,スクリーンにダンプ結果が出力される.

[image]

ダンプ結果をファイルに保存したいときは,先に「.output <ファイル名>」を実行する.

[image]

ファイルに保存されたダンプ結果をリストアしたいときは,「.read ファイル名」を実行する.

[image]