오라클 함수(count,sum,avg,max,min/round,floor,trunc,ceil,mod/upper,lower,initcap,substr,length,replace,decode/sysdate,last_dat,add months/to_char,to_date)

* 집계함수
1. count : 갯수	
 - 함수의 결과는 반드시 별칭을 지정할 것*******
 - 함수의 결과는 단일 데이터와 동시에 select 작업 불가능*******
2. sum	 : 합계 오로지 숫자컬럼만
3. avg	 : 평균
 - null컬럼값이 제외****
4. max	 : 최대
5. min	 : 최소
* 숫자함수
 - round : 반올림
 - floor, trunc : 무조건 내림
 - ceil : 무조건 올림
 - mod : 나머지 함수
* 문자열 함수
 - upper, lower, initcap : 대소문자변환
 - substr : 추출, char substr(컬럼명, 시작위치, 갯수)**
 - length : 길이
 - replace, tranlate : 치환
 - decode :문자열 치환**
* 날짜 함수
 - sysdate
 - last_day
 - add months
* 형변환 함수
 - 숫자->문자 : to_char()
 - 날짜->문자 : to_char(날짜, 형식문자열) ******
 - 문자->날짜 : to_date(문자, 형식문자열)

 
-- SQL 함수
 
-- 1. 집계함수 (******)
--  a. count  : 갯수
--  b. sum    : 합계
--  c. avg    : 평균
--  d. max    : 최대
--  e. min    : 최소
 
 
 
 
 
-- a. count 함수
--  : number count(컬럼명)
--  : 해당 컬럼의 값이 총 몊개인지 반환 -> 행의 갯수 반환
--  : null값을 가지는 행은 제외
 
select * from tblCountry;
 
-- 국가가 총 몇개국입니까?(행의 갯수)
select count(*) from tblCountry; --14개국
select count(name) from tblCountry; --14
 
-- 인구수가 조사된 나라의 갯수?(null 제외)
select count(popu) from tblCountry; --13
select count(*) from tblCountry where popu is not null; 
 
-- 인구수가 조사 안 된 나라의 갯수?
select count(*) from tblCountry where popu is null; 
 
-- 서울에 사는 직원이 몇 명?
select count(*) from insa where city = '서울';
 
-- 컬럼은 1개 또는 * 만 넣을 수 있다. 
select count(name, capital) from tblCountry; --표현x,
 
-- 회사(insa)에서 연말 보너스 1000000000원 -> 1/n -> 1억/사원수
select 100000000 /count(*) from insa;
 
-- 국가테이블의 대륙이 몇 종류?(갯수)
-- count로 묶어만 주면 됨.,
select distinct cont from tblCountry;
select count(distinct cont) from tblCountry;
 
-- 회사에 몇개의 부서가?
select distinct buseo from insa;
select count(distinct buseo) from insa;
 
-- ******함수의 결과는 반드시 별칭을 지정할 것
-- ******함수의 결과는 단일 데이터와 동시에 select 작업 불가능
select count(*), name from insa;
 
 
 
 
 
-- b. sum 함수
--  : number sum(컬럼명)
--  : 해당 컬럼값의 종합을 반환
--  : 숫자 컬럼
 
select popu from tblCountry;
 
-- 국가테이블의 모든 나라의 인구 총합은 몇 명?
select sum(popu) from tblCountry; 
select sum(name) from tblCountry; --에러]invalid number
select sum(*) from tblCountry; --x 
 
select sum(popu * 2), sum(popu) from tblCountry;
 
-- 총무부 직원들의 총 급여 합은?
select sum(basicpay) from insa where buseo = '총무부';
 
-- 총무부 직원들의 평균 급여는?
select sum(basicpay) / count(*) from insa where buseo = '총무부';
 
 
 
 
 
 
-- c. avg 함수
--  : number avg(컬럼명)
--  : 해당 컬럼값이 평균값 반환
--  : null컬럼값이 제외(***)
--  : 숫자 컬럼
 
 
-- 평균 인구수? ..null의 여부
select popu from tblCountry;
 
select sum(popu) from tblCountry; --202652
select count(*) from tblCountry; --14
select sum(popu)/ count(*) from tblCountry; -- 202652 / 14
 
 
select avg(popu) from tblCountry; --15588
select sum(popu) / count(popu) from tblCountry; --15588
 
 
 
 
-- d. max, min 함수
--  : number mas(컬럼명)
--  : number min(컬럼명)
--  : 숫자 컬럼
 
-- 인구수가 가장 많은 나라의 인구는 몇 명?
select max(popu) from tblCountry;
select max(popu), name from tblCountry; --x
 
