前回からの続きです。
SQL ~ DB 作成から基本操作、テーブルの結合まで ~ DB 作成編
テーブルの結合編はこちらから
SQL ~ DB 作成から基本操作、テーブルの結合まで ~ テーブルの結合編
ここでは基本操作についてまとめています。
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 |
+----+----------------+------------------+-------+----------+----------+-------------+---------------------+