1018(23일차) - sql2일

[ 공부 ]

1018(23일차) - sql2일

2023년 10월 18일 11시 48분 55초에 업로드 된 글입니다.

작성자: 삶은고구마

다른 환경에서 설치,세팅 시 주의할 점.

1.utf8 인코딩 설정(가장 먼저 할 것)

2.sh 계정 생성시 아이디와 비밀번호에 공백주의,특히 비밀번호 대소문자 확인

(어떤것으로 했는지 기억하지 못한다면 계정 삭제 후 재생성)

3.sh계정 생성 후 , table 내용이 들어있는 SH_sql파일 스크립트 실행 할 것

그렇지 않으면 테이블이 존재하지 않는다고 오류 발생.

작업 순서 준수 할 것.

Function (함수)

BUILT-IN FUCTION

ORACLE 안에 포함 된 함수
일련의 처리를 묶어서 작성해두고 호출하여 사용 하는 것
반드시 리턴 값이 존재함

단일 행 함수	그룹 함수
행 별로 처리되는 함수	행을 그룹핑하고 그룹별로 실행되는 함수
문자처리함수 숫자처리함수 날짜처리함수 형변환함수 등..	아직 안 배움

1)문자 처리 함수

1-1

length() , lengthb()

길이값을 반환하는 함수

length(값) : number	lengthb(값) : number
길이 값을 반환 length(emp_name)	바이트 수를 반환 lengthb(emp_name)

1-2

instr(src, search[,start_position, occurrence]) : index

찾을 문자열에서 검색할 문자열의 인덱스를 반환. []안은 생략 가능

검색 시작위치, 출현횟수 등을 지정가능.

1-3

substr(src,strt_index[,length]):string

찾을 문자열에서 start_index 부터 length갯수의 문자열을 잘라 반환.

만약 m만 가져오고싶다면 첫번째 sql문은 6,1로 수정하면 될 것이다.

1-4

lpad/rpad (left, right) 여백을 주기
lpad(value, length, padding str):string
rpad(value, length, padding str):string
length에 value를 작성하고 남은 공간에 padding을 왼쪽/오른쪽에 채우기

padding str을 쓰지않는다면 기본적으로 공백으로 채움.

1-5

dbms_random 난수생성.

floor(dbms_random.value()*10000)"랜덤",

java와 비슷한 방식으로 난수 생성이 가능하다.

1-6

정규표현식 REGEXP 함수

문자 검색에 특화된 표현식

regexp_replace(src, regexp, new_str)

src문자열에서 regexp와 일치하는 문자열을
new_str로 변환한 문자열을 반환한다.

숫자만 제거:[[:digit:]]

숫자가 아닌 것을 제거 : [^[:digit:]]

regexp_like(src,regexp):boolean

src문자열에서 regexp 패턴이 있다면 true
없으면 false 반환
직접 t/f 값을 확인할 수 없고 where 조건절로 사용함

위에서는 ^가 not으로 쓰였지만

regexp_like(emp_name,'^(김|이)'); --이때는 ^가 시작을 의미

2)숫자 처리 함수

mod (sql은 %연산이 없음)
ceil(n) / floor(n) , trunc(n) / round(n)

3)날짜 처리 함수

3-1

add_months(date,number) : date

한달 후 , 세달 후 등 날짜 조회

말일 관련 처리 주의

=> 전 달이 다음 달보다 말일이 더 많은 경우

아래 예제처럼 1월 31일의 한 달 뒤를 체크하는데 2월 28일이 나올 수있다.

2월 27일의 한달 뒤를 조회하면 3월 27일이 나옴.

3-2

months_between(future_date, past_date):number

두 날짜 사이의 개월 수를 반환한다.

내년 1월1일과 오늘날짜가 몇개월 남았나 확인가능.

trunc 함수로 소수점 첫째자리까지만 출력하도록 했다.

3-3

extract(년월일시분초 from date/timestamp):number

4)형 변환 함수

문자 - 숫자 - 날짜 사이의 형 변환 지원

숫자에서 char 로 변환할때

to_char(number,format) : char

-숫자에 형식을 부여함.

9:해당자리에 숫자를 의미. 값이 없는 경우 소수점 이상은 공백/소수점 이하는 0으로 처리

0:해당자리에 숫자를 의미. 값이 없는 경우, 소수점이상과 이하 모두 0으로 처리

