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

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

# レコードの挿入

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

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

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

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

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

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

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

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

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

```sql
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テーブル``の内容を表示させる。

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

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

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

```sql
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``を含めてレコードを挿入すると、当然エラーが発生してしまう。

```sql
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句``を忘れた場合、全てのレコードが削除されてしまうので要注意だ。

```sql
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句``を忘れた場合は全てのレコードが変更されてしまう。

```sql
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``がずれてしまった。

```sql
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``の値をリセットした上で改めてレコードを挿入してみた。

```sql
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``するようだ。（今回、コマンドの実行はしていない。）

```sql
yuki:sample_db>TRUNCATE TABLE member;
```

今回はここまで。