DML (Data Manipulation Language)(INSERT, UPDATE, DELETE)

김찬희·2023년 3월 21일

KH정보교육원

목록 보기

20/27

▶ DML(Data Manipulation Language)

데이터 조작 언어로 테이블에 값을 삽입(INSERT), 수정(UPDATE), 삭제(DELETE)하는 구문을 말함
✓ 예시

INSERT INTO EMPLOYEE
VALUES(1, ‘홍길동’, ‘820114-1010101’, ‘hong_kd@kh.or.kr’, ‘01099998888’, ‘D5’, ‘J2’, ‘S4’, 3800000, NULL, ‘200’, SYSDATE, NULL, DEFAULT);

UPDATE EMPLOYEE
SET EMP_ID = 290
WHERE EMP_NAME = ‘홍길동’;

DELTE FROM EMPLOYEE
WHERE EMP_NAME = ‘홍길동’;

▶ INSERT

테이블에 새로운 행을 추가하여 테이블의 행 개수를 증가시키는 구문

✓ INSERT 예시1

INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, EMP_NO, EMAIL, PHONE, DEPT_CODE, JOB_CODE, SAL_LEVEL, SALARY, BONUS, MANAGER_ID, HIRE_DATE, ENT_DATE, ENT_YN)
VALUES(900, '장채현', '901123-1080503', 'jang_ch@kh.or.kr', '01055569512', 'D1', 'J8', 'S3', 4300000, 0.2, '200', SYSDATE, NULL, DEFAULT);

또는

INSERT INTO EMPLOYEE VALUES(900, '장채현', '901123-1080503', 'jang_ch@kh.or.kr', '01055569512', 'D1', 'J8', 'S3', 4300000, 0.2, '200', SYSDATE, NULL, DEFAULT);

＊ INSERT하고자 하는 컬럼이 모든 컬럼인 경우 컬럼명 생략 가능. 단, 컬럼의 순서를 지켜서 VALUES에 값을 기입해야 함

✓ INSERT 예시2

CREATE TABLE EMP_01(
			EMP_ID NUMBER,
			EMP_NAME VARCHAR2(30),
			DEPT_TITLE VARCHAR2(20)
);

INSERT INTO EMP_01(
		SELECT EMP_ID,
			EMP_NAME,
			DEPT_TITLE
		FROM EMPLOYEE
		LEFT JOIN DEPARTMENT ON (DEPT_CODE = DEPT_ID)
);

＊ INSERT 시 VALUES 대신 서브쿼리 이용 가능

▶ INSERT ALL

INSERT 시 서브쿼리가 사용하는 테이블이 같은 경우 두 개 이상의 테이블에 INSERT ALL을 이용하여 한 번에 삽입 가능
단, 각 서브쿼리의 조건절이 같아야 함
✓ INSERT ALL 예시1

CREATE TABLE EMP_DEPT_D1
AS SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE
	FROM EMPLOYEE
	WHERE 1 = 0;

CREATE TABLE EMP_MANAGER
AS SELECT EMP_ID, EMP_NAME, MANAGER_ID
	FROM EMPLOYEE
	WHERE 1 = 0;

EMP_DEPT_D1테이블에 EMPLOYEE테이블의 부서코드가 D1인 직원의 사번, 이름, 소속부서, 입사일을 삽입하고 EMP_MANAGER테이블에 EMPLOYEE테이블의 부서코드가 D1인 직원의 사번, 이름, 관리자 사번을 조회하여 삽입

INSERT ALL
INTO EMP_DEPT_D1 VALUES(EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE)
INTO EMP_MANAGER VALUES(EMP_ID, EMP_NAME, MANAGER_ID)
SELECT EMP_ID, EMP_NAME, DEPT_CODE, HIRE_DATE, MANAGER_ID
FROM EMPLOYEE
WHERE DEPT_CODE = ‘D1’;

✓ INSERT ALL 예시2

EMPLOYEE테이블의 구조를 복사하여 사번, 이름, 입사일, 급여를 기록할 수 있는
테이블 EMP_OLD와 EMP_NEW 생성