fm:포맷으로 생긴 앞쪽의 공백 제거

l 혹은 L: \표시

to_char(123456789,'999,999,999'),
to_char(123456789,'9,999'), --###### 이렇게 나옴
to_char(123.456,'FM9,999.99999'), --123.45600 / fm,FM 포맷으로 생긴 앞쪽 공백 제거
to_char(123.456,'0,000.00000'), --0,123.45600
to_char(123456,'FML9,999,999') --왼쪽정렬+\표시

날짜에서 char 로 변환할때

to_char(date,format) : char

hh24 =>24시간

mi=>minute 분 표시

dy=>수 day=>수요일 d=>4 (일요일1~)

select
    sysdate,
    to_char(sysdate,'yy-mm-dd hh24:mi'), --hh24:24시간
    to_char(sysdate,'yyyy"년" mm"월" dd"일"(dy) dy day d') --dy:수   day:수요일   d:4
from
    dual;

char에서 숫자 로 변환할때

to_number(char,format) : number

char에서 날짜 로 변환할때

to_date(char,format) : date

select
    to_date('19900909', 'yyyymmdd'),
    to_date('1990년02월06일(화)', 'yyyy"년"mm"월"dd"일"(dy)'),
    /*
    yyyy ,rrrr 둘다 네자리 년도 읽을 때엔 동일 but 2자리 년도일땐 차이가 있음
    */
    to_date('990909','yymmdd'), --yy:현재 세기 기준 (2000~2099) -2099
    to_date('990909','rrmmdd') --rr:이전세기 50년부터 현재세기 49사이(1950~2049) -1999
from
    dual;

5)기타 함수

null 처리
nvl(value,null일 때 값)
nvl2(value,null이 아닐 때 값,null일 때 값)

그 외에!

선택함수

decode
case1 (decode와 동일)
case2(if else if와 비슷)

decode

decode(expression,result1,value1,result2,value2,.... , [,default value]):value

decode(substr(emp_no,8,1),'1','남','3','남','2','여','4','여') gender

이런식으로 쓰인다 특정값이 1이면 남자, 3이면 남자, 2면 여자, 4면 여자...

여러 개 사용 가능함. 마지막 값은 기본값인데 생략이 가능함.

case1(decode 와 동일)

case substr(emp_no,8,1)
        when '1' then '남'
        when '2' then '여'
        when '3' then '남'
        when '4' then '여'
    end gender

주민번호 뒷자리가 1로 시작하면 남자, 2면 여자를 case1 방식으로 작성

case2(if else if 와 동일)

case
        when substr(emp_no,8,1) in('1','3') then '남'
        else '여'
    end gender

동일한 방식인데 1이나 3이면 남자, else로 여자라고 체크한다.

else인 경우는 1,3이 아닌 즉, 2,4일때!

금일 수업 코드(어제 생성한 oracle_sh.sql에 이어서 진행하였다.

--23.10.18
--============================================
-- BUILT-IN FUCTION
--============================================
--oracle 안에 포함된 함수
--일련의 처리를 묶어서 작성해두고 호출하여 사용하는 것.
--반드시 리턴값이 존재함

/*
구분
1.단일 행 함수 - 행 별로 처리되는 함수
    문자 처리 함수, 숫자 처리 함수, 날짜 처리 함수,형 변환 함수, 기타....
2.그룹 함수 - 행을 그룹핑하고 그룹별로 실행되는 함수
*/

-----------------------------------------------------------
--문자 처리 함수
-----------------------------------------------------------
--length(value):number 길이 값을 반환
--lengthb(value):number 바이트 수를 반환
select
    emp_name,
    length(emp_name) "길이",
    lengthb(emp_name) "바이트 수",
    email,
    length(email)"길이",
     lengthb(email)"바이트 수"
from
    employee;
    
--instr(src, search[,start_position , occurrence]):index
--src문자열에서 search 문자열의 인덱스를 반환 []는 생략가능
--검색 시작위치,출현횟수등을 지정가능.

select
    instr('kh정보교육원 국가정보원 정보문화사', '정보')예제1,--index는 1부터 시작
    instr('kh정보교육원 국가정보원 정보문화사', '정보',1,2)예제2,
     instr('kh정보교육원 국가정보원 정보문화사', '정보',-1)예제3 --음수인 경우 뒤에서부터 검색..
 from
    dual;

--substr(src, start_index[,length]):string
--src문자열에서 start_index부터 length갯수의 문자열을 잘라 반환

select
    substr('show me the moeny',6,2), --me
    substr('show me the moeny',6), --me the money
    substr('show me the moeny',-5) --money
from
    dual;
    
--ex)사원테이블에서 이메일 앞의 아이디만 조회

