[days04] SQL Operation, Function 예제

<1.  Operation>

 

1. tbl_like 테이블에서 subject 칼럼 값이 100%라는 문자열을 포함하는 레코드를 조회

select * 
from tbl_like 
where subject like '%100\%%' escape '\';  --1  %를 출력하고 싶으면 escape를 준다

where regexp_like(subject, '100%');      --2

 

오라클 집합연산자
-- union 합집합
-- union all 합집합
-- minus 차집합
-- intersect 교집합

 

1. 사는 지역이 인천이면서 부서가 개발부인 사원 조회

select * 
from insa  
where city = '인천' 
intersect --교집합 
select * 
from insa  
where buseo ='개발부';

2.  출신 지역이 인천 또는 부서가 개발부인 사원 조회

select * 
from insa  
where city = '인천' 
union --합집합 union all : 중복 허용(개발부+인천 중복6명 포함) 
select * 
from insa  
where buseo ='개발부';

3. 인천출신이지만 개발부가 아닌 사원 조회

select * 
from insa  
where city = '인천' 
minus -- 차집합
select * 
from insa  
where buseo ='개발부';

 

오라클 연결연산자 

 

1. 홑따옴표를 출력하려면 ? 

select ''''||ename||'''남은 부서가 "'||deptno||'"입니다.'msg  
from emp;

--이처럼 홑따옴표를 2개 준다.

2. 문자열을 연결하는 방법

- empno와 ename을 연결하고자 한다.

   1) empno||ename

   2) concat(empno, ename)

---

<2.  Function>

 

 

1. roung(a,[b]) 반올림함수

반올림함수는 첫번째 인자값을 두번째 인자값자리에서 반올림 한다.

-2 : 자연수 둘째자리 반올림

-1 : 자연수 첫째자리 반올림

0 : 소수점 첫째자리 반올림

1 : 소수점 둘째자리 반올림

2 : 소수점 셋째자리 반올림

select round(15.193) --15 
        ,round(15.193, 0)--15 두번째 인자값을 안주면 그냥 0이다 
        ,round(15.593, 0)--16 첫째자리에서 반올림

        ,round(15.193, -1)--20 일의자리에서 반올림
        ,round(98.12645, 2)--98.13 소수점 2자리까지 출력 %.2f (2+1)자리에서 반올림 일어남 
        ,round(98.12645, -1)--100 음수면 소수점을 기준으로 왼쪽자리에서 반올림 
        ,round(98.12645, -2)--100 십의자리에서 반올림 
from dual;

 

 

2. trunc(a,[b]) 절삭하는 함수

select 15.23456 
        ,trunc(15.23456) --15 
        ,trunc(15.27456, 1) --15.2 
        ,trunc(15.23456, -1) --10 일의자리에서 절삭 
        ,trunc(15.23456, -2) --0 십의자리에서 절삭 
from dual;

 

3. ceil() 절상하는 함수 

지정한 숫자보다 크거나 같은 정수 중에서 최소 값을 출력하는 함수

* 나중에 게시판 만들 때 총 게시글이 몇개있고 한 페이지에 몇개 뿌릴지 결정할떄 ceil함수 쓴다.

select ceil(12.345) --13 
        ,ceil(12.768) --13 
from dual;

절상 응용

4. floor(n) 절하하는 함수

trunc()와의 차이점 : floor(n) trunc(n, [m]) 
-- trunc쓰는게 좋다 매개변수만 주면 되니까 (그거빼면 다같음)

select floor(12.345) --12 
        ,floor(12.789) --12 
from dual;

 

5. .sign()함수 **** 

양수(1) 음수(-1) 0(0)돌리는 함수 

select sign(10), sign(-10), sign(0) -- 1 -1 0
from dual;

 

6.  power(2,3) 2의3승

select power(2,3), power(2,-3) --8, 0.125 
from dual;

 

7. sqrt()

제곱근값

select sqrt(2), sqrt(4) --루트2, 2 
from dual;

 

8. upper()/lower()/initcap() 

대문자로 바꿔주는 / 소문자로 바꿔주는 / 첫자를 대문자로 바꿔주는 함수

select upper('daYeoN'), lower('daYeoN'), initcap('daYeoN') 
from dual;

--DAYEON   dayeon Dayeon 

 

