백엔드 프로젝트 4주차 (SQL 첫걸음) - 4장

데이터의 추가, 삭제, 갱신

 

🎯행 추가하기 - INSERT

INSERT 명령: INSERT INTO 테이블명 VALUES(값1, 값2, ...)

SELECT 명령: 데이터 검색을 위한 것으로, 질의를 하면 데이터베이스 서버가 클라이언트로 결과 반환

INSERT 명령: 데이터 추가, 클라이언트에서 서버로 데이터를 전송하는 형식으로 서버 측은 전송받은 데이터를 데이터베이스에 저장. 

 

▶️INSERT로 행 추가하기

데이터베이스를 생성하면 테이블에는 아무런 데이터도 저장되어 있지 않은 상태, 이후 INSERT 명령으로 데이터를 추가해 데이터베이스 구축

 

DESC sample41;

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| no    | int         | NO   |     | NULL    |       |
| a     | varchar(30) | YES  |     | NULL    |       |
| b     | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

 

빈 데이터셋인 sample41 테이블의 열 구성 화긴(각 열에 어떤 유형의 데이터를 저장할 수 있는지 DESC 명령으로 확인)

no 열은 int이므로 수치형 데이터 저장 가능

a 열은 varchar(30)이므로 최대 기리 30인 문자열 저장 가능

b 열은 날짜시간형 데이터 저장 가능

 

INSERT INTO sample41 VALUES(1, 'ABC', '2014-01-25');

Query OK, 1 row affected (0.01 sec)

 

INSERT 명령을 이용하여 추가할 테이블 지정, VALUES 구를 사용해 행의 데이터 지정

INSERT 명령은 데이터가 클라이언트에서 서버로 전송되므로 반환되는 결과가 없음

 

SELECT * FROM sample41;

+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  1 | ABC  | 2014-01-25 |
+----+------+------------+
1 row in set (0.00 sec)

 

기존에 없던 행이 추가됨

 

▶️값을 저장할 열 지정하기

INSERT 명령으로 행을 추가할 경우 값을 저장할 열 지정 가능, VALUES 구로 값 지정

INSERT INTO 테이블명 (열1, 열2, ...) VALUES (값1, 값2, ...)

 

INSERT INTO sample41(a, no) VALUES('XYZ', 2);
SELECT * FROM sample41;

+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  1 | ABC  | 2014-01-25 |
|  2 | XYZ  | NULL       |
+----+------+------------+
2 rows in set (0.00 sec)

 

지정한 열에 값을 넣어 행 추가

별도의 값을 지정하지 않았던 b열에는 기본값인 NULL(b 열의 default 값)이 저장됨

 

▶️NOT NULL 제약

행을 추가할 때 유효한 값이 없는 상태(NULL)로 두고 싶을 경우 VALUES 구에서 NULL로 값 지정 가능

하지만 어떤 열에 NOT NULL 제약이 걸려있다면 NULL값 허용 X

이와 같이 테이블에 저장하는 데이터를 설정으로 제한하는 것을 통틀어 제약이라 부르며 그 중 하나가 NOT NULL

 

INSERT INTO sample41(no, a, b) VALUES(3, NULL, NULL);
SELECT * FROM sample41;

+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  1 | ABC  | 2014-01-25 |
|  2 | XYZ  | NULL       |
|  3 | NULL | NULL       |
+----+------+------------+
3 rows in set (0.00 sec)

 

no 열은 NOT NULL 제약이 있어 NULL 이외의 값을 넣으면 문제없이 실행됨

NULL을 허용하고 싶지 않다면 NOT NULL 제약을 걸어두는 것이 좋음

 

▶️DEFAULT

Default: 명시적으로 값을 지정하지 않았을 경우 사용하는 초깃값, Default 값은 테이블을 정의할 때 지정할 수 있음.

열을 지정해 행을 추가할 때 지정하지 않은 열은 Default 값을 사용하여 저장됨

 

DESC sample411;

+-------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------+------+-----+---------+-------+
| no    | int  | NO   |     | NULL    |       |
| d     | int  | YES  |     | 0       |       |
+-------+------+------+-----+---------+-------+
2 rows in set (0.00 sec)

 

sample411의 d열에는 디폴트값으로 0이 설정되어 있음

 

INSERT INTO sample411(no, d) VALUES (1,1);
SELECT * FROM sample411;

+----+------+
| no | d    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

 

d열에 숫자를 지정해 행 추가

 

INSERT INTO sample411(no, d) VALUES (2, DEFAULT);
SELECT * FROM sample411;

+----+------+
| no | d    |
+----+------+
|  1 |    1 |
|  2 |    0 |
+----+------+
2 rows in set (0.00 sec)

 

DEFAULT 키워드를 사용하여 명시적으로 디폴트값 지정

 

 INSERT INTO sample411(no) VALUES (3);
 SELECT * FROM sample411;

