SQLD - DML, TCL

20240819 TIL

1. DML

• 테이블에 데이터를 삽입(insert), 삭제(DELETE), 수정(UPDATE), 조회(select) 하는 일을 수행하는 SQL
• 추가적으로 데이터베이스에 완전하게 반영하는 커밋(COMMIT)과 데이터베이스 작업의 취소를 위한 롤백(ROLLBACK)

▶ INSERT

데이터를 저장할 때 사용

 

1. 칼럼 리스트를 명시하는 방법

• 어떤 칼럼에 넣을지 명시하며 저장할 데이터도 칼럼의 순서에 맞게 작성

2. 칼럼 리스트를 명시하지 않는 방법

• 모든 칼럼에 값을 넣을 것이라는 의미
• 테이블에서 정의된 칼럼의 개수에 맞추어 저장되는 값을 순서대로 입력

<예시>

-- 칼럼 리스트 명시

INSERT INTO player (name, height, weight) VALUES ('KIM', 184, 75);

-- 칼럼 리스트 명시 x

INSERT INTO player VALUES ('LEE', 187, 74);

 

▶ UPDATE

한 번 입력된 데이터를 수정해야 할 때 수정 가능

where 문을 사용하여 조건에 맞는 데이터를 찾아 해당 데이터 수정 가능

#구조
UPDATE 테이블_이름 SET 수정_할_컬럼_이름 = 수정_할_새로운_값, ...;

#예시
# layer 테이블의 back_no를 99로 변경하기
# player 테이블의 position을 MF로 변경하기
UPDATE player SET back_no = 99;
UPDATE player SET position = 'MF';

 

▶ DELETE

데이터를 삭제하기위한 명령어

where 문을 사용하여 조건을 만족하는 데이터만 선택하여 삭제 가능

# 구조
DELETE [FROM] 테이블_이름;
DELETE [FROM] 테이블_이름 WHERE 삭제할_조건;

#예시
# player 테이블의 모든 데이터를 삭제하기
# player 테이블에서 back_no가 50인 데이터만 삭제하기
DELETE FROM player;
DELETE FROM player WHERE back_no = 50;

 

📌 DELETE vs TRUNCATE
DELETE 명령어는 데이터가 삭제될 때 복원을 위해 log를 남기기 때문에 시스템에서 데이터 log를 기록하고 저장하기 위한 작업으로 인한 약간의 부하가 있다.
DELETE 명령어는 실행하면 바로 데이터가 삭제되지 않는다
DELETE를 이용하여 잘못 삭제했다면  ROLLBACK 명령어를 통해 다시 되돌릴 수 있다

<DDL의 TRUNCATE와 데이터 삭제라는 유사한 동작을 진행하지만 가장 큰 차이점>
DDL 명령어는 바로 테이블에 적용이 되기 때문에 ROLLBACK이 불가능
즉, TRUNCATE로 삭제된 데이터는 되돌릴 수 없다!
다만, 복원을 위한 log를 남기지 않기 때문에 TRUNCATE로 모든 데이터를 지울 때 시스템의 부하가 적다는 것이 특징

 

-- Oracle

DELETE FROM player;          -- player 테이블에서 데이터 삭제
ROLLBACK;                    -- 되돌리기
SELECT COUNT(*) FROM player; -- player 테이블의 전체 데이터 개수 구하기
-- 480

TRUNCATE TABLE player;       -- player 테이블에서 데이터 삭제
ROLLBACK;                    -- 되돌리기
SELECT COUNT(*) player;      -- player 테이블의 전체 데이터 개수 구하기
-- 0​

DROP	TRUNCATE	DELETE
DDL	DDL (일부 DML 느낌)	DML
ROLLBACK 불가능	ROLLBACK 불가능	COMMIT 이전 ROLLBACK 가능
AUTO COMMIT	AUTO COMMIT	사용자 COMMIT
테이블의 정의 자체를 완전히 삭제	테이블을 최초 생성된 초기 상태로 만듦	테이블은 그대로 두고 데이터만 삭제

 

 

2. TCL

• TCL(Transaction Control Language)은 데이터를 삽입, 삭제, 수정하는 DML의 수행 이후 COMMIT이나 ROLLBACK을 하기 위해 사용하는 SQL

▶ 트랜잭션

논리적인 연산의 단위 --> '논리적인 연산'이라고 하는 것은 분할할 수 없는 최소한의 단위로, 어떠한 연산을 전부 적용하거나 혹은 적용하지 않는 양자택일의 관계로 구성

<기본 특성>

1. 원자성(Atomicity)

• 모든 연산은 최소한의 조작 묶음이기 때문에 하나의 트랜잭션을 완료했다는 말은 모든 연산이 정확하게 수행되었다는 것을 의미
• 조작 과정에서 하나의 논리적인 연산이라도 실패하게 된다면 아무것도 수행하지 않은 상태로 남아 있어야 함
• 즉, 물건을 사기 위해 돈을 줬으면 물건이 있는 경우라면 물건을 받아야 하고 물건이 없다면 돈을 돌려받아야 함 (All or Nothing)

2. 일관성(Consistency)

• 트랜잭션이 수행하기 전의 상태 그리고 수행하고 나서의 상태도 잘못된 내용이 없어야 함
• 돈이 없다면 송금이 되어서는 안되고, 송금된 돈을 받았는데 통장에는 송금된 돈이 저장이 되어야 하지 전달받은 돈의 이미지 형태가 저장이 되지 않아야 함

3. 고립성/격리성(Isolation)

• 다른 트랜잭션에 영향을 받아 잘못된 결과를 만들어선 안됨
• 각각의 트랜잭션은 개별적으로 동작이 보장되어야 함

4. 지속성(Durability)

