Oracle 3일차 : 내장함수

숫자함수

함수	반환값
abs(n)	절댓값
ceil(n)	올림
floor(n)	내림
mod(m, n)	m을 n으로 나눈 나머지
power(m, n)	m의 n승
round(n, [m])	n값의 반올림, m은 소숫점아랫자리수
trunc(n, m)	n값을 m 소숫점 아래로 내림
sign(n)	양수/음수/0 식별함양수>1 , 음수>-1 , 0>0

🐣 실습하기

SELECT ROUND(345670, -2) FROM DUAL;
SELECT sum(salary), floor(avg(salary)) FROM employees;
SELECT max(salary), min(salary) FROM employees;
SELECT job_id, sum(salary), floor(avg(salary)) FROM employees ORDER BY 3;

문자열 함수

함수	반환값
concat(‘문자1’, ‘문자2’)	두 문자를 더하여 출력
lower(char)	모두 소문자로 변환
upper(char)	모두 대문자로 변환
lpad(char1, n, [char2])	왼쪽에 문자열을 끼워넣는 역할을 함(n은 전체 길이)
rpad(char1, n, [char2])	오른쪽에 문자열을 끼워넣는 역할을 함(n은 전체 길이)
substr(char, m, [n])	char에서 m번째부터 n개의 문자열을 추출 (첫번째=1)만일 m이 음수면 뒤에서부터 센다n생략시 m번째부터 끝까지 추출
length(char)	길이 출력
replace(char, str1, str2)	char에서 str1을 str2로 치환
instr(str, char, [m, n])	char가 str에 포함된 위치를 반환/없을경우0 / 있을경우 해당 인덱스(시작점) 반환 /m, n 생략시 첫번째로 나오는 char의 위치 반환
trim(char from str)	앞뒤의 특정 문자를 제거한다.제거할 문자 미기재 시(trim(str)) 공백만 제거됨

🐣 실습하기

SELECT SUBSTR('have a nice day', -3, 3) FROM DUAL; --day가 출력됨

SELECT hire_date 입사일, SUBSTR(hire_date, 4, 2)  입사월 FROM employees;
--hire_date의 형태가 yy/mm/dd이므로 네번째부터 두글자를 세주면 입사월이 출력됨\
				
SELECT LENGTH(TRIM(' ' FROM '   have   ')) FROM DUAL; --앞뒤 공백이 제거됨
SELECT INSTR('miller', 'l', 1, 2) FROM DUAL; 
--첫번째 글자부터 l을 검색하여 두번째 l의 위치를 반환함
SELECT INSTR('have a nice day', 'a', -1, 1) FROM DUAL;
--끝에서 첫번째로 'a'가 나오는 위치를 반환함 -> 14
			
SELECT RPAD(SUBSTR('910511-2234113', 1,8),14,'*') FROM DUAL;
--주민번호 뒷6자리를 *로 처리하기

날짜 함수

함수	반환값
last_day(‘yyyy-mm-dd’)	달의 마지막 날을 구함
add_months(‘yyyy-mm-dd’, m)	특정 개월수를 더한 날짜를 계산하여 반환(음수도 가능)
to_char(date, ‘format’)	date 타입이나 number 타입을 varchar2 타입으로 변환
to_date(str)	문자타입을 날짜타입으로 변환
months_between(date1, date2)	date1과 date2 사이의 달 수를 number타입으로 반환소숫점 달고나오면 round로 처리하자
to_number(str)	숫자형태의 문자타입을 숫자타입으로 변환(JS의 eval과 비슷)
next_day(date, str)	str 에는 ‘월’ ,’화’,… 등을 넣는다 숫자로 쓸때는 일=1

날짜 출력 형식

형식	출력값
yyyy	년도표현 4자리
yy	년도표현 2자리
mm	숫자 월
mon	알파벳 월
day	요일
dy	요일 약자
dd	숫자 일

시간 출력 형식

형식	출력값
am, pm	오전, 오후 표시(아무거나 써도됨)
hh, hh12	시간 (12시간)
hh24	시간 (24시간)
mi	분
ss	초

🐣 실습하기

SELECT to_char(sysdate, 'yyyy-mm-dd') FROM dual; --2019-11-20
SELECT to_char(sysdate, 'yyyy-mm-dd am hh:mi:ss') FROM dual; --2019-11-20 오후 12:30:12
SELECT to_char(sysdate, 'yyyy-mm-dd hh24:mi') FROM dual; --2019-11-20 12:30
SELECT to_char(sysdate, 'yyyy-mm-dd day dy') FROM dual; --2019-11-20 수요일 수
SELECT to_char(sysdate, 'mon') FROM dual; --11월
SELECT to_char(sysdate, 'yy-mm-dd') FROM dual; --19-11-20
		
--'1989-02-20'일 생의 나이를 구하라 (한국 나이)
SELECT to_char(sysdate, 'yyyy')-to_char(to_date('1989-02-20'), 'yyyy')+1 "나이" FROM dual;
			
--employees에 hire_date가 생년월일이라고 치고 나이구하기
SELECT last_name||' '||first_name "이름", to_char(sysdate, 'yyyy')-to_char(hire_date, 'yyyy') "나이" FROM employees;

조건함수

함수명	출력값
greatest(값1, 값2, …)	가장 큰 값 구하기 (가로방향만 가능)
least(값1, 값2, …)	가장 작은 값 구하기 (가로방향만 가능)
case 컬럼명 when if1 then then1 when if2 then then2 else then3 end	decode와 같은 용도
case when 컬럼명&조건 then1 when…. end	when 이하에 조건식을 줄 수 있다

🐣 실습하기

--각자의 과목들 중 최고점수와 최저점수를 구한다
SELECT name, greatest(java,toeic,script) FROM student;
SELECT name, least(java,toeic,script) FROM student;

그룹함수

• 그룹함수란 여러행 또는 테이블 전체에 대하여 함수가 적용되어 하나의 결과값을 가져오기 위한 함수들이다.

• group by 절을 이용하여 그룹별로 결과가 주어지도록 할 수 잇다.

• having 절을 사용하여 그룹함수를 가지고 조건 비교를 할 수 있다.

• 일반 조건과 그룹조건이 같이 나올 경우 순서는: select > where > group by > having > order by

함수	반환값
count(컬럼명)	검색된 행의 수 반환컬럼명 대신 *쓰면 전체행 갯수 반환
max(컬럼명)	최댓값
min(컬럼명)	최솟값
avg(컬럼명)	평균값
sum(컬럼명)	합계구하기
stddev(컬럼명)	표준편차 구하기

🐣 실습하기

--전공별 인원 수 구하기
SELECT major 전공, COUNT(*) 전공별인원수 FROM student GROUP BY major;
	
-- 각 과목별 최댓값과 최솟값을 구하라
SELECT max(java) 자바최고점, min(java) 자바최저점 FROM student;
			
-- 각 전공별로 각 과목의 최고점을 구하라
SELECT major ,max(java) 자바최고점, max(toeic) 토익최고점, max(script) 스크립트최고점 FROM student GROUP BY major;
			
--각 전공별 평균나이 구하기
SELECT major,round(avg(age)) 평균나이 FROM student GROUP BY major;

SELECT job_id 직업 max(salary) 최대연봉, min(salary) 최소연봉 FROM employees GROUP BY job_id;
SELECT department_id, sum(salary) FROM employees WHERE department_id IN(30,50,60,100) GROUP BY department_id HAVING sum(salary)>=90000 ORDER BY 1;