select max(name) from tblCountry;
 
--가장 적은 나라의 인구수
select min(popu) from tblCountry;
 
 
select count(*) as "총 직원수"
      , sum(basicpay) as "총 월급"
      , avg(basicpay) as "평균 월급"
      , min(ibsadate) as "최고참의 입사날짜" --작은것이 빠른
      , max(ibsadate) as "신입의 입사날짜"
      , months_between(min(ibsadate), max(ibsadate)) / 12 as "최고참vs신입의 연차"
from insa;
 
 
-- ****** 집계함수는 일반컬럼과 동시에 select 불가능
-- ****** 집계함수는 where에서 사용불가 -> where 적은 단일 레코드에 대한 질문(조건)
 
select max(basicpay) from insa; --최고급여.. 누군지 알고싶다!
select name from insa where basicpay = max(basicpay); --x,,where절은 집합 함수를 못 씀.
select name from insa where basicpay = 2650000; --해결!직접 상수를 입력.
 
 
 
 
 
 
 
 
-- 숫자함수
 
-- a. round 함수
--  : 반올림
--  : number round(숫자)
--  : number round(숫자, 자릿수)
 
select 3.45678 from dual; --원본
select round(3.45678) from dual; --정수, 3
select round(3.45678, 0) from dual; --정수, 3
select round(3.45678, 1) from dual; --3.5
select round(3.45678, 2) from dual; --3.46
 
select avg(basicpay) from insa;
select round(avg(basicpay)) from insa; --***
 
 
 
 
-- b. floor, trunc 함수
--  : 절삭, 무조건 내림
--  : 반올림 o,x -> x
--  : number floor(컬럼명)
--  : number trunc(컬럼명 [,자릿수])
--  : 현재 숫자와 가장 가까운 '적은 정수 중' 최대 정수를 반환한다.
 
select 4.5678 from dual;
select floor(4.5678) from dual; --정수, 4
select trunc(4.5678) from dual; --4
select trunc(4.5678, 1) from dual; --4.5
 
 
 
 
-- c. ceil 함수
--  : 무조건 올림
-- number ceil(컬럼명)
-- 현재 숫자와 가장 가까운 정수를 반환한다.
 
select 4.123 from dual;
select ceil(4.123) from dual; --5
select ceil(4.000001) from dual; --5
 
--언제쓰느냐?... 페이징
select 75/20 as 총페이지 from dual;
select ceil(75/20) as 총페이지 from dual;
 
select 61/20 as 총페이지 from dual;
select ceil(61/20) as 총페이지 from dual;
 
 
 
 
-- d. mod 함수
--  : 나머지 함수(%연산자)
--  : number mod(피제수, 제수)
 
-- 10 % 3
select mod(10, 3) from dual;
 
--100분은 몇시간 몇분?
-- : 100/60 -> 몫(시)
-- : 100/60 -> 나머지(분)
select trunc(100/60) as "시", mod(100,60) as "분" from dual;
 
 
 
 
 
 
-- 문자열 함수
 
--  a. upper, lower, initcap 함수
 
select first_name from employees;
select first_name, upper(first_name), lower(first_name) from employees; -- 대소문자 변환
 
-- 이름중에 'EL' 들어간 사람? 
select * from employees where first_name like '%EL%';
select * from employees where first_name like '%el%'; --대소문자..?
select * from employees where lower(first_name) like '%el%'; --***
 
selet 'test' from dual;
select initcap('test') from dual; --앞글자만 대문자로 변화
 
 
 
 
-- b.  substr 함수
--  : 문자열 추출
--  : 서수가 1부터 시작
--  : 두번째 매개변수가 문자열의 갯수
--  : char substr(컬럼명, 시작위치, 갯수)**
 
select '가나다라마바사아자차' from dual;
select substr('가나다라마바사아자차', 2, 3) from dual;  --시작위치에서 갯수만큼.,
select substr('가나다라마바사아자차', 2) from dual;     --시작위치에서 끝까지., (갯수 설정x)
select substr('가나다라마바사아자차', -2) from dual;    --끝에서부터 갯수만을.,
 
 
select name from insa;
 
--이름 가져오기
select substr(name, 1, 1) as "성"
        , substr(name, 2) as "이름"
          from insa;
          
--회사에 여직원이 몇명?
select * from insa;
 
select * from insa where substr(ssn, 8 ,1) = '2';
select count(*) from insa where substr(ssn, 8 ,1) = '2'; --29
select count(*) from insa where substr(ssn, 8 ,1) = '1'; --남자직원수 31
 
