トップページ -> 研究道具箱と教材 -> SQLite データベース・ブラウザ (SQLite Database Browser) を使ってみる
[サイトマップへ]  

SQLite データベース・ブラウザ (SQLite Database Browser) を使ってみる

SQLite データベース・ブラウザ (SQLite Database Browser) とは， Windows, OS-X, Linux で動作する　SQLite のプログラムです． この Web ページでは，SQLite データベース・ブラウザ (SQLite Database Browser) バージョン 2.0b1 を使って下記の操作を行う手順を，図解で説明します．

SQLite の SQL に関する詳しい説明は：

SQliteが認識できる SQL について，日本語でのすばらしい説明が http://net-newbie.com/sqlite/lang.html にあります．

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

この Web ページでは，データベースの生成を行いますので， 生成するデータベースのデータベース論理名を決めておくこと． この Web ページでは，次のように書きます．

この Web ページで行うこと

SQLite Database Browser バージョン 2.0b1 の Windows 版のインストール (Install SQLite Database Browser b2.01 Windows binary)

ここでは Windows でのインストール手順を説明する．（ソースコードが配布されているので UNIX へのインストールは難しくないだろう）．

  1. ディレクトリ C:\SQLite を使う

    ここに SQLite Database Browser とデータベースファイルを置くことにする．

  2. SQLite Database Browser の Windows 版の入手

    http://sqlitebrowser.sourceforge.net/index.html

    「SOURCEFORGE PROJECT PAGES」をクリック

  3. 「Download Now!」をクリック

  4. sqlitebrowser-200_b1_win.zip の解凍

    解凍するといくつかのファイルができる．

  5. 解凍して出来たファイルを C:/SQLite にコピー

SQLite Database Browser の起動と終了 (Start and end SQLite Database Browser)

  1. SQLite Database Browser の起動 (Start SQLite Database Browser)

    「SQLite Database Browser 2.0 b1.exe」 をダブルクリック (double click "SQLite Database Browser 2.0 b1.exe")

    新しいウインドウが開く．(A New window appears)

  2. 終了 (End SQLite Database Browser)

    「File」→ 「Exit」で終了．

SQLite Database Browser で SQLite データベースの新規作成 (Create a new database using SQLite Database Browser)

以下の手順で，新規の SQLite データベースファイル「C:\SQLite\mydb」を作成する． (Create a new SQLite database file 'C:\SQlite\mydb')

  1. データベースを生成したいので「File」→ 「New Database」と操作する

  2. 「保存する場所」としてC:\SQLiteを，「ファイル名」としてデータベース論理名 mydb を指定し， 「保存 」をクリック

    データベース名は自由につけてよいが，日本語は避ける．

  3. テーブル定義用のウインドが開く．

    「Cancel」をクリックしたとしても，あとでテーブルを定義できるので，問題ない． あまり悩まないこと．

データベースの消去

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

SQLite Database Browser で既存のデータベースを開く (Open an existing database using SQLite Database Browser)

以下の手順で，既存の SQLite データベースファイル「C:\SQLite\mydb」を開く． (Open an existing SQLite database file 'C:\SQlite\mydb')

  1. 「File」→ 「Open Database」

  2. データベースファイル C:\SQLite\mydb を選び， 「開く」をクリック (Click '開く' after choosing the database file "C:\SQLite\mydb")

  3. テーブルの一覧 (List of Tables) が表示されるので確認する (List of tables appears)

SQL を用いたテーブル定義と一貫性制約の記述 (Table definition and integrity constraint specification using SQL)

SQL を用いて，order_records テーブルを定義し，一貫性制約を記述する． (Define 'order_records' table and specify integrity constrants of the table using SQL) 

