SQL ゼロからはじめるデータベース操作のアウトプット2

前回に続いてアウトプットしていきます。

データの登録（INSERT文）

INSERT文の基本構文

INSERT INTO <デーブル名> (列1, 列2, 列3, ・・・) VALUES (値1,値2,値3, ・・・);

例

INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) VALUES ('0001', 'Tシャツ', '衣服', 1000, 500, '2009-09-20');

※列名や値をカンマで区切って、外側を()でくくった形式をリストと呼びます。
上記例だと
列リスト→(shohin_id,shohin_mei,shohin_bunrui,hanbai_tanka,shiire_tanka,torokubi)
値リスト→('0001','Tシャツ','衣服',1000,500,'2009-09-20')
※INSERT文は、基本的に1回で1行を挿入します。複数の値を挿入したい場合は、原則的にその行数だけINSERT文も繰り返し実行する必要があります。

列リストの省略

テーブル名の後の列リストは、テーブルの全列に対してINSERTを行う場合、省略することができます。このとき、VALUES句の値が暗黙のうちに、左から順に各列に割り当てられます。そのため、以下の2つは同じデータを挿入することを意味します。

-- 列リストあり
INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) VALUES ('0005', '圧力鍋', 'キッチン用品', 6800, 5000, '2009-01-15');

-- 列リストなし
INSERT INTO ShohinIns VALUES ('0005', '圧力鍋', 'キッチン用品', 6800, 5000, '2009-01-15');

デフォルト値を挿入する

テーブルの定義時にDEFAULT値を設定するとINSERT文の列の値として自動的に割り当てられる。
例としてShohinInsテーブルを作成する際にデフォルト制約をつけます。

CREATE TABLE ShohinIns
(shohin_id  CHAR(4)  NOT NULL,
   (略)
hanabi_tanka  INTEGER  DEFAULT 0, -- 販売単価のデフォルト値を0に設定
  (略)
PRIMARY KEY (shohin_id));

これでデフォルト値の準備ができました。では実際に設定したデフォルト値を使ってみます。
使い方①明示的にデフォルト値を挿入する
VALUES句にDEFAULTキーワードを指定する。

INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) VALUES('0007', 'おろしがね', 'キッチン用品', DEFAULT, 790, '2009-04-28');

これでshohin_id=0007の行を選択した場合、販売単価＝0として割り当てられた行を返します。

使い方②暗黙的にデフォルト値を挿入する
デフォルト値が設定されている列を列リストからもVALUESからも省略する。

INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui,shiire_tanka, torokubi) VALUES ('0007', 'おろしがね', 'キッチン用品', 790, '2009-04-28');

上の例ではhanbai_tanka列を省略しています。
この場合もhanbai_tankaにはデフォルト値の0が挿入されます。
では、この2つの使い方のどちらを使うのが良いかについては使い方①を筆者はお勧めしています。それはぱっと見て何がデフォルト値に利用されてるかわかりやすいからです。

ほかのテーブルからデータをコピーする

あらかじめShohinCopyテーブルを作成しているものとします。
このShohinCopyテーブルに、前章まで使っていたShohinテーブルのデータを挿入します。

-- 商品テーブルのデータを商品コピーテーブルへコピー
INSERT INTO ShohinCopy (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi)
SELECT shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi
 FROM Shohin;

このINSERT・・・SELECT文を実行すると、たとえば元のShohinテーブルに8行のデータが入っていたとすれば、ShohinCopyテーブルにも全く同じ8行のデータが追加されます。

他にもこのINSERT文内のSELECT文には、WHERE句やGROUP BY句などを使うこともできます。
例えば以下のようなShohinBunruiテーブルがあったとする。

-- 商品部類ごとにまとめたテーブル
CREATE TABLE ShohinBunrui
(shohin_bunrui  VARCHAR(32) NOT NULL,
 sum_hanbai_tanka INTEGER  ,
 sum_shiire_tanka INTEGER  ,
 PRIMARY KEY (shohin_bunrui));

これは商品分類ごとに販売単価の合計と仕入単価の合計を保持するためのテーブルです。ここに、Shohinテーブルからデータを挿入するならば以下のようなINSERT・・・SELECT文を使います。

INSERT INTO ShohinBunrui (shohin_bunrui, sum_hanbai_tanka, sum_shiire_tanka)
SELECT shohin_bunrui, SUM(hanbai_tanka), SUM(shiire_tanka)
  FROM Shohin
GROUP BY shohin_bunrui;

これで最初の商品分類テーブルにShohinテーブルの商品分類ごとの販売単価の合計、仕入単価の合計が挿入されることとなる。

