SQL 第2版ゼロからはじめるデータベース操作アウトプット（第6回）

Posted at 2024-09-28

概要

本記事はSQL 第2版ゼロからはじめるデータベース操作で学んだ内容をまとめたものです。
- 前回は「第3章：集約と並び替え」3-3: 集約した結果に条件を指定する, 3-4: 検索結果を並び替えるで学んだ内容をまとめました：SQL 第2版ゼロからはじめるデータベース操作アウトプット（第5回）
- 今回は「第4章：データの更新」4-1: データの登録, 4-2: データの削除, 4-3: データの更新についてまとめていきます
- 「第4章：データの更新」4-4: トランザクションについては次回まとめます

データの登録

テーブルにデータ（行）を登録するには、INSERT文を使う
- 原則として、INSERT文は1回の実行で1行のデータを登録する
列名や値をカンマで区切って、外側をかっこ()で括った形式を「リスト」と呼ぶ
- 列リストと値リストが存在する。列リストと値リストの列数は一致している必要がある
- 列リスト：登録する列名のリスト (列名1, 列名2, ...)
- 値リスト：登録するデータのリスト (値1, 値2, ...)
テーブル名の後の列リストは、テーブルの全列に対してデータを登録する場合は省略できる
INSERT文の基本構文
- INSERT INTO テーブル名 (列名1, 列名2, ...) VALUES (値1, 値2, ...);
値リストの数が不足してたりするとエラーになる

mysql> CREATE TABLE ShohinIns
  -> (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));
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM ShohinIns;
Empty set (0.02 sec)

mysql> INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) VALUES ('0001', 'T-Shirt', 'Clothing', 1000, 500, '2009-09-20');
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM ShohinIns;
+-----------+------------+---------------+--------------+--------------+------------+
| shohin_id | shohin_mei | shohin_bunrui | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+------------+---------------+--------------+--------------+------------+
| 0001      | T-Shirt    | Clothing      |         1000 |          500 | 2009-09-20 |
+-----------+------------+---------------+--------------+--------------+------------+
1 row in set (0.00 sec)
             
mysql> INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) VALUES ('0001', 'T-Shirt', 'Clothing', 1000, 500);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

複数行の登録

複数行のデータを登録する場合は、INSERT文を複数回実行するか、INSERT文の値リストをカンマで区切って記述する
- INSERT文の値リストをカンマで区切って記述する場合は、VALUES句の後にカンマで区切って値リストを記述する
- INSERT文の記述内容に間違いがあると、エラーが発生する = どの行のどの箇所でエラーだったのかが特定しづらい

mysql> INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) VALUES ('0002', 'Hole Punch', 'Office Supplies', 500, 320, '2009-09-11'), ('0003', 'Dress Shirt', 'Clothing', 4000, 2800, NULL), ('0004', 'Kitchen Knife', 'Kitchen Supplies', 3000, 2800, '2009-09-20') ;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ShohinIns;
+-----------+---------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei    | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+---------------+------------------+--------------+--------------+------------+
| 0001      | T-Shirt       | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch    | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt   | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
+-----------+---------------+------------------+--------------+--------------+------------+
4 rows in set (0.00 sec)

INSERTにおけるポイント

列リストの省略
- テーブルの全列に対してデータを登録する場合は、列リストを省略できる

mysql> INSERT INTO ShohinIns VALUES ('0005', 'Pressure Cooker', 'Kitchen Supplies', 6800, 5000, '2009-01-15');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM ShohinIns;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-Shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
+-----------+-----------------+------------------+--------------+--------------+------------+
5 rows in set (0.00 sec)

NULLを挿入する場合
- NULLを挿入する場合は、値リストにそのままNULLを設定する

mysql> INSERT INTO ShohinIns VALUES ('0006', 'Fork', 'Kitchen Supplies', 500, NULL, '2009-09-20');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM ShohinIns;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-Shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
+-----------+-----------------+------------------+--------------+--------------+------------+
6 rows in set (0.00 sec)

デフォルト値を挿入する
- デフォルト値を挿入する場合は、値リストにそのままDEFAULTを設定する
- もしくは、暗黙的にデフォルト値を挿入する場合は、列リストを省略する
デフォルト値が設定されていない列を省略するとNULLが挿入される
- NOT NULL制約が設定されている列にNULLを挿入するとエラーになる

