[SQL] 집합 연산자 & 그룹 함수

 

 

1. 집합 연산자.

:집합 연산자는 합집합, 교집합, 차집합을 의미한다. 

 

 

<합집합>

-동일한 컬럼을 기준으로 행을 합치는 것. (컬럼을 합치는 것이 아님 )

*UNION : 합집합, 검색된 결과의 합으로 중복은 제거.

*UNION ALL : 합집합, 검색된 결과의 합으로 중복을 포함.

 

<교집합>

INTERSECT :교집합, 양쪽 모두에서 포함된 행을 검색한다. 

 

<차집합>

MINUS : 차집합, 첫번째 검색된 결과에서 두번쨰 검색결과를 제외한 나머지를 검색한다. 

 

SELECT FROM  WHERE [UNION ALL | UNION | INTERSECT | MINUS] SELECT FROM WHERE

 

 

두 SELECT 문의 결과를 집합 연산한다. 

검색 결과의 헤더는 앞쪽 SELECT 문에 의해 결정된다. 

두 SELECT 문의 컬럼 개수와 데이터 타입이 일치해야한다. 

ALL : 중복을 포함한다. 

 

 

2. 단일 행 함수와 그룹함수

:단일 행 함수는 입력 값의 형태나 출력 값의 형태에 따라 구별되며 그 구분은 아래와 같다. 

 

-문자함수

-숫자함수

-날짜함수

-변환함수

-일반함수

 

단일 행 함수를 이용하면 검색된 데이터에 대해서 각 행별로 연산된 결과 값을 얻을 수 있다. 

함수의 결과 값은 입력 값마다 하나씩 리턴된다. 

인수로는 컬럼 명, 수식, 변수, 상수 등을 사용할 수 있다. 

단일 행 함수는 SELECT 절이나 WHERE 절 또는 HAVING 절 등 값이 필요한 거의 모든 구문에 사용이 가능하다. 

 

 

<문자함수>

1. 대소문자 변환 함수

LOWER : 문자열을 소문자로 변환한다.

UPPER : 문자열을 대문자로 변환한다. 

INITCAP : 첫문자만 대문자로 변환하고 나머지는 소문자로 변환한다. 

 

2.문자 연산 함수

CONCAT : 두 개의 문자열을 연결한다.

  CONCAT (문자열1, 문자열2) 

 

3. SUBSTR (자주사용함!!)

: 문자열 내에 지정된 위치의 문자열을 반환한다. 

SUBSTR(문자열, [시작위치], 출력문자의 개수)

     EX) SUBSTR('ORACLE' 1,2) => OR
     자바와 달리 시작 위치가 1부터 시작한다. 

 

4.LENGTH

 

LENGTH (B): 문자열의 길이나 BYTE 를 반환한다.

LENGTH(문자열), LENGTHB(문자열)
    LENGTH('오라클') => 3
    LENGTHB('오라클') => 6
 

 

 

5. INSTR

:지정된 문자의 위치를 리턴한다

 

INSTR(문자열, 검색문자,[시작위치, [횟수]])

   INSTR('ORACLE','A') =>3

 

6.TRIM

TRIM(  [LEADING | TRAILING |BOTH] 제외문자 FROM 문자열 )

    EX) TRIM('O' FROM 'ORACLE') => RACLE

-제외 문자를 지정하지 않으면 공백 문자를 잘라낸다. 거의 이 용도로 씀

 

 

7.LPAD, RPAD

:지정된 문자열의 길이만큼 빈 부분에 문자를 채운다. 

 

LPAD (문자열, 출력폭, 채움문자)          --좌측부터 채움

   EX)  LPAD('20000' , 10 ,'*') => *****20000

RPAD (문자열, 출력폭, 채움문자)          --우측부터 채움


   EX)  RPAD('20000' , 10 ,'*') => 20000*****

 

 

8.TRANSLATE

:문자단위 치환된 값을 리턴한다. 

 

 

TRANSLATE(문자열, 검색문자, 치환문자)

   EX)  TRANSLATE('ORACLE','A','#') =>OR#CLE

 

 

9.REPLACE

:문자열 단위 치환된 값을 리턴한다.

REPLACE (문자열, 검색 문자열, 치환 문자열)
REPLACE (ORACLE','OR','##')=> ##ACLE

 

<숫자함수>

:숫자 함수는 숫자를 인자로 사용하고 결과값도 숫자를 반환하는 함수이다.

 

1.ROUND 

:N자리까지 반올림한다. 

ROUND(M,N)

EX)
  ROUNT(123.4567,3) =>123.457

2.TRUNC 

: N 자리 미만을 절삭한다. 

TRUNC(M, N)
 
EX)
   TRUNC (123.4567,3) =>123.456

 

3.MOD 

:M을 N으로 나눈 나머지 계산한다. 

MOD(M, N)

  EX
     MOD(10 , 4)  => 2

 

4. POWER

:M의 N승을 계산한다. 

POWER(M , N)
  
   EX
     POWER(2, 4) => 16

 

5. CEIL

:M보다 큰 가장 작은 정수를 찾는다. 

인수 값이 정수이면 입력한 정수값 반환

소수이면 소수보다 큰 정수값 반환

CEIL(M)
 
   EX)
    CEIL(2.34) =>3
    CEIL(2) =>2

 

 

6.FLOOR

:M보다 작은 가장 큰 수를 찾는다. 

FLOOR(M)

   EX
   FLOOR(2.34) => 2
   FLOOR(2)=>2

 

 

7.ABS

:M의 절대 값을 계산한다. 

 

ABS(M)

   ABS(-4)=>4

 

8.SQRT

:M의 제곱근을 계산한다. (루트)

SQRT(M) =>POWER (M,1/2)

    SQRT(9)=>3

9.SIGN

:M이 음수일때 -1, 양수일때 1, 0이면 0을 반환한다. 

SIGN(M)

 SIGN(-3)=>-1

 

 

<날짜 함수와 날짜 연산의 이해>

 

SYSDATE : 오늘 날짜

ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD:HH24:MI:SS'; //날짜 포맷 변경 
SELECT SYSDATE FROM DUAL;//오늘 날짜 확인

-날짜+ 숫자 : 날짜 반환 

-날짜-숫자 : 날짜 반환

-날짜+N/24 : 날짜

       > 24시간중 N시간 

-날짜-날짜: 숫자 반환

 

<날짜 함수>

1.MONTHS_BETWEEN

:두 날짜 간의 기간을 월 수로 계산

MONTHS_BETWEEN(날짜1, 날짜2)
날짜1> 날짜2 인 경우는 결과는 양수이다. 
MONTHS_BETWEEN('2013/09/01', '2013/01/01/') =>8

 

 

2.ADD_MONTHS

:날짜에 N 달을 더한 날짜를 계산한다. 

'-날짜' 하면 뒤의 날짜도 구할 수 있다. 

ADD_MONTHS (날짜, N)


   ADD_MONTHS('2011/07/01',23) => 2013/06/01

 

3.NEXT_DAY

:날짜 이후 지정된 요일에 해당하는 날자를 계산한다. 

NEXT_DAY(날짜, 요일)
     

       NEXT_DAY('2013/07/14', '일요일') => '2013/07/21'

4.LAST_DAY

:날짜를 포함한 달의 마지막 날짜를 계산한다. 

 

LAST_DAY(날짜)


  LAST_DAY('2013/09/24') =>'2013/09/30'