--회사에 어떤 성씨가 있냐?
select name from insa;
select distinct name from insa;
select distinct substr(name, 1, 1) from insa;
select count(distinct substr(name, 1, 1)) from insa; --추가] 몇종류의 성씨가 있나?
select count(distinct substr(name, 1, 1)) from insa where city = '서울';
 
 
 
 
 
 
-- c. length 함수
--  : 문자열 길이
 
select name, length(name) from tblcountry;
select name, length(name) from tblcountry where length(name) = 2; --나라 이름 2글자인 나라?
select name, length(name) from tblcountry order by length(name) desc; --나라이름이 긴 나라부터?
 
 
 
 
-- d. replace, tranlate 함수
--  : 문자열 치환
--  : translate -> 1글자 -> 1글자
--  : repalce -> 문자열 -> 문자열
 
select name, translate(name, '홍', '김'), replace(name, '길동', '하하') from insa;
 
-- e. decode 함수**
--  : 문자열 치환
select cont from tblCountry;
select replace(cont, 'AS', '아시아') from tblCountry; --as만 이사로 치환
select replace(replace(replace(replace(replace(cont, 'AS', '아시아')
                , 'SA', '남아메리카')
                  , 'EU', '유렵') 
                    , 'AF', '아프리카')
                      , 'AU', '호주') from tblCountry; --이st..불편해..!
                      
                      
select decode(cont, 'AS', '아시아') from tblCountry; --바꿀게 없으면 null을 반환시킴*
select decode(cont, 'AS', '아시아'
                    ,'EU', '유럽'
                    ,'SA', '남아메리카'
                    ,'AF', '아프리카'
                    ,'AU', '호주') from tblCountry; -- 한방에 치환! ***
                    
--아시아에 속한 나라는 몇개국?
select count(decode(cont, 'AS', '아시아')) from tblCountry; 
select count(*) from tblCountry where cont = 'AS'; --정석
 
--아시아 몇개국? 유럽 몇개국?
select count(*) from tblCountry where cont = 'AS';
select count(*) from tblCountry where cont = 'EU';
select count(*) from tblCountry where cont = 'AS' or cont = 'EU'; --이런 답변nono..
 
select count((cont, 'AS', '아시아')) from tblCountry;
select decode((cont, 'EU', '유럽')) from tblCountry;
select count(decode(cont, 'AS', '아시아')), count(decode(cont, 'EU', '유럽')) from tblCountry; --한방에! ***
 
 
--김씨 몇명? 박씨 몇명 ? 최씨 몇명?
select count(*) from insa where substr(name, 1, 1) = '김';
 
select decode(substr(name, 1, 1), '김', '1') from insa;
select count(decode(substr(name, 1, 1), '김', '1'))
        ,count(decode(substr(name, 1, 1), '박', '1'))
        ,count(decode(substr(name, 1, 1), '최', '1')) from insa; --한방에! ***
        
        
        
        
        
                
-- 날짜 함수
 
-- a. sysdate
--  : 현재 시간 가져오기
select sysdate from dual;
 
 
select * from insa where ibsadate > sysdate; --앞으로 해야하는 일정
 
 
 
-- b. last_day
--  : 해당 날짜의 월의 마지막 일
 
--2015년 1월은 몇일까지 있나?
select last_day(sysdate) from dual; 
 
 
 
-- c. months_between
--  : 날짜 간격
 
 
 
-- d. add months
--오늘부터 3개월 후는 어떤날짜?
select sysdate, add_months(sysdate, 3) from dual;
 
 
 
 
 
 
-- 형변환 함수
--  1. 숫자 -> 문자
--  2. 날짜 -> 문자
--  3. 문자 -> 문자
--  4. 문자 -> 날짜
 
 
 
-- 1. 숫자 -> 문자
-- to_char() 함수
--  : char to_char(숫자, 형식문자열)
 
-- name like '김__'
 
-- 형식문자열
--  1. 9  : 숫자 1자리
--  2. 0  : 숫자 1자리
--  3. $  : 달러표시
--  4. L  : 로케일 통화(원)
--  5. .  : 소숫점 표시
--  6. ,  : 천단위 표시
 
 
-- 100-> '100'
select to_char(100, '999') from dual; --'100'
select to_char(100, '000') from dual; --'100'
select to_char(10, '999') from dual; --'10'
select to_char(10, '000') from dual; --'010'
 
select to_char(1, '999') from dual; -- '1' 데이터만 출력
select to_char(1000, '999') from dual; -- '####'
 