+----+------+
| no | d    |
+----+------+
|  1 |    1 |
|  2 |    0 |
|  3 |    0 |
+----+------+
3 rows in set (0.00 sec)

 

d열을 제외하고 no 열만 INSERT 명령 실행, 그 결과 d열의 값이 디폴트로 저장됨

>> 암묵적으로 디폴트 지정(열을 지정하지 않으면 디폴트값으로 행이 추가됨)

 

 

 

🎯삭제하기 - DELETE

DELETE 명령: DELETE FROM 테이블명 WHERE 조건식

저장공간이 모자라 데이터를 삭제해야 하는 경우, DELETE 명령으로 데이터를 삭제

 

▶️DELETE로 행 삭제하기

RDBMS에서 데이터를 삭제할 경우 행 단위로 DELETE 명령 수행 가능

SELECT * FROM sample41;

+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  1 | ABC  | 2014-01-25 |
|  2 | XYZ  | NULL       |
|  3 | NULL | NULL       |
+----+------+------------+
3 rows in set (0.00 sec)

 

sample41 테이블 불러오기

 

 DELETE FROM sample41 WHERE no=3;
 SELECT * FROM sample41;

+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  1 | ABC  | 2014-01-25 |
|  2 | XYZ  | NULL       |
+----+------+------------+
2 rows in set (0.00 sec)

 

no 열의 값이 3인 행 삭제 >> 삭제는 행 단위로 수행

SELECT 명령과 같이 열 지정 X(특정 열만 삭제는 불가), WHERE 조건에 일치하는 모든 행을 삭제

 

▶️DELETE 명령구

• WHERE구에서 대상이 되는 행을 검색하는 것은 SELECT, DELETE 명령에서 동일
• SELECT 명령에서는 조건에 맞는 행의 결괏값이 클라이언트로 반환되지만, DELETE 명령에서는 조건에 맞는 행이 삭제된다는 점이 차이
• 조건식을 변경하여 삭제할 행 변경 가능
• ORDER BY 구 사용X(어떤 행부터 삭제할 것인지는 중요하지 않으며 의미가 없음), 다만 MySQL에서는 DELETE 명령에서 ORDER BY 구와 LIMIT 구를 사용해 삭제할 행을 지정할 수 있음

 

 

🎯데이터 갱신하기 - UPDATE

UPDATE 명령: UPDATE 테이블명 SET 열1 = 값1, 열2 = 값2, ...WHERE 조건식

데이터를 잘못 입력하여 수정해야 하는 경우, UPDATE 명령을 사용하여 데이터 갱신

 

▶️UPDATE로 데이터 갱신하기

RDBMS에서는 UPDATE 명령으로 데이터 갱신 가능

UPDATE 명령은 테이블의 셀 값을 갱신하는 명령

 

SELECT * FROM sample41;

+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  1 | ABC  | 2014-01-25 |
|  2 | XYZ  | NULL       |
+----+------+------------+
2 rows in set (0.00 sec)

 

UPDATE 명령 실행 전 기존 데이터 테이블 확인

 

• DELETE와 달리 UPDATE는 셀 단위로 데이터 갱신 가능. WHERE 구에서 조건을 지정하면 그에 일치하는 행을 갱신.
• WHERE 구를 생략한 경우에는 DELETE의 경우와 마찬가지로 테이블의 모든 행이 갱신됨
• SET구를 사용하여 갱신할 열과 값을 지정(이때 '='는 대입 연산자). 값은 상수로 표기하며, 자료형에 맞는 값을 지정해야 함(수치형-수치형 리터럴, 문자열형-문자열형 리터럴 등). 갱신해야 할 열과 값이 복수인 경우 '열=값'을 콤마(,)로 구분하여 리스트 형식으로 지정 가능. SET 구에 지정한 갱신 내용은 처리 대상이 되는 모든 행에 적용

UPDATE sample41 SET b = '2023-01-01' WHERE no = 2;
SELECT * FROM sample41;

+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  1 | ABC  | 2014-01-25 |
|  2 | XYZ  | 2023-01-01 |
+----+------+------------+
2 rows in set (0.00 sec)

 

b열의 NULL인 행의 값을 UPDATE 명령으로 갱신

행 지정: WHERE no = 2, 갱신할 값: SET b = '2023-01-01' (b열은 날짜형이므로 날짜의 리터럴로 값 표기)

*WHERE 구를 생략할 경우 테이블의 모든 행이 갱신 대상이 되기 때문에 주의해야 함

 

▶️UPDATE로 갱신할 경우 주의사항

UPDATE sample41 SET no=no+1;
SELECT * FROM sample41;

+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  2 | ABC  | 2014-01-25 |
|  3 | XYZ  | 2023-01-01 |
+----+------+------------+
2 rows in set (0.00 sec)

 

