데이터베이스 2일차_sql 검색, 단일행함수(oracle, sql)

## 컬럼 설명

EMPLOYEE_ID  -> 사번
FIRST_NAME
LAST_NAME    -> 이름
EMAIL
PHONE_NUMBER
HIRE_DATE   -> 입사일
JOB_ID        -> 직군, 직위
SALARY       -> 월급, 급여
COMMISSION_PCT -> 판매수당율
MANAGER_ID        -> 직속상관 사번
DEPARTMENT_ID   -> 부서번호

===============================================

SQL 실행순서 *****

from -> where -> group by -> having -> select -> order by 


---------------------------------------------------------------------------------

## 문자함수


upper : 대문자
lower : 소문자
initcap : 첫자만 대문자 : 단어의 첫자가 대문자

SELECT EMAIL, LOWER(EMAIL), INITCAP(EMAIL )
FROM  EMP ;

SELECT *
FROM EMP
WHERE EMAIL = 'sking' ; 


SELECT *
FROM EMP
WHERE upper(EMAIL)= upper('sking') ; 

SELECT *
FROM EMP
WHERE EMAIL = upper('sking') ; 

SELECT *
FROM EMP
WHERE lower(EMAIL) =  'sking'  ; 

=> 위 세가지 중에 어느것이 성능이 좋을까?
=> 검색조건의 컬럼에 인덱스가 있는경우 함수를 쓰면 변형되므로
      인덱스를 사용하지 않고 fullscan 속도가 떨어진다!

substr ( 컬럼명, 시작위치, [문자갯수] )
=> *****
=> 시작위치 > 0 : 왼쪽부터 시작
=> 시작위치 < 0 : 오른쪽부터 시작

select email, substr( email, 1, 3), substr(email, -2 )
from  emp ;

901010-1234567 : 주민번호 =>사원테이블

남자사원만 검색 

select *
from  사원
where substr( 주민번호, -7, 1)  in ( '1' , '3' ) ;

instr( 컬럼명, '찾고자 하는 문자 또는 문자열' )
instr( 컬럼명, '찾고자 하는 문자 또는 문자열', [ 시작위치, 몇번째것] )
=> 찾는 문자의 시작위치
=> 없으면 0 리턴
=> 여러개 있으면 첫번째 위치가 반환

select email, instr( email, 'S' )
from  emp ;

select email, instr( email, 'S' , 1, 2)
from  emp ;

010-2101-6751
02-2345-9876
010-234-2333

11212-23232
3232-23233344
323-45454
=>code , item

code컬럼에 '-'  앞 문자열만
code컬럼에 '-'  뒷 문자열만

select code,
         ? as '앞문자열',
         ? as '뒷문자열'
from  item ;

job_id 컬럼에서 '_' 앞 뒤 문자열 검색

select job_id,
         substr( job_id, 1, instr( job_id, '_' ) -1   ) as 앞,
         substr( job_id,  instr( job_id, '_' ) +1  ) as 뒤
from  emp ;

length(컬럼명)
- 해당 컬럼의 문자의 길이
- 영, 숫자, 특수문자, 공백, 한글 => 한자로 인식 

select email, length(email )
from  emp ;

LPAD( 컬럼명, 전체사이즈, '채울문자')

SELECT EMAIL, LPAD( EMAIL, 15, '*')
FROM  EMP;

SKING -> SKI**
NKOCHHAR -> NKO*****

SELECT EMAIL, RPAD( SUBSTR(EMAIL, 1, 3) , LENGTH(EMAIL), '*')
FROM  EMP;
           
REPLACE( 컬럼명, '찾는문자열',' 바꿀문자열')

SELECT EMAIL, REPLACE(EMAIL, 'S', '***' )
FROM EMP;

TRIM( 컬럼명 )
- 컬럼의 앞뒤 공백을 잘라줌
- 문자 중간의 공백은 유지됨

SELECT TRIM ( '        KDFJKDF    DKJFKDFJDK    KDJFKDJF        ')
FROM  DUAL ; 


==============================================

숫자 함수

ROUND ( 컬럼명, 자릿수 )
- 반올림
- 자릿수 
  =>  = 0 : 정수만 표시
  =>  > 0 : 소숫점 이하 자릿수
  =>  < 0 : 정수자릿수


TRUNC ( 컬럼명, 자릿수 )
- 절사
  =>  = 0 : 정수만 표시
  =>  > 0 : 소숫점 이하 자릿수
  =>  < 0 : 정수자릿수

SELECT COMMISSION_PCT , ROUND( COMMISSION_PCT, 1),
           TRUNC ( COMMISSION_PCT, 1)
FROM  EMP
WHERE COMMISSION_PCT IS NOT NULL ;

SELECT SALARY, ROUND( SALARY , -3) FROM EMP ;


==============================================

날짜 함수
- 날짜 타입의 자료는 연산가능함!!
  => 날짜 - 날짜, 날짜 +  숫자
- 날짜 타입 : 세기, 년, 월, 일, 시, 분, 초 => 7가지 정보

- 날짜 +  숫자 => 일자에 연산이 가능 
- +, - 연산가능, *, / 연산 안됨

SELECT HIRE_DATE, HIRE_DATE + 10
FROM  EMP ; 

SELECT SYSDATE - HIRE_DATE 
FROM  EMP ;

ADD_MONTHS( 날짜컬럼, 개월수 )
ADD_MONTHS( 날짜컬럼, -개월수 )

SELECT HIRE_DATE, HIRE_DATE + 10 , 
       ADD_MONTHS( HIRE_DATE, 3),  ADD_MONTHS( HIRE_DATE, -3 )
FROM  EMP ; 

MONTHS_BETWEEN ( 최근날짜, 오래된날짜) 
- 두 날짜 사이의 개월수 

