Database D+2

database D+2

 

## SQL 2일차 database

 

FROM -> WHERE -> GRUOP BY -> HAVING

-> SELECT -> ORDER BY



자료형



VAR CHAR2 문자열 데이터

NUMBER 숫자

DATE 날짜, 세기, 연, 월, 일, 시, 분, 초 까지 저장 가능


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


단일행 함수

SELECT 함수이름

FROM dual, 테이블명

LOWER : 소문자

UPPER : 대문자

INITCAP : 단어의 첫자만 대문자

CONCAT : 

SUBSTR : 

LENGTH

INSTR

LPAD

RPAD

TRIM


SELECT EMAIL, LOWER(EMAIL), INITCAP(EMAIL)

FROM EMP;


SELECT * LOWER(EMAIL)

FROM EMP

WHERE EMAIL = 'sking' ;



SELECT * 

FROM EMP

WHERE EMAIL = upper('sking') ;


SELECT *

FROM EMP

WHERE lower(EMAIL) = 'sking' ;


=> 위 세가지 중에 어느것이 성능이 좋을까?

=> 검색 조건의 컬럼에 인덱스가 있는 경우 함수를 쓰면

변형되므로 인덱스를 사용하지 않고 fullscan 속도가 떨어진다.

substr ( 컬럼명, 시작위치, [문자갯수] )

=> ****

=> 시작위치 > 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 리턴
=> 여러개 있으면 첫번째 위치가 반환
 

instr (컬럼명, '찾고자 하는 문자 또는 문자열')

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

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

ex) 
11212-23232-dfdf
3232-2323-fdf
323-45454-sdsds 

/*dfdf 와 fdf 와 sdsds 를 찾고 싶은데
어떻게 해야하나?  조합으로 생각해서 사용해한다

*/
11212-23232
3232-2323
323-45454
=> code, item

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

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


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 ;

-1한 경우는 하이퍼 앞에까지 
+1한 경우는 하이퍼 뒤부터라서 해준다

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

select email, length(email)
from emp ;

select email, length(email), length('aa77안동대')
from emp ;

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

select email, lpad(email, 15, '*')
from emp ;

select email, lpad(email, 15, ' ' )
from emp ;

예제)
SKING을 SKI**로 바꾸자

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


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

select email, replace(email, 'S', '***' )
from emp;


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

select trim ( '     kdfjkes sdassdka  sdkasoda  ' )
from dual;   ///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 ;

sysdate는 현재 시스템상의 날짜이다.,

날짜 빼기 날짜는 두 날짜를 일자로 바꿔서 연산한다.. 외워두기

**현재 사회에선 달이라는 개념이 중요 

##add_months (날짜 컬럼, 개월수)

select hire_date, hire_date + 10 ,
 add_months ( hire_date ,10)
from emp ;


select hire_date, hire_date + 10 ,
 add_months ( hire_date ,3), add_months(hire_date, -30)
from emp ;

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

사원의 근속월수가 계산

select last_name, hire_date,
months_between ( sysdate, hire_date) as 근속월수
from emp;

sysdate로 인해 시스템상의 현재 날짜와 계산하기 때문에 소수점이 지저분하게  결과값이 나온다

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;
/* 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;



select hire_date, round(hire_date , 'year')
from emp;


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

일반 함수

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

총급여 = salary + (salary * nvl (commission_pct, 0  ) ) 

select last_name,
salary + (salary * nvl (commission_pct, 0 ) ) as 총급여
from emp;

널을 포함할수도 있는 컬럼은 nvl를 적용해줘야 된다

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

select last_name, nvl2( commission_pct, '수당받음', '수당안받음')
from emp ;

널이 아닐경우와 널일 경우 같이 표현할때 좋을듯 하다
예를 들어 수당 받고 안받고 
, 
case 문 
- 쿼리문 안의 조건문
ex)
월급의 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 department_id
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,
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 ;
-> 24체계로 표시
select sysdate, to_char (sysdate, ' mon dd, yyyy 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;
-> 앞에 L을 붙이면 지역

select commission_pct, to_char (commission_pct, '0.00') from emp;
-> 뒤에 소수점 자리를 맞춤으로써 깔끔하게 보인다


select * 
from emp
where to_char( hire_date, 'mm' ) = '06' ;