[국비 2일차 TIL] 오라클 데이터베이스 함수 (내장, 숫자, 다중행)

 

 

-- EMP 테이블 구성 살펴보기 (DESCRIBE)

DESC DEPT;

 

DESC SALGRADE;

  

-- 열의 중복 데이터를 삭제하는 DISTINCT

SELECT DISTINCT DEPTNO FROM EMP;

SELECT DISTINCT DEPTNO, JOB FROM EMP;

 

-- 별칭 설정

SELECT ENAME, SAL, SAL*12+COMM AS ANNSAL, COMM FROM EMP;

 

-- ORDER BY 정렬 : 오름차순 어센딩 ASC, 내림차순 디센딩 DESC

-- ORDER BY (칼럼별로 차순을 따로따로 정렬할 수는 없다.)

 

SELECT * FROM EMP

ORDER BY SAL ASC;

 

-- WHERE절

SELECT * FROM EMP

WHERE DEPTNO = 30;

 

SELECT * FROM EMP

WHERE EMPNO = 7782;

 

-- AND, OR

SELECT * FROM EMP

WHERE DEPTNO = 30 AND JOB = 'SALESMAN';

---

-- 연산자 

-- 곱셈 *,   대소 비교 > <,    >=

 

SELECT * FROM EMP

WHERE SAL > 250 AND JOB = 'ANALYST';

 

SELECT * FROM EMP

WHERE ENAME <= 'FORD';

 

-- 등가비교 연산자 != 같지 않다 / <> 값이 다를 경우 true, false

SELECT * FROM EMP

WHERE SAL != 3000;

 

SELECT * FROM EMP

WHERE SAL <> 3000;

 

-- NOT (!=를 NOT으로 변경 가능)

SELECT * FROM EMP

WHERE SAL NOT 3000;

 

-- OR  (여러개의 조건을 만족하는 데이터 출력)

SELECT * FROM EMP

WHERE JOB = 'CLERK'

    OR JOB = 'MANAGER'

    OR JOB = 'SALESMAN';

 

-- IN

SELECT * FROM EMP

WHERE JOB IN ('MANAGER', 'SLAESMAN', 'CLERK');

 

SELECT ENAME, DEPTNO FROM EMP

WHERE DEPTNO IN (10,20);

 

-- BETWEEN A AND B

SELECT * FROM EMP

WHERE SAL BETWEEN 1000 AND 2000;

 

-- LIKE 일부 문자열이 포함된 데이터 출력

-- 문자열 + % = 문자열로 시작하는 데이터

-- % + 문자열 = 문자열로 끝나는 데이터

-- '%문자열%' = 문자열이 포함되어 있는 데이터

 

SELECT * FROM EMP

WHERE ENAME LIKE 'S%';

 

SELECT * FROM EMP

WHERE ENAME LIKE '%SC%';

 

-- IS NULL : 값이 존재하지 않음, 해당 사항 없음, 미확정 값

-- COMM 값이 NULL인 데이터 출력 = IS NULL / IS NOT NULL

-- AND OR 연산자와 같이 사용 가능

 

SELECT * FROM EMP

WHERE COMM IS NULL;

 

SELECT * FROM EMP

WHERE SAL > NULL OR COMM IS NULL;

 

-- UNION 집합 연산자(두 개의문 사이에 UNION)

-- UNION : 결과 값을 합집합으로, 중복은 제거됨.

-- UNION ALL : " , 중복 제거 없이 모두 다 출력

-- MINUS : 먼저 작성한 SELECT문에서 다음 SELECT문에 존재하지 않는 데이터만 출력

-- INTERSECT : 교집합, 같은 데이터만 출력

 

SELECT EMPNO, SAL, DEPTNO FROM EMP

WHERE SAL = 3000

UNION

SELECT EMPNO, SAL, DEPTNO FROM EMP

WHERE SAL = 1300;

 

SELECT EMPNO, SAL, DEPTNO FROM EMP

INTERSECT

