[SQL] 함수(숫자,문자,날짜)

SQL 함수

컬럼의 명이 아니기때문에 alias 필수사용권장
alias : 컬럼명 as "별칭" : 더블쿼터 생략가능

숫자형 함수, 문자형 함수, 날짜 함수
형변환 : to_number > to_char > to_date
NVL : null 체크
if처럼 사용할 수 있는 함수 : DECODE, CASE WHEN THEN END

그룹함수

하나의 테이블에서 하나 이상의 행을 그룹으로 묶어서 하나의 결과로 나타내는 함수
SAL 14개의 행이지만 SUM 그룹함수를 통해서 14개의 행에 대한 값을 다 더하여 하나의 행으로 출력

SELECT SUM(SAL) FROM EMP;

직계함수

COUNT : 행의 갯수
SELECT COUNT(*) FROM EMPLOYEES;
COUNT(컬럼) : 중복처리를 한 갯수 : DISTINCT

부서의 갯수

SELECT COUNT(DISTINCT DEPTNO) FROM EMP;
SELECT COUNT(DISTINCT JOB) FROM EMP;

SUM(), AVG(), MAX(), MIN()

SELECT SUM(SAL), AVG(SAL), MAX(SAL), MIN(SAL) FROM EMP;

NULL은 그룹함수에서 어떻게 처리하는지 확인

SELECT SAL, COMM, SAL+COMM FROM EMP;
SELECT SUM(COMM) FROM EMP; -- NULL 제외하고 계산
SELECT MIN(COMM),MAX(SAL) FROM EMP; -- NULL 제외하고 계산
SELECT COUNT(COMM) FROM EMP; -- NULL 제외하고 계산

데이터의 행의 갯수 : 기본키로 작성된 컬럼은 갯수를 세는데 특화 (null, 중복 없음)
기본키로 작성되지 않은 컬럼에서 갯수를 셀때는 주의! (null, 중복 여부 확인)

SELECT ENAME, SUM(SAL) FROM EMP; -- ORA-00937: 단일 그룹의 그룹 함수가 아닙니다

DUAL 테이블

임시테이블 + 한 행의 결괄르 출력하기 위한 테이블, 산술연산, 가상컬럼등을 만들때 사용, 일회성

SELECT 999*2342 FROM DUAL;

숫자형 함수

SELECT -10, ABS(-10) FROM DUAL; -- 절대값 : ABS
SELECT 3.14, FLOOR(3.14)FROM DUAL; -- 소수점내림
SELECT 34.567, ROUND(3.567)FROM DUAL; -- 소수점반올림 
SELECT 34.567, ROUND(3.567, 2)FROM DUAL; -- 소수점반올림
SELECT 34.567, ROUND(3.567, -1)FROM DUAL; -- 소수점반올림
SELECT 3.14, CEIL(3.14)FROM DUAL; -- 소수점올림
SELECT 34.567, TRUNC(34.567, 2), TRUNC(34.567, -1), TRUNC(34.567), TRUNC(34.567,0)FROM DUAL;
SELECT MOD(27,2), MOD(27,5), MOD(27,7) FROM DUAL; -- 나머지 구하는 함수 TRUNC(3.567)

SELECT * FROM EMP;
-- 사원명, 급여, 월급(급여/12)를 출력하되 월급은 십단위에서 반올림하여 출력
SELECT ENAME, SAL, TRUNC(SAL/12,-1) FROM EMP;
-- 사원명, 급여, 세금(급여의 3.3%)를 원단위 절삭하고 출력
SELECT ENAME, SAL, TRUNC(SAL*0.033) AS TAX FROM EMP;

문자열 함수

SELECT 'Hello World', UPPER('Hello World'), LOWER('Hello World') FROM DUAL;
SELECT 'hello world', INITCAP('hello world') FROM DUAL;
SELECT 'Hello World', LENGTH('Hello World') FROM DUAL;
SELECT 'Hello World', LENGTHB('Hello World') FROM DUAL;
SELECT '헬로우 월드', LENGTH('헬로우 월드') FROM DUAL;
SELECT '헬로우 월드', LENGTHB('헬로우 월드') FROM DUAL;

SELECT DETAIL, LENGTH(DETAIL), LENGTHB(DETAIL)FROM SANGPUM;
SELECT MAX(LENGTHB(DETAIL)) FROM SANGPUM;

SELECT '오라클', LENGTH('오라클'), LENGTHB('오라클') FROM DUAL;

INSTR

글자위치 찾아주는 함수 : 없으면 0, 있으면 해당 위치

