4장. SQL - DML(추가)

<System Dictionary>

System Dictionary는 DBA 권한을 가진 ID가 이용할 수 있음

 

SELECT * FROM SYS.DBA_USERS; -- 모든 사용자(스키마) 출력

 

SELECT * FROM SYS.DBA_TAB_COLUMNS

WHERE OWNER = 'HR' AND TABLE_NAME = 'EMPLOYEES';

----// 타 스키마 구조 검색

 

<부분합, 총합 구하기>

select 주문고객, sum(수량)

from 주문

group by rollup(주문고객); => 총합계

 

select 주문고객, 주문제품, sum(수량)

from 주문

group by rollup(주문고객, 주문제품); => 소, 중, 총합계

 

select 주문고객, sum(수량), grouping_id(주문고객) ==> 그룹화한 ID를 표시 : 레벨값 출력

from 주문

group by rollup(주문고객);

 

 

SELECT 주소, 학년, count(학번) as 인원수

FROM UNIV.학생

GROUP BY CUBE(주소, 학년) ==> 주소별, 학년별로 몇 명인지 보여줌

ORDER BY 주소, 학년;

 

<WITH & CTE>

WITH : CTE(Common Table Expression)을 표현하는 키워드

CTE : 임시테이블 역할, Non-Recursive, Recursive로 나뉨

 

WITH 구매수량(고객ID, 주문수량)

AS

( SELECT 주문고객, SUM(수량)

FROM 주문 GROUP BY 주문고객 )

SELECT 고객ID, 주문수량 FROM 구매수량 ORDER BY 주문수량 DESC ;

==> 임시로 생성되는 중간 테이블을 공유하는 방법을 제공

 

WITH 학년평균(학년, 평균나이)

AS

( SELECT 학년, avg(나이) from UNIV.학생 group by 학년)

SELECT 학년, 평균나이 FROM 학년평균 WHERE rownum =1 ORDER BY 평균나이 DESC;

==> 보기에는 맞아보이지만 단계적으로 진행되기 때문에 DESC이든 ASC이든 rownum =1가 먼저 실행되기 때문에 정렬을 먼저 해줘야한다!

 

WITH 학년평균(학년, 평균나이)

AS

( SELECT 학년, avg(나이) from UNIV.학생 group by 학년)

SELECT 학년, 평균나이

FROM ( SELECT 학년, 평균나이 FROM 학년평균 ORDER BY 평균나이 DESC)

WHERE ROWNUM = 1;

==> 정렬을 먼저 해주고 1번을 출력하는 모습

 

WITH

일차(고객ID, 주문량)

AS (SELECT 주문고객, SUM(수량) FROM 주문 GROUP BY 주문고객 ),

이차(부분합)

AS (SELECT SUM(주문량) FROM 일차 ),

삼차(주문평균)

AS (SELECT 부분합 / (SELECT count(*) FROM 주문) FROM 이차)

SELECT * FROM 삼차; ==> 이런식으로 단계적으로 표현가능, 부질의는 SFW 모두 가능

 

###########모르겠음#############

CREATE TABLE 직원

(

이름 NCHAR(3),

직책 NCHAR(5),

부서 NCHAR(5),

관리자 NCHAR(3)

);

 

INSERT INTO 직원 VALUES('JDK','CEO', NULL, NULL);

INSERT INTO 직원 VALUES('유재석','예능이사', '예능국', 'JDK');

INSERT INTO 직원 VALUES('송강호','영화이사', '영화국', 'JDK');

INSERT INTO 직원 VALUES('현빈', '드라마이사', '드라마국', 'JDK');

INSERT INTO 직원 VALUES('전현무', '예능부장', '예능국', '유재석');

INSERT INTO 직원 VALUES('조세호', '예능1과장', '예능국', '전현무');

INSERT INTO 직원 VALUES('정형돈', '예능2과장', '예능국', '전현무');

INSERT INTO 직원 VALUES('남창희', '예능직원', '예능국', '조세호');

INSERT INTO 직원 VALUES('팽수', '예능직원', '예능국', '정형돈');

INSERT INTO 직원 VALUES('이병헌', '영화부장', '영화국', '송강호');

INSERT INTO 직원 VALUES('전지현', '영화과장', '영화국', '이병헌');