WHERE 구가 지정되어 있지 않으므로 갱신 대상은 테이블의 모든 행이 됨

SET 구에서 no 열의 값을 (갱신 전의 본래 값 + 1)로 갱신 >> 실행 결과 모든 행의 no 값에 1씩 더해짐

이와 같이 갱신할 값을 열이 포함된 식으로도 표기할 수 있음(해당 열이 갱신 대상이 되는 열이라 해도 상관 X)

*갱신은 행 단위로 처리되므로, no=no+1의 두 번째 no는 현재의 no 값, 즉 그 행이 갱신되기 전 값에 해당함

 

▶️복수열 갱신

UPDATE 명령의 SET 구에서는 필요에 따라 콤마(,)로 구분하여 갱신할 열을 여러 개 지정 가능

여러 개의 열을 한 번에 갱신할 수 있어 편리하지만, SET 구가 갱신 처리를 하는 순서에 주의해야 함

UPDATE sample41 SET no=no+1, a=no;
 SELECT * FROM sample41;

+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  3 | 3    | 2014-01-25 |
|  4 | 4    | 2023-01-01 |
+----+------+------------+
2 rows in set (0.00 sec)

 

위의 경우 no 열과 a 열의 값이 서로 같아짐. no 열의 값에 1을 더하여 no 열에 다시 저장한 뒤, 그 값을 a 열에 대입한 것.

 

UPDATE sample41 SET a = no, no=no+1;
SELECT * FROM sample41;

+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  4 | 3    | 2014-01-25 |
|  5 | 4    | 2023-01-01 |
+----+------+------------+
2 rows in set (0.00 sec)

 

반면 이 경우 no 열의 값을 a 열에 대입한 후 no 열의 값을 1씩 더한 것, 따라서 no열의 값은 a-1이 됨

 

이는 데이터베이스 제품에 따라 그 처리 방식이 달라지는데, MySQL의 경우 위와 같이 서로 다른 결괏값이 나오지만 Oracle의 경우 어느 명령을 실행해도 결과는 동일함. Oracle에서는 SET구에 기술한 식의 순서가 처리에 영향을 주지 않지만, MySQL의 경우 SET구에 기술된 순서로 갱신 처리가 일어난다는 것. 따라서 MySQL의 경우 갱신식 안에서 열을 참조할 때 처리 순서를 고려해야 함.

 

▶️NULL로 갱신하기

SET b=NULL과 같이 갱신할 값으로 NULL을 지정

>> NULL 초기화(NULL로 값을 갱신)

UPDATE sample41 SET a=NULL;
SELECT * FROM sample41;

+----+------+------------+
| no | a    | b          |
+----+------+------------+
|  5 | NULL | 2014-01-25 |
|  6 | NULL | 2023-01-01 |
+----+------+------------+
2 rows in set (0.00 sec)

 

다만 NOT NULL 제약이 설정되어 있는 열은 NULL이 허용되지 않으며 UPDATE 명령에 있어서도 NOT NULL 제약은 유효함. NOUT NULL 제약이 걸린 열을 NULL 초기화할 경우, 제약 위반으로 에러 발생

 

 

 

🎯물리삭제와 논리삭제

▶️두 종류의 삭제방법

데이터베이스에서 데이터를 삭제할 때 물리삭제와 논리삭제의 두 가지 방법이 존재

• 물리삭제: SQL의 DELETE 명령을 사용해 직접 데이터를 삭제하자는 사고 방식(필요없는 데이터 취급)
• 논리삭제: 테이블에 '삭제플래그'와 같은 열을 미리 준비함. 테이블에서 실제로 행을 삭제(물리삭제)하는 대신, UPDATE 명령을 이용해 '삭제플래그'의 값을 유효하게 갱신해두자는 사고 방식. 실제 테이블 안에는 데이터가 남아 있지만, 참조할 때 삭제플래그가 삭제로 설정된 행을 제외하는 SELECT 명령을 실행. 
  • 장점: 데이터를 삭제하지 않기 때문에 삭제 전 상태로 간단히 복구 가능
  • 단점: 저장공간이 늘어나지 않고, 데이터베이스의 크기가 증가함에 따라 검색속도가 떨어짐. 또한 삭제임에도 불구하고 UPDATE 명령을 사용하므로 혼란을 야기할 수 있음

▶️삭제방법 선택하기

상황에 맞게 선택

• 사용자의 개인정보를 다루는 시스템에서 사용자가 탈퇴한 경우 >> 개인정보 유출 방지를 위해 물리삭제 선택
• 쇼핑 사이트에서 사용자가 주문을 취소 >> 주문이 취소되었다고 해도 발주는 된 것으로, 해당 정보가 완전히 불필요한 것이 아니기에 논리삭제 선택
• 하드웨어의 저장공간이 부족 >> 저장공간 확보 목적으로 물리삭제 선택