SELECT INSTR('Wecome To Oracle','e') FROM DUAL;
-- SUBSTR : 대상문자열에서 시작위치부터 몇개를 추출하는 함수
SELECT SUBSTR('Wecome To Oracle',4,3) FROM DUAL;
SELECT SUBSTR('Wecome To Oracle',1,2) FROM DUAL;

SELECT * FROM EMP;
-- 사원들의 입사년도와 입사 달 출력
SELECT ENAME,
       SUBSTR(HIREDATE,1,2) AS HIRE_YEAR,
       SUBSTR(HIREDATE,4,2) AS HIRE_MONTH,
       19||SUBSTR(HIREDATE,1,2)||'년 '||SUBSTR(HIREDATE,4,2)||'월' AS "입사일"
  FROM EMP;

SUBSTR / SUBSTRB

SELECT '안녕하세요오라클',
       SUBSTR('안녕하세요오라클',4,3),
       SUBSTRB('안녕하세요오라클',4,6)
  FROM DUAL;

LPAD / RPAD

LPAD : 오른쪽 정렬을 한 후 왼쪽에 생긴 공간에 특수기호 채우기
RPAD : 왼쪽 정렬을 한 후 왼쪽에 생긴 공간에 특수기호 채우기
정렬 시 사용하는 함수, 특정기호로 채우는 함수

SELECT LPAD('Oracle',20,'#') FROM DUAL;
-- 20자리를 #으로 채우는데 Oracle 포함
SELECT RPAD('Oracle',20,'#') FROM DUAL;
SELECT RPAD('Oracle',20,' ') FROM DUAL;

REPLACE(문장, 바꿀문자열, 바뀔문자열)

TRANSLATE(문장, 바꿀문자열, 바뀔문자열)

SELECT REPLACE('내가 그린 기린 그림은 잘 그린 기린 그림이다.','기린','사자')
          FROM DUAL;
--내가 그린 사자 그림은 잘 그린 사자 그림이다.

SELECT TRANSLATE('내가 그린 기린 그림은 잘 그린 기린 그림이다.','기린','사자')
          FROM DUAL;
--내가 그자 사자 그림은 잘 그자 사자 그림이다.
--: 문자열단위로 치환하는것이 아니라 문자 한 글자씩 매핑하여 변경
--1234를 일이삼사로 바꿀때

예제

SELECT * FROM EMP;
-- smith의 정보를 사원번호, 성명, 담당업무(소문자) 출력
SELECT EMPNO,ENAME, LOWER(JOB)
  FROM EMP
 WHERE ENAME = 'SMITH';

-- 사원번호, 사원명(첫글자만 대문자), 담당업무(첫글자만 대문자)로 출력
SELECT EMPNO,ENAME,UPPER(JOB)
  FROM EMP;

-- 이름의 첫글자가 ‘K’보다 크고 ‘Y’보다 작은 사원의 정보
-- ( 사원번호, 이름, 업무, 급여, 부서번호)를 출력하되 이름순으로 정렬
SELECT empno, ename, comm, job, sal, deptno
from emp
WHERE substr(ename,1,1)>'K' And substr(ename,1,1)<'Y';

-- 이름이 5글자 이상인 사원들을 출력
SELECT * FROM EMP
 WHERE LENGTHB(ENAME) >= 5;
-- 이름을 15자로 맞추고 글자는 왼쪽에 오른쪽에는 ‘*’로 채운다
SELECT RPAD(ENAME,15,'*')
  FROM EMP;
-- 급여는 10자로 맞추고 숫자는 오른쪽에 왼쪽엔 ‘-‘로 채운다
SELECT LPAD(SAL,10,'-')
  FROM EMP;
-- 급여를 숫자에서 ‘영일이삼사오육칠팔구’ 글자로 대체
SELECT TRANSLATE(SAL, '0123456789', '영일이삼사오육칠팔구') AS "한글급여" FROM EMP;
-- 급여의 숫자에서 0을 ‘$’로 바꾸어 출력
SELECT REPLACE(SAL,0,'$') AS "급여" FROM EMP;

TRIM : 공백 제거

select  '-' || trim('  이순신  ') || '-' as col1, 
        '-' || ltrim('  이순신  ') || '-' as col2, 
        '-' || rtrim('  이순신  ') || '-' as col3 from dual;
        
SELECT TRIM('a' FROM 'aaaaaaaaORACLEaaaaaaaaaa') FROM DUAL;

---

날짜 연산

날짜 + 숫자 : 날짜 기준으로 그 기간만큼 지난 날짜
날짜 - 숫자 : 날짜 기준으로 그 기간만큼 이전 날짜
날짜 - 날짜 : 두 날짜 사이의 기간

SYSDATE : 현재날짜를 반환하는 함수, 키워드

현재시스템의 날짜를 읽어와서 출력

