[Oracle] to_date, to_char

 

dual

: 가상테이블을 의미

 

 

 

CONCAT

: 두 열 합치기

SELECT
	CONCAT(empno, ename),
	CONCAT(empno, CONCAT(':',ename))
FROM emp

 

 

 

LPAD, RPAD

: 삽입

SELECT 
	LPAD('Oracle', 10, '#'), --####Oracle
	RPAD('Oracle', 10, '#'), --Oracle****
	LPAD('Oracle', 10), --    Oracle
	RPAD('Oracle', 10) --Oracle    
FROM dual;

 

 

숫자랑 문자는 +로 합칠 수 없다

• || 를 사용시, 합쳐서 출력됨 SELECT empno, empno+100, ename || '100', ename + '100' FROM EMP;

 

 

TO_CHAR

• 날짜, 숫자 → 문자 데이터
• SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM dual
  • 2022-08-10… 이렇게 문자식으로 출력될것임
  • YYYY(연)/MM(월)/DD(일) HH24(24시간으로 표현한 시간 (15,16…시)):MI(분):SS(초)
  • day : 요일을 알려줌
  • AM, PM : 오전,오후 표시 (시간 앞에 붙여주면 됨. 단, 시간이 오전이면 pm 써도 오전으로 표시됨)
  • HH(12시간으로 표현한 시간)
• TO_CHAR(3456, '00,000'),
  • 0으로 비어있는 부분을 다 채우되 3456을 채우니 03456 이 됨
• SELECT to_char(12345, '999,999'), —12,345
• TO_CHAR(3456, '00,000'), —03,456
• TO_CHAR(11.234, '00.00') —11.23 FROM dual

 

 

TO_NUMBER

• 문자 → 숫자데이터로 변환

SELECT
	'1500' - 100,  --1400. 1,500으로 적을 시 인식 못함
	to_number('1,500', '9,999') - 100,  --1400. 1,500을 9,999 양식에 맞게 숫자로 인식함
	to_number('1,500', '999,999') - 100  --1400
FROM
	dual

 

 

TO_DATE

• 문자 → 날짜 데이터로 변환
• SYSDATE : 현재 날짜

SELECT
	TO_DATE('2022-06-15', 'yyyy-mm-dd') a,
	TO_DATE('2022/08/10', 'YYYY/MM/DD') - TO_DATE('2022-06-15', 'yyyy-mm-dd'), --56. 빼기해서 날짜 계산
	TO_DATE('2022/08/10', 'YYYY/MM/DD') b, --2022-08-10 00:00:00.000
	months_between(TO_DATE('2022-08-10', 'YYYY-MM-DD'), TO_DATE('2022-06-15', 'yyyy-mm-dd')) AS "mon diff"
	--1.8387...
FROM
	dual

 

 

nvl (null 처리함수)

• null인지 아닌지 알아보기

SELECT nvl(comm, 0) FROM emp --comm값이 null인것은 0으로 바꿔주기.

 

 

DECODE 함수

• 모두 CASE문으로 바꿀 수 있음

DECODE([검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과],
[조건1],[데이터가 조건 1과 일치할때 반환할 결과]
...
[위 조건들에 일치하는 경우가 없을 때 반환할 결과])

SELECT job, sal,
	DECODE(job, --job의 값이..
	'MANAGER', sal*1.1,
	'SALESMAN', sal*1.05,
	'ANALYST', sal,
	sal * 1.03) AS upsal --조건을 안쓴거는 DEFAULT 값들.
--조건에 속하지 못한것들 (sal*1.03 실행하라)
FROM
	emp

 

 

 

CASE 문

• DECODE 함수와 역할은 비슷. 더 범용성이 높음.
• decode 보다는 case 문을 많이 씀
• 다른 점은, 기준 데이터를 반드시 명시해야 함.

CASE ([검사 대상이 될 열 또는 데이터, 연산이나 함수의 결과],
WHEN [조건1] THEN [조건 1의 결과값이 true일 때, 반환할 결과]
...
ELSE [위 조건들에 일치하는 경우가 없을 때 반환할 결과]
...)

SELECT job, sal,
	CASE job
		WHEN 'MANAGER' THEN sal*1.1
		WHEN 'SALESMAN' THEN sal*1.05
		WHEN 'ANALYST' THEN sal
		ELSE sal*1.03
	END AS UPSAL -- END 끝남
FROM
	emp

//아래는 예시 이미지
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;

 

 

예제

--Q2
SELECT
	EMPNO,
	ENAME,
	sal,
	TRUNC(sal / 21.5, 2) AS DAY_PAY,
    -- 소수점 삭제
	ROUND((sal / 21.5) / 8 , 1) AS TIME_PAY
    -- 소수점 반올림
FROM emp



