728x90
- 종류
- nvl
- decode
- case
- pivot : 보기 좋게 table 형태
- rollup: 1차원
- cube: 다차원
- rank
- correlated subquery : main queary(1) -> sub queary (2) * main queary에 별명 꼭 필요
- multi row 연산자 : in, not in, any, exists, not exists
- pseudo column : rowid, rownum(무조건 1부터)
- index 효율적 사용
- trigger 자동 실행 프로그램
- nvl
1. nvl
- null 값을 포함하는 컬럼을 지정된 값으로 변경하는데 사용
--nvl 사용법
SELECT nvl(name2, '0')
from MYTABLE m ;
--nvl2 사용법
SELECT NVL2(name, '01', '23') -- 값 존재시 01, null 일 시 23
FROM MYTABLE m ;
2.decode
- 등급 나누기
-- 사용법 switch문과동일
SELECT name, salary, DECODE(TRUNC(salary/1000),0,'e',1,'d',2,'c',3,'b','a')
from S_EMP se ;
3. case문
- 등급 나누기
-- case문 case, when then , else, end
SELECT id, name,
case salary
when 1000 then 'low'
WHEN 5000 then 'high'
ELSE 'medium'
END
from S_EMP se ;
-- case문 salary가 when절 안에 존재
SELECT id, name,
case
when salary < 1000 then 'very low'
WHEN SALARY >=1000 and SALARY <=5000 then 'low'
WHEN salary > 5000 then 'high'
ELSE 'medium'
END
from S_EMP se ;
4. pivot함수
- 보기 편리한 표 제공
SELECT *
from (select dept_id, title from s_emp)
pivot(
count(*) -- 수행할 작업
for title in ('사원','과장','부장','이사','사장') -- 피벗할 column
)
order by DEPT_ID
;
-- 비교
select dept_id, title from s_emp;
5. Rollup 함수
- 중간 합계, 최종 합계 제공 - 현재는 count여서 합계를 제공하는 것. 다른 작업도 가능
SELECT dept_id, title, count(*)
from S_EMP se
WHERE DEPT_ID in (106, 112, 113)
group by rollup(DEPT_ID, TITLE)
order by DEPT_ID
;
6. cube 함수
- 다차원 합계 제공
- 부서별 합계, title별 합계, 총합계
SELECT dept_id, title, count(*)
from S_EMP se
WHERE DEPT_ID in (106, 112, 113)
group by cube (DEPT_ID, TITLE)
order by DEPT_ID
;
7. rank 함수
- 행별 순위 계산
SELECT id, name, salary,
RANK() over (order by salary desc) as RANK -- rank라는 column이 추가
from S_EMP se
;- 그룹별 순위 계산
SELECT id, name, salary,
RANK() over (
PARTITION by DEPT_ID -- partition으로 group을 나눠버림
order by salary desc) as RANK -- rank라는 column이 추가
from S_EMP se
;
8. 상관 함수 (correlated subquery)
- main queary 닉네임 필수
-- 자신의 급여가 자신이 속한 부서의 평균 급여보다 적은 경우 출력
SELECT name, salary, DEPT_ID
from S_EMP outer
WHERE SALARY < (select AVG(SALARY)
from S_EMP se
WHERE DEPT_ID = outer.dept_id)
;
9. multi row 연산자
- 종류
- in
- not in
- any
- all
- exists
- not exists
- 기본적으로 subquery는 나의 정보를 어떤 table에서 무언가와 비교해서 얻어낼 때 사용
-- 본인의 급여가 각 부서별 평균 급여중 어느한 부서의 평균 급여보다 적은 경우, 그사람 정보 출력하기
SELECT NAME , SALARY , DEPT_ID
FRom S_EMP se
WHERE se.SALARY < ANY (SELECT AVG(SALARY) from S_EMP se2 group by DEPT_ID);
-- 본인이 다른 사람의 관리자로 되어 있는 경우, 정보 출력
SELECT name, title, dept_id
from S_EMP se
WHERE EXISTS (select id from S_EMP se2 WHERE MANAGER_ID = se.ID)
10. pseudo column
- rowid, rownum 으로 구성
- rowid: 고유 num
- rownum: 1번 부터 순차적으로 붙어지는 값
-- rowNum은 정렬 후 생성되는 것이 아니라, 먼저 생성된 후 우리가 정렬하는 것이므로
-- subquery로 정렬하고 나서 거기에 rowNUm을 붙일 수 있도록 해야한다.
SELECT name
FROM (select * from S_EMP se order by name) name
WHERE rownum < 6
;
-- 잘못된 결과
SELECT se.NAME
FROM S_EMP se
order by se.NAME
;
-- 월급(s_emp.salary)이 상위 6위에서 10위 5명의 이름(s_emp.name)과 월급(s_emp.salary)을 나타내시오.
select inTable.name, inTable.salary
from (select rownum rn, name, SALARY
from (select * from S_EMP se order by salary DESC) inin order by inin.salary DESC) inTable
where inTable.rn BETWEEN 6 and 10
;
-- rownum을 통한 나열 먼저하기
select rownum rn, name, SALARY from (select * from S_EMP se order by salary DESC) inin order by inin.salary DESC ;
11. index 활용하기
- index가 사용안되는 조건이 존재
- index column 비교전 변형 일어나는 경우
- 부정 조건 기술
- null로 비교할 경우 - index는 null을 저장하지 않음
-- 컬럼 변형
select id, name, TITLE from s_emp e
where SALARY*12 = 24000;
;
-- 컬럼 변형 안쓰는 방법 - index 사용
select id, name, TITLE from s_emp e
where SALARY = 24000/12;
;
-- 부정
select id, name, TITLE from s_emp e
where title <> '사원'
;
-- 부정 안쓰는 방법 - index 사용
select id, name, TITLE from s_emp e
where not exists (SELECT 'x' from S_EMP se WHERE e.title = '사원')
;
12. trigger
- 사용자가 부르는 것이 아니라 임의의 테이블에 특정한 dml 문이 수행 되었을 때, db에서 자동으로 동작하는 프로그램
예제 찾아봐야될 듯