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 고객.고객아이디 = 주문.주문고객;