[SQL] SQL 기본 - DML, TCL

SQLD 자격증 스터디를 하며 기억하고 싶은 내용들을 블로그 글로 정리중이다.

이어서 DML, TCL을 정리해보려고 한다. 

내용은 책 'SQL 자격검정 실전문제' 그리고 사이트 'DATA ON-AIR'의  SQL 부문(링크)을 참고하여 정리하였다. 

 

---

DML 문법 정리

DML (Data Manipulation Language)로 데이터 삽입, 삭제, 수정, 조회 가능한 명령어를 지칭한다. 

 

[1] INSERT

테이블에 데이터 입력하는 두 가지 방법

1. 첫번째 방법 

데이터 넣을 칼럼을 직접 지정하여 데이터 넣기

INSERT INTO 테이블명 (COLUMN_LIST) VALUES (COLUMN_LIST에 넣을 VALUE_LIST);
(ex) 
INSERT INTO PLAYER (PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, HEIGHT, WEIGHT, BACK_NO) VALUES ('2002007', '박지성', 'K07', 'MF', 178, 73, 7);

2. 두번째 방법

데이터 값만 바로 넣는 방법

 INSERT INTO 테이블명 VALUES (전체 COLUMN에 넣을 VALUE_LIST);
 (ex) 
 INSERT INTO PLAYER VALUES ('2002010','이청용','K07','','BlueDragon','2002','MF','17',NULL, NULL,'1',180,69);

 

---

[2] UPDATE

테이블에 넣은 데이터를 수정

UPDATE 테이블명 SET 수정되어야 할 칼럼명 = 수정되기를 원하는 새로운 값;
(ex)
UPDATE PLAYER SET BACK_NO = 99;

 

---

[3] DELETE

테이블에 넣은 데이터를 삭제

DELETE [FROM] 삭제를 원하는 정보가 들어있는 테이블명;
(ex)
DELETE FROM PLAYER; // 모든 데이터 삭제

 

---

[4] SELECT

테이블에 넣은 데이터를 조회

SELECT [ALL/DISTINCT] 조회할 칼럼명, 칼럼명2,... FROM 테이블명;
(ex)
SELECT PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, HEIGHT, WEIGHT, BACK_NO FROM PLAYER;
SELECT * FROM PLAYER;
SELECT DISTINCT POSITION FROM PLAYER;

(ALIAS ex)
SELECT PLAYER_NAME AS 선수명, POSITION AS 위치, HEIGHT AS 키, WEIGHT AS 몸무게 FROM PLAYER;

(산술연산 ex)
SELECT PLAYER_NAME 이름, HEIGHT - WEIGHT "키-몸무게" FROM PLAYER;

• ALL은 default옵션으로 종복된 데이터 그대로 모두 출력함. DISTINCT는 중복된 데이터는 1건으로 해서 출력함
• ALIAS를 부여하여, 본 칼럼명 대신 약어로 사용가능함
• 산술 연산자를 이용하여 NUMBER나 DATE 자료형에 대해 수학의 4칙 연산 가능하다.

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?pageid=4&mod=document&uid=339

 

---

---

TCL 문법 정리

[1] 트랜잭션

트랜잭션은 DB의 논리적 연산 단위로, 밀접히 관련되어 분리될 수 없는 한 개 이상의 DB 조작을 가리킨다. 

하나의 트랜잭션에는 하나의 SQL문장이 포함되고, 분할할 수 없는 최소의 단위이다.

 

트랜잭션 특성 

특성	설명
원자성	트랜잭션에서 정의된 연산들은 모두 성공저긍로 실행되거나 전혀 실행되지 않은 상태로 남아있어야 한다.
일관성	트랜잭션이 실행되기 전의 DB내용이 잘못 되어 있지 않다면 트랜잭션이 실행된 이후에도 Db의 내용에 잘못이 있으면 안 된다.
고립성	트랜잭션이 실행되는 도중에 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안된다.
지속성	트랜잭션이 성공적으로 수행되면 그 트랜잭션이 갱신한 DB의 내용은 영구적으로 저장된다.

 

---

[2] COMMIT

입력한 자료나 수정한 자료에 대해서 또는 삭제한 자료에 대해서 전혀 문제가 없다고 판단되었을 경우 COMMIT 명령어를 통해서 트랜잭션을 완료할 수 있다.

 

[ 사용법 및 특징 ] 

COMMIT;

• 단지 메모리 BUFFER에만 영향을 받았기 때문에 데이터의 변경 이전 상태로 복구 가능하다.
• 변경된 행은 잠금(LOCKING)이 설정되어서 다른 사용자가 변경할 수 없다.
• COMMIT 명령어는 이처럼 INSERT 문장, UPDATE 문장, DELETE 문장을 사용한 후에 이런 변경 작업이 완료되었음을 데이터베이스에 알려 주기 위해 사용한다. 
• COMMIT 이후에는 관련된 행에 대한 잠금(LOCKING)이 풀리고, 다른 사용자들이 행을 조작할 수 있게 된다.

 

SQL Server는 기본적으로 AUTO COMMIT 모드라, DML 수행 후 사용자가 COMMIT/ROLLBACK을 처리할 필요가 없다.

DML 구문이 성공이면 자동으로 COMMIT이 되고 오류가 발생할 경우 자동으로 ROLLBACK 처리된다.

 

 

---

[3] ROLLBACK

Table내 입력 및 수정, 삭제한 데이터에 대하여 COMMIT이전에는 변경 사항을 취소할 수 있는데, ROLLBACK 기능을 사용한다.  ROLLBACK은 데이터 변경 사항이 취소되어, 데이터 이전 상태로 복구되며, 관련된 행에 대한 잠금이 풀리고 다른 데이터 변경을 할 수 있게 된다.

 

ROLLBACK

 

[ ROLLBACK 이후 데이터 상태 ]

• 데이터 변경 사항 취소 & 이전 데이터 재저장됨
• 관련된 행의 잠금(LOCKING) 풀리고, 다른 사용자들이 행 조작 가능

SQL Server의 경우 명시적 트랜잭션으로  트랜잭션의 시작과 끝을 모두 사용자가 명시적으로 지정하는 방식이다.

BEGIN TRANSACTION (=BEGIN TRAN )으로 트랜잭션을 시작하고

COMMIT TRANSACTION(TRANSACTION은 생략 가능) 또는ROLLBACK TRANSACTION(TRANSACTION은 생략 가능)으로 트랜잭션을 종료한다.

 

ROLLBACK 구문을 만나면 최초의 BEGIN TRANSACTION 시점까지 모두 ROLLBACK이 수행된다.

 

 

---

[4] SAVEPOINT

SAVEPOINT를 이용하면 ROLLBACK할 때 전체 작업을 롤백하는게 아닌, 현 시점에서 SAVEPOINT까지 트랜잭션의 일부만 롤백할 수있다.

 

[ORACLE]

SAVEPOINT SVPT1;
...
ROLLBACK TO SVPT1;

[SQL Server]

SAVE TRANSACTION SVPT1;
...
ROLLBACK TRANSACTION SVPT1;