SELECT EMPNO, SAL, DEPTNO FROM EMP

WHERE SAL = 3000;

 

---

 

-- 내장 함수

-- UPPER : 대문자로 변경

-- LOWER : 소문자로 변경

-- INITCAP : 앞글자만 대문자, 나머지는 소문자

 

SELECT ENAME, LOWER(ENAME) FROM EMP;

 

-- LENGTH 문자열 길이 구하기

SELECT ENAME, LENGTH(ENAME) FROM EMP;

 

-- 이름 길이가5 이상인 행 출력

SELECT ENAME, LENGTH(ENAME) FROM EMP

WHERE LENGTH(ENAME) >= 5;

 

-- LENGTH / LENGTHB (B= BITE, 1 BITE = 8BIT)

-- 영어 알파벳 하나 1바이트, 한글 하나 2바이트

 

-- DUAL은 가상의 테이블임.

SELECT 1+1 FROM DUAL;

 

-- SUBSTR 문자열 일부를 출력 (String)

SELECT JOB, SUBSTR(JOB,1,2) FROM EMP;

                       = 첫번째 글자부터 2글자 출력

 

-- SUBSTR(해당 데이터, 몇 번째 글자부터 시작해서, 몇 글자 출력)

-- 뒷글자부터도 가능 - 사용

 

-- INSTR (특정 문자 위치 찾는 함수)

SELECT INSTR('HELLO, ORACLE!', 'L') AS INSTR1,

              INSTR('HELLO, ORACLE!', 'L', 5) AS INSTR2,

              INSTR('HELLO, ORACLE!', 'L', 2,2) AS INSTR3

FROM DUAL;

 

-- REPLACE

SELECT '010-9999-1234', REPLACE('010-9999-1234','9999', '0000')

FROM DUAL;

 

-- LPAD RPAD (LEFT, RIGHT 정렬)

SELECT 'ORACLE',

    LPAD('ORACLE', 10),

    RPAD('ORACLE', 10),

    LPAD('ORACLE', 10, '$'),

    RPAD('ORACLE', 10, '@')

FROM DUAL;

 

-- CONCAT 데이터 합병

SELECT CONCAT(EMPNO, DEPTNO) FROM EMP;

 

-- CONCAT과 같은 의미 = || 이게 더 자주 쓰임

SELECT EMPNO || ' : ' || ENAME

FROM EMP;

 

---

 

-- 숫자 함수 

 

-- ROUND : 지정된 숫자의 위치에서 반올림

SELECT ROUND(1234.5678) FROM DUAL;

SELECT ROUND(1234.5678, 2) FROM DUAL;    <-소수점 둘째 자리까지

 

-- TRUNC : 지정된 숫자의 위치에서 버림 한 값

SELECT TRUNC(1234.5678) FROM DUAL;

 

-- CEIL

지정된 숫자보다 큰 정수 중 가장 작은 정수를 반환 = 올림

-- FLOOR

지정된 숫자보다 작은 정수 중 가장 큰 정수를 반환 = 내림

-- MOD

지정된 숫자를 나눈 나머지 값

 

 

SELECT CEIL(1234.5678), FLOOR(1234.5678), MOD(11,4) FROM DUAL;

 

-- 날짜 데이터 함수

-- SYSDATE

 SELECT SYSDATE AS NOW,

        SYSDATE-1 AS YESTERDAY,

        SYSDATE+1 AS TOMORROW FROM DUAL;

 

-- ADD_MONTHS 몇 개월 이후 날짜 (YEAR, DAY는 불가함)

-- 지금 날짜, 3개월 후의 날짜 출력

SELECT SYSDATE, ADD_MONTHS(SYSDATE,3) FROM DUAL;

 

-- 입사일로부터 10년 후 날짜 출력

SELECT EMPNO, ENAME, HIREDATE,

        ADD_MONTHS(HIREDATE,120) FROM EMP;

 

-- MONTHS_BETWEEN 개월 수 차이

