大学で使用した自作の資料等を,手直しの上公開している. クリエイティブ・コモンズ BY NC SA.
リレーショナルデータベースの基礎であるテーブル定義,一貫性制約,SQL,結合と分解,トランザクション,埋め込みSQL,実行計画,二次索引を学ぶ.SQLite 3 を用いて,SQL についての演習も行う.
【サイト内のリレーショナルデータベース関連の資料】
データベース更新を行うときは,最初に「begin transaction;」を実行する. データベース更新が終わったら「commit;」または「ROLLBACK;」を実行する.
SQL での INSERT 文は,表に行を挿入するためのもの.次の2種類に大別される.
INSERT INTO R(A1', A2', ..., Ap') VALUES (a1', a2', ..., ap')
これは,表 R(A1, A2, ..., An) に,行(..., a1', ..., a2', ..., ap', ...) を挿入する.A1', A2', ..., Ap' 以外の値は NULL(空値)になる.
なお「R(A1', A2', ..., Ap')」ではなく,「R」とだけテーブル名を書く場合もある(次のように)
INSERT INTO R VALUES (a1, a2, ..., an)
このときは,表 R(A1, A2, ..., An) に,行(a1, a2, ..., an) が挿入される(A1にa1が, A2にa2が,というように挿入される)
INSERT INTO R(A1', A2', ..., Ap') SELECT B1, B2, ..., Bp FROM <探索表リスト> WHERE <探索条件>
これは,SELECT文で得られた評価結果(表の形をしている)を,表R に挿入する.
DELETE 文は,条件に合致する行の削除を行う。一般形は「DELETE FROM <table-name> WHERE <expression>;」のようになる. 例えば、テーブルRから<探索条件>を満たす行を全て削除するときは次のように書く.
DELETE FROM R WHERE <探索条件>
UPDATE 文は,条件に合致する行に関するデータの更新を行う.一般形は「UPDATE <table-name> SET <attribute-name>=<expression> WHERE <expression>」のようになる.
例えば、表R の中で,探索条件を満たす行について,指定した属性(複数可)の値を更新するときは次のように書く.
UPDATE R SET A1 = 値式1 A2 = 値式2 ... Ap = 値式p WHERE <探索条件>
現在の日時を取得する SQLite 3 の関数
A SQLite 3 function to get the current datetime.
日時のデータを「YYYY-MM-DD HH:MM:SS」形式の文字列に変換する SQLite 3 の関数
A SQLite 3 function to convert a datetime data into the string like "YYYY-MM-DD HH:MM:SS".
すでに作成済みのデータベースを,下記の手順で開くことができる.
以下の手順で,既存のデータベースファイルを開く. (Open an existing database file)
■ Ubuntu での実行例(「SQLite/mydb」を開く場合)
データベースファイル SQLite/mydb を選び, 「開く」をクリック (Click '開く' after choosing the database file "SQLite/mydb")
■ Windows での実行例(「C:\SQLite\mydb」を開く場合)
データベースファイル C:\SQLite\mydb を選び, 「開く」をクリック (Click '開く' after choosing the database file "C:\SQLite\mydb")
要するに,/home/<ユーザ名>/SQLite 3の下の mydb を選ぶ.
SQL を用いて,E テーブルを定義し,一貫性制約を記述する. (Define table 'E' and specify integrity constrants of the table using SQL)
リレーショナル・スキーマ (relational schema); E(name, score, student_name)
次の SQL を入力し,「Run SQL」のアイコンをクリック (Write the following SQL, and click "Run SQL" icon).
create table E ( name text not null, score integer not null check ( score >= 0 AND score <=100 ), student_name text not null, created_at datetime not null, updated_at datetime, unique (name, student_name) );
エラーメッセージが出ていないことを確認
テーブルの 'created_at' 属性には,行を挿入する日時を記録する .
以下の手順で,SQL を用いて E テーブルへの行の挿入を行う (Insert rows into table 'E' using SQL)from
「insert into ...」は行の挿入.ここには 5つの SQL 文を, 「begin transaction」と「commit」で囲む. ("insert into ..." means inserting a row into a table. Five 'INSERT ... INTO ...' statements are wrote).
begin transaction; insert into E values( 'Database', 80, 'KK', datetime('now', 'localtime'), NULL ); insert into E values( 'Database', 95, 'AA', datetime('now', 'localtime'), NULL ); insert into E values( 'Database', 80, 'LL', datetime('now', 'localtime'), NULL ); insert into E values( 'Programming', 85, 'KK', datetime('now', 'localtime'), NULL ); insert into E values( 'Programming', 75, 'LL', datetime('now', 'localtime'), NULL ); 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)
エラーメッセージが出ていないことを確認
SQL を用いた問い合わせの実行例を示す.
テーブルの全ての行の表示 (List all rows of a table)
SELECT * FROM E;
条件を満足する行のみの表示 (List the rows which satisfy a given condition)
SELECT * FROM E WHERE name = 'Database';
SELECT score FROM E WHERE name = 'Database';
まず,オブジェクト・ブラウザ (Object Browser) の中の「Tables」を展開 (Click 'Tables')
※ もし,データに間違いがあれば,このウインドウで修正できる (If you find any mistakes, you can modify the data using this window).
なお,「insert into E values( HOGEHOGE );」の「HOGEHOGE」は間違いである(あとで,間違いを含むプログラムでの振る舞いを見たいから,わざと間違えている).(There is a mistake in SQL. what happen ?)
begin transaction; insert into E values( HOGEHOGE ); 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)
ここでは,処理を続行したいので「Ignore」を選ぶ。 処理を継続したので「begin transaction;」で開始したトランザクションも継続している。 (select 'Ignore' to continue transaction)
SELECT * FROM E;
begin transaction; insert into E values( HOGEHOGE ); commit;
今度は,「Abort」を選ぶ.すると, 「insert into E values( HOGEHOGE );」の行以降がすべて無視されることになる。 したがって、処理を中断したけれど「begin transaction;」で開始したトランザクションは継続している。 (commit も ROLLBACK も行われていない状態である) (select 'Abort'. Note that the started transaction is still active)
ROLLBACK:
これは、1度トランザクションを開始したのに、その終了をせずに、再びトランザクションを開始しようとしているため。決して、故障ではない。1度、commit あるいは ROLLBACK で、トランザクションを終了させること。
SQL を用いたデータの更新 (update)の実行例を示す. 「UPDATE <table-name> SET <attribute-name>=<expression> WHERE <expression>」の形をした SQL は,データの更新である. この SQL 文を 「begin transaction」と「commit」で囲む. ("UPDATE ... SET ..." means database update).
「UPDATE ... SET ...」は更新.ここには 1つの SQL 文を書き, 「begin transaction」と「commit」で囲む. ("UPDATE ... SET ..." means database update.
begin transaction; UPDATE E SET score=90, updated_at=datetime('now', 'localtime') WHERE name = 'Database' AND student_name ='KK'; 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)
エラーメッセージが出ていないことを確認
「UPDATE ... SET ...」は更新.ここには 1つの SQL 文を書き, 「begin transaction」と「commit」で囲む. ("UPDATE ... SET ..." means database update.
begin transaction; UPDATE E SET score=80, updated_at=datetime('now', 'localtime') WHERE name = 'Programming' AND student_name ='LL'; 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)
エラーメッセージが出ていないことを確認
SQL を用いた行の削除 (delete row(s))の実行例を示す.
「DELETE FROM <table-name> WHERE <expression>;」の形をした SQL は行の削除である. この SQL 文を 「begin transaction」と「commit」で囲む. ("DELETE FROM ... WHERE ..." means deletion of rows).
begin transaction; DELETE FROM E WHERE name = 'Database' AND student_name ='AA'; 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)
エラーメッセージが出ていないことを確認
今度は commit を書かない (Without commit)
begin transaction; insert into E values( 'Database', 90, 'BB', datetime('now', 'localtime'), NULL ); insert into E values( 'Programming', 95, 'BB', datetime('now', 'localtime'), NULL );
複数の 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)
エラーメッセージが出ていないことを確認
■ Linuxでの実行例
まず端末を開く。デスクトップに「端末」アイコンがある場合には、それをクリック(下記)。ない場合にはメニューで「アクセサリ」→「端末」 と操作する。
端末を開いて次のように操作。「cd SQLite」は、SQLite 3 のデータベースファイルが置いてあるディレクトリに移動する操作なので、別のディレクトリにデータベースファイルを作った場合には、適切に読み替えること。
cd cd SQLite ls
「mydb-journal」というファイルができている。これがジャーナルファイルである。(Journal file name is 'mydb-journal' for 'mydb' database)
■ Windows での実行例
C:\SQLite を開く.「mydb-journal」がジャーナル・ファイルである.
commit;
エラーメッセージが出ていないことを確認
commit を発行した.データベースの修正は終わっている.そのためジャーナルファイルが消えている.
■ Windows での実行例
C:\SQLite を開く.
今度も commit を書かない (Without commit)
begin transaction; insert into E values( 'Database', 95, 'CC', datetime('now', 'localtime'), NULL ); insert into E values( 'Programming', 80, 'CC', datetime('now', 'localtime'), NULL );
複数の 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)
エラーメッセージが出ていないことを確認
「mydb-journal」がジャーナル・ファイルである.
■ Windows での実行例
C:\SQLite を開く.
「insert into E values( 'Database', 95, 'CC', datetime('now', 'localtime'), NULL );」, 「insert into E values( 'Programming', 80, 'CC', datetime('now', 'localtime'), NULL );」の結果がデータベースに反映されているように見える. (It seems that database update is finished)
今度は ROLLBACK を発行する.(Issue ROLLBACK)
ROLLBACK;
エラーメッセージが出ていないことを確認
実はデータベースファイルは一切修正されていない (Database file is unchaned. It is observerd by using the file timestamp).
次の問いに答えよ.その後,下記の解答例を確認せよ. Answer the following questions. Then, inspect answers described below.
問い (Questions)
name | type | color ------------------------------ apple | fruit | red apple | fruit | blue rose | flower | white rose | flower | red rose | flower | yellow
name | type | color ------------------------------ apple | fruit | red apple | fruit | blue rose | flower | white rose | flower | red rose | flower | blue
name | type | color ------------------------------ apple | fruit | red rose | flower | white rose | flower | red rose | flower | blue
create table GG ( id integer primary key, name text ); begin transaction; insert into GG values ( 1, 'X' ); commit; select * from GG;
begin transaction; insert into GG values ( 2, 'Y' ); ROLLBACK; select * from GG;
解答例 (Answers)
UPDATE PTABLE SET color='blue' WHERE name='rose' AND type='flower' AND color='yellow';
DELETE FROM PTABLE WHERE name='apple' AND type='fruit' AND color='blue';
2問とも、評価結果は次の通り.
id|name 1|X