RDBMS:データの更新

下記のようなテーブルからデータを選択したい時に使用するのが、SELECT文だった。SELECT文はデータベースに対して変化を与えるものではなく、『読み取り専用』の状態で、データ検索を行うコマンドであった。 image.png

今回は、データベースのデータを更新(登録、削除、更新)する方法について学んだ。

まずはデータ更新用のテーブルを作るところから。

CREATE TABLE ShohinCopy
(shohin_id CHAR(4) NOT NULL,
shohin_mei VARCHAR(100) NOT NULL,
shohin_bunrui VARCHAR(32) NOT NULL,
hanbai_tanka INTEGER  DEFAULT 0,
shiire_tanka INTEGER  ,
torokubi DATE   ,
PRIMARY KEY (shohin_id));

テーブルを作るときに、NOT NULLを与えると、当該カラムはNULLを認めない設定になる。つまり、データ更新時に当該カラムがNULLになる際にはエラーが出る。

また、テーブル生成時にDEFAULT で値を与えてあげると、レコードの新規登録時に当該カラムをDEFAULTで設定すると、テーブル生成時に設定した値でレコードが生成される。

INSERT:データの登録

テーブルを作成するのに使うのがCREATE TABLEで、テーブルにデータ(行)を登録する際に用いるのがINSERT文。

データを登録する

  • コマンド:INSERT INTO テーブル名 (列リスト) VALUES (値リスト);
  • 実行例
    • 列リストと値リストを全て入力 image.png
    • 値リストのみ入力(全列に対してINSERTを行う) image.png 全列に対してINSERTする時には、列リストは省略可能だが、注意としては、値リストの列数が、列リストの数分だけ必要にある。

他のテーブルからデータをコピー

他のテーブルからデータをコピーしたい場合には、SELECTした結果を、そのままINSERTしてコピーしてあげれば良い。ポイントとしては、SELECT句のカラム名はカッコで括らないところ。

  • コマンド:INSERT INTO コピー先テーブル (列リスト) SELECT カラム名 FROM コピー元テーブル;
  • 実行例 image.png

DELETE:データの削除

テーブルの削除に使うのがDROP TABLEで、テーブルからデータ(行)を削除する際に用いるのがDELETE文。

テーブルデータを全部削除

  • コマンド:DELETE FROM テーブル名;
  • 実行例 image.png

テーブルデータを指定して削除

行に対して条件指定を行う役目を持つのがWHERE句だった。よって削除においても、削除する行を条件指定することで、特定の行のみを削除することができる。

  • コマンド:DELETE FROM テーブル名 WHERE 条件;
  • 実行例 image.png

UPDATE:データの更新

登録済みのデータを変更したい時に使用するのが、UPDATE文。

全行の値を更新する

  • コマンド:UPDATE テーブル名 SET 列名 = 式;
  • 実行例: image.png

条件を指定して更新する

ここも行に対して条件指定を行うと言う観点から、WHERE句を用いる。

  • コマンド:UPDATE テーブル名 SET 列名 = 式 WHERE 条件;
  • 実行例1: image.png
  • 実行例2:複数列をまとめて更新 image.png

トランザクション

トランザクションとは、データベースに対する1つ以上の更新をまとめて呼ぶ時の名称トランザクションを利用することで、データベースにおけるデータ更新処理の確定や取り消しなどを管理できるようになる。

例えば、下記のような『2つ完了して初めて1つのタスクとして完了するタスク』があるとする。

  1. Shohinテーブルのカッターシャツの販売単価を1000円上げる
  2. ShohinテーブルのTシャツの販売単価を1000円下げる

よって、片方だけが完了するような中途半端な状態にはできない。そうした場合に使用するのが、トランクジョン

トランザクションを作る

  • コマンド:

    トランザクション開始文;
    
      DML文(SELECT/INSERT/UPDATE/DELETE )
      DML文(SELECT/INSERT/UPDATE/DELETE )
      ...
    
    トランザクション終了文(COMMIT or ROLLBACK);
    

    トランザクション開始文は方言があり、PostgreSQL,SQL ServerではBEGIN TRANSACTIONMySQLではSTART TRANSACTIONOracle DB2には開始文はない。

  • 実行例

    COMMITトランザクションの結果をデータベースに保存する image.png

ROLLBACK:処理の取り消し

トランザクションは処理のまとまりであるため、トランザクションが開始されてから、トランザクション終了文が来るまでは、一連の処理はデータベースに対して確定されない。

よって、トランザクション終了文の前に取り消しすることも可能になっている。イメージ的には『保存せずに終了』のような感じ。

ACID特性

トランザクションにはACID特性という標準規格が定められている。

  • Atomicity:原子性

    トランザクション終了時、そこに含まれる更新処理は、全て実行されるもしくは全て実行されない状態となることを保証する性質(オールオアナッシング。)

    原子性が守られないと、トランザクションを実行時に中途半端な更新をデータベースに対して実行してしまうことになる。

  • Consistency:一貫性(整合性)

    トランザクションに含まれる処理は、データベース設定された制約(主キーやNOT NULL等)を満たすことを保証する性質。

    一貫性がないと、制約に違反する更新をデータベースに反映してしまうことになる。トランザクション内に違反するDML文があった場合、どこまで反映するか(当該DML文のみ実行しないもしくはトランザクション全てを実行しないなど)は方言になっている。PostgreSQLでは、後者になっている。

  • Isolation:独立性

    トランザクション同士が干渉を受けないことを保証する性質。

    トランザクションが完了するまでは、他のトランザクションからも当該のトランザクションの結果は見えない状態になる。

  • Durabillity:永続性

    トランザクションが終了したら、その時点でのデータの状態が保存されることを保証する性質。トランザクション終了時に何らかのシステム障害が発生しデータの状態が失われても、データベースは何らかの手段によって復旧できる手段を持たないといけない。

    永続性がないと、トランザクション終了時に何らかのシステム障害が発生しデータが失われた場合に、1からやり直しとなってしまうことになる。

    データ復旧の方法として一般的なのは、トランザクションの記録をディスクにログとして保存しておき、障害時にはログをもとに障害前の状態に戻すという方法。

参考