데이터베이스 (집공부3)

1. EMPLOYEE 테이블의 구조를 살펴보기 위해서 DESC 명령어를 사용한다.

ex) DESC EMPLOYEE;

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

2. 숫자 함수

구 분	설 명
ABS	절대값을 구한다.
COS	COSINE 값을 반환한다.
EXP	e(2.71828183...)의 n승을 반환한다.
FLOOR	소수점 아래를 잘라낸다.(버림)
LOG	LOG 값을 반환한다.
POWER	POWER(m,n) m의 n승을 반환한다.
SIGN	SIGN (n) n<0이면 -1, n=0이면 0, n>0이면 1을 반환한다.
SIN	SINE값을 반환한다.
TAN	TANGENT 값을 반환한다.
ROUND	특정 자릿수에서 반올림한다.
TRUNC	특정 자릿수에서 잘라낸다. (버림)
MOD	입력 받은 수를 나눈 나머지 값을 반환한다.

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

2.1. 절대값 구하는 ABS 함수

 -> ABS 함수는 절대값을 구합니다. 절대값은 방향은 없고 크기만 있는 것으로서 주어진 데이터가 음수일 경우 양수로 표현합니다.

ex)

select -10, ABS(-10) FROM dual;

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

2.2. 소수점 아래를 버리는 FLOOR 함수

 -> FLOOR 함수는 소수점 아래를 버립니다. 34.5678를 FLOOR 함수에 적용하면 34가 구해집니다.

ex)

select 34.5678, FLOOR(34.5678) from dual;

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

2.3. 특정 자릿수에서 반올림하는 ROUND 함수

 -> 34.5678를 반올림하면 35 입니다. 이와 같이 반올림한 결과를 구하기 위한 함수로 오라클에서는 ROUND가 제공된다.

ex)

select 34.5678, ROUND(34.5678) from dual;

형식 : ROUND(대상, 자릿수)

ex)

select 34.5678,ROUND(34.5678,2) from dual;

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

2.4. 특정 자릿수에서 잘라내는 TRUNC 함수

 -> TRUNC 함수는 지정한 자리 수 이하를 버린 결과를 구해주는 함수입니다.

ex)

select TRUNC(34.5678,2), TRUNC(34.5678,-1),TRUNC(34.5678) FROM dual;

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

2.5. 나머지 구하는 MOD 함수

 -> MOD 함수는 나누기 연산을 한 후에 구한 몫이 아닌 나머지를 결과로 되돌려주는 함수입니다.

ex)

select MOD(27,2), MOD(27,5), MOD(27,7) FROM DUAL;

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

3. 문자 처리 함수

구 분	설 명
LOWER	소문자로 변환한다.
UPPER	대문자로 변환한다.
INITCAP	첫 글자만 대문자로 나머지 글자는 소문자로 변환한다.
CONCAT	문자의 값을 연결한다.
SUBSTR	문자를 잘라 추출한다. (한글 1Byte)
SUBSTRB	문자를 잘라 추출한다. (한글 2Byte)
LENGTH	문자의 길이를 반환한다. (한글 1Byte)
LENGTHB	문자의 길이를 반환한다. (한글 2Byte)
LPAD, RPAD	입력 받은 문자열과 기호를 정렬하여 특정 길이의 문자열로 반환한다.
TRIM	잘라내고 남은 문자를 표시한다.
CONVERT	CHAR SET을 변환한다.
CHR	ASCII 코드 값으로 변환한다.
ASCII	ASCII 코드 값을 문자로 변환한다.
REPLACE	문자열에서 특정 문자를 변경한다.

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

3.1. 대문자로 변환하는 UPPER 함수

 -> UPPER 함수는 입력한 문자값을 대문자로 변환하는 함수입니다.

ex)

select 'Welcome to Oracle', UPPER('Welcome to Oracle') FROM DUAL;

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

3.2. 소문자로 변환하는 LOWER 함수

 -> LOWER 함수는 문자열을 모두 소문자로 변경합니다.

ex)

SELECT 'Welcome to Oracle',LOWER('Welcome to Oracle') FROM DUAL;

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

3.3. 이니셜만 대문자로 변환하는 INITCAP 함수

 -> INITCAP 함수는 문자열의 이니셜만 대문자로 변경합니다.

ex)

SELECT 'WELCOME TO ORACLE', INITCAP('WELCOME TO ORACLE') FROM DUAL;

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

3.4. 문자 길이를 구하는 LENGTH

 -> LENGTH 함수는 컬럼에 저장된 데이터 값이 몇 개의 문자로 구성되었는지 길이를 알려주는 함수입니다. 영문자와 한글의 길이를 구해봅시다.

