반응형
목차
REPLACE
- 특정 문자열 데이터에 포함된 문자를 다른 문자로 대체할 경우
REPLACE([문자열 데이터 또는 열 이름(필수)],[찾는 문자],[대체할 문자(선택)])
- 선택 하지 않으면 해당 문자를 없앤다.
SELECT '010-1234-5678' AS REPLACE_BEFORE,
REPLACE('010,1234,5678','-',' ') AS REPLACE_1,
REPLACE('010,1234,5678','-') AS REPLACE_2,
REPLACE('010,1234,5678','-','') AS REPLACE_3
FROM DUAL;
출력 결과
REPLACE_1 REPLACE_2 REPLACE_3
010-1234-5678 010,1234,5678 010,1234,5678
데이터의 빈 공간을 채우는 함수
- 데이터와 자릿수를 지정한 후 데이터 길이가 지정한 자릿수보다 작을 경우 나머지 공간을 특정 문자로 채움
- LPAD
- 왼쪽 패딩
- RPAD
- 오른쪽 패딩
SELECT RPAD('971225-',14,'*') AS R_PAD,
RPAD('010-1234-','13','*') AS L_PAD
FROM DUAL;
출력 결과
971225-******* 010-1234-****
두 문자열 데이터를 합치는함수
- 두 개의 문자열 데이터를 하나로 연결
- CONCAT
- 문자열 데이터를 연결하는 || 연산자
SELECT CONCAT(EMPNO,ENAME),
CONCAT(EMPNO, CONCAT(':',ENAME)),
CONCAT(CONCAT(EMPNO,':'), ENAME),
EMPNO || ':' || ENAME
FROM EMP
WHERE ENAME = 'SCOTT';
출력 결과
7788SCOTT 7788:SCOTT 7788:SCOTT 7788:SCOTT
특정 문자를 지우는 함수
- 문자열 데이터 내에서 특정 문자를 지우기 위해 사용
TRIM([삭제 옵션(선택)] [삭제할 문자(선택)] FROM [원본 문자열 데이터(필수)]
- 매개 변수가 없는 TRIM
SELECT '[' || TRIM(' __Oracle__ ') || ']' AS TRIM,
'[' || TRIM(LEADING FROM ' __Oracle__ ') || ']' AS TRIM_LEADING,
'[' || TRIM(TRAILING FROM ' __Oracle__ ') || ']' AS TRIM_TRAILING,
'[' || TRIM(BOTH FROM ' __Oracle__ ') || ']' AS TRIM_BOTH
FROM DUAL;
출력 결과
[__Ora cle__] [__Oracle__ ] [ __Oracle__] [__Oracle__]
- 매개 변수가 있는 TRIM
SELECT '[' || TRIM('_' FROM '_ _Oracle_ _') || ']' AS TRIM,
'[' || TRIM(LEADING '_' FROM '_ _Oracle_ _') || ']' AS TRIM_LEADING,
'[' || TRIM(TRAILING '_' FROM '_ _Oracle_ _') || ']' AS TRIM_TRAILING,
'[' || TRIM(BOTH '_' FROM '_ _Oracle_ _') || ']' AS TRIM_BOTH
FROM DUAL;
출력 결과
[ _Oracle_ ] [ _Oracle_ _] [_ _Oracle_ ] [ _Oracle_ ]
- LTRIM, RTRIM
- 여러 문자열 공백 삭제
숫자 데이터를 연산하고 수치를 조정하는 숫자 함수
- ROUND
- 지정된 숫자의 특정 위치에서 반올림한 값을 반환
- TRUNC
- 지정된 숫자의 특정 위치에서 버림한 값을 반환
- CEIL
- 지정된 숫자보다 큰 정수 중 가장 작은 정수를 반환
- FLOOR
- 지정된 숫자보다 작은 정수 중 가장 큰 정수를 반환
- MOD
- 지정된 숫자를 나눈 나머지 값을 반환
특정 위치에서 반올림하는 함수
- 특정 숫자를 반올림하되 반올림할 위치를 지정할 수 있음
- 지정하지 않으면 첫째 자리에서 반올림한 결과 반환
SELECT ROUND(1234.5678) AS ROUND,
ROUND(1234.5678, 0) AS ROUND_0,
ROUND(1234.5678, 1) AS ROUND_1,
ROUND(1234.5678, 2) AS ROUND_2,
ROUND(1234.5678, -1) AS ROUND_MINUS1,
ROUND(1234.5678, -2) AS ROUND_MINUS2
FROM DUAL;
출력 결과
1235 1235 1234.6 1234.57 1230 1200
특정 위치에서 버리는 TRUNC 함수
- 지정된 자리에서 숫자 버림 처리
- 지정하지 않으면 첫째 자리에서 버림 처리
SELECT TRUNC(1234.5678) AS TRUNC,
TRUNC(1234.5678, 0) AS TRUNC_0,
TRUNC(1234.5678, 1) AS TRUNC_1,
TRUNC(1234.5678, 2) AS TRUNC_2,
TRUNC(1234.5678, -1) AS TRUNC_MINUS1,
TRUNC(1234.5678, -2) AS TRUNC_MINUS2
FROM DUAL;
출력 결과
1234 1234 1234.5 1234.56 1230 1200
지정한 숫자와 가까운 정수를 찾는 함수
- CEIL과 FLOOR는 각각 입력된 숫자와 가장 가까운 큰 정수, 작은 정수를 반환한다.
- 정수만 가능하다는 점에서 FLOOR와 ROUND는 다르다.
SELECT CEIL(3.14),
FLOOR(3.14),
CEIL(-3.14),
FLOOR(-3.14)
FROM DUAL;
출력 결과
4 3 -3 -4
숫자를 나눈 나머지 값을 구하는 함수
- MOD
SELECT MOD(15,6),
MOD(10,2),
MOD(11,2)
FROM DUAL;
출력 결과
3 0 1
날짜 데이터를 다루는 날짜 함수
- 연산
- 날짜 데이터 +- 숫자
- 날짜 데이터보다 숫자만큼 일수 이후/이전의 날짜
- 날짜 데이터 - 날짜 데이터
- 두 날짜 데이터 간의 일수 차이
- 날짜 데이터 + 날짜 데이터
- 연산 불가
- 날짜 데이터 +- 숫자
SELECT SYSDATE AS NOW,
SYSDATE-1 AS YESTERDAY,
SYSDATE+1 AS TOMORROW
FROM DUAL;
출력 결과
25/04/22 25/04/21 25/04/23
- 날짜를 빼줄 때에는 TRUNC로 소수점을 버려주어야 한다.
SELECT
TRUNC(to_date('2025-10-01') - sysdate)
FROM DUAL;
출력 결과
161
몇 개월 이후 날짜를 구하는 함수
- ADD_MONTHS
- 윤년도 계산해주기 때문에 유용하게 쓰임
SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 3)
FROM DUAL;
출력 결과
25/04/22 25/07/22
- 직원들의 10주년을 계산
SELECT EMPNO, ENAME, HIREDATE,
ADD_MONTHS(HIREDATE, 120) AS WORK10YEAR
FROM EMP;
출력 결과
7369 SMITH 80/12/17 90/12/17
7499 ALLEN 81/02/20 91/02/20
7521 WARD 81/02/22 91/02/22
7566 JONES 81/04/02 91/04/02
7654 MARTIN 81/09/28 91/09/28
7698 BLAKE 81/05/01 91/05/01
7782 CLARK 81/06/09 91/06/09
7788 SCOTT 87/04/19 97/04/19
7839 KING 81/11/17 91/11/17
7844 TURNER 81/09/08 91/09/08
7876 ADAMS 87/05/23 97/05/23
7900 JAMES 81/12/03 91/12/03
7902 FORD 81/12/03 91/12/03
7934 MILLER 82/01/23 92/01/23
두 날짜 간의 개월 수 차이를 구하는 함수
- MONTHS_BETWEEN
SELECT EMPNO, ENAME, HIREDATE, SYSDATE,
MONTHS_BETWEEN(HIREDATE, SYSDATE) AS MONTHS1,
MONTHS_BETWEEN(HIREDATE, SYSDATE) AS MONTHS2,
TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS MONTH3
FROM EMP;
출력 결과
7369 SMITH 80/12/17 25/04/22 -532.176719683393070489844683393070489845 -532.176719683393070489844683393070489845 532
7499 ALLEN 81/02/20 25/04/22 -530.07994548984468339307048984468339307 -530.07994548984468339307048984468339307 530
7521 WARD 81/02/22 25/04/22 -530 -530 530
7566 JONES 81/04/02 25/04/22 -528.660590651135005973715651135005973716 -528.660590651135005973715651135005973716 528
7654 MARTIN 81/09/28 25/04/22 -522.821880973715651135005973715651135006 -522.821880973715651135005973715651135006 522
7698 BLAKE 81/05/01 25/04/22 -527.692848715651135005973715651135005974 -527.692848715651135005973715651135005974 527
7782 CLARK 81/06/09 25/04/22 -526.434784199522102747909199522102747909 -526.434784199522102747909199522102747909 526
7788 SCOTT 87/04/19 25/04/22 -456.112203554360812425328554360812425329 -456.112203554360812425328554360812425329 456
7839 KING 81/11/17 25/04/22 -521.176719683393070489844683393070489845 -521.176719683393070489844683393070489845 521
7844 TURNER 81/09/08 25/04/22 -523.467042264038231780167264038231780167 -523.467042264038231780167264038231780167 523
7876 ADAMS 87/05/23 25/04/22 -454.983171296296296296296296296296296296 -454.983171296296296296296296296296296296 454
7900 JAMES 81/12/03 25/04/22 -520.628332586618876941457586618876941458 -520.628332586618876941457586618876941458 520
7902 FORD 81/12/03 25/04/22 -520.628332586618876941457586618876941458 -520.628332586618876941457586618876941458 520
7934 MILLER 82/01/23 25/04/22 -518.983171296296296296296296296296296296 -518.983171296296296296296296296296296296 518
돌아오는 요일, 달의 마지막 날짜를 구하는 함수
- NEXT_DAY
- 돌아오는 요일의 날짜를 출력
- LAST_DAY
- 달의 마지막 날짜를 출력
SELECT SYSDATE,
NEXT_DAY(SYSDATE, '월요일'),
LAST_DAY(SYSDATE)
FROM DUAL;
출력 결과
25/04/22 25/04/28 25/04/30
랭킹 함수
- ROW_NUMBER
- 중복된 순위 없이 차례대로 순위를 리턴
- RANK
- 같은 순위가 있을 경우 동률로 처리하고, 그 다음 순위를 뛰어 넘음
- DENSE_RANK
- 같은 순위는 같게 표현하고, 뛰어넘지 않음
SELECT
ename, sal,
row_number() over (order by sal desc) as row_number,
rank() over (order by sal desc) as rank,
dense_rank() over (order by sal desc) as dense_rank
from emp;
출력 결과
ENANE SAL ROW_NUMBER RANK DENSE_RANK
KING 5000 1 1 1
FORD 3000 2 2 2
SCOTT 3000 3 2 2
JONES 2975 4 4 3
BLAKE 2850 5 5 4
CLARK 2450 6 6 5
ALLEN 1600 7 7 6
TURNER 1500 8 8 7
MILLER 1300 9 9 8
WARD 1250 10 10 9
MARTIN 1250 11 10 9
ADAMS 1100 12 12 10
JAMES 950 13 13 11
SMITH 800 14 14 12
자료형을 변환하는 형 변환 함수
- 저장할 데이터의 자료형을 변환
- 종류
- TO_CHAR
- TO_NUMBER
- TO_DATE
- TO_CHAR
SELECT
EMPNO, ENAME, EMPNO + '500'
FROM EMP
WHERE ENAME = 'SCOTT';
또는
SELECT
EMPNO, ENAME, EMPNO + to_number('500')
FROM EMP
WHERE ENAME = 'SCOTT';
출력 결과
// 문자 데이터인 '500'이 자동 형 변환 됨
7788 SCOTT 8288
- 날짜 포매팅
SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일"')
FROM DUAL;
출력 결과
2025년 04월 22일
- TO_NUMBER
- 1,300 + 2,000 같은 연산을 할 때에는( 정해진 포매팅 형식이 있는 경우) 직접 알려주어야 함
SELECT 1700 - '1500',
'1700' - 1500
FROM DUAL;
SELECT TO_NUMBER('1,700','999,999') - TO_NUMBER('1,500','999,999')
FROM DUAL;
출력 결과
200 200
Null 처리 함수
- NVL
- NULL이 사용될 경우 다른 값으로 대체할 수 있음
SELECT EMPNO, ENAME, SAL, COMM, SAL + COMM, NVL(COMM, 0), SAL+NVL(COMM,0) FROM EMP; 출력 결과 7369 SMITH 800 0 800 7499 ALLEN 1600 300 1900 300 1900 7521 WARD 1250 500 1750 500 1750 7566 JONES 2975 0 2975 7654 MARTIN 1250 1400 2650 1400 2650 7698 BLAKE 2850 0 2850 7782 CLARK 2450 0 2450 7788 SCOTT 3000 0 3000 7839 KING 5000 0 5000 7844 TURNER 1500 0 1500 0 1500 7876 ADAMS 1100 0 1100 7900 JAMES 950 0 950 7902 FORD 3000 0 3000 7934 MILLER 1300 0 1300 - NVL2
- NVL과 비슷하지만 데이터가 NULL이 아닐 때 반환할 데이터를 추가로 지정해 줄 수 있음
SELECT EMPNO, ENAME, COMM, NVL2(COMM, 'O', 'X'), NVL2(COMM, SAL*12COMM. SAL*12) AS ANNSAL FROM EMP; 출력 결과 EMPNO ENAME COMM 7369 SMITH X 9600 7499 ALLEN 300 O 19500 7521 WARD 500 O 15500 7566 JONES X 35700 7654 MARTIN 1400 O 16400 7698 BLAKE X 34200 7782 CLARK X 29400 7788 SCOTT X 36000 7839 KING X 60000 7844 TURNER 0 O 18000 7876 ADAMS X 13200 7900 JAMES X 11400 7902 FORD X 36000 7934 MILLER X 15600
상황에 따라 다른 데이터를 반환하는 함수와 CASE문
- 특정 열 값이나 데이터 값에 따라 어떤 데이터를 반환할지 정할 수 있는 함수
- DECODE 내에 DECODE를 넣어 중첩 할 수도 있다.
- 직책에 따른 급여 인상 출력
SELECT EMPNO, ENAME, JOB, SAL,
DECODE(JOB,
'MANAGER' , SAL * 1.1,
'SALESMAN', SAL * 1.05,
'ANALYST', SAL,
SAL * 1.03) AS UPSAL
FROM EMP;
출력 결과
7369 SMITH CLERK 800 824
7499 ALLEN SALESMAN 1600 1680
7521 WARD SALESMAN 1250 1312.5
7566 JONES MANAGER 2975 3272.5
7654 MARTIN SALESMAN 1250 1312.5
7698 BLAKE MANAGER 2850 3135
7782 CLARK MANAGER 2450 2695
7788 SCOTT ANALYST 3000 3000
7839 KING PRESIDENT 5000 5150
7844 TURNER SALESMAN 1500 1575
7876 ADAMS CLERK 1100 1133
7900 JAMES CLERK 950 978.5
7902 FORD ANALYST 3000 3000
7934 MILLER CLERK 1300 1339
- MANAGER가 아닌 사람들 중에 DEPTNO가 10이면 1.5배 인상
SELECT
DECODE (JOB,
'MANAGER', SAL*1.1,
DECODE(DEPTNO, 10, SAL * 1.5, SAL)
) AS UPSAL
FROM EMP;
출력 결과
UPSAL
800
1600
1250
3272.5
1250
3135
2695
3000
7500
1500
1100
950
3000
1950
SWITCH
- DECODE와 마찬가지로 특정 조건에 따라 반환할 데이터를 설정할 때 사용
- 열 기준 데이터가 없다면 DECODE대신 CASE 사용
SELECT EMPNO, ENAME, COMM,
CASE
WHEN COMM IS NULL THEN '해당사항 없음'
WHEN COMM = 0 THEN '수당없음'
WHEN COMM > 0 THEN '수당 : '|| COMM
END AS COMM_TEXT
FROM EMP;
출력 결과
7369 SMITH 해당사항 없음
7499 ALLEN 300 수당 : 300
7521 WARD 500 수당 : 500
7566 JONES 해당사항 없음
7654 MARTIN 1400 수당 : 1400
7698 BLAKE 해당사항 없음
7782 CLARK 해당사항 없음
7788 SCOTT 해당사항 없음
7839 KING 해당사항 없음
7844 TURNER 0 수당없음
7876 ADAMS 해당사항 없음
7900 JAMES 해당사항 없음
7902 FORD 해당사항 없음
7934 MILLER 해당사항 없음
하나의 열에 출력 결과를 담는 다중행 함수
- NULL은 제외하고 계산
- SUM
- 해당 열 값을 모두 더함
- DISTICT
- 같은 결과 값은 1번만 계산
- ALL
- 디폴트 결과와 같음
SELECT SUM(SAL)
FROM EMP;
출력 결과
29025
- COUNT
- 데이터 개수를 출력
-
- 사용 시 SELECT 결과의 행 수 만큼 개수를 반환
- DISTICT
- 중복되 행을 제거한 개수를 반환
- ALL
- 디폴트 결과와 같음
SELECT COUNT(*)
FROM EMP;
출력 결과
14
- MAX
- 최대 값을 출력
- MIN
- 최솟값을 출력
- AVG
- 평균 값을 출력
- DISTICT
- 중복 값을 제거한 평균 값
- ALL
- 디폴트 결과와 같음
- DISTICT
- 평균 값을 출력
- ORA-00937 오류 (NOT A SINGLE GROUP FUNCTION)
- 다중행 함수는 기본적으로 여러 행이 나오는 함수와 같이 사용할 수 없다.
- 이름과 합계를 같이 출력해도, 이름은 N개인 반면, SUM은 무조건 1개 이므로, 의미가 없다.
- GROUP BY 혹은 집계 함수를 이용해야 한다.
SELECT ENAME, SUM(SAL) FROM EMP; 출력 결과 ORA-00937: not a single-group group function <https://docs.oracle.com/error-help/db/ora-00937/00937>. 00000 - "not a single-group group function" *Cause: A SELECT list cannot include both a group function, such as AVG, COUNT, MAX, MIN, SUM, STDDEV, or VARIANCE, and an individual column expression, unless the individual column expression was included in a GROUP BY clause. *Action: Drop either the group function or the individual column expression from the SELECT list or add a GROUP BY clause that includes all individual column expressions listed. 1행, 8열에서 오류 발생
결과 값을 원하는 열로 묶어 출력
- GROUP BY
- 여러 데이터에서 의미 있는 하나의 결과를 특정 열 값 별로 묶어서 출력 (그룹화)
- 별칭이 인식되지 않음
SELECT AVG(SAL), DEPTNO
FROM EMP
GROUP BY DEPTNO;
출력 결과
1566.666666666666666666666666666666666667 30
2175 20
2916.666666666666666666666666666666666667 10
GROUP BY절에 조건을 줄 때 사용하는 HAVING절
- HAVING
- SELECT문에 GROUP BY절이 존재할 때만 사용 가능
- HAVING은 GROUP BY 후에 실행
- WHERE는 GROUP BY 전에 실행
SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
GROUP BY DEPTNO, JOB
HAVING AVG(SAL) >= 2000
ORDER BY DEPTNO, JOB;
출력 결과
10 MANAGER 2450
10 PRESIDENT 5000
20 ANALYST 3000
20 MANAGER 2975
30 MANAGER 2850
그룹화 관련 함수
- GROUP BY절에 지정할 수 있는 특수 함수
- ROLLUP
- 명시한 열을 소그룹부터 대그룹의 순서로 각 그룹별 결과를 출력하고 마지막에 총 데이터의 결과 출력
- CUBE
- 지정한 모든 열에서 가능한 조합의 결과를 모두 출력
메모
- ~자리로 반올림 ≠ ~자리에서 반올림
- 날짜의 반올림, 버림은 ROUND와 TRUNC로 할 수 있다.
- 코딩테스트 날짜 포매팅 직접 해주기
- YYYY-MM-DD는 많이 쓰이는 포매팅
SELECT
TO_DATE('2020-12-12','YYYY-MM-DD') DATE1,
TO_DATE('20201212','YYYY-MM-DD') DATE2,
--TO_DATE('2020/12/12','YYYYMMDD') DATE3,
TO_DATE('20201212','YYYYMMDD') DATE4
FROM DUAL;
출력 결과
20/12/12 20/12/12 20/12/12
- 대부분 문자는 왼쪽, 숫자는 오른쪽 정렬
SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY ROLLUP(DEPTNO,JOB); -- 부서별, 직책별 합계
출력 결과
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
10 8750
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
20 10875
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
30 9400
29025
SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY GROUPING SETS(DEPTNO, JOB); -- 부서별, 직책별, 별도 합계
출력 결과
30 9400
20 10875
10 8750
CLERK 4150
SALESMAN 5600
PRESIDENT 5000
MANAGER 8275
ANALYST 6000
SELECT DEPTNO, JOB, SUM(SAL) FROM EMP GROUP BY CUBE(DEPTNO, JOB); -- 부서, 직책 조합별 합계
출력 결과
29025
CLERK 4150
ANALYST 6000
MANAGER 8275
SALESMAN 5600
PRESIDENT 5000
10 8750
10 CLERK 1300
10 MANAGER 2450
10 PRESIDENT 5000
20 10875
20 CLERK 1900
20 ANALYST 6000
20 MANAGER 2975
30 9400
30 CLERK 950
30 MANAGER 2850
30 SALESMAN 5600
- ~별로 라는 키워드는 GROUP BY
- 날짜를 가져올 때 between을 쓴다면 to_char을 사용할 경우 해당 당일의 날짜는 포함하지 않기 때문에 구간의 마지막 날짜를 +1 해준다.
- 보통 숫자 함수는 단일 행 함수
- power - 제곱
- sqrt - 제곱근
728x90
반응형
'Database' 카테고리의 다른 글
| 4/24 - DDL (0) | 2025.04.27 |
|---|---|
| 4/23 - 조인, 서브 쿼리 (0) | 2025.04.27 |
| 4/21 - SQL 데이터 정의어, 오라클 함수 (0) | 2025.04.27 |
| 4/18 - 데이터 베이스, 관계형 데이터 베이스 (0) | 2025.04.19 |
| 4/17 - 데이터베이스 시작 (0) | 2025.04.19 |
