230623 빠진 수업 내용4 Oracle SQL [ROUND, TRUNC, 날짜 함수, 형변환]

권주희·2023년 7월 14일
0
post-custom-banner

이전 내용 정리

  1. 오라클, mySQL 설치
  2. 기본 select문

    select + 보고싶은 컬럼명
    from 테이블명
    where 검색조건
    order by 정렬할 컬럼명
    내부 실행 순서는 from -> where -> select -> order by

  3. 함수
    1. 단일행 함수: 문자, 숫자, 날짜, 변환, 일반
    • 단일행 함수의 문자함수
      upper, lower, inicap, substr, instr, replace, length, trim
    1. 복수행 함수: max, min, avg, sum, count

오늘의 TIL

  1. 반올림해서 출력하기(ROUND)
  2. 숫자를 버리고 출력하기(TRUNC)
  3. 나눈 나머지 값 출력하기(MOD)
  4. 날짜 간 개월 수 출력하기(MONTHS_BETWEEN)
  5. 개월 수 더한 날짜 출력하기(ADD_MONTHS)
  6. 특정 날짜 뒤에 오는 요일 날짜 출력하기(NEXT_DAY)
  7. 특정 날짜가 있는 달의 마지막 날짜 출력하기(LAST_DAY)
  8. 문자형으로 데이터 유형 변환하기(TO_CHAR) 날짜-> 문자 / 숫자-> 문자
  9. 날짜형으로 데이터 유형 변환하기(TO_DATE) 문자 -> 날짜
  10. 암시적 형 변환 이해하기

? 생각 해 볼 문제
이름과 태어난 요일을 출력하는데, 태어난 요일이 월화수목금토일로 정렬이 되어서 출력되게 하세요.

반올림해서 출력하기(ROUND)

숫자함수 3가지?

  1. ROUND (반올림 하는 함수)
  2. TRUNC
  3. MOD

EX) 

select round (787.678, 2)
 from dual;

787.68 인데, 787.678 에서 소수점 기준(소수점뒤로)으로 6이 1, 7이 2 이다. 그래서 맨뒤에있는 8이 반올림되어서787.68이 되었다. 0은 소수점이라고 보면 되서, (787.678, 0) 쓰면 소수점 뒤에있는 6이 반올림되서 788이 나온다.
음수도 쓸 수 있는데, 787.678에서 앞자리 부터 7(-3) / 8(-2) / 7(-1)

  • dual -> 결과값만 보기 위한 가상의 테이블(dummy table) - mysql에도 있다.

문제 86. emp 테이블에서 이름, 월급을 출력하고 월급의 십의 자리에서 반올림해서 출력

select ename, sal, round(sal, -2)
 from emp;

  • 왜 음수 썼냐면 누구는 3000, 20000 이면 반올림되는 자리가 다르니까

숫자를 버리고 출력하기(TRUNC)

7 8 7 . 6 7 8
      0 1 2   // 이거 자리수

select trunc(787.678,2)
from dual;
이렇게 쓰면 결과는 787.67 이 나온다. 2번째 자리 기준으로 뒤를 버린다.

문제 87. 우리반 테이블에서 이름, 나이, 나이의 일의자리 숫자를 버려서 출력해보기

select ename, age, trunc(age, -1) 
 from emp17;

문제 88. 위의 SQL을 수정해서 이름, 나이대를 출력하시오.
ex)
이름 나이대
김하람 30대
윤영민 20대
.
.

select ename as 이름 , trunc(age, -1) || '대' as "나이대"
  from emp17;
  • 오라클의 trunc 함수는 mySQL에서는 truncate 임!
select ename as "이름" , truncate(age, -1)  as "나이대"
  from emp17;

문제 89. 나이가 20대인 학생들의 이름, 나이를 출력하는데, 나이가 높은 학생부터 출력

select ename, age
 from emp17
 where age like '2%' // between 20 and 29 
 order by age desc;

나눈 나머지 값 출력하기(MOD)

10 / 3 하면 나머지가 1인데, 이 1을 출력하는 함수

select mod(10,3)
 from dual;

문제 90. 이름, 나이, mod함수를 이용해서 나이를 2로 나눈 나머지값 출력 