ex)

SELECT LENGTH('Oracle'), LENGTH('오라클') FROM DUAL;

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

3.5. 바이트 수를 알려주는 LENGTHB 함수

 -> 이번에 살펴볼 LENGTHB 함수는 바이트 수를 알려주는 함수입니다. 

ex)

SELECT LENGTHB('Oracle'),LENGTHB('오라클') FROM DUAL;

-> 한글 1자는 2바이트를 차지합니다. 그렇기 때문에 수행 결과를 보면 한글 3자로 구성된 '오라클'의 LENGTHB 함수의 결과는 6이 됩니다.

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

3.6. 문자열 일부만 추출하는 SUBSTR 함수

 -> SUBSTR 과 SUBSTRB 함수는 대상 문자열이나 칼럼의 자료에서 시작위치부터 선택 개수만큼의 문자를 추출합니다. SUBSTRB 함수도 같은 형식이지만 명시된 개수만큼의 문자가 아닌 바이트 수를 잘라낸다는 점에서만 차이가 나타납니다.

형식 : SUBSTR(대상,시작위치,추출할 개수)

ex)

SELECT SUBSTR('Welcome to Oracle',4,3) FROM DUAL;

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

3.7. 특정 문자의 위치를 구하는 INSTR 함수

 -> INSTR 함수는 대상 문자열이나 칼럼에서 특정 문자가 나타나는 위치를 알려줍니다.

ex)

SELECT INSTR('WELCOME TO ORACLE','O') FROM DUAL;

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

3.8. 특정 기호로 채우는 LPAD / RPAD 함수

 -> LPAD(LEFT PADDING) 함수는 칼럼이나 대상 문자열을 명시된 자릿수에서 오른쪽에 나타내고, 남은 왼쪽 자리를 특정 기호로 채웁니다.

ex) SELECT LPAD('Oracle',20,'#') FROM DUAL;

 -> RPAD(RIGHT PADDING) 함수는 반대로 칼럼이나 대상 문자열을 명시된 자릿수에서 왼쪽에 나타내고, 남은 오른쪽 자리를 특정 기호로 채웁니다.

ex) SELECT RPAD('Oracle',20,'#') FROM DUAL;

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

3.9. 왼쪽에서 공백 문자를 삭제하는 LTRIM 함수

 -> LTRIM 함수는 문자열의 왼쪽(앞)의 공백 문자들을 삭제합니다.

ex) SELECT LTRIM('Oracle') FROM DUAL;

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

3.10. 오른쪽에서 공백 문자를 삭제하는 RTRIM 함수

 -> RTRIM 함수 역시 다음과 같이 기술하면 공백 문자를 잘라냅니다.

ex) SELECT RTRIM('Oracle') FROM DUAL;

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

3.11. 특정 문자를 잘라내는 TRIM 함수

 -> TRIM 함수는 칼럼이나 대상 문자열에서 특정 문자가 첫 번째 글자이거나 마지막 글자이면 잘라내고 남은 문자열만 반환합니다.

ex) SELECT TRIM('a' FROM 'aaaaaOracleaaaaa') FROM DUAL;

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

4. 날짜 함수

구 분	설 명
SYSDATE	시스템 저장된 현재 날짜를 반환한다.
MONTHS_BETWEEN	두 날짜 사이가 몇 개월인지를 반환한다.
ADD_MONTHS	특정 날짜에 개월 수를 더한다.
NEXT_DAY	특정 날짜에서 최초로 도래하는 인자로 받은 요일의 날짜를 반환한다.
LAST_DAY	해당 달의 마지막 날짜를 반환한다.
ROUND	인자로 받은 날짜를 특정 기준으로 반올림 한다.
TRUNC	인자로 받은 날짜를 특정 기준으로 버린다.

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

4.1. 현재 날짜를 반환하는 SYSDATE 함수

 -> SYSDATE 함수는 시스템에 저장된 현재 날짜를 반환하는 함수입니다. 

ex)

SELECT SYSDATE FROM DUAL;

 -> 날짜 형 데이터에 숫자를 더하면 그날짜로부터 그 기간만큼 계산을 한다.

ex)

SELECT SYSDATE-1 어제, SYSDATE 오늘, SYSDATE+1 내일 FROM DUAL;

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

4.2. 특정 기준으로 반올림하는 ROUND 함수

 -> 숫자를 반올림하는 함수로 학습하였다.

형식 : ROUND(date,format)

