오라클 DBMS 정리 04

-- 문자열의 길이를 숫자로 반환해주는 함수 LENGTH(문자열)
LENGTH('ABCD')
-- 4

-- 영어 문자열을 대문자로 바꾸는 함수 UPPER(문자열)
UPPER('AbcD')
-- ABCD

-- 영어 문자열을 소문자로 바꾸는 함수 LOWER(문자열)
LOWER('AbcD')
-- abcd

-- 영어 문자열의 첫 글자만 대문자로 바꾸고 나머지는 소문자로 변환하는 함수 INITCAP(문자열)
INITCAP('name')
-- Name
-- 문자열 중간에 띄어쓰기가 존재하면 띄어쓰기 다음 나오는 문자의 첫 글짜는 대문자로 바꿈
INITCAP('abcd ef ghi')
-- Abcd Ef Ghi

-- 찾는 특정 문자열의 인덱스를 반환해주는 함수 INSTR(문자열, 찾을문자열)
-- 오라클은 인덱스가 1번부터 시작
INSTR('AbcDef', 'D')
-- 4
-- 찾는 문자열이 없으면 0이 반환됨(JAVA같은 언어에서 찾는 데이터가 없을 땐 -1이 반환)

-- 문자열 자르기 SUBSTR(문자열, 시작인덱스, 자를글자수)
-- 부모 문자열(Superstring)에서 자식 문자열(Substring)을 잘라내는 것
SUBSTR('ABCDEF', 2, 3)
-- BCD
-- 자를글자수를 입력 안하면 시작인덱스부터 끝까지 자름
SUBSTR('ABCDEF', 3)
-- CDEF

-- 문자열 공백 제거 세가지(앞(좌), 뒤(우), 양뒤 공백 제거)
-- 사이 공백은 REPLACE함수를 통해 없앨 수 있음!
-- 앞뒤 공백을 제거하는 TRIM(문자열)
TRIM('    ABCDE     ')
-- ABCDE
-- 앞 공백을 제거하는 LTRIM(문자열)
LTRIM('    ABCDE    ')
-- ABCDE    
-- 뒤 공백을 제거하는 RTRIM(문자열)
RTRIM('    ABCDE     ')
--      ABCDE

-- 문자열 결합해주는 함수 CONCAT(문자열, 붙일문자열)
-- 붙임연산자(||)와 동일한 기능
-- 많이 결합할 경우 복잡해지므로 ||를 더 많이 쓴다
CONCAT('ABC', 'DEF')
-- ABCDEF

-- 문자열 채우기는 왼쪽 채우기 LPAD와 오른쪽 채우기 RPAD가 있음
-- 이 함수에서 한글은 2byte로 계산함
-- LPAD(문자열, 총자릿수, 채울문자열), RPAD(문자열, 총자릿수, 채울문자열)
LPAD('ABCDE', 10, '#')
-- #####ABCDE
RPAD('ABCDE', 10, '$')
-- ABCDE$$$$$
-- 규격화된 문자열을 만들 때 자주 사용

-- sin, cos, tan 구해주는 함수 SIN(값) COS(값) TAN(값), 잘 안쓰임
-- 절대값을 반환하는 함수 ABS(값)
ABS(-5)
-- 5

-- 반올림을 해주는 함수 ROUND(값, 반올림할자리수)
-- '.'을 기준으로 ROUND(555.555) 소수부는 +, 정수부는 - 
-- 소수부는 반올림했을 때 볼 자리수
ROUND(5.555, 1) 
-- 5.6
ROUND(5.555, 2)
-- 5.56
-- 정수부는 그 자리에서 반올림
ROUND(555, -1)
-- 560

-- 0이 아니면 무조건 올리는 함수 CEIL(값)
CEIL(10.1)
-- 11

-- 내림 함수 FLOOR(값)
FLOOR(10.8)
-- 10

-- 지정한 위치의 수를 자르는 절삭 함수 TRUNC(값, 절삭할자리수)
---소수부는 반올림했을 때 볼 자리수를 뜻하고, 정수부는 반올림할 그 자리를 뜻함
-- 소수부는 그 다음자리를 자름
-- 정수부는 그 자리를 자름
TRUNC(555.555, 2)
-- 555.55
-- 원단위 절삭
TRUNC(555.555, -1)
-- 550
-- 소수 이하 절삭
TRUNC(555.555, 0)
-- 555

