##SQL 2일차 0428

EMPLOYEE_ID -> 사번

FIRST_NAME

LAST_NAME -> 이름

EMAIL

PHONE_NUMBER

HIRE_DATE -> 입사일

JOB_ID -> 직군, 직위

SALARY -> 월급, 급여 

COMMISSION_PCT -> 판매수당률

MANAGER_ID -> 직속상관 사번

DEPARTMENT_ID -> 부서번호 

 

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

*******SQL 실행순서 *******

 

 FROM -> WHERE -> GRUOP 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 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 리턴

=> 여러개 있으면 첫번째 위치가 반환

ex) 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;

 

select email, length(email), length('안동대')

from emp;

 

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

 

select email, lpad(email, 15, '*')

from emp;

 

예제 1

SKING -> SKI**

NKOCHHAR -> NKO*****

 

예제 1 답

select email, rpad(substr(email,1,3) , length(email), '*')

from emp;

 

replace(컬럼명, '찾는문자열', '바꿀문자열')

 

select email, replace(email, 'S', '***')

from emp;

 

TRIM(컬럼명)

-컬럼의 앞 뒤 공백을 잘라줌

-문자 중간의 공백은 유지가 됨

 

select trim('        d s a sssss df s sfa      ')

from dual ; 

 

*dual = 임시 테이블 ( 테이블 없을 때)

       

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

숫자 함수

 

ROUND(컬럼명, 자릿수)

-반올림

-자릿수

 1  2  3  .  4  5  6  7

      -1  0  1

=> =0 : 정수만 표시

=> >0 : 소수점 이하 자릿수

=> <0 : 정수 자리수

TRUNC(컬럼명, 자릿수)

-절사

=> =0 : 정수만 표시

=> >0 : 소수점 이하 자릿수

=> <0 : 정수 자리수

 

SELECT COMMISSION_PCT, ROUND(COMMISSION_PCT,1),

           TRUNC(COMMISSIOM_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( 날짜컬럼, 개월수)

 

SELECT HIRE_DATE, HIRE_DATE + 10 , ADD_MONTHS(HIRE_DATE, 10)

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(날짜컬럼)

=>마지막 날을 알려줌 EX) 03/06/17 => 03/06/30

 

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))AS총급여

 

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

FROM EMP;

 

 

NVL2(컬럼명, NULL아닐 때, NULL일 때)

 

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 ;                **AM, PM 상관 없음

 

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, '$0099,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' ;