SQL ゼロから始めるデータベース操作 第4章 データの更新
今回から、実際のデータの登録や更新、削除を学んでいきます。
今回のキーワード
INSERT
UPDATE
DELETE
CD付 SQL ゼロからはじめるデータベース操作 (プログラミング学習シリーズ)
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2010/06/29
- メディア: 大型本
- 購入: 39人 クリック: 484回
- この商品を含むブログ (16件) を見る
サンプルテーブル
今回も、以下のサンプルテーブルで説明します。
テーブル名は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
句を使うよりも早く処理が終わる。