--Q3
SELECT
	empno,
	ename,
	TO_CHAR(hiredate,'YYYY-MM-DD'),
	TO_CHAR(NEXT_DAY(ADD_MONTHS(HIREDATE , 3), 2),'YYYY-MM-DD') AS R_JOB,
	-- 3개월 증가 후. 월요일에 해당하는 것 (1은 일요일, 2는 월요일)
	-- 'YYYY-MM-DD' 형식으로 자르기(문자형으로)
	nvl(TO_CHAR(comm), 'N/A') AS COMM
	-- 문자형의 상태에서 null값이면 'N/A' 로 변경
	-- 아래 형태로 해도 됨
	--CASE
	--	WHEN comm IS NULL THEN 'N/A'
	--	WHEN comm IS NOT NULL THEN to_char(comm)
	--END
FROM
	emp

	
	
	

--Q4
SELECT
	empno,
	ename,
	MGR,
	CASE
		WHEN MGR IS NULL THEN '0000' 
		ELSE
			CASE 
				--substr 시작위치는 0이 아니라 1이다
				--앞에서 두번째까지의 자리 체크
				WHEN substr(MGR, 1, 2) = '75' THEN '5555'
				WHEN substr(MGR, 1, 2) = '76' THEN '6666'
				WHEN substr(MGR, 1, 2) = '77' THEN '7777'
				WHEN substr(MGR, 1, 2) = '78' THEN '8888'
				ELSE TO_CHAR(MGR) --문자형으로 반환해야함
			END
		END AS CHG_MGR
FROM
	emp

 

 

 

 

 

다중행 함수

• sum, count, max, min, avg

-- 그 외 함수
--sum 함수 : 해당 열 값의 합을 구해줌
SELECT sum(sal) FROM emp
SELECT deptno = 20
ORDER BY sal


--count 함수 : 해당 열의 갯수를 구함
-- null 값은 카운팅되지 않음
SELECT count(ename) FROM emp
-- ename 값이 있는 곳만 카운트

SELECT count(*) FROM emp
WHERE sal >= 3000
--연봉이 3000 넘는 사람들의 수


SELECT max(sal), min(sal) FROM emp
WHERE DEPTNO = 30
-- max() 괄호값이 최대값인 값
-- min() 괄호값이 최소값인 값

SELECT max(ename), min(ename) FROM emp
-- 첫번째 줄의 값, 제일 마지막 줄의 값
-- 오름차순이면 알파벳 A에 가까운 값이 나오고, 내림차순 정렬이 되있으면 그 반대가 됨

SELECT avg(sal) FROM emp
-- 평균 구하기

 

응용하기

-- 각 부서별로(deptno) 연봉액 총액, 평균, 최대, 최소, 사람수
-- union all : 각 결과를 모두 포함한 합집합
-- 각각 한 줄을 의미함
-- 결과값 사진 1-1
SELECT 10 deptno, sum(sal), floor(avg(sal)), max(sal), min(sal), count(*)
FROM emp
WHERE DEPTNO = 10
UNION ALL
SELECT 20 deptno, sum(sal), floor(avg(sal)), max(sal), min(sal), count(*)
FROM emp
WHERE DEPTNO = 20
UNION ALL
SELECT 30 deptno, sum(sal), floor(avg(sal)), max(sal), min(sal), count(*)
FROM emp
WHERE DEPTNO = 30



-- 결과값 사진 1-2
-- 위 문제와 결과값은 같다
SELECT DEPTNO, sum(sal), floor(avg(sal)), max(sal), min(sal), count(*)
FROM emp
GROUP BY DEPTNO --deptno 그룹별로 한줄씩 나열
ORDER BY DEPTNO -- 오름차순으로 나열



-- 부서별, 직업별, 연봉의 총액... 등 알수있음
SELECT deptno, job, sum(sal), floor(avg(sal)), max(sal), min(sal), count(*)
FROM emp
GROUP BY deptno, job -- 이 둘을 기준으로 SELECT 값을 나열함
ORDER BY DEPTNO



SELECT deptno, job, sum(sal), floor(avg(sal)), max(sal), min(sal), count(*)
FROM emp
WHERE DEPTNO IN (10,20) --DEPTNO가 10,20에 해당되는 것만
GROUP BY deptno, job -- 이 둘을 기준으로 SELECT 값을 나열함
ORDER BY deptno

 

1-1

1-2

 

 

 

 

GROUP BY - HAVING 절

• group by 하려는 컬럼은 모두 select에 명시해주어야 함
• where은 모든 필드를 조건에 둘 수 있고,
  having 은 group by 된 (필드들로 그룹화된) 새 테이블에 조건을 둠
• 둘은 비슷하지만 쓰임새가 조금 다르다. (where을 쓸 수 있는 상황이라면 되도록 where 을 쓰기)

= group by의 where 느낌이라 보면 됨

SELECT Deptno, job, avg(sal)
	FROM emp
GROUP BY DEPTNO, JOB --그룹에 해당하는 것들 중
--	HAVING avg(sal) >= 2000, --avg가 2000 이상이어야함 (출력 그룹을 제한하는 조건식)
-- select 에 들어가는 것들만 사용 가능
-- where 에서 표현할 수 있는 것은 가급적 where에서 표현함
-- 거의 한줄로 표시되는 함수들을 조건으로 주고싶을 때 사용함
	HAVING  avg(sal) <= 3000 AND 2000 <= avg(sal) --avg가 2000~3000 사이의 값일 때
ORDER BY DEPTNO, job;