데이터베이스는 데이터(data)와 베이스(base) 합성어이며 DBMA는 Database Management System의 약자로 데이터베이스 관리시스템을 의미한다.
개체(entity) :
데이터베이스에서 데이터화하려는 사물, 개념의 정보 단위. 관계형 데이터베이스의 테이블(table) 개념과 대응되며 테이블은 릴레이션(relation)으로 표기하기도 한다.
속성(attribute) :
개체를 구성하는 데이터의 가장 작은 논리적 단위로서 데이터의 종류, 특성, 상태 등을 정의합니다.
관계형 데이터베이스의 열(column) 개념과 대응된다.
관계(relationship) :
개체와 개체 또는 속성간의 연관성을 나타내기 위해 사용. 관계형 데이터베이스에서는 테이블 간의 관계를 외래키(foreign key) 등으로 구현하여 사용된다.
DISTINCT : 열 중복 제거하기
SELECT DISTINCT DEPTNO
FROM EMP;
SELECT DISTINCT JOB, DEPTNO
FROM EMP;
별칭을 지정하는 방식 :
SAL*12+COMM ANNSAL
SAL*12+COMM "ANNSAL"
SAL*12+COMM AS ANNSAL
SAL*12+COMM AS "ANNSAL"
등가 비교 연산자 종류
연산자 사용법 의미
= A = B A 값이 B 값과 같을 경우 true 다를 경우 false
!= A != B A 값과 B 값이 다를 경우 true, 같은 경우 false
<> A <> B
^= A ^= B
논리 부정 연산자(NOT)
SELECT *
FROM EMP
WHERE SAL != 3000;
SELECT *
FROM EMP
WHERE SAL <> 3000;
SELECT *
FROM EMP
WHERE SAL != 3000;
SELECT *
FROM EMP
WHERE NOT SAL = 3000;
사용예1)
SELECT *
FROM EMP
WHERE NOT COMM IS NULL;
IN 연산자
SELECT *
FROM EMP
WHERE JOB = 'MANAGER'
OR JOB = 'SALESMAN'
OR JOB = 'CLERK';
SELECT *
FROM EMP
WHERE JOB IN ('MANAGER', 'SALESMAN', 'CLERK');
SELECT *
FROM EMP
WHERE JOB != 'MANAGER'
AND JOB <> 'SALESMAN'
AND JOB ^= 'CLERK';
SELECT *
FROM EMP
WHERE NOT JOB IN ('MANAGER', 'SALESMAN', 'CLERK');
BETWEEN A AND B 연산자
SELECT *
FROM EMP
WHERE SAL >= 2000 AND SAL <= 3000;
SELECT *
FROM EMP
WHERE SAL BETWEEN 2000 AND 3000;
SELECT *
FROM EMP
WHERE SAL NOT BETWEEN 2000 AND 3000;
LIKE 연산자와 와일드 카드
_ : 어떤 값이든 상관없이 한 개의 문자 데이터를 의미
% : 길이과 상관없이 (문자 없는 경우도 포함) 모든 문자 데이터를 의미
SELECT *
FROM EMP
WHERE ENAME LIKE 'S%';
SELECT *
FROM EMP
WHERE ENAME LIKE '_L%';
SELECT *
FROM EMP
WHERE ENAME LIKE '%AM%';
SELECT *
FROM EMP
WHERE ENAME NOT LIKE '%AM%';
IS NULL 연산자
SELECT *
FROM EMP
WHERE COMM = NULL; (X)
SELECT *
FROM EMP
WHERE COMM IS NULL; (O)
SELECT *
FROM EMP
WHERE COMM IS NOT NULL;
SELECT *
FROM EMP
WHERE MGR IS NOT NULL;
SELECT *
FROM EMP
WHERE SAL > NULL
AND COMM IS NULL;
SELECT *
FROM EMP
WHERE SAL > NULL
OR COMM IS NULL;
집합 연산자
UNION : 연결된 SELECT문의 결과 값을 합집합으로 묶어 준다. 결과 값의 중복은 제거된다.
UNION ALL : 연결된 SELECT문의 결과 값을 합집합으로 묶어준다. 중복된 결과 값도 제거 없이 모두 출력된다.
MINUS : 먼저 작성한 SELECT문의 결과 값에서 다음 SELECT문의 결과 값을 차집합 처리한다. 먼저 작성한 SELECT문의 결과 값 중 다음 SELECT문에 존재하지 않는 데이터만 출력된다.
INTERSECT : 먼저 작성한 SELECT문과 다음 SELECT문의 결과 값이 같은 데이터만 출력된다. 교집합과 같은 의미.
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 20;
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10;
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10
UNION ALL
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10;
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
MINUS
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10;
: MINUS는 차집합
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
INTERSECT
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO = 10;
: INTERSECT는 교집합
오라클 함수의 종류
오라클 함수는 함수를 제작한 주체를 기준으로 오라클에서 기본으로 제공하는 내장 함수(built-in function) 와 사용자가 필요에 의해 직접 정의한 사용자 정의 함수(user-defined function)로 나눈다.
대, 소문자를 바꿔 주는 UPPER, LOWER, INITCAP 함수 :
SELECT ENAME, UPPER(ENAME), LOWER(ENAME), INITCAP(ENAME)
FROM EMP;
SELECT *
FROM EMP
WHERE UPPER(ENAME) = UPPER('scott');
SELECT *
FROM EMP
WHERE UPPER(ENAME) LIKE UPPER('%scott%');
문자열 길이를 구하는 LENGTH함수
SELECT ENAME, LENGTH(ENAME)
FROM EMP
WHERE LENGTH(ENAME) > 5;
LENGTH 함수와 LENGTHB 함수:
LENGTHB 함수는 바이트수를 반환.
SELECT LENGTH('한글'), LENGTHB('한글')
from DUAL;
문자열 일부를 추출하는 SUBSTR 함수
SUBSTR(문자열 데이터, 시작위치, 추출길이)
SUBSTR(문자열 데이터, 시작위치)
SELECT JOB, SUBSTR(JOB, 1, 2), SUBSTR(JOB, 3, 2), SUBSTR(JOB, 5)
FROM EMP;
SELECT JOB,
SUBSTR(JOB, -LENGTH(JOB)),
SUBSTR(JOB, -LENGTH(JOB), 2),
SUBSTR(JOB, -3)
FROM EMP;
문자열 데이터 안에서 특정문자 위치를 찾는 INSTR 함수
문자열 데이터 안에 특정 문자나 문자열이 어디에 포함되어 있는지 알고자 할 때 사용.
SELECT INSTR('HELLO, ORACLE!', 'L') AS INSTR1,
INSTR('HELLO, ORACLE!', 'L', 5) AS INSTR2,
INSTR('HELLO, ORACLE!', 'L', 2, 2) AS INSTR3
FROM DUAL;
SELECT *
FROM EMP
WHERE INSTR(ENAME, 'S') > 0;
SELECT *
FROM EMP
WHERE ENAME LIKE '%S%';
* 확장자명 추출하기
SELECT INSTR('L201912291234.JPG', '.', -1),
SUBSTR('L201912291234.JPG', INSTR('L201912291234.JPG', '.', -1))
FROM DUAL;
SELECT SUBSTR('L201912291234.JPG', INSTR('L201912291234.JPG', '.', -1)) AS EXTENSION
FROM DUAL;
특정 문자를 다른 문자로 바꾸는 REPLACE 함수
특정 문자열 데이터에 포함된 문자를 다른 문자로 대체.
SELECT '010-1234-5678' AS REPLACE_BEFORE,
REPLACE('010-1234-5678', '-', ' ') AS REPLACE1,
REPLACE('010-1234-5678', '-') AS REPLACE2
FROM DUAL;
데이터의 빈 공간을 특정 문자로 채우는 LPAD, RPAD 함수
LPAD(Left padding), RPAD(Right Padding) 는 데이터와 자릿수를 지정한 후 데이터길이가 지정한 자릿수보다 작을 경우 나머지 공간을 특정 문자로 채우는 함수.
LPAD는 남은 빈 공간을 왼쪽에 채우고 RPAD는 오른쪽에 채운다.
빈 공간에 채울 문자를 지정하지 않으면 빈 공간의 자릿수만큼 공백문자로 채운다.
SELECT 'Oracle',
LPAD('Oracle', 10, '#') AS LPAD1,
RPAD('Oracle', 10, '*') AS RPAD1,
LPAD('Oracle', 10) AS LPAD2,
RPAD('Oracle', 10) AS RPAD2
FROM DUAL;
SELECT RPAD('970102-', 14, '*') AS RPAD_JMNO,
RPAD('010-1234-', 13, '*') AS RPAD_PHONE
FROM DUAL;
SELECT '790102-1696123' AS JUMINNO,
INSTR('790102-1696123', '-', -1) AS INSRT1,
RPAD(SUBSTR('790102-1696123', 1, INSTR('790102-1696123', '-') - 1) || '-', 14, '*') AS MASKING_JUMINNO
FROM DUAL;
두 문자열 데이터를 합치는 CONCAT 함수
SELECT CONCAT(EMPNO, ENAME) AS CONCAT1,
CONCAT(EMPNO, CONCAT(' : ', ENAME)) AS CONCAT2
FROM EMP;
SELECT EMPNO || ENAME,
EMPNO || ' : ' || ENAME
FROM EMP;
특정 문자를 지우는 TRIM, LTRIM, RTRIM 함수
보통 실무에서는 양쪽 끝의 공백을 제거할 때 사용.
로그인 시 아이디를 입력했을 때 사용자의 실수로 Spacebar 가 눌러져 공백이 입력되는 경우.
특정 위치에서 반올림하는 ROUND 함수
SELECT ROUND(1234.5678) AS ROUND,
ROUND(1234.5678, 0) AS ROUND0,
ROUND(1234.5678, 1) AS ROUND1,
ROUND(1234.5678, 2) AS ROUND2,
ROUND(1234.5678, -1) AS ROUND_MINUS1,
ROUND(1234.5678, -2) AS ROUND_MINUS2
FROM DUAL;
결과: 1235, 1235, 1234.6, 1234.57, 1230, 1200
반올림 위치 값이 0에서 양수로 올라가면 반올림 위치가 한 자리씩 더 낮은 소수점 자리를 향함.
음수는 자연수 쪽으로 한자리씩 위로 반올림함.
특정 위치에서 버리는 TRUNC 함수
지정된 자리에서 숫자를 버림 처리하는 함수.
SELECT TRUNC(1234.5678) AS TRUNC,
TRUNC(1234.5678, 0) AS TRUNC_0,
TRUNC(1234.5678, 1) AS TRUNC1,
TRUNC(1234.5678, 2) AS TRUNC2,
TRUNC(1234.5678, -1) AS TRUNC_MINUS1,
TRUNC(1234.5678, -2) AS TRUNC_MINUS2
FROM DUAL;
결과: 1234, 1234, 1234.5, 1234.56, 1230, 1200
지정한 숫자와 가까운 정수를 찾는 CEIL, FLOOR 함수
SELECT CEIL(3.14),
FLOOR(3.14),
CEIL(-3.14),
FLOOR(-3.14)
FROM DUAL;
결과: 4, 3, -3, -4
숫자를 나눈 나머지 값을 구하는 MOD 함수
SELECT MOD(15, 6),
MOD(10, 2),
MOD(11, 2)
FROM DUAL;
결과: 3, 0, 1
날짜데이터를 다루는 날짜 함수
오라클에서 제공하는 날짜 함수 중 가장 대표 함수는 SYSDATE 함수입니다.
SELECT SYSDATE AS NOW,
SYSDATE-1 AS YESTERDAY,
SYSDATE+1 AS TOMORROW
from DUAL;
몇 개월 이후 날짜를 구하는 ADD_MONTHS 함수
SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 3)
FROM DUAL;
SELECT EMPNO, ENAME, HIREDATE,
ADD_MONTHS(HIREDATE, 120)
FROM EMP;
SELECT EMPNO, ENAME, HIREDATE, ADD_MONTHS(HIREDATE, 460), SYSDATE
FROM EMP
WHERE ADD_MONTHS(HIREDATE, 460) < SYSDATE;
두 날짜 간의 개월 수 차이를 구하는 MONTHS_BETWEEN 함수
SELECT EMPNO, ENAME, HIREDATE, SYSDATE,
MONTHS_BETWEEN(HIREDATE, SYSDATE) AS MONTHS1,
MONTHS_BETWEEN(SYSDATE, HIREDATE) AS MONTHS2,
TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE)) AS MONTHS3,
TRUNC(MONTHS_BETWEEN(SYSDATE, HIREDATE) / 12) AS YEARS
FROM EMP;
돌아오는 요일, 달의 마지막 날짜를 구하는 NEXT_DAY, LAST_DAY 함수
SELECT SYSDATE,
NEXT_DAY(SYSDATE, '월요일'),
LAST_DAY(SYSDATE)
FROM DUAL;
결과:
2019/12/29 오후 8:21:35
2019/12/30 오후 8:21:35
2019/12/31 오후 8:21:35
날짜의 반올림, 버림을 하는 ROUND, TRUNC 함수
SELECT SYSDATE,
ROUND(SYSDATE, 'CC') AS FORMAT_CC,
ROUND(SYSDATE, 'YYYY') AS FORMAT_YYYY,
ROUND(SYSDATE, 'Q') AS FORMAT_Q,
ROUND(SYSDATE, 'DDD') AS FORMAT_DDD,
ROUND(SYSDATE, 'HH') AS FORMAT_HH
FROM DUAL;
SELECT SYSDATE,
TRUNC(SYSDATE, 'CC') AS FORMAT_CC,
TRUNC(SYSDATE, 'YYYY') AS FORMAT_YYYY,
TRUNC(SYSDATE, 'Q') AS FORMAT_Q,
TRUNC(SYSDATE, 'DDD') AS FORMAT_DDD,
TRUNC(SYSDATE, 'HH') AS FORMAT_HH
FROM DUAL;
날짜, 숫자 데이터를 문자 데이터로 변환하는 TO_CHAR 함수
SELECT TO_CHAR(SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS NOW1
FROM DUAL;
SELECT SYSDATE,
TO_CHAR(SYSDATE, 'MM') AS MM,
TO_CHAR(SYSDATE, 'MON') AS MON,
TO_CHAR(SYSDATE, 'MONTH') AS MONTH,
TO_CHAR(SYSDATE, 'DD') AS DD,
TO_CHAR(SYSDATE, 'DY') AS DY,
TO_CHAR(SYSDATE, 'DAY') AS DAY
FROM DUAL;
SELECT SYSDATE,
TO_CHAR(SYSDATE, 'HH24:MI:SS') AS HH24MISS,
TO_CHAR(SYSDATE, 'HH12:MI:SS AM') AS HHMISS_AM,
TO_CHAR(SYSDATE, 'HH:MI:SS P.M.') AS HHMISS_PM
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;
문자 데이터를 숫자 데이터로 변환하는 TO_NUMBER 함수
SELECT 1300 - '1500',
'1300' + 1500
FROM DUAL; (O)
SELECT '1,300' - '1,500'
FROM DUAL; (X)
SELECT TO_NUMBER('1,300', '999,999') - TO_NUMBER('1,500', '999,999')
FROM DUAL; (O)
문자 데이터를 날짜 데이터로 변환하는 TO_DATE 함수
SELECT TO_DATE('2018-07-14', 'YYYY-MM-DD') AS TODATE1,
TO_DATE('20180714', 'YYYY-MM-DD') AS TODATE2
FROM DUAL;
SELECT *
FROM EMP
WHERE HIREDATE > TO_DATE('19810601', 'YYYY-MM-DD');
NULL 처리 함수
NVL 함수의 기본 사용법
SELECT EMPNO, ENAME, SAL, COMM, SAL+COMM,
NVL(COMM, 0),
SAL+NVL(COMM, 0)
FROM EMP;
NVL2 함수의 기본 사용법
SELECT EMPNO, ENAME, SAL, COMM,
NVL2(COMM, 'O', 'X'),
NVL2(COMM, SAL*12+COMM, SAL*12) AS ANNSAL
FROM EMP;