자바 오라클 1

/*
    개체 (Entity)
    필드 (Field,Column)
    필드의 내용집합 (Domain)
    행의 집합(row, recod, tuple)
    뷰(view) : 테이블처럼 쓸 수 있는 가상의 테이블 
    뷰(View) 사용이유 : 데이터 보완성을 위해 (외부로 부터 데이터를 감추기 위함)
*/
 
-- : 1줄 주석
/*
    범위주석

    1) 문자형자료형
        char(크기)        : 고정형
        varchar2(크기)    : 가변형(자동으로 데이터 크기만큼 설정됨)
        clob            : character large object(4G)
    
        create table board
        (    
            --     변수명   자료형
                idx      int,
                content     char(2000),
                content1 varchar2(2000)
        )

    2) 숫자형자료형
        number(전체자릿수, 소숫점이하자릿수)
        int => nuber(38)
        
    3) 날짜형자료형
        date <= 년월일시분초
*/

/*
    ## 연산자
    
    1)산술연산자
        : + - * / 
        mod(피젯수, 젯수)
        
    select 
        1+1, 3-1, 3*2, 10/3, mod(10, 3)
    from dual;

    select 
        sabun, saname, sapay*0.1 as bonus 
    from sawon
            
    실행 순서
    1_ from 
    2_ where
    3_ select
    4_ order by
    
    2)비교연산자
        : = >= <= < 
            같지않냐 !=    <>
            
    ## 부장만 추출
    select * from sawon where sajob='부장'

    ## 부장만 빼고 추출
    select * from sawon where sajob != '부장'
    select * from sawon where sajob <> '부장'

    ## 급여가 3000이상인 직원 추출
    select * from sawon where sapay >= 3000
    
    3)논리연산자
        : and, or, not
        
    ## 10번부서 직원중 여자만 추출
    select * from sawon 
    where deptno=10 and  sasex='여자'
    --      (조건1)         (조건2)
     
    ## 직급이 부장, 과장인 직원만 추출
    select * from sawon
    where sajob='부장' or sajob='과장'
    --      (조건1)           (조건2)
    
    ## 여자가 아닌 직원을 추출
    select * from sawon where sasex='남자'
    
    select * from sawon where not (sasex='여자') --역조건 (가급적이면 사용금지 하지만 방법이 없을 경우 사용)
    
    
    4) 기타연산자
        필드 between A and B : A ~ B 사이냐
        필드 in(값1,값2,...) : 필드=값1 or 필드=값2 or ...
        
        ##급여가 3000~4000사이 받는 직원
        select * from sawon
        where sapay>=3000 and sapay<=4000
        
        select * from sawon
        where sapay between 3000 and 4000
        
        ##10번 20번 부서직원만 추출
        select * from sawon
        where deptno=10 or deptno=20
        
        select * from sawon
        where deptno in(10,20)
        
    5) 문자열 유사검색식
       필드 like '%_'
       % : 모든값을 의미
       _ : 1문자의 모든 값
       1자리 문자인데 모든 값을 출력
       
       ## '이'씨 성씨만 검색
       select * from sawon
       where saname like '이%'
       (첫 글만 이씨이고 나머지는 뭐라도 상관없음)
       
       ##이름 2번째 글자가 '미'인 직원 추출
       select * from sawon
       where saname like '_미%'
    
       ##고객테이블
       select * from gogek
       
       ##고객테이블에서 여자고객만 추출
       select * from gogek
       where GOJUMIN like '_______2%'
    
       select * from gogek
       where gojumin like '______-2%' or
             gojumin like '______-4%' or
                gojumin like '______-6%' or
                gojumin like '______-8%'
                
       -6은 외국인 1900년대
       -8은 외국인 2000년대
       
       select * from gogek
       where substr(gojumin,8,1) in ('2','4','6','8')
       
       select * from gogek
       where to_number(substr(gojumin,8,1)) in (2,4,6,8) <= 문자열을 숫자로 바꿔줌 to_number
       
                   12345678901234    <=위치값(1 base 시작)
       gojumin='801212-2123456'
       
       cf) 문자열 추출함수 : substr(필드,시작,갯수)
       
       select 
               substr(gojumin,1,2) 년,
               substr(gojumin,3,2) 월,
               substr(gojumin,5,2) 일
       from gogek
       
       
       
*/
 
    Colored by Color Scripter

