SQL ~ DB 作成から基本操作、テーブルの結合まで ~ 基本操作編

前回からの続きです。
[SQL ~ DB 作成から基本操作、テーブルの結合まで ~ DB 作成編](https://qiita.com/kouhei_wuo_0825/items/830d8f524336dba88fbf)

テーブルの結合編はこちらから
[SQL ~ DB 作成から基本操作、テーブルの結合まで ~ テーブルの結合編](https://qiita.com/kouhei_wuo_0825/items/487dc5ababdedac583e5)

ここでは基本操作についてまとめています。

## INSERT 文

基本構文

```
INSERT INTO テーブル名 VALUES (値);
```
列を指定して挿入。全ての列を指定しなくても OK。

```
INSERT INTO wheelchair(id, name, type, floor_id, occupnat_id) VALUES (1,'low_wheelchair','wheelchair', 1, 1);

```

その他の値を入れた結果。

```
mysql> select * from wheelchair;
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+
| id | name           | type             | money | purchase | floor_id | occupant_id | created_at          |
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+
|  1 | low_wheelchair | wheelchair       |  NULL | NULL     |        1 |           1 | 2020-10-16 10:16:53 |
|  2 | wheelchair     | wheelchair       |  NULL | NULL     |        1 |           2 | 2020-10-16 10:17:48 |
|  3 | action_3       | modular          |  NULL | NULL     |        2 |           3 | 2020-10-16 10:19:08 |
|  4 | rebo           | modular          |  NULL | NULL     |        3 |           4 | 2020-10-16 10:20:04 |
|  5 | mytilt         | tilt_ant_recline |  NULL | NULL     |        4 |           5 | 2020-10-16 10:22:33 |
|  6 | mytilt_pro     | tilt_ant_recline |  NULL | NULL     |        5 |           6 | 2020-10-16 10:23:11 |
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+
```

## SELECT 文
テーブル内のカラムを選んで検索する。「*」を使うと全検索になる。

```
SELECT	カラム名,カラム名... FROM	テーブル名
```
### カラムに対し、演算子を利用できる
```
SELECT カラム名(+, -, *, /)2 FROM テーブル名
```

### WHERE 句で絞り込み検索
#### 比較演算子を使用する。
```
SELECT	カラム名 FROM	テーブル名 WHERE カラム名 (=, <, >, <=, >=, <>) 20(条件式)
```
```
mysql> mysql> select * from wheelchair where name = 'low_wheelchair';
+----+----------------+------------+-------+----------+----------+-------------+---------------------+
| id | name           | type       | money | purchase | floor_id | occupant_id | created_at          |
+----+----------------+------------+-------+----------+----------+-------------+---------------------+
|  1 | low_wheelchair | wheelchair |  NULL | NULL     |        1 |           1 | 2020-10-16 10:16:53 |
+----+----------------+------------+-------+----------+----------+-------------+---------------------+
```
wheelchair テーブルの name が low_whhelchair を検索している。

#### 複合条件には論理演算子
```
SELECT	カラム名 FROM	テーブル名 WHERE カラム名 (=, <, >, <=, >=, <>) 20 (and, or, not) カラム名 (=, <, >, <=, >=, <>) 40
```

```
mysql> select * from wheelchair where floor_id = 1 and occupant_id = 2;
+----+------------+------------+-------+----------+----------+-------------+---------------------+
| id | name       | type       | money | purchase | floor_id | occupant_id | created_at          |
+----+------------+------------+-------+----------+----------+-------------+---------------------+
|  2 | wheelchair | wheelchair |  NULL | NULL     |        1 |           2 | 2020-10-16 10:17:48 |
+----+------------+------------+-------+----------+----------+-------------+---------------------+
```
wheelchair テーブルの floor_id が 1 「かつ（and）」 occupant_id が 2 を検索。

```
mysql> select * from wheelchair where floor_id = 1 or occupant_id = 2;
+----+----------------+------------+-------+----------+----------+-------------+---------------------+
| id | name           | type       | money | purchase | floor_id | occupant_id | created_at          |
+----+----------------+------------+-------+----------+----------+-------------+---------------------+
|  1 | low_wheelchair | wheelchair |  NULL | NULL     |        1 |           1 | 2020-10-16 10:16:53 |
|  2 | wheelchair     | wheelchair |  NULL | NULL     |        1 |           2 | 2020-10-16 10:17:48 |
+----+----------------+------------+-------+----------+----------+-------------+---------------------+
```
wheelchair テーブルの floor_id が 1 「または（or）」 occupant_id が 2 を検索。


## UPDATE 文
基本構文

```
UPDATE  表名
SET    列名 = 値
WHERE　更新する行を特定する条件;
```
```
mysql> UPDATE wheelchair SET money = 10000 WHERE type = 'wheelchair';
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0
```
wheelchair テーブルの 列 type が wheelchair の列 money を 10000 に更新する処理。

結果

```
mysql> select * from wheelchair;
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+
| id | name           | type             | money | purchase | floor_id | occupant_id | created_at          |
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+
|  1 | low_wheelchair | wheelchair       | 10000 | NULL     |        1 |           1 | 2020-10-16 10:16:53 |
|  2 | wheelchair     | wheelchair       | 10000 | NULL     |        1 |           2 | 2020-10-16 10:17:48 |
|  3 | action_3       | modular          |  NULL | NULL     |        2 |           3 | 2020-10-16 10:19:08 |
|  4 | rebo           | modular          |  NULL | NULL     |        3 |           4 | 2020-10-16 10:20:04 |
|  5 | mytilt         | tilt_ant_recline |  NULL | NULL     |        4 |           5 | 2020-10-16 10:22:33 |
|  6 | mytilt_pro     | tilt_ant_recline |  NULL | NULL     |        5 |           6 | 2020-10-16 10:23:11 |
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+
```
列 type が wheelchair は2つあるので2つとも更新された。

## DELETE 文
基本構文

```
DELETE  FROM  テーブル名 
WHERE  (条件);
```

```
mysql> select * from wheelchair;
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+
| id | name           | type             | money | purchase | floor_id | occupant_id | created_at          |
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+
|  1 | low_wheelchair | wheelchair       | 10000 | NULL     |        1 |           1 | 2020-10-16 10:16:53 |
|  2 | wheelchair     | wheelchair       | 10000 | NULL     |        1 |           2 | 2020-10-16 10:17:48 |
|  3 | action_3       | modular          |  NULL | NULL     |        2 |           3 | 2020-10-16 10:19:08 |
|  4 | rebo           | modular          |  NULL | NULL     |        3 |           4 | 2020-10-16 10:20:04 |
|  5 | mytilt         | tilt_ant_recline |  NULL | NULL     |        4 |           5 | 2020-10-16 10:22:33 |
|  6 | mytilt_pro     | tilt_ant_recline |  NULL | NULL     |        5 |           6 | 2020-10-16 10:23:11 |
|  7 | 削除用         | 削除用           |  NULL | NULL     |     NULL |        NULL | 2020-10-16 10:59:47 |
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+
```
削除用のレコードを削除

```
mysql> DELETE FROM wheelchair WHERE id = 7;
Query OK, 1 row affected (0.01 sec)
```
結果

```

mysql> select * from wheelchair;

+----+----------------+------------------+-------+----------+----------+-------------+---------------------+
| id | name           | type             | money | purchase | floor_id | occupant_id | created_at          |
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+
|  1 | low_wheelchair | wheelchair       | 10000 | NULL     |        1 |           1 | 2020-10-16 10:16:53 |
|  2 | wheelchair     | wheelchair       | 10000 | NULL     |        1 |           2 | 2020-10-16 10:17:48 |
|  3 | action_3       | modular          |  NULL | NULL     |        2 |           3 | 2020-10-16 10:19:08 |
|  4 | rebo           | modular          |  NULL | NULL     |        3 |           4 | 2020-10-16 10:20:04 |
|  5 | mytilt         | tilt_ant_recline |  NULL | NULL     |        4 |           5 | 2020-10-16 10:22:33 |
|  6 | mytilt_pro     | tilt_ant_recline |  NULL | NULL     |        5 |           6 | 2020-10-16 10:23:11 |
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+
```