select
    substr(email,1,instr(email, '@')-1) "메일 아이디"
 from
    employee;

--ex)사원 테이블에서 사원들의 성만 가나다순으로 조회 중복x 모든 성은 한글자임

select distinct
    substr(emp_name,1,1) "성"
from
    employee
order by
    "성";



--lpad/rpad (left, right) 여백을 주기
--lpad(value, length, padding str):string
--rpad(value, length, padding str):string
--length에 value를 작성하고 남은 공간에 padding을 왼쪽/오른쪽에 채우기
select
    lpad(email,20), --안쓰면 공백으로 채움(defalut)
    rpad(email,20,'%')
from
    employee;

--dbms_random 난수생성
select
    lpad(123,5,0),
    lpad(1,5,0),
    'kh-'||to_char(sysdate,'yymmdd')||'-'||lpad(123,5,0)"주문번호", --주문번호처럼 만들어보기
    floor(dbms_random.value()*10000)"랜덤",
    'kh-'||to_char(sysdate,'yymmdd')||'-'||lpad(floor(dbms_random.value()*10000),5,0)"랜덤 주문번호"
from
    dual;


-- 정규표현식 REGEXP 함수 
-- 문자 검색에 특화된 표현식
-- regexp_replace(src,regexp,new_str)
-- src문자열에서 regexp와 일치하는 문자열을 new_str로 변환한 문자열 반환
select
    '2023/12/25 22:30:55',
    regexp_replace('2023/12/25 22:30:55', ' |/|:',''), --가운데 해당하는 공백,슬래시,:를 ''로 변경
    '242352345435hellowolrd34523523531hahaha',
    regexp_replace('242352345435hellowolrd34523523531hahaha', '[[:digit:]]',''), --숫자만 제거
    regexp_replace('242352345435hellowolrd34523523531hahaha', '[^[:digit:]]','') --숫자가 아닌것을 제거 ^:부정
from
    dual;

--regexp_like(src,regexp):boolean t/f를 확인하는데 직접 확인할 순 없고 where절에서사용
--src문자열에서 regexp패턴이 있으면 true,없으면 false
select
    emp_name
from
    employee
where
   -- emp_name like '김%' or emp_name like '이%',
  --  substr(emp_name,1,1) in ('김','이');
    regexp_like(emp_name,'^(김|이)'); --이때는 ^가 시작을 의미

--남자사원만 조회
select
    emp_name,
    substr(emp_no,8,1)
from
    employee
where 
regexp_like(substr(emp_no,8,1),'^(1|3)'); --혹은 in('1','3')

select * from employee;


-------------------------------------------------------
--숫자 처리 함수
-------------------------------------------------------
--mod(m,n):remainder 
--sql은 %연산 지원안함. 

select
    mod(10,3) "나머지"
from
    dual;
    
--ex)생일이 짝수인 사원만 조회..
select
    *
from
    employee
where
    mod(substr(emp_no,1,6),2)=0;

--질의 결과에서 문자열은 왼쪽 정렬/숫자는 오른쪽 정렬

--ceil(n)올림
--floor(n)버림 | trunc(n,소수점이하자리수)
--round(n,소수점이하자리수)반올림

select
    ceil(123.456), --124
    
    ceil(123.456 *100 )/100"test", --123.46
    
    floor(123.456), --123
    round(123.456), --123
    round(123.567), --124
    round(123.456,2),--123.46 반올림됨
    trunc(123.456,2) --123.45 
from
    dual;

-----------------------------------------------------
--날짜 처리 함수
-----------------------------------------------------
-- add_months(date, number) : date
-- 1달 후 날짜, 3달 후 날짜 조회
-- 말일 관련 처리 주의
select
    add_months(sysdate,1), --1달 뒤 / -1이면 한달 전
    add_months(to_date('20230131','yyyymmdd'),1), --1월 31일의 한달 뒤는 2월 31일? 하지만 28일까지나옴
     add_months(to_date('20230228','yyyymmdd'),1) --3월 31일
