생활코딩 #SQL의 DELETE 구문(DELETE)

 오늘 배운 것 

SQL의 DELETE 구문

먼저 기본 문법은 아래와 같다. 싱글과 멀티로 나눠진다.

#Single-Table Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [[AS] tbl_alias]
    [PARTITION (partition_name [, partition_name] ...)]
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
    
    
    
#Multiple-Table Syntax

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

 

실습은 다음과 같다

ex. DELETE FROM topic WHERE id = 3;

여기서도 마찬가지로 WHERE 빠뜨리면 모든 데이터가 사라진다!!!

MariaDB [daco]> SELECT * FROM topic;
+----+------------+----------------------------+---------------------+-----------+-----------+
| id | title      | description                | created             | author    | profile   |
+----+------------+----------------------------+---------------------+-----------+-----------+
|  1 | MySQL      | MySQL is my first database | 2021-08-09 21:07:51 | daco      | developer |
|  2 | ORACLE 2   | ORACLE is database         | 2021-08-09 21:13:02 | dacoZZING | developer |
|  3 | PostgreSQL | PostgreSQL is database     | 2021-08-09 21:13:47 | daco      | developer |
|  4 | MongoDB    | MongoDB is database        | 2021-08-09 21:14:05 | daco      | developer |
|  5 | SQL Server | SQL Server is database     | 2021-08-09 21:14:57 | daco      | developer |
+----+------------+----------------------------+---------------------+-----------+-----------+
5 rows in set (0.000 sec)

MariaDB [daco]> DELETE FROM topic WHERE id = 3;
Query OK, 1 row affected (0.003 sec)

MariaDB [daco]> SELECT * FROM topic;
+----+------------+----------------------------+---------------------+-----------+-----------+
| id | title      | description                | created             | author    | profile   |
+----+------------+----------------------------+---------------------+-----------+-----------+
|  1 | MySQL      | MySQL is my first database | 2021-08-09 21:07:51 | daco      | developer |
|  2 | ORACLE 2   | ORACLE is database         | 2021-08-09 21:13:02 | dacoZZING | developer |
|  4 | MongoDB    | MongoDB is database        | 2021-08-09 21:14:05 | daco      | developer |
|  5 | SQL Server | SQL Server is database     | 2021-08-09 21:14:57 | daco      | developer |
+----+------------+----------------------------+---------------------+-----------+-----------+
4 rows in set (0.000 sec)

 

---

 

 오늘 느낀 것 

 

WHERE는 강박적으로 확인해야 할 듯!

DELETE 에서 신기한 점은 id = 3 을 삭제한다고 해서 4와 5의 id가 3과 4로 자동변환되지 않는다는 점.

id는 html과 마찬가지로 고유값을 가지게 되는 걸까? 

그러면 앞으로 추가되는 데이터는 비어있는 id에 자동 입력되는 걸까? 

궁금해서 CREATE 해보았다!

 

... 결과는 놀랍게도 비어있는 3 이 아닌 6의 id로 들어갔다! 특별히 id를 지정하지 않는다면 자동적으로 빈자리가 아닌, 다음 id를 부여받는 것으로 보인다!

MariaDB [daco]> INSERT INTO topic (title,description,created,author,profile) VALUES('MongoDB','MongoDB is database',NOW(),'daco','developer');

MariaDB [daco]> SELECT * FROM topic;
+----+------------+----------------------------+---------------------+-----------+-----------+
| id | title      | description                | created             | author    | profile   |
+----+------------+----------------------------+---------------------+-----------+-----------+
|  1 | MySQL      | MySQL is my first database | 2021-08-09 21:07:51 | daco      | developer |
|  2 | ORACLE 2   | ORACLE is database         | 2021-08-09 21:13:02 | dacoZZING | developer |
|  4 | MongoDB    | MongoDB is database        | 2021-08-09 21:14:05 | daco      | developer |
|  5 | SQL Server | SQL Server is database     | 2021-08-09 21:14:57 | daco      | developer |
|  6 | MongoDB    | MongoDB is database        | 2021-08-11 21:36:16 | daco      | developer |
+----+------------+----------------------------+---------------------+-----------+-----------+
5 rows in set (0.000 sec)