220125 oracle: sql함수_형변환, 그룹함수, 조인

3. SQL 함수

 * 형 변환 함수
    - to_char() : 문자형으로 변환
      01. 날짜형 -> 문자형
        select TO_CHAR(sysdate, 'YYYY-MM-DD (DY) HH24:MI:SS') from dual;
        ---> 날짜를 어떠한 형식으로 출력하고 싶을 때 사용
               참고) DAY: 월요일 DY: 월   //   HH12, HH24: 시 MI: 분 SS: 초

      02. 숫자형 -> 문자형
        select TO_CHAR(sal, 'L000,000'),TO_CHAR(sal, 'L999,999') from emp;
          - 출력 형식
             0: 자릿수가 맞지 않을 경우 0으로 채움 (반드시 모든 자리를 채워야 할 때 사용)
             9: 자릿수가 맞지 않아도 채우지 않음 (그냥 어떤 형식에 맞춰 숫자를 기입하고 싶을 때 사용)
             L: 지역 통화 기호 (앞이나 뒤에 붙여 표시)

    - to_date() : 날짜형으로 변환
        select trunc(sysdate-TO_DATE(19810220,'YYYYMMDD')) from dual;
        ---> 문자나 숫자를 날짜로 변환해 검색하거나 연산해야 할 때 사용
        ---> 날짜 형식은 써주는 게 원칙이지만 내부적으로 자동 변환되기도 함

    - to_number() : 숫자형으로 변환
        select TO_NUMBER('10,000','99,999')-sal from emp;
        ---> 문자형을 연산해야 할 때 사용


 ※ null★
      : 알 수 없는(unknown), 정해지지 않은 어떤 값
      : 존재하지 않는 게 아니라서 연산, 할당, 비교 불가능

 * NVL(column_name, N) --> (컬럼명)에 존재하는 null 값을 N으로 변환
        select ename, sal, nvl(comm,0), sal*12+nvl(comm,0) from emp;
        select MGR, nvl(to_char(mgr),'CEO') from emp;
        ---> 컬럼과 변환시킬 값의 데이터형이 일치해야 함

 * decode(): switch case문 (특정 조건과 일치할 경우 그 결과 출력)
      decode(컬럼명, 조건1, 결과1,
                         조건2, 결과2,
                         조건3, 결과3,
                         기본 결과 n )
        ex. deptno를 부서명으로 바꿔 출력

 * case : if else문 (비교연산자로 조건 범위 지정 가능)
      case when 조건1 then 결과1
             when 조건2 then 결과2
             when 조건3 then 결과3
             else 결과n 
      end    //// 괄호, 반점 쓸 필요 없음! 마지막에 end 꼭 써줘야 함!
        ex. deptno를 부서명으로 바꿔 출력

