주니어 기초 코딩공부/Database 기초

05장 SQL 주요 함수 (숫자 함수, 문자 함수)

jju_developer 2022. 12. 13. 20:39
728x90

오늘은 지난 시간의 SELECT로 특정 데이터를 추출하기에 이어서 SQL의 주요 함수에 대해 소개하는 시간을 갖겠습니다.

#DATABASE

#Oracle SQL

 

우선 만들어진 표의 이름은 EMP이며, 정보는 다음과 같습니다.

 

✔ SQL 주요 함수

제가 만든 EMP와 비슷한 테이블인데 SQL안에는 DUAL이라는 테이블이 있습니다.


 DUAL 테이블은 산술 연산의 결과를 한 줄로 얻기 위해서 오라클에서 제공하는 테이블이며,

 DUAL 테이블은 DUMMY라는 단 하나의 칼럼에 X라는 단 하나의 로우만을 저장하고 있으나 이 값은 아무런 의미가 없습니다.

기본적으로 DUMMY가 생성되어 있습니다.

 

현재 날짜를 구함

이렇게 DUAL 테이블을 통해 산술 연산의 결과를 한줄로 얻을 수 있습니다.

 

✔ SQL 주요 함수

1. 숫자 함수

주로 사용하는 함수는 하이라이트 되어있습니다.

 

예시)

*ABS - 절댓값을 구합니다.

예시)

*FLOOR- 소수점 아래 버립니다.

예시)

*ROUND - 반올림 합니다.

예시)

*TRUNK- 특정 자릿수 버립니다.

예시)

*MOD- 입력받은 수를 나눈 나머지 값을 반환합니다.

27%3=0

✔ SQL 주요 함수

2. 문자 함수

no 함수 내용 예시
1 UPPER 모두 대문자로 변경합니다. SELECT 'Welcome to Oracle', UPPER('Welcome to Oracle')
FROM DUAL;
2 LOWER 모두 소문자로 변경합니다. SELECT 'Welcome to Oracle', LOWER('Welcome to Oracle')
FROM DUAL;
3 INITCAP 문자열의 이니셜만 대문자로 변경합니다. SELECT 'WELCOME TO ORACLE', INITCAP('WELCOME TO ORACLE')
FROM DUAL;
4 LENGTH 컬럼에 저장된 데이터 값이 몇 개의 문자로 구성되었는지 길이를 알려주는 함수이다. SELECT LENGTH('Oracle'), LENGTH('오라클')
FROM DUAL;
5 LENGTHB 바이트 수를 알려주는 함수입니다.
특히, 한글 1자는 2바이트를 차지하기 때문에 수행 결과를 보면 한글 3자로 구성된 '오라클'의 LENGTHB 함수의 결과는 9가 됩니다.		 SELECT LENGTHB('Oracle'), LENGTHB('오라클')
FROM DUAL;
6 SUBSTR / SUBSTRB SUBSTR 과 SUBSTRB 함수는 대상 문자열이나 칼럼의 자료에서 시작위치부터 선택 개수만큼의 문자를 추출합니다.
SUBSTRB 함수도 같은 형식이지만 명시된 개수만큼의 문자가 아닌 바이트 수를 잘라낸다는 점에서만 차이가 있습니다.		 SELECT SUBSTR('Welcome to Oracle',4.3) FROM DUAL;
7 INSTR 대상 문자열이나 칼럼에서 특정 문자가 나타나는 위치를 알려줍니다. SELECT INSTR('WELCOME TO ORACLE', 'O') FROM DUAL;
8 INSTRB 바이트로 문자의 위치를 알아냅니다. SELECT INSTR('데이터베이스', '이', 3, 1), INSTRB('데이터베이스', '이', 3, 1)
FROM DUAL;
9 LPAD / RPAD 칼럼이나 대상 문자열을 명시된 자릿수에서 오른쪽에 나타내고, 남은 왼쪽 자리를 특정 기호로 채웁니다. 

RPAD(RIGHT PADDING) 함수는 반대로 칼럼이나 대상 문자열을 명시된 자릿수에서 왼쪽에 나타내고, 남은 오른쪽 자리를 특정 기호로 채웁니다.		 SELECT LPAD('Oracle', 20, '#')
FROM DUAL;
SELECT RPAD('Oracle', 20, '#')
FROM DUAL;
10 LTRIM / RTRIM LTRIM 함수는 문자열의 왼쪽(앞)의 공백 문자들을 삭제합니다.
RTRIM 함수 역시 오른쪽(뒤)의 공백 문자를 잘라냅니다.		 SELECT LTRIM(' Oracle ')
FROM DUAL;
SELECT RTRIM(' Oracle ')
FROM DUAL;
11 TRIM 칼럼이나 대상 문자열에서 특정 문자가 첫 번째 글자이거나 마지막 글자이면 잘라내고 남은 문자열만 반환합니다. SELECT TRIM('a' FROM 'aaaaOracleaaaa')
FROM DUAL;
12 REPLACE REPLACE()는 문자열을 치환하는 함수입니다.
예를 들어 담당자의 실수로 도서의 제목을 잘못 입력한 경우 REPLACE 함수를 사용하면 일일이 변경하지 않고 한꺼번에 변경할 수 있습니다.		 CONN MADANG/MADANG;
SELECT BOOKID, REPLACE(BOOKNAME,'야구','농구') BOOKNAME, PUBLISHER, PRICE FROM BOOK;

 

< 문자 함수 예시 >

UPPER의 결과
INITCAP 함수
UTF-8 가변형 문자셋 LENGTH
BYTE 길이
SUBSTR
O가 몇번째에 있는가?
INSTR, INSTRB
LPAD
LTRIM으로 왼쪽 여백만 지워지는 것을 볼 수 있습니다.

 

 

