SQLD SQL 기본 및 활용(17~18) - 16일차

목차
17.	DML
18.	TCL

 

17. DML 

● DML(Data Manipulation Language) 

- 데이터의 삽입(INSERT), 수정(UPDATE), 삭제(DELETE), 병합(MERGE) 
- 저장(commit) 혹은 취소(rollback) 반드시 필요

● INSERT

- 테이블에 행을 삽입할 때 사용 
- 한 번에 한 행만 입력가능(SQL Server. 여러 행 동시 삽입 가능)
- 하나의 컬럼에는 한 값만 삽입 가능 
- 컬럼별 데이터타입과 사이즈에 맞게 삽입 
- INTO 절에 컬럼명을 명시하여 일부 컬럼만 입력 가능. 작성하지 않은 컬럼은 NULL 이 입력됨 

  -> NOT NULL 컬럼의 경우 오류 발생 
- 전체 컬럼에 대한 데이터 입력시 테이블명 뒤의 컬럼명 생략 가능 

 

** 문법 

 

예제) 테이블에 데이터 INSERT(한 행씩)

 < 테이블 구조 > 

 

< 여러 행 INSERT > 

-> 테이블의 각 컬럼별 데이터타입과 사이즈에 맞게 입력 
-> 문자 컬럼에 숫자값 입력 가능(권장 X) 
-> 숫자 컬럼에 '001'처럼 숫자처럼 생긴 문자값 입력 가능(권장 X) 

 

< 결과 > 

 

예제) 서브쿼리를 사용한 여러 행 INSERT 

 

예제) INSERT 시 컬럼 명시 생략으로 인한 오류 발생 

< 테이블 구조 > 

 

< 오류 발생 > 

-> 테이블명 뒤에 INSERT 할 컬럼명을 명시하지 않으면 모든 컬럼 INSERT 시도하는데 입력된 값은 두 개 밖에 없으므로 에러!

< 해결 > 

 

● UPDATE 

- 데이터 수정할 때 사용 
- 컬럼 단위 수행 
- 다중 컬럼 수정 가능

** 문법 
1) 단일컬럼 수정 

-> WHERE 절로 수정 대상을 선택 가능 

 

예제) AMERICANO 의 PRICE 을 1500 으로 변경 

 

2) 다중컬럼 수정 
 - 방법 1  

 

예제) 3 번의 NAME 을 HOT_MILK 로 PRICE 을 2500 으로 변경 

 

- 방법 2  

※ 서브쿼리의 결과가 수정할 각 행의 값마다 하나씩 전달돼야 함 

예제) 서브쿼리를 사용한 여러 컬럼 동시 수정 

< 수정 전 > 

 

-> 서브쿼리 결과가 각 컬럼마다 한 값으로 정의되지 않으면 수행 불가!

● DELETE 

- 데이터를 삭제할 때 사용 
- 행 단위 실행 

 

**문법 

☞ WHERE 절로 삭제할 행 선택 가능 

예제) NO 가 3 인 행 삭제 

-> 1 개의 행이 삭제되었다는 것을 알 수 있음 

4) MERGE 
- 데이터 병합 
- 참조 테이블과 동일하게 맞추는 작업(참조테이블의 데이터 입력, 참조테이블의 값으로 수정 등) 
  -> INSERT, UPDATE, DELETE 작업을 동시에 수행 

 

** 문법 

 

예제) OLD 테이블을 NEW 테이블과 동일하게 MERGE 문 작성 

STEP1) MERGE 할 TABLE 확인 

**  MERGE_NEW 데이터 확인

 

** MERGE_OLD 테이블 확인 

 

STEP2) MERGE 문 작성 

-> 수정할 테이블명을 MERGE INTO 절에 명시, 참조테이블을 USING 절 명시

-> 두 테이블의 데이터를 참조할 참조 조건을 ON 절 명시(괄호 필수) 
-> UPDATE 문에서는 테이블명 명시 X 
-> SET 절의 왼쪽이 수정테이블, 오른쪽이 참조 테이블 컬럼

