🔍 DML(Data Manipulation Language)

✍ 데이터베이스 내의 데이터를 조작하고 관리하는 데 사용되는 SQL 명령어의 집합. DML 명령어를 사용하면 테이블에 저장된 데이터를 조회, 삽입, 수정, 삭제할 수 있으며 데이터베이스 관리 시스템(DBMS)에서 DML은 데이터의 실제 콘텐츠를 관리하는 데 중점을 둔다

✍ 이 글을 작성하기 전 SQL 블로그를 작성할 때 DML을 간단하게 설명한 적이 있다. 이 글에서는 자세하게 사용하는 법과 서브쿼리를 같이 쓰는 법에 대해서 알아보는 시간을 가질 생각이다.

🔍 CTAS(CREATE TABLE AS SELECT)

✍ CTAS를 사용하면 기존 테이블의 구조를 기반으로 새로운 테이블을 생성하고, 선택한 데이터를 새로운 테이블로 복사할 수 있다.
✍ 복사된 테이블에는 원본 테이블의 데이터뿐만 아니라 해당 데이터에 대한 인덱스, 제약 조건, 트리거 등은 포함되지 않는다. 단순히 데이터와 스키마 구조만 복사. (NOT NULL 제약조건은 복사)

-- CTAS
-- WHERE절에 1=2를 넣으면 테이블 행에 대한 모든 값이 FALSE반환이기 때문에 테이블의 구조만 갖게된다.
-- 제약조건은 NOT NULL만 가져오고 다른 제약 조건은 복사가 되지 않는다.
CREATE TABLE MANAGERS AS
    SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY, HIRE_DATE
    FROM EMPLOYEES
    WHERE 1=2; -- MANAGERS 테이블은 EMPLOYEES 데이터 없이 테이블의 구조만 갖게된다.
    
SELECT * FROM MANAGERS; 

🔍 INSERT

✍ 테이블에 새로운 데이터(행)을 삽입할 수 있다. 컬럼 명을 명시했을 때에는 VALUES에 명시한 컬럼의 데이터 값만 넣어주면 되고, 컬럼 명을 생략했을 시에는 테이블의 모든 컬럼의 값을 차례대로 삽입해야 한다.

-- DML
-- 테이블 구조 확인
DESC DEPARTMENTS;

-- 컴럼명을 생략하면 모든 컬럼에 대한 값을 VALUES에 넣어 삽입하여야 한다.
INSERT INTO DEPARTMENTS
VALUES (280, 'Data Analytics', null, 1700);

-- 컬럼명을 명시하면 VALUES에 컬럼 명 순서대로 값을 넣으면 된다.
-- 그리고 명시하지 않은 컬럼의 값은 자동으로 NULL이 된다.
-- NOT NULL 제약조건을 잘 파악하여야 함.
INSERT INTO DEPARTMENTS
    (DEPARTMENT_ID, DEPARTMENT_NAME, LOCATION_ID)
VALUES 
    (280, 'Data Analytics', 1700);

SELECT * FROM DEPARTMENTS;

-- INSERT 다른 테이블로부터 행 복사(서브쿼리)
-- CTAS로 EMPLOYEES 테이블의 구조를 복사한 MANAGERS 테이블에 JOB_ID가 MAN으로 끝나는 데이터들을 삽입
INSERT INTO MANAGERS
    SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY, HIRE_DATE
    FROM EMPLOYEES
    WHERE JOB_ID LIKE '%MAN';
    
SELECT * FROM MANAGERS;

🔍 UPDATE

✍ 데이터베이스 테이블의 기존 데이터를 수정하는 데 사용된다. UPDATE 문을 통해 특정 조건을 만족하는 하나 이상의 행의 데이터를 변경할 수 있다.

SELECT * FROM MANAGERS;

-- CTAS 테이블 복사
CREATE TABLE EMPS AS SELECT * FROM EMPLOYEES;

-- CTAS 테이블 복사하면 제약조건이 없기 때문에 추가
ALTER TABLE EMPS
ADD (CONSTRAINT EMPS_EMP_ID_PK PRIMARY KEY (EMPLOYEE_ID),
     CONSTRAINT EMPS_MANAGER_FK FOREIGN KEY (MANAGER_ID)
                REFERENCES EMPS(EMPLOYEE_ID)
    );
    
SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
FROM EMPS
WHERE EMPLOYEE_ID = 103; -- SALARY = 9000

UPDATE EMPS
SET SALARY = SALARY * 1.1
WHERE EMPLOYEE_ID = 103; -- SALARY = 9900

-- UPDATE 다중 열 갱신(서브쿼리)
-- 서브쿼리로 109번 사원번호를 가지고 있는 컬럼의 값을 108번의 사원번호를 갖고 있는 사원의 값으로 변경
UPDATE EMPS
SET (JOB_ID, SALARY, MANAGER_ID) = 
    (SELECT JOB_ID, SALARY, MANAGER_ID
     FROM EMPS
     WHERE EMPLOYEE_ID = 108)
WHERE EMPLOYEE_ID = 109;

SELECT JOB_ID, SALARY, MANAGER_ID
FROM EMPS
WHERE EMPLOYEE_ID = 108;

🔍 DELETE

✍ 데이터베이스 테이블에서 데이터를 삭제하는 데 사용되며 DELETE 문을 사용하면 테이블에서 특정 조건에 맞는 행들을 제거할 수 있습니다.

-- DELETE
DELETE FROM EMPS
WHERE EMPLOYEE_ID = 104;

SELECT * FROM EMPS WHERE EMPLOYEE_ID = 104;

-- 참조 무결성 제약 조건으로 인해 삭제 불가
DELETE FROM EMPS
WHERE EMPLOYEE_ID = 103;

-- 다른 테이블을 이용한 행 삭제(서브쿼리)
CREATE TABLE DEPTS AS SELECT * FROM DEPARTMENTS;

-- 106개의 행
SELECT * FROM EMPS; 

DELETE FROM EMPS
WHERE DEPARTMENT_ID =
                    (SELECT DEPARTMENT_ID
                     FROM DEPTS
                     WHERE DEPARTMENT_NAME = 'Shipping'); -- 50번 부서번호(45명)

-- 삭제 서브쿼리 후 결과: 61개의 행
SELECT * FROM EMPS; 

🔍 MERGE

✍ 두 테이블을 병합할 때 사용하며 데이터가 존재하는지 여부를 체크하고 데이터가 존재하면 UPDATE, 존재하지 않으면 INSERT를 사용하는 것이 MERGE이다.

CREATE TABLE EMPS_IT AS SELECT * FROM EMPLOYEES WHERE 1=2;

INSERT INTO EMPS_IT
    (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID)
VALUES
    (105, 'David', 'Kim', 'DAVIDKIM', '06/03/04', 'IT_PROG');

SELECT * FROM EMPS_IT;

-- JOB_ID가 'IT_PROG'인 데이터 중 ON절 조건에 해당되는 행이 존재하는데, 
-- 그 중 앞에 미리 삽입했었던 105번 사원번호의 DAVID는 UPDATE, 미리 삽입하지 않은 데이터들은 iNSERT
MERGE INTO EMPS_IT a
    USING (SELECT * FROM EMPLOYEES WHERE JOB_ID = 'IT_PROG') b
    ON (a.EMPLOYEE_ID = b.EMPLOYEE_ID) 
WHEN MATCHED THEN -- 매칭 되는 것이 있으면 UPDATE
    UPDATE SET
    	a.PHONE_NUMBER = b.PHONE_NUMBER,
    	a.HIRE_DATE = b.HIRE_DATE,
    	a.JOB_ID = b.JOB_ID,
    	a.SALARY = b.SALARY,
    	a.COMMISSION_PCT = b.COMMISSION_PCT,
    	a.MANAGER_ID = b.MANAGER_ID,
    	a.DEPARTMENT_ID = b.DEPARTMENT_ID
WHEN NOT MATCHED THEN -- 매칭 되는 것이 없으면 INSERT
    INSERT VALUES
    	(b.EMPLOYEE_ID, b.FIRST_NAME, b.LAST_NAME, b.EMAIL,
     	b.PHONE_NUMBER, b.HIRE_DATE, b.JOB_ID, b.SALARY,
     	b.COMMISSION_PCT, b.MANAGER_ID, b.DEPARTMENT_Id);

