데이터베이스 20180816 (학원2)

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

2018-08-16

@ LOWER / UPPER / INITCAP

LOWER : 소문자로 변환

UPPER : 대문자로 변환

INITCAP : 각 단어 첫글자만 대문자

@ CONCAT

 -> 컬럼의 문자 혹은 문자열을 두개 전달 받아서 하나로 합친후에 리턴

리턴

@REPLACE

 -> 문자열 변경

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

EMPLOYEE 테이블에서 메일주소 있을텐데 kh.or.kr -> iei.or.kr

출력은 이름이랑 이메일만 하기

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

숫자처리

@ABS

 -> 절대값 리턴

@MOD

 -> % 나머지값 리턴

@ROUND

 -> 지정한 위치 이후의 값을 반올림

@FLOOR

 -> 소수점 자리수를 버림

@TRUNC

 -> 지정한 자리까지 표시하고 그 뒤를 버림

 -> 지정한 위치 이후 자리의 수를 버림

@CEIL

 -> 숫자를 올림계산하여 나온수

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

날짜처리 함수

@SYSDATE

 -> 시스템에 저장되어있는 현재 날짜를 반환

 CURRENT_DATE, LOCALTIMESTAMP, CURRENT_TIMESTAMP

@ MONTHS_BETWEEN

 -> 날짜 두개를 전달받아서, 개월수 차이를 숫자형으로 반환

@ADD_MONTHS

 -> 전달받은 날짜에 전달받은 숫자만큼 개월수를 더해서 특정날짜를 리턴

@ NEXT_DAY

 -> 인자로 전달받은 날짜에 인자로 전달받은 요일중 가장 가까운 다음 요일을 반환

@ LAST_DAY

 -> 인자로 전달받은 날짜가 속한 달의 마지막 날짜를 반환

 -> 8월 31일 -> LAST_DAY(8/31) 넣으면 9월 30일이 온다.

@ EXTRACT

 -> 년, 월, 일 정보를 추출

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

1. EMPLOYEE테이블에서 사원의 이름, 입사일, 년차를 출력

단, 입사일은 YYYY년,M월,D일로 출력

년차출력은 소숫점인경우 올림(28.11 -> 29년차)

출력시 정렬은 입사일기준 오름차순

2.특별보너스를 지급하기 위한 자료가 필요

기준달은 입사 다음달의 6개월 후 시점

-> 입사일을 기점으로 다음달 1일부터 6개월을 계산하여 출력

(이름,입사일,기준일,기준일+6,기준달(월))

EX) 90년 2월6일 입사인경우 -> 90년 3월 1일이 기준일

    97년12월1일 입사인경우 -> 98년 1월 1일이 기준일

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

형변환 함수

@ TO_CHAR

 -> 날짜형데이터를 문자형데이터로 변환, 숫자데이터를 문자형데이터로 변환

1. 날짜 -> 문자

ex) TO_CHAR(DATE,[FORMAT])

TO_CHAR(SYSDATE,FORMAT)

YYYY-> 년도 4자리로

YY -> 년도 2라리로표현

MONTH -> 월 표시

MM -> 월을 숫자로 표현

DD -> 날짜 표현

D -> 요일을 숫자로 표현(1:일요일 ~ 7:토요일)

DAY -> 요일 표현

DY -> 요일을 약어 표현(월요일 -> 월)

HH, HH12 -> 시간을 표현(12시간으로 표현)

HH24 -> 시간을 표현(24시간으로 표현)

MI -> 분

SS -> 초

AM,PM -> 오전 오후 표기

FM -> 월,일,시,분,초 앞에 0을 제거

ex) 08월 -> 8월

ex) EMPLOYEE 테이블에서 사원명, 고용일을 출력

고용일 -> 1990/02/06(화)

숫자 -> 문자

1. 숫자표시단위는 충분한 크기를 주어야함

2. 변환될 숫자의 길이보다 포맷길이가 작으면 오류발생

@ TO_DATE

 -> 숫자 혹은 문자형데이터를 날짜형 데이터로 변환하여 리턴

@ TO_NUMBER

 -> 날짜 또는 문자형 데이터를 숫자형 데이터로 변환하여 리턴

@ NVL

 -> NULL을 처리하는 함수

 -> NULL로 되어있는 컬럼의 값을 지정한 숫자 혹은 문자로 변경하여 리턴