データの削除

データの削除方法は、大きく分けて2つあります。
①DROP TABLE文によって、テーブルそのものを削除する
②DELETE文によって、テーブル（入れ物、容器）は残したまま、テーブル内のすべての行を削除する。
ここでは②について詳しく見ていきます。
構文

DELETE FROM <テーブル名>;

この構文の場合は指定したテーブルの全ての行を削除します。
次に削除対象を指定したDELETE文を見ていきます。
構文

DELETE FROM <テーブル名>
 WHERE <条件>;

例

DELETE FROM Shohin
 WHERE hanbai_tanka >= 4000;

データの更新(UPDATE文）

UPDATE文はテーブルにINSERT文でデータを登録した後、変更したいときなどに使います。
構文

UPDATE <テーブル名>
   SET <列名> = <式>;

例（登録日をすべて「2009年10月10日」に変更する）

UPDATE Shohin
  SET torokubi = '2009-10-10';

※尚、更新前にNULLだった部分にも更新した内容が反映されます。

条件を指定したUPDATE文

構文

UPDATE <テーブル名>
  SET <列名> = <式>
 WHERE <条件>;

例（商品分類が「キッチン用品」の行のみ販売単価を10倍に変更）

UPDATE Shohin
  SET hanbai_tanka = hanbai_tanka * 10
 WHERE shohin_bunrui = 'キッチン用品';

NULLで更新する

この場合は代入式の右辺にNULLを指定してあげることで可能となります。又、この処理をNULLクリアと呼びます。
例（商品IDが「0008」のボールペンの登録日をNULLに変更）

UPDATE Shohin
  SET torokubi = NULL
WHERE shohin_id = '0008';

複数列の更新

複数の列を更新対象として記述することが可能です。
例えば以下のようなUPDATE文があったとします。

-- 1回のUPDATEで1列だけ更新する
UPDATE Shohin
  SET hanbai_tanka = hanbai_tanka * 10
 WHERE shohin_bunrui = 'キッチン用品';

UPDATE Shohin
  SET shiire_tanka = shiire_tanka / 2
 WHERE shohin_bunrui = 'キッチン用品';

これでも正しく更新されますが、これを1つのUPDATE文にまとめることもできます。それは下記の2パターンがあります。
方法①

-- 列をカンマ区切りで並べる
UPDATE Shohin
  SET hanbai_tanka = hanbai_tanka * 10,
    shiire_tanka = shiire_tanka / 2
 WHERE shohin_bunrui = 'キッチン用品';

方法②

-- 列をカッコ（）で囲むことによるリスト表現
UPDATE  Shohin
  SET (hanbai_tanka, shiire_tanka) = (hanbai_tanka * 10,shiire_tanka / 2)
 WHERE shohin_bunrui = 'キッチン用品';

トランザクション

トランザクションは「セットで実行されるべき1つ以上の更新処理の集まりのこと」です
トランザクションを作るには以下のような構文を用います。

トランザクション開始文;

  DML文①;
  DML文②;
  DML文③;
  ・
  ・
  ・
トランザクション終了文（COMMITまたはROLLBACK）;

上記のトランザクション開始文はDBMSによって変わります。
SQL Server、PostgreSQLの場合
BEGIN TRANSACTION

MySQLの場合
START TRANSACTION

Oracle、DB2の場合
ない
尚、これらはあくまで各DBMSによって変化する方言であり標準SQLにおいては「トランザクションは暗黙的に開始され、明示的な開始ステートメントは存在しない」と明記されています。

処理の終了について

明示的な開始ステートメントがないのに対し、トランザクションの終わりはユーザーが指示する必要があります。
終わりには処理の確定と処理の取り消しがあります。
処理の確定にはCOMMITを用います。これはファイルでいうところの「上書きして保存」にあたります。コミットしてしまうと、トランザクション開始前の状態に戻すことはできないので注意が必要です。
次に処理の取り消しにはROLLBACKを用います。これはファイルでいうところの「保存せずに終了」にあたります。
例

BEGIN TRANSACTION;

 -- カッターシャツの販売単価を1000円値引き
 UPDATE Shohin
  SET hanbai_tanka = hanbai_tanka - 1000
 WHERE shohin_mei = 'カッターシャツ';

 -- Tシャツの販売単価を1000円値上げ
 UPDATE Shohin
  SET hanbai_tanka = hanbai_tanka + 1000
 WHERE shohin_mei = 'Tシャツ';

ROLLBACK;

「;」は各UPDATE文毎につける。
ロールバック（処理の取り消し）は保存せずに終了するのであればわざわざ記述する必要性がわからない。後でわかったら追記しようと思います