1. 단일행 함수(2)
1) 날짜 관련 함수
(1) EXTRACT
: 년도 또는 월 또는 일 정보를 추출해서 반환
[표현법] EXTRACT( 날짜 FROM DATE)
=> 결과 값: NUMBER
2) 형변환 함수
: 데이터 타입을 변환시키는 함수들
- TO_CHAR() : NUMBER | DATE => CHARACTER 타입
- TO_DATE() : NUMBER | CHARACTER => DATE 타입
- TO_NUMBER() : CHARACTER => NUMBER 타입
(1) TO_CHAR
: 숫자형 또는 날짜형 데이터를 문자형타입으로 변환
[표현법] TO_CHAR( NUMBER | DATE, (포맷) )
=> 결과 값: CHARACTER
※ 포맷 : 어떤 형식을 지정해주는 것
TO_CHAR에서 포맷 예시
(숫자 -> 문자형)
SELECT TO_CHAR(1234, '00000') FROM DUAL; -- 1234 => '01234' : 빈칸 0으로 채움
SELECT TO_CHAR(1234, '99999') FROM DUAL; -- 1234 => ' 1234' : 빈칸 공백으로 채움
SELECT TO_CHAR(1234, 'L00000') FROM DUAL; -- 1234 => '\01234' : 현재 설정된 나라(LOCAL)의 화폐단위
SELECT TO_CHAR(1234, 'L99999') FROM DUAL; -- 1234 => '\1234'
SELECT TO_CHAR(1234, '$99999') FROM DUAL; -- 1234 => '$1234'
SELECT TO_CHAR(1234, 'L99,999') FROM DUAL; -- 1234 => '\1,234' : , 추가해주기
(날짜 -> 문자형)
SELECT SYSDATE FROM DUAL;
SELECT TO_CHAR(SYSDATE) FROM DUAL; --> '21/02/02'
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL; --> '2021-02-02'
SELECT TO_CHAR(SYSDATE, 'PM HH:MI:SS') FROM DUAL; --> '오전 10:02:30'
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL;
SELECT TO_CHAR(SYSDATE, 'MON DY, YYYY') FROM DUAL;
-- 년도로써 쓸수있는 포맷
SELECT TO_CHAR(SYSDATE, 'YYYY')
, TO_CHAR(SYSDATE, 'RRRR')
, TO_CHAR(SYSDATE, 'YY')
, TO_CHAR(SYSDATE, 'RR')
, TO_CHAR(SYSDATE, 'YEAR')
FROM DUAL;
-- 월로써 쓸 수 있는 포맷
SELECT TO_CHAR(SYSDATE, 'MM')
, TO_CHAR(SYSDATE, 'MON')
, TO_CHAR(SYSDATE, 'MONTH')
, TO_CHAR(SYSDATE, 'RM')
FROM DUAL;
-- 일로써 쓸 수 있는 포맷
SELECT TO_CHAR(SYSDATE, 'D') -- 주 기준 몇일 째
, TO_CHAR(SYSDATE, 'DD') -- 월 기준 몇일 째
, TO_CHAR(SYSDATE, 'DDD') -- 년 기준 몇일 째
FROM DUAL;
-- 요일로써 쓸 수 있는 포맷
SELECT TO_CHAR(SYSDATE, 'DY')
, TO_CHAR(SYSDATE, 'DAY')
FROM DUAL;
-- 2021년 02월 02일(화)
SELECT TO_CHAR(SYSDATE, 'YYYY"년" MM"월" DD"일" (DY)') -- 없는 포맷형식 추가하고 싶으면 ""
FROM DUAL;
(2) TO_DATE
: 문자형 또는 숫자형 타입을 날짜타입으로 변환
[표현법] TO_DATE( NUMBER | CHARACTER , (포맷) )
=> 결과 값: DATE
예시)
* 문자형 => 날짜
SELECT TO_DATE(20210101) FROM DUAL;
SELECT TO_DATE('20210101') FROM DUAL;
SELECT TO_DATE(000101) FROM DUAL; --- 에러
SELECT TO_DATE('000101') FROM DUAL;
SELECT TO_DATE('20100101') FROM DUAL;
SELECT TO_DATE('041030 143021', 'YYMMDD HH24MISS') FROM DUAL;
SELECT TO_DATE('140630', 'YYMMDD') FROM DUAL; -- 2014
SELECT TO_DATE('980630', 'YYMMDD') FROM DUAL; -- 2098년으로 적용
-- TO_DATE 함수를 이용해서 DATE형식으로 변환시 두자리 년도에 대해 YY포맷을 적용시켰을 경우 => 무조건 현재세기
-- 'YY' -> 'RR' 포맷으로 바꿈
-- 'RR' 포맷 : 50년 이상이면 이전세기, 50미만이면 현재세기 (BUT, 정확한건 네자리년도를 그대로 하는것)(3) TO_NUMBER
: 문자형 타입을 숫자형 타입으로 변환
[표현법] TO_NUMBER( CHARACTER , (포맷) )
=> 결과 값: NUMBER
* 문자형 => 숫자
SELECT '123' + '123' FROM DUAL; --> 알아서 자동형변환
SELECT '10,000,000' + '550,000' FROM DUAL;
SELECT TO_NUMBER('10,000,000', '99,999,999') + TO_NUMBER('550,000', '999,999')
FROM DUAL; -- 포맷형식 지정
SELECT TO_NUMBER('0123') --
FROM DUAL;
3) NULL 처리 함수
(1) NVL
: NULL 값을 변환해주는 함수
[표현법] NVL( 컬럼명, 해당 컬럼 값이 NULL일 경우 반환할 결과 값)
NVL( 컬럼, 0 ) => 컬럼의 값이 NULL일 경우 0으로 반환
NVL( 컬럼, '없음' ) => 컬럼의 값이 NULL일 경우 '없음'으로 반환(2) NVL2
: 해당 컬럼 값이 존재할 경우 결과값 1반환, 해당 컬럼값이 NULL일 경우 결과값 2반환
[표현법] NVL2( 컬렴명, 결과값1, 결과값2 )
NVL2( 컬럼, 1, 0 ) => 컬럼 값이 존재하면 1, NULL일 경우 0
NVL2( 컬럼, '완료', '없음' ) => 컬럼 값이 존재하면 '완료', NULL일 경우 '없음'
(3) NULLIF
: 두 개의 값이 일치하면 NULL, 일치하지 않으면 첫번째 값 돌려줌
[표현법] NULLIF( 비교대상 1, 비교대상 2 )
SELECT NULLIF('123', '123') FROM DUAL; => NULL
SELECT NULLIF('123', '456') FROM DUAL; => 123 출력
4) 선택함수
(1) DECODE 함수
: 비교하고자 하는 값 또는 컬럼이 조건식과 같으면 결과 값 반환
[표현법] DECODE ( 비교대상( 컬럼명 | 산술연산 | 함수 ), 조건값1, 결과값1, 조건값2, 결과값2,.... , 마지막 결과값 )
비교대상 <-> 조건값1과 비교 후 결과값 1
비교대상 <-> 조건값2와 비교후 결과값 2
...
맞는 조건 없을 경우 마지막 결과값
- JAVA - SWITCH문과 유사함
switch(비교대상) {
case 조건값 1 : 결과값 1;
case 조건값 2 : 결과값 2; ....
default : 마지막 결과값
(2) CASE WHEN THEN 구문
: 특정 조건 제시시 내 마음대로 조건식 기술 가능
=> 자바로 IF - ELSE IF 문 같은 느낌
[표현법] CASE WHEN 조건식1 THEN 결과값1, WHEN 조건식2 THEN 결과값2 END
2. 그룹 함수
: N개의 값을 읽어서 1개의 결과를 반환 (하나의 그룹별로 함수 실행 결과 반환)
1) 그룹 함수 종류
(1) SUM
: 해당 컬럼 값들의 총 합계를 반환해주는 함수
[표현법] SUM ( 숫자타입 컬럼 )
응용
SELECT SUM ( 총 합계 )
FROM 테이블
WHERE 조건;
=> 해당 (1) 테이블에 (2) 조건에 맞는 컬럼이 (3) 총 합계 구해줌(순서 잘 알아야 함)(2) AVG
: 해당 컬럼 값들의 평균값을 구해서 반환
[표현법] AVG ( 숫자타입 컬럼 )
(3) MIN
: 해당 컬럼값들 중 가장 작은 값(문자열도 가능) 반환
[표현법] MIN( ANY타입 컬럼 )
(4) MAX
: 해당 컬럼값들 중 가장 큰 값 반환
[표현법] MAX ( ANY타입 컬럼 )
(5) COUNT
: 행의 갯수를 세서 반환
- COUNT(*) : 조회결과에 해당하는 모든 행 갯수 다 세서 반환
- COUNT(컬럼명) : 제시한 해당 컬럼 값이 NULL 이 아닌 것만 다 세서 반환
- COUNT(DISTINCT컬럼명) : 해당 컬럼의 중복은 제거한 값을 다 세서 반환
2) GROUP BY절
: 그룹을 묶어줄 기준을 제시할 수 있는 구문 (그룹 정렬)
=> 해당 제시된 기준별로 그룹을 묶을 수 있음!!
(ORDER BY 절은 오름차순, 내림차순 정렬)
여러개의 값들을 하나의 그룹으로 묶어서 처리할 목적으로 사용
[표현법] GROUP BY 컬럼명;
SELECT절에 GROUP 함수를 제외한 나머지 컬럼은 반드시 GROUP BY에 포함되어야 함.
SELECT절에 기록된 컬럼(1,2,3,4개...) = GROUP BY절에 기록된 컬럼(1,2,3,4개 ...)
EX)
SELECT 컬럼 3개, 그룹함수 2개
FROM 테이블
GROUP BY 컬럼 3개;
이렇게 적용하는 방식해야 함.
3) HAVING 절
: 그룹함수에 대한 조건을 제시하고자 할 때 사용되는 구문(그룹함수를 가지고 조건 제시)
(그룹 함수에 대해서는 WHERE 조건을 제시할 수 없음)
[표현법] HAVING 조건;
※ 작성순서
1) SELECT절 ( * | 조회하고자 하는 칼럼명 | 산술연산식 | 함수식 ) AS "별칭"
2) FROM 테이블
3) WHERE 조건식
4) GROUP BY 그룹 기준에 해당하는 컬럼명 | 함수식
5) HAVING 그룹함수식에 대한 조건식
6) ORDER BY 정렬기준이 되는 컬럼명 | 별칭 | 컬럼 순번 (ASC / DESC) (NULLS FIRST / LAST)
※ 실행 순서
2) FROM ~ 5) HAVING -> 1) SELECT 컬럼 -> 6) ORDER BY 컬럼
