Oracle 3일차.

ORDER BY절

• 로우를 정렬하려면 SELECT문에 ORDER BY절을 추가해야 한다.

 

 

SELECT * FROM EMP ORDER BY SAL ASC;

SELECT ENAME, SAL*12+NVL(COMM, 0) AS ANNSAL FROM EMP;

AS는 생략 가능

EX)SELECT ENAME, SAL*12+NVL(COMM, 0) ANNSAL FROM EMP;

 

연결 연산자

• SELECT문 내부에 "||" 수직바를 사용해서 표현한다.
• 기존의 컬럼내에 문자열을 추가하고자 할 떄 사용한다

산술형 함수 처리 예시

 

 

TRUNC 함수 처리 예제

MOD연산자 처리 예제.

 

UPPER 사용 예제.

 

LOWER 사용 예제.

 

INITCAP 사용 예제.

LENGTH 사용 예제.

INSTR 사용 예제.

SUBSTR 사용 예제.

LPAD와 RPAD의 사용 예제.

LTRIM과 RTRIM 사용 예제.

TRIM 사용 예제.

TRIM은 중간에 있는 문자는 처리 못함

EX)

SELECT TRIM('O' FROM 'aaaaaORACLEaaaaa')FROM DUAL;
--이같이 가운데에 있는 문자열을 없애는 명령어는
--인식하지 못한다.

SYSDATE 사용 예제.

 

MONTHS_BETWEEN 사용 예제.

 

ADD_MONTHS 사용 예제.

 

NEXT_DAY 사용 예제.

 

LAST_DAY 사용 예제.

2) 형 변환 함수(숫자형, 문자형, 날짜형)

• 숫자, 문자, 날짜의 데이터형을 다른 데이터형으로 변환하는 함수이다.

출력 형식의 종류.

TO_CHAR로 날자 형변환 예제.

TO_CHAR 로 날자 형변환 예제2.

 

TO_CHAR 로 표시 형식 변경.

TO_DATE 사용 예제

TO_NUMBER 사용예제

 

NVL 사용 예제.

 

DECODE 사용 예제.

 

WHEN-THEN 사용 예제.

 

--그룹함수
SELECT SUM(SAL) FROM EMP;
--SUM(컬럼명) : 컬럼의 총합을 표시.
SELECT AVG(SAL) FROM EMP;
--AVG(컬럼명) : 컬럼의 평균을 표시.
SELECT ROUND(AVG(SAL), 2) FROM EMP;
SELECT MAX(SAL), MIN(SAL) FROM EMP;
--최대 최소값 구하기.
SELECT COUNT(COMM) FROM EMP;
--갯수 세기, 널은 제외.
SELECT DEPTNO FROM EMP GROUP BY DEPTNO;
--전체 사원 중에서 부서번호별 부서번호 표시.
SELECT AVG(SAL) FROM EMP GROUP BY DEPTNO;
--부서별 급여 평균.
--그룹을 짓고 그다음에 그룹함수 처리.
SELECT DEPTNO,ENAME, AVG(SAL) FROM EMP GROUP BY DEPTNO;
--부서별로 묶었을때, 부서가 최소 단위. 그러므로 부서명은 가능하지만,
--사원 명은 사용 불가.
--관련 오류 : NOT A GROUP BY EXPRESSION

그룹함수 사용 예제.

3) HAVING 절

• GROUP BY 절에 의해 생성된 결과 값 중 원하는 조건에 부합하는 자료만 보고자 할 때 사용한다.

 

HAVING 사용 예제.

 

10. 조인

조인의 필요성

• 오라클은 관계형 데이터베이스인데 모든 정보들이 하나의 테이블에 몰려 있는 것이 아니라 여러 테이블에 정규화 되어 분산되어 있다. 따라서 각 테이블끼리는 서로 특정한 규칙이나 관계가 있도록 설계되어 있다.
• 데이터가 여러 곳에 흩어져 있기 때문에 사용자가 원하는 데이터를 모두 찾으려면 여러 테이블을 다 조회해야 한다.
• 그래서 등장한 기술이 JOIN이다.
• JOIN
  • 여러 테이블에 흩어져 있는 정보 중에서 사용자가 필요한 정보만 가져와서 가상의 테이블처럼 만들어서 결과를 보여준다.
  • 예를 들어 사원테이블에서 이름을 가져오고 부서테이블에서 부서명만을 가져와서 새로운 결과를 도출하는 것이 JOIN이다.

