오라클 날짜

connect hr

SQL> select sysdate from dual;

SQL> ALTER SESSION SET nls_date_format='yyyy/mm/dd hh24:mi:ss'; //시간 표시 형식 변경 

SESSION : 한번 커넥션한 단위, 커넥션 단위 라고 함.
ALTER SESSION SET: sqlplus나 일부 오라클 툴에서만 사용가능.

SQL> select last_name , hire_date from employees; //시분초가 표시되지 않음

SQL> select sysdate, sysdate+2/24 from dual; //시간을 컨트롤 할때는 /24 를 통해서 시간임을 표시 해줘야 함. 시간단위계산

SQL> select sysdate, sysdate+20/(24*60) from dual // 분단위로 환산 해서 계산 

SQL> select last_name, sysdate-hire_date from employees //  입사날짜 구하기 오늘 날짜 - 입사날짜 

SQL> select last_name, trunc(sysdate-hire_date,0) from employees // trunc를 이용해서 0단위 이하 절삭

SQL> select last_name, trunc((sysdate-hire_date)/7 ,0) AS WEEKS from employees // 주단위 계산

SQL> SELECT last_name, MONTHS_BETWEEN(sysdate, hire_date) months FROM employees; //월단위계산 

SQL> SELECT sysdate, ADD_MONTHS(sysdate, 6) REVIEW , NEXT_DAY(sysdate, 'FRIDAY'), LAST_DAY(sysdate) FROM dual; // 달계산, 6개월 후 , 다음 금요일, 마지막날 

SQL> ALTER SESSION SET nls_date_format='yyy/mm/dd Day hh24:mi:ss'; // 표시 형식 변경 

SQL> SELECT ROUND(sysdate, 'MONTH') FROM dual; // 다음달

SQL> SELECT ROUND(sysdate, 'DAY') FROM dual; // 다음주

SQL> SELECT ROUND(sysdate, 'DD') FROM dual; //다음 날 

SQL> SELECT ROUND(sysdate, 'YEAR') FROM dual; // 다음년

ROUND를 TRUNC로 바꾸면 절삭 해서 이전 단위의 시간 으로 변경.

SQL> SELECT TRUNC(sysdate, 'MONTH') FROM dual; 


로그아웃해서 sqlplus hr 재접속 ( alter session set 했던것을 없앰)
SQL> select last_name, hire_date ,salary from employees; // 원래의 포멧으로 돌아옴.
SQL> select last_name, TO_CHAR(hire_date,'yyyy/mm/dd hh24:mi:ss') As hiredate ,salary from employees // 표시형식 포멧 변경 및 문자로 변경
SQL> SELECT last_name, TO_CHAR(salary, '$999,000') from employees;  //단위 표시 형식 및 자리 수
SQL> SELECT last_name, TO_CHAR(salary, '$099,000') from employees // 자리수 고정 0으로 채워줌 

형변환
SQL> SELECT last_name,LPAD(salary,10,'*') FROM employees; // 표시 형식 변경 , 문자료 변경 됨.TO_CHAR을 안써줘도 자동으로 문자로 변경해서 LAPD를 적용 암시적 형변환 (내생각 : 자동형변환 )

형식지정
SQL> SELECT sysdate,TO_CHAR(sysdate,'yyyy/Mon/dd hh24:mi:ss') from dual ; 
SQL> SELECT sysdate,TO_CHAR(sysdate,'yy/Mon/dd hh24:mi:ss') from dual ; //년도 두자리
SQL> SELECT sysdate,TO_CHAR(sysdate,'fmyy/Mon/dd hh24:mi:ss') from dual ; // 선행 0 및 공백을 제거
SQL> SELECT sysdate,TO_CHAR(sysdate,'yyyy/Mon/dd hh12:mi:ss PM') from dual ;  // 오전 오후 표시 
SQL>  SELECT sysdate,TO_CHAR(sysdate,'fmyy/Mon/dd hh24:mi:ss Q') from dual ; // 분기표시
SQL>  SELECT sysdate,TO_CHAR(sysdate,'fmyy/Mon/dd hh24:mi:ss w') from dual ; // 주 표시 9월의 3주차 
SQL>  SELECT sysdate,TO_CHAR(sysdate,'fmyy/Mon/dd hh24:mi:ss ww') from dual ; // 주 표시 올해의 38주차 
SQL>  SELECT sysdate,TO_CHAR(sysdate,'Year/Mon/dd hh24:mi:ss ww') from dual ; // year가 스펠링으로 표시
SQL>  SELECT sysdate,TO_CHAR(sysdate,'Year/Mon/ddsp hh24:mi:ss ww') from dual ; // day가 스펠링으로 표시
SQL> SELECT last_name,TO_CHAR(hire_date, 'YY/MM') from employees;  //입사직원 월날짜 표시
SQL> SELECT last_name,TO_CHAR(hire_date, 'Q') from employees // 직원입사 분기 표시
SQL> select last_name,TO_CHAR(salary,'$999,999.99') from employees ; // 돈 단위 표시
SQL> select last_name,TO_CHAR(salary,'L999,999.99') from employees // 시스템 디폴트 돈 단위