-> INSERT 문에는 INTO 절 없이 VALUES 로 참조 컬럼명 전달 

STEP3) MERGE 결과 확인 

-> 처음 테이블에는 없던 MOCHA 행이 삽입, 기존 LATTE 가격이 2000 에서 2500 으로 수정됨 

18. TCL 

● TCL(Transaction Control Language)

- 트랜잭션 제어어로 COMMIT, ROLLBACK 이 포함됨 
- DML 에 의해 조작된 결과를 작업단위(트랜잭션) 별로 제어하는 명령어
- DML 수행 후 트랜잭션을 정상 종료하지 않는 경우 LOCK 발생할 수 있음 

※ 잠금(LOCK) 
- 트랜잭션이 수행하는 동안 특정 데이터에 대해서 다른 트랜잭션이 동시에 접근하지 못하도록 제한
- 잠금이 걸린 데이터는 잠금을 실행한 트랜잭션만이 접근 및 해제 가능(관리자 권한 계정 제외) 

● 트랜잭션

- 트랜잭션은 데이터베이스의 논리적 연산 단위(하나의 연속적인 업무 단위)
- 하나의 트랜잭션에는 하나 이상의 SQL 문장이 포함 
- 분할 할 수 없는 최소의 단위 
- ALL OR NOTHING 개념(모두 COMMIT 하거나 ROLLBACK 처리 해야 함)

※ 트랜잭션의 특성
-  원자성(atomicity)  :  트랜잭션  정의된  연산들  모두  성공적으로  실행되던지  아니면 전혀 실행되지 않은 상태로 남아 있어야 함 
- 일관성(consistency) : 트랜잭션 실행 전 데이터베이스 내용이 잘못되어 있지 않다면 트랜잭션 실행 이후에도 데이터베이스 내용의 잘못이 있으면 안됨 
- 고립성(isolation) : 트랜잭션 실행도중 다른 트랜잭션의 영향을 받아 잘못된 결과를 만들어서는 안됨 
- 지속성(durability) : 트랜잭션이 성공적으로 수행되면 갱신한 데이터베이스 내용이 영구적으로 저장 

● COMMIT

- 입력, 수정, 삭제한 데이터에 이상이 없을 경우 데이터를 저장하는 명령어 
- 한 번 COMMIT 을 수행하면 COMMIT 이전에 수행된 DML 은 모두 저장되며 되돌릴 수 없음
- ORACLE 은 DDL 시 AUTO COMMIT(23c 버전부터 비활성화 가능) 이지만 SQL Server 는  
AUTO COMMIT 비활성화 설정 가능 

● ROLLBACK

- 테이블 내 입력한 데이터나 수정한 데이터, 삭제한 데이터에 대해 변경을 취소하는 명령어 
- 데이터베이스에 저장되지 않고 최종 COMMIT 지점/변경 전/특정 SAVEPOINT 지점으로 원복됨
- 최종 COMMIT 시점 이전까지 ROLLBACK 가능
- SAVEPOINT 를 설정하여 최종 COMMIT 시점이 아닌, 그 이후의 원하는 시점으로의 원복 가능 

 

※ SAVEPOINT
- 트랜잭션 내에서 롤백을 부분적으로 수행하기 위해 사용되는 지점을 지정하는 데 사용
- 사용자가 원하는 위치에 원하는 이름으로 설정 가능
- ROLLBACK TO SAVEPOINT_NAME 으로 원하는 지점으로 원복 가능(단, COMMIT 이전으로는 원복 불가) 

 

** 문법 

 

예제) COMMIT 과 ROLLBACK 후 최종 데이터 상태 

< 원본 테이블 데이터 > 

 

< 여러 DML 수행 > 

 

< 조회 결과 > 

-> SAVEPOINT 이전 수행한 UPDATE 는 취소되지 않음! 

 

※ 출처 : 홍쌤의 데이터랩 - SQLD