SQLitemanshe は,SQLite 3 のデータベースを操作する機能を持った優れたソフトウェア. Windows 版,Linux 版,Max OS X 版がある.ソースコードも配布されている. 下記の操作を行う手順を,説明する.
テーブルの一覧表示と中身の表示と更新(行の削除,値の変更.SQL でなく,グラフィカル・ユーザインタフェースを使用,
このページでは,データベースの生成を行うので, 生成するデータベースのデータベース名を決めておくこと. このページでは,次のように書く.
データベース名は,自由に決めてよいが,半角文字(つまり英字と英記号)を使い,スペースを含まないこと,
自由に決めてよいが,半角文字(つまり英字と英記号)を使い,スペースを含まないこと,
インストール・ディレクトリ: C:\SQLite
リレーショナル・スキーマ:
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 );
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 の関数
SELECT * FROM orders; SELECT * FROM orders WHERE day = 26; SELECT * FROM orders WHERE customer_name = 'kaneko'; SELECT * FROM orders WHERE unit_price > 2;
「
「DELETE FROM ... WHERE ...」は行の削除.ここには 1つの SQL 文を書き, 「begin transaction」と「commit」で囲む.
begin transaction;
UPDATE orders SET qty=12, updated_at=datetime('now', 'localtime')
WHERE id = 1;
commit;
begin transaction;
DELETE FROM orders
WHERE id = 2;
commit;
ここに データベースファイルを置くことにする.
以下、C:\SQLiteに展開(解凍)したとして説明を続ける
新しい画面が開くので確認する.
sudo apt -y update sudo apt -y install sqliteman
■ Windows での SQLiteman の起動例
Windows での操作例
SQLiteman の新しいウインドウが開く.
■ Ubuntu での SQLiteman の起動例
端末を開き、「sqliteman」
SQLiteman の新しいウインドウが開く.
「Help」→ 「Help Content」でヘルプが表示される.
「File」→ 「Exit」で終了.
以下の手順で,新しいデータベースを作成する.その結果,データベースファイルができる.
ここでの設定
Windows での操作例
■ Windows での実行例(データベースファイル名を「hoge.db」にする場合) ディレクトリと、データベースファイル名を指定.「保存」をクリック
※ 以下の例では「C:\SQLite\hoge.db」を指定している).
※ データベースファイル名は何でも良いが、英語の名前がよい.
※ データベースを新規作成したいときは,データベースファイル名として「新しい」ものを指定すること
■ Ubuntu での実行例(データベースファイル名を「mydb」にする場合)
データベースファイル名である mydb を指定し, 「保存」をクリック
※ データベースファイル名は何でも良いが、英語の名前がよい.
※ データベースを新規作成したいときは,データベースファイル名として「新しい」ものを指定すること
すでに作成済みのデータベースを,下記の手順で開くことができる.
以下の手順で,既存のデータベースファイルを開く.
Windows での操作例
まず,「Pragmas」をクリック.
encodingの行に「UTF-8」のように表示されている.
※ もし,データベースの文字のエンコーディングを変えたいときは, SQLiteman のようなグラフィカルなツールを使うのではなく, sqlite.exe を起動し「PRAGMA encoding=...;」で変える方がずっと簡単でしょう. 例えば「UTF-16le」などに変えたいなど.
SQL を用いて,orders テーブルを定義し,一貫性制約を記述する.
リレーショナル・スキーマ
orders(id, year, month, day, customer_name, product_id, qty, created_at)
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 プログラムを書くことができる.
エラーメッセージが出ていないことを確認
これで,テーブル定義がうまくいったことを確認.
次のような orders テーブルを作る.
以下の手順で,SQL を用いてorder records テーブルへの行の挿入を行う
「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') );
複数の SQL 文を一括実行したいので,カーソルを先頭行に移動した後に,「Run multiple SQL statements ...」のボタンをクリックする.
エラーメッセージが出ていないことを確認
ここでは,一貫性制約に違反するような更新を試みる.データベース管理システムソフトウェアが一貫性を維持するので, 一貫性制約に違反するような更新はできない.
begin transaction; insert into orders values( 1003, 2018, 4, 30, 'kaneko', 1, 3, datetime('now', 'localtime') ); ROLLBACK;
※ すでに属性 id には 3 という値がある. 主キー制約「PRIMARY KEY」に違反.
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 問い合わせの詳細については,別の 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 を用いたデータの更新 (update)の実行例を示す. 「UPDATE <table-name> SET <attribute-name>=<expression> WHERE <expression>」の形をした SQL は,データの更新である. この SQL 文を 「begin transaction」と「commit」で囲む.
「UPDATE ... SET ...」は更新.ここには 1つの SQL 文を書き, 「begin transaction」と「commit」で囲む.
begin transaction; UPDATE orders SET qty=12 WHERE id = 1001; commit;
複数の SQL 文を一括実行したいので,カーソルを先頭行に移動した後に,「Run multiple SQL statements ...」のボタンをクリックする.
エラーメッセージが出ていないことを確認
SQL を用いた行の削除 (delete row(s))の実行例を示す.
「DELETE FROM <table-name> WHERE <expression>;」の形をした SQL は行の削除である. この SQL 文を 「begin transaction」と「commit」で囲む.
begin transaction; DELETE FROM orders WHERE id = 1002; commit;
複数の 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)
エラーメッセージが出ていないことを確認
※ もし,データに間違いがあれば,このウインドウで修正できる (If you find any mistakes, you can modify the data using this window).
ここで使用する CSV ファイル: 580.csv
テーブルのデータとして日本語を試す(属性名やテーブル名を日本語にするわけではない)
create table JANCHAR ( id integer primary key not null, bunsyou text ); begin transaction; insert into JANCHAR values( 1, 'コンニチハ' ); insert into JANCHAR values( 2, '① ② I Ⅱ ㍉ ㌢ ㈱' ); insert into JANCHAR values( 3, '‖ 〜 − ¢ £ ¬' ); insert into JANCHAR values( 4, '表 十 構' ); insert into JANCHAR values( 5, '〒' ); commit;
複数の 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)
エラーメッセージが出ていないことを確認