3. 단일행 함수

 

■함수 (단일행 함수)

*함수 (function) 가 필요한 이유?

-다양하게 데이터를 검색하고 분석하기 위해서 반드시 필요한 기능

예:

영화 겨울왕국에는 elsa 가 많이 나오나 anna 가 많이 나오나?

 

*함수의 종류 2가지

1. 단일행 함수: 문자, 숫자, 날짜, 변환, 일반

2. 복수행 함수: 최대, 최소, 평균, 토탈, 개수, 분산, 표준편차
 

 

 

데이터--------->정보를 추출

 함수를 잘 알아야 한다

 

예:

  내가 무슨 요일에 태어났는지?

  우리나라시장에서 가장 비싼 생필품은 무엇인지?

  우리나라에서 같은 이름이 가장 많은 이름이 무엇인지?

 

 

■문자함수

upper, lower, initcap, concat, substr, length, instr, ipad, rpad, trim, replace

 

■upper 함수

 "대문자로 출력하는 함수"

 

■lower 함수

 "소문자로 출력하는 함수"

 

 

문제57. 이름과 월급을 출력하는데 이름을 출력할 때

 소문자로 출력하시오

select lower(ename), sal from emp;

 

소문자로 특정 값 보고 싶을 때

select ename, sal from emp

where lower(ename)='scott';

 

 

문제58.  우리반테이블에서 네이버이메일을 사용하는

학생들의 이름과 이메일을 출력하시오

select ename, email from emp2

where lower(email)  like '%naver%';

 

 

 

 

■initcap 함수

"문자를 출력할 때 첫 번 째 철자는 대문자로 출력하고

나머지는 소문자로 출력하는 함수"

 

 

문제59. 이름을 출력하는데 이름의 첫번째 철자는

대문자로 출력하고 나머지는 소문자로 출력하시오

select initcap(ename) from emp;

 

 

■substr 함수

 "문자에서 특정 철자만 출력하는 함수"

예:

 select ename, substr(ename, 1,3)

   from emp;

첫번째를 뜻함

앞 숫자 순서부터 3개 갖고와라

 

king 의 (ename, 2,2) 면 in 이런식으로.

 

 

 

문제60. 이름의 첫번째 철자를 출력하는데

소문자로 출력하시오

 

select lower(substr(ename,1,1))

from emp;

 

 

문제61. (점심시간 문제)

아래의 SQL 의 결과를 initcap 쓰지말고

upper, lower, substr, || 를 이용해서 출력하시오

 

select initcap(ename) from emp;

 

문법: substr (지정값, 자리수, 개수)

 

우리조 답:

select upper(substr(ename,1,1)) ||

 lower(substr(ename,2))

as initcap from emp;

 

 

내 답:

 select upper(substr(ename,1,1))||

lower(substr(ename,2))

from emp;

 

다른조 답:

select upper(substr(ename,1,1))||

lower(substr(ename,2,99))

from emp;

 

 

■ concat

 "두 개의 컬럼의 데이터를 연결해서 출력하는 함수"

예:

  select concat(ename, age)

  from emp2;

 

 

화면 캡처: 2018-10-16 오후 1:46

 

 

 

■length

 "철자의 갯수를 세는 함수"

예:

  select ename, length(ename)

  from  emp;

 

 

 

문제62. 이름, 이메일, 이메일 철자의 갯수를

출력하는데

이메일 철자의 갯수가 많은 학생부터

출력하시오

select ename, email, length(email)

from emp2

order by length(email) desc;

 

 

■ instr

" 특정 철자의 위치번호를(인덱스) 출력하는 함수"

예:

  select ename, instr(ename, 'A')

  from emp;

 

문법: instr(지정값, '지정값의 철자 하나') = 지정 철자의 순번

   ex): instr (explanation,   x  )   = 2

  

 

 

문제63. 이메일에서 @ 이전 철자들만 출력하시오
 

select substr(email, 1, instr(email, '@')-1)

from emp2;

********문자자리 라서 -1 연산 안됨*********

 

 

 

문제64. 이메일에서 아래의 철자만 출력하시오

              naver

              gmail

              xerato

 

select substr(email, instr(email , '@')+1, instr(email, '.')-instr(email, '@')-1)

from emp2;

 

select

 substr(email, instr(email, '@')+1, (instr(email, '.')-1)-(instr(email, '@') ))

 from emp2;

*substr(email, 순서, 길이)

 

 

■ trim 함수

 "특정 철자나 공백문자를 잘라내고 출력하는 함수"

 

rtrim-> 오른쪽에 있는 특정 철자나 공백문자를

           잘라내는 함수

ltrim ->왼쪽에 있는 특정 철자나 공백문자를

           잘라내는 함수