リレーショナル・スキーマ (relational schema): order_records(id, year, month, day, customer_name, product_name,  unit_price, qty)

  1. 「Execute SQL」をクリック (Click "Execute SQL")

  2. order_records テーブルの定義 (Define table 'order_records')

    次の SQL を入力し，「Execute Query」をクリック (Write the following SQL, and click 'Execute Query'). ※ 「SQL string」のウインドウには，SQL 文を書くことができる． In the 'SQL string' window, you can write down SQL program(s). 

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

  3. コンソールの確認 (Inspect console)

    エラーメッセージが出ていないことを確認

SQL を用いたテーブルへの行の挿入 (Insert rows into a table using SQL)

次のような order_records テーブルを作る． (Construct table 'order_records')

以下の手順で，SQL を用いてorder records テーブルへの行の挿入を行う (Insert rows into table 'order_records' using SQL)

  1. SQL 文の記述

    「INSERT INTO ...」は行の挿入．ここには 4つの SQL 文を書き， 「BEGIN TRANSACTION」と「COMMIT」で囲む． ("INSERT INTO ..." means inserting a row into a table. Four SQL statements are wrote).

    ※ つまり， 挿入の前に BEGIN TRANSACTION; を実行し，一連の挿入が終わったら COMMIT; を実行する．(Issue "BEGIN TRANSACTION" before database update and "COMMIT" after database update). 

    BEGIN TRANSACTION;
INSERT INTO order_records VALUES( 1, 2009, 10, 26,  'kaneko', 'orange A', 1.2, 10, datetime('now'), NULL );
INSERT INTO order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) VALUES( 2009, 10, 26,  'miyamoto', 'Apple M',  2.5, 2, datetime('now') );
INSERT INTO order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) VALUES( 2009, 10, 27,  'kaneko',   'orange B', 1.2, 8, datetime('now') );
INSERT INTO order_records (year, month, day, customer_name, product_name, unit_price, created_at) VALUES( 2009, 10, 28,  'miyamoto',   'Apple L', 3, datetime('now') );
COMMIT;

    INSERT INTO には 2つの方法がある．(Two styles of "INSERT INTO")

    ■ 属性の値を，テーブル定義の順に全て並べる (List all attribute values. The order is the same as its table definition) 

    INSERT INTO order_records VALUES( 1, 2009, 10, 26,  'kaneko', 'orange A', 1.2, 10, datetime('now'), NULL );

    ■ 属性の値の並び方を，属性名を使って明示的に指定する (Specify the order of attribute values using attribute name list)

    このとき，属性値を省略すると，テーブル定義のときに指定されたデフォルト値が使われる (defaults values are used) 

    INSERT INTO order_records (year, month, day, customer_name, product_name, unit_price, qty, created_at) VALUES( 2009, 10, 26,  'miyamoto', 'Apple M',  2.5, 2, datetime('now') );

  2. 複数の SQL 文の一括実行 (Execute multiple SQL statements)

    複数の SQL 文を一括実行したいので，「Execute query」のボタンをクリックする． Click "Execute Query"

  3. 「Script Output」ウインドウの確認 (Inspect "Script Output" window)

    エラーメッセージが出ていないことを確認

  4. 「Database Structure」をクリックし order_table が増えていることを確認 (Click "Database Structure" and inspect the table 'order_table')

  5. 「Browse Data」をクリックし，「order_table」を選び，テーブルの中身を確認 (Click "Browse Data" and select 'order_table' and inspect the table 'order_table')

一貫性制約に違反する更新ができないことの確認 (Integrity constraint violation is not permitted when database update)

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

主キー制約 (PRIMARY KEY) 

BEGIN TRANSACTION;
INSERT INTO order_records VALUES( 3, 2009, 10, 30, 'kaneko', 'banana', 10, 3, datetime('now'), NULL );
ROLLBACK;

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

非空制約 (NOT NULL) 

BEGIN TRANSACTION;
INSERT INTO order_records VALUES( 6, 2009, 10, 30,  NULL, 'melon', 10, 3, datetime('now'), NULL );
ROLLBACK;

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

その他の一貫性制約

一貫性制約に違反する例 