/*
    cf)문자열 결합연산자 : ||
    select saname || '님 안녕하세요' from sawon
    
    1)날짜형 함수
    
    # 오라클은 문자 1개가 3바이트
    # 자바는 2바이트
    
    # 오라클의 trim은 문자와 공백제거
    # 자바는 공백제거
    
    # 문자형을 날짜로 바꾸는거 to_date()
    # 날짜형을 문자형으로 바꾸는거 to_char()
    
    # 문자형을 숫자로 바꾸는거 to_number()
    # 숫자를 문자형으로 바꾸는거 to_char()
    
    # 날짜형을 문자형으로 바꾸는거 to_char() 와 
    숫자를 문자형으로 바꾸는거 to_char() 에서 쓰는
    to_char()는 서로 틀림(인자도 틀리고)
    
    add_months
    months_between
    
    select sysdate 입대일자, add_months(sysdate,21) 제대일자 from dual    
    
    select sabun, saname, sahire,
        round(months_between(sysdate,sahire),1) as 전체근무월수,
        floor(months_between(sysdate,sahire)/12) as 근무년수,
        round(mod(months_between(sysdate,sahire),12),0) as 근무월수,
        round(sapay/13+0.5,0) as 월급,
        (
            (months_between(sysdate,sahire))     -- 총근무월수
            *
            ( round(sapay/13+0.5,0) )            --월급여
        ) / 12 as 퇴직금
    from sawon
    
    2)형변환함수
    to_char(), to_date(), to_number()
    --to_char(sahire, 'YYYY') 입사년도, --charactor
    
    select 
        sabun, saname, sahire,
        to_char(sahire, 'YYYY') 입사년도,
        to_char(sahire, 'MM') 입사월,
        to_char(sahire, 'DD') 입사일    
        from sawon
    where to_number(to_char(sahire, 'YYYY')) between 2000 and 2005
    --위처럼 하게 되면 결과가 완벽하게 출력 됨
    
    --시간때문에 잘못된 결과가 나올수도 있다
    --where sahire >= '2000-1-1' and sahire < '2006-1-1' 
        
     '2005-12-31 00:00:00.000'     
    '2005-12-31 23:59:59.999'     

    //날짜는 between 쓰면 절대 안됨(시간 때문에...)
    '2000-1-1 00:00:00.001'
    
            
    select to_date('2000-1-1'), sysdate from dual;
    
    3)decode, case
    select 
        gobun, goname, gojumin,
        decode
        (
             substr(gojumin,8,1), 1, '남자',
                                  2, '여자',
                                  3, '남자',
                                  4, '여자'
                                  
        ) as 성별
    from gogek
    
    
      
    select
         gobun, goname, gojumin,
         case
            when substr(gojumin,8,1) in(1,3,5,7) then '남자'
            else '여자'
         end as "성별"
    from gogek
    
    select
         gobun, goname, gojumin,
         case
            when substr(gojumin,8,1) in(1,3,5,7) then '남자'
            when substr(gojumin,8,1) in(2,4,6,8) then '여자'
         end "제3의성"
    from gogek
    
    
    --석래아저씨 코드
    select
        gobun, goname, gojumin,
        decode
        (
            mod(
                substr(gojumin,8,1),2),  1, '남자',
                                         0, '여자'
        ) as 성별
        from gogek    
    
    
    ## 고객테이블에서 고객의 출생계절
    3~5:    봄
    5~8:    여름
    9~11:    가을
    12,1,2:    겨울

    [방법1]    
    --            12345678901234
    -- gojumin='801212-1234567'
    select
        gobun,goname,gojumin,
        case
            when to_number(substr(gojumin,3,2)) in(3,4,5) then '봄'
            when to_number(substr(gojumin,3,2)) in(6,7,8) then '여름'
            when to_number(substr(gojumin,3,2)) in(9,10,11) then '가을'
            when to_number(substr(gojumin,3,2)) in(12,1,2) then '겨울'
        end as 계절
    from gogek
    
    [방법2]
    -- 4/3=>1.3333 => floor(): 정수만 구함 
    select
        gobun,goname,gojumin,
        case floor(to_number(substr(gojumin,3,2))/3)    
            when 1 then '봄'
            when 2 then '여름'
            when 3 then '가을'
            else '겨울'
        end as 계절
    from gogek
    
    
*/
 
 

