[SQL] DML (Data Manipulation Language, 데이터 조작어)

DML (Data Manipulation Language, 데이터 조작어)은 데이터베이스의 테이블에 새로운 데이터를 저장 INSERT 하거나 삭제 DELETE 또는 수정 UPDATE 및 병합 MERGE 할 때 사용하는 조작어를 의미한다.

INSERT문

INSERT 문은 테이블에 데이터를 저장하기 위한 데이터 조작어이다.

-- 기본
INSERT INTO 테이블명 (컬럼명, 컬럼명2, ...)
VALUES (값, 값2, ...)

1. 하나의 테이블에 하나의 행을 저장하는 단일행 INSERT문
2. 하나의 테이블에 여러 행을 저장하는 다중행 INSERT문
3. 여러 테이블에 여러 행을 저장하는 다중 테이블 다중행 INSERT문

---

1. 단일행 INSERT문

INSERT INTO 테이블명 (컬럼명, 컬럼명2, ...)
VALUES (값, 값2, ...)

• INSERT문을 실행하면 테이블에 새로운 행(레코드)이 삽입
• 명시한 컬럼은 VALUES절에서 지정한 컬럼값에 일대일 대응이 되도록 순서대로 입력해야함
• 컬럼명과 VALUES절에서 지정한 컬럼값은 개수 일치, 데이터타입 일치, 크기 일치해야함
• 컬럼명은 생략할 수 있으며 생략하면 테이블 생성 시 정의한 컬럼 순서대로 값 지정
• 명시되지 않은 컬럼의 값은 자동으로 널(null)값이 지정

-- 컬럼명을 명시한 INSERT문
-- DEPT 테이블에 새로운 부서정보 저장
INSERT INTO dept( deptno, dname, loc )
VALUES ( 50 ,'개발','서울');

-- 컬럼명을 생략한 INSERT문
INSERT INTO dept
VALUES ( 60 ,'인사','경기');

💡 널(null) 데이터 입력하기

-- 명시적 입력  
INSERT INTO dept(deptno, dname , loc )
VALUES ( 70 ,'인사', NULL );  

-- 묵시적 입력  
INSERT INTO dept(deptno, dname )
VALUES ( 80 ,'개발'); -- LOC 컬럼이 생략되어 자동으로 널 값 지정

💡 날짜데이터 입력하기

• YYYY/MM/DD 또는 YYYY-MM-DD
• TO_DATE 이용
• SYSDATE : 현재 시점을 날짜 데이터로 사용할 경우

---

2. 다중행 INSERT문

INSERT INTO 테이블명 (컬럼명, 컬럼명2, ...)
SELECT 
FROM

💡 서브쿼리를 사용하여 한 번에 여러 데이터 추가

• VALUES 절은 사용하지 않음
• 데이터가 추가되는 테이블의 열 개수와 서브쿼리의 열 개수 일치, 자료형 일치해야 함

-- CTAS(씨탁스) : 존재하는 테이블을 서브쿼리로 사용하여 새로운 테이블을 생성하는 방법
CREATE TABLE mydept
AS
SELECT * FROM dept
WHERE 1=2; -- WHERE 절을 false로 설정하여 데이터를 제외한 테이블 구조만 복사

INSERT INTO mydept
SELECT deptno,dname,loc -- 서브쿼리 사용하여 복수행 INSERT
FROM dept;

---

3. 다중테이블 다중행 INSERT문

INSERT ALL
 [WHEN 조건식 THEN]
 INTO 테이블1 VALUES ( 컬럼명,컬럼명2,...,컬럼명n)
 [WHEN 조건식2 THEN]
 INTO 테이블2 VALUES ( 컬럼명,컬럼명2,...,컬럼명n)
Subquery;

• 서브쿼리를 실행한 결과가 지정된 테이블1과 테이블2에 자동으로 INSERT 된다.
• WHEN절이 있으면 조건식이 ture인 경우에만 INSERT

INSERT ALL
INTO myemp_hire VALUES ( empno,ename,hiredate,sal )
INTO myemp_mgr VALUES ( empno,ename,mgr,sal )
SELECT empno,ename,hiredate,sal,mgr
FROM emp;

💡 INSERT FIRST
WHEN절에 지정된 조건이 중복되어 만족하는 경우에 처음 조건에 일치하는 테이블에만 저장되고 이후에는 조건이 일치해도 테이블에 저장되지 않는다.

