【SQLに慣れる】データの挿入・表示・更新・削除

今回はデータベース版CRUDとも言うべき、データの挿入・表示・更新・削除に関する基本コマンドを確認する。

レコードの挿入

INSERTコマンドでレコードを挿入できる。

memberテーブルのキャラクター名、ジョブ名はFF14から拝借した。ちなみに自分のメインジョブはモンク（弱小）です。

下記のコマンドでは、全カラムに対応する並び順で各カラムの内容を指定している。

yuki:sample_db>INSERT INTO member VALUES(1, 'ミンフィリア', '採掘師');
Query OK, 1 row affected (0.00 sec)

idはAUTO_INCREMENTなので自動的に入力される。下記のコマンドでは、id以外のカラム（nameとjob）を指定し、データを挿入している。

yuki:sample_db>INSERT INTO member (name, job) VALUES('サンクレッド', '双剣士');
Query OK, 1 row affected (0.00 sec)

しかし、AUTO_INCREMENTだからと言ってidを無視するわけにはいかない。下記コマンドのようにカラムを指定しなかった場合は、全カラムを指定したとみなされる。そのためVALUES以下も全カラム数に合わせた数のデータを用意しなければ、エラーになってしまう。

yuki:sample_db>INSERT INTO member VALUES('ミンフィリア', '採掘師');
ERROR 1136 (21S01): Column count doesn't match value count at row 1

複数レコードを1回で入力することもできる。

yuki:sample_db>INSERT INTO member (name, job) VALUES('パパリモ', '呪術士'), ('イダ', '格闘士');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

レコードの表示

SELECTコマンドで、先ほどレコードを挿入したmemberテーブルの内容を表示させる。

*で全カラムを表示できる。

yuki:sample_db>SELECT * FROM member;
+----+--------------------+-----------+
| id | name               | job       |
+----+--------------------+-----------+
|  1 | ミンフィリア       | 採掘師    |
|  2 | サンクレッド       | 双剣士    |
|  3 | パパリモ           | 呪術士    |
|  4 | イダ               | 格闘士    |
+----+--------------------+-----------+
4 rows in set (0.00 sec)

ちなみに、INSERTコマンドはSELECTコマンドで抽出したレコードをそのまま挿入できる。

yuki:sample_db>INSERT INTO member (name, job) SELECT name, job FROM member;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

yuki:sample_db>SELECT * FROM member;
+----+--------------------+-----------+
| id | name               | job       |
+----+--------------------+-----------+
|  1 | ミンフィリア       | 採掘師    |
|  2 | サンクレッド       | 双剣士    |
|  3 | パパリモ           | 呪術士    |
|  4 | イダ               | 格闘士    |
|  5 | ミンフィリア       | 採掘師    |
|  6 | サンクレッド       | 双剣士    |
|  7 | パパリモ           | 呪術士    |
|  8 | イダ               | 格闘士    |
+----+--------------------+-----------+
8 rows in set (0.00 sec)

idはPRIMARY KEYとして設定されているため、重複が許されない。SELECTコマンドでidを抽出しても、それを登録することができないのだ。

この場合、idはAUTO_INCREMENTによる自動入力に任せ、id以外のカラム（nameとjob）のみ指定してレコードを挿入する必要がある。

下記コマンドのようにidを含めてレコードを挿入すると、当然エラーが発生してしまう。

yuki:sample_db>INSERT INTO member (id, name, job) SELECT * FROM member;
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

レコードの削除

DELETEコマンドで、先ほど複製したレコードを削除する。WHERE句でidが4より大きいレコードのみ対象にしている。

もしWHERE句を忘れた場合、全てのレコードが削除されてしまうので要注意だ。

yuki:sample_db>DELETE FROM member WHERE id > 4;
Query OK, 4 row affected (0.00 sec)

yuki:sample_db>SELECT * FROM member;
+----+--------------------+-----------+
| id | name               | job       |
+----+--------------------+-----------+
|  1 | ミンフィリア       | 採掘師    |
|  2 | サンクレッド       | 双剣士    |
|  3 | パパリモ           | 呪術士    |
|  4 | イダ               | 格闘士    |
+----+--------------------+-----------+
4 rows in set (0.00 sec)

レコードの更新

UPDATEコマンドでデータの更新ができる。id=1のレコードのjobを変更してみる。

こちらのコマンドも、WHERE句を忘れた場合は全てのレコードが変更されてしまう。

yuki:sample_db>UPDATE member SET job = '暁の血盟盟主' WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

yuki:sample_db>SELECT * FROM member;
+----+--------------------+--------------------+
| id | name               | job                |
+----+--------------------+--------------------+
|  1 | ミンフィリア       | 暁の血盟盟主       |
|  2 | サンクレッド       | 双剣士             |
|  3 | パパリモ           | 呪術士             |
|  4 | イダ               | 格闘士             |
+----+--------------------+--------------------+
4 rows in set (0.00 sec)

連番のリセット

新たなメンバーを登録しようとしたが、idがずれてしまった。

yuki:sample_db>INSERT INTO member (name, job) VALUES('ヤ・シュトラ', '幻術士');
Query OK, 1 row affected (0.00 sec)

yuki:sample_db>SELECT * FROM member;
+----+--------------------+--------------------+
| id | name               | job                |
+----+--------------------+--------------------+
|  1 | ミンフィリア       | 暁の血盟盟主       |
|  2 | サンクレッド       | 双剣士             |
|  3 | パパリモ           | 呪術士             |
|  4 | イダ               | 格闘士             |
| 12 | ヤ・シュトラ       | 幻術士             |
+----+--------------------+--------------------+
5 rows in set (0.00 sec)

どうやらデータの複製・削除を行っているうちに、連番のはずであるidの数字がずれてしまったようだ。

UPDATEコマンドを使用して手直しで修正もできるが、下記のALTER TABLEコマンドを使用すれば、AUTO_INCREMENTの値をリセットできる。

一度該当のレコードを削除し、AUTO_INCREMENTの値をリセットした上で改めてレコードを挿入してみた。

yuki:sample_db>ALTER TABLE member AUTO_INCREMENT=5;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

yuki:sample_db>INSERT INTO member (name, job) VALUES('ヤ・シュトラ', '幻術士');
Query OK, 1 row affected (0.01 sec)

yuki:sample_db>SELECT * FROM member;
+----+--------------------+--------------------+
| id | name               | job                |
+----+--------------------+--------------------+
|  1 | ミンフィリア       | 暁の血盟盟主       |
|  2 | サンクレッド       | 双剣士             |
|  3 | パパリモ           | 呪術士             |
|  4 | イダ               | 格闘士             |
|  5 | ヤ・シュトラ       | 幻術士             |
+----+--------------------+--------------------+
5 rows in set (0.00 sec)

無事に連番で登録されたようだ。

テーブルの初期化

TRUNCATE TABLEコマンドでテーブルを初期化できる。このコマンドはDELETEコマンドと違い、テーブルを一度DROPし、再度テーブルをCREATEするようだ。（今回、コマンドの実行はしていない。）

yuki:sample_db>TRUNCATE TABLE member;

今回はここまで。