トップページ -> 教材 -> リレーショナルデータベースの基礎 -> テーブルの更新,トランザクション
[サイトマップへ]

URL: https://www.kkaneko.jp/cc/addb/8.html

トランザクション

サイト構成 連絡先,業績など 実践知識 データの扱い コンピュータ 教材 サポートページ

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

  • BEGIN TRANSACTION, COMMIT, ROLLBACK

    データベース更新を行うときは,最初に「BEGIN TRANSACTION;」を実行する. データベース更新が終わったら「COMMIT;」または「ROLLBACK;」を実行する.

  • INSERT 文

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

    1. 値を指定して 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が,というように挿入される)

    2.  ある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

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

    A SQLite3 function to get the current datetime.

  • datetime

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

    A SQLite3 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)

    1. File」→ 「Open

    2. データベースファイルを開く (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 のの mydb を選ぶ. 

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


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

    2. コンソールの確認 (Inspect console)

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


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

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

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

    1. 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'), NULL );
      INSERT INTO E VALUES( 'Database',    95, 'AA', datetime('now'), NULL );
      INSERT INTO E VALUES( 'Database',    80, 'LL', datetime('now'), NULL );
      INSERT INTO E VALUES( 'Programming', 85, 'KK', datetime('now'), NULL );
      INSERT INTO E VALUES( 'Programming', 75, 'LL', datetime('now'), NULL );
      COMMIT;
      

    2. 複数の 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)

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

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


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

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

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


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

    1. まずは,文法エラーを含むSQLプログラムの記述

      なお,「INSERT INTO E VALUES( HOGEHOGE );」の「HOGEHOGE」は間違いである(あとで,間違いを含むプログラムでの振る舞いを見たいから,わざと間違えている).(There is a mistake in SQL. what happen ?)

      BEGIN TRANSACTION;
      INSERT INTO E VALUES( HOGEHOGE ); 
      COMMIT;
      

    2. 複数の 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)

    3. 「INSERT INTO E VALUES( HOGEHOGE );」の「HOGEHOGE」が間違っているのでエラーメッセージが出る.

      ここでは,処理を続行したいので「Ignore」を選ぶ。 処理を継続したので「BEGIN TRANSACTION;」で開始したトランザクションも継続している。 (select 'Ignore' to continue transaction)

    4. 処理が続行され「COMMIT;」まで進む.ここで、トランザクションが終わる (transaction commit here)

    5. 確認のため、テーブルの全ての行の表示. テーブル E に変化はない. (List all rows of a table. table E is unchanged)

      SELECT * FROM E;
      

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

    1. 前の SQL プログラムをそのまま使う (use the previous SQL programs again)
      BEGIN TRANSACTION;
      INSERT INTO E VALUES( HOGEHOGE ); 
      COMMIT;
      

    2. もう1度,複数の SQL 文の一括実行 (Run multiple SQL statements)

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

    4. ロールバック(ROLLBACK)したいので、末尾に「ROLLBACK」と書き加えて、「Run SQL」のアイコンをクリック (Write 'ROLLBACK;' statment)

      ROLLBACK:
      

    5. もしロールバック(ROLLBACK) せずに, もう1度、SQL文の実行を行ったとする。 すると、「Can not start a transaction withing a transaction」 (トランザクションの中でトランザクションを開始できない)というエラーメッセージが出る。

      これは、1度トランザクションを開始したのに、その終了をせずに、再びトランザクションを開始しようとしているため。決して、故障ではない。1度、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).

    1. SQL プログラムの記述

      「UPDATE ... SET ...」は更新.ここには 1つの SQL 文を書き, 「BEGIN TRANSACTION」と「COMMIT」で囲む. ("UPDATE ... SET ..." means database update.

      BEGIN TRANSACTION;
      UPDATE E SET score=90, updated_at=datetime('now')
      WHERE name = 'Database' AND student_name ='KK';
      COMMIT;
      

    2. 複数の 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)

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

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

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

    5. SQL プログラムの記述

      「UPDATE ... SET ...」は更新.ここには 1つの SQL 文を書き, 「BEGIN TRANSACTION」と「COMMIT」で囲む. ("UPDATE ... SET ..." means database update.

      BEGIN TRANSACTION;
      UPDATE E SET score=80, updated_at=datetime('now')
      WHERE name = 'Programming' AND student_name ='LL';
      COMMIT;
      

    6. 複数の 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)

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

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

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

    1. SQL プログラムの記述

      BEGIN TRANSACTION;
      DELETE FROM E
      WHERE name = 'Database' AND student_name ='AA';
      COMMIT;
      

    2. 複数の 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)

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

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

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

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

    1. SQL プログラムの記述

      今度は COMMIT を書かない (Without COMMIT)

      BEGIN TRANSACTION;
      INSERT INTO E VALUES( 'Database',    90, 'BB', datetime('now'), NULL );
      INSERT INTO E VALUES( 'Programming', 95, 'BB', datetime('now'), NULL );
      

    2. 複数の 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)

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

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

    4. ジャーナル・ファイルの確認 (Journal File)

      Linuxでの実行例

      まず端末を開く。デスクトップに「端末」アイコンがある場合には、それをクリック(下記)。ない場合にはメニューで「アクセサリ」→「端末」 と操作する。

      端末を開いて次のように操作。「cd SQLite」は、SQLite3 のデータベースファイルが置いてあるディレクトリに移動する操作なので、別のディレクトリにデータベースファイルを作った場合には、適切に読み替えること。

      cd
      cd SQLite
      ls
      

      「mydb-journal」というファイルができている。これがジャーナルファイルである。(Journal file name is 'mydb-journal' for 'mydb' database)

      Windows での実行例

      C:\SQLite を開く.「mydb-journal」がジャーナル・ファイルである.

    5. Sqliteman で COMMIT の発行 (COMMIT)

      COMMIT;
      

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

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

    7. ジャーナル・ファイルの確認 (Journal File)

      COMMIT を発行した.データベースの修正は終わっている.そのためジャーナルファイルが消えている.

      Windows での実行例

      C:\SQLite を開く.


    ROLLBACK の確認 (Example a journal file)

    1. SQL プログラムの記述

      今度も COMMIT を書かない (Without COMMIT)

      BEGIN TRANSACTION;
      INSERT INTO E VALUES( 'Database',    95, 'CC', datetime('now'), NULL );
      INSERT INTO E VALUES( 'Programming', 80, 'CC', datetime('now'), NULL );
      

    2. 複数の 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)

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

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

    4. ジャーナル・ファイルの確認 (Journal File disappears)

      「mydb-journal」がジャーナル・ファイルである.

      Windows での実行例

      C:\SQLite を開く.

    5. E テーブル のブラウズ (Browse table 'E')

      「INSERT INTO E VALUES( 'Database', 95, 'CC', datetime('now'), NULL );」, 「INSERT INTO E VALUES( 'Programming', 80, 'CC', datetime('now'), NULL );」の結果がデータベースに反映されているように見える. (It seems that database update is finished)

    6. Sqliteman で ROLLBACK の発行 (ROLLBACK)

      今度は ROLLBACK を発行する.(Issue ROLLBACK)

      ROLLBACK;
      

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

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

    8. データベース・ファイルとジャーナル・ファイルの確認 (Journal File disappears)

      実はデータベースファイルは一切修正されていない (Database file is unchaned. It is observerd by using the file timestamp).

    9. E テーブル のブラウズ (Browse table 'E')

    演習問題と解答例

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

    問い (Questions)

    1. 次の PTABLE テーブルに関する問題 (About the following 'PTABLE' table)
      name    |  type   |  color
      ------------------------------
      apple   |  fruit  |  red
      apple   |  fruit  |  blue
      rose    |  flower |  white
      rose    |  flower |  red
      rose    |  flower |  yellow
      
      1. 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
        
      2. さらに,PTABLE から行を削除し,次のテーブルに変更する SQL を書きなさい (Write a SQL which delete a row).
        name    |  type   |  color
        ------------------------------
        apple   |  fruit  |  red
        rose    |  flower |  white
        rose    |  flower |  red
        rose    |  flower |  blue
        

    2. COMMIT, ROLLBACK に関する次の問に解答せよ.
      1. 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;
        

      2. その後、SQLite を使い,下記の SQL を評価させる.「select * from GG;」の評価結果は何か?
        	BEGIN TRANSACTION;
        	INSERT INTO GG values ( 2, 'Y' );
        	ROLLBACK;
        	select * from GG;
        

    解答例 (Answers)

    1. 問1
      1. UPDATE PTABLE SET color='blue' WHERE name='rose' AND type='flower' AND color='yellow';
        
      2. DELETE FROM PTABLE WHERE name='apple' AND type='fruit' AND color='blue';
        
    2. 問2

      2問とも、評価結果は次の通り.

      id|name
      1|X