[SQL 첫걸음] 17강 - 19강

17강: 삭제하기 - DELETE

1. DELETE로 행 삭제하기

mysql> select * from sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  1 | abc  | 2014-02-02 |
|  2 | xyz  | NULL       |
|  3 | NULL | NULL       |
+----+------+------------+
3 rows in set (0.00 sec)

mysql> delete from sample41 where no = 3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  1 | abc  | 2014-02-02 |
|  2 | xyz  | NULL       |
+----+------+------------+
2 rows in set (0.00 sec)

 

18강 : 데이터 갱신하기 - UPDATE

1. UPDATE로 데이터 갱신하기

mysql> select * from sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  1 | abc  | 2014-02-02 |
|  2 | xyz  | NULL       |
+----+------+------------+
2 rows in set (0.00 sec)

mysql> update sample41 set b = '2014-09-07' where no = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  1 | abc  | 2014-02-02 |
|  2 | xyz  | 2014-09-07 |
+----+------+------------+
2 rows in set (0.00 sec)

 

2. UPDATE로 갱신할 경우 주의사항

mysql> select * from sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  1 | abc  | 2014-02-02 |
|  2 | xyz  | 2014-09-07 |
+----+------+------------+
2 rows in set (0.00 sec)

mysql> update sample41 set no = no + 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  2 | abc  | 2014-02-02 |
|  3 | xyz  | 2014-09-07 |
+----+------+------------+
2 rows in set (0.00 sec)

 

3. 복수열 갱신

- 콤마(,)로 구

mysql> select * from sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  2 | abc  | 2014-02-02 |
|  3 | xyz  | 2014-09-07 |
+----+------+------------+
2 rows in set (0.00 sec)

mysql> update sample41 set no = no + 1, a = no;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  3 | 3    | 2014-02-02 |
|  4 | 4    | 2014-09-07 |
+----+------+------------+
2 rows in set (0.00 sec)

# 순서 변경
mysql> select * from sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  3 | 3    | 2014-02-02 |
|  4 | 4    | 2014-09-07 |
+----+------+------------+
2 rows in set (0.00 sec)

mysql> update sample41 set a = no, no = no + 1;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  4 | 3    | 2014-02-02 |
|  5 | 4    | 2014-09-07 |
+----+------+------------+
2 rows in set (0.00 sec)

# null로 update
mysql> select * from sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  4 | 3    | 2014-02-02 |
|  5 | 4    | 2014-09-07 |
+----+------+------------+
2 rows in set (0.00 sec)

mysql> update sample41 set a = null;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from sample41;
+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  4 | NULL | 2014-02-02 |
|  5 | NULL | 2014-09-07 |
+----+------+------------+
2 rows in set (0.00 sec)

 

19강 : 물리삭제와 논리삭제

1. 두 종류의 삭제방법

- 물리 삭제

    - DB에 있는 데이터를 delete로 삭제

- 논리 삭제

    - '삭제 플래그'같은 열의 값을 이용해 삭제로 설정

    - select 시 삭제로 설정된 행을 제외하고 조회

 

-> 용도에 맞는 삭제 방법을 선택하자