Day22

키워드 : 자료형 변환함수 / 조건함수 / 그룹핑 / 조인 / 정규식 표현

 

****

 

 

 

1. 자료형 변환함수

- TO_CHAR(컬럼이름, 출력형태)

  (+) EXTRACT(출력정보 FROM 컬럼이름)=비교값;

- TO_DATE

- TO_NUMBER

 

입사일자에서 입사년도를 출력

to_char(hiredate, 'YYYY') AS hireyear

 

입사일자를 '1999년 1월 1일' 형식으로 출력

to_char(hiredate, 'YYYY"년" MM"월" DD"일"')

 

급여 앞에 $를 삽입하고 3자리 마다 ,를 출력

to_char(sal, '$999,999,999')

 

1981년도에 입사한 사원 검색

to_char(hiredate, 'YYYY')=1981

EXTRACT(YEAR FROM hiredate)=1981

 

5월에 입사한 사원 검색

to_char(hiredate, 'MM')=05

EXTRACT(MONTH FROM hiredate)=05

 

1981년에 입사하지 않은 사원 검색

NOT (to_char(hiredate, 'YYYY')=1981)

 

1981년 하반기에 입사한 사원 검색

to_char(hiredate, 'YYYY')=1981 AND EXTRACT(MONTH FROM hiredate)>6

to_char(hiredate, 'YYMM')>8106 AND to_char(hiredate, 'YYMM')<8201

 

 

2. 조건함수 

DECODE ( expr, search 1, result1, search2, result2, ... , [default] )

CASE expr WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... ELSE default END

주민번호에서 성별 구하여 출력하기

 

부서번호가 10이면 영업부, 20이면 관리부, 30이면 IT부 그 외는 기술부로 출력

 

업무(job)이 analyst이면 급여 증가가 10%이고 clerk이면 15%,

manager이면 20%인 경우 사원번호, 사원명, 업무, 급여, 증가한 급여를 출력

 

3. 조건함수 연습문제

-- 1. 사원번호, 이름, 업무, 급여,  현재 급여에 15% 증가된 급여를(New Salary),  증가액(Increase)를 출력

SELECT empno, ename, job, sal, sal*1.15 AS new_salary, sal*0.15 AS increase FROM emp;

-- 2. 이름, 입사일, 입사일로부터 6개월 후에 돌아오는 월요일을 출력

SELECT ename, hiredate, NEXT_DAY(ADD_MONTHS(hiredate,6), '월') AS "6개월후MON" FROM emp;
-- 3.이름, 입사일, 입사일로부터 현재까지의  년수, 급여, 입사일로부터 현재까지의 급여의 총계를 출력
SELECT ename, hiredate, TRUNC((sysdate-hiredate)/365,0) AS years, sal, 
    to_char(TRUNC(((sysdate-hiredate)/12)*sal,0),'999,999,999') AS total_sal FROM emp;
SELECT ename, hiredate, EXTRACT(YEAR FROM sysdate)-EXTRACT(YEAR FROM hiredate) AS years, sal,
    to_char(TRUNC(((sysdate-hiredate)/12)*sal,0),'999,999,999') AS total_sal FROM emp;
-- 4.이름, 업무, 입사일, 입사한 요일을 출력
SELECT ename, job, hiredate, to_char(hiredate, 'day') AS first_day FROM emp;
-- 5. 모든 사원의 이름과 급여를 출력 ( 급여는 15자리로 좌측의 빈곳에 '*'로 대치 )
SELECT ename, LPAD(sal, 15, '*') AS sal FROM emp;
-- 6. 다음의 결과처럼 출력
    -- KING earns $5,000.00 monthly but wants $15,000.00
    -- BLAKE earns $2,850.00 monthly but wants $8,550.00
    -- CLARK earns $2,450.00 monthly but wants $7,350.00
SELECT (ename||' '||'earns '||TRIM(to_char(sal, '$999,999,999.99')
    ||' monthly but wants ')||' '||TRIM(to_char(sal*3, '$999,999.99'))) AS result FROM emp;

 

 