9. length(), lengthb()

문자열 길이/바이트 수

select ename 
        ,length(ename) 
        ,length('홍길동') --3 
        ,lengthb('홍길동') --9, lengthb()는 바이트 수 
from emp;

10. substr()

select ssn 
        ,substr(ssn,8) 8번째부터 끝까지
        ,substr(ssn, -7) --뒤에서 7째자리부터 가져오겠다

from insa;

substr함수는 다른 함수와 같이 사용하는 경우도 종종 있다. 다음은 length 함수를 사용한 예시이다.

select job
        ,substr(job, -length(job))
        ,substr(job, -length(job),2)
        ,substr(job, -3)
from emp;

 

11. instr()

Java의 indexOf() 

INSTR( 대상 문자열 데이터(필수),

           위치를 찾으려는 부분 문자(필수),

           위치 찾기를 시작할 대상 문자열 데이터 위치(선택, 기본값은 1),

            시작 위치에서 찾으려는 문자가 몇번째인지 지정(선택, 기본값은 1) )

select ename, instr(ename, 'M') --없으면 0 
        ,instr(ename, 'NE') --대소문자 구분 
from emp;

select INSTR('HELLO, ORACLE', 'L') l1
        ,INSTR('HELLO, ORACLE', 'L', 5) l2
        ,INSTR('HELLO, ORACLE', 'L', 2, 2) l3
from dual;

INSTR함수를 LIKE와 비슷한 용도로 사용할 수도 있다. (특정 문자를 포함하고 있는 행 찾기)

WHERE INSTR(ENAME, 'S') > 0;

WHERE ENAME LIKE '%s%';

--같은 의미

입력값

출력값

 

12. rpad( 출력물 , 자리확보 , 남은자리 뭐로 채울지 ) /lpad(  ,  ,  )

오른쪽, 왼쪽 자리 확보해 특정 문자(숫자,기호 등)로 채워라

만약 빈 공간에 채울 문자를 지정하지 않으면 그 자릿수만큼 공백 문자로 띄운다.

select ssn 
        ,substr(ssn, 1, 6)||'-*******' 
        ,rpad(substr(ssn, 1, 7), 14, '*') 
        ,rpad(concat(substr(ssn, 1, 6),'-'), 14, '*')--6자리만 가져오면 이렇게  
from insa;

셋 다 같은 의미, 980730-2****** 출력

입력값

출력값

문제) *****이름***** 으로 출력하라

select  
   rpad( lpad ( ename, length( ename ) + 5, '*' ), length( ename ) + 10, '*' ) 
from emp;

 

13. trim(), pad()

특정 문자(공백, 숫자 등 모든 문자) 제거 : rtrim() ltrim() trim()

특정 문자로 채우는 역할 : rpad(), lpad()

select 'hhhtesthhh'

        ,rtrim( 'hhhtesthhh', 'h' ) --우측 h제거
        ,ltrim( 'hhhtesthhh', 'h' ) --좌측 h제거 

        ,TRIM( '*' from '***test***' ) --양쪽 별 제거, 문법 조금 다르다 
from dual;

select '0012' 
        ,ltrim('0012', '0')--12 
        ,'12' 
        ,lpad('12',4,'0') --0012 4자리 확보하고 왼쪽부터 빈자리는 0으로 채우자 pad 
        ,rpad('12',4,'0') 
from dual;

14. ascii(), chr()

아스키코드값<->문자 돌려주는 함수

select substr(ename, 0, 1) 
        ,ascii(substr(ename, 0, 1)) -- 아스키코드 값 돌려줌 
        ,chr(65) --A  
from emp;

 

15. greatest(), least() 

최솟값, 최댓값 돌려주는 함수

select greatest(20,30,10,50,100) -- 100 최댓값 
        ,least(20,30,10,50,100) --10 최솟값 
        ,GREATEST('KOREA','COREA','SEOUL') --SEOUL 
from dual;

 

16. vsize(char)

바이트 길이 돌려주는 함수 == lengthb()

select name, vsize(name) --한글3글자:9 =>바이트 길이구나 
        ,length(name)--3 
        ,lengthb(name)--9  vsize(name)와 같은 의미 
from insa;

 

17. 날짜함수

입력값

출력값

입력값

출력값

입력값

출력값