select to_char(1, '000') from dual; --'001'
select to_char(1000, '000') from dual; --'####'
 
-- $, L
select to_char(1000, '9999') from dual;
select to_char(1000, '$9999') from dual;
select to_char(1000, '9999원') from dual; --x
select to_char(1000, '9999') || '원' from dual;
select to_char(1000, 'L9999') from dual;
 
-- .
select to_char(123.456, '999.999') from dual;
select to_char(123.456, '999.99') from dual; --123.46
select to_char(123.456, '999.9') from dual; --123.5
select to_char(123.456, '999') from dual; --123
 
-- ,
select to_char(1000, '9999') from dual;
select to_char(1000, '9,999') from dual;
select to_char(1000, '99,99') from dual;
select to_char(1000, '999,9') from dual;
 
select to_char(1000000, '9,999,999') from dual;
 
 
 
 
 
-- 1. 날짜 -> 문자 (**********)
--  : date -> char
--  : char to_char(날짜, 형식문자열)
 
-- 형식문자열
-- 1. YYYY        : 년도 4자리 ***
-- 2. YY          : 년도 2자리
-- 3. MONTH       : 월 단어(풀네임)
-- 4. MON         : 월 단어(약어)
-- 5. MM          : 월 2자리(숫자) ***
-- 6. DAY         : 요일 단어(풀네임)
-- 7. DY          : 요일 단어(약어)
-- 8. DDD, DD, D  : 날짜 DD
-- 9. HH, HH24    : 시 ***
-- 10. MI         : 분 ***
-- 11. SS         : 초 ***
 
 
select sysdate, to_char(sysdate, 'YYYY'), to_char(sysdate, 'YY') from dual; --2015
select to_char(sysdate, 'MONTH') from dual; --1월
select to_char(sysdate, 'MON') from dual; --1월
select to_char(sysdate, 'MM') from dual; --01
select to_char(sysdate, 'DAY') from dual; --목요일
select to_char(sysdate, 'DY') from dual; --목
select to_char(sysdate, 'DDD') from dual; --008 (1월 1일부터 누적된 날짜가 출력, 년기준)
select to_char(sysdate, 'DD') from dual; --08 (월기준)
select to_char(sysdate, 'D') from dual; --5 (주기준, 일-1, 토-7)*
select to_char(sysdate, 'HH') from dual; --01(12시)
select to_char(sysdate, 'HH24') from dual; --13(24시)
select to_char(sysdate, 'MI') from dual; --04
select to_char(sysdate, 'SS') from dual; --01
 
 
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;
 
 
 
-- 출력용 & 조건
-- 입사년도가 1999년 직원만 출력
select * from insa;
select * from insa where to_char(ibsadate, 'YYYY') = '1999';
--입사년도 12월인 사람?
select * from insa where to_char(ibsadate, 'MM') = '12'; 
 
--97, 98, 99년도 입사한 사람?
select * from insa where ibsadate between '1997-01-01' and '1999-12-31'; --between
select * from insa where to_char(ibsadate, 'YYYY') in ('1997', '1998', '1999'); --to_char, in
 
 
 
 
-- 3. 문자 -> 숫자
--  java : integer.parseInt("100") -> 100
 
-- to_number 함수
--  : number to_number(문자)
 
select '123' from dual;
select to_char(sysdate, 'YYYY') from dual; --'2015'
 
select '123' from dual;
select '123' + 100 from dual; --223, 가능o, 권장x
select '123' || 100 from dual; --123100
 
select to_number('123') + 100 from dual; --이렇게..
 
 
 
-- 4. 문자 -> 날짜
 
-- to_date 함수
-- : date to_date(문자, 형식문자열)
 
-- 형식문자열
-- : to_char()에서 사용하던 형식문자열과 동일하게 사용
 
select '2015-01-08' from dual; --string
select sysdate from dual;
select * from insa where ibsadate < '2015-01-05';
 
select to_date('2015-01-08', 'YYYY-MM-DD') from dual;
 
select to_date('2015-01-08 1:44:50', 'YYYY-MM-DD HH24:MI:SS') from dual; --*******
 
 
 
 
-- nvl함수
--  : null value 함수
--  : 특정 컬럼이 null이면 대신할 값을 치환 함수
--  : nvl(컬럼명, 대체값)
--  : 대체값은 컬럼값과 동일한 자료형
 
select name, popu from tblCountry;
select name, nvl(popu, 0) from tblCountry; --null->0으로 치환
 
select namem tel from insa;
select name, nvl(tel, '(연락처 미기재)') from insa;