SELECT EMPNO, ENAME, HIREDATE, SYSDATE,

    MONTHS_BETWEEN(HIREDATE, SYSDATE) AS MONTH1,

    MONTHS_BETWEEN(SYSDATE, HIREDATE) AS MONTH2,

    TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS MONTH3

FROM EMP;

 

-- ROUND 이용 날짜 데이터 (ROUND 지정된 곳에서 반올림)

SELECT SYSDATE AS NOW,

       ROUND(SYSDATE, 'CC') AS CC,

       ROUND(SYSDATE, 'YYYY') AS YYYY,

       ROUND(SYSDATE, 'DD') AS DD

FROM DUAL;

 

-- 자료형을 변환하는 형 변환 함수

-- 숫자(NUMBER) <-> 문자(CHARACTER) <-> 날짜(DATE)

SELECT EMPNO, EMPNO + '500'

FROM EMP;

 

-- TO_CHAR : 숫자 또는 날짜를 문자 데이터로 변환

SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS NOW

    FROM DUAL;

   

-- TO_NUMBER : 문자를 숫자로 변환

SELECT TO_NUMBER(2000-1500)

FROM DUAL;

 

SELECT TO_NUMBER('1,300', '999,999') - TO_NUMBER('1,500', '999,999')

FROM DUAL;

 

-- TO_DATE : 문자를 날짜로 변환

-- 오라클에서 문자란 : 알파벳, 숫자, 밑줄(_), 큰따옴표(")

 

SELECT TO_DATE('2018-07-04', 'YYYY-MM-DD')

FROM DUAL;

 

-- NULL 처리 함수 (NULL VALUE)

SELECT COMM, NVL(COMM, 0), SAL * 12 + NVL(COMM,0)

FROM EMP;

 

-- NVL2(데이터, 값이 있을 때, 값이 없을 때)

SELECT ENAME, COMM, LPAD(NVL2(COMM, 'O','X'),10),

                    NVL2(COMM, COMM,0)

FROM EMP;

 

SELECT ENAME, SAL*12 + NVL2(COMM, COMM, 0)

FROM EMP;

 

-- 삼항 연산자 // 조건식 ? 참일 때 값 : 거짓일 때 값

 

-- CASE

SELECT EMPNO, ENAME, JOB,

    CASE JOB

    WHEN 'MANAGER' THEN SAL*1.1

    WHEN 'SALESMAN' THEN SAL*1.05

    ELSE SAL*1.03

END

FROM EMP;

 

---

 

-- 다중행 함수

-- SUM (단일 그룹만 가능)

SELECT SUM(SAL)

FROM EMP;

 

-- COUNT 지정된 데이터의 개수 (행의 개수 = 보통은 * 많이 씀)

SELECT COUNT(*)

FROM EMP;

 

-- AVG 평균값

SELECT AVG(SAL)

FROM EMP;

 

SELECT AVG(SAL) FROM EMP

WHERE DEPTNO = 30;

 

-- ALL, DISTINCT 중복 제거

SELECT SUM(SAL), SUM(ALL SAL), SUM(DISTINCT SAL)

FROM EMP;

 

-- MAX

SELECT MAX(SAL) FROM EMP

WHERE DEPTNO = 10;

 

-- NVL : NULL 처리 함수

SELECT NVL(AVG(COMM),0) FROM EMP

WHERE DEPTNO = 10;

 

-- GROUP BY

SELECT DEPTNO, ROUND(AVG(SAL),0) FROM EMP

GROUP BY DEPTNO;

 

SELECT DEPTNO, JOB, AVG(SAL) FROM EMP

GROUP BY DEPTNO, JOB

ORDER BY DEPTNO;

 

-- HAVING 절 (GROUP BY에만 적용되는 조건)

SELECT DEPTNO, JOB, AVG(SAL)

FROM EMP

GROUP BY DEPTNO, JOB

    HAVING AVG(SAL) >= 2000

ORDER BY DEPTNO, JOB;