下記のようなテーブルからデータを選択したい時に使用するのが、SELECT
文だった。SELECT
文はデータベースに対して変化を与えるものではなく、『読み取り専用』の状態で、データ検索を行うコマンドであった。
今回は、データベースのデータを更新(登録、削除、更新)する方法について学んだ。
まずはデータ更新用のテーブルを作るところから。
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 (値リスト);
- 実行例
- 列リストと値リストを全て入力
- 値リストのみ入力(全列に対して
INSERT
を行う) 全列に対してINSERT
する時には、列リストは省略可能だが、注意としては、値リストの列数が、列リストの数分だけ必要にある。
他のテーブルからデータをコピー
他のテーブルからデータをコピーしたい場合には、SELECT
した結果を、そのままINSERT
してコピーしてあげれば良い。ポイントとしては、SELECT
句のカラム名はカッコで括らないところ。
- コマンド:
INSERT INTO コピー先テーブル (列リスト) SELECT カラム名 FROM コピー元テーブル;
- 実行例
DELETE
:データの削除
テーブルの削除に使うのがDROP TABLE
で、テーブルからデータ(行)を削除する際に用いるのがDELETE
文。
テーブルデータを全部削除
- コマンド:
DELETE FROM テーブル名;
- 実行例
テーブルデータを指定して削除
行に対して条件指定を行う役目を持つのがWHERE
句だった。よって削除においても、削除する行を条件指定することで、特定の行のみを削除することができる。
- コマンド:
DELETE FROM テーブル名 WHERE 条件;
- 実行例
UPDATE
:データの更新
登録済みのデータを変更したい時に使用するのが、UPDATE
文。
全行の値を更新する
- コマンド:
UPDATE テーブル名 SET 列名 = 式;
- 実行例:
条件を指定して更新する
ここも行に対して条件指定を行うと言う観点から、WHERE
句を用いる。
- コマンド:
UPDATE テーブル名 SET 列名 = 式 WHERE 条件;
- 実行例1:
- 実行例2:複数列をまとめて更新
トランザクション
トランザクションとは、データベースに対する1つ以上の更新をまとめて呼ぶ時の名称。トランザクションを利用することで、データベースにおけるデータ更新処理の確定や取り消しなどを管理できるようになる。
例えば、下記のような『2つ完了して初めて1つのタスクとして完了するタスク』があるとする。
Shohin
テーブルのカッターシャツの販売単価を1000円上げるShohin
テーブルのTシャツの販売単価を1000円下げる
よって、片方だけが完了するような中途半端な状態にはできない。そうした場合に使用するのが、トランクジョン。
トランザクションを作る
コマンド:
トランザクション開始文; DML文(SELECT/INSERT/UPDATE/DELETE ) DML文(SELECT/INSERT/UPDATE/DELETE ) ... トランザクション終了文(COMMIT or ROLLBACK);
トランザクション開始文は方言があり、PostgreSQL,SQL Serverでは
BEGIN TRANSACTION
、MySQLではSTART TRANSACTION
、Oracle DB2には開始文はない。実行例
COMMIT
:トランザクションの結果をデータベースに保存する
ROLLBACK
:処理の取り消し
トランザクションは処理のまとまりであるため、トランザクションが開始されてから、トランザクション終了文が来るまでは、一連の処理はデータベースに対して確定されない。
よって、トランザクション終了文の前に取り消しすることも可能になっている。イメージ的には『保存せずに終了』のような感じ。
実行例:
ROLLBACK
:トランザクションの結果をデータベースに保存せずに終了する
ACID特性
トランザクションにはACID特性という標準規格が定められている。
Atomicity:原子性
トランザクション終了時、そこに含まれる更新処理は、全て実行されるもしくは全て実行されない状態となることを保証する性質(オールオアナッシング。)
原子性が守られないと、トランザクションを実行時に中途半端な更新をデータベースに対して実行してしまうことになる。
Consistency:一貫性(整合性)
トランザクションに含まれる処理は、データベース設定された制約(主キーや
NOT NULL
等)を満たすことを保証する性質。一貫性がないと、制約に違反する更新をデータベースに反映してしまうことになる。トランザクション内に違反するDML文があった場合、どこまで反映するか(当該DML文のみ実行しないもしくはトランザクション全てを実行しないなど)は方言になっている。PostgreSQLでは、後者になっている。
Isolation:独立性
トランザクション同士が干渉を受けないことを保証する性質。
Durabillity:永続性
トランザクションが終了したら、その時点でのデータの状態が保存されることを保証する性質。トランザクション終了時に何らかのシステム障害が発生しデータの状態が失われても、データベースは何らかの手段によって復旧できる手段を持たないといけない。
永続性がないと、トランザクション終了時に何らかのシステム障害が発生しデータが失われた場合に、1からやり直しとなってしまうことになる。
データ復旧の方法として一般的なのは、トランザクションの記録をディスクにログとして保存しておき、障害時にはログをもとに障害前の状態に戻すという方法。