trim->양쪽에 있는 특정 철자나 공백문자를

           잘라내는 함수

예:

   select ename, rtrim(ename, 'T')

      from emp;

 

 

*자료입력

(insert 앞에서 컨트롤엔터

commit; 에서 컨트롤엔터)

insert into emp(empno, ename, sal)

   values(2919, 'JACK     ', 4500);

commit;

select ename from emp;

 

 

문제65. 이름이 JACK 인 사원의 이름과 월급을

출력하시오

select ename, sal

from emp;

where ename='JACK'

 

답이 안나온다. 왜냐면 아까 JAKC 입력할 때

공백을 넣었기 때문이다.

인식시키려면

공백수만큼 숫자로 입력해줘야 한다.

 

이런 상황을 생각해서

select ename, sal

from emp;

where trim(ename) = 'JACK'

 

현업에서 거의 trim 을 이용함.

근데 이 방식으로 하면 엄청 느림.

빠르게 하기 위한 튜닝기법도 배워야 함.

 

※설명

trim(ename)은 ename 양쪽에 공백을 자르는 함수이다

위의 SQL은 검색속도를 느리게 하는 악성 SQL 이다.

그래서 튜닝이 필요하다. 튜닝 방법은 SQL 튜닝 수업 때

배운다.

 

 

■ replace 함수

 "특정 철자를 다른 철자로 변경하는 함수"

예:

  select ename, replace(ename, 'A', 'B')

   from  emp;

 

현업에서

고객 데이터 중에 주민등록번호를 암호화해서

저장해야 한다.

 

문제66. 이름과 월급을 출력하는데 숫자 0을

*표로 출력하시오

select ename, sal, replace(sal, '0', '*')

from emp;

***

where 는 특정지정값을 표현할 때 쓰는 것

select ename, replace(sal, '0', '*') as salary from emp;

 

 

 

 

문제67. 이름과 월급을 출력하는데 월급을 출력할 때에

숫자 0부터 3까지는 * 로 출력되게 하시오

select ename, sal, regexp_replace(sal, '[0-3]', '*')

 from emp;

※ 설명: regular expression (정규 표현식) 함수를

이용해서  출력하면 된다.

*정규 표현식은 나중에 배움.

 

Regexp_replace(지정값, '[범위]',  변환값)

 

 

 

■lpad, rpad 함수

자리수 채워주는 함수.

(Padding : 형태를 잡기 위해 넣는 속, 충전재)

 

 

 "lpad 함수는 문자나 숫자를 출력할 때

오른쪽에 원하는 철자를 출력하는 함수이고

 

rpad 함수는 문자나 숫자를 출력할 때

왼쪽에 원하는 철자를 출력하는 함수이다"

 

예:

  select ename, lpad(sal, 10, '*'), rpad(sal, 10, '*')

   from emp;

뜻:

 lpad(sal, 10, '*') : 연봉을 10자리로 뽑는데,

                       남는 왼쪽을 * 로 채워라.

 

 

■ 숫자함수(142페이지)

round, trunc, mod

 

■ round 함수

 " 반올림하는 함수"

select round (758.567, 2)

 from dual;

dual 은 결과값만 보기 위한 가상의 테이블

 

 

7   5   8   .   5   6   7

-3 -2  -1  0  1    2   3

 

만약

round(758.567, 0) 으로 하면

점 바로 다음 숫자에서 반올림 한다.

또는

round(758.567) 아무것도 안 쓴것과 그 값이 같다.

 

round(758.567, -1) 은 좀 다른데

7 5 8 . 5 6 7

8에서 반올림 받아서 759 라고 생각하나

음수는

한 칸 더 가서 반올림 해준다. 760

 

 

■ trunc 함수

 " 값을 버리는 함수"

select trunc(758.567, -1)

 from dual;

7   5   8   .   5   6   7

-3 -2  -1  0   1   2  3  ------------>답 750

 

(758.567, -2)  의 답: 700

 

 

■ mod 함수

 "나눈 나머지 값을 출력하는 함수"

select mod(10, 3)

 from dual;

 

ex) 10/3 했을 때 몫3, 나머지 1

   그 나머지 1값을 출력하는 함수.

 

 

문제68. 이름, 나이, 나이가 짝수이면 0을 출력하고

                         나이가 홀수이면 1을 출력하시오

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

 

 

 

 

문제69. 위의 SQL 에 replace 함수를 써서 아래와 같이

결과를 출력하시오(난이도 상)

 

select ename, age,

     replace(replace(mod(age,2),'0','짝수'),'1','홀수')

from emp2;

 

select replace(replace(mod(age,2), '0', '짝수'), '1', '홀수')