포맷 모델	단 위
CC, SCC	4자리 연도의 끝 두글자를 기준으로 반올림
SYYY, YYYY, YEAR SYEAR, YYY, YY, Y	년(7월 1일부터 반올림)
DDD, D, J	일을 기준
HH, HH12, HH24	시를 기준
Q	한 분기의 두 번째 달의 16일을 기준으로 반올림
MONTH, MON, MM, RM	월(16일을 기준으로 반올림)
DAY, DY, D	한주가 시작되는 날짜
MI	분을 기준

-> ROUND 함수의 포멧 모델로 MONTH로 지정하였기에 특정 날짜(DATE)를 달(MONTH)을 기준으로 반올림한 날짜를 구합니다.

-> 일을 기준으로 16일보다 적으면 이번달 1일을 크면 다음달 1일을 구합니다. 6월 9일 반올림하며 6월 1일이 되고 11월 17일은 반올림하여 12월 1이 됩니다. 또한 1월 23일을 반올림하면 2월 1일 됩니다.

ex)

SELECT HIREDATE, ROUND (HIREDATE,'MONTH') FROM EMP;

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

4.3. 특정 기준으로 버리는 TRUNC 함수

 -> TRUNC 함수 역시 숫자를 잘라내는 것뿐만 아니라 날짜를 잘라낼 수 있습니다.

ex)

TRUNC(data,format)

 -> 특정날짜(DATE)를 달(MONTH)을 기준으로 버림한 날짜를 구하기 위해서는 다음과 같이 표현합니다.

ex) SELECT HIREDATE, TRUNC(HIREDATE, 'MONTH') FROM EMP;

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

4.4. 두 날짜 사이 간격을 MONTHS_BETWEEN 함수

 -> MONTHS_BETWEEN 함수는 날짜와 날짜 사이의 개월 수를 구하는 함수입니다.

형식 : MONTHS_BETWEEN(date1, date2)

 -> 다음은 각 직원들의 근무한 개월 수를 구하는 예제입니다.

ex)

SELECT ENAME, SYSDATE, HIREDATE, MONTHS_BETWEEN (SYSDATE, HIREDATE) FROM EMP;

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

4.5. 개월 수를 더하는 ADD_MONTHS 함수

 -> ADD_MONTHS 함수는 특정 개월 수를 더한 날짜를 구하는 함수입니다.

형식 : ADD_MONTHS(date, number)

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

4.6. 해당 요일의 가장 가까운 날짜를 반환하는 NEXT_DAY 함수

 -> NEXT_DAY 함수는 해당 날짜를 기준으로 최초로 도래하는 요일에 해당되는 날짜를 반환하는 함수이다.

형식 : NEXT_DAY(date,요일)

 -> 오늘을 기준으로 최초로 도래하는 수요일은 언제인지 알아보는 예제이다.

ex) SELECT SYSDATE, NEXT_DAY(SYSDATE,'수요일') FROM DUAL;

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

4.7. 해당 달의 마지막 날짜를 반환하는 LAST_DAY 함수

 -> LAST_DAY 함수는 해당 날짜가 속한 달의 마지막 날짜를 반환하는 함수이다.

ex)

SELECT HIREDATE, LAST_DAY(HIREDATE) FROM EMP;

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

5. 형 변환 함수

 -> 오라클을 사용하다 보면 숫자, 문자, 날짜의 데이터 형을 다른 데이터 형으로 변환해야 하는 경우가 생긴다.

구 분	설 명
TO_CHAR	날짜형 혹은 숫자형을 문자형으로 변환한다.
TO_DATE	문자형을 날짜형으로 변환한다.
TO_NUMBER	문자형을 숫자형으로 변환한다.

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

5.1. 문자형으로 변환하는 TO_CHAR 함수 : 날짜형을 문자형으로 변환

 -> DATE 형태의 데이터를 지정한 양식에 의해 VARCHAR2 형의 문자로 변환합니다.

형식 : TO_CHAR (날짜 데이터, '출력형식')

종 류	의미
YYYY	년도 표현(4자리)
YY	년도 표현(2자리)
MM	월을 숫자로 표현
MON	월을 알파벳으로 표현
DAY	요일 표현
DY	요일을 약어로 표현

-> 다음은 현재 날짜를 기본 형식과 다른 형태로 출력해보자

ex)

SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM DUAL;

-> 사원들의 입사일을 출력하되 요일까지 함께 출력해 봅시다.

ex)

SELECT HIREDATE, TO_CHAR (HIREDATE, 'YYYY/MM/DD DAY') FROM EMP;

-> 년도를 2자리(YY)로 출력하고 월은 문자(MON)로 표시하고 요일을 약어(DY)로 표시한 예입니다.

ex)