select ename, age, mod(age, 2)
 from emp17;

문제 91. 우리반에서 나이가 짝수인 학생들의 이름, 나이 출력

select ename, age
 from emp17
 where mod(age, 2) = 0; // 나머지가 0이 나오면 짝수니까

날짜 간 개월 수 출력하기(MONTH_BETWEEN)

단일행 함수 ( 문자, 숫자, 날짜, 변환, 일반 )

  • 날짜 - 날짜 = 숫자

  • 날짜 - 숫자 = 날짜

  • 날짜 + 숫자 = 날짜

  • 날짜 + 날짜 = 안됨X

오늘 날짜 보는 키워드! : sysdate

select sysdate
 from dual;

문제 92. 오늘날짜 - 입사일을 출력

select sysdate - hiredate
 from emp;


입사해서 지금까지 총 일수 나오는데, 소수점이 나오는 이유는 시간까지 계산되는 것.

문제 93. 위 결과에서 소수점 이후는 버리고 출력하기

select trunc(sysdate - hiredate, 0)
 from emp;

문제 94. 내가 태어난 날짜부터 오늘까지 총 몇일 살았는지 출력

문제 95. 이름, 태어난 날짜부터 오늘까지 총 몇 주 살았는지 출력

수업에서 한거

select ename, trunc(trunc(sysdate - birth)/7)
 from emp17;

내가한거

select ename, trunc((sysdate - birth)/7, 0)
 from emp17;

