SQL 내장함수
문자: SUBSTR, INSTR, LENGTH, RPAD
숫자: ROUND, TRUNC, MOD, CEIL
날짜: SYSDATE, MONTHS_BETWEEN, LAST_DAY, ROUND, TRUNC
변환: TO_CHAR
일반: NVL
| 구분 | 내용 | 비고 |
|---|---|---|
| 문자형 | CHAR | 1~2000 Byte (고정데이터형) |
| VARCHAR2 | 1~4000 Byte (가변) | |
| CLOB | 4GB | |
| 숫자형 | NUMBER(4) | 0~9999 |
| NUMBER(7.2) | 99999.99 전체자리.소수자리 | |
| 날짜형 | Date | . |
| Timestamp | 100만분의 1초: 경주, 올림픽.. | |
| BLOB | Binary 형태 동영상,그림 | |
| BFILE | File 형태 동영상,그림 |
※ 펑션과 프로시저의 차이 : 리턴형이 있다(Function:함수) / 없다(프로시저)
| 구분 | 내용 | 비고 |
|---|---|---|
| 변환 | TO_CHAR |
문자열 변환 TO_CHAR(날짜,숫자,’변경’) (YYYY-MM-DD HH24:MI:SS) HH24 라 써야 24시가 나옴. 그냥 HH라 쓸거면 오전, 오후로 나오는걸 우리가 만들어야 함 TO_CHAR(sal, ‘$9999’) 800 => $800 TO_CAHR(sal, ‘$99,999) 70000 => $70,000 9 는 1자리 의미 0 은 빈자리를 0으로 표시 $ 는 $를 붙임 L 은 \을 붙임 . 은 소수점 이하표시 , 는 천단위 구분기호 표시 |
| 일반 | NVL | NULL값을 다른값으로 변경 |
SQL 단일행 함수
■ 문자 함수
| 구분 | 내용 | 비고 |
|---|---|---|
| 변형 | INITCAP | 1~2000 Byte (고정데이터형) |
| UPPER | 1~4000 Byte (가변) | |
| LOWER | 4GB | |
| 제어 | SUBSTR(문자,시작,갯수) | 문자를 잘라내옴 (첫번째 문자는 1) |
| INSTR(문자열,’찾는문자’,시작위치,몇번째인지(기본값1)) |
INSTR(ABABAB,’A’,1)=>1 INSTR(ABABAB,’B’,-1)=>6 INSTR(ABABAB,’A’,1,2)=>3 INSTR(ABABAB,’A’,-1,3)=>1 |
|
| 채우기 | LPAD |
LPAD(‘abc’,7,’#’) => ####abc |
| RPAD | .. | |
| TRIM | LTRIM |
LTRIM(‘ abcd’)=>’abcd’ LTRIM(‘abca’,’a’)=>’bca’ LTRIM(‘a bcd’,’a’)=>’ bcd’ |
| RTRIM | RTRIM(‘abca’,’a’)=>’abc’ | |
| 기타 | LENGTH |
■ 숫자 함수
| 구분 | 내용 | 비고 |
|---|---|---|
| ROUND | ROUND |
ROUND(123.78, 2)=>123.78 ROUND(123.78, 1)=>123.8 |
| TRUNC |
ROUND(123.78, 1)=>123.7 |
|
| MOD | MOD(5,2)=>5%2=>1 | |
| CEIL |
■ 날짜 함수
| 구분 | 내용 | 비고 |
|---|---|---|
| SYSDATE |
시스템 날짜, 시간 SYSDATE-1 SYSDATE+1 |
90일 뒤라면 +90 |
| MONTHS_BETWEEN | MONTHS_BETWEEN(현재, 입사) | |
| ADD_MONTHS | ADD_MONTH(hiredate, 6) | |
| LAST_DAY |
2월 넘기면 2월 28일 나옴 12월 넘기면 12월 31일 나옴 (윤년까지 반영) |
|
| NEXT_DAY |
(‘2015-03-13′,’금’) 이 다음날부터 따져서 첫번째 돌아오는 금요일을 찾음=>3월 20일 |
■ DECODE
(스위치 케이스와 비슷하다)
SELECT ename,DECODE(deptno,10,’영업부’,
20,’개발부’,
30,’총무부’) AS dname FROM emp;
■ CASE
(다중이프 문과 비슷하다)
SELECT ename,CASE WHEN deptno=10 THEN ‘영업부’
WHEN deptno=20 THEN ‘개발부’
WHEN deptno=30 THEN ‘총무부’
ELSE ‘신입사원’
END dname
FROM emp;
※ case문은 콤마를 찍지 않는다.
■ 복수행함수
COUNT : 회원가입 중복체크에 씀. COUNT해서 1이면 중복, 0이면 생성가능
SUM
AVG
MAX : 게시판 자동증가번호에 씀. MAX+1 하면 새로운 게시판번호
MIN
RANK
| 구분 | 내용1 | 내용2 | 내용3 |
|---|---|---|---|
| COUNT | ROW의 갯수 |
COUNT(*) ※전부 셀 경우 널값 포함 COUNT(column) ※컬럼명 적용시 널값은 제외한다 ex)회원가입시 아이디 중복체크 |
|
| SUM, AVG | |||
| MIN, MAX | ex)예약번호 max치에 +1 자동증가 | ||
| RANK | ex)조회수 순으로 정렬 |
■ 그룹바이
–한 개 묶기
SELECT deptno,ROUND(AVG(sal),2),SUM(sal),MAX(sal),MIN(sal)
FROM emp
GROUP BY deptno;
–두 개묶기
SELECT deptno,job,ROUND(AVG(sal),2),SUM(sal),MAX(sal),MIN(sal)
FROM emp
GROUP BY deptno,job;
–그룹 안에서 조건주기
–같은 부서의 인원수가 4명 이상인 업무와 인원수를 출력
SELECT deptno,count(*)
FROM emp
GROUP BY deptno
HAVING COUNT(*)>=4;