SQL - 형 변환 / NULL / 선택 / 그룹
형 변환
- 데이터 타입을 변환
TO_CHAR(형 변환)
- 날짜, 숫자 타입의 데이터를 문자 타입의 데이터로 변환
1) 숫자 > 문자
TO_CHAR(NUMBER, FORMAT)
SELECT TO_CHAR(12345, '999999') FROM DUAL;
SELECT TO_CHAR(12345, '000000') FROM DUAL;
FORMAT을 9와 0, 어떤 것으로 지정하느냐에 따라 다르게 출력된다.
0으로 지정할 경우 빈자리는 0으로 채운다.
-
SELECT TO_CHAR(12345, '999,999') FROM DUAL; -- 3자리마다 콤마(,) 표기
SELECT TO_CHAR(12345, 'L999,999') FROM DUAL; -- 통화기호 표기(LOCAL)
SELECT TO_CHAR(12345, '&999,999') FROM DUAL; -- 통화기호 직접 명시
2) 날짜 > 문자
- YY : 연
- MM : 월
- DD : 일
- HH : 시
- MI : 분
- SS : 초
TO_CHAR(DATE, FORMAT)
SELECT TO_CHAR(SYSDATE, 'YY/MM/DD HH:MI:SS') FROM DUAL;
시,분,초 까지 표시 가능.
-
SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS') FROM DUAL; -- 24시간 형태로 표기
SELECT TO_CHAR(SYSDATE, 'AM HH24:MI:SS') FROM DUAL; -- 오전/오후 표기
- 24시간제, 오전/오후 표기도 가능하다.
-
SELECT TO_CHAR(SYSDATE, 'MON DD DY, YYYY') FROM DUAL;
-- 언어 변경
ALTER SESSION SET NLS_LANGUAGE = AMERICAN;
ALTER SESSION SET NLS_LANGUAGE = KOREAN;
날짜 표기의 한글과 영어의 차이점을 볼 수 있다.
프로젝트 내에서 약속된 날짜 형태에 따라 변경하여 사용할 수 있겠다.
-
- 연도 포맷
- Y : 현재 세기
- R : 50 미만일 경우 현재 세기, 50 이상일 경우 이전 세기
- YY : 20 / 18 / 90 > 2020 / 2018 / 2090
- RR : 20 / 18 / 90 > 2020 / 2018 / 1990 : 90이 50 이상이기 때문에 이전 세기로 반영
SELECT TO_DATE('210928', 'YYMMDD') FROM DUAL; -- 2021/09/28
SELECT TO_DATE('980928', 'YYMMDD') FROM DUAL; -- 2098/09/28
SELECT TO_DATE('210928', 'RRMMDD') FROM DUAL; -- 2021/09/28
SELECT TO_DATE('980928', 'RRMMDD') FROM DUAL; -- 1998/09/28
-- 날짜 포맷 변경
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; -- 테스트를 위해 변경
ALTER SESSION SET NLS_DATE_FORMAT = 'RR/MM/DD'; -- 원복
Y와 R의 차이점. 필요에 따라 알맞은 걸로 사용할 수 있다.
TO_NUMBER(형 변환)
- 문자 타입의 데이터를 날짜 타입의 데이터로 변환
TO_NUMBER(CHARACTER, FORTMAT)
SELECT TO_NUMBER('0123456789') FROM DUAL;
문자 타입의 데이터 ‘0123456789’를 문자로 변환한다. 눈으로 확인하기는 쉽지 않았다..!
-
SELECT '123' + '456' FROM DUAL; -- 자동 형 변환
SELECT '123' + 456 FROM DUAL; -- 자동 형 변환
SELECT '123' + '456A' FROM DUAL; -- ERROR
여기서는 문자 타입의 데이터를 연산했을 때, 자동 형 변환이 되어 계산되는 것을 볼 수 있다.
단, 문자가 포함되어 있을 경우 자동 형 변환 되지 않아 ERROR를 출력한다.
NULL(NULL 관련)
- NULL로 되어 있는 컬럼의 값을 인자로 지정한 숫자 혹은 문자로 변경하여 반환
NVL([NULL을 가진 컬럼명], [변환할 숫자 혹은 문자])
BONUS가 NULL로 지정되어 있는 영역을 지정한 값 ‘0.5’로 변환하였다.
-
문자로도 지정이 가능하다. 부서가 없는 사원의 해당 컬럼을 ‘부서 없음’으로 변경했다.
NVL2(NULL 관련)
- NULL 값의 컬럼을 인자로 지정한 숫자 혹은 문자로 변환
- NULL이 아닐 경우 반환값1, NULL일 경우 반환값2로 변경
NVL([NULL을 가진 컬럼명], [변환할 숫자 혹은 문자1], [변환할 숫자 혹은 문자2])
핸드폰 번호 있는 컬럼 : 번호 입력 완료
핸드폰 번호 없는 컬럼 : 번호 미입력
NVL2를 이용하여 두 종류의 데이터 값을 변경할 수 있다.
NULLIF(NULL 관련)
- 두 개의 값이 동일할 경우 NULL, 아닐 경우 비교대상1 반환
NULLIF(비교대상1, 비교대상2)
SELECT NULLIF('123', '123') FROM DUAL; -- NULL
SELECT NULLIF('123', '456') FROM DUAL; -- 123
‘가가가’ / ‘가가가’ : 값이 동일할 경우 NULL을 출력한다.
‘가가가’ / ‘하’ : 다른 값일 경우 첫번째 인자인 ‘가가가’를 출력한다.
DECODE(선택)
- 비교하고자 하는 값 또는 컬럼이 조건식과 같을 경우 결과 값을 반환
- 모든 조건을 만족하지 않을 경우 [DEFAULT] 반환
DECODE([컬럼 OR 값], 조건1, 결과1, 조건2, 결과2, ..., DEFAULT)
아이디가
JAVA > 자바
Oracbl > 오라클로 변경하고
만족하는 조건이 없었던 HTML은 HHHHHH(DEFAULT)로 값을 변경한다.
CASE(선택)
- 비교하고자 하는 값 또는 컬럼이 조건식과 같을 경우 결과 값을 반환
- 마지막에 END 작성 필수
- JAVA의 switch문과 비슷
CASE WHEN 조건1 THEN 결과1
WHEN 조건2 THEN 결과2
WHEN 조건3 THEN 결과3
[ELSE 결과N]
END
만족하는 조건의 값이 있을 경우 지정한 값으로 변환한다.
JAVA > 자바
Oracle > 오라클
HTML > html
SELECT MEMBER_NO AS "번호"
, CASE WHEN MEMBER_ID = 'JAVA' THEN '자바'
WHEN MEMBER_ID = 'Oracle' THEN '오라클'
ELSE 'html'
END AS "아이디"
FROM BLOG;
ELSE를 이용하여 위처럼 작성해도 동일한 결과로 조회된다.
상황에 맞게 알맞은 함수를 사용하여 데이터를 변경하면 될 거 같다 !
그룹 함수
- 하나 이상의 행을 그룹으로 묶어 연산
- 총합, 평균 등을 하나의 컬럼으로 반환
- 모든 그룹 함수는 NULL 값을 제외하고 연산
SUM(그룹)
- 값의 합 반환
SUM(컬럼 OR 값)
SELECT SUM(SALARY) FROM EMPLOYEE;
SELECT SUM(123 + 123) FROM DUAL; -- 246
SELECT SUM('123' + '123') FROM DUAL; -- 246 : 자동 형 변환
AVG(그룹)
- 값의 평균 반환
AVG(컬럽 OR 값)
세번째는 소수점이 넘 더러워서 FLOOR 함수를 사용하여
소수점 아래를 잘라서 조회했다.
MIN / MAX(그룹)
- 최소값 / 최대값 반환
- 데이터가 많을 수록 시간 소모가 많음
MAX(컬럼 OR 값)
MIN(컬럼 OR 값)
COUNT(그룹)
- 컬럼 또는 행의 개수 반환
COUNT(컬럼 OR 값)
‘안녕하세요’ 문자를 만족하는 행의 개수를 반환한다.
-
이번엔 TEXT 컬럼 개수를 조회했다.
모든 그룹함수는 NULL 값을 제외하기 때문에 4가 조회된다.