mysql> INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) VALUES ('0007', 'Grater', 'Kitchen Supplies', DEFAULT, 790, '2009-04-28');
Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM ShohinIns;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-Shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |            0 |          790 | 2009-04-28 |
+-----------+-----------------+------------------+--------------+--------------+------------+
7 rows in set (0.00 sec)

-- 列リストを省略する場合
mysql> INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, shiire_tanka, torokubi) VALUES ('0007', 'Grater', 'Kitchen Supplies', 790, '2009-04-28');
Query OK, 1 row affected (0.02 sec)


mysql> SELECT * FROM ShohinIns;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-Shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |            0 |          790 | 2009-04-28 |
+-----------+-----------------+------------------+--------------+--------------+------------+
7 rows in set (0.00 sec)

-- shiire_tanka列を省略するとNULLが挿入される
mysql> INSERT INTO ShohinIns (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, torokubi) VALUES ('0008', 'Ballpoint pen', 'Office Supplies', 100, '2009-04-28');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM ShohinIns;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-Shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |            0 |          790 | 2009-04-28 |
| 0008      | Ballpoint pen   | Office Supplies  |          100 |         NULL | 2009-04-28 |
+-----------+-----------------+------------------+--------------+--------------+------------+
8 rows in set (0.00 sec)

-- shohin_mei列を省略するとエラーになる：NOT NULL制約に違反
mysql> INSERT INTO ShohinIns (shohin_id, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) VALUES ('0009', 'Office Supplies', 1000, 500, '2009-12-12');
ERROR 1364 (HY000): Field 'shohin_mei' doesn't have a default value

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

データを挿入する方法として、他のテーブルからデータをコピーする方法がある
- INSERT INTO句にSELECT句を指定することで、他のテーブルからデータをコピーできる
- SELECT句で指定する列数とデータ型は、INSERT INTO句で指定する列数とデータ型と一致している必要がある
- SELECT句で指定する列名は、INSERT INTO句で指定する列名と一致している必要はない
- SELECT句で指定する列名は、INSERT INTO句で指定する列名と一致している場合は省略できる
Shohinテーブルと同じ構造のテーブル：ShohinCopyを作成し、Shohinテーブルのデータをコピーすれば、データのバックアップが可能

mysql> CREATE TABLE ShohinCopy
  -> (
       ->     shohin_id     CHAR(4)      NOT NULL,
       ->     shohin_mei    VARCHAR(100) NOT NULL,
       ->     shohin_bunrui VARCHAR(32)  NOT NULL,
       ->     hanbai_tanka  INTEGER,
       ->     shiire_tanka  INTEGER,
       ->     torokubi      DATE,
       ->     PRIMARY KEY (shohin_id)
       -> );
Query OK, 0 rows affected (0.04 sec)

mysql> 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;
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ShohinCopy;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen   | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+-----------------+------------------+--------------+--------------+------------+
8 rows in set (0.00 sec)

mysql> INSERT INTO ShohinCopy (shohin_id, shohin_mei, shohin_bunrui, hanbai_tanka, shiire_tanka, torokubi) SELECT * FROM Shohin;
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ShohinCopy;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen   | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+-----------------+------------------+--------------+--------------+------------+
8 rows in set (0.00 sec)

mysql> DELETE FROM ShohinCopy;
Query OK, 8 rows affected (0.00 sec)

mysql> INSERT INTO ShohinCopy SELECT * FROM Shohin;
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ShohinCopy;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen   | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+-----------------+------------------+--------------+--------------+------------+
8 rows in set (0.00 sec)

INSERT文中のSELECT文には、WHERE句やORDER BY句を指定することもできる
- WHERE句やORDER BY句を指定することで、特定のデータだけをコピーすることも可能

mysql> CREATE TABLE ShohinBunrui
  -> (
       ->     shohin_bunrui    VARCHAR(32) NOT NULL,
       ->     sum_hanbai_tanka INTEGER,
       ->     sum_shiire_tanka INTEGER,
       ->     PRIMARY KEY (shohin_bunrui)
       -> );