/*

    서브쿼리(Sub Query) : 하위쿼리의 결과를 이용해서 주쿼리를 수행하는 쿼리
    
    select * from sawon
    
    ##장동건과 동일한 부서직원 추출
    주쿼리 : 
        select * from sawon where deptno=40
    하위쿼리 : (장동건 부서추출) 
        select deptno from sawon where saname='장동건'
        
    select * from sawon 
    where deptno=( select deptno from sawon where saname='장동건' )
    
    ##장동건과 직급이 동일한 직원추출
    select * from sawon 
    where sajob=( select sajob from sawon where saname='장동건' )
    
    ##장동건과 동일한 년도에 입한 직원추출
    select * from sawon 
    where to_char(sahire,'YYYY')=
        ( select to_char(sahire,'YYYY') 입사년도 from sawon where saname='장동건' )
        
    ##장동건과 동일한 계절에 입사한 직원추출
    
    --장동건이 입사한 계절
    select * from sawon
    where
            case floor(to_number(to_char(sahire, 'MM'))/3)
                when 1 then '봄'
                when 2 then '여름'
                when 3 then '가을'
                else '겨울'
            end
    =
    (
        select 
            case floor(to_number(to_char(sahire, 'MM'))/3)
                when 1 then '봄'
                when 2 then '여름'
                when 3 then '가을'
                else '겨울'
            end
        from sawon where saname='장동건'
    )
    
    ##장동건과 동일한 년대에 입사한 직원 추출(1993=> 1990년대 입사자)

    select * from sawon 
    where substr(to_char(sahire, 'YYYY'), 1, 3) =
    (
        select 
            substr(to_char(sahire, 'YYYY'),1,3)
        from sawon where saname='장동건'    
    )
    
    ##장동건(40) 또는 안재욱(20)의 부서직원 추출
    select * from sawon
    --where deptno=40 or deptno=20
    where deptno in(40, 20)
    
    --서브쿼리
    select deptno from sawon where saname in('장동건', '안재욱')
    
    --서브쿼리결과가 다중행일경우 in으로 처리해라
    select * from sawon
    where deptno in (select deptno from sawon where saname in('장동건', '안재욱'))
    
    -- 아래처럼 =을 사용할 경우 값 1개를 구하는 식이라. 다중행이 아님
    select * from sawon
    where deptno = (select deptno from sawon where saname in('장동건', '안재욱'))
    
    --★★★★다중행이든 단일행이든 둘중 어떤 것인지 잘 모를 경우 in 으로 처리
    
*/
 
 
 
 
 

/*
     ##전체직원중에서 최대급여자를 추출
     select * from sawon
     where sapay = ( select max(sapay) from sawon )
 
 
 
    ##10번 부서의 최대급여자를 추출
    select * from sawon
    where sapay=(select max(sapay) from sawon where deptno = 10) and deptno=10
    
    ##각 부서의 최대급여자를 추출
    select * from sawon
    where 
        (sapay=(select max(sapay) from sawon where deptno=10) and deptno=10)
        or
        (sapay=(select max(sapay) from sawon where deptno=20) and deptno=20)
        or
        (sapay=(select max(sapay) from sawon where deptno=30) and deptno=30)
        or
        (sapay=(select max(sapay) from sawon where deptno=40) and deptno=40)
    
        
    ##상관쿼리이용해서 각부서의 최대급여자를 추출
    (추후 부서번호가 바껴지거나 추가될 경우 가능하다면 사용금지: 성능 저하)
    
    select * from sawon s
    where sapay = ( select max(sapay) from sawon where s.deptno=deptno )
    
    주쿼리 값 참조해서
    서브쿼리의 값으로 왔다갔다?    
            
        
*/