from 
    dual;
    
--입사 후 3개월 뒤 정식사원으로 계약하게 된다.
--사원들이 정식사원이 된 날을 조회 (이름,날짜)
select
    emp_name,
    add_months(hire_date,3)
from
    employee;

--months_between(future_date, past_date):number
--두 날짜 사이의 개월수 반환

select
    months_between('20240101',sysdate),
    trunc(months_between('20240101',sysdate),1) --2.4개월
from 
    dual;

--근무 개월수 조회
--이름 n개월 m년 k개월

select
    emp_name,
    hire_date,
   -- abs(trunc(months_between(hire_date,sysdate),1))                    ||'개월',
   -- floor(abs((trunc(months_between(hire_date,sysdate),1))/12))             ||'년' "년", 
    --floor(mod(abs(trunc(months_between(hire_date,sysdate),1)),12))         ||'개월' "개월"       
    
    trunc(months_between(nvl(quit_date, sysdate),hire_date)),
    floor(trunc(months_between(nvl(quit_date, sysdate),hire_date))/12) 년,
    trunc(mod(months_between(nvl(quit_date, sysdate),hire_date),12)) 개월
from employee;





--extract(년월일시분초 from date/timestamp): number
select
    extract(year from sysdate) year,
    extract(month from sysdate) month,
    extract(day from sysdate) day,
    extract(hour from cast(sysdate as timestamp)) hour,
    extract(minute from cast(sysdate as timestamp)) minute,
    extract(second from cast(sysdate as timestamp)) second
    
from 
    dual;
    

    
    
 --ex)2001년 입사자의 정보 조회
select
    *
from 
    employee
where 
    extract(year from hire_date) = 2001;
    

--trunc(date): date
--시분초정보 제거
select 
    sysdate,
    trunc(sysdate) --시분초 날라감
from
    dual;
    
-----------------------------------------------
--형변환 함수
-----------------------------------------------
-- 문자/숫자/날짜 사이의 형변환 지원
/*
                 
                to_char                to_date 
            --------------->     ----------------->
     number                char                     date
          <----------------    <-------------------
              to_number              to_char
     
*/

--to_char (number, format):char
--숫자에 형식부여
--9 : 해당자리에 숫자를 의미.값이 없는 경우, 소수점 이상은 공백, 소수점 이하는 0으로 처리
--0 : 해당자리에 숫자를 의미.값이 없는 경우, 소수점 이상,이하 모두 0으로 처리
--format의 자릿수는 항상 실제 값보다 커야함.

select
    to_char(123456789,'999,999,999'),
    to_char(123456789,'9,999'), --###### 이렇게 나옴
    to_char(123.456,'FM9,999.99999'), --123.45600 / fm,FM 포맷으로 생긴 앞쪽 공백 제거
    to_char(123.456,'0,000.00000'), --0,123.45600
    to_char(123456,'FML9,999,999') --왼쪽정렬+\표시
from 
    dual;
    
    
--to_char(date,format):char
--형식문자는 대소문자 구분 안함
select 
    sysdate,
    to_char(sysdate,'yy-mm-dd hh24:mi'), --hh24:24시간
    to_char(sysdate,'yyyy"년" mm"월" dd"일"(dy) dy day d') --dy:수   day:수요일   d:4
from
    dual;
    
select * from employee;
--사원테이블에서 사원명 급여,보너스율, 입사일 조회
--급여:세자리콤마
--보너스율:%로 표현(null인 경우 0%)
--입사일:년월일(요일)


select
    emp_name "사원명",
    to_char(salary,'999,999,999') "급여", 
    nvl(bonus,0)*100  ||'%',                         --to_char(nvl(bonus,0),'0.0')||'%',
    to_char(hire_date,'yyyy"년" mm"월" dd"일"(dy)') 
    
from
    employee;


--to_number(char ,format):number

select
    to_number('1,234,567','9,999,999'),  --바로 더하기연산해도 작동 잘됨
    to_number('1234567','FML9,999,999') 
from
    dual;