기본 랭귀지 변경. 
SQL> ALTER SESSION SET nls_territory='KOREA' ;
SQL> select last_name,TO_CHAR(salary, 'L999,999') from employees; // 통화가 \으로 바뀌어서 보여준다.
SQL> select last_name,salary from employees where salary = TO_NUMBER('$8,300','$99,999') // TO_NUMBER 형식을 줘서 형식에 맞게 숫자로 변경 
SQL> select last_name, hire_date from employees where hire_date = TO_DATE('09-07-98','dd-mm-yy') //이렇게 하면 매칭이 안된다. yy때문에.
SQL> select last_name, hire_date from employees where hire_date = TO_DATE('09-07-1998','dd-mm-yyyy') // 이렇게 해주면 뜬다. 년도를 표시 할때  yy는 현재 세기를 사용 한다. 그래서 위에 처럼 yy만 지정하면 2098으로 인식 한다. 그래서 1900년도는 네자리로 표시 하도록 한다. 아니면 yy 대신에 rr을 사용 하면 된다. rr 은 이전세기 50년에서 현재 세기 49년은 이전 세기를 붙여 주고 이후는 현재 세기를 붙여 준다. 98년은 1998로 인식 된다.

Nested Function
SQL> select last_name,upper(concat(substr(last_name,1,8),'_US')) FROM employees where department_id = 60;
SQL> select to_char(next_day(add_months(hire_date,6), 'FRIDAY'),'yyyy/mm/dd')review_day from employees where department_id=50

General Function
NVL 널값이 나오면 특정값으로 변환
SQL> select last_name, salary , commission_pct, NVL(commission_pct,0) from employees // 커미션이 널값을 0으로 대체 
SQL> select last_name, salary+(salary*NVL(commission_pct,0)) from employees // 커미션이 없는 사원도 출력

SQL> select employee_id, last_name, NVL(manager_id, 'No Manager') from employees // manager_id 는 숫자, 'No Managers'는 문자 그래서 에러남
SQL> select employee_id, last_name, NVL(TO_CHAR(manager_id), 'No Manager') from employees // 숫자를 문자로 형을 바꿔서 사용

NVL2 : 첫번째 표현식이 널이 아니면 2번째를 반환 해주고 널이면 3번째를 반환 해줌
SQL> select last_name,salary+(salary*NVL(commission_pct,0)) real_sal, NVL2(commission_pct, 'SAL+COMM','SAL') income FROM employees; // 커미션이 있는경우는 SAL+COMM 을 표시 하고 없는 경우는 SAL만 표시.

NULLIF : 식을 평가 해서 두결과가 같을 경우 NULL 같지 않을 경우는 첫번째 식을 반환 

COALESCE : N개의 표현식 중에서 첫번째로 널이 아닌 표현식의 값을 반환 

SQL> select last_name, coalesce(manager_id,commission_pct,-1) comm from employees order by commission_pct ;

Conditional expressions
CASE exporession ANSI 표준 9i이상에서 사용 가능, 다른 DBMS와 호환 하려면 CASE를 사용.
DECODE function ORACLE에서만 사용 가능. 오라클 이전버전에서도 같이 쓰려면 DECODE를 사용.

CASE expression

SELECT last_name, job_id, salary,<br />        CASE job_id WHEN 'IT_PROG' THEN 1.10*salary<br />                    WHEN 'ST_CLERK' THEN 1.15*salary<br />                    WHEN 'SA_RED' THEN 1.20*salary<br />        ELSE salary END "REVISED_SALARY"<br />FROM employees

salary는 디폴트. 조건에 맞는게 없는 경우 NULL 대신에 salary를 표현.
DECODE Function

SELECT last_name, job_id, salary,<br />        DECODE( job_id , 'IT_PROG' ,1.10*salary,<br />                    'ST_CLERK' ,1.15*salary,<br />                    'SA_RED' ,1.20*salary,<br />                salary)<br />                REVISED_SALARY<br />FROM employees

동일한 내용을 DECODE Function으로 표현
ex)

SELECT last_name,  salary,<br />        DECODE(TRUNC(salary/2000, 0),<br />                0,0.00,<br />                1,0.09,<br />                2,0.20,<br />                3,0.30,<br />                4,0.40,<br />                5,0.42,<br />                6,0.44,<br />                  0.45)TAX_RATE<br />FROM employees<br />WHERE department_id=80