보너스가 포함되어있는 연봉 만들어보기

@ DECODE

 -> 선택함수

ex) DECODE(표현식,조건1,결과1,조건2,결과2,조건3,결과3) 등등

 -> 일치하는 값으로 해야한다.

@ CASE 

 -> 선택함수

 -> 범위값도 가능하다.

ex) CASE WHEN 조건1 THEN 결과 1

WHEN 조건2 THEN 결과 2

WHEN 조건3 THEN 결과 3

ELSE 결과 END

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

단일행함수 -> 여러개의 결과

그룹함수 -> 1개결과

@ SUM

 -> 해당 컬럼 값들의 총합

@ AVG

 -> 해당 컬럼 값들의 평균

@ COUNT

 -> 테이블에서 조건을 만족하는 행의 갯수를 반환해준다.

@ MAX/MIN

 -> 그룹의 최대값, 최소값을 반환

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

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

-- ////////////////////////////////////////////////////////////////////////////--

--2018-08-16

--LOWER : 소문자로 변환

select LOWER('Hello Java') from dual; --전부 소문자

--UPPER : 대문자로 변환

select UPPER('Hello Java') from dual; --전부 대문자

--INITCAP : 각 단어 첫글자만 대문자

select INITCAP('hello java') from dual;--첫글자만 대문자로 바꿔준다. 공백 후에도 첫글자로 인식

select concat('가나라다','ABCD') from dual;--두개가 합쳐져서 나온다.,문자에 대해서만 합쳐준다.

-- ||이것도 배웠는데 이것도 타입안가리고 바로 합친다.

select EMP_NAME || ' 연봉 은? ' || salary from employee;

select replace('KH JAVA','JAVA','DB') from dual; --KH JAVA에서 자바라는 단어를 DB로변경할꺼야

/*

EMPLOYEE 테이블에서 메일주소 있을텐데 kh.or.kr -> iei.or.kr

출력은 이름이랑 이메일만 하기

*/

select * from EMPLOYEE;

select EMP_NAME, replace(EMAIL,'kh.or.kr','iei.or.kr') from EMPLOYEE;

--@ABS -> 절대값 리턴

select abs(-10) from dual;---> 절대값 리턴

--@MOD -> % 나머지값 리턴

select mod(10,3) from dual;--나머지를 말하는 것이다. -> % 나머지값 리턴

--@ROUND -> 지정한 위치 이후의 값을 반올림

select round(126.456,2) from dual;--소수점 기준으로 첫번째 두번째 세번째이다.반올림 해준다는것이다.

select round(123.456,-2) from dual;--양수면 뒤로가고 음수면 앞으로 간다.

select FLOOR(123.456) from dual;

select TRUNC(123.456,2) from dual;

select TRUNC(123.456,-1) from dual;

--@CEIL  -> 숫자를 올림계산하여 나온수

select CEIL(126.456) from dual;--그냥 다 올린다.

select sysdate from dual;

select current_date from dual;

select localtimestamp from dual;

select current_Timestamp from dual;

--@ MONTHS_BETWEEN  -> 날짜 두개를 전달받아서, 개월수 차이를 숫자형으로 반환

select EMP_NAME, FLOOR(MONTHS_BETWEEN(sysdate, HIRE_DATE)) as 근무개월수 from EMPLOYEE;

--기준이되는날짜, 개월수 구할려는것

select EMP_NAME , FLOOR((sysdate-HIRE_DATE)/365) from employee;

select ADD_MONTHS(SYSDATE,6) from dual;--오늘날짜에 6개월을 더하는 것이다.

/*

군복무기간 36개월

1. 제대일자

2. 제대까지 먹을 짬밥수준(하루3끼)

dual 테이블 사용

*/

select ADD_MONTHS(SYSDATE,36) as 제대일자 , ((ADD_MONTHS(SYSDATE,36)-sysdate)*3)||'끼' as "짬밥" from dual;

select NEXT_DAY(SYSDATE,'금요일') from dual; --아주 중요한 날이 나온다.

select NEXT_DAY(SYSDATE,'금') from dual; --아주 중요한 날이 나온다.

select NEXT_DAY(SYSDATE,6) from dual; --아주 중요한 날이 나온다.