문제 96. 이름, 태어난 날짜부터 오늘까지 총 몇 달 살았는지 출력

  • months_between (날짜1, 날짜2)
    : 날짜2 ~ 날짜` 사이의 개월수를 출력합니다 !!! (다른 디비에서는 이게 아니라서 시험에 자주나오는건 아닌데 ocp시험에서는 나옵니당.)
  • 최신날짜를 뒤에 써야한다.months_between (최신날짜, 옛날날짜) 지켜줘야하고, 반대로 쓰면 음수값이 나온다 !!! (ocp출제.)
    select ename, months_between(sysdate, birth)
 from emp17;

문제 97. 그동안 살아온 달이 300달 이상인 학생들의 이름, 나이, 살아온 개월수 출력

select ename, age, trunc(months_between(sysdate,birth),0)
 from emp17
 where trunc(months_between(sysdate,birth),0) >= 300;

개월 수 더한 날짜 출력하기(ADD_MONTHS)

예제. 오늘 날짜에서 3일을 더한 날짜

예제. 오늘 날짜에서 3달 뒤의 날짜를 출력하시오! (ADD_MONTHS)사용 

select add_months(sysdate ,3)
 from dual;

문제 98. 이름, 입사일, 입사한 날짜 100달 을 출력하세요.

select ename, hiredate, add_months(hiredate, 100)
 from emp;

문제 99. 위 문제 MySQL 에서 -> add_months 아니고 date_add!!, interval 사용

select ename, hiredate, date_add(hiredate, interval 100 month)
 from emp;

문제 100. 아래의 SQL을 mySQL에서 실행 -> sysdate는 sysdate() 로 써야하고
months_between 는 timestampdiff()로 실행된다.

select ename, hiredate, months_between(sysdate, hiredate)
 from emp;

mySQL

select ename, hiredate, timestampdiff(month, hiredate, sysdate())
 from emp;

  • 월단위는 timestampdiff, 일단위는 
select ename,hiredate,datediff(sysdate(),hiredate)
from emp

날짜 함수 오라클, mySQL 차이!

MySQL에서는 SYSDATE() 함수를 사용하여 현재 날짜와 시간을 가져올 수 있습니다. 이 함수는 Oracle의 SYSDATE와 유사한 동작을 합니다.

CURDATE(): CURDATE() 함수는 현재 날짜를 "YYYY-MM-DD" 형식의 문자열로 반환합니다. 시간 정보는 포함되지 않습니다.

NOW(): NOW() 함수는 현재 날짜와 시간을 "YYYY-MM-DD HH:MI:SS" 형식의 문자열로 반환합니다. 날짜와 함께 시간까지 포함됩니다.

SYSDATE(): SYSDATE() 함수는 MySQL에서도 사용 가능한 함수로, 현재 날짜와 시간을 "YYYY-MM-DD HH:MI:SS" 형식의 문자열 또는 DATETIME 데이터 타입으로 반환합니다.

문제 101. 오늘날짜, 오늘날짜에서 100달 뒤 출력

select sysdate, add_months(sysdate, 100)
 from dual;

문제 102. 오늘날짜, 오늘날짜에서 10년 뒤의 날짜를 출력

  • add_years라는 함수는 없다!
    select sysdate + (interval '10' year)
 from dual;
    • interval '숫자' year
      interval '숫자' month
      interval '숫자' day, hour, minute, second .. 다된다 !, 숫자에 싱글 꼭 둘러줘야함!

특정 날짜 뒤에 오는 요일 날짜 출력하기(NEXT_DAY)

오라클 날짜 함수 4가지
1. months_between : 날짜와 날짜 사이의 개월수를 출력하는 함수
2. add_months : 날짜에서 개월수를 더한 날짜를 출력하는 함수
3. next_day : 특정 날짜 뒤에 돌아오는 특정 요일의 날짜를 출력하는 함수
4. last_day : 특정 날짜의 달에 마지막 날짜를 출력하는 함수

예제. 오늘 날짜에서 바로 돌아오는 월요일의 날짜를 출력하기

select sysdate as "오늘", next_day(sysdate, '월요일') 
 from dual;
  • next_day(특정날짜, 요일) --> 특정 날짜 뒤에 돌아오는 요일 의 날짜를 출력

문제 103. 오늘부터 100달 뒤에 돌아오는 월요일의 날짜를 출력

두개로 품

select sysdate as "오늘", next_day(add_months(sysdate, 100), '월요일') 
 from dual;
select sysdate as "오늘", next_day(sysdate + (interval '100' month), '월요일') 
from dual;
  • 함수는 위와같이 중첩해서 사용할 수 있다.

문제 104. 직업이 salesman이 아닌 사원들이고, 월급이 1200이사ㅏㅇ인 사원들의 이름, 월급, 직업 출력 근데 월급 높은 사원부터 출력

select ename, sal, job
 from emp
 where job != 'SALESMAN' and sal >= 1200
 order by sal desc;

특정 날짜가 있는 달의 마지막 날짜 출력하기(LAST_DAY)

예제. 오늘날짜, 오늘날짜의 달에 마지막 날짜를 출력하시오!

select sysdate, last_day(sysdate)
 from dual;

문제 105. 오늘부터 이번달 말일까지 총 몇일 남았는지 출력

select  last_day( sysdate )  -  sysdate 
  from  dual;

문제 106. 위 문제를 mySQL에서

 SELECT  datediff(LAST_DAY(sysdate() ),  sysdate()  ) 
 from    dual;

문제 107. 사원 테이블에서 이름에 S를 포함하고 있는 사원들의 사원이름, 입사일 출력하는데 최근에 입사한 사원부터 출력

select ename, hiredate
 from emp
 where ename like '%S%'
 order by hiredate desc;

문자형으로 데이터 유형 변환하기(TO_CHAR)

데이터 유형을 변환하는 함수 !

오라클의 데이터 유형은 크게 3가지가 있다.
1. 문자형(이름, 직업..) 2. 숫자형(월급, 사원번호, 커미션, 부서번호...) 3. 날짜형(입사일..)

emp 테이블의 컬럼이 뭐가있고, 컬럼의 데이터 유형이 어떤것이 있는지 확인하려면!

describe emp; 혹은 desc emp; -> 라고 쓴다.

  • varchar2는 문자형

숫자형 -> 문자형 (TO_CHAR: 문자형으로 변환하는 함수)
문자형 -> 날짜형 (TO_CHAR)

문자형 -> 숫자형 (TO_NUMBER: 숫자형으로 변환하는 함수)
문자형 -> 날짜형 (TO_DATE: 날짜형으로 변환하는 함수)

1. 날짜형 -> 문자형

  • 내가 태어난 요일이 어떻게 되는가? -> 변환함수 사용하면 알 수 있다.
    문제 108. 오늘날짜, 오늘 요일을 출력하시오
    select sysdate, to_char(sysdate, 'day') //'day' 자리에 날짜형식에 맞춰서 출력해줌
 from dual;
  • 날짜형식은 책 p.93 참고!
  1. 년도 : RRRR , YYYY, RR, YY
  2. 월 : MM, MON
  3. 일 : dd
  4. 주 : WWW, IW, W
  5. 시간: HH, HH24
  6. 분 : MI
  7. 초 : SS
  8. 요일 : DAY, DY, D
TO_CHAR(날짜, 날짜형식) -> 날짜를 날짜형식에 맞춰서 문자로 출력해준다 !

문제 109. 이름 생일, 생일의 요일 출력

select ename, birth, to_char(birth, 'day')
 from emp17;

문제 110. 이름, 입사일, 입사한 년도, 입사한 달, 입사한 일, 입사한 요일

select ename, hiredate , to_char(hiredate, 'RRRR') , 
                         to_char(hiredate, 'MM'), 
                         to_char(hiredate, 'dd') , 
                         to_char(hiredate, 'day')
 from emp;

문제 111. 1981년도에 입사한 사원들의 이름, 입사일 출력 (like, between...and 말고 to_char이용하기)

select ename, hiredate
 from emp
 where to_char(hiredate, 'RRRR') = '1981';
  • WHERE절에 쓸 수 있다!

문제 112. 우리반에서 월요일에 태어난 학생들의 이름, 생일 출력

select ename, birth 
 from emp17
 where to_char(birth, 'day') like '월%'; //  where to_char(birth, 'day') = '월요일';

문제 113. 우리반 테이블에서 5월에 태어난 학생들의 이름, 생일을 출력

select ename, birth 
 from emp17
 where to_char(birth, 'MM') = '05';

문제 114. 이름, 생일, 태어난 요일, 태어난 요일의 축약, 태어난 요일의 번호를 출력

select ename, birth, to_char(birth, 'day'),
                     to_char(birth, 'dy'),
                     to_char(birth, 'd')
  from emp17;

문제 115. 이름과 태어난 요일을 출력하는데, 태어난 요일이 월화수목금토일로 정렬이 되어서 출력되게 하세요.

select ename, to_char(birth, 'day')
 from emp17
 order by to_char(birth - 1, 'd') asc;
  • 내가 만약 화요일에 태어났으면 월요일 이니까 ! birth - 1 하면 2가 1로 바뀌니까 .
    -1 안해주면 일 -> 월 -> 화 -> 순으로 나온다

지금까지 우리는 날짜형을 문자형으로 바꾼 것 !!! 이제 숫자형을 문자형으로 바꿔보자

2. 숫자형 -> 문자형 (금융, 제조업)

문제 116. 이름, 월급을 출력하는데, 월급을 출력할 때 천단위를 표시하세요

select ename, to_char(sal, '999,999') // (sal, 'L999,999')이렇게 L쓰면 로컬 화폐단위가 나온다!! // (sal, '$999,999') 쓰면 $가 앞에 붙어나옴

  • 9는 해당 자리를 의미하고, 이 자리에 0~9까지 어떤 숫자가 와도 관계없다.like쓸때 언더바( _ ) 같은 것..!
  • 한국에서 일본 엔화 표기하고싶으면 어떻게 할까..?

문제 117. (sal, '$999,999') 이렇게 $를 붙여 위 SQL을 다시 출력하는데, 월급이 높은 사원부터 이름, 월급을 출력

select ename, to_char(sal, '$999,999')
 from emp
 order by sal desc; // 여기 sal 자리에 숫자 2 넣어도 정렬 잘 된다.

날짜형으로 데이터 유형 변환하기(TO_DATE)

1. 문자형 -> 날짜형

예제. 81년 11월 17일에 입사한 사원들의 이름, 입사일을 출력

select ename, hiredate
 from emp
 where hiredate = '81/11/17'; // 이건 우리나라 날짜 형식이고 다른나라 가면 17/11/81 해야함!

그런데 어느나라에서든 무조건 sql 나오게 하고 싶으면,
where hiredate = to_date('81/11/17', 'RR/MM/DD'); 로 써준다!

  • 날짜는 무조건 이렇게 배워야한다!!!

    아래는 같은 데이터가 뽑히긴 하지만 이건 악성 SQL이다 !!!!

    select ename, hiredate
 from emp
 where to_char(hiredate, 'RR/MM/DD') = '81/11/17';

    왼쪽은 가급적 컬럼명만 놔둬야한다. 아래처럼 SQL 짜기!!

    select ename, hiredate
 from emp
 where hiredate = to_date('81/11/17', 'RR/MM/DD');

문제 118. 81년도에 입사한 사원들의 이름, 입사일 출력

튜닝 전

 select ename, hiredate
 from emp
 where to_char(hiredate, 'RRRR') = '1981'; // = 기준으로 좌변, 우변

좌변이 너무 더럽다. 최대한 건들면 안됨
내 오답 

select ename, hiredate
 from emp
 where hiredate = to_date('81', 'RR');

튜닝 후 - between ... and 사용!

select ename, hiredate
  from emp
  where hiredate between to_date('81/01/01', 'RRRR/MM/DD') 
                     and to_date('81/12/31', 'RRRR/MM/DD');

문제 119. 우리반 테이블에서 1993년과 1994년에 태어난 학생들의 이름, 생일, 나이 출력

내 오답 -> 비트윈이니까 사이 계산하려면 2번째 처럼 적어주어야 했다.

select ename, birth, age
 from emp17
 where birth between to_date('1993', 'YYYY') and to_date('1994', 'YYYY');
select ename, birth, age
 from emp17
 where birth between to_date('93/01/01', 'RRRR/MM/DD') 
                 and to_date('94/12/31', 'RRRR/MM/DD');
  • TO_DATE쓰지 않으면 다른나라에서 X
    지금 현재 database 에서 설정된 날짜 형식 확인하기!!!
select *
 from nls_session_parameters; //national language support 국가별 언어 지원!

  • 미국 환경으로 현재 세션의 날짜 형식을 변경해보자!
 alter session set nls_date_format = 'DD/MM/RR';



위 사진처럼 TO_DATE쓰지 않으면 미국환경에서는 에러가 난다.
바뀌었다!
궁금한 코드는.. IN쓰는건데
1. 내 오답1

select ename, birth, age
 from emp17
 where birth in (to_date('1993', 'YYYY') , to_date('1994', 'YYYY'));
  1. 내 오답2
select ename, birth, age
 from emp17
 where birth LIKE '%(to_date('%93', 'RRRR'))%' OR '%(to_date('94', 'RRRR'))%';
  1. 정답 -> LIKE 연산자는 문자열에만 사용이 가능해서, 날짜값을 검색할 수 없다고 한다.
    그래서 문자열 형태로 날짜 값을 변환한 SQL인데, 컬럼이 가공된거라서 아마 악성이겠지? (데이터는 잘 나옴)
    select ename, birth, age
from emp17
WHERE TO_CHAR(birth, 'RRRR') LIKE '%93%' OR TO_CHAR(birth, 'RRRR') LIKE '%94%';

암시적 형 변환 이해하기

형 변환 작업은 2가지가 있다.
1. 명시적 형변환 : TO_CHAR , TO_DATE , TO_NUMBER
2. 암시적 형변환 : 오라클이 알아서 형변환을 수행

예제. 다음의 SQL은 에러 없이 수행이 잘 될까? 

SELECT ename, sal
  FROM emp
  WHERE sal = '3000';
  • 숫자는 양쪽에 싱글 쿼테이션 마크를 쓸 필요 없는데, 개발자가 실수로 써준 SQL일 때,
    오라클이 알아서 다음과같이 수행한다. WHERE sal = 3000; (알아서 ' ' 뺌)

    진짜인지 오라클이 내부족으로 수행한 실행계획과 sql을 확인해보자!

explain plan for
 select ename, sal 
  from emp
  where sal = '3000'; // 순서
  
select * from table(dbms_xplan.display); // 대로

  • 확인해보면 오라클이 ' ' 안쓰고 실행을 했다. 알아서 문자를 숫자로 변경한 것!
  • 숫자형 컬럼 = 숫자 이거나 문자형 컬럼 = 문자 여야 하는데, sal(숫자) = '3000'(문자) 이건 안되는 거다 원래!
  • 문자와 숫자를 비교하면 숫자가 우선순위가 높아서, 문자를 숫자로 변경해준다.
    아래 경우는 %를 숫자로 바꿀수가 없어서 위에서는 sal을 문자형으로 암시적 형변환이 발생
    -> 튜닝 방법은 인덱스 단원 배울 때 배우게 된다!
select ename, sal
  from emp
  where sal like '30%';
      (숫자컬럼)   (문자)

분명 볼 것임..! 너무 느리다면 plan 해보기!

table access full 엄청 안좋은것!! -> 책을 처음부터 끝까지 다 본것

수업에 필요한 table 만들기 - emp100

create table emp100
(ename varchar2(10), // 이름 문자형으로 10자리 허용
sal    varchar2(10)); // 월급 문자형으로 10자리 허용 -> 원래 숫자인데 문자로 만듦!

insert into emp100 values('scott', '3000'); // sal 원래는 숫자인데 여기서 문자로 만들었으니까 ' ' 썼다.
insert into emp100 values('smith', '1200');

commit;

위 테이블에서 

select ename, sal
 from emp100
 where sal = '3000';

이렇게 문자형으로 써줘야 한다. 그런데 '3000' 아닌 3000 이라고 쓰면 출력될까? -> 되긴한다.
그런데 plan 해보면

  • sal을 to_number해서 숫자로 암시적 형변환이 일어났다. ( 숫자가 문자보다 우선순위가 높으니까 ! )

문제 120. 우리반 테이블에서 이름과 이메일을 출력하는데, 이메일을 출력할 때 도메인만 나오게 출력!

궁금한 점

이거 왜 안될까 

 SELECT ename, birth,  age
  FROM emp17
  WHERE birth IN (to_date('1993', 'YYYY'), to_date('1994', 'YYYY'));
SELECT ename, birth,  age
  FROM emp17
  WHERE birth IN (to_date('93', 'RR'), to_date('94', 'RR'));

이거 아래는 됨

SELECT ename, birth,  age
  FROM emp17
  WHERE birth IN (to_date('93/04/05', 'RR/MM/DD'), to_date('97/06/30', 'RR/MM/DD'));

해결

위에 in 쓰면 안되는 이유는! 1993 만 등록되어있어야 데이터가 출력된다.
나는 1993 이라는 숫자가 포함되어있으면 뽑힌다고 생각했는데 그게 아니었음. in 쓰려면 완전 정확하게 써줘야해서 맨 아래 SQL은 나오는 것!
1993, 1994년도에 태어난 사람 출력을 원하면 between..and를 써야한다.

어제 이메일 문제 다시시도하기

오늘의 실수
1. where 절에서 길이가 5이상인 문자열 출력하려고 했는데 좌변에 LENGTH 안썼었다.
2. substr(email, instr(email,'@')+1) 여기서 처음에 INSTR 안쓰고 SUBSTR(email, '@' + 1) 이라고 썼음. 당연 안나옴 substr 저자리는 숫자로 입력해줘야해서 instr이 필요함

SELECT   ename, 
         substr(email, 1,instr(email,'@')-1),
         length(substr(email, 1,instr(email,'@')-1)),
         rtrim(substr(email, instr(email,'@')+1),'.com')
FROM     emp17
WHERE    length(substr(email, 1,instr(email,'@')-1)) >= 5
         and lower(rtrim(substr(email, instr(email,'@')+1),'.com')) like 'n%'
ORDER BY 3 asc ;

profile
권주희
열씨미하자
이전 포스트

230622 빠진 수업 내용 3 Oracle SQL [is null, IN, 논리연산자, 대소문자변환함수, 단일함수, 복수함수, substr, length, instr, replace, LPAD, RPAD, TRIM,

다음 포스트

230714 Oracle SQL 15 [제약 (CONSTRAINT), primary key, foreign key, not null, unique, check]

post-custom-banner

0개의 댓글