Query OK, 0 rows affected (0.11 sec)

mysql> 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;
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM ShohinBunrui;
+------------------+------------------+------------------+
| shohin_bunrui    | sum_hanbai_tanka | sum_shiire_tanka |
+------------------+------------------+------------------+
| Clothing         |             5000 |             3300 |
| Kitchen Supplies |            11180 |             8590 |
| Office Supplies  |              600 |              320 |
+------------------+------------------+------------------+
3 rows in set (0.00 sec)

データの削除

テーブルからデータ（行）を削除するには、DELETE文を使う
- DELETE文は、WHERE句を使って削除対象のデータを指定する
- DELETE文を実行すると、削除対象のデータが削除される
- WHERE句をつけない場合、テーブルの全データが削除される

mysql> SELECT * FROM ShohinCopy;
+-----------+-----------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei      | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+-----------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt         | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch      | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0003      | Dress Shirt     | Clothing         |         4000 |         2800 | NULL       |
| 0004      | Kitchen Knife   | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0005      | Pressure Cooker | Kitchen Supplies |         6800 |         5000 | 2009-01-15 |
| 0006      | Fork            | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater          | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen   | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+-----------------+------------------+--------------+--------------+------------+
8 rows in set (0.00 sec)

mysql> DELETE FROM ShohinCopy WHERE hanbai_tanka >= 4000;
Query OK, 2 rows affected (0.01 sec)

mysql> SELECT * FROM ShohinCopy;
+-----------+---------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei    | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+---------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt       | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch    | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0004      | Kitchen Knife | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0006      | Fork          | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater        | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+---------------+------------------+--------------+--------------+------------+
6 rows in set (0.01 sec)

DELETE文以外にも、多くのDBMSではTRUNCATE文を使ってテーブルの全データを削除することができる
- TRUNCATE文は、テーブルの全データを削除するため、WHERE句を指定することはできない

mysql> SELECT * FROM ShohinCopy;
+-----------+---------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei    | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+---------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt       | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch    | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0004      | Kitchen Knife | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0006      | Fork          | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater        | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+---------------+------------------+--------------+--------------+------------+
6 rows in set (0.00 sec)

mysql> TRUNCATE ShohinCopy;
Query OK, 0 rows affected (0.04 sec)

mysql> SELECT * FROM ShohinCopy;
Empty set (0.00 sec)

データの更新

テーブルのデータ（行）を更新するには、UPDATE文を使う
- UPDATE文は、SET句を使って更新する列と値を指定し、WHERE句を使って更新対象のデータを指定する
- UPDATE文を実行すると、更新対象のデータが更新される
- WHERE句をつけない場合、テーブルの全データが更新される

mysql> SELECT * FROM Shohin;
+-----------+---------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei    | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+---------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt       | Clothing         |         1000 |          500 | 2009-09-20 |
| 0002      | Hole Punch    | Office Supplies  |          500 |          320 | 2009-09-11 |
| 0004      | Kitchen Knife | Kitchen Supplies |         3000 |         2800 | 2009-09-20 |
| 0006      | Fork          | Kitchen Supplies |          500 |         NULL | 2009-09-20 |
| 0007      | Grater        | Kitchen Supplies |          880 |          790 | 2008-04-28 |
| 0008      | Ballpoint Pen | Office Supplies  |          100 |         NULL | 2009-11-11 |
+-----------+---------------+------------------+--------------+--------------+------------+
6 rows in set (0.00 sec)

