# SQL 함수
단일행함수와 복수행함수로 나눔
# 단일행 함수
각각 행에적용
데이터값을 조작,인수()를 받아들여 한개의값을 리턴 ,데이터 타입을 변환
# 문자처리함수
LOWER(컬럼명 or 스트링) : 소문자로변경
UPPER(컬럼명 or 스트링) : 대문자로변경
INITCAP(컬럼명 or 스트링) : 알파벳단어의 첫글자는대문자로 나머지는 소문자로
CONCAT(컬럼명 or 스트링 , 합칠컬럼명 or 합칠스트링 ) : 문자열합쳐줌
( 합성연산자 || 해도됨)
( 오라클은 처음이 1번... 0번아님! 즉, index가 1부터 시작 )
SUBSTR(컬럼명 or 스트링, m , n ) : m의위치에서 n길이만큼 문자열을 잘라리턴!
LENGTH(컬럼명 or 스트링) : 문자열의 길이를 리턴
INSTR(컬럼명 or 스트링, 찾고자하는문자) : 문자열컬럼내에서 찾고자하는문자의
시작위치값을리턴 (없으면 0 리턴)
LPAD(컬럼명 or 스트링, m ,'문자') : 문자열 전체의 길이가 m 이되도록 문자열의 왼쪽에
지정한 '문자'를 채워준다
RPAD(칼럼명또는 스트링, M ,'문자') : 문자열 전체의 길이가 m 이되도록 문자열의 오른쪽
에 '문자'를 채워준다
ex> select lpad(sal,10,'#') from emp;
REPLACE(칼럼명또는 문자열, '대체하고자하는문자열', '변환할문자열' )
문자열의 특정부분을 찾아 일치하면 변환할 문자열로 변환함
ex)SELECT REPLACE('12345678-11(반려)','(반려)','') FROM DUAL;
# 숫자처리함수
ROUND(칼럼명 또는 숫자값,N)-->숫자값을 소수점 N의 위치까지 반올림
EX> ROUND(45.925,2)=45.93
TRUNC(칼럼명또는숫자값,N)-->숫자값을 소수점N의 위치아래까지잘라냄
EX> TRUNC(45.925,2)=45.92
FLOOR(칼럼명또는 숫자값)-->소수점 잘라버리고 정수값만 리턴
ex> floor(45.925)=45
ceil(칼럼명또는 숫자값)-->소수점이 있으면 올림하고 정수값만 리턴
ex> ceil(45.925)=46
ceil(45.05)=46
ceil(45.00)=45
#인자값이없는 함수( () 생략 )
sysdate --> 현재날짜와 시간을 돌려주는 날짜함수
( 밀리세컨값..기본값은 parameter에 있는걸로 )
dual --> sysdate 연산식등의 결과를 출력해보기위해사용하는 임시테이블
dual 테이블은 sys사용자소유의 테이블로 모든 사용자들의 접근이 허용가능!
sysdate, user 등의 함수값과 연산식의 결과값을 출력해보는 용도로 사용
사실 emp테이블은 scott.emp 임!!!
ex> select user from dual ( 현재 소유자?? )
ex> select sysdate from dual ( 현재날짜와시간 )
ex>현재날짜와 5일후,7일전의 날짜를 출력하시오
--> select sysdate, sysdate+5, sysdate-7 from dual;
날짜값 + 숫자 = 날짜값
날짜값 - 날짜값 = 일수
( select sysdate - ( sysdate - 7 ) from dual 하면 일수가 나옴 ! )
이렇게말고 내가정한임의의날짜는 날짜형식이 잘못되었는지 잘안됨...
만약 날짜-날짜 해서 일수가 나오는데 소수부분이 나올수도있음...
4479.45341 이 리턴이되었다면 4479일 0.45341일이다
일을 시간으로 나타내기위해서는 24시간 * 0.45341 = 10.88184
즉, 10시간 하고도 시간을 분으로 나타내기위해서는 60분 * 0.88184 = 52.9104
즉, 52분하고도 분을 초로 나타내기위해 60초 * 0.9104 = 54.624
즉, 54초 하고도 ...나머진 대략!
=> 즉, 4479일 10시간 52분 약 55초 정도 일했다는것이 결론!!!
ex> 근무시간이 100000시간이 넘은 사원을 출력
select last_name from s_emp where ( sysdate - start_date ) * 24 > 100000;
ex> 근무한지 몇주가 지났는지출력 ( 일을 7로 나누면 주단위가 나옴 ! )
select ( sysdate - start_date ) / 7 from s_emp ;
# 날짜함수
add_months(날짜값,N) : 날짜값에 N개월뒤의 날짜를 리턴
ex> ADD_MONTHS('02/01/01/,2)=02/03/01
months_between(날짜1,날짜2) : 두 날자싸이의 개월수를 리턴
( 날짜2가 날짜1보다 미래일경우 - 로 표시 )
SELECT months_between(TO_DATE('02/01-01','YY-MM-DD') ,TO_DATE('02/05/01','YY-MM-DD')) FROM DUAL;---> -4
예제>31번 부서사원들의 이름 근무시작일 근무개월수를 출력하시오
select last_name,start_date,
months_between(sysdate,start_date)
from s_emp
where dept_id=31;
next_day(날짜값,'요일') : 날짜값의 첫 해당 요일의 날짜를 리턴
요일값은 korea로 설정되어있을시 한글로 입력해야함
그러니깐 특정날짜기준으로 돌아오는 첫 요일의 날짜를 리턴
ex> next_day('02/07/01','금요일')=02/07/05
last_day(날짜값) : 날짜값이 속한 달의 마지막 날짜를 리턴
ex> select last_day('02/02/01') from dual; = 02/02/28
round(날짜값,형식) : 형식을 기준으로 반올림 값을 리턴
( 형식은 날짜형식으로하면됨... YYYY MM 등등 기준 형식 단 하나만됨 )
ex> select round( to_date('02/07/01'), 'year')
form dual; = 03/01/01
ex> select round( to_date('02/07/16'), 'month')
from dual; = 02/08/01
ex> 'day'--> 일주일을 기준으로 반올림값
( 일월화수목금토 중 수요일을 기준으로 반올림 )
따라서 무조건 해당일의 다음주 일요일의 날짜를 반환
trunc(날짜값,형식)-->형식을 기준으로 그이하는 버리고 리턴
ex> select trunc(to_date('02/07/16),'year')
from dual; = 02/01/01
select trunc(to_date('02/07/16),'month')
from dual; = 02/07 /01
SELECT TRUNC( TO_DATE('02/07/16','YY-MM-DD'),'YYYY' ) FROM DUAL;
(2002-01-01 00:00:00) 년이 기준이니깐 해당년빼고 나머지는 버림
SELECT TRUNC( TO_DATE('02/07/16','YY-MM-DD'),'MM' ) FROM DUAL;
(2002-07-01 00:00:00) 월이 기준이니깐 해당월빼고 나머지는 버림
# 변환함수-->데이터 타입을 변환해 주는 함수
형식에는 ' ' 넣어야함!
to_char( 숫자값 , '형식')-->숫짜값을 형식에 맞춰 문자 스트링으로 만들어준다
ex>select to_char(salary,'9999') from s_emp;
형식
9 : 숫자길이( 형식보다 숫자값이 길면 전부# 로 표시 )
0 : 숫자길이( 형식보다 숫자값이 적으면 자리수전부 0으로 채움 )
EX)SELECT TO_CHAR(23,'0000') FROM DUAL; ---> 0023(문자임)
. : 소수점자리표시
EX) SELECT TO_CHAR(23,'999.9') FROM DUAL; ---> 23.0
, : 지정된 단위에 표시
EX) SELECT TO_CHAR(233232,'9,999,999,999') FROM DUAL; ---> 233,232
$ : 달러표시
EX) SELECT TO_CHAR(233232,'$9,999,999,999') FROM DUAL; ---> $233,232
L : 국가별화폐단위표시
EX) SELECT TO_CHAR(233232,'L9,999,999,999') FROM DUAL; ---> $233,232
to_char( 날짜값, '형식')-->날짜값을 형식에 맞춰 문자 스트링으로 만들어준다
형식
YYYY : 연도4자리
YY : 연도뒤의 두자리
MM : 월 두자리
DD : 일 두자리
HH24 : 단위시간(24시)
HH : 단위시간
AM,PM : 오전 오후
MI : 분
SS : 초
DAY : 요일의 이름(ex:월요일)
DY : 요일의축약(ex:mon)
D : 주안의 요일번호( 일요일1 월요일2 .. 토요일7 )
DDD : 연도내의 일 수
W : 월 내의 주 수
WW : 연도내의 주수
MONTH : 월의 전체 이름
select to_char(sysdate,'HH24') FROM DUAL;-->18
*중요:rr/yy형식->Y2k를 해결하기위해 만들어진것
즉 yy는 현제 시스템 사용년도(2000년이후)라고 생각하고
새로만든 rr은 과거 50-99년까지의 사용년도라고 생각하면됨
50-99-->rr형식 사용시-1950-1999
50-99-->yy형식 사용시-2050-2099
00-49-->rr형식 사용시-2000-2049
00-49-->yy형식 사용시-2000-2049
to_date(문자 스트링, '형식')-->문자 스트링을 주어진 형식으로 해석해서 날짜값으로 만들어준다.
( 형식은 to_char( ) 에서 날짜형식과 똑같음 ! )
SELECT TO_DATE('2013-11-05','YYYY-MM-DD') FROM DUAL; --> 2013-11-05 00:00:00
SELECT TO_DATE('2013-12-3 18:52:13','YYYY-MM-DD HH24:MI:SS') FROM DUAL;
-->2013-12-03 18:52:13
주의점은 문자스트링에 특수문자말고 숫자영어알파벳이 구분선으로 있으면 다음과같이 일일이
형식에 " " 사이에 넣어야함
SELECT TO_DATE('2015년 5월 1일', 'YYYY년MM월DD일') from dual; ( 오류 )
SELECT TO_DATE('2015년 5월 1일', 'YYYY"년"MM"월"DD"일"') from dual;(2015-05-01 00:00:00)
두번째것과같이 사이에 ""로 넣어줘야함( 공백도안됨! )
SELECT TO_DATE('1999%1_3_', 'YYYY@MM_DD-') from dual; (1999-01-03 00:00:00)
( 특수문자는 상관없음 )
to_number(문자 스트링, '형식')-->문자 스트링을 주어진 형식으로 해석해서 숫자값으로
만들어준다.
ex>to_number('1234') from dual;-->문자열1234를 숫자로 변환
SELECT TO_NUMBER('$123,456' , '$999,999') FROM dual; => 123456(to_char()의반대원리)
# decode(칼럼 또는 수식, 조건1,값1,조건2,값2,....디폴트값)--if else와비슷
칼럼또는 수식값이 조건1에해당하면 값1을 리턴......
조건에 맞는값이 없으면 디폴트 값을 리턴
ex> select decode(dept_id, 10, salary*1.1, 50, sallary*1.2, sallary)
from s_emp;
-->dept_id값이 10이면 salary*1.1값을 리턴하고
-->dept_id값이 50이면 salary*1.2값을 리턴하고
-->dept_id값이 10이나50이아니면 sallary값을 리턴한다
# 단일행 함수는 여러 레벨에 걸쳐 중첩이가능하다.
하위레벨에서-->상위레벨순으로 진행한다
**예제)
사원의 이름과 매니저사번을 출력하시오,
-->select last_name,manager_id from s_emp;
매니저가 없는 사원은 'no manager'을 출력하시오
-->select last_name,nvl(to_char(manager_id) ,'no manger') from s_emp;
( manager_id의 자료형이 number라서 바로안됨..문자로 변경해야 됨 )
숫자값을 문자스트링으로 전환하기위한함수"to_char()"
null값에 문자스트링을 대체하기위한함수"nvl(A,B)"
nvl(to_char(), B )-->단일행함수의 중첩
* 현재월의 첫째주 토요일의 날짜를 출력하시오( 꼭 해볼것. )
select decode(to_char(trunc(sysdate,'month'),'d'),
-->sysdate의 달 기준으로 반올림값의 버린값의 주안의날짜 수가
7,trunc(Sysdate,'month'),
-->7과같으면 sysdate의 달 기준 반올림값의 버린값을 리턴하고( Decode함수의 조건1, 값1 )
next_day(trunc(systade,'month'),'토요일')) from dual;
-->그렇지않으면 next_day(날짜값,'요일')즉 sysdate의 달 기준
반올림값의 버린값에 해당하는 요일의 토요일의날짜값을 리턴해라!!( Decode함수의 디폴트값 )
SELECT DECODE(
TO_CHAR(TRUNC(SYSDATE, 'MM'),'D')
,7
,TRUNC(SYSDATE, 'MM')
,NEXT_DAY( TRUNC(SYSDATE,'MM') , 'SATURDAY')
)
FROM DUAL;
# 복수행 함수
여러행을 조작
#group함수
그룹함수라고도하는데 그룹화안하였을경우 단 하나의 결과값만 나오기때문
그룹화(아래 group by) 하면 그룹별로 결과값이 나옴
count():개수
sum():합계
avg():평균값
max():최대값
min():최소값
*select count(*) from emp;
--->emp의 사원수를 모두 더하라..
select depno,count(empno) from emp group by depno;
--->그룹별 사원수를 구하라
*select avg(nvl(comm,0)) from emp;
--->사원의 평균 comm을 구하라
-->select avg(comm) from emp--->안됨!!! null값을 제외한 값으로 평균을
내기때문에 null값의 인원 수도 같이 더해서 평균을 내야함!!
*두개의 관련된 조합이 적용되는 group by
select deptno,job,count(*) from emp group by deptno,job;
: deptno로 그룹화하고 거기서 또 job로 그룹화(그룹이 세분화됨)
# group by절
위에 그룹함수는 한개의 결과값만 산출함
하지만 부서별로 구하기위해서는 group by절을 이용해야함
*select sum(sal) from emp group by deptno;
-->deptno(그룹)별 sal(월급)의 합계를 구하라(emp)테이블에서...!!
--->group 절 뒤의 테이블을 그룹화 한후 select절 뒤에오는 값을 대입
# having절--->미리 만들어져있는 데이터값을 검색조건은 where절에서...
내가 만든 내용의 검색은 having절에서 한다.
( group by 해서 나온거의 조건검색은 having절로! )
*select job,sum(sal) from emp group by job having sum(sal)>=5000
---->직업별로 5000이상 월급을 구하시오
*select job,sum(sal) from emp group by job having count(job)>=3;
--->직업별로 3명이상 근무하는 직업의 총 월급을 구하라
**>데이터 실행 순서..
SELECT JOB, SUM(SAL)
FROM EMP
WHERE JOB<> 'MANAGER'
GROUP BY JOB
HAVING COUNT(JOB) >= 3;
1. EMP테이블에서 WHERE조건에 만족하는 행들을 찾음
2. GROUP BY절의 칼럼값에 따라 그룹핑함
3. 그룹핑한것중 조건에 맞는 그룹을 찾음
4. 마지막에 ORDER BY가 있으면 명시된 순서대로 정렬함
*-->1992년 8월 주문상품별 총 판매수량
select i.product_id,sum(i.quantity)--->4.상품명,총판매수량 출력
from s_item i inner join s_ord o--->1.조인
on i.ord_id=o.id
where o.date_ordered like '92/08/%'-->2.92년8월(원래 있는데이터검색)
group by i.product_id --->3.상품별로 그룹핑
'DB > 오라클' 카테고리의 다른 글
| 오라클 조인 ( join ) (0) | 2019.04.03 |
|---|---|
| 오라클 서브쿼리 ( subquery ) (0) | 2019.04.03 |
| 계층형 쿼리 (0) | 2019.04.03 |
| 오라클 기초 (0) | 2019.04.03 |
| 오라클 DDL, DML, DCL, TCL (제약조건 포함) (0) | 2019.04.03 |