INSERT INTO 직원 VALUES('하정우', '영화직원', '영화국', '전지현');

INSERT INTO 직원 VALUES('손예진', '드라마부장', '드라마국', '현빈');

INSERT INTO 직원 VALUES('이승기', '드라마과장', '드라마국', '손예진');

INSERT INTO 직원 VALUES('최양락', '드라마직원', '드라마국', '이승기');

 

 

COMMIT;

 

WITH 직원CTE(이름, 직책, 부서, 관리자, 직급)

AS

(

( SELECT 이름, 직책, 부서, 관리자, 0

FROM 직원

WHERE 관리자 IS NULL ) -- JDK는 상관이 없음. 직급 0

UNION ALL

(SELECT 직원.이름, 직원.직책, 직원.부서, 직원.관리자, 직원CTE.직급+1

FROM 직원, 직원CTE

WHERE 직원.관리자 = 직원CTE.이름) -- Where절의 결과가 있으면 이 SFW 재귀적으로 호출

)

SELECT * FROM 직원CTE ORDER BY 부서, 직급;

 

-- 다른 버전(보기 편한 다른 버전)

WITH 직원CTE(이름, 직책, 부서, 관리자, 직급)

AS

(

( SELECT 이름, 직책, 부서, 관리자, 0

FROM 직원

WHERE 관리자 IS NULL ) -- JDK는 상관이 없음. 직급 0

UNION ALL

(SELECT 직원.이름, 직원.직책, 직원.부서, 직원.관리자, 직원CTE.직급+1

FROM 직원, 직원CTE

WHERE 직원.관리자 = 직원CTE.이름) -- select의 결과가 있으면 이 SFW 재귀적으로 호출

)

SELECT CONCAT(RPAD(' -', 직급 + 1, '-'), 이름) , 부서

FROM 직원CTE ORDER BY 부서, 직급;

 

 

-- 특정 직급(부장급) 이상만 출력

WITH 직원CTE(이름, 직책, 부서, 관리자, 직급)

AS

(

( SELECT 이름, 직책, 부서, 관리자, 0

FROM 직원

WHERE 관리자 IS NULL ) -- JDK는 상관이 없음. 직급 0

UNION ALL

(SELECT 직원.이름, 직원.직책, 직원.부서, 직원.관리자, 직원CTE.직급+1

FROM 직원, 직원CTE

WHERE 직원.관리자 = 직원CTE.이름 and 직급 <=1 )

)

SELECT CONCAT(RPAD(' -', 직급 + 1, '-'), 이름) , 부서

FROM 직원CTE ORDER BY 부서, 직급;

########################

<Sequence와 Delete, Drop, Truncate>

Sequence : 일련번호를 만드는 객체를 따로 만드는 것

(생성) CREATE SEQUENCE 일련번호

START WITH 1

INCREMENT BY 1; ==> 1부터 시작해서 1씩 증가한다

(삭제) DROP SEQUENCE 일련번호;

(사용) SELECT 일련번호.NEXTVAL FROM DUAL; ==> Sequence 값 증가

SELECT 일련번호.CURRVAL FROM DUAL; ==> Sequence 값 조회

-- Sequence를 만들고증가 후 조회를 해야지, 조회먼저 해버리면 안됨

DUAL : 함수에 대한 쓰임을 알고 싶을때 특정 테이블을 생성할 필요없이 dual 테이블을 이용 하여 함수의 값을 리턴(return)받을 수 있다.

(변경) ALTER SEQUENCE 일련번호 INCREMENT BY 5;

(복합 옵션) CREATE SEQUENCE 반복일련번호

START WITH 10

INCREMENT BY 2

MINVALUE 10 -- 최소값

MAXVALUE 20 -- 최대값

CYCLE -- 반복설정

NOCACHE ; -- 캐시 사용 안함

 

SELECT level AS 일련번호 FROM DUAL CONNECT BY level <= 5

==> level을 쓰고 CONNECT BY level <=n를 하면 1~n까지의 일련번호를 생성

 

DELETE FROM 대용량1; -- 10초 소요

ROLLBACK; -- 9초 소요

SELECT * FROM 대용량1; -- 복구됨

 

DELETE FROM 대용량2;

COMMIT;

