🔍 단일행 함수
➰ extract( datetime )
날짜시간 데이터에서 원하는 항목 추출
- YEAR, MONTH, DAY, HOUR, MINUTE, SECOND 항목을 추출할 수 있음
sysdate: YEAR, MONTH, DAY만 적용 가능systimestamp: 전부 적용할 수 있음
SELECT
extract ( YEAR FROM sysdate) "연도"
, extract ( MONTH FROM sysdate) "월"
, extract ( DAY FROM sysdate) "일"
FROM dual;
SELECT
extract ( HOUR FROM systimestamp) "시" --GMT 시간 기준
, extract (TIMEZONE_HOUR FROM systimestamp) "타임존시간" --한국은 GMT +9시
, extract ( MINUTE FROM systimestamp) "분"
, extract ( SECOND FROM systimestamp) "초"
FROM dual;➰ 변환함수
숫자, 문자, 날짜 사이의 형변환을 해주는 함수 ❗ 숫자 날짜간 변환은 없음
👉🏻 to_char( NUMBER )👉🏻
숫자(NUMBER) 문자(VARCHAR2)
👈🏻 to_number( VARCHAR2 ) 👈🏻
👉🏻 to_date( VARCHAR2 ) 👉🏻
문자(VARCHAR2) 날짜(DATE)
👈🏻 to_char( DATE ) 👈🏻▶ to_char(number) / to_char(number, format)
원하는 서식으로 지정하여 숫자를 문자(VARCHAR2)로 변환
- 서식문자
0: 숫자가 들어갈 문자 자리를 확보 / 남은 자리를 0으로 LPAD9: 숫자가 들어갈 문자 자리를 확보 / 남은 자리를 ' '빈칸으로 LPAD
✔ 소수부는 0, 9 상관없이 서식문자가 적으면 반올림, 많으면 0으로 채움.: 소수점 자리 구분 문자 ➡ 정수 자릿수만 맞으면 소수점은 알아서 반올림하여 출력,: 자리수 구분 문자$: 통화기호 $ 추가L: 세계 통화기호 추가 (컴퓨터에 설정된 통화(Locale)을 따름, 한국 : \
▶ to_char(datetime) / to_char(datetime, format)
원하는 서식으로 지정하여 날짜시간을 문자(VARCHAR2)로 변환
SELECT
to_char(sysdate, 'YYYY/MM/DD DAY AM HH24:MI:SS')
, to_char(sysdate, 'YYYY"년" MM"월" DD"일" DAY AM HH24"시" MI"분" SS"초"')
FROM dual;- 서식문자
scc: 세기YEAR, Tear, year: 문자 연도 /YYY, YYY, YY, Y: 숫자연도 ➡ Y의 개수는 표현하는 자릿수MM: 숫자 월 /MONTH, MON: 문자 월Q: 분기DD: 월 기준 날짜 /D: 주 기준 날짜(일요일부터 1, 토요일 7) /DDD: 연 기준 날짜DAY: 요일 /DY: 요일의 약자HH: 12시간 표기법 시간 /HH12: 12시간 표기법 시간 /HH24: 24시간 표기법 시간MI: 분SS: 초AM, PM, A.M., P.M.: 오전 / 오후FF, FF3, FF2, FF1: 6자리 혹은 자릿수 지정으로 밀리초
- 날짜 접미어 서식
th: 순서로 날짜 표시sp: 영문 숫자로 날짜 표시spth: 순서를 영문으로 날짜표시
👉🏻 앞에 오는 서식에 따라 UPPER, lower, Initcap이 적용됨
▶ to_number(VARCHAR2) / to_char(VARCHAR2, format)
원하는 서식으로 지정하여 문자를 숫자로 변환
SELECT
123.456 "숫자"
, '123.456' "문자"
, to_number('123.456') "문자>숫자"
, to_number('123.456', '999999.999999') "format 변환" --더 큰건 받아들이지 않겠다
-- , to_number('123.456', '99.999999') "format 변환" --에러, 정수는 두자리만 허용
-- , to_number('123.456', '99.9') "format 변환" --에러, 소수점은 한자리만 허용
-- , to_char('56,789', '99999999')
, to_number('56,789', '99,99999') --, 콤마는 기본 숫자형식이 아니므로 format에서 맞춰줘야 함 / , 기준으로 앞의 자리수나 뒤의 자리수 하나만 맞춰주면 됨
FROM dual;▶ to_date(VARCHAR2) / to_date(VARCHAR2, format)
원하는 서식으로 지정하여 문자를 날짜로 변환
SELECT
'19/3/4' "문자"
, to_date('19/3/4') "날짜"
, '12 1 11' "문자"
, to_date('12 1 11', 'yy mm dd') "날짜"
FROM dual;➰ NVL(a, b)
a값이 null이라면 b값으로 치환
SELECT empno, ename, sal, comm
, sal + comm pay --null값과 연산시 결과가 null이 됨
, NVL(comm, 0) comm2
, sal + NVL(comm, 0) pay2
FROM emp;➰ NVL(a, b, c)
a값이 null이 아니라면 b값으로, null이라면 c값으로 치환
SELECT empno, ename, sal, comm
, NVL2(comm, sal + comm, sal) pay
FROM emp
WHERE sal IS NOT NULL;➰ DECODE( ) 함수
기준값과 비교값을 지정하고 같은 값일 경우 반환값이 달라지도록 설정
➡ 자바의 SWITCH구문과 비슷한 기능을 수행
- 기준값과 같은 비교값을 찾으면 반환값을 리턴하고, 같은 반환값이 없으면 마지막 기본 반환값을 리턴
👉🏻 기본 반환값 생략 가능 ➡ null값 반환
SELECT empno, ename, deptno
, decode( deptno
, 10, 'ACCOUNTING'
, 20, 'RESEARCH'
, 30, 'SALE'
, 40, 'OPERATIONS'
,'부서없음') dname
FROM emp;➰ CASE구문
DECODE와 비슷한 동작을 하는 구문
CASE-WHEN THEN-END형식으로 작성
👉🏻 WHEN절 마지막으로ELSE로 기본반환값 설정 가능- WHEN절을 여러개 작성하여 조건구문으로 사용할 수 있음
SELECT empno, ename, deptno
, CASE deptno
WHEN 10 THEN '회계팀'
WHEN 20 THEN '연구팀'
WHEN 30 THEN '영업팀'
WHEN 40 THEN '운영팀'
ELSE '부서없음'
END dname
FROM emp;
SELECT empno, ename, deptno
, CASE
WHEN job = 'PRESIDENT' THEN '사장님'
WHEN deptno = 10 THEN '회계팀'
WHEN deptno = 20 THEN '연구팀'
WHEN deptno = 30 THEN '영업팀'
WHEN deptno = 40 THEN '운영팀'
ELSE '부서없음'
END dname
FROM emp;🔍 그룹함수
조회하려는 행들을 그룹화하여 그룹별 결과를 묶음으로 반환하는 함수 👉🏻 그룹화 기준에 따라 결과 개수가 달라짐
-
COUNT: 데이터를 가지고 있는 행의 수를 반환 ➡ NULL은 무시 -
MIN: 최소값 -
MAX: 최대값 -
SUM: 합계 -
AVG: 평균
SELECT count(*) <FROM emp;
SELECT count(comm) FROM emp;
SELECT MIN(sal) FROM emp;
SELECT MAX(hiredate) FROM emp; --날짜는 가장 최근(미래)값이 MAX
SELECT SUM(sal) FROM emp;
SELECT AVG(sal) FROM emp;➰ GROUP BY절
데이터를 원하는 기준에 맞춰 그룹화할 때 사용하는 절
- SELECT절의 그룹함수를 제외한 모든 컬럼들은 GROUP BY에 작성되어야 함
- 컬럼들의 값들이 같은 행(나열된 모든 컬럼의 값이 같은 행)끼리 같은 그룹으로 만들어짐
👉🏻 나열된 컬럼들의 조합에 따라 그룹화된 결과가 달라짐 GROUPING
SELECT deptno, ename, sum(sal)
FROM emp
GROUP BY deptno, ename
ORDER BY deptno;GROUPING: GROUP BY에서 지칭한 것 외에도 소계를 해줌- GROUP BY ROLLUP : 나열된 컬럼들을 뒤에서부터 하나씩 줄여가면서 만든 추가 그룹 집계결과를 전체 결과에 포함시킴
SELECT deptno, job, count(*) cnt, sum(sal) tot_sal FROM emp WHERE deptno IS NOT NULL GROUP BY ROLLUP (deptno, job) --() 그룹, deptno 그룹, 전체 그룹 ORDER BY deptno; - GROUP BY CUBE : 단계적으로 그룹핑하지 않고 모든 그룹 조합의 집계결과를 포함시킴
SELECT deptno, job, count(*) cnt, sum(sal) tot_sal FROM emp WHERE deptno IS NOT NULL GROUP BY CUBE (deptno, job) --() 그룹, deptno 그룹, job 그룹, 전체 그룹 ORDER BY deptno; - GROUP BY GROUPING SETS
SELECT deptno, job, count(*) cnt, sum(sal) tot_sal FROM emp WHERE deptno IS NOT NULL --GROUP BY GROUPING SETS (deptno, job) --deptno 그룹, job 그룹 --GROUP BY GROUPING SETS ( (deptno, job) ) --()그룹 / GROUP BY와 같음 GROUP BY GROUPING SETS ( (deptno, job), deptno, job, () ) --()그룹, deptno 그룹, job 그룹, 전체 그룹 / GROUP BY CUBE와 같음 ORDER BY deptno;
- GROUP BY ROLLUP : 나열된 컬럼들을 뒤에서부터 하나씩 줄여가면서 만든 추가 그룹 집계결과를 전체 결과에 포함시킴
➰ HAVING 절
GROUP BY 절 다음에 오는 조건절 👉🏻 그룹함수를 이용한 조건식을 적용할 수 있음
❗ WHERE절은 GROUP BY절 전에 작성되므로 그룹함수를 사용할 수 없음
SELECT deptno, count(*)
FROM emp
--WHERE count(*) < 5 --그룹함수는 where절에 사용될 수 없음
GROUP BY deptno
HAVING count(*) < 5
ORDER BY deptno;🔍 SELECT 구문의 수행순서
<작성순서> <실행순서>
SELECT 5
FROM 1
WHERE 2
GROUP BY 3
HAVING 4
ORDER BY 6-
FROM절
조회대상 테이블을 설정 -
WHERE절
조회되는 행들을 선택하는 조건식을 적용 -
GROUP BY절
지정된 컬럼으로 그룹화 -
HAVING절
그룹함수를 이용한 조건식을 적용 -
SELECT절
조회하는 컬럼들을 선택(DISTINCT 중복 제거 가능) -
ORDER BY절
결과 집합 정렬
✔ 수행순서에 따라 Alias(별칭)이 적용됨
- SELECT절에서 지정한 컬럼 별칭은 ORDER BY에서만 사용할 수 있음
- FROM절에서 지정한 테이블 별칭은 모든 절에서 사용할 수 있음
IT꿈나무 댓츠미