Blank?=False

ゆるゆる仕事したいフリーランスエンジニアの記事

SQL ゼロから始めるデータベース操作 第4章 データの更新

今回から、実際のデータの登録や更新、削除を学んでいきます。

今回のキーワード
INSERT UPDATE DELETE

CD付 SQL ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)

CD付 SQL ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)

サンプルテーブル

今回も、以下のサンプルテーブルで説明します。
テーブル名はSampleです。

id Name VALUE Date Category
0001 2000000 2009-11-25 乗り物
0002 えんぴつ 100 2009-11-27 文房具
0003 キャビネット 12500 2009-11-27 家具
0004 消しゴム 12500 2009-12-11 文房具
0005 バイク 195000 2009-12-21 乗り物
0006 椅子 8500 2010-05-12 家具
0007 ボールペン 8500 2010-06-08 文房具
0008 クレヨン 820 2010-06-15 文房具
0009 ガレージ 5282000 2010-09-01 NULL
0010 MacBookPro NULL 2011-02-03 NULL


INSERT

文法

INSERT INTO Sample (id,Name,VALUE,Date,Category) 
VALUES ('0011', 'Mac Pro', 1250000, '2015-02-20', NULL);

INSERT INTO の後にテーブル名、フィールドリスト、VALUESの後に実際に挿入する値を書きます。

値を挿入しないフィールドは省略できます。その場合、デフォルト値が挿入されます。
ただし、NOTNULLのフィールドでデフォルト値がNULLになっている場合、エラーが発生します。

mysql> INSERT INTO Sample (Name) VALUES ('Power BookG4');
ERROR 1364 (HY000): Field 'id' doesn't have a default value


こういう場合は、ちゃんと値を設定してあげます。
設定されなかった値は全部デフォルト値のNULLになっています。

mysql> INSERT INTO Sample (id,Name) VALUES ('0012', 'Power BookG4');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM Sample Where id='0012';
+------+--------------+-------+------+----------+
| id   | Name         | VALUE | Date | Category |
+------+--------------+-------+------+----------+
| 0012 | Power BookG4 |  NULL | NULL | NULL     |
+------+--------------+-------+------+----------+



逆に、すべてのフィールドに値を設定する場合はフィールドリストを省略できます。

INSERT INTO Sample
VALUES ('0011', 'Mac Pro', 1250000, '2015-02-20', DEFAULT);

デフォルト値を設定する場合、DEFAULTと入力すればOKです。

実例

他のテーブルからデータをコピー
 INSERT INTO Sample  SELECT * FROM Sample2;

フィールドと型が一致していれば、これでSample2のすべてのレコードが挿入されます。
条件を付ける場合、WHERE句を追加すればOKです。

INSERT文内のSELECTについて、本書の鉄則を引用します。

INSERT文内のSELECT文は、WHERE,GROUP BY等、どんなSQL文も使用可能。
ただしORDER BY句は使っても効果がない。

ORDER BY句はあくまで並び替えているだけなので、挿入するものが変わるわけではないのが
原因だと思います。

SQLの約束の1つに、ORDERを指定しない場合の並び順はランダムであること
綺麗に並んでいたとしても、それはたまたまであること、というのがあるように、
挿入されるものを並び替えたとしても挿入後その並び順になっていてもたまたまということなのでしょうか。


UPDATE

文法

UPDATE Sample SET Name=`Foo`;

実例

特定のデータのみを更新する

探索型UPDATEと言います。
以下の例では、カテゴリが'乗り物'のすべてのレコードを
'移動手段'というカテゴリに更新しています。

mysql> SELECT * FROM Sample Where Category='乗り物';
+------+-----------+---------+------------+-----------+
| id   | Name      | VALUE   | Date       | Category  |
+------+-----------+---------+------------+-----------+
| 0001 | 車        | 2000000 | 2009-11-25 | 乗り物    |
| 0005 | バイク    |  195000 | 2009-12-21 | 乗り物    |
+------+-----------+---------+------------+-----------+
2 rows in set (0.00 sec)

mysql> UPDATE Sample Set Category='移動手段' Where Category='乗り物';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> SELECT * FROM Sample Where Category='移動手段';
+------+-----------+---------+------------+--------------+
| id   | Name      | VALUE   | Date       | Category     |
+------+-----------+---------+------------+--------------+
| 0001 | 車        | 2000000 | 2009-11-25 | 移動手段     |
| 0005 | バイク    |  195000 | 2009-12-21 | 移動手段     |
+------+-----------+---------+------------+--------------+
2 rows in set (0.00 sec)
複数列の更新

複数の列を更新する場合、1回1回UPDATE文で更新する方法もありますが、
それだとSQL文が読みにくくなりがちです。

そのため、複数列を更新する方法が用意されています。
以下の例では、カテゴリがNULLのレコードのVALUEとカテゴリをそれぞれ、0,未設定に更新します。

mysql> SELECT * FROM Sample WHERE Category IS NULL;
+------+--------------+---------+------------+----------+
| id   | Name         | VALUE   | Date       | Category |
+------+--------------+---------+------------+----------+
| 0009 | ガレージ     | 5282000 | 2010-09-01 | NULL     |
| 0010 | MacBookPro   |    NULL | 2011-02-03 | NULL     |
| 0011 | PowerBook G4 |  215000 | 2015-02-20 | NULL     |
| 0012 | Mac Pro      | 1250000 | 2015-02-20 | NULL     |
+------+--------------+---------+------------+----------+
4 rows in set (0.00 sec)

mysql> UPDATE Sample
    -> SET VALUE = 0,
    ->     Category = '未設定'
    -> WHERE Category IS NULL;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM Sample WHERE Category = '未設定';
+------+--------------+-------+------------+-----------+
| id   | Name         | VALUE | Date       | Category  |
+------+--------------+-------+------------+-----------+
| 0009 | ガレージ     |     0 | 2010-09-01 | 未設定    |
| 0010 | MacBookPro   |     0 | 2011-02-03 | 未設定    |
| 0011 | PowerBook G4 |     0 | 2015-02-20 | 未設定    |
| 0012 | Mac Pro      |     0 | 2015-02-20 | 未設定    |
+------+--------------+-------+------------+-----------+
4 rows in set (0.00 sec)


DELETE

文法

DELETE FROM Sample;

DELETE句の鉄則として、こう書かれていました。

DELETEの削除対象はテーブルや列ではなく、「レコード」である。

どういうことかというと、
列を対象にすることはできないので、DELETE SELECT * FROM Sample等、列を指定することはできない。
また、テーブルを対象にもできないので、DELETE Sampleもできない。
あくまで、を対象とするため、FROMが必要ということですね。

実例

特定のデータのみを削除する

探索型DELETEと言います。
UPDATEと同様、WHERE句を使い条件を設定します。 以下の例では、カテゴリがNULLのものを削除します。

DELETE FROM Sample WHERE Category IS NULL


TRUNCATEコマンド

標準SQLではないが、多くのデータベースで対応されているコマンド。
コラムで紹介されていたので、説明してみます。
(※対応DB Oracle, SQLServer, PostgreSQL, MySQL 等)

TRUNCATE Sample;

指定したテーブルのデータをすべて削除する。
DELETE FROM <テーブル名>文との違いはWHERE句で削除するデータを絞り込むことができない代わり、
実効速度に優れる、というもの。

たとえば、1ヶ月分のデータしか保持しないため、月のはじめには全部のデータを削除する〜
というテーブルであれば、DELETE句を使うよりも早く処理が終わる。

ただし、Oracleの場合トランザクションを使えないので要注意