Group Functions

SELECT AVG(salary), MAX(salary),<br />       MIN(salary), SUM(salary)<br />FROM employees<br />WHERE job_id LIKE '%REP%'

select 목록에 그룹함수를 사용할 경우, 그룹을 사용하지 않는것을 사용할 수 없음.
SQL> select min(hire_date),max(hire_date) from employees 

AVG는 NULL을 빼고 계산한다. 

SQL> select count(*) from employees 
count() 는 
column이나 expr 이나  (NULL은 제거 )
distinct column 이 올수 있다. (중복제거)

SQL> select count(salary), count(commission_pct) from employees
SQL> SELECT COUNT(DISTINCT salary) FROM employees

SQL> select avg(commission_pct) from employees;
SQL> select avg(NVL(commission_pct, 0)) from employees
NULL 값이 계산에서 제외 된것과 0으로 대체 된것

* group by는 9i 까지는 정렬하나 10g부터는 정렬을 안합. 대신에 hash를 사용해서 성능향상. 긴장안하고 편하게 사용 해도 됨.
SQL> select department_id, count(*), avg(salary) from employees group by department_id; //department_id 를 기준으로 그룹핑 및 연산

SQL> select department_id, job_id, count(*), avg(salary) from employees GROUP BY department_id, job_id order by 4

SQL> select department_id, AVG(salary) from employees GROUP BY department_id HAVING AVG(salary) > 9000 //group by는 having으로 조건 줄것.
SQL> select department_id, AVG(salary) from employees where department_id > 30 GROUP BY department_id HAVING AVG(salary) > 8000 order by 2 desc // 풀 신텍스 

Nesting Group Functions 
SELECT MAX(AVG(salary)) FROM employees GROUP BY department_id;

JOIN 종류
CROSS JOIN
NATURAL JOIN
JOIN [using | on ]
OUTER JOIN

NATURE JOIN : match 되는 모든 조건에 대한 모두를 연결 , 테이블 네임을 지정할 필요가 없다.같은 이름의 필드 모두를 매칭.결과가 모호해질수 있음.
SQL> select employee_id , department_id,manager_id from employees natural join departments;
SQL> select employee_id , department_id,department_id from employees natural join departments

USING : 네튜럴 조인을 사용하려 했으나 같은이름의 칼럼이 데이터 타입이 다르거나, 조인할 조건 칼럼을 명확하게 해주고 싶을때.
select employee_id,last_name, department_id,department_name from employees join departments using(department_id) //department_id만을 이용해서 조인 

JOIN USING을 쓸때 USING에 쓴 칼럼네임은 테이블 이름을 접두어로 쓸 수 없다.
ex) USING(department_id) 했을때 departments.department_id으로 쓸 수 없다.
SQL> select employees.employee_id, employees.last_name,  department_id, departments.department_name from employees join departments using(department_id)

테이블 접두어 
SQL> select e.employee_id, e.last_name,  department_id, d.department_name from employees e join departments d  using(department_id) // 테이블 이름이 너무 긴경우 단순화 시키기 위해 사용. 별칭을 주고 나면 쿼리문 내에서 별칭만 사용 가능 

JOIN ON
 select e.employee_id, e.last_name,  e.department_id,d.department_id, d.department_name from employees  e join departments d on(e.department_id = d.department_id) 

select e.last_name, e.salary, j.grade_level from employees e join job_grades j on (e.salary BETWEEN j.lowest_sal AND j.highest_sal) // join on 은 범위지정도 가능

using 은 equijoin만 사용가능 하나 on은 (none-equijoin) 좀더 자유로운 지정이 가능하다.

self Join
select e.employee_id, e.manager_id, m.last_name, e.last_name from employees e JOIN employees m ON (e.manager_id = m.employee_id) // 테이블에 별칭을 줘서 다른 테이블을 자기자신 처럼 참조.

Three-Way Join :3개 이상의 테이블 조인

OUTER JOIN : 조인의 조건을 만족하는 행만을 보여주는것이 이너 조인이고, 만족하지 않더라도 함께 보여주는것을 아웃터 조인이라 한다. 

SQL> SELECT e.employee_id, d.department_id, d.department_name from employees e left outer join departments d on (e.department_id = d.department_id) ; // left 조인
SQL> SELECT e.employee_id, d.department_id, d.department_name from employees e right outer join departments d on (e.department_id = d.department_id) // right 조인
SQL> SELECT e.employee_id, d.department_id, d.department_name from employees e full outer join departments d on (e.department_id = d.department_id) // full 조인

CROSS JOIN : 카티샨 프로덕트 
select last_name, department_name from employees cross join departments  // 크로스조인

Equi-Join
Natural Join
Join ... Using
Join On("=")
outer Join

None Equi-join
Join ...On(=이 아닌)