サイトの全体構成

トランザクション

大学で使用した自作の資料等を，手直しの上公開している．クリエイティブ・コモンズ BY NC SA．

リレーショナルデータベースの基礎であるテーブル定義，一貫性制約，SQL，結合と分解，トランザクション，埋め込みSQL，実行計画，二次索引を学ぶ．SQLite 3 を用いて，SQL についての演習も行う．

【サイト内のリレーショナルデータベース関連の資料】

リレーショナルデータベースの基本まとめ：別ページ »で説明している．
SQL 入門演習（SQLite 3 を使用）（全３回）：別ページ »にまとめ
入門者向け．リレーショナルデータベースを知る．
リレーショナルデータベースシステム（スライド資料と SQL 演習）（全15回）：別ページ »にまとめ
入門者向け．設計と利用と運用．MySQL, Access を利用．基本を学ぶ．演習付き．
リレーショナルデータベース演習（全１５回）：別ページ »にまとめ
入門者向け．設計と利用に関する演習．
リレーショナルデータベースの基礎（まとめ） [PDF], [ワードファイル]
基礎を１つにまとめた資料．

演習を行うために必要になる機能や文法

begin transaction, commit, ROLLBACK

データベース更新を行うときは，最初に「begin transaction;」を実行する．データベース更新が終わったら「commit;」または「ROLLBACK;」を実行する．

commit: ・・・「begin transaction;」以降の全てのデータベース更新操作を確定したいとき
ROLLBACK: ・・・「begin transaction;」以降の全てのデータベース更新操作を破棄したいとき

INSERT 文

SQL での INSERT 文は，表に行を挿入するためのもの．次の2種類に大別される．