✔ SQL 주요 함수

3. 날짜 함수

no 함수 내용 예시
1 SYSDATE SYSDATE 함수는 시스템에 저장된 현재 날짜를 반환하는 함수입니다. SELECT SYSDATE
FROM DUAL;
2 ROUND 숫자 이외에 날짜에 대해서도 반올림을 할 수 있습니다. SELECT HIREDATE, ROUND(HIREDATE, 'MONTH')
FROM EMP;
3 TRUNC 숫자를 잘라내는 것뿐만 아니라 날짜를 잘라낼 수 있습니다.
ROUND 함수와 마찬가지로 포맷 형식을 주어 다양한 기준으로 날짜를 잘라낼 수 있습니다.		 SELECT HIREDATE, TRUNC(HIREDATE, 'MONTH')
FROM EMP;
4 MONTHS_BETWEEN 날짜와 날짜 사이의 개월 수를 구하는 함수 SELECT ENAME, SYSDATE, HIREDATE, MONTHS_BETWEEN (SYSDATE, HIREDATE)
FROM EMP;
5 ADD_MONTHS ADD_MONTHS 함수는 특정 개월 수를 더한 날짜를 구하는 함수 SELECT ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 6)
FROM EMP;
6 NEXT_DAY NEXT_DAY 함수는 해당 날짜를 기준으로 최초로 도래하는 요일에 해당되는 날짜를 반환하는 함수 SELECT SYSDATE, NEXT_DAY(SYSDATE, '수요일')
FROM DUAL;
7 LAST_DAY LAST_DAY 함수는 해당 날짜가 속한 달의 마지막 날짜를 반환하는 함수 SELECT HIREDATE, LAST_DAY(HIREDATE)
FROM EMP;

< 날짜 연산자 예시 >

SYSDATE
SYSDATE-1

✔ SQL 주요 함수

4. 형 변환 함수

 

no 함수 내용 예시
1 TO_CHAR (1) 날짜형을 문자형으로 변환하기

(2) 숫자형을 문자형으로 변환하기		 --1) 날짜형을 문자형으로 반환하기 : TO_CHAR

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

--2) 숫자형에서 문자형으로 변환하기 :  TO_CHAR

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

2 TO_DATE 날짜 형은 세기, 년도, 월, 일. 시간, 분. 초와 같이 날짜와 시간에 대한 정보를 저장

TO_DATE 함수는 문자열을 날짜 형으로 변환		 3) NUMBER->DATE
SELECT ENAME,HIREDATE FROM EMP
WHERE HIREDATE=TO_DATE(19810220,'YYYYMMDD');
-->숫자를 날짜 데이터 객체로 변환

4) CHAR-> DATE
SELECT TRUNC(SYSDATE-TO_DATE('2022/11/02','YYYY/MM/DD')) FROM DUAL;
-->오늘에서 2022/11/02 사이에 몇일이 지났는지 알아내기!

3 TO_NUMBER 특정 데이터를 숫자형으로 변환 5) 숫자형으로 변환:TO_NUMBER
--> CHAR-> NUMBER
SELECT TO_NUMBER('20,000','99,999') - TO_NUMBER('10,000','99,999') FROM DUAL;
4 NVL NULL을 0 또는 다른 값으로 변환하기 위해서 사용 SELECT ENAME, SAL, COMM, SAL*12+COMM,
NVL(COMM, 0), SAL*12+NVL(COMM, 0)
FROM EMP
ORDER BY JOB;

SELECT NVL(TO_CHAR(MGR),'다른값') FROM EMP;
5 DECODE switch case 문과 같은 기능을 갖는다. 즉, 여러 가지 경우에 대해서 선택할 수 있도록 합니다. DECODE (표현식, 조건1, 결과1,
조건2, 결과2,
조건3, 결과3,
기본결과n
)
6 CASE 프로그램 언어의 if else if else 와 유사한 구조를 가지고 있습니다. CASE 표현식 WHEN 조건1 THEN 결과1
WHEN 조건2 THEN 결과2
WHEN 조건3 THEN 결과3
ELSE 결과n
END

SQL에서는 형 변환 함수가 중요합니다.

 

서로 타입이 다른 데이터 형으로 변환해야 하는 경우가 자주 있기 때문에

위에 표를 보고 하나씩 예제를 살펴보도록 하겠습니다.

 

날짜를 --> 문자형으로 변환
숫자에서 문자로 변환

->앞에 아무런 숫자도 없으면 0으로 채웁니다.

 

숫자 -> 날짜 데이터로 변환

-> 변환과 동시에 해당되는 년도의 사람을 찾았습니다.

 

문자-> 날짜 데이터로 변환

->SYSDATE에서 희망하는 날짜의 문자열을 뺸것입니다.

 

CHAR-> NUMBER
날짜 데이터 형의 테이블에 값을 넣을때 일반 숫자는 들어가지 않습니다.
NULL값을 NVL로 감싸주었습니다.
DECODE
CASE

 

[ 확인 문제 ]

 

5-1 사원 테이블(EMP)에서 입사일(HIREDATE)을 4자리 연도로 출력되도록 SQL문을 작성하세요? (ex. 1980/01/01)

 

 

5-2. 사원 테이블(EMP)에서 MGR칼럼의 값이 null 인 데이터의 MGR의 값을 CEO로 출력하는 SQL문을 작성하세요?

 

방법 1)

방법 2)

 

지금까지 SQL의 주요 함수에 대해서 알아보았습니다.

 

수정사항이나 궁금하신 점은 댓글 부탁드립니다.

 

감사합니다~!

728x90