4. 그룹 함수
   : 하나 이상의 행을 그룹으로 묶어 연산해 '하나의 결과'(총합, 평균 등)로 나타냄
     ---> 즉 결과가 하나 이상인 다른 컬럼과는 row 개수가 다르기 때문에 함께 출력할 수 없음

 * 함수
    - sum(): 총합
        select sum(sal) from emp;
        select sum(comm) from emp; -- null 값 제외하고 계산됨
        select sum(sal), sum(comm) from emp; -----그룹함수끼리는 함께 사용 가능
        select sal, sum(sal) from emp; ----- 서로 row가 다르면 출력 불가- 오류 발생
        select sum(sal) from emp where deptno=10; --조건에 해당하는 row만 계산 가능

    - avg(): 평균
        select avg(sal) from emp;
        select avg(comm) from emp; -- null 값 제외하고 계산됨
        select avg(sal), sum(sal) from emp; -----그룹함수끼리는 함께 사용 가능
        select avg, avg(sal) from emp; ----- 서로 row가 다르면 출력 불가- 오류 발생
        select avgm(sal) from emp where deptno=10; --조건에 해당하는 row만 계산 가능

    - max(): 최대값  /  min(): 최소값
        select max(sal), min(sal) from emp;
        select max(hiredate) "가장 최근 입사" min(hiredate) "가장 예전 입사" from emp; --날짜 가능
        select max(ename) "이름 가장 뒤" min(ename) "이름 가장 앞" from emp; --문자 가능

    - count(): 조건을 만족하는 row의 개수를 반환
        select count(comm) from emp; --커미션을 받는 사원의 수를 count. null값은 제외한다
        select count(*) from emp; --emp 테이블의 전체 row 수
        select count(distinct job) from emp; --중복 제외한 row 수

 * group by
    : 어떤 컬럼을 기준으로 그룹 단위의 함수값을 구할 때 사용
      ---> 기준이 된 컬럼(group by에 쓰인 컬럼)은 그룹함수와 함께 사용할 수 있음
        select deptno from emp group by deptno; -- 부서 번호 기준으로 묶음 (10,20,30)
        select deptno, avg(sal) from emp group by deptno; -- 부서 번호마다 평균을 구함
        select deptno, max(sal), min(sal) from emp group by deptno; --부서 번호 기준으로 최대,최소 급여
        select deptno, count(*) from emp group by deptno; --부서 번호마다 사원수(=row)가 몇명인지
      ex. job을 기준으로 sal-합, 평, 최대, 최소 구하기
        select job, sum(sal), avg(sal), max(sal), min(sal) from emp group by job;

 * having 조건
    : 그룹의 결과를 제한할 때 사용    // where은 select 조건 제한이고.. 그룹은 having 써야함
        select deptno, avg(sal) from emp group by deptno HAVING avg(sal)>=2000;
        -- deptno를 기준으로 평균을 구하는데, '평균이 2000 이상인 조건으로 제한'해 결과를 출력
        select deptno, max(sal) from emp group by deptno HAVING max(sal)>=2900;
        -- deptno를 기준으로, '최대급여가 2900 이상인 조건으로 제한'해 최대급여 결과를 출력


