トップページ -> 研究道具箱と教材 -> SQLite のインストールと使い方 -> PupSQLite を使ってみる
[サイトマップへ]  

PupSQLite を使ってみる

PupSQLite は，SQLitep データベースを操作する機能を持った優れたソフトウエア． 下記の操作を行う手順を，図解で説明します．

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

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

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

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

この Web ページで行うこと

PupSQLite の起動と終了 (Start and end PupSQLite)

  1. PupSQLite の起動 (Start PupSQLite)

    「PupSQLite.exe」 をダブルクリック (double click "PupSQLite.exe")

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

  2. 終了 (End PupSQLite)

    「ファイル」→ 「終了」で終了．

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

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

  1. 「ファイル」→ 「開く」

  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. 「SQL を記入して実行のアイコン」をクリック (Click "SQL を記入して実行" icon)

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

    次の SQL を入力し，「実行」をクリック (Write the following SQL, and click '実行'). ※ 「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 pop up window)

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

  4. テーブル一覧に，order_records テーブルが表示されるので確認する (Table 'order_records' appears in 'テーブル一覧')

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 文を一括実行したいので，「実行」のボタンをクリックする． Click "実行"

  3. ポップアップウインドウの確認 (Inspect pop up window)

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

  4. テーブルの一覧の「order_records」をダブルクリック (Double-click 'order_records)'

  5. order_records テーブルの中身を確認 (Inspect the table 'order_table')

    ※ テーブルの中身がうまく表示されないときは，データウインドウを閉じて，もう１度テーブルの一覧の「order_records」をダブルクリック

一貫性制約に違反する更新ができないことの確認 (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 文を一括実行したいので，「実行」のボタンをクリックする． Click "実行"

  3. ポップアップウインドウの確認 (Inspect pop up window)

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

  4. テーブルの一覧の「order_records」をダブルクリック (Double-click 'order_records)'

  5. order_records テーブルの中身を確認 (Inspect the table 'order_table')

    ※ テーブルの中身がうまく表示されないときは，データウインドウを閉じて，もう１度テーブルの一覧の「order_records」をダブルクリック

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

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

「DELETE FROM <table-name> WHERE <expression>;」の形をした SQL は行の削除である． この SQL 文を 「BEGIN TRANSACTION」と「COMMIT」で囲む． ("DELETE FROM ... WHERE ..." means deletion of rows).

  1. SQL 文の記述 

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

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

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

  3. ポップアップウインドウの確認 (Inspect pop up window)

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

  4. テーブルの一覧の「order_records」をダブルクリック (Double-click 'order_records)'

  5. order_records テーブルの中身を確認 (Inspect the table 'order_table')

    ※ テーブルの中身がうまく表示されないときは，データウインドウを閉じて，もう１度テーブルの一覧の「order_records」をダブルクリック

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

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

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

  1. 「ファイル」 →「現在の状態を CSV に出力」 →「カンマ区切り (CSV)」

  2. 保存する場所とファイル名の設定

  3. ポップアップウインドウの確認

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

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

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

  2. 「ファイル」 →「インポート」 →「ファイルからインポート」

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

  4. 既存のテーブルにマージしたい場合

    既存のテーブル（例えば order_records）にマージしたい場合には，次のような操作を行います．

    まず，「既存のテーブル」をチェックし，「OK」をクリック．

    order_records テーブルをクリックし， 「①テーブルの選択」をクリックした後， 「追加実行」をクリック．

    order_records テーブルに２行増える．

日本語を試してみる

テーブルのデータとして日本語を試す（属性名やテーブル名を日本語にするわけではない）

  1. SQL 文の記述 

    BEGIN TRANSACTION;
INSERT INTO order_records VALUES( 100, 2010, 2, 1, 'kaneko', 'コンニチハ', 1, 1, datetime('now'), NULL );
INSERT INTO order_records VALUES( 101, 2010, 2, 1, 'kaneko', '① ② Ｉ Ⅱ ㍉ ㌢ ㈱', 1, 1, datetime('now'), NULL );
INSERT INTO order_records VALUES( 102, 2010, 2, 1, 'kaneko', '∥ ～ － ￠ ￡ ￢', 1, 1, datetime('now'), NULL );
INSERT INTO order_records VALUES( 103, 2010, 2, 1, 'kaneko', '表 十 構', 1, 1, datetime('now'), NULL );
INSERT INTO order_records VALUES( 104, 2010, 2, 1, 'kaneko', '〒', 1, 1, datetime('now'), NULL );
COMMIT;

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

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

  3. ポップアップウインドウの確認 (Inspect pop up window)

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

  4. テーブルの一覧の「order_records」をダブルクリック (Double-click 'order_records)'

  5. order_records テーブルの中身を確認 (Inspect the table 'order_table')

    ※ テーブルの中身がうまく表示されないときは，データウインドウを閉じて，もう１度テーブルの一覧の「order_records」をダブルクリック