CREATE TABLE EMP_OLD AS SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY 
FROM EMPLOYEE 
WHERE 1 = 0;

CREATE TABLE EMP_NEW AS SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
FROM EMPLOYEE
WHERE 1 = 0;

EMPLOYEE테이블의 입사일 기준으로 2000년 1월 1일 이전에 입사한 사원의 사번, 이름,
입사일, 급여를 조회해서 EMP_OLD테이블에 삽입하고 그 후에 입사한 사원의 정보는
EMP_NEW테이블에 삽입

INSERT ALL
WHEN HIRE_DATE < ‘2000/01/01’ THEN
	INTO EMP_OLD VALUES(EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
WHEN HIRE_DATE >= ‘2000/01/01’ THEN
	INTO EMP_NEW VALUES(EMP_ID, EMP_NAME, HIRE_DATE, SALARY)
SELECT EMP_ID, EMP_NAME, HIRE_DATE, SALARY
FROM EMPLOYEE;

▶ UPDATE

테이블에 기록된 컬럼의 값을 수정하는 구문으로 테이블의 전체 행 개수에는 변화가 없음

✓ UPDATE 예시1

CREATE TABLE DEPT_COPY
AS SELECT * FROM DEPARTMENT;

UPDATE DEPT_COPY
SET DEPT_TITLE = ‘전략기획팀’
WHERE DEPT_ID = ‘D9’;

＊ WHERE 조건을 설정하지 않으면
모든 행의 컬럼 값이 변경됨

✓ UPDATE 예시2

방명수 사원의 급여와 보너스율을 유재식 사원과 동일하게 변경

CREATE TABLE EMP_SALARY
AS SELECT EMP_ID,
		EMP_NAME,
        DEPT_CODE,
        SALARY,
        BONUS
   FROM EMPLOYEE;
   
SELECT * FROM EMP_SALARY
WHERE EMP_NAME IN('유재식', '방명수');

UPDATE EMP_SALARY
SET SALARY = (SELECT SALARY
			FROM EMP_SALARY
            WHERE EMP_NAME='유재식'),
    BONUS = (SELECT BONUS
    		FROM EMP_SALARY
            WHERE EMP_NAME='유재식')
WHERE EMP_NAME = '방명수';

＊ UPDATE 시에도 서브쿼리 이용 가능

✓ UPDATE 예시3

각각 쿼리문 작성한 것을 다중 행 다중 열 서브쿼리로 변경

UPDATE EMP_SALARY
SET (SALARY, BONUS) = (SELECT SALARY, BONUS
						FROM EMP_SALARY
						WHERE EMP_NAME = ‘유재식’)
WHERE EMP_NAME IN (‘노옹철’, ‘전형돈’, ‘정중하’, ‘하동운');

SELECT * FROM EMP_SALARY
WHERE EMP_NAME IN (‘유재식’, ‘노옹철’, ‘전형돈’, ‘정중하’, ‘하동운');

✓ UPDATE 예시4

EMP_SALARY테이블에서 아시아 지역에 근무하는 직원의 보너스 포인트를 0.3으로 변경

UPDATE EMP_SALARY
SET BONUS = 0.3
WHERE EMP_ID IN (SELECT EMP_ID
				FROM EMPLOYEE
				JOIN DEPARTMENT ON(DEPT_ID = DEPT_CODE)
				JOIN LOCATION ON(LOCATION_ID = LOCAL_CODE)
				WHERE LOCAL_NAME LIKE ‘ASIA%’);

▶ MERGE

구조가 같은 두 개의 테이블을 하나의 테이블로 합치는 기능 제공
두 테이블에서 지정하는 조건의 값이 존재하면 UPDATE되고 조건의 값이 없으면 INSERT 함

✓ 예시

CREATE TABLE EMPLOYEE_M01 
AS SELECT * 
FROM EMPLOYEE; 

CREATE TABLE EMP_M02
AS SELECT *
FROM EMPLOYEE
WHERE JOB_CODE = ‘J4’;

INSERT INTO EMP_M02
VALUES(999, ‘곽두원’, ‘561016-1234567’, ‘kwack_dw@kh.or.kr’, ‘01011112222’, ‘D9’, ‘J1’, ‘S1’, 9000000, 0.5, NULL, SYSDATE, DEFAULT, DEFAULT);
UPDATE EMP_M02 SET SALARY = 0;

MERGE INTO EMP_M01 USING EMP_M02 ON(EMP_M01.EMP_ID = EMP_M02.EMP_ID)
WHEN MATCHED THEN
UPDATE SET
  EMP_M01.EMP_NAME = EMP_M02.EMP_NAME,
  EMP_M01.EMP_NO = EMP_M02.EMP_NO,
  EMP_M01.EMAIL = EMP_M02.EMAIL,
  EMP_M01.PHONE = EMP_M02.PHONE,
  EMP_M01.DEPT_CODE = EMP_M02.DEPT_CODE,
  EMP_M01.JOB_CODE = EMP_M02.JOB_CODE,
  EMP_M01.SAL_LEVEL = EMP_M02.SAL_LEVEL,
  EMP_M01.SALARY = EMP_M02.SALARY,
  EMP_M01.BONUS = EMP_M02.BONUS,
  EMP_M01.MANAGER_ID = EMP_M02.MANAGER_ID,
  EMP_M01.HIRE_DATE = EMP_M02.HIRE_DATE,
  EMP_M01.ENT_DATE = EMP_M02.ENT_DATE,
  EMP_M01.ENT_YN = EMP_M02.ENT_YN
WHEN NOT MATCHED THEN
  INSERT VALUES(EMP_M02.EMP_ID, EMP_M02.EMP_NAME, EMP_M02.EMP_NO, EMP_M02.EMAIL,
  EMP_M02.PHONE, EMP_M02.DEPT_CODE, EMP_M02.JOB_CODE, EMP_M02.SAL_LEVEL,
  EMP_M02.SALARY, EMP_M02.BONUS, EMP_M02.MANAGER_ID, EMP_M02.HIRE_DATE,
  EMP_M02.ENT_DATE, EMP_M02.ENT_YN);

SELECT * FROM EMP_M01;

▶ DELETE

테이블의 행을 삭제하는 구문으로 테이블의 행 개수가 줄어듦
✓ DELETE 예시1

DELETE FROM EMPLOYEE
WHERE EMP_NAME = '장재현';

＊ WHERE조건을 설정하지 않으면 모든 행 삭제

DELETE FROM DEPARTMENT
WHERE DEPT_ID = 'D1';

＊ FOREIGN KEY 제약조건이 설정되어 있는 경우 참조되고 있는 값에 대해서는 삭제 불가능

삭제 시 FOREIGN KEY 제약조건으로 컬럼 삭제가 불가능한 경우 제약조건을 비활성화 할 수 있음

✓ DELETE 예시2

DELETE FROM DEPARTMENT
WHERE DEPT_ID = ‘D1’;

ALTER TABLE EMPLOYEE
DISABLE CONSTRAINT EMP_DEPTCODE_FK CASCADE;

DELETE FROM DEPARTMENT
WHERE DEPT_ID = ‘D1’;

ALTER TABLE EMPLOYEE
ENABLE CONSTRAINT EMP_DEPTCODE_FK;

＊ 비활성화 된 제약 조건을 다시 활성화 시킬 수 있음

▶ TRUNCATE

테이블 전체 행 삭제 시 사용 하는 DDL로 DELETE보다 수행 속도가 빠르고 ROLLBACK을 통해 복구 불가능
또한 DELETE와 마찬가지로 FOREIGN KEY 제약조건일 때는 적용 불가능하기 때문에 제약 조건을 비활성화 해야 삭제할 수 있음

✓ 예시

TRUNCATE TABLE EMP_SALARY;
SELECT * FROM EMP_SALARY;

＊ 모든 컬럼이 삭제되긴 하지만 테이블의 구조는 남아있음

ROLLBACK;

＊ ROLLBACK 후에도 컬럼이 복구되지 않음

김찬희

김찬희입니다.

이전 포스트

JOIN

다음 포스트