JOIN 예시

• 조인의 종류
  • CARTESIAN PRODUCT(카티션 곱)
  • EQUI JOIN
  • NON-EQUI JOIN
  • SELF JOIN
  • OUTER JOIN

 

1) CARTESIAN PRODUCT(논리곱,카티젼 프로덕트)

• 두개 이상의 테이블이 조인될 때 WHERE절에 공동되는 컬럼에 의한 조인이 발생되지 않아 모든 데이터가 검색 결과로 나타나는 경우이다.
• 그래서 데이블에 존재하는 모든 데이터가 검색 결과로 나타난다.
• [SELECT * FROM EMP, DEPT;] 쿼리문을 실행하면 컬럼수는 11개(EMP컬럼 8개 + DEPT컬럼 4개 - DEPTNO 중복컬럼 1개)와 로우수는 56개(EMP로우 14개 * DEPT로우 4개)로 아무런 의미를 갖지 못한다. 조인 결과가 의미를 가지려면, WHERE절에 올바른 조인 조건을 지정해야 한다.

2) EQUI JOIN

• 조인 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치되는 행을 연결하여 결과를 생성하는 조인 기법이다.
• 예) 사원 정보를 출력할 때, 각 사원이 소속된 부서의 상세 정보를 출력하기 위해 두 개의 테이블을 조인하는 쿼리문
• 일치하는 값을 뽑아냄.

 

EQUI JOIN 예시.

EQUI JOIN 예시2.

테이블 별칭 이용 예시

3) NON-EQUI JOIN(비등가 조인)

• 동일 컬럼이 없이 다른 조건을 사용하여 조인 할 떄 쓰인다.
• EQUI JOIN과 NON-EQUI JOIN의 차이점
  • EQUI JOIN : JOIN조건에 서로 다른 조건(=)을 가진 데이터를 JOIN해서 가져오는 방법
  • NON-EQUI JOIN : 같은 조건이 아닌 크거나 작거나 하는 경우 JOIN을 수행하는 방법

NON EQUI JOIN 예제.

4) SELF JOIN

• 자기 자신과 조인을 하는 기법이다.
• 조인은 서로 다른 두 개의 테이블을 연결하기도 하지만 하나의 테이블 내에서 조인을 해야만 자료를 얻을 수 있는 경우에 쓰인다.
• 이름이 SMITH인 사원의 매니저 이름을 알아내려면 이 역시 EMP테이블에서 확인 할 수 있다. 그럼 담당 매니저의 이름을 알아내려면 어떻게 해야할까? 매니저 테이블이 있다면 이 두 테이블을 조인하여 알아낼 수 있겠지만 매니저 역시 이 회사에 사원이기 때문에 중복된 데이터가 생길 수 있으므로 SMITH 담당 매니저의 정보는 사원 테이블을 참조해서 얻어내야 한다. 이렇게 자신과 자기 자신을 조인하는것이 SELF JOIN이라고 한다.

• 

  SELF JOIN 예제.

5) OUTER JOIN

• 조인 조건에 만족하지 않는 행도 나타내는 조인 기법이다.
• 2개 이상의 테이블이 조인될 때, 어느 한쪽의 테이블에는 해당하는 데이터가 존재하는데 다른 쪽 테이블에는 데이터가 존재하지 않는 경우 그 데이터가 출력되지 않는 문제를 해결하기 위해서 사용되는 조인기법이다.
• [SELECT COUNT(*) FROM EMP;] 쿼리문을 실행하면 EMP테이블의 로우는 14개이다. 하지만 SELF JOIN에서 매니저가 누구인지의 예에서는 로우가 13개로 나와있다. 그 이유는 이름이 KING인 사원의 정보가 제외되었기 때문이다.
  • 그럼 왜 제외가 되었을까? KING은 사장(PRESIDENT)으로 매니저가 존재하지 않으므로 MGR 컬럼값이 NULL이기 때문이다. 이때 조인 조건에 만족하지 못하였더라도 해당 로우를 나타내고 싶을 때 OUTER JOIN을 사용한다.

OUTER JOIN 예제.