[ SQL ] SQL 함수 정리

오늘은 SQL을 활용하기 전 자주 사용하는 함수들을  정리하는 글을 써보려 합니다.

 

어떤 일을 수행하는 기능으로써 주어진 인수를 입력받아 처리하여 그 결과를 반환하는 일을 수행하려면 함수를 활용해야 하기 때문에 유용한 함수들을 알아봅시다.

 

■ 함수의 종류

 

• 단일행 함수 : 결과 레코드를 찾아서 출력할 때마다 (각 행에) 함수가 적용됩니다.

문자함수 - 날짜함수 - 문자변환함수 - 숫자변환함수 - 날짜변환함수 - 시스템함수 - 숫자함수 등

• 복수행 함수[집계 함수, 그룹함수] : 조건절에 만족하는 모든 행을 다 찾고 나서 모든 레코드를 한 번에 연산합니다.

count() : 레코드 개수
sum() : 합계
avg() : 평균
max() : 최대값
min() : 최소값
round(값, 소수점자리수) : 지정소수점자리에서 반올림 등

 

 

■ 유용한 함수들

 

<문자함수>

chr(아스키코드) : 해당 아스키 코드값에 대한 문자 반환

ascii(문자) : 문자의 아스키코드 값을 반환함

concat(컬럼명, '문자열') : 컬럼에 해당하는 문자열을 붙임

initcap('문자열') : 시작 문자를 대문자로, 다른 문자는 소문자로 반환함

lower('문자열'), upper('문자열') : 문자열을 소문자로, 대문자로 반환함

LPAD('문자열1', 자리수,'문자열2') : 문자열1을 자리수만큼 늘리는데 왼쪽으로 늘어난 자리수 공간에 문자열2를 채워서 반환함,문자열2가 생략되면 공백으로 채워짐

RPAD('문자열1',자리수,'문자열2') : 오른쪽으로 공간을 늘림

LTRIM('문자열1' , '문자열2') : 문자열1에서 문자열2를 왼쪽으로 제거한 결과값을 반환함

RTRIM('문자열1','문자열2') : 오른쪽에서 제거함

replace('문자열','문자열2','문자열3') : 문자열1 중에 있는 문자열2를 문자열3으로 바꾸어서 결과값 반환함

substr('문자열',자리수,개수) : 문자열의 자리수부터 시작해서 지정된 개수만큼 문자를 잘라내서 결과를 반환함(시작인덱스는 1부터)

instr('문자열1','문자열2',자리수1,자리수2) : 자리수1부터 자리수2번째의 문자열2를 찾아서 시작위치를 반환함

length('문자열') : 문자열의 길이를 반환함

greatest('값1','값2','값3'....) : 가장 큰 값을 반환함 <--> least('값1','값2'...)

 

<날짜 함수>

sysdate : 시스템의 현재 날짜를 반환함 (sysdate는 의사컬럼이
아니라 함수임.)
//select sysdate from dual;

** 의사컬럼(모조컬럼) : 테이블에 있는 일반적인 컬럼처럼 행동하기는
하지만 실제로 테이블에 저장되어 있지 않은 컬럼.

1) rownum : 쿼리의 결과로 나오게 되는 각각의 row들에 대한 순서값을
가리키는 의사컬럼 (즉, 레코드의 순번을 출력)

2) rowid : 테이블에 저장된 각각의 row들에 대한 주소값을 가진 의사컬럼.
각각의 row들은 자신만의 rowid를 가짐 (즉, 레코드의 주소값을 출력)
//select rownum, rowid, empno, ename from emp
//where rownum <= 10;

add_months(날짜컬럼 or 날짜데이터, 숫자): 날짜값에 개월 수를
더해서 결과값을 반환함

 last_day(날짜컬럼 or 날짜데이터) : 파라미터 데이터와 같은 달의 마지
막 날짜를 반환함

 months_between(날짜컬럼1 or 이후 날짜 데이터1, 날짜컬럼2 or 이전
날짜데이터2): 두 날짜 사이의 개월수를 반환함

next_day(날짜컬럼 or 날짜데이터, 숫자 or 요일): 날짜데이터 이후의
날짜 중에서 숫자 or 요일로 명시된 첫번째 날짜를 반환함

 

<변환 함수>

문자변환 함수: 
to_char(날짜컬럼 or 날짜데이터, '??')
*** "??" 에 올 수 있는 값들

가) d: 주중의 일을 숫자로표시(1~7) ex) 일(1), 월(2), 화(3), ...토(7)

나) day: 일을 요일로 표시

다) dd: 1~31 형태로 일을 표시

라) mm: 01~12형태로 월을 표시

마) month, mon: 월을 서술형 이름으로 표시

바) yy: 뒤의 두자리 연도 표시

사) yyyy: 네자리 연도 표시

아) dd-mm-yy: 일-월-연도

자) yyyy-mm-dd: 연도-월-일 (활용)yyyy/mm/dd

차) hh, hh12: 1~12 형태로 시를 표시 (비교)hh24

카) mi: 0~59 형태로 분을 표시

타) ss: 0~59 형태로 초을 표시

파) am,pm: 오전, 오후 표시

하) yyyy-mm-dd am hh:mi:ss day

숫자변환 함수: to_number('숫자 형태의 문자열')

날짜변환 함수 : to_date('날짜 형태의 문자열');

시스템 함수: user - 현재 오라클에 접속중인 사용자를 반환함

 

<숫자 함수>

가) trunc(숫자1, 자리수) : 숫자1을 소수점 자리수에서 버림(절사)

나) round(숫자1, 자리수) : 숫자1을 소수점 자리수에서 반올림

다) ceil(숫자1) : 올림

 

<일반 함수>

가) NVL(컬럼, 치환할 값) : 컬럼의 값이 null이면 다른 값으로
치환(대체)함

나) decode : 값을 비교하여 해당하는 값을 돌려주는 함수. 단, 비교시에는
정확히 같은 값(=)만 비교가 가능함. 마치 java의 switch case문과 같다.
-- 형식 : decode(컬럼이나 값, 비교값1, 결과값1,비교값2, 결과값2,
....... 기본치) 또한 숫자값은 정수만 가능하다.
(참고: decode 함수에서 쓰이는 파라미터의 개수는 조건에 의해 늘어날
수 있음.)

 

함수를 활용해서 student테이블에서 제1전공(deptno1)이 101인 학과학생들의 이름과 주민번호, 학년(grade), 성별을 출력하되 성별은 주민번호 칼럼을 사용하여 7번째 숫자가 1일 경우 '남', 2일 경우 '여'로 출력해봅시다.

 

<예제 1>

select name 이름, deptno1 제1전공, grade 학년,jumin 주민번호, decode(substr(jumin,7,1),1,'남',2,'여') 성별
from student
where deptno1=101
order by grade;

---

마치며

 

오늘은 함수 용어 정리를 해보았습니다. 앞으로 SQL을 할 때 유용한 함수들이니 외워두시는 게 제일 좋습니다.

다음 포스팅에서 뵙겠습니다.