SELECT * FROM 대용량2;

 

DROP TABLE 대용량3; -- 0.06초 소요

ROLLBACK; -- DROP은 DDL이라 ROLLBACK 안됨

SELECT * FROM 대용량3; -- 오류

 

TRUNCATE TABLE 대용량4; -- 0.07초 소요

ROLLBACK; -- TRUNCATE(DDL)도 RollBack의 대상이 안됨

SELECT * FROM 대용량4; -- 대용량4 테이블(빈테이블)은 남아 있음

# TRUNCATE : 테이블을 최초 생성된 초기상태로 만든다

 

<Merge>

원 테이블을 남겨놓고 데이터 관리

변경 테이블을 누적했다가 주기적으로 Merge하고자 함 (삽입, 삭제, 변경이 일어남)

 

MERGE INTO [TABLE / VIEW]- update 또는 insert할 테이블 혹은 뷰

USING [TABLE / VIEW / DUAL] - 비교할 대상 테이블 혹은 뷰

ON [조건] - (조건이 일치하면 UPDATE / 불일치 시 INSERT)

WHEN MATCHED THEN

UPDATE SET

[COLUMN1] = [VALUE1],

[COLUMN2] = [VALUE2],

...

WHEN NOT MATCHED THEN

INSERT(COLUMN1, COLUMN2, ...)

VALUES(VALUE1, VALUE2, ...)

 

MERGE INTO 고객관리 소스

USING (SELECT 내역, 고객아이디, 고객이름, 적립금 FROM 정보변경) 변경

ON (소스.고객아이디 = 변경.고객아이디)

WHEN MATCHED THEN ==> 데이터가 있는 경우

UPDATE SET 소스.적립금 = 변경.적립금 -- CASE1 : 변경(Update)

DELETE WHERE 변경.내역 = '탈퇴' -- CASE2 : 탈퇴(Delete)

WHEN NOT MATCHED THEN ==> 데이터x -- CASE3 : 신규(Insert)

INSERT (고객아이디, 고객이름, 적립금)

VALUES(변경.고객아이디, 변경.고객이름, 변경.적립금);

==> 고객관리 : 원 테이블, 변경 테이블 : 소스

 

<조인>

# 내부조인

select 주문제품, 주문일자

from 고객, 주문

where 고객.고객아이디 = 주문.주문고객 and 고객이름 = '정소화';

 

select 주문제품, 주문일자

from 고객 JOIN 주문 on 고객.고객아이디 = 주문.주문고객

where 고객이름 = '정소화';

 

select 주문제품, 주문일자

from 고객 INNER JOIN 주문 on 고객.고객아이디 = 주문.주문고객

where 고객이름 = '정소화';

 

3개 모두 같은 의미

 

# 외부조인 : 조인의 조건에 만족되지 않는 행도 포함

LEFT OUTER JOIN : 조인문의 왼쪽에 있는 테이블의 모든 결과를 가져 온 후 오른쪽 테이블의 데이터를 매칭하고, 매칭되는 데이터가 없는 경우 NULL로 표시한다.

 

RIGHT OUTER JOIN : 조인문의 오른쪽에 있는 테이블의 모든 결과를 가져온 후 왼쪽의 테이블의 데이터를 매칭하고, 매칭되는 데이터가 없는 경우 NULL을 표시한다.

 

FULL OUTER JOIN : LEFT OUTER JOIN 과 RIGHT OUTER JOIN을 합친 것으로, 양쪽 모두 조건이 일치하지 않는 것까지 모두 결합해 출력한다

 

-- LEFT OUTER JOIN : 주문하지 않은 고객 정보도 출력. 주문 정보는 NULL

SELECT *

FROM 고객 LEFT OUTER JOIN 주문 on 고객.고객아이디 = 주문.주문고객;

 

-- RIGHT OUTER JOIN : 비회원 주문 정보도 출력. 회원 정보는 NULL

SELECT *

FROM 고객 RIGHT OUTER JOIN 주문 on 고객.고객아이디 = 주문.주문고객;

 

-- FULL OUTER JOIN : 주문하지않은 고객, 비회원 주문 정보 모두 표현

SELECT *

FROM 고객 FULL OUTER JOIN 주문 on 고객.고객아이디 = 주문.주문고객;