NVL(age, 0)

-- 잘못된 데이터형을 삽입한 예
NVL(age, '없음')

-- 오라클은 자동형변환을 해주기 때문에 문자열이 숫자면 괜찮다
NVL(age, '0')

-- TO_CHAR(숫자, pattern)
TO_CHAR(2018, '0,000,000')
-- 0,002,018
TO_CHAR(2018, '9,999,999')
--    2,018

SELECT TO_CHAR(2018.1025, '999999.999')
FROM DUAL;
-- 2018.103

-- 사원테이블에서 사원번호, 사원명, 입사일, 연봉을 조회
-- 단, 연봉은 데이터가 있는 것까지만 ,를 넣어 출력
SELECT empno, ename, hiredate, TO_CHAR(sal, '999,999') sal
FROM emp;

-- 에러!, TO_CHAR의 반환값은 문자열! 문자열은 사칙연산을 할 수 없음
SELECT TO_CHAR(sal, '9,999')+100
FROM emp;
-- 이거는 가능
-- SELECT TO_CHAR(sal+100, '9,999')

-- TO_CHAR(날짜, pattern)
TO_CHAR(SYSDATE, 'y')
-- 8
TO_CHAR(SYSDATE, 'yy')
-- 18
TO_CHAR(SYSDATE, 'yyyy')
-- 2018
TO_CHAR(SYSDATE, 'yyyyy')
-- 20188

-- 필요한 형태로 패턴을 만들어 사용한다
-- SYSDATE로 입력한게 아니면 시간정보는 들어가지 않는다
SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd am hh(hh24):mi:ss day dy q')
FROM DUAL;

SELECT SYSDATE
FROM DUAL;
-- 툴마다 SYSDATE형태가 다르게 나옴, 같게 만들고 싶으면 TO_CHAR를 사용한다

-- perttern이 특수문자가 아닌 문자열 사용할때에는 "로 묶는다
SELECT TO_CHAR(SYSDATE, 'yyyy"년" mm"월" dd"일"')
FROM DUAL;

-- perttern을 너무 길게 사용하면 Error 발생!
SELECT TO_CHAR(SYSDATE, 'yyyy " 년 " mm " 월 " dd " 일 " hh24 " 시 " mi " 분 " ss " 초 "')
FROM DUAL;

-- 이럴경우 잘라서 ||나 CONCAT으로 붙이는 방법을 사용
SELECT TO_CHAR(SYSDATE, 'yyyy " 년 " mm " 월 " dd " 일 "')
       || TO_CHAR(' hh24 " 시 " mi " 분 " ss " 초 "')
FROM DUAL;

TO_DATE(문자열, pattern)
-- pattern은 TO_CHAR에서 쓰던 pattern과 동일
-- 단, 문자열이 날짜형식의 문자열일 때만 변환됨

TO_DATE('2018-10-25','yyyy-mm-dd')
-- 2018-10-25
-- 문자형 데이터가 날짜형 데이터로 변환됨

-- 현재 날짜가 아닌 날짜를 추가할 땐, 날짜 형식의 문자열을 추가하면 됨
INSERT INTO class4_info(num, name, input_date)
VALUES(8, '양세찬', '2018-10-21');
-- SYSDATE로 데이터를 넣지 않았기 때문에 시간정보는 없다

INSERT INTO class4_info(num, name, input_date)
VALUES(9, '양세형', TO_DATE('2018-10-22', 'yyyy-mm-dd'));
-- 굳이 TO_DATE를 안써도 년월일 구별해서 잘 들어간다

-- TO_CHAR는 날짜나 숫자를 입력받아야함.(함수의 값(인자)은 데이터형을 구분한다.)
-- 아래 '2018-10-25'는 문자열, 문자열로 함수의 인자로 들어갔을 경우 에러 발생
SELECT TO_CHAR('2018-10-25', 'mm')
FROM DUAL;

