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 |
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+