INSERT FIRST
WHEN sal = 500 THEN
 INTO table VALUES ( empno,ename,hiredate,sal )
WHEN sal < 1000 THEN
 INTO table2 VALUES ( empno,ename,mgr, sal )
SELECT empno,ename,hiredate,sal,mgr
FROM emp;

월급 sal이 500이면 첫 번째 WHEN절에도 만족하고 두 번째 WHEN절에도 만족한다. 따라서 첫 번째 조건에 만족하면 이후에는 조건이 일치해도 테이블에 저장하지 않도록하는 방식이 INSERT FIRST문이다.

---

UPDATE문

UPDATE문은 테이블에 저장된 데이터를 수정하기 위해서 사용된다.

UPDATE 테이블명
SET 컬럼명 = 값, 컬럼명2 = 값2, ...
[WHERE 조건식]

💡 서브쿼리를 이용한 UPDATE문
SET 절에서 서브쿼리를 사용하면 서브쿼리가 실행된 결과값으로 테이블을 수정할 수 있다.

UPDATE mydept
SET dname= ( SELECT dname
             FROM dept
             WHERE deptno = 10)
 ,loc= ( SELECT loc
         FROM dept
         WHERE deptno=20)
         WHERE deptno = 60;

💡 수정한 내용을 되돌리고 싶을 때
ROLLBACK 명령문 사용하면 UPDATE 명령어를 실행한 이전 상태로 돌아간다.

---

DELETE문

DELETE 문은 테이블에 저장된 데이터를 삭제하기 위해서 사용된다.

DELETE FROM 테이블명
[WHERE 조건식]; -- 조건에 일치하는 행들만 삭제 

💡 서브쿼리를 이용한 DELETE문
WHERE 절에서 서브쿼리를 사용하면 서브쿼리가 실행된 결과값으로 테이블을 삭제할 수 있다.

DELETE 
FROM mydept
WHERE loc = (SELECT loc
              FROM dept
              WHERE deptno = 20);

---

트랜잭션 (Transaction)

트랜잭션(Transaction)은 데이터베이스의 논리적인 작업 단위로서 하나 이상의 SQL문장이 포함될 수 있으며 트랜잭션의 대상은 DML문이다. Oracle에서 발생되는 여러 개의 작업들을 하나의 작업처럼 처리해야 되는 경우에 트랜잭션이 이용된다.

• 하나 이상의 DML이 모여서 하나의 트랜잭션으로 구성
• Oracle에서 DML문의 실행 결과는 COMMIT을 해야 데이터베이스에 저장된다.
• 트랜잭션 제어를 위한 명령어가 TCL(Transaction Control Language) 이다.

COMMIT

DML문에 의해서 실행되었으나 실제로 저장되지 않은 모든 데이터를 데이터베이스에 저장하고 현재의 트랜잭션을 종료하는 명령어이다.즉, 트랜잭션 내의 모든 개별적인 작업들이 정상적으로 처리되어 데이터베이스에 모두 반영되도록 한다.

ROLLBACK

저장되지 않은 모든 데이터의 변경사항을 취소하고 현재의 트랜잭션을 종료하는 명령어이다. 즉, 트랜잭션으로 인한 하나의 묶음처리가 시작되기 이전의 상태로 복구하는 것을 의미한다.TO SAVEPOINT 를 사용하면 SAVEPOINT 로 지정한 위치까지만 변경사항을 취소할 수 있다.

SAVEPOINT

진행중인 트랙잭션을 특정 이름으로 지정하는 명령어로서 책갈피 기능이다.

 

💡 LOCK 경합

B 사용자는 아무런 작업도 할 수 없게 되는데, 이유는 A 사용자가 부서번호가 40에 대해서 트랜잭션(UPDATE)을 발생시키고 명시적으로 종료하지 않았기 때문이다. 다른 모든 사용자는 A 사용자가 발생시킨 트랜잭션을 종료하기 전까지 무한 대기에 빠지게 된다. 이런 경우를 Lock 경합 이라고 부른다. 결론적으로 DML문을 사용한 경우에는 반드시 COMMIT이나 ROLLBACK으로 트랜잭션을 종료시켜야 다른 사용자가 무한 대기하는 상황을 피할 수 있다.