4. 그룹핑

    (1) ALL, DISTINCT

       select ALL job from emp;

       select DISTINCT job from emp;

   (#) 함수 인자에 distinct를 이용하면 중복되지 않는 값으로 계산

-- rownum : 가상컬럼, 실제로 존재하진 않으나 조건을 만드는 용도로 사용할 수 있다.
-- rownum에 의한 정렬 순서는 확정이 아니라서 어느 날 변경될 수도 있다.

 

    (2) 집계 함수

AVG	평균
COUNT	행의 갯수
SUM	합계
MIN / MAX	최소값, 최대값
VARIANCE	분산값
STDDEV	표준편차값

 

 

 

    (3) 데이터 그룹

※ 출력 컬럼은 그룹핑하는 컬럼과 집계함수만 들어갈 수 있다.

SELECT columns FROM table_name WHERE condition GROUP BY group_by_expression HAVING condition ORDER BY column;

[ GROUP BY 절에 사용하는 함수 ]

- ROLLUP : 결과에 그룹별 합계 정보를 추가

- CUBE : 그룹핑 된 컬럼의 합계 정보를 추가

 

(+) WHERE절에는 집계함수가 들어갈 수 없기 때문에, HAVING절에서 처리한다. 

 

select job, sum( sal ) from emp group by job;

select job, sum( sal ) from emp group by rollup(job);

select job, sum( sal ) from emp group by cube(job);

 

 

5. 조인

 : 하나 이상의 테이블로부터 데이터를 검색하고자 할 때 사용한다. 

(1) 두 테이블만 기술 => Cartesian Product

select ename, dname from emp, dept;

emp 행수 : 14 dept 행수 : 4 결과 : 14 * 4개의 행수로 출력

 

(2) 두 테이블 연결 => 조인 ( 내부조인 )

select ename, dname from emp, dept where emp.deptno = dept.deptno;

결과 : 14행

(1) 부서번호로 검색하고자 한다면 두 테이블에 있기에 어느 테이블에서 가져올지 지정

(2) 매 컬럼마다 테이블이름 지정 대신 테이블 alias로 대신

* 자바에서 불러올 때 단순한 컬럼명으로만 부르기에 alias 지정 필수 !

(3) 부서 없이 본인 데이터를 하나 입력한다. (deptno 없이 emp 테이블에 입력)

* 다시 dept 테이블과 조인하여 확인 -> 입력된 데이터 출력 안됨.

 

(3) 외부조인

없는 데이터를 포함하여 조인

select e.ename as ename, d.dname as dname, e.deptno as deptno from emp e, dept d where e.deptno = d.deptno(+);

결과 : 15행

* 외부조인 : 한 테이블에 공통 컬럼값이 없더라도 (null이라도) 검색

* (+) : 해당 데이터가 존재하지 않더라도 이를 무시하고 조인에 참여

 

 

[ 외부조인 특징 ]

- 세 개의 테이블 조인시 한 조건이라도 외부조인이면 다른 조인에도 (+)를 붙여야함

- (+) 컬럼에만 붙이고, OR 연산자와 사용 못함

- 두 테이블에 모두 (+)를 붙일 수 없음

- (+)이 붙은 컬럼과 IN 연산자, 서브쿼리를 같이 사용 못함

 

 

(4) ANSI 표준안

select e.ename, d.dname, d.deptno from emp e, dept d where e.deptno = d.deptno;

select e.ename, d.dname, d.deptno from emp e INNER JOIN dept d ON e.deptno = d.deptno;

* INNER JOIN

select e.ename, d.dname, d.deptno from emp e, dept d where e.deptno = d.deptno;

select e.ename, d.dname, deptno from emp e INNER JOIN dept d USING (deptno);

* USING을 사용하면 deptno 검색시 테이블명을 지정하면 안되고 컬럼명만 기술.

select e.ename, d.dname, deptno from emp e Natural JOIN dept d;

* Natural join

select e.ename, d.dname, d.deptno from emp e, dept d where e.deptno = d.deptno(+);	select e.ename, d.dname, d.deptno from emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;
select e.ename, d.dname, d.deptno from emp e, dept d where e.deptno(+) = d.deptno;	select e.ename, d.dname, d.deptno from emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;

* 기존문법에서 (+)가 데이터가 없는 테이블에 붙였다면,

LEFT나 RIGHT는 반대로 데이터가 있는 테이블에 붙인다.

* 기존 문법에서는 양 쪽에 (+)에 붙일 수 없는데, FULL OUTER JOIN으로 가능.

 

 

 

(+) 셀프조인

사원테이블(emp)에 각 사원의 매니저를 조회

select e.empno, e.ename, e.mgr, e2.ename

from emp e, emp e2

where e.mgr = e2.empno(+);

 

 

(+) 집합(SET)

UNION	합집합
UNION ALL	합집합에 중복되는 교집합 두 번 출력
INTERSECT	교집합
MINUS	차집합

* select 컬럼수과 반드시 동일

` 업무가 CLERK인 사원조회

` 10번 부서의 사원조회

 

SELECT empno, ename, job, deptno FROM emp WHERE job=’CLERK’

[ ]

SELECT empno, ename, job, deptno FROM emp WHERE deptno=10;

 

 

 

6. 조인 연습문제

 

 

7. 복습 문제 (hr계정 employees 테이블)

 

8. 정규식 표현 [과제]

REGEXP_LIKE : 정규식에 해당되는 문자열을 검색할 수 있음

REGEXP_REPALCE : 문자열 대체의 확장판

REGEXP_INSTR : 위치를 찾는 함수

REGEXP_SUBSTR : 특정 문자열을 꺼내올 때 정규식을 사용할 수 있음

REGEXP_COUNT : 특정 문자의 갯수를 세는 함수

 

kutar37.tistory.com/entry/oracle-REGEXPLIKE

[oracle] 정규식을 사용하는 REGEXP 함수

jhnyang.tistory.com/127

[리눅스 유닉스] 정규표현식 (Regular Expression) 메타문자 정리. vi편집기에서 정규표현식 응용해 검