/*
    select * from sawon order by deptno, sasex;
    select deptno, sasex, count(*) from sawon group by deptno, sasex;

    집계함수
    -count(필드)     : 필드의 갯수 Domain : 필드의 집합을 의미 ( 같은것 끼리 묶인것 ) null포함되지 않는다
    -count(*)        : 전체레코드의 갯수
    
    select 
        count(*)         전체사원수,
        count(samgr)     일반사원수
    from sawon

    select 
        count(*)     전체사원수,
        max(sapay)     최대급여액,
        min(sapay)     최저급여액,
        max(sahire) 최근입사일자,
        min(sahire) 최초입사일자,
        sum(sapay)     전체급여합계, 
        avg(sapay)     전체급여평균 
    from sawon
    
    ##1. 부서별 인원수 및 급여합계(그룹별로 묶고 그 결과를 가지고 인원수, 급여합계 산출)
    select
        deptno,
        count(*) 인원수, 
        sum(sapay) 급여합계
    from sawon                            
    group by deptno -- 부서별                        
    order by deptno

    ##2. 부서별 직급별 인원수
    select * from sawon    
    select
        deptno, sasex,
        count(*) 인원수
    from sawon
    group by deptno,sasex
    order by deptno,sasex

    ##3. 입사년도별 인원수
    select
        sahire, to_char(sahire, 'YYYY') 입사년도
    from sawon
    group by sahire
    order by to_char(sahire, 'YYYY')

    select
        to_char(sahire, 'YYYY') 입사년도,
        count(*) 인원수
    from sawon
    group by to_char(sahire, 'YYYY')
    order by to_char(sahire, 'YYYY')

    ##4. 입사년대별 인원수
    select
        substr(to_char(sahire, 'YYYY'),1,3) || '0' as 입사년대,
        count(*) 인원수
    from sawon
    group by substr(to_char(sahire, 'YYYY'),1,3)
    order by substr(to_char(sahire, 'YYYY'),1,3)

    ##5. 입사계절별 인원수
    select
        case floor(to_number(to_char(sahire, 'MM'))/3)
                when 1 then '봄'
                when 2 then '여름'
                when 3 then '가을'
                else '겨울'
            end as season,
        count(*) 인원수
    from sawon
    group by
        case floor(to_number(to_char(sahire, 'MM'))/3)
                when 1 then '봄'
                when 2 then '여름'
                when 3 then '가을'
                else '겨울'
            end      
    order by
        case floor(to_number(to_char(sahire, 'MM'))/3)
                when 1 then '봄'
                when 2 then '여름'
                when 3 then '가을'
                else '겨울'
            end      
            
    ## 지역별로 추출
    select * from gogek
    ##6. 고객테이블에서 거주지역별 인원수(서울, 경기...)
    select
        substr(goaddr,1,2) as 지역,
        count(*) as 인원수
    from gogek
    group by substr(goaddr,1,2)
    
    ##7. 고객테이블에서 출생년대 인원수
    select
        substr(gojumin,1,1) || '0 년대' as 출생년대,
        count(*) 인원수
    from gogek
    group by substr(gojumin,1,1) 
     
    
    ##8. 고객테이블에서 성별인원수
    select
        substr(gojumin, 8, 1) as 성별,
        count(*)
    from gogek
    group by substr(gojumin, 8, 1)
    
    select
        case
            when substr(gojumin, 8, 1) in(1,3,5,7) then '남자'
            else '여자'
        end as 성별,
        count(*) as 인원수
    from gogek
    group by
        case
            when substr(gojumin, 8, 1) in(1,3,5,7) then '남자'
            else '여자'
        end
    
    ##9 부서별인원수에서 그룹통계에 대한 조건절 처리( 일반조건은 where, 그룹조건은 having 사용 )
    select
        deptno,
        count(*)
    from sawon
    where deptno <> 10 --10 번 부서 제외
    group by deptno
    having count(*) >= 5
            
    select * from sawon
    ##10. 사원테이블에서 성씨별 인원수
    select
        case
            when substr(saname, 1, 1) in('장') then '장씨'
            when substr(saname, 1, 1) in('안') then '안씨'
            when substr(saname, 1, 1) in('이') then '이씨'
            when substr(saname, 1, 1) in('최') then '최씨'
            when substr(saname, 1, 1) in('영') then '영씨'
            when substr(saname, 1, 1) in('김') then '김씨'
            when substr(saname, 1, 1) in('맥') then '맥씨'
            when substr(saname, 1, 1) in('배') then '배씨'
            when substr(saname, 1, 1) in('전') then '전씨'
            when substr(saname, 1, 1) in('데') then '데씨'
            when substr(saname, 1, 1) in('감') then '감씨'
            else '최씨'
        end as 성별,
        count(*) as 인원수
    from sawon
    group by
        case
            when substr(saname, 1, 1) in('장') then '장씨'
            when substr(saname, 1, 1) in('안') then '안씨'
            when substr(saname, 1, 1) in('이') then '이씨'
            when substr(saname, 1, 1) in('최') then '최씨'
            when substr(saname, 1, 1) in('영') then '영씨'
            when substr(saname, 1, 1) in('김') then '김씨'
            when substr(saname, 1, 1) in('맥') then '맥씨'
            when substr(saname, 1, 1) in('배') then '배씨'
            when substr(saname, 1, 1) in('전') then '전씨'
            when substr(saname, 1, 1) in('데') then '데씨'
            when substr(saname, 1, 1) in('감') then '감씨'
            else '최씨'
        end
    
    select 
        substr(saname, 1, 1) 성씨,
        count(*) 인원수
    from sawon
    group by substr(saname, 1, 1)
        
    ##11. 년봉별 인원수        
    select * from sawon
    select
        case
            when substr(sapay, 1, 1) in('4') then '4000 대'
            else '최씨'
        end
        
    from sawon


    ##12. 년봉별 인원수(천단위) 4000대 3000대 2000대 1000대        
            
            
            
    ##9. 고객테이블에서 계절별 인원수( 이거 해보기 ) 
    select
        case floor(to_number(to_char(gojumin, 'MM'))/3)
                when 1 then '봄'
                when 2 then '여름'
                when 3 then '가을'
                else '겨울'
            end as season
    from gogek

              
/*         
-- 석래아저씨 코드 ---        
select
        deptno,
        count(*) 
    from sawon
1)  where deptno <> 10
2)  having count(*) >= 5
3)  group by deptno

  
     ## 성별 인원수
    select
        decode(mod(substr(gojumin,8,1),2),
                                1, '남자',
                                0, '여자'
                    ) as 성별,
        count(*) 인원수
    from gogek
    group by decode(mod(substr(gojumin,8,1),2),
                                1, '남자',
                                0, '여자'
                    )


## 입사년대
select
substr(to_char(sahire, 'yyyy'),1,3) 입사년대,
count(*) 인원수
from sawon
group by substr(to_char(sahire, 'yyyy'),1,3)

##입사년도
select
to_char(sahire, 'yyyy') 입사년도,
count(*) 인원수
from sawon
group by to_char(sahire, 'yyyy')


    select
    case floor(to_number(to_char(sahire,'MM'))/3) 
                when 1 then '봄'
                when 2 then '여름'
                when 3 then '가을'
                else '겨울'
        end as "계절",
        count(*) 인원수
    from sawon
    group by case floor(to_number(to_char(sahire,'MM'))/3) 
                when 1 then '봄'
                when 2 then '여름'
                when 3 then '가을'
                else '겨울'
        end
*/
 
 
 
 
 
 
 
 
 
 
 
 
 