/*

1 = 일요일, 2 = 월요일, 3 = 화요일 ~7 = 토요일

MONDAY로 쓰면 안된다. 오라클 설치할때 한글버전을 설치해서 그렇다. 굳이 영어로 쓰고싶다 하면 

ALTER SESSION SET NLS_LANGUAGE = AMERICAN; 치고 하면 영어로도 된다.

*/

select last_day(sysdate) from dual;--이번달의 마지막날짜를 반환해준다.

select last_day(sysdate)+1 from dual;--1을 더하면 그다음날짜 1일이 된다.

select last_day(last_day(sysdate)+1) from dual;

select last_day(TO_DATE('18/08/31','yy/mm/dd')) from dual;

select EXTRACT(YEAR FROM SYSDATE), EXTRACT(MONTH FROM SYSDATE), EXTRACT(DAY FROM SYSDATE) from dual;

/*

1. EMPLOYEE테이블에서 사원의 이름, 입사일, 년차를 출력

단, 입사일은 YYYY년,M월,D일로 출력

년차출력은 소숫점인경우 올림(28.11 -> 29년차)

출력시 정렬은 입사일기준 오름차순

2.특별보너스를 지급하기 위한 자료가 필요

기준달은 입사 다음달의 6개월 후 시점

-> 입사일을 기점으로 다음달 1일부터 6개월을 계산하여 출력

(이름,입사일,기준일,기준일+6,기준달(월))

EX) 90년 2월6일 입사인경우 -> 90년 3월 1일이 기준일

    97년12월1일 입사인경우 -> 98년 1월 1일이 기준일

*/

select * from EMPLOYEE;

select EMP_NAME, EXTRACT(YEAR FROM HIRE_DATE) || '년' ,EXTRACT(MONTH from HIRE_DATE) ||'월',

EXTRACT(DAY from HIRE_DATE) ||'일', FLOOR(CEIL((SYSDATE-HIRE_DATE)/365)) from EMPLOYEE order by 5;

select EMP_NAME ,  ((EXTRACT(YEAR FROM HIRE_DATE)) || '년') ||(EXTRACT(MONTH from HIRE_DATE) ||'월') ||

(EXTRACT(DAY from HIRE_DATE) ||'일') as 입사일, FLOOR(CEIL((SYSDATE-HIRE_DATE)/365))as 년차 from EMPLOYEE order by 2;

select EMP_NAME, HIRE_DATE as 입사일, last_day(HIRE_DATE)+1 as 기준일,ADD_MONTHS((last_day(HIRE_DATE)+1),6) as "기준일+6"

,EXTRACT(MONTH from (last_day(HIRE_DATE)+1)) || '월'as 월 from EMPLOYEE;

--////////////////////////////////////////////////--

select TO_CHAR(SYSDATE,'YYYY-MM-DD') from dual;

--내가 지정한 형태로 나온다.

select to_char(sysdate,'yyyy/mm/dd') from dual;

select to_char(sysdate,'yyyy/mm/dd/DY') from dual;

select to_char(sysdate,'yyyy/mm/dd/DAY') from dual;

select to_char(sysdate,'yyyy/MONTH/dd/DAY') from dual;

select to_char(sysdate,'yyyy/mm/dd HH"시"MI"분"SS"초"') from dual;

select to_char(sysdate,'yyyy/FMmm/dd HH"시"MI"분"SS"초"') from dual;

/*

ex) EMPLOYEE 테이블에서 사원명, 고용일을 출력

고용일 -> 1990/02/06(화)

*/

select EMP_NAME,to_char(HIRE_DATE,'yyyy/mm/dd"("dy")"') as 고용일 from EMPLOYEE;

select * from EMPLOYEE;

select EMP_NAME, TO_CHAR(5000000,'999,999,999'), TO_CHAR(5000000,'999,999'),

TO_CHAR(5000000,'000,000,000'), TO_CHAR(5000000,'L999,999,999'),

TO_CHAR(5000000,'999,999,999.999') from EMPLOYEE;

/*

억단위로 쓸수있고, 범위가 충분하면 정상적으로 출력이 되고, 내가 지정한 범위보다 숫자가 더 크다면 정상적으로 출력이 안된다.

L은 원화표시로 출력이 된다. LOCAL로 통화로 표시되는데 우리나라는 원화 쓰기 때문에 이렇게 된다.

*/