5. 조인 Join
   : 데이터가 두 개 이상의 테이블에 나눠져 있을 때, 테이블을 결합해 한 번의 질의로 원하는 결과를 얻는 기능

 * Cross join
    : 특별한 키워드 없이 테이블을 콤마(,)로 연결해 연속하여 기술
      A+B 컬럼의 수: A 컬럼 + B 칼럼      A+B row의 수: A row * B row   (이거는 정처기 문제로도 나오더라)

   01. Equi join 등가 조인★
      : 동일 컬럼을 기준으로 조인
        select * from emp, dept where emp.deptno=dept.deptno; --deptno를 기준으로 두 테이블을 조인
        select ename, dname from emp, dept where emp.deptno=dept.deptno and ename='SCOTT';
        -- 두 테이블을 조인해서, 서로 다른 테이블의 컬럼을 하나의 질의어로 출력 (SCOTT의 부서명)
        select emp.dept from emp, dept where emp.deptno=dept.deptno and ename='SCOTT';
        --기준이 된 컬럼은 조인하면 두 개가 되기 때문에(emp.deptno,dept.deptno) 어느 테이블의 컬럼인지 명시해야함야 함!

    ※ 테이블 별칭
        select d.deptno, ename, dname from emp E, dept D where E.deptno=D.deptno;
        -- from 테이블에 별칭 부여
            -> 이후 테이블명 대신 별칭으로 테이블을 구분해야 함 (테이블명 사용 불가)
            -> 별칭명 대소문자 구분 안 함 (d.deptno=D.deptno)
            -> 공통 컬럼(deptno)는 별칭명.컬럼명 형식으로 사용해야 함 (테이블명 사용 불가!!!)
            -> 공통 컬럼이 아닌 것은 테이블명 생략하듯 별칭명 생략 가능

   02. Non-Equi join 비등가 조인
      : 동일 칼럼 없이 다른 조건을 사용해 조인
        select ename, sal, grade from emp, salgrade where sal between losal and hisal;
        -- 공통 컬럼 없는 두 테이블 조인 후 salgrade의 범위를 조건으로 sal이 속하는 row와 연결함
        -- 예를 들어 sal=800이라면 salgrade의 losal=700과 hisal=1200 사이의 범위에 위치하므로 이 범위에 등치되는 grade 컬럼의 값(=1)이 해당 sal=800인 row와 조인되는 방식

   03. Self join
      : 한 테이블 내에서 조인(=자기 자신과 조인)
        select empl.ename, mger.ename from emp empl, emp mger where empl.mgr=mger.empno;
        -- 같은 테이블을 두 별칭으로 분리해 조인한 뒤 각 별칭의 컬럼을 등치시켜 연결함
        -- empl의 mgr(상사번호)와 mger의 empno(사원번호)를 연결 -> empl의 상사명이 등치되어 조인됨
        -- KING의 경우 mgr이 null이므로 등치되는 값이 없음 -> 제외하고 출력됨

   04. Outer join외부 조인
      : 조건에 만족하지 않는 행도 출력          -> 개념 확실히 숙지하기
        -- 테이블을 조인할 때, 한 쪽의 테이블에는 데이터가 존재하지만 다른 테이블에는 존재하지 않는 경우, 없으면 없는대로 그대로 출력시킴
        -- ★등치되는 값이 존재하지 않는 테이블 쪽에 (+) !! 헷갈리지 않기!
      ex. 예를 들어 위의 self join에서 mgr가 null인 KING은 제외하고 출력되었는데 이 row도 그냥 출력시키려면
        select empl.ename, mger.ename from emp empl, emp mger where empl.mgr=mger.empno(+);
        -- empl의 KING의 상사번호(null)에 등치되는 mger의 사원명(ename)이 존재하지 않음-> mger에 (+) 붙임 
        -- empl.mgr이 아닌 이유: !null은 존재하지만 알 수 없는 값! 즉 null과 등치되는 상사명이 없는 것이기 때문
      ex. dept 테이블의 40번 부서는 emp 테이블의 부서번호와 등치되는 값이 없어 출력되지 않는데 이때 40번 부서명도 출력하려면
        select ename, d.deptno, dname from dept d, emp e where d.deptno=e.deptno order by deptno;
        -- dept의 40번 부서에 등치되는 emp의 부서번호(deptno)가 존재하지 않음-> emp에 (+)를 붙임

 * ANSI Join
    : ANSI(미국 표준 협회) 표준안에 따라서 만들어진 join 방법

    - ANSI cross join
        select * from dept cross join emp; --4*14=56개 row 검색
        select * from emp cross join dept; --14*4=56개 row 검색
        -- (,) 대신 cross로 조인

    - ANSI inner join
        select * from emp INNER JOIN dept ON emp.deptno=dept.deptno WHERE ename='SCOTT';
        -- INNER JOIN으로 emp와 dept 테이블을 조인
        -- ON으로 조인 조건(emp.deptno=dept.deptno) 명시
        -- WHERE에 조인과 연관 없는 기타 조건(ename='SCOTT') 지정
      +) USING(): ON 조인 조건을 간결하게 표현
        select * from emp INNER JOIN dept USING(deptno) WHERE ename='SCOTT';
        -- ON 대신 USING 사용 -> 괄호 안의 컬럼명을 공통조건으로 자동 인식
        -- Equi join일 때 사용할 수 있음
      +) NATURAL join: 동일 컬럼이 존재하는 경우, 테이블 조인 시 INNER JOIN ~ ON 간결하게 표현 가능
        select * from emp NATURALN JOIN dept WHERE ename='SCOTT';
        -- from 뒤 테이블 조인 시 INNER JOIN 대신 사용
        -- 굳이 'ON 조인 조건' 명시하지 않아도 자동으로 공통 조건 지정됨