사원의 근속월수가 계산

SELECT LAST_NAME, HIRE_DATE,
          TRUNC ( MONTHS_BETWEEN ( SYSDATE, HIRE_DATE ) , 0 ) AS 근속월수
FROM EMP ; 

EXTRACT ( YEAR FROM 날짜컬럼 )
=> YEAR , MONTH, DAY,.....
=> 날짜 컬럼에 일부 정보만 추출

SELECT LAST_NAME, HIRE_DATE
FROM  EMP
WHERE EXTRACT ( YEAR FROM HIRE_DATE) = 2004 ; 

LAST_DAY ( 날짜컬럼 ) 
=> 마지막 날 

SELECT HIRE_DATE, LAST_DAY(HIRE_DATE)
FROM  EMP ;

NEXT_DAY ( 날짜컬럼, 구분)
=> 구분 : 요일 -> '월요일','월',  
=> 요일 : 숫자 : 일 월 화 수 목 금 토 => 1 2 3 4 5 6 7
=> 날짜컬럼 이후 요일의 날짜 

SELECT HIRE_DATE, NEXT_DAY(HIRE_DATE, 7)
FROM  EMP ;

ROUND, TRUNC 사용가능 

SELECT HIRE_DATE, ROUND(HIRE_DATE,  MONTH)
FROM  EMP ;

===================================================

일반 함수

NVL ( 컬럼명, NULL 일 경우 대신 반환할 값 )  => *****

총급여 = SALARY + ( SALARY * NVL ( COMMISSION_PCT , 0 ) ) 

SELECT LAST_NAME, 
           SALARY + ( SALARY * NVL ( COMMISSION_PCT , 0 ) )  AS 총급여
FROM  EMP ;


NVL2( 컬럼명, 널이 아닐경우, 널일 경우  )

SELECT LAST_NAME, NVL2( COMMISSION_PCT  , '수당받음', '수당안받음') 
FROM EMP ;

CASE  문
- 쿼리문 안의 조건문 

월급의 2배 보너스를 지급코자 한다.

SELECT LAST_NAME, SALARY, SALARY * 2 AS BONUS
FROM  EMP ;

부서별 보너스를 다르게 지급코자 한다.
- 20 -> 2배,  30 -> 3배,  40-> 4배
- 나머지 부서 월급 1배 

SELECT LAST_NAME, SALARY, DEPARTMENT_ID, 
          CASE  DEPARTMENT_ID  
                 WHEN 20   THEN SALARY * 2
                 WHEN 30   THEN SALARY * 3                 
                 WHEN 40   THEN SALARY * 4
           ELSE
                 SALARY * 1
           END    AS BONUS
FROM  EMP ;


SELECT LAST_NAME, SALARY, DEPARTMENT_ID, 
          CASE    
                 WHEN DEPARTMENT_ID= 20   THEN SALARY * 2
                 WHEN DEPARTMENT_ID= 30   THEN SALARY * 3                 
                 WHEN DEPARTMENT_ID= 40   THEN SALARY * 4
           ELSE
                 SALARY * 1
           END    AS BONUS
FROM  EMP ;


SELECT LAST_NAME, SALARY, DEPARTMENT_ID, 
          CASE    
                 WHEN JOB_ID LIKE '%CLERK'         THEN SALARY * 2
                 WHEN JOB_ID LIKE '%ACCOUNT'    THEN SALARY * 3                 
                 WHEN JOB_ID LIKE '%MAN'          THEN SALARY * 4
           ELSE
                 SALARY * 1
           END    AS BONUS
FROM  EMP ;

CASE => DECODE() 함수

SELECT LAST_NAME, SALARY, DEPARTMENT_ID, 
          CASE  DEPARTMENT_ID  
                 WHEN 20   THEN SALARY * 2
                 WHEN 30   THEN SALARY * 3                 
                 WHEN 40   THEN SALARY * 4
           ELSE
                 SALARY * 1
           END    AS BONUS
FROM  EMP ;


SELECT LAST_NAME, SALARY, DEPARTMENT_ID, 
          DECODE(  DEPARTMENT_ID      
             ,20   , SALARY * 2  
                             ,30   , SALARY * 3                
                             ,40   , SALARY * 4,   SALARY * 1 )
        AS BONUS
FROM  EMP ;

====================================================

형전환 함수
=> TO_ DATE()
=> TO_ NUMBER()
=> TO_CHAR()

TO_CHAR()
=> 숫자 또는 날짜를 표시형식 바꿔서 출력할 때 

날짜 => 문자 

SELECT SYSDATE, TO_CHAR( SYSDATE, ' YY-MM-DD HH:MI:SS' )  FROM DUAL ; 

SELECT SYSDATE, TO_CHAR( SYSDATE, ' YY-MM-DD AM HH:MI:SS' )  FROM DUAL ; 

SELECT SYSDATE, TO_CHAR( SYSDATE, ' YY-MM-DD DY HH24:MI:SS' )  FROM DUAL ; 

SELECT SYSDATE, TO_CHAR( SYSDATE, ' YY-MON-DD HH:MI:SS' )  FROM DUAL ; 


숫자 => 문자
- 천단위 콤마, 화폐단위 .... 

SELECT SALARY, TO_CHAR( SALARY, '$99,999) FROM EMP ;
SELECT SALARY, TO_CHAR( SALARY, '$009,999) FROM EMP ;
SELECT SALARY, TO_CHAR( SALARY, 'L99,999) FROM EMP ;

SELECT COMMISSION_PCT , TO_CHAR ( COMMISSION_PCT , '0.00' )  FROM EMP ;

SELECT *
FROM EMP
WHERE TO_CHAR( HIRE_DATE, 'MM' ) = '06' ;