SQL - DML 데이터 조작어 기본 | dual, INSTR, SUBSTR, REPLACE, LPAD/RPAD, CONCAT, 날짜(SYSDATE, ADD_MONTHS, MONTHS_BETWEEN,NEXT_DAY,AST_DAY), 형태 변환(TO_CHAR, TO_NUMBER, TO_DATE), NULL 값 처리(NVL), 조건문(DECODE, WHEN), 집계함수..

 

dual, INSTR, SUBSTR, REPLACE, LPAD/RPAD, CONCAT, 날짜(SYSDATE, ADD_MONTHS, MONTHS_BETWEEN,NEXT_DAY,AST_DAY), 형태 변환(TO_CHAR, TO_NUMBER, TO_DATE), NULL 값 처리(NVL), 조건문(DECODE, WHEN), 집계함수(ROLLUP, CUBE)

 

 

Oracle SQL Developer를 이용하여

기초에서 다루지 않았던 문법들을 다룰 예정

 

 

처음이라면 생성 후 연결

 

 

 

 

---

 

 

 

 

가상 테이블

임의로 테이블 만들고 싶을 때

FROM dual;

 

 

 

---

 

값의 문자열 다루기

INSTR

찾은 문자열의 인덱스 값 찾기

 

SELECT INSTR('CORPORATE FLOOR', 'OR', 1, 1)
FROM dual;

'OR' 값을 1번째 부터 찾아서 1번째로 시작하는 위치

CORPORATE FLOOR 의 1번째 위치에서 'OR' 이 1번째로 나오는 위치 값

2번째 위치에 'OR'값이 시작하기 때문에 결과값은 2

 

 

 

 

SELECT INSTR('CORPORATE FLOOR', 'OR', -3, 1)
FROM dual;

 

OR' 값을 -3번째 부터 반대로 찾아서 1번째로 나오는 위치

 

 

 

 

SUBSTR

문자열 자르기

 

SUBSTR(ename,0,2)

ename 컬럼 값에서 0에서 부터 2개 : 0 ~ 1 인덱스의 값

SELECT ename,SUBSTR(ename,0,2)
FROM emp;

 

 

 

SPLIT 처럼 이용하기

SELECT SUBSTR(tel,1,INSTR(tel,')')-1)
FROM emp2;

INSTR(tel,')') 의 위치 값은 인덱스 값보다 +1 더 크기 때문에

SUBSTR 으로 자를 때 -1을 범위로 문자열을 자른다.

SUBSTR(tel,1,INSTR(tel,')')-1)

 

---

 

REPLACE

SELECT '010-1234-5678' AS rep_replace,
    REPLACE('010-1234-5678','-', ' ') rep_after
FROM dual;

전화 번호 내의 '-' 값을 space 값으로 대체

 

---

 

LPAD / RPAD

값 채우기

 

 

LPAD

지정 문자로 길이를 채운다. 기본 값은 공백

SELECT 'Oracle', LPAD('Oracle', 10, '#') LPAD1
FROM dual;

LPAD('Oracle', 10, '#') 

10개 길이에 맞게 '#' 으로 채운다.

 

자매품 RPAD

SELECT empno, RPAD(substr(empno,1,2), length(empno), '*')
FROM emp;

 

 

---

 

 

CONCAT

컬럼 합치기

SELECT CONCAT(ename, CONCAT(':',job))
FROM emp;

 

 

연결 ||

SELECT ename || ':' || job as ename_job
FROM emp;

 

 

연산 +

SELECT sal,deptno, sal + deptno
FROM emp;

 

 

 

 

그 밖의

 

TRIM, LTRIM, RTRIM

문자열 공백 제거

 

 

올림 반올림 버림

CEIL 올림

ROUND(COL, 소수자리) 반올림
TRUNC(COL, 소수자리) 버림

FLOOR 가까운 정수
MOD(B,A) 나머지 

 

 

---

 

 

날짜

 

sysdate

SELECT SYSDATE
FROM dual;

 

ADD_MONTHS

몇 개월 후의 날짜

 

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

 

MONTHS_BETWEEN

두 날짜간의 개월 수 차이 구하기

 

SELECT empno, ename, hiredate, sysdate,
    MONTHS_BETWEEN(sysdate, hiredate) || ' 개월' as months1
FROM emp;

 

 

---

 

NEXT_DAY

궁금한 다음 요일이 몇 일 인지 알려줌 

SELECT NEXT_DAY(SYSDATE, '월요일')
FROM dual;

LAST_DAY
해당 날짜 월의 마지막 날

