SQLiteman のインストールと利用（Windows 上）

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

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

• データベース名: hoge.db

  データベース名は，自由に決めてよいが，半角文字（つまり英字と英記号）を使い，スペースを含まないこと，

• SQLiteman プログラムを置くディレクトリ:
  • Windows の場合：　C:\SQLite

  自由に決めてよいが，半角文字（つまり英字と英記号）を使い，スペースを含まないこと，

この Web ページで行うこと

• Sqliteman Windows 版のインストール

  インストール・ディレクトリ: C:\SQLite

• SQLiteman の起動と終了
• SQLiteman で SQLite 3 のデータベースの新規作成
• SQLiteman で既存のデータベースを開く
• SQL を用いたテーブル定義と一貫性制約の記述

  リレーショナル・スキーマ:

  orders(id, year, month, day, customer_name, product_id, qty, created_at)

  SQL プログラム:

  create table orders (
      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_id    INTEGER  not null,
      qty           INTEGER  not null DEFAULT 1 CHECK ( qty > 0 ),
      created_at    DATETIME not null );
  

• SQL を用いたテーブルへの行の挿入

  begin transaction;
  insert into orders values( 1001, 2022, 1, 26,  'kaneko',   1, 10, datetime('now', 'localtime') );
  insert into orders values( 1002, 2022, 1, 26,  'miyamoto', 2, 2, datetime('now', 'localtime') );
  insert into orders values( 1003, 2022, 1, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
  insert into orders values( 1004, 2022, 1, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
  commit;
  

  「now」は，現在の日時を取得する SQLite の関数

  「datetime」は，日時のデータを「YYYY-MM-DD HH:MM:SS」形式の文字列に変換する SQLite の関数

• 一貫性制約に違反する更新ができないことの確認
• SQL 問い合わせの発行と評価結果の確認

  SELECT * FROM orders;
  SELECT * FROM orders WHERE day = 26;
  SELECT * FROM orders WHERE customer_name = 'kaneko';
  SELECT * FROM orders WHERE unit_price > 2;
  

• SQL を用いた更新

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

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

• SQL を用いた行の削除

  「DELETE FROM ... WHERE ...」は行の削除．ここには 1つの SQL 文を書き， 「begin transaction」と「commit」で囲む．

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

• SQLiteman を用いたデータのブラウズ
• テーブルの CSV ファイル形式データのインポート

Sqliteman Windows 版のインストール

1. 新しいディレクトリ C:\SQLite を作る

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

   
2. Sqliteman を入手したい．SourceForge のウェブページを開く

   https://sourceforge.net/projects/sqliteman/

3. 「Files」をクリック
   
4. 「sqliteman」をクリック
   
5. 最新版が欲しいので，「1.2.2」をクリック
   
6. Windows 版が欲しいので「Sqliteman-1.2.2-win32.zip」をクリック
   
7. ダウンロードが始まる
   
8. ダウンロードした .zip ファイルを展開（解凍）する．

   以下、C:\SQLiteに展開（解凍）したとして説明を続ける

   Windows での展開（解凍）に便利な 7-Zip: 別ページ »で説明

   
9. 確認のため、sqliteman.exe を試しに実行してみる．
   

   新しい画面が開くので確認する．

   

Ubuntu での Sqliteman のインストール (Install Sqliteman on Ubuntu)

Ubuntu での Sqliteman のインストールは，次のコマンドにより行う．

sudo apt -y update
sudo apt -y install sqliteman

SQLiteman の起動と終了

1. SQLiteman の起動

   * Windows での SQLiteman の起動例

   Windows での操作例

   

   SQLiteman の新しいウインドウが開く．

   

   * Ubuntu での SQLiteman の起動例

   端末を開き，「sqliteman」

   

   SQLiteman の新しいウインドウが開く．

   
2. ヘルプの表示

   「Help」→ 「Help Content」でヘルプが表示される．

   
   
3. 終了

   「File」→ 「Exit」で終了．

   

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

以下の手順で，新しいデータベースを作成する．その結果，データベースファイルができる．

1. sqliteman を起動する

   Windows での操作例

   
2. データベースを生成したいので 「File」→ 「New」 と操作する
   
3. データベースの新規作成を開始する

   * Windows での実行例（データベースファイル名を「hoge.db」にする場合） ディレクトリと、データベースファイル名を指定．「保存」をクリック

   * 以下の例では「C:\SQLite\hoge.db」を指定している）．

   * データベースファイル名は何でも良いが、英語の名前がよい．

   * データベースを新規作成したいときは，データベースファイル名として「新しい」ものを指定すること

   

   * Ubuntu での実行例（データベースファイル名を「mydb」にする場合）

   データベースファイル名である mydb を指定し， 「保存」をクリック

   * データベースファイル名は何でも良いが、英語の名前がよい．

   * データベースを新規作成したいときは，データベースファイル名として「新しい」ものを指定すること

   
   

SQLiteman で既存のデータベースを開く

すでに作成済みのデータベースを，下記の手順で開くことができる．

以下の手順で，既存のデータベースファイルを開く．

1. sqliteman を起動する

   Windows での操作例

   
2. データベースをオープンしたいので，「File」→「Open」と操作する．
   
3. オープンしたいデータベースのデータベースファイル名（下の図では hoge.db）を選び， 「開く」をクリック
   
4. データベースの中身が表示されるので確認する
   
5. テキスト・エンコーディングの設定を確認する

   まず，「Pragmas」をクリック．

   

   encodingの行に「UTF-8」のように表示されている．

   

   * もし，データベースの文字のエンコーディングを変えたいときは， SQLiteman のようなグラフィカルなツールを使うのではなく， sqlite.exe を起動し「PRAGMA encoding=...;」で変える方がずっと簡単でしょう． 例えば「UTF-16le」などに変えたいなど．

6. もしテーブルを作成済みならば、「Tables」を展開すると，テーブルの一覧 (List of Tables) が表示されるので確認する
   
7. もしテーブルを作成済みならば、「System Catalogue」を展開し，「sqlite_master」をクリックすると，データベース・スキーマ (database schema) が表示される
   

SQL を用いたテーブル定義と一貫性制約の記述

SQL を用いて，orders テーブルを定義し，一貫性制約を記述する．

リレーショナル・スキーマ

orders(id, year, month, day, customer_name, product_id, qty, created_at)

1. sqliteman を実行する．
   
2. データベースをオープンしたいので，「File」→「Open」と操作する．
   
3. オープンしたいデータベースのデータベースファイル名（下の図では mydb）を選び， 「開く」をクリック．
   
4. テーブル定義

   SQL編集画面に，次のような SQL を書く

   create table order_records (
       id            integer primary key 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    timestamp with time zone not null,
       updated_at    timestamp with time zone,
       check ( ( unit_price * qty ) < 200000 ) );
   

   * 「SQL Editor」のウインドウには，SQL プログラムを書くことができる．

   
5. マウスカーソルを先頭に移して（先頭部分をクリック）から、「実行ボタン (Run SQL)」をクリック
   
6. コンソールの確認

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

   
7. 「Tables」を展開し，いま定義したテーブル「order_records」をダブルクリック．すると，テーブル編集画面が開く．テーブルの中身は空である．

   これで，テーブル定義がうまくいったことを確認．

   

SQL を用いたテーブルへの行の挿入

次のような orders テーブルを作る．

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

1. SQL 文の記述

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

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

   begin transaction;
   insert into orders values( 1001, 2022, 1, 26,  'kaneko',   1, 10, datetime('now', 'localtime') );
   insert into orders values( 1002, 2022, 1, 26,  'miyamoto', 2, 2, datetime('now', 'localtime') );
   insert into orders values( 1003, 2022, 1, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
   insert into orders values( 1004, 2022, 1, 27,  'kaneko',   3, 8, datetime('now', 'localtime') );
   commit;
   

   

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

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

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

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

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

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

2. 複数の SQL 文の一括実行

   複数の SQL 文を一括実行したいので，カーソルを先頭行に移動した後に，「Run multiple SQL statements ...」のボタンをクリックする．

   
3. 「Script Output」ウインドウの確認

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

   

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

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

主キー制約 (PRIMARY KEY)

begin transaction;
insert into orders values( 1003, 2018, 4, 30, 'kaneko', 1, 3, datetime('now', 'localtime') );
ROLLBACK;

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

非空制約 (not null)

begin transaction;
insert into orders values( 1005, 2018, 4, 30,  NULL, 1, 3, datetime('now', 'localtime') );
ROLLBACK;

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

その他の一貫性制約

一貫性制約に違反する例

begin transaction;
insert into orders values( 1006, 1009, 10, 30,  'kaneko', 1, 3, datetime('now', 'localtime') );
ROLLBACK;

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

SQL 問い合わせの発行と評価結果の確認

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

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

SELECT * FROM orders;

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

SELECT * FROM orders WHERE product_id = 3;

SELECT * FROM orders WHERE customer_name LIKE 'k%';

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

SELECT * FROM orders WHERE qty >= 10;

SELECT * FROM orders WHERE qty < 10 AND customer_name = 'kaneko';

SQL を用いた更新

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

1. SQL 文の記述

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

   begin transaction;
   UPDATE orders SET qty=12
   WHERE id = 1001;
   commit;
   

   
2. 複数の SQL 文の一括実行

   複数の SQL 文を一括実行したいので，カーソルを先頭行に移動した後に，「Run multiple SQL statements ...」のボタンをクリックする．

   
3. 「Script Output」ウインドウの確認

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

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

SQL を用いた行の削除

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

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

1. SQL 文の記述

   begin transaction;
   DELETE FROM orders
   WHERE id = 1002;
   commit;
   

   
2. 複数の SQL 文の一括実行

   複数の SQL 文を一括実行したいので，カーソルを先頭行に移動した後に，「Run multiple SQL statements ...」のボタンをクリックする．
   (Move the cursor to the top statement. Click "Run multiple SQL statements from current cursor position in one batch" icon)

   
3. 「Script Output」ウインドウの確認

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

   
4. orders テーブル のブラウズ
   

SQLiteman を用いたデータのブラウズ

1. まず，オブジェクト・ブラウザ (Object Browser) の中の「Tables」を展開
   
2. 次に，テーブル ordersを選ぶ (Select table 'orders')
   
3. テーブル ordersが表示される (table 'orders' appears)
   

   * もし，データに間違いがあれば，このウインドウで修正できる (If you find any mistakes, you can modify the data using this window).

4. 行を削除したいときは，左端の行番号をクリックした後， 「この行を削除」を選ぶ
   

以下、書きかけ

テーブルの CSV ファイル形式データのインポート (Import table data)

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

   ここで使用する CSV ファイル: 580.csv

   
2. 「Database」 →「Import Table Data ...」と操作する．
   
3. インポート先のテーブル名とファイル名を設定．「Comma」を選び， 下側のプレビュー画面を確認したのち、 「OK」をクリック
   

日本語を試してみる

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

• 「"① ② Ｉ Ⅱ ㍉ ㌢ ㈱"」は Shift_JIS にはなく，Shift_JIS を拡張した文字コードセットである Windows-31J にある
• 「"‖ 〜 − ¢ £ ¬ " 」は Shift_JIS, EUC-JP, ISO-2022-JP では同じ文字コードなのに Windows-31J では違う文字コード
• 「表 十 構」は Shift_JIS では 2 バイト目が「5C」になっているもの

1. SQL 文の記述

   create table JANCHAR (
     id integer primary key not null, 
     bunsyou text
   );
   begin transaction;
   insert into JANCHAR values( 1, 'コンニチハ' );
   insert into JANCHAR values( 2, '① ② Ｉ Ⅱ ㍉ ㌢ ㈱' );
   insert into JANCHAR values( 3, '‖ 〜 − ¢ £ ¬' );
   insert into JANCHAR values( 4, '表 十 構' );
   insert into JANCHAR values( 5, '〒' );
   commit;
   

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

   複数の SQL 文を一括実行したいので，カーソルを先頭行に移動した後に，「Run multiple SQL statements ...」のボタンをクリックする．
   (Move the cursor to the top statement. Click "Run multiple SQL statements from current cursor position in one batch" icon)

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

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

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