BEGIN TRANSACTION;
INSERT INTO order_records VALUES( 6, 1009, 10, 30,  'kaneko', 'melon', 10, 3, datetime('now'), NULL );
ROLLBACK;

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

一貫性制約に違反する例 

BEGIN TRANSACTION;
INSERT INTO order_records VALUES( 7, 2009, 10, 31,  'kaneko', 'strawberry', 4.6, 100000, datetime('now'), NULL );
ROLLBACK;

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

SQL 問い合わせの発行と評価結果の確認 (Issue SQL queries and inspect the results)

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

テーブルの全ての行の表示 (List all rows of a table) 

SELECT * FROM order_records;

条件を満足する行のみの表示 (List the rows which satisfy a given condition) 

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


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

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

SELECT * FROM order_records WHERE unit_price > 2;


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

SQL を用いた更新 (Update using SQL)

SQL を用いたデータの更新 (update)の実行例を示す． 「UPDATE <table-name> SET <attribute-name>=<expression> WHERE <expression>」の形をした SQL は，データの更新である． この SQL 文を 「BEGIN TRANSACTION」と「COMMIT」で囲む． ("UPDATE ... SET ..." means database update).

  1. SQL 文の記述

    「UPDATE ... SET ...」は更新．ここには 1つの SQL 文を書き， 「BEGIN TRANSACTION」と「COMMIT」で囲む． ("UPDATE ... SET ..." means database update. 

    BEGIN TRANSACTION;
UPDATE order_records SET qty=12, updated_at=datetime('now')
WHERE id = 1;
COMMIT;

  2. 複数の SQL 文の一括実行 (Execute multiple SQL statements)

    複数の SQL 文を一括実行したいので，「Execute query」のボタンをクリックする． Click "Execute Query"

  3. 「Script Output」ウインドウの確認 (Inspect "Script Output" window)

    エラーメッセージが出ていないことを確認

  4. order_records テーブル のブラウズ (Browse table order_records)

SQL を用いた行の削除 (Delete row(s) using SQL)

「DELETE FROM ... WHERE ...」は行の削除．ここには 1つの SQL 文を書き， 「BEGIN TRANSACTION」と「COMMIT」で囲む． ("DELETE FROM ... WHERE ..." means deletion of rows). 

BEGIN TRANSACTION;
DELETE FROM order_records
WHERE id = 2;
COMMIT;

SQL を用いた行の削除 (delete row(s))の実行例を示す． SQL は「DELETE FROM <table-name> WHERE <expression>;」の形になる．

SQLite Database Browser を用いたデータのブラウズ (Browse Data using SQLite Database Browser)

テーブルの CSV ファイル形式でのインポートとエクスポート (Export and import table data)

CSV ファイルからリストアしたり，CSVファイル形式でダンプするために， SQLite Database Browser の「Import Database」, 「Export Database」 を使う

テーブルの中身を CSV ファイル形式でダンプ

  1. 「File」 →「Export」 →「Table as CSV file」

  2. テーブルを選び，「Export」をクリック

  3. ディレクトリ名とファイル名を設定し，「保存」をクリック

  4. 念のため Microsoft Excel で開いて確認してみる

テーブルの中身を CSV ファイルからリストア

  1. CSV ファイルを準備しておく

  2. 「File」 →「Import」 →「Table from CSV file」

  3. ディレクトリ名とファイル名を設定し，「開く」をクリック

  4. テーブル名の指定など

    テーブル名を指定し，「Create」をクリック．

    CSV ファイルの第一行目に属性名を書いている場合には，「Extract field names from first line」をチェック

  5. （オプション）既存のテーブルにマージしたい場合

    上記の操作では，新しい newdata テーブルが定義され，そこにデータがリストアされました． 既存のテーブル（例えば order_records）にマージしたい場合には，次のような操作を行います． 

    INSERT INTO order_records SELECT * FROM newdata;
DROP TABLE newdata;

    説明

    order_records の中身を確認すると次の通り．満足！