SELECT * FROM EMPS_IT;

🔍 MULTIPLE INSERT

✍ SQL에서 여러 행을 한 번에 삽입할 때 사용하는 기능, 이는 여러 개의 INSERT 명령어를 한 번에 실행하는 것보다 효율적이며, 코드의 가독성과 유지보수성을 높여준다.

🔍 UNCONDTIONAL INSERT ALL

✍ 서브쿼리로부터 한 번에 하나의 행을 반환받아 조건 없이 여러 개의 테이블에 값을 입력하기 위해 INSERT ALL을 사용한다. 테이블의 테이터를 열 단위로 나눠 여러 테이블에 저장할 떄 사용

-- CTAS 구문에서 결과에서 TRUE를 반환하면 구조 뿐 아니라 데이터까지 복사
CREATE TABLE EMP2 AS SELECT * FROM EMPLOYEES;

-- CTAS 구문에서 결과에서 FALSE를 반환하면 구조만 복사
CREATE TABLE EMP3 AS SELECT * FROM EMPLOYEES WHERE 1=2;

SELECT * FROM EMP2;
SELECT * FROM EMP3;

-- UNCONDITIONAL INSERT ALL = 조건과 상관없이 기술되어진 여러 개의 테이블에 데이터를 입력
INSERT ALL
    INTO EMP2 VALUES
        (300, 'Kildong', 'Hong', 'KHONG', '011-234-5678',
         TO_DATE('2015-05-11', 'YYYY-MM-DD'), 'IT_PROG', 6000,
         NULL, 100, 90)
    INTO EMP3 VALUES
         (400, 'Kilseo', 'Hong', 'KSHONG', '011-456-7899',
          TO_DATE('2015-06-20', 'YYYY-MM-DD'), 'IT_PROG', 5500,
          NULL, 100, 90)
    SELECT * FROM DUAL;
    
CREATE TABLE EMP_SALARY 
    AS SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT 
FROM EMPLOYEES 
WHERE 1=2;

CREATE TABLE EMP_HIRE_DATE 
    AS SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, DEPARTMENT_ID 
FROM EMPLOYEES 
WHERE 1=2;

SELECT * FROM EMP_SALARY;

SELECT * FROM EMP_HIRE_DATE;

INSERT ALL
    INTO EMP_SALARY VALUES
        (EMPLOYEE_ID, FIRST_NAME, SALARY, COMMISSION_PCT) 
    INTO EMP_HIRE_DATE VALUES
        (EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, DEPARTMENT_ID) 
    SELECT * FROM EMPLOYEES;
-- EMPLOYEES 테이블의 컬럼 값으로 EMP_SALARY, EMP_HIRE_DATE의 컬럼 값을 채워서 총 214개의 행이 추가.

🔍 CONDTIONAL INSERT ALL

✍ 특정 조건을 기술하여 그 조건에 맞는 행들을 원하는 테이블에 나누어 삽입, WHEN ~ THEN절에 동등비교를 이용하여 테이블 데이터를 나눈다.

-- CONDITIONAL INSERT ALL = 특정 조건들을 기술하여 그 조건에 맞는 행들을 원하는 테이블에 나누어 삽입
CREATE TABLE EMP_10 AS SELECT * FROM EMPLOYEES WHERE 1=2;

CREATE TABLE EMP_20 AS SELECT * FROM EMPLOYEES WHERE 1=2;

INSERT ALL
    WHEN DEPARTMENT_ID = 10 THEN
        INTO EMP_10 VALUES
            (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER,
             HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID,
             DEPARTMENT_ID)
    WHEN DEPARTMENT_ID = 20 THEN
        INTO EMP_20 VALUES
            (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER,
             HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID,
             DEPARTMENT_ID)
    SELECT * FROM EMPLOYEES;
    
SELECT * FROM EMP_10;

SELECT * FROM EMP_20;

🔍 CONDTIONAL INSERT FIRST

✍ 첫 번째 WHEN 절에서 조건을 만족할 경우 다음 WHEN절을 수행하지 않는다. WHEN ~ THEN 절에 비교 연산자를 사용하여 행 단위 테이블의 데이터를 나눈다. (자바에서 else if를 생각하면 쉽게 이해할 수 있다.)