SELECT HIREDATE, TO_CHAR (HIREDATE, 'YY/MON/DD DY') FROM EMP;

종 류	의미
AM 또는 PM	오전(AM), 오후(PM) 시각 표시
A.M 또는 P.M	오전(A.M), 오후(P.M) 시각 표시
HH또는 HH12	시간(1~12)
HH24	24시간으로 표현(0~23)
MI	분 표현
SS	초 표현

-> 현재 날짜와 시간을 출력하는 예제이다.

ex) SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD',HH24:MI:SS') FROM DUAL;

-> 1230000이란 숫자를 문자 형태로 출력하는 예입니다.

ex) SELECT TO_CHAR(1230000) FROM DUAL;

구 분	설 명
0	자릿수를 나타내며 자릿수가 맞지 않을 경우 0으로 채운다.
9	자릿수를 나타내며 자릿수가 맞지 않아도 채우지 않는다.
L	각 지역별 통화 기호를 앞에 표시한다.
.	소수점
,	천 단위 자리 구분

-> 각 지역별 통화 기호를 앞에 붙이고 천 단위마다 콤마를 붙여서 출력하려면 이렇게 해야합니다.

ex) SELECT ENAME, SAL, TO_CHAR (SAL,'L999,999') FROM EMP;

-> 9는 자릿수를 나타내며 자릿수가 맞지 않으면 채우지 않습니다. 하지만 0은 자릿수를 나타내며 자릿수가 맞지 않을 경우 0으로 채웁니다.

ex) SELECT TO_CHAR(123456,'0000000'),TO_CHAR(123456,'999,999,999') FROM DUAL;

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

5.2. 날짜형으로 변환하는 TO_DATE 함수

-> TO_DATE 함수는 문자열을 날짜 형으로 변환합니다.

형식 : TO_DATE('문자','format')

-> 숫자형태인 19810220을 TO_DATE 함수를 사용해서 날짜형으로 변환해보자

ex) SELECT ENAME, HIREDATE FROM EMP WHERE HIREDATE=TO_DATE(19810220,'YYYYMMDD')

-> 올해 며칠이 지났는지 현재 날짜에서 2008/01/01을 뺀 결과를 출력해보자

ex) SELECT TRUNC(SYSDATE-TO_DATE('2008/01/01','YYYY/MM/DD')) FROM DUAL;

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

5.3. 숫자형으로 변환하는 TO_NUMBER 함수

-> 특정 데이터를 숫자형으로 변환해 주는 함수입니다.

ex)

SELECT TO_NUMBER('20,000','99,999') - TO_NUMBER('10,000','99,999') FROM DUAL;

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

6. NULL을 다른 값으로 변환하는 NVL함수

-> NVL 함수는 NULL을 0 또는 다른 값으로 변환하기 위해서 사용하는 함수입니다.

ex)

SELECT ENAME, SAL, COMM, SAL*12+COMM, NVL(COMM,0), SAL*12+NVL(COMM,0) FROM EMP ORDER BY JOB;

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

7. 선택을 위한 DECODE 함수

-> DECODE 함수는 프로그램 언어에서 가장 많이 사용되는 switch case 문과 같은 기능을 갖습니다.

형식 : DECODE(표현식, 조건1, 결과1, 조건2, 결과2, 조건3, 결과3, 기본결과n)

ex) SELECT ENAME, DEPTNO, DECODE(DEPTNO, 10, 'ACCOUNTING',20,'RESEARCH',30,'SALES',40,'OPERATIONS') AS DNAME FROM EMP;

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

8. 조건에 따라 서로 다른 처리가 가능한 CASE 함수

-> CASE 함수 역시 여러 가지 경우에 대해서 하나를 선택하는 함수입니다.

-> DECODE 함수와 차이가 있다면 DECODE 함수는 조건이 일치(=비교연산자)하는 경우에 적용되는 반면, CASE 함수는 다양한 비교 연산자를 이용하여 조건을 제시할 수 있으므로 범위를 지정할 수도 있다.

-> CASE 함수는 프로그램 언어의 if else if else 와 유사한 구조를 갖는다.

형식 : CASE 표현식 WHEN 조건1 THEN 결과1 WHEN 조건2 THEN 결과2 WHEN 조건3 THEN 결과3 ELSE 결과n END

ex)

SELECT ENAME, DEPTNO, CASE WHEN DEPTNO=10 THEN 'ACCOUNTING' WHEN DEPTNO=20 THEN 'RESEARCH'

WHEN DEPTNO=30 THEN 'SALES' WHEN DEPTNO=40 THEN 'OPERATIONS' END AS DNAME FROM EMP;