[SQL] DB 스터디 - 14

대형 데이터 집합 조작

※ 대부분 DML명령어

1. Subquery를 사용하여 데이터 조작

- DML문의 Subquery를 사용하여 다음의 작업을 수행할 수 있음

-> 인라인 뷰에서 데이터 검색

-> 테이블 간에 데이터 복사

-> 다른 테이블의 값을 기반으로 테이블의 데이터 갱신

-> 다른 테이블의 행을 기반으로 테이블에서 행 삭제

예)

->

-- departments 테이블과 JOIN

-- FROM절에 Subquery 사용

SELECT department_name, city

FROM departments

NATURAL JOIN (

SELECT l.location_id, l.city, l.country_id

FROM loc l JOIN countries c

ON ( l.country_id = c.country_id )

JOIN regions USING(region_id)

WHERE region_name = 'Europe'

);

-- INSERT문에 Subquery 사용

INSERT INTO (

SELECT l.location_id, l.city, l.country_id

FROM locations l

JOIN countries c

ON( l.country_id = c.country_id )

JOIN regions USING(region_id)

WHERE region_name = 'Europe'

-- WITH CHECK OPTION

-- 입력되는 값에 제약조건을 달 수 있음

)

VALUES (3300, 'Cardiff', 'UK');

---

2. 다중 테이블 INSERT문의 사용

- 전제조건이 필요 함

-> SELECT명령문을 서로 다른 조건으로 실행

-> 검색된 결과는 INSERT INTO를 이용해 특정 테이블 2에 데이터를 입력 할 때

-> 각각의 테이블은 하나 이지만 읽었던 행을 또 읽을 때

-> 반드시 Subquery가 사용 되어야 함

- 실제로 값이 저장 될 때 만약 emp테이블의 정보를 서로 다른 컬럼을 이용해 접근 한다면, emp테이블에 한 번만 접근 하면 됨

- 다중 테이블의 명령문

-> INSERT

-> INSERT ALL

-> INSERT FIRST

예)

->

-- 무조건 INSERT ALL 명령문

-- 조건이 필요 하다면 ALL다음에 WHEN절을 작성

-- 컬럼만 변경 해서 값을 저장하는 부분

INSERT ALL

INTO HIRE_EMP VALUES (empno, ename, hiredate)

INTO SAL_EMP VALUES (empno, ename, sal)

SELECT * FROM emp;

-- 조건부 INSERT ALL 명령문

-- WHEN절이 여러 개 있어도 모든 WHEN절 마다 수행 됨

INSERT ALL

-- HIREDATE가 01-JAN-95보다 작으면 emp_history테이블에 입력

WHEN HIREDATE < '01-JAN-95' THEN

INTO  emp_history VALUES(EMPID,HIREDATE,SAL)

-- COMM이 NULL이 아니면 emp_sales테이블에 입력

WHEN COMM IS NOT NULL THEN

INTO  emp_sales VALUES(EMPID,COMM,SAL)

SELECT 

employee_id EMPID, hire_date HIREDATE, salary SAL, commission_pct COMM

FROM employees;

-- 조건부 INSERT FIRST

-- 첫 번째 WHEN절을 기준으로 대상 sal_low테이블에 저장

-- 이 경우 두 번째 WHEN절은 건너 뜀

-- 모든 WHEN절의 조건이 충족되지 않으면 ELSE절로 넘어가서 sal_high테이블에 저장

INSERT FIRST

WHEN salary < 5000 THEN

INTO sal_low VALUES (employee_id, last_name, salary)

WHEN salary between 5000 and 10000 THEN

INTO sal_mid VALUES (employee_id, last_name, salary)

ELSE

INTO sal_high VALUES (employee_id, last_name, salary)

SELECT 

employee_id, last_name, salary

FROM employees

-- 피벗팅 INSERT

-- 비 정형화된 데이터를 보기 좋게 만드는 작업

-- 컬럼을 요일 순서로 보여주면서 달력의 형태로 매출 정보를 출력

INSERT ALL

INTO sales_info VALUES (employee_id,week_id,sales_MON)

INTO sales_info VALUES (employee_id,week_id,sales_TUE)

INTO sales_info VALUES (employee_id,week_id,sales_WED)

INTO sales_info VALUES (employee_id,week_id,sales_THUR)

INTO sales_info VALUES (employee_id,week_id, sales_FRI)

SELECT 

EMPLOYEE_ID, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR,sales_FRI

FROM sales_source_data;

---

3. 테이블 병합

- 데이터베이스 테이블을 조건부로 갱신하거나 데이터를 삽입 또는 삭제하는 기능을 제공

- 행이 존재하는 경우 UPDATE를 수행, 새로운 행인 경우 INSERT 수행

-> 별도의 갱신을 방지

-> 성능 및 사용 편의성 향상

-> 데이터 웨어하우징 응용 프로그램에서 유용

예)

->

-- 변경된 데이터가 무엇인지는 모른다고 가정

-- MERGE문을 사용하지 않는 경우

-- 변경된 값은 UPDATE

UPDATE copy_emp C

SET

    -- 모든 컬럼을 비교하여 모두 입력 해야 함 

    ename = (SELECT ename FROM emp WHERE empno = C.empno), 

    job = (SELECT job FROM emp WHERE empno = C.empno),

    mgr = (SELECT mgr FROM emp WHERE empno = C.empno), 

    hiredate = (SELECT hiredate FROM emp WHERE empno = C.empno),

    sal = (SELECT sal FROM emp WHERE empno = C.empno);

-- 없는 값은 INSERT

INSERT INTO copy_emp

SELECT * FROM emp E

WHERE NOT EXISTS (

        SELECT * FROM copy_emp WHERE empno = E.empno

);

-- MERGE명령어 사용

MERGE INTO copy_emp C

USING emp E

ON (E.empno = C.empno)

WHEN MATCHED THEN

UPDATE

SET

    C.ename = E.ename,

    C.job = E.job,

    C.sal = E.sal,

    C.deptno = E.deptno

WHEN NOT MATCHED THEN

INSERT (C.ename, C.job, C.sal, C.deptno)

VALUES (E.ename, E.job, E.sal, E.deptno);