CREATE TABLE EMP_SAL5000 
    AS SELECT EMPLOYEE_ID, FIRST_NAME, SALARY 
FROM EMPLOYEES 
WHERE 1=2;

CREATE TABLE EMP_SAL10000 
    AS SELECT EMPLOYEE_ID, FIRST_NAME, SALARY 
FROM EMPLOYEES 
WHERE 1=2;

CREATE TABLE EMP_SAL15000 
    AS SELECT EMPLOYEE_ID, FIRST_NAME, SALARY 
FROM EMPLOYEES 
WHERE 1=2;

CREATE TABLE EMP_SAL20000 
    AS SELECT EMPLOYEE_ID, FIRST_NAME, SALARY 
FROM EMPLOYEES 
WHERE 1=2;

CREATE TABLE EMP_SAL25000 
    AS SELECT EMPLOYEE_ID, FIRST_NAME, SALARY 
FROM EMPLOYEES 
WHERE 1=2;

-- 급여 구간에 따라 각각 다른 테이블의 데이터를 나누어 저장
INSERT FIRST
    WHEN SALARY <= 5000 THEN
        INTO EMP_SAL5000 VALUES (EMPLOYEE_ID, FIRST_NAME, SALARY)
    WHEN SALARY <= 10000 THEN
        INTO EMP_SAL10000 VALUES (EMPLOYEE_ID, FIRST_NAME, SALARY)
    WHEN SALARY <= 15000 THEN
        INTO EMP_SAL15000 VALUES (EMPLOYEE_ID, FIRST_NAME, SALARY)
    WHEN SALARY <= 20000 THEN
        INTO EMP_SAL20000 VALUES (EMPLOYEE_ID, FIRST_NAME, SALARY)
    WHEN SALARY <= 25000 THEN
        INTO EMP_SAL25000 VALUES (EMPLOYEE_ID, FIRST_NAME, SALARY)
    SELECT EMPLOYEE_ID, FIRST_NAME, SALARY FROM EMPLOYEES;

🔍 PIVOTING INSERT

✍ 스프레드 시트 형식으로 되어있는 테이블을 관계형 데이터 행으로 바꿔주는 역할

-- PIVOTING INSERT
DROP TABLE SALES;

CREATE TABLE SALES(
    EMPLOYEE_ID NUMBER(6),
    WEEK_ID NUMBER(2),
    SALES_MON NUMBER(8,2),
    SALES_TUE NUMBER(8,2),
    SALES_WED NUMBER(8,2),
    SALES_THU NUMBER(8,2),
    SALES_FRI NUMBER(8,2)
);

INSERT INTO SALES VALUES(1101, 4, 100, 150, 80, 60, 120);
INSERT INTO SALES VALUES(1102, 5, 300, 300, 230, 120, 150);
COMMIT;
SELECT * FROM SALES;

CREATE TABLE SALES_DATA(
    EMPLOYEE_ID NUMBER(6),
    WEEK_ID NUMBER(2),
    WEEK_DAY VARCHAR2(10),
    SALES NUMBER(8,2)
);

SELECT * FROM SALES_DATA;

INSERT ALL
    INTO SALES_DATA
        VALUES(EMPLOYEE_ID, WEEK_ID, 'SALES_MON', SALES_MON)
    INTO SALES_DATA
        VALUES(EMPLOYEE_ID, WEEK_ID, 'SALES_TUE', SALES_TUE)
    INTO SALES_DATA
        VALUES(EMPLOYEE_ID, WEEK_ID, 'SALES_TUE', SALES_TUE)
    INTO SALES_DATA
        VALUES(EMPLOYEE_ID, WEEK_ID, 'SALES_WED', SALES_WED)
    INTO SALES_DATA
        VALUES(EMPLOYEE_ID, WEEK_ID, 'SALES_THU', SALES_THU)
    INTO SALES_DATA
        VALUES(EMPLOYEE_ID, WEEK_ID, 'SALES_FRI', SALES_FRI)
    SELECT * FROM SALES;
    
SELECT * FROM SALES_DATA;

📖 reference

인프런 오라클 데이터베이스