SELECT SYSDATE FROM DUAL;
SELECT SYSDATE-1 어제, SYSDATE 오늘, SYSDATE+1 내일 FROM DUAL;

MONTHS_BETWEEN : 날짜간의 개월수를 구하는 함수

SELECT ENAME, SYSDATE, HIREDATE, MONTHS_BETWEEN(SYSDATE,HIREDATE) 근무개월수 FROM EMP;

ADD_MONTHS : 특정개월수를 더하는 함수

SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE,4) FROM EMP;

NEXT_DAY

SELECT SYSDATE, NEXT_DAY(SYSDATE,'수') FROM DUAL;
SELECT SYSDATE, NEXT_DAY(SYSDATE,'화요일') FROM DUAL;
-- DB에서는 요일이 일요일 기준 1부터 시작
SELECT SYSDATE, NEXT_DAY(SYSDATE,1) FROM DUAL;

LAST_DAY : 해당월의 마지막날짜를 반환

SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT HIREDATE, LAST_DAY(HIREDATE) FROM EMP;

---

예제

-- 현재까지 근무일 수가 많은 사람 순으로 출력
SELECT TRUNC(SYSDATE-HIREDATE) from emp;

-- 현재까지 근무일 수가 몇 주 몇 일인가를 출력
SELECT TRUNC((SYSDATE-HIREDATE)/7)||'주 '||TRUNC(MOD(SYSDATE-HIREDATE,7))||'일' AS 근무일
  FROM EMP;

-- 10번 부서의 사원의 현재까지의 근무 월수를 계산
SELECT ENAME, MONTHS_BETWEEN(SYSDATE,HIREDATE)
  FROM EMP
 WHERE DEPTNO =10;
-- 현재 날짜에서 3개월 후의 날짜 구하기
SELECT SYSDATE, ADD_MONTHS(SYSDATE,3) FROM DUAL;
-- 현재 날짜에서 돌아오는 ‘월’요일의 날짜 구하기
SELECT NEXT_DAY(SYSDATE,2) FROM DUAL;
-- 현재 날짜에서 해당 월의 마지막 날짜 구하기
SELECT LAST_dAY(SYSDATE) FROM DUAL;

---

형변환

숫자형 / 문자형 / 날짜형
to_number / to_char / to_date
숫자형 > 문자형 : to_char()
문자형 > 숫자형 : to_number()
날짜형 > 문자형 : to_char()
문자형 > 날짜형 : to_date()
형변환함수( 데이터, '표시형식' )

TO_CHAR : 날짜형 숫자형을 문자형으로 변환해주는 함수

SELECT SYSDATE, TO_CHAR(SYSDATE,'YYYY-MM-DD DAY') FROM DUAL;
SELECT HIREDATE, TO_CHAR(HIREDATE, 'YYYY/MM/DD DAY') FROM EMP;
SELECT SYSDATE, TO_CHAR(SYSDATE,'YYYY-MM-DD,HH24:MI:SS') FROM DUAL;
SELECT ENAME, SAL, TO_CHAR(SAL,'L999,999') FROM EMP;

TO_DATE : 문자형만 날짜형으로 변환하는 함수, 연도월일시분초등, 기본형 YY/MM/DD

SELECT SYSDATE FROM DUAL;

SELECT ENAME,HIREDATE FROM EMP
-- WHERE HIREDATE = 19810220; -- 숫자형과 날짜형을 비교, 연상 불가
 WHERE HIREDATE = TO_DATE('19810220','YYYYMMDD');
 
SELECT TRUNC(SYSDATE-TO_DATE(20220101,'YYYYMMDD')) FROM DUAL;

TO_NUMBER : 문자형을 숫자형으로 변환하는 함수

SELECT '20,000'-'10,000' FROM DUAL;
SELECT TO_NUMBER('20,000','99,999')-TO_NUMBER('10,000','99,999') FROM DUAL;

예제

-- 입사일자에서 입사년도를 출력
SELECT TO_CHAR(HIREDATE,'YYYY') FROM EMP;
-- 입사일자를 ‘1999년 1월 1일’ 형식으로 출력
SELECT TO_CHAR(HIREDATE,'""YYYY"년"MM"월"DD"일"') FROM EMP;
-- 1981년도에 입사한 사원 검색
SELECT ENAME, HIREDATE FROM EMP
 WHERE TO_CHAR(HIREDATE,'YYYY') LIKE '1981'; 
-- 5월에 입사한 사원 검색
SELECT ENAME, HIREDATE FROM EMP
 WHERE TO_CHAR(HIREDATE,'MM') LIKE '05'; 
-- 급여 앞에 $를 삽입하고 3자리 마다 ,를 출력
SELECT ENAME, TO_CHAR(SAL,'$999,999') FROM EMP;