[MySQL] DML-INSERT, UPDATE, DELETE

INSERT

INSERT INTO <테이블-이름> (필드1, 필드2, 필드3, 필드4, …)
	VALUES (필드1의 값, 필드2의 값, 필드3의 값, 필드4의 값, …)

INSERT INTO <테이블-이름>
	VALUES (필드1의 값, 필드2의 값, 필드3의 값, 필드4의 값, …)

 

mysql> DESC ROLE;
+-------------+--------------+------+-----+---------+-------+
| Field       | Type         | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| role_id     | int(11)      | NO   | PRI | NULL    |       |
| description | varchar(100) | YES  |     | NULL    |       |
+-------------+--------------+------+-----+---------+-------+

mysql> SELECT * FROM ROLE;
+---------+-----------------+
| role_id | description     |
+---------+-----------------+
|     100 | Developer       |
|     101 | Researcher      |
|     102 | Project manager |
+---------+-----------------+
3 rows in set (0.00 sec)

mysql> INSERT ROLE (ROLE_ID, DESCRIPTION) VALUES (200, 'CEO');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM ROLE;
+---------+-----------------+
| role_id | description     |
+---------+-----------------+
|     100 | Developer       |
|     101 | Researcher      |
|     102 | Project manager |
|     200 | CEO             |
+---------+-----------------+
4 rows in set (0.00 sec)

mysql> INSERT ROLE (DESCRIPTION) VALUES (200);
ERROR 1364 (HY000): Field 'role_id' doesn't have a default value
mysql> INSERT ROLE (ROLE_ID) VALUES (103);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM ROLE;
+---------+-----------------+
| role_id | description     |
+---------+-----------------+
|     100 | Developer       |
|     101 | Researcher      |
|     102 | Project manager |
|     103 | NULL            |
|     200 | CEO             |
+---------+-----------------+
5 rows in set (0.00 sec)

---

UPDATE

 UPDATE <테이블-이름>
	SET <필드1> = <필드1의-값>, <필드2> = <필드2의-값>, <필드3> = <필드3의-값>, …
	WHERE <조건식>

 

mysql> SELECT * FROM ROLE;
+---------+-----------------+
| role_id | description     |
+---------+-----------------+
|     100 | Developer       |
|     101 | Researcher      |
|     102 | Project manager |
|     103 | NULL            |
|     200 | CEO             |
+---------+-----------------+
5 rows in set (0.00 sec)

mysql> UPDATE ROLE SET DESCRIPTION = 'CTO' WHERE ROLE_ID = 200;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM ROLE;
+---------+-----------------+
| role_id | description     |
+---------+-----------------+
|     100 | Developer       |
|     101 | Researcher      |
|     102 | Project manager |
|     103 | NULL            |
|     200 | CTO             |
+---------+-----------------+
5 rows in set (0.00 sec)

---

DELETE

DELETE
	FROM <테이블-이름>
	WHERE <조건식>

 

mysql> SELECT * FROM ROLE;
+---------+-----------------+
| role_id | description     |
+---------+-----------------+
|     100 | Developer       |
|     101 | Researcher      |
|     102 | Project manager |
|     103 | NULL            |
|     200 | CTO             |
+---------+-----------------+
5 rows in set (0.00 sec)

mysql> DELETE FROM ROLE WHERE ROLE_ID = 200;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM ROLE;
+---------+-----------------+
| role_id | description     |
+---------+-----------------+
|     100 | Developer       |
|     101 | Researcher      |
|     102 | Project manager |
|     103 | NULL            |
+---------+-----------------+
4 rows in set (0.00 sec)