1. 데이터 처리, 오라클 함수 (128P)
1) 오라클 함수
* 내장 함수 : 재료가 이미 안에 들어 있는
* 단일행 함수(개별적용) : 결과값을 넣은 것만 출력
* 다중행 함수(요약) : 행만 출력된
2) 문자 함수
* UPPER : 대문자 변환
* LOWER : 소문자 변환
* INITCAP : 첫글자만 대문자, 나머지는 소문자
--문자함수
--회원 이름 정보 출력
SELECT * FROM EMP;
SELECT ENAME, UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME) FROM EMP;
--
SELECT * FROM EMP WHERE ENAME = 'scott';
SELECT * FROM EMP WHERE UPPER(ENAME) = UPPER('scott');
SELECT * FROM EMP WHERE UPPER(ENAME) LIKE UPPER('%scott%');
SELECT * FROM EMP WHERE UPPER(ENAME) LIKE UPPER('%AM%');
--
SELECT * FROM EMP WHERE LOWER(ENAME) LIKE '%am%';
SELECT * FROM EMP WHERE UPPER(ENAME) LIKE '%AM%';
--문자 바이트
SELECT LENGTH('AB'), LENGTHB('AB') FROM DUAL;
SELECT LENGTH('이름'), LENGTHB('이름') FROM DUAL;
* 영어는 1바이트,
한글은 2바이트
--문자열 일부 추출
SELECT JOB, SUBSTR (JOB, 1, 2), SUBSTR (JOB, 3, 2), SUBSTR (JOB, 5) FROM EMP;
SELECT ENAME, SUBSTR (ENAME, 3) FROM EMP;
--SUBSTR & LENGTH
SELECT JOB, LENGTH (JOB) FROM EMP;
--CLERK 1~5
-- -LENGTH('CLERK') -5~-1
SELECT JOB,
SUBSTR (JOB, -LENGTH (JOB)),
SUBSTR (JOB, -LENGTH (JOB), 2),
SUBSTR (JOB, -2)
FROM EMP;
예시)
1 2 3 4 5
C L E R K
-5-4-3-2-1
* INSTR ('HELLO, ORACLE!', 'L', 2, 2) : 2번째 L을 찾아라
-- 특정 문자 위치
SELECT INSTR ('HELLO, ORACLE!', 'L'),
INSTR ('HELLO, ORACLE!', 'L', 5),
INSTR ('HELLO, ORACLE!',
'L',
2,
2)
FROM DUAL;
--
SELECT *
FROM EMP
WHERE ENAME LIKE '%S%';
SELECT *
FROM EMP
WHERE INSTR (ENAME, 'S') > 0;
--다른 문자로 대체
SELECT '010-1234-5678' AS REPLACE_BEFORE,
REPLACE ('010-1234-5678', '-', ' '),
REPLACE ('010-1234-5678', '-', '*'),
REPLACE ('010-1234-5678', '-')
FROM DUAL;
--빈공간을 특정 문자로 채우는 함수
SELECT 'Oracle',
LPAD ('Oracle', 10, '#'),
RPAD ('Oracle', 10, '*'),
LPAD ('Oracle', 10),
RPAD ('Oracle', 10)
FROM DUAL;ㄴ
-- 문자열 합치기
SELECT EMPNO, ENAME
FROM EMP
WHERE ENAME = 'SCOTT';
SELECT CONCAT (EMPNO, ENAME)
FROM EMP
WHERE ENAME = 'SCOTT';
SELECT CONCAT (':', ENAME), CONCAT (EMPNO, CONCAT (':', ENAME))
FROM EMP
WHERE ENAME = 'SCOTT';
--
SELECT EMPNO || ENAME
FROM EMP
WHERE ENAME = 'SCOTT';
SELECT ':' || ENAME, EMPNO || ':' || ENAME
FROM EMP
WHERE ENAME = 'SCOTT';
--
--'['|| ||']'
--' _ _Oracle_ _ '
--TRIM()
--TRIM(BOTH FROM)
--TRIM(LEADING FROM)
--TRIM(TRAILING FROM)
--특정 문자 지우는 함수
SELECT '[' || ' _ _Oracle_ _ ' || ']',
'[' || TRIM (' _ _Oracle_ _ ') || ']',
'[' || TRIM (BOTH FROM ' _ _Oracle_ _ ') || ']',
'[' || TRIM (LEADING FROM ' _ _Oracle_ _ ') || ']',
'[' || TRIM (TRAILING FROM ' _ _Oracle_ _ ') || ']'
FROM DUAL;
LEADING : 왼쪽 글자 지우기
TRAILING : 오른쪽 글자 지우기
3) 숫자함수
--숫자함수
--반올림
SELECT ROUND (1234.5678),
ROUND (1234.5678, 0),
ROUND (1234.5678, 1),
ROUND (1234.5678, 2),
ROUND (1234.5678, -1),
ROUND (1234.5678, -2)
FROM DUAL;
--버리기
SELECT TRUNC (1234.5678),
TRUNC (1234.5678, 0),
TRUNC (1234.5678, 1),
TRUNC (1234.5678, 2)
FROM DUAL;
TRUNC : 소수점을 버리는 함수
-- CEIL : 큰 정수 값 = 3 < 3.14 < 4
-- FLOOR : 작은 정수 값 = 3 < 3.14 < 4
-- CEIL : 큰 정수 값 = -4 < 3.14 < -3
-- FLOOR : 작은 정수 값 = 4 < 3.14 < -3
SELECT CEIL(3.14),
FLOOR(3.14),
CEIL(-3.14),
FLOOR(-3.14)
FROM DUAL;
--나머지
SELECT MOD(15,6),
MOD(10,2),
MOD(11,2)
FROM DUAL;
ROUND : 반올림
TRUNC : 버리기
CEIL : 위로 반올림
FLOOR : 아래 반올림
뒤에 자리를 의미함.
4) 날짜 함수
SELECT SYSDATE AS NOW,
SYSDATE -1 AS TESTERDAY,
SYSDATE +1 AS TOMORROW
FROM DUAL;
--개월수 추가
SELECT SYSDATE, ADD_MONTHS(SYSDATE, 3) FROM DUAL;
--입사 10주년
SELECT EMPNO,ENAME,HIREDATE,ADD_MONTHS(HIREDATE, 120) AS WORK10YEARS FROM EMP;
--입사 41년 미만
SELECT EMPNO, ENAME,HIREDATE, ADD_MONTHS(HIREDATE,492),SYSDATE
FROM EMP WHERE ADD_MONTHS(HIREDATE, 492) > SYSDATE;
--입사 41년 초과
SELECT EMPNO,ENAME,HIREDATE,ADD_MONTHS(HIREDATE, 492),SYSDATE
FROM EMP WHERE ADD_MONTHS(HIREDATE, 492) < SYSDATE;
--날짜 반올림
SELECT SYSDATE ,
ROUND(SYSDATE,'CC'),
ROUND(SYSDATE,'YYYY'),
ROUND(SYSDATE,'Q'),
ROUND(SYSDATE,'DDD'),
ROUND(SYSDATE,'HH')
FROM DUAL;
--날짜 버리기
SELECT SYSDATE,
TRUNC(SYSDATE, 'CC'),
TRUNC(SYSDATE, 'YYYY'),
TRUNC(SYSDATE, 'Q'),
TRUNC(SYSDATE, 'DDD'),
TRUNC(SYSDATE, 'HH')
FROM DUAL;
CC : ABCD년도의 CD를 기준.
YYYY : ABCD년도의 ABCD년도 기준.
Q : 각 분기의 두 번째 달의 16일 기준
DDD : 해당 일의 정오를 기준 (12:00)
HH : 해당 일의 시간을 기준
5) 자료형 변환하는 형 변환 함수 // 나라별 날짜 표출
--형변환함수
SELECT EMPNO,ENAME,EMPNO,+'500' FROM EMP WHERE ENAME = 'SCOTT';
--숫자데이터를 따옴표로 묶어주면 문자데이터
--문자 데이터 500을 숫자로 인식해서 자동으로 숫자로 바꿔서 계산됨 = 자동형변환, 암시적형변환
SELECT 'ABC' + EMPNO, EMPNO FROM EMP WHERE ENAME = 'SCOTT';
--정말 문자 데이터이기 때문에 자동형변환이 안됨
--명시적형변환
--문자형변환
SELECT SYSDATE, TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') FROM DUAL;
--월일요일
SELECT SYSDATE,
TO_CHAR (SYSDATE, 'MM'),
TO_CHAR (SYSDATE, 'MON'),
TO_CHAR (SYSDATE, 'MONTH'),
TO_CHAR (SYSDATE, 'DD'),
TO_CHAR (SYSDATE, 'DY'),
TO_CHAR (SYSDATE, 'DAY')
FROM DUAL;
--나라별 월별 표시
SELECT SYSDATE,
TO_CHAR (SYSDATE, 'MM'),
TO_CHAR (SYSDATE, 'MON','NLS_DATE_LANGUAGE = KOREAN') AS K,
TO_CHAR (SYSDATE, 'MON','NLS_DATE_LANGUAGE = JAPANESE') AS J,
TO_CHAR (SYSDATE, 'MON','NLS_DATE_LANGUAGE = ENGLISH') AS E,
TO_CHAR (SYSDATE, 'MONTH','NLS_DATE_LANGUAGE = KOREAN') AS K,
TO_CHAR (SYSDATE, 'MONTH','NLS_DATE_LANGUAGE =JAPANESE') AS J,
TO_CHAR (SYSDATE, 'MONTH','NLS_DATE_LANGUAGE =ENGLISH') AS E
FROM DUAL;
--나라별 요일별 표시
SELECT SYSDATE,
TO_CHAR (SYSDATE, 'DD'),
TO_CHAR (SYSDATE, 'DY','NLS_DATE_LANGUAGE = KOREAN') AS K,
TO_CHAR (SYSDATE, 'DY','NLS_DATE_LANGUAGE = JAPANESE') AS J,
TO_CHAR (SYSDATE, 'DY','NLS_DATE_LANGUAGE = ENGLISH') AS E,
TO_CHAR (SYSDATE, 'MONTH','NLS_DATE_LANGUAGE = KOREAN') AS K,
TO_CHAR (SYSDATE, 'MONTH','NLS_DATE_LANGUAGE =JAPANESE') AS J,
TO_CHAR (SYSDATE, 'MONTH','NLS_DATE_LANGUAGE =ENGLISH') AS E
FROM DUAL;
--시분초
SELECT SYSDATE,
TO_CHAR(SYSDATE,'HH24:MI:SS') AS HH24,
TO_CHAR(SYSDATE,'HH12:MI:SS PM') AS HH12,
TO_CHAR(SYSDATE, 'HH:MI:SS PM') AS HH
FROM DUAL;
--숫자형식
SELECT SAL,
TO_CHAR(SAL,'$999,999') AS SAL_$,
TO_CHAR(SAL,'L999,999') AS SAL_L,
TO_CHAR(SAL,'999,999.00') AS SAL_1,
TO_CHAR(SAL,'000,999,999.00') AS SAL_2,
TO_CHAR(SAL,'000999999.99') AS SAL_3,
TO_CHAR(SAL,'999,999,00') AS SAL_4
FROM EMP;
L : 지역 화폐를 의미함 (한국)
암시적 형변환 : 자동으로 형을 바꿔주는 것.
--숫자형변환
SELECT 1300 - '1500', '1300' + 1500 FROM DUAL;
--
SELECT '1,300' - '1,500' FROM DUAL;
--
SELECT TO_NUMBER ('1,300', '999,999') - TO_NUMBER ('1,500', '999,999')
FROM DUAL;
--
TO_NUMBER를 쓰지 않으면 2번째 줄은 에러가 나온다.
문자에서 숫자를 빼야 하는 상황이기 때문이다.
--날짜형변환
SELECT TO_DATE('2018-07-14','YYYY-MM-DD') AS DATE1,
TO_DATE ('20180714','YYYY-MM-DD') AS DATE2
FROM DUAL;
--날짜 비교 = 1981년 6월 1일 이후에 입사한 사람
SELECT * FROM EMP WHERE HIREDATE > TO_DATE ('1981/06/01','YYYY/MM/DD');
--날짜 비교 = 1981년 6월 1일 이전에 입사한 사람
SELECT * FROM EMP WHERE HIREDATE < TO_DATE('1981/06/01','YYYY/MM/DD');
미래 날짜는 큰값이다 A > 11
과거 날짜는 작은값이다 A < 11
--RR 1950~2049
SELECT TO_DATE ('49/12/10', 'YY/MM/DD') AS Y1,
TO_DATE ('49/12/10', 'YY/MM/DD') AS Y2,
TO_DATE ('50/12/10', 'RR/MM/DD') AS Y3,
TO_DATE ('50/12/10', 'YY/MM/DD') AS Y4,
TO_DATE ('51/12/10', 'RR/MM/DD') AS Y5,
TO_DATE ('51/12/10', 'YY/MM/DD') AS Y6
FROM DUAL;
-- NULL 처리 함수
SELECT EMPNO,ENAME ,SAL, COMM,SAL+COMM,NVL(COMM,0),SAL+NVL(COMM,0) FROM EMP;
--
SELECT EMPNO,ENAME,SAL,COMM,NVL2(COMM,'0','X'),NVL2(COMM,SAL*12+COMM,SAL*12) FROM EMP;
NVL : 첫째값이 NULL이면 변함
NVL2 : 첫째값이 NULL이 아니면 변함
-- 상황에 따라 다른 데이터 반환
-- DECODE = 같다는 조건만 사용 가능
--연봉 협상
SELECT EMPNO,ENAME,JOB,SAL,
DECODE(JOB,
'MANAGER', SAL*1.1,
'SALESMAN',SAL*1.05,
'ANALYST',SAL,
SAL*1.03) AS UPSAL
FROM EMP;--CASE = 같다는 조건 말고 다른 조건 사용 가능
SELECT EMPNO,ENAME,JOB,SAL,
CASE JOB
WHEN 'MANAGER' THEN SAL*1.1
WHEN 'SALESMAN'THEN SAL*1.05
WHEN 'ANALYST' THEN SAL
ELSE SAL*1.03 END AS UPSAL
FROM EMP;
6) CASE 문
SELECT EMPNO,ENAME,COMM,
CASE
WHEN COMM IS NULL THEN '해당사항 없음'
WHEN COMM = 0 THEN '수당 없음'
WHEN COMM > 0 THEN '수당: ' || COMM
END AS COMM_TXT
FROM EMP;
7) 다중행 함수 - 하나의 열
--합계
SELECT SUM(SAL) FROM EMP;
--
SELECT ENAME, SUM(SAL) FROM EMP;
--
SELECT SUM(COMM) FROM EMP;
--
SELECT SUM(DISTINCT SAL),
SUM(ALL SAL),
SUM(SAL)
FROM EMP;
--
SELECT SUM(SAL), SUM(COMM) FROM EMP;
-- 개수
SELECT COUNT(*) FROM EMP;
SELECT COUNT(*) FROM EMP WHERE DEPTNO = 30;
--
SELECT COUNT(DISTINCT SAL),
COUNT(ALL SAL),
COUNT(SAL)
FROM EMP;
--최대값
SELECT MAX(SAL) FROM EMP;
SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 20;
SELECT MAX(HIREDATE) FROM EMP;
--최소값
SELECT MIN(SAL) FROM EMP;
SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 30;
SELECT MIN(HIREDATE) FROM EMP;
--평균
SELECT AVG(SAL) FROM EMP;
SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 10;
SELECT AVG(SAL) FROM EMP WHERE DEPTNO = 20;
SELECT AVG(DISTINCT SAL) FROM EMP WHERE DEPTNO = 20;
SELECT AVG(COMM) FROM EMP;
SELECT AVG(COMM) FROM EMP WHERE DEPTNO = 30;
* 중복을 제거한다고 해서 다른 사람들의 값을 무시하면 안됨 (따라서 DISTINCT를 사용하는건 말이 안됨)
7) 열로 묶어 출력 - GROUP BY
--그룹별
SELECT AVG(SAL), '10' AS DEPTNO FROM EMP WHERE DEPTNO = 10
UNION ALL
SELECT AVG(SAL),'20' AS DEPTNO FROM EMP WHERE DEPTNO = 30
UNION ALL
SELECT AVG(SAL),'30' AS DEPTNO FROM EMP WHERE DEPTNO = 30;
--
SELECT AVG(SAL), DEPTNO FROM EMP GROUP BY DEPTNO ;
SELECT AVG(SAL), DEPTNO FROM EMP GROUP BY DEPTNO ORDER BY DEPTNO;
--
--
SELECT DEPTNO, JOB, AVG(SAL)
FROM EMP
GROUP BY DEPTNO , JOB
ORDER BY DEPTNO , JOB;
--
SELECT DEPTNO, AVG(SAL) FROM EMP GROUP BY DEPTNO;
--
SELECT ENAME ,DEPTNO ,AVG(SAL) FROM EMP GROUP BY DEPTNO;
--ENAME은 문자이기 때문에 나오지 않음
8) GROUP BY 절에 조건을 주는 문법- HAVING (WHERE와는 다름 // 새로운 조건 부여) // 포인트
--GROUP BY HAVING 조건
SELECT DEPTNO , JOB, AVG(SAL) FROM EMP GROUP BY DEPTNO , JOB ORDER BY DEPTNO ,
JOB;
--
SELECT DEPTNO , JOB, AVG(SAL) FROM EMP
GROUP BY DEPTNO, JOB
HAVING AVG(SAL) >= 2000
ORDER BY DEPTNO , JOB;
--
SELECT DEPTNO, JOB, AVG(SAL) FROM EMP
WHERE AVG(SAL) >= 2000
GROUP BY DEPTNO, JOB
ORDER BY DEPTNO, JOB;
--
SELECT DEPTNO,JOB, AVG(SAL) FROM EMP
WHERE SAL <= 3000
GROUP BY DEPTNO, JOB
HAVING AVG(SAL) >= 2000
ORDER BY DEPTNO, JOB;
9) LISTAGG 함수 / WITHIN GROUP 함수
-- 리스트로 합치기
SELECT ENAME FROM EMP WHERE DEPTNO = 10;
--
SELECT DEPTNO , ENAME FROM EMP GROUP BY DEPTNO, ENAME;
--
SELECT DEPTNO ,
LISTAGG(ENAME,', ')
WITHIN GROUP(ORDER BY SAL DESC) AS ENAMES
FROM EMP
GROUP BY DEPTNO;
SELECT LISTAGG (ENAME , ', ')
WITHIN GROUP (ORDER BY ENAME ) AS ENAMES
FROM EMP;
* 핵심
- GROUP BY : 부서별로 볼 수 있는 기능 (연령)
'배운 책들 정리 > 오라클로 배우는 데이터베이스 입문' 카테고리의 다른 글
| 0208 오라클 DB 입문 - 제약 조건 및 사용자, 권한, 롤 관리 (0) | 2023.02.08 |
|---|---|
| 0207 오라클 DB 입문 - 데이터 정의어, 객체 종류, 제약 조건 (0) | 2023.02.07 |
| 0206 오라클 DB 입문 - 데이터 조작어, 트랜잭션 제어와 세션 (0) | 2023.02.06 |
| 0203 오라클 DB 입문 - 조인(JOIN), 서브쿼리, DML (0) | 2023.02.03 |
| 0201 - 목차 및 데이터베이스, SQL 쿼리 조회까지 (0) | 2023.02.01 |