--to_date(char,format) : date
select
    to_date('19900909', 'yyyymmdd'),
    to_date('1990년02월06일(화)', 'yyyy"년"mm"월"dd"일"(dy)'),
    /*
    yyyy ,rrrr 둘다 네자리 년도 읽을 때엔 동일
    */
    to_date('990909','yymmdd'), --yy:현재 세기 기준 (2000~2099) -2099
    to_date('990909','rrmmdd') --rr:이전세기 50년부터 현재세기 49사이(1950~2049) -1999    
from
    dual;
    
---------------------------------------------------
--기타 함수
---------------------------------------------------
--null처리
--nvl(value, null일때값)
--nvl2(value, null이 아닐때값,null일때값)
select
    emp_name,
    bonus,
    nvl2(bonus, 'o','x') 
from
    employee;
    
--선택함수
--decode(expression,result1,value1,result2,value2,.... , [,default value]):value
--성별 조회

select
    emp_name,
    emp_no,
    decode(substr(emp_no,8,1),'1','남','3','남','2','여','4','여') gender,
    decode(substr(emp_no,8,1),'1','남','3','남','여') gender,
    decode(substr(emp_no,8,1),'2','여','4','여','남') gender
from
    employee;


--ex)직급명 조회
--job table
select
    emp_name,
    job_code,
    decode(job_code, 
                'J1', '대표', 
                'J2', '부사장', 
                'J3', '부장',
                'J4', '차장',
                'J5', '과장',
                'J6', '대리',
                '사원') job_name
from
    employee;

--case1 (decode와 동일)
/*
    case expression
        when result1 then value1
        when result2 then value2
        when result3 then value3
        .........
        else default_value
    end


*/

select
    emp_name,
    emp_no,
    case substr(emp_no,8,1)
        when '1' then '남'
        when '2' then '여'
        when '3' then '남'
        when '4' then '여'
    end gender,
    case substr(emp_no,8,1)
        when '2' then '여'
        when '4' then '여'
        else '남'
    end gender
from
    employee;


--case2 (if else if와 비슷)
/*
    case 
        when condition1 then value1  --condition:boolean
        when condition2 then value2
        ..
        [else default_value]
    end
*/
select 
    emp_name,
    emp_no,
   /* case
        when substr(emp_no,8,1)='1' then '남'
        when substr(emp_no,8,1)='2' then '여'
        when substr(emp_no,8,1)='3' then '남'
        when substr(emp_no,8,1)='4' then '여'
    end gender,
    */
     case
        when substr(emp_no,8,1) in('1','3') then '남'
        else '여'
    end gender,
    
    case
        when substr(emp_no,8,1) in('2','4') then '여'
        else '남'
    end gender
    
from
    employee;
    

    
    
--직급 출력 J1=대표, J2/J3 = 임원, 나머지는 평사원
select 
    emp_name,
    job_code,
    case
        when job_code = 'J1' then '대표'
        when  job_code in ('J2','J3' )then '임직원'
        else '평사원'
    end jobname
from
    employee;
    
    
--나이 계산

select
    emp_name,
    emp_no,
    to_date(substr(emp_no,1,2),'yy'),
    to_date(substr(emp_no,1,2),'rr'),
    decode(substr(emp_no,8,1),'1',1900,'2',1900,2000)+substr(emp_no,1,2) birthyear,
    extract(year from sysdate)- (decode(substr(emp_no,8,1),'1',1900,'2',1900,2000)+substr(emp_no,1,2)) age
    만나이 -- (현재날짜 - 생일)/365 , months_between(현재날짜, 생일)/12
    
from
    employee;



--1018 숙제
--employee 테이블에서 남자만 사원번호, 사원명, 주민번호, 연봉을 나타내세요.
--단, 주민번호의 뒷6자리는 *처리하세요.


    
--남자사원만 조회
select
    emp_id "사원번호",
    emp_name "사원명",
    rpad(substr(emp_no,1,8),14,'*')  "주민번호",
    salary "연봉"

from
    employee
where 
regexp_like(substr(emp_no,8,1),'^(1|3)'); --혹은 in('1','3')

    
    
select * from employee;


commit;

저작자표시 비영리

'공부' 카테고리의 다른 글

1019(24일차) - sql 3일 (0)	2023.10.19
oracle sql developer 오류 (0)	2023.10.18
1017(22일차) - 오라클,sql기초 (1)	2023.10.17
1013(20일차) (0)	2023.10.13
1012(19일차) 수정중.. (0)	2023.10.12

다음글이 없습니다.

이전글이 없습니다.