select EMP_NAME, TO_CHAR(SALARY,'000,000,000'), TO_CHAR(SALARY,'L999,999,999')

 from EMPLOYEE;

select TO_DATE(20000101,'YYYYMMDD') from dual;

select TO_CHAR(TO_DATE('20180816','YYYYMMDD'),'YYYY/MM/DD HH24:MI:SS') from dual;

/*

EMPLOYEE 테이블에서 2000년도 이후 입사한 사원의 사번, 이름, 입사일을 조회

TO_DATE -> WHERE절에 사용

*/

select * from EMPLOYEE;

select EMP_ID, EMP_NAME, HIRE_DATE from EMPLOYEE where HIRE_DATE>= TO_DATE('20000101','YY/MM/DD');

select to_NUMBER('1,000,000','9,999,999') - to_NUMBER('500,000','999,999') from dual;

--연산을 해보자, 문자백만을 숫자로 숫자로 바꾸고 문자 50만을 숫자로 바꿨다. 그럼 딱 50만이 나와야한다.뒤에는 범위이다.

select to_NUMBER('123') - to_NUMBER('23') from dual;

select to_NUMBER('123A') - to_NUMBER('23') from dual;--이건 안된다.

select '123'+'23' from dual; --자동형변환되서 된다.

--보너스가 포함되어있는 연봉

select EMP_NAME,(salary*12+salary*12*NVL(BONUS,0)) from EMPLOYEE; --보너스의 값이있으면 그대로 쓰고 null값이 있으면 null값을 0으로 쓸것이다.

select EMP_NAME, BONUS, NVL(BONUS,0) from EMPLOYEE;--NVL의 사용법이다.

select EMP_NAME as 이름, DECODE(SUBSTR(EMP_NO,8,1),'1','남','2','여','종규')as 성별 from EMPLOYEE;

--EMP_NO의 8번째 글자중 하나를 가지고와서 그값이 1이면 남자 2면 여자야,1도2도아니면 종규입니다.

select EMP_NAME, case when SUBSTR(EMP_NO,8,1)=1 then '남' when SUBSTR(EMP_NO,8,1)=2 then '여'

ELSE '종규' END from EMPLOYEE;

select EMP_NAME, case when SUBSTR(EMP_NO,1,2) <65 then '60년생 초반'

when SUBSTR(EMP_NO,1,2) >=65 then '60년생 후반' END as 성별 from EMPLOYEE WHERE SUBSTR(EMP_NO,1,2) BETWEEN 60 and 69;

--60년생부터 69년생을 다 갖고와서 60년대 후반인지 60년대 초반인지 볼수있다.

select sum(SALARY) from EMPLOYEE;--한달에 내야하는 월급이 이거이다.

select sum(SALARY) from EMPLOYEE where SUBSTR(EMP_NO,8,1)=1;--남자사원의 총합

/*

EMPLOYEE 테이블에서 부서코드가 D5인 직원의 보너스를 포함한 연봉의 총합

*/

select sum(salary*12+(salary*NVL(BONUS,0)*12)) from EMPLOYEE where DEPT_CODE='D5';

select * from EMPLOYEE;

/*

EMPLOYEE 테이블에서 전사원의 보너스 평균을 소수 둘째자리에서 반올림

*/

select round(avg(salary*(NVL(BONUS,0)*12)),2) from EMPLOYEE;

select round(AVG(BONUS),2) from EMPLOYEE;

--이거일경우 null값일경우 평균에서 빼고 계산한다.

select round (AVG(NVL(BONUS,0)),2) from EMPLOYEE;

--이거일경우 0이포함되었기때문에 0도 포함해서 계산해서 값이 더 낮게 나온다.

select count(*) from EMPLOYEE;

select * from EMPLOYEE;

select count(*) from EMPLOYEE where DEPT_CODE='D5';--부서코드가 D5인 사원수

select DEPT_CODE from EMPLOYEE;

select DISTINCT DEPT_CODE from EMPLOYEE;--중복값 제거

select count(DISTINCT DEPT_CODE) from EMPLOYEE;--null값 제외하고 뜬다.

select MAX(SALARY), MIN(SALARY) from EMPLOYEE;

select MAX(HIRE_DATE), MIN(HIRE_DATE) from EMPLOYEE;