/*
    pk => not null, unique+index
    
    [ What is index? ]
    인덱스는 테이블이나 클러스트에서 쓰여지는 선택적인 객체로서, 
    오라클 데이터베이스 테이블내의 원하는 레코드를 빠르게 찾아갈 수 있도록 
    만들어진 데이터 구조이다.
    # B-tree 알고리즘 : 작은 값을 왼쪽, 큰 값을 오른쪽 세팅

    [ B-tree 구조 ]
    1. 100이냐 물어보고
    2. 찾고자하는 값이 현재 값보다 크냐 물어보고
    3. 크면 오른쪽, 작으면 왼쪽을 탐색
    4. 그리고 크면 오른쪽으로 이동
    -- ## index end ## --
    
    -- ## 뷰(view) start ## --
    뷰(view)
    1. 가상의 테이블
        : 테이블과 동일한 방법으로 이용할 수 있다.
    2. SQL 명령을 저장한 개체(Entity)
    3. 목적
        1) 편리성 : 복잡한 명령을 간결하게 이용할 수 있다.(뷰를 만들어서 복잡한 명령을 저장해서 뷰이름을 호출하면 됨)
        2) 보안성 : 

    [ 2. SQL 명령을 저장한 개체(Entity) ]
    ## exam1)
    create or replace view sawon_view_man
    as
        select * from sawon where sasex='남자'        
    
    select * from sawon_view_man    
    
    ## exam2)
    create or replace view sawon_view_season_count
    as
    select
        case floor(to_number(to_char(sahire, 'MM'))/3)
                when 1 then '봄'
                when 2 then '여름'
                when 3 then '가을'
                else '겨울'
            end as 계절,
        count(*) 인원수
    from sawon
    group by
        case floor(to_number(to_char(sahire, 'MM'))/3)
                when 1 then '봄'
                when 2 then '여름'
                when 3 then '가을'
                else '겨울'
            end      
    order by
        case floor(to_number(to_char(sahire, 'MM'))/3)
                when 1 then '봄'
                when 2 then '여름'
                when 3 then '가을'
                else '겨울'
            end
            
    =>view 실행
    select * from sawon_view_season_count
    
    ## exam3)
    /*
        2000년 이전 출생자 
        (내국인) : 남자 1, 여자 2, 
        (외국인) 남자 5, 여자 6으로 주민번호 뒷자리 시작
        
        2000년~ 현재 출생자 
        (내국인) : 남자 3, 여자 4, 
        (외국인) 남자 7, 여자 8으로 주민번호 뒷자리 시작
    */
    
    
    select * from gogek
 
    --            123456789
    -- gojumin='801212-1234560'
    
    create or replace view gogek_view
    as
        select 
            gobun,goname,goaddr, 
            to_number(substr(gojumin,1,6)) birth_day, 
        to_number(substr(gojumin,1,2)) + (
            case
                when substr(gojumin,8,1) in(1,2,5,6) then 1900
                else 2000
            end
            ) as year,
            to_number(substr(gojumin,3,2)) month,
            to_number(substr(gojumin,5,2)) day
        from gogek
        
    ##보안성 테스트 하기위해서 system 계정으로 전환 계정생성
    1. 계정생성
    create user test2 identified by test2
    2. 권한부여(연결권한만 부여)
    grant connect to test2
    
    ##데이터 소유주(dbo.test)가 test2에게 개체사용권한 부여 
    ## 다시 test계정으로 전환
    //개체사용에 대한 권한 부여
    //      select    개체(view) to 대상
    //grant 권한종류 on    개체(view) to 대상
    //grant all on gogek_view to test2(grant select, insert, update, delete on gogek_view to test2)
    grant select on gogek_view to test2
    
    ##Test2계정으로 조회
    (test로부터 gogek_view의 select 권한부여받은내용)
    //              dbo.개체명 (이게 원칙)
    select * from test.gogek_view
    
    
    ### inline 뷰(이름없는 뷰)
    인라인뷰 ( 익명클래스처럼 한번 쓰고 버릴라고 즉, 일시적으로 쓸때.. )
    -- 오라클에서는 아래의 문법 허용안함(다른 SQL은 됨)
    select *, sapay*0.1 as bonus from sawon
    
    -- 오라클에서 "select *, sapay*0.1 as bonus from sawon" 이 문법을 사용하고 싶을때 아래 코드
    select 
        s.*, 
        sapay*0.1 as bonus,
        (select avg(sapay) from sawon) as tot_avg,
        sapay - (select avg(sapay) from sawon) as gap
    from 
        (select * from sawon) s
        
    --(select * from sawon) s 인라인뷰
    ###급여순위
    select 
        s.*,
        rank() over(order by sapay desc) as rank
    from
        (select * from sawon) s
    order by sabun 
    
    --#############################
    -- 5위 이내인것만 보는 방법
    select 
        s.*,
        rank() over(order by sapay desc) as rank
    from
        (select * from sawon) s
    where rank <= 5
    
    --실행순서 1 from, 2 where, 3 select
    --select ~ from 까지 뷰로 만들고 , where를 만들면 문제안됨
    select * from
    (
        select 
            s.*,
            rank() over(order by sapay desc) as rank
        from
            (select * from sawon) s
    )
    where rank <= 5
    
    /* 인라인뷰    
    (
        select 
            s.*,
            rank() over(order by sapay desc) as rank
        from
            (select * from sawon) s
    )
    */
 
*/