-- 함수의 매개변수로 이용할 땐 TO_DATE가 필요!
SELECT TO_CHAR(TO_DATE('2018-10-25', 'yyyy-mm-dd'), 'mm')
FROM DUAL;

DECODE(컬럼명, 비교값, 출력값, 비교값, 출력값, ..., 비교값이없을때출력할값)

-- deptno가 10, 20, 30이 있을 때
-- deptno가 10이면 SI로, 20이면 SM으로 30이면 QA로 그 외는 Solution을 반환하는 DECODE 함수
DECODE(deptno, 10, 'SI', 
               20, 'SM', 
               30, 'QA', 'Solution')

-- 사원테이블에서 사원번호, 사원명, 연봉, 부서명을 조회
-- 단, 부서명은 아래의 부서번호에 해당하는 부서명으로 출력
-- 10-개발부, 20-유지보수부, 30-품질보증부, 그 외는 탁구부로 출력
SELECT empno, ename, sal,
       DECODE(deptno, 10, '개발부', 
        20, '유지보수부', 30, '품질보증부',
        '탁구부') dname
FROM   emp;               

SELECT CASE 컬럼명 
           WHEN 비교값 THEN 실행코드
           WHEN 비교값 THEN 실행코드
           WHEN ...
           ELSE 비교값이없을때실행코드
       END alias 

-- DECODE랑 다르게 콤마 안씀! 조심할 것

-- DECODE 예제와 같은 문제를 CASE로 조회
SELECT empno, ename, sal, deptno,
    CASE deptno WHEN 10 THEN '개발부'
                WHEN 20 THEN '유지보수부'
                WHEN 30 THEN '품질보증부'
                ELSE '탁구부'
    END dname
FROM emp; 

SELECT COUNT(empno), ename
FROM emp;
-- Error!, COUNT는 한 행으로 조회되고 ename은 14개가 조회됨
-- COUNT(empno) 컬럼값으로 뭘 넣어야 할 지 몰라서 에러발생

-- COUNT는 레코드의 수를 세는 함수
-- NULL인 레코드는 COUNT에 포함되지 않는다!
COUNT(컬럼명)

-- NULL이 아닌 모든 레코드의 수를 셀 때
COUNT(*)

-- 컬럼 값의 합을 구하는 함수 SUM
SUM(컬럼명)

-- 컬럼 값의 평균을 구하는 함수 AVG
AVG(컬럼명)

-- 컬럼 값 중 가장 큰 수를 찾는 반환하는 MAX
MAX(컬럼명)

-- 컬럼 값 중 가장 작은 수를 찾는 반환하는 MIN
MIN(컬럼명)


-- 최고연봉액, 최저연봉액, 최고연봉액과 최저연봉액의 차이
SELECT MAX(sal), MIN(sal), MAX(sal)-MIN(sal)
FROM emp;

-- **WHERE절에서는 집계함수를 사용할 수 없다
--   서브쿼리를 사용해서 처리해야 함
-- 사원테이블에서 평균연봉보다 많이 받는 수령하는 사원의
-- 사원번호, 사원명, 연봉, 입사일을 조회
SELECT empno, ename, sal, hiredate
FROM emp
WHERE sal > AVG(sal); -- 에러!

-- 집계함수는 GROUP BY와 같이 사용하면 그룹별 집계를 조회한다
-- 부서번호, 부서별인원수, 부서별 연봉의 합, 부서별 연봉평균, 부서별 최고연봉액
SELECT deptno, COUNT(empno), SUM(sal), TRUNC(AVG(sal),2), MAX(sal), MIN(sal)
FROM emp
GROUP BY deptno;

ADD_MONTHS(날짜, 더할개월수)

MONTHS_BETWEEN(큰날짜, 작은날짜)

-- 날짜에 +를 사용하면 일을 더함
SELECT SYSDATE+5
FROM DUAL;

-- 현재로부터 5개월 후
SELECT ADD_MONTHS(SYSDATE,5)
FROM DUAL;

-- 현재(2018-10-25)부터 2019-05-25까지의 개월수 차이를 구한다 
SELECT MONTHS_BETWEEN('2019-05-25', SYSDATE)
FROM DUAL;