• 트랜잭션이 성공적으로 수행된다면 그 결과를 갱신한 데이터베이스는 내용을 영구적으로 유지하고 있어야 함
• 특정 시간 동안만 유지되고 다시 돌아가는 불안정하 부분 있어서는 안됨

▶ COMMIT 

• 입력한 자료나 수정한 자료, 혹은 삭제한 자료에 대해서 문제가 없다고 판단이 되었을 때, 최종 트랜잭션을 확정하는 TCL 명령어

-COMMIT을 하지 않아도 변경된 데이터의 내용을 SELECT 문을 통해 조회가 가능
-다만, 다른 사용자는 현재 변경된 내용에 접근하여 데이터의 변화를 확인할 수 없으며 변경 중인 데이터 베이스도 Locking이 되어   다른 사용자가 변경할 수도 없다
-데이터베이스를 변경하고 COMMIT을 하지 않으면 최종 데이터베이스에 변경 내용 저장이 확정되지 않았기 때문에 데이터 변경 이전 상태로 복구가 가능
-COMMIT을 하고 나면 변경된 내용이 데이터베이스에 반영이 되고 변경되기 전의 데이터는 사라지게 된다
-Locking이 풀리면서 다른 사용자들이 값을 조회하고 수정할 수 있게 된다

# 기본 형태
-- Oracle
SQL 쿼리문;
COMMIT;

-- SQL Server
BEGIN {TRANSACTION|TRAN}
SQL 쿼리문
COMMIT [TRANSACTION];

#예시
-- Oracle

INSERT INTO PLAYER VALUES ('1997035', 'K02', '이운재', 'GK', 182, 82, 1);
COMMIT;

UPDATE PLAYER SET HEIGHT = 100;
COMMIT;

DELETE FROM PLAYER;
COMMIT;

 

▶ ROLLBACK

• 트랙잭션 진행중 에러가 발생하거나 잘못된 연산이 이뤄진 경우 변경 전 가장 최신 상태로 되돌릴 수 있다
• ROLLBACK이 실행되면 COMMIT되지 않은 모든 데이터 변경 사항들이 취소되어 데이터의 이전 상태로 복구되고 관련된 Locking이 풀리면서 다른 사용자들이 데이터를 변경할 수 있게 된다

📌COMMIT과 ROLLBACK을 사용할 때 장점
1.데이터의 무결성 보장
2. 영구적인 변경을 하기 전에 데이터의 변경 사항 확인 가능
3. 논리적으로 연관된 작업을 묶어(Grouping) 처리 가능

📌 COMMIT과 ROLLBACK을 사용할 때의 차이
1. DML의 데이터 삽입, 수정, 삭제 이후 COMMIT or ROLLBACK 전 데이터 상태

• 데이터베이스에 최종적으로 적용된 상태가 아니라 메모리 영역에만 적용된 상태이기 때문에 변경 전 상태로 복구 가능합니다.
• SELECT 구문을 통해 삽입, 수정, 삭제의 결과를 조회 가능
  • 작업자는 가능
  • 작업자외 다른 사람은 불가능
• 변경된 행은 Locking 설정으로 인해 다른 사용자가 변경할 수 없음

2. DML의 데이터 삽입, 수정, 삭제 이후 COMMIT한 상황

• 데이터의 삽입, 수정, 삭제 내역이 데이터베이스에 완전하게 반영
• COMMIT 실행 시점 이후에는 해당 구문의 실행으로 인한 변화로 이전 데이터는 완전히 삭제
• 작업자 뿐만 아니라 모든 사람은 SELECT 구문을 통해 동일한 결과를 얻을 수 있다
• 변경된 행의 Locking이 모두 풀리고 모든 사용자는 특정한 행에 대한 조작이 가능

📌 COMMIT과 ROLLBACK을 사용하지 않아도 트랜잭션이 종료되는 경우
1. DDL 문장 실행 시 자동으로 COMMIT
2. DML 문장 이후에 COMMIT 없이 DDL 문장이 실행될 때
3. 정상적으로 DB 접속을 종료하면 COMMIT
4. 장애로 인한 비정상 종료로 DB 접속이 단절되었을 경우에는 AUTO ROLLBACK

# 기본형태
-- Oracle
SQL 쿼리문
ROLLBACK;

-- SQL Server
BEGIN {TRANSACTION|TRAN}
SQL 쿼리문
ROLLBACK [TRANSACTION];

#예시
-- Oracle

INSERT INTO PLAYER VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1); 
ROLLBACK;

UPDATE PLAYER SET HEIGHT = 100;
ROLLBACK;

-- SQL Server

BEGIN TRAN 
INSERT INTO PLAYER VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1);

ROLLBACK;

 

▶ SAVEPOINT

• 트랜잭션 중간에 마치 게임을 저장하듯 'SAVEPOINT'라는 것을 설정
• 트랜잭션 중간에 SAVEPOINT를 설정하여 처음으로 되돌리는 것이 아닌 SAVEPOINT 설정한 지점까지만 되돌려서 시스템의 부하를 줄일 수 있다

 

# 기본 형태
-- Oracle
SAVEPOINT 저장점_이름;

ROLLBACK TO 저장점_이름;

-- SQL Server
SAVE TRANSACTION 저장점_이름;

ROLLBACK TRANSACTION 저장점_이름;

# 예시
-- Oracle

SAVEPOINT SVPT1; 
INSERT INTO PLAYER VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1); 
ROLLBACK TO SVPT1;

-- SQL Server

SAVE TRAN SVTR1; 
INSERT INTO PLAYER  VALUES ('1999035', 'K02', '이운재', 'GK', 182, 82, 1);
ROLLBACK TRAN SVTR1;