LAST_DAY(SYSDATE)

 

 

 

 

 

 

---

 

 

형태 변환

 

 

 

TO_CHAR

문자 형태 변환

SELECT TO_CHAR(sysdate, 'YYYY/MM/DD HH24:MI:SS')
FROM dual;

 

SELECT sal, TO_CHAR(sal, '$999,999') AS "$_SAL",
    TO_CHAR(sal, 'L999,999') AS "\SAL",
    TO_CHAR(sal, '000,999') AS "00..SAL"
FROM EMP;

 

 TO_NUMBER

숫자 형태 변환

SELECT TO_NUMBER('1,300', '999,999')
FROM dual;

 

 TO_DATE

SELECT TO_DATE('2020-02-02', 'YYYY-MM-DD')
FROM dual;

날짜

 

 

 

 

---

 

 

NVL

null 일 때 대체 

 

SELECT comm, NVL(comm, 0) 
FROM emp;

 

 

NULL 을 'N/A' 으로 대체

 

COMM 속성의 타입이 NUMBER 타입이기 때문에

문자열로 바꿀 수 없다.

 

SELECT NVL(TO_CHAR(comm), 'N/A') as COMM 
FROM emp;

그래서 TO_CHAR 통해 문자열 변환 

 

SELECT NVL(TO_CHAR(comm), 'N/A') as COMM 
FROM emp;

 

 

 

SELECT NVL2(comm, 'O', 'X')
FROM emp;

 

 

---

 

 

 

조건문

DECODE, WHEN

 

 DECODE

swith ~case 문과 유사

기준이 되는 데이터를 먼저 지정한 후 일치에 따라 변환

SELECT empno, ename, job ,sal,
    DECODE(job, 'MANAGER', sal *1.95,
            'ANALYST', sal *1.65,
            'SALESMAN', sal *1.3,
            'CLERK', sal *1.2) AS upsal
FROM emp;

 

---

SELECT 
DECODE(SUBSTR(tel,1,INSTR(tel,')')-1),02,'서울'
                                    ,051,'부산',
                                    052,'울산',
                                    053,'대구',
                                    '기타') as 지역
, name, tel
FROM student;

 

---

연도별 입사 사원 수

SELECT ename,hiredate,
    DECODE(TO_CHAR(hiredate,'YYYY'), 1980,1,0) "1980",
    DECODE(TO_CHAR(hiredate,'YYYY'), 1981,1,0) "1981",
    DECODE(TO_CHAR(hiredate,'YYYY'), 1982,1,0) "1982"
FROM emp;

SELECT 
    SUM(DECODE(TO_CHAR(hiredate,'YYYY'), 1980,1,0)) "1980",
    SUM(DECODE(TO_CHAR(hiredate,'YYYY'), 1981,1,0)) "1981",
    SUM(DECODE(TO_CHAR(hiredate,'YYYY'), 1982,1,0)) "1982"
FROM emp;

 

 

 

 

WHEN

SELECT empno, ename, job ,sal,
    CASE job
        WHEN 'MANAGER' then sal*1.5
        WHEN 'SALESMAN' then sal*1.2
    ELSE sal
    END 
FROM emp;

 

 

---

SELECT empno, ename, mgr, 
    CASE
        WHEN to_char(mgr) LIKE '75%' THEN '5555'
        WHEN to_char(mgr) LIKE '76%' THEN '6666'
        WHEN to_char(mgr) LIKE '77%' THEN '7777'
        WHEN to_char(mgr) LIKE '78%' THEN '8888'
        WHEN mgr IS NULL THEN '0000'
        ELSE to_char(mgr)
    END AS chg_mgr
FROM emp;

 

 

 

---

SELECT POSITION, AVG(pay),
    CASE
        WHEN AVG(pay) > 300 THEN '우수'
        ELSE '보통'
    END AS 비고
FROM professor
GROUP BY POSITION;

 

 

---

 

 

ROLLUP

그룹별 결과 뿐만 아니라 총 집계도 해줌 / null 포함

SELECT deptno, JOB, COUNT(*), MAX(sal),SUM(sal), AVG(sal)
FROM emp
GROUP BY ROLLUP(deptno, JOB);

 

ROLLUP 안했을 떄와 비교

SELECT deptno, JOB, COUNT(*), MAX(sal),SUM(sal), AVG(sal)
FROM emp
GROUP BY deptno, JOB;

CUBE

자세하게 소그룹 합계 제공

SELECT deptno, JOB, COUNT(*), MAX(sal),SUM(sal), AVG(sal)
FROM emp
GROUP BY CUBE(deptno, JOB);