mysql> UPDATE Shohin SET torokubi = '2009-10-10';
Query OK, 6 rows affected (0.02 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> SELECT * FROM Shohin;
+-----------+---------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei    | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+---------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt       | Clothing         |         1000 |          500 | 2009-10-10 |
| 0002      | Hole Punch    | Office Supplies  |          500 |          320 | 2009-10-10 |
| 0004      | Kitchen Knife | Kitchen Supplies |         3000 |         2800 | 2009-10-10 |
| 0006      | Fork          | Kitchen Supplies |          500 |         NULL | 2009-10-10 |
| 0007      | Grater        | Kitchen Supplies |          880 |          790 | 2009-10-10 |
| 0008      | Ballpoint Pen | Office Supplies  |          100 |         NULL | 2009-10-10 |
+-----------+---------------+------------------+--------------+--------------+------------+
6 rows in set (0.00 sec)

mysql> UPDATE Shohin SET hanbai_tanka = hanbai_tanka * 100 WHERE shohin_bunrui = 'Kitchen Supplies';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM Shohin;
+-----------+---------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei    | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+---------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt       | Clothing         |         1000 |          500 | 2009-10-10 |
| 0002      | Hole Punch    | Office Supplies  |          500 |          320 | 2009-10-10 |
| 0004      | Kitchen Knife | Kitchen Supplies |       300000 |         2800 | 2009-10-10 |
| 0006      | Fork          | Kitchen Supplies |        50000 |         NULL | 2009-10-10 |
| 0007      | Grater        | Kitchen Supplies |        88000 |          790 | 2009-10-10 |
| 0008      | Ballpoint Pen | Office Supplies  |          100 |         NULL | 2009-10-10 |
+-----------+---------------+------------------+--------------+--------------+------------+
6 rows in set (0.01 sec)

値をNULLで更新することを「NULLクリア」と呼ぶ
- NULLクリアが可能なのは、NULLを許容する列のみ（= NOT NULL制約が設定されていない列）

mysql> UPDATE Shohin SET torokubi = NULL WHERE shohin_id = 0008;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM Shohin;
+-----------+---------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei    | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+---------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt       | Clothing         |         1000 |          500 | 2009-10-10 |
| 0002      | Hole Punch    | Office Supplies  |          500 |          320 | 2009-10-10 |
| 0004      | Kitchen Knife | Kitchen Supplies |       300000 |         2800 | 2009-10-10 |
| 0006      | Fork          | Kitchen Supplies |        50000 |         NULL | 2009-10-10 |
| 0007      | Grater        | Kitchen Supplies |        88000 |          790 | 2009-10-10 |
| 0008      | Ballpoint Pen | Office Supplies  |          100 |         NULL | NULL       |
+-----------+---------------+------------------+--------------+--------------+------------+
6 rows in set (0.00 sec)

mysql> UPDATE Shohin SET shohin_mei = NULL WHERE shohin_id = 0008;
ERROR 1048 (23000): Column 'shohin_mei' cannot be null

データの更新は複数列に対して行うことも可能
- SET句に複数の列と値を指定することで、複数列に対して一度に更新できる

mysql> UPDATE Shohin SET hanbai_tanka = hanbai_tanka * 10, shiire_tanka = shiire_tanka / 2  WHERE shohin_bunrui = 'Kitchen Supplies';
Query OK, 3 rows affected (0.01 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM Shohin;
+-----------+---------------+------------------+--------------+--------------+------------+
| shohin_id | shohin_mei    | shohin_bunrui    | hanbai_tanka | shiire_tanka | torokubi   |
+-----------+---------------+------------------+--------------+--------------+------------+
| 0001      | T-shirt       | Clothing         |         1000 |          500 | 2009-10-10 |
| 0002      | Hole Punch    | Office Supplies  |          500 |          320 | 2009-10-10 |
| 0004      | Kitchen Knife | Kitchen Supplies |      3000000 |         1400 | 2009-10-10 |
| 0006      | Fork          | Kitchen Supplies |       500000 |         NULL | 2009-10-10 |
| 0007      | Grater        | Kitchen Supplies |       880000 |          395 | 2009-10-10 |
| 0008      | Ballpoint Pen | Office Supplies  |          100 |         NULL | NULL       |
+-----------+---------------+------------------+--------------+--------------+------------+
6 rows in set (0.00 sec)

まとめ

本記事では「第4章：データの更新」4-1: データの登録, 4-2: データの削除, 4-3: データの更新についてまとめました！

参考

SQL 第2版ゼロからはじめるデータベース操作

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

SQL 第2版 ゼロからはじめるデータベース操作 アウトプット（第6回）

概要

データの登録

複数行の登録

INSERTにおけるポイント

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

データの削除

データの更新

まとめ

参考

SQL 第2版ゼロからはじめるデータベース操作アウトプット（第6回）