値を指定して 1 行挿入
```
    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が，というように挿入される）

　あるSELECT文の評価結果を挿入

    INSERT
    INTO    R(A1', A2', ..., Ap')
        SELECT B1, B2, ..., Bp
        FROM ＜探索表リスト＞
        WHERE ＜探索条件＞

これは，SELECT文で得られた評価結果（表の形をしている）を，表R に挿入する．

DELETE 文

DELETE 文は，条件に合致する行の削除を行う。一般形は「DELETE FROM <table-name> WHERE <expression>;」のようになる．例えば、テーブルRから＜探索条件＞を満たす行を全て削除するときは次のように書く．

    DELETE
    FROM    R
    WHERE ＜探索条件＞

UPDATE 文

UPDATE 文は，条件に合致する行に関するデータの更新を行う．一般形は「UPDATE <table-name> SET <attribute-name>=<expression> WHERE <expression>」のようになる．

例えば、表R の中で，探索条件を満たす行について，指定した属性（複数可）の値を更新するときは次のように書く．

    UPDATE R
    SET     A1 = 値式1
        A2 = 値式2
        ...
        Ap = 値式p
    WHERE    ＜探索条件＞

now

現在の日時を取得する SQLite 3 の関数

A SQLite 3 function to get the current datetime.

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".

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

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

以下の手順で，既存のデータベースファイルを開く． (Open an existing database file)

「File」→ 「Open」
データベースファイルを開く (Open 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 を選ぶ．

「Tables」を展開すると，テーブルの一覧 (List of Tables) が表示されるので確認する (List of tables appears by clicking 'Tables')

[image]

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

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

リレーショナル・スキーマ (relational schema); E(name, score, student_name)

E テーブルの定義 (Define a table)

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

コンソールの確認 (Inspect console)
エラーメッセージが出ていないことを確認

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

テーブルの 'created_at' 属性には，行を挿入する日時を記録する ．

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

SQL プログラムの記述

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

[image]

複数の SQL 文の一括実行 (Run 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)
「Script Output」ウインドウの確認 (Inspect "Script Output" window)
エラーメッセージが出ていないことを確認

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

SQL を用いた問い合わせの実行例を示す．

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

SELECT * FROM E;

[image]

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

SELECT * FROM E WHERE name = 'Database';

SELECT score FROM E WHERE name = 'Database';

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

E テーブル
まず，オブジェクト・ブラウザ (Object Browser) の中の「Tables」を展開 (Click 'Tables')

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

SQL 内の文法エラー (grammatical error in SQL)

まずは，文法エラーを含むSQLプログラムの記述
なお，「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)
複数の 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)
「insert into E values( HOGEHOGE );」の「HOGEHOGE」が間違っているのでエラーメッセージが出る．

ここでは，処理を続行したいので「Ignore」を選ぶ。処理を継続したので「begin transaction;」で開始したトランザクションも継続している。 (select 'Ignore' to continue transaction)
処理が続行され「commit;」まで進む．ここで、トランザクションが終わる (transaction commit here)
確認のため、テーブルの全ての行の表示. テーブル E に変化はない． (List all rows of a table. table E is unchanged)
```
SELECT * FROM E;
```

トランザクション開始後に、トランザクション開始できない (Can not start a transaction within a transaction

前の SQL プログラムをそのまま使う (use the previous SQL programs again)
```
begin transaction;
insert into E values( HOGEHOGE ); 
commit;
```
もう１度，複数の SQL 文の一括実行 (Run multiple SQL statements)
「insert into E values( HOGEHOGE );」の「HOGEHOGE」が間違っているのでエラーメッセージが出る．
今度は，「Abort」を選ぶ．すると，「insert into E values( HOGEHOGE );」の行以降がすべて無視されることになる。したがって、処理を中断したけれど「begin transaction;」で開始したトランザクションは継続している。（commit も ROLLBACK も行われていない状態である） (select 'Abort'. Note that the started transaction is still active)
ロールバック(ROLLBACK)したいので、末尾に「ROLLBACK」と書き加えて、「Run SQL」のアイコンをクリック (Write 'ROLLBACK;' statment)
```
ROLLBACK:
```
もしロールバック(ROLLBACK) せずに，もう１度、SQL文の実行を行ったとする。すると、「Can not start a transaction withing a transaction」 (トランザクションの中でトランザクションを開始できない)というエラーメッセージが出る。
これは、１度トランザクションを開始したのに、その終了をせずに、再びトランザクションを開始しようとしているため。決して、故障ではない。１度、commit あるいは ROLLBACK で、トランザクションを終了させること。

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

SQL プログラムの記述
「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)
複数の 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)
「Script Output」ウインドウの確認 (Inspect "Script Output" window)
エラーメッセージが出ていないことを確認
E テーブル のブラウズ (Browse table 'E')
SQL プログラムの記述
「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)
複数の 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)
「Script Output」ウインドウの確認 (Inspect "Script Output" window)
エラーメッセージが出ていないことを確認
E テーブル のブラウズ (Browse table 'E')

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

SQL プログラムの記述

begin transaction;
DELETE FROM E
WHERE name = 'Database' AND student_name ='AA';
commit;

複数の SQL 文の一括実行 (Run 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)
「Script Output」ウインドウの確認 (Inspect "Script Output" window)
エラーメッセージが出ていないことを確認
E テーブル のブラウズ (Browse table 'E')

ジャーナルファイルの確認 (Examine a journal file)

SQL プログラムの記述

今度は 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)
複数の 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)
「Script Output」ウインドウの確認 (Inspect "Script Output" window)
エラーメッセージが出ていないことを確認
ジャーナル・ファイルの確認 (Journal File)
■ 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」がジャーナル・ファイルである．
Sqliteman で commit の発行 (commit)
```
commit;
```
「Script Output」ウインドウの確認 (Inspect "Script Output" window)
エラーメッセージが出ていないことを確認
ジャーナル・ファイルの確認 (Journal File)
commit を発行した．データベースの修正は終わっている．そのためジャーナルファイルが消えている．
■ Windows での実行例
C:\SQLite を開く．

ROLLBACK の確認 (Example a journal file)

SQL プログラムの記述

今度も 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)
複数の 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)
「Script Output」ウインドウの確認 (Inspect "Script Output" window)
エラーメッセージが出ていないことを確認
ジャーナル・ファイルの確認 (Journal File disappears)
「mydb-journal」がジャーナル・ファイルである．
■ Windows での実行例
C:\SQLite を開く．
E テーブル のブラウズ (Browse table 'E')
「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)
Sqliteman で ROLLBACK の発行 (ROLLBACK)
今度は ROLLBACK を発行する．(Issue ROLLBACK)
```
ROLLBACK;
```
「Script Output」ウインドウの確認 (Inspect "Script Output" window)
エラーメッセージが出ていないことを確認
データベース・ファイルとジャーナル・ファイルの確認 (Journal File disappears)
実はデータベースファイルは一切修正されていない (Database file is unchaned. It is observerd by using the file timestamp).
E テーブル のブラウズ (Browse table 'E')

演習問題と解答例

次の問いに答えよ．その後，下記の解答例を確認せよ． Answer the following questions. Then, inspect answers described below.

問い (Questions)

次の PTABLE テーブルに関する問題 (About the following 'PTABLE' table)

name    |  type   |  color
------------------------------
apple   |  fruit  |  red
apple   |  fruit  |  blue
rose    |  flower |  white
rose    |  flower |  red
rose    |  flower |  yellow

PTABLE を次のように更新する SQL を書きなさい (Write a SQL which updates the table).

name    |  type   |  color
------------------------------
apple   |  fruit  |  red
apple   |  fruit  |  blue
rose    |  flower |  white
rose    |  flower |  red
rose    |  flower |  blue

さらに，PTABLE から行を削除し，次のテーブルに変更する SQL を書きなさい (Write a SQL which delete a row).

name    |  type   |  color
------------------------------
apple   |  fruit  |  red
rose    |  flower |  white
rose    |  flower |  red
rose    |  flower |  blue

commit, ROLLBACK に関する次の問に解答せよ．

SQLite を使い，下記の SQL を評価させる．「select * from GG;」の評価結果は何か？

    create table GG (
        id integer primary key,
        name text );
    begin transaction;
    insert into GG values ( 1, 'X' );
    commit;
    select * from GG;

その後、SQLite を使い，下記の SQL を評価させる．「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';

問２
２問とも、評価結果は次の通り．
```
id|name
1|X
```

本サイトは金子邦彦研究室のWebページである．

資料等の公開では，原則，「クリエイティブコモンズ BY NC SA」として公開するようにしている． PDFファイル，パワーポイントファイルなどには，「クリエイティブコモンズ BY NC SA」を明記するとともに，ロゴを記載するようにしている（作業が間に合っていない分もあるのでご容赦ください）．

公開している資料をご利用になる場合の，再配布の条件，剽窃の防止などについて，別ページ »で説明再配布や資料改変の際には，そのページをご確認ください．

サイトマップは，サイトマップのページをご覧下さい．本サイト内の検索は，サイト内検索のページをご利用下さい．

問い合わせ先：金子邦彦（かねこくにひこ） [image]