지난번 “[SQL 개발자] SQL 기본 자격증 시험 요약”에 이어서 SQL 활용에 대해 요약을 해보았습니다.
아래 내용은 SQL 개발자 자격증에 대한 서적 중 [이기적 SQL 개발자]를 참고하여 요약을 하였습니다.
SQL 기본에서는 TABLE을 하나만 사용하여 개념을 배울 수 있었다면 활용에서는 테이블들을 조합하여 다양한 결과를 얻을 수 있는 방법을 학습 하게 됩니다.
1. JOIN(조인)
1.1 EQUI Join(등가 조인)
1) EQUI Join(등가 조인)
두 개의 Table 간에 일치하는 Column을 조인 한다.
조인의 가장 기본은 교집합을 만드는 것.
조인은 여러 개의 릴레이션을 사용해서 새로운 릴레이션을 만드는 과정.
SQL> SELECT EMPNO, ENAME, SAL, DEPTNO 2 FROM EMP; EMPNO ENAME SAL DEPTNO ------- -------- ------ ------- 7369 SMITH 800 20 7499 ALLEN 1600 30 7782 CLARK 2450 10 7788 SCOTT 3000 20 7839 KING 5000 10 7902 FORD 3000 20 7934 MILLER 1300 10
SQL> SELECT DEPTNO, DNAME, LOC 2 FROM DEPT; DEPTNO DNAME LOC -------- ------------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
EQUI 조인은 “=”을 사용해서 두 개의 테이블을 연결 한다.
SQL> SELECT EMPNO, ENAME, SAL, EMP.DEPTNO, DNAME, LOC 2 FROM EMP, DEPT 3 WHERE EMP.DEPTNO = DEPT.DEPTNO; EMPNO ENAME SAL DEPTNO DNAME LOC ------- -------- ------- -------- ------------- ---------- 7369 SMITH 800 20 RESEARCH DALLAS 7499 ALLEN 1600 30 SALES CHICAGO 7782 CLARK 2450 10 ACCOUNTING NEW YORK 7788 SCOTT 3000 20 RESEARCH DALLAS 7839 KING 5000 10 ACCOUNTING NEW YORK 7902 FORD 3000 20 RESEARCH DALLAS 7934 MILLER 1300 10 ACCOUNTING NEW YORK
2) INNER JOIN
EQUI Join 과 동일한 결과를 얻을 수 있으며 INNER JOIN은 Where절이 아닌 From 절에서 ON 문을 이용하여 Join 한다.
SELECT * FROM EMP INNER JOIN DEPT ON (EMP.DeptCd = DEPT.DeptCd);
3) Hash Join(해시 조인)
- 선행 테이블을 결정하고 선행 테이블에서 주어진 조건에 해당하는 행을 선택 한다.
- 해당 행이 선택되면 조인 키를 기준으로 해시 함수를 사용해서 해시 테이블을 메인 메모리(Main Memory)에 생성하고 후행 테이블에서 주어진 조건에 만족하는 행을 찾는다.
- 후행 테이블의 조인 키를 사용해서 해시 함수를 적용하여 해당 버킷을 검색 한다.
4) INTERSECT 연산
두 개의 테이블에서 교집합 측 공통된 column을 조회 한다. DEPT 테이블의 40은 제외된다.
SQL> SELECT DEPTNO FROM EMP 2 INTERSECT 3 SELECT DEPTNO FROM DEPT; DEPTNO ---------- 10 20 30
1.2 Non-EQUI JOIN (비등가 조인)
두 개의 테이블을 조인하는 경우 등가(“=”)를 사용하지 않고 비등가(“>”, “<”, “>=”, “<=”) 등을 사용 하여 일치하지 않는 값을 구하는 조인 방식.
1.3 OUTER JOIN
OUTER JOIN은 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN이 있다.
1) LEFT OUTER JOIN
LEFT OUTER JOIN은 왼쪽 테이블을 기준으로 해서 비교조건과 동일한 값을 오른쪽 테이블에서가져와 뒤에 붙여주는 형태의 작업 이다. 동일한 값이 없다면 NULL이 붙는다.
SQL> SELECT * 2 FROM DEPT LEFT OUTER JOINEMP 3 ONDEPT.DEPTNO = EMP.DEPTNO; DEPTNO DNAME EMPNO ENAME JOB SAL DEPTNO -------- ------------ ------- -------- ----------- ------- -------- 20 RESEARCH 7369 SMITH CLERK 800 20 30 SALES 7499 ALLEN SALESMAN 1600 30 10 ACCOUNTING 7782 CLARK MANAGER 2450 10 20 RESEARCH 7788 SCOTT ANALYST 3000 20 10 ACCOUNTING 7839 KING PRESIDENT 5000 10 20 RESEARCH 7902 FORD ANALYST 3000 20 10 ACCOUNTING 7934 MILLER CLERK 1300 10 40 OPERATIONS
2) RIGHT OUTER JOIN
RIGHT OUTER JOIN은 LEFT OUTER JOIN과 반대로 오른쪽 테이블을 기준으로 왼쪽 테이블에서 비교조건과 동일한 값을 가져와 붙여주는 형태의 작업이다. 동일한 값이 없다면 NULL이 붙는다.
SQL> SELECT * 2 FROM EMP RIGHT OUTER JOINDEPT 3 ON EMP.DEPTNO = DEPT.DEPTNO; EMPNO ENAME JOB SAL DEPTNO DEPTNO DNAME ------- --------- ----------- ------- --------- --------- ----------- 7369 SMITH CLERK 800 20 20 RESEARCH 7499 ALLEN SALESMAN 1600 30 30 SALES 7782 CLARK MANAGER 2450 10 10 ACCOUNTING 7788 SCOTT ANALYST 3000 20 20 RESEARCH 7839 KING PRESIDENT 5000 10 10 ACCOUNTING 7902 FORD ANALYST 3000 20 20 RESEARCH 7934 MILLER CLERK 1300 10 10 ACCOUNTING 40 OPERATIONS
3) FULL OUTER JOIN
FULL OUTER JOIN은 LEFT OUTER JOIN과 RIGHT OUTER JOIN을 모두 표현하는 것으로 왼쪽 테이블을 모두 나열하고 오른쪽 테이블도 모두 나열하여 비교조건이 없는 부분은 NULL로 처리한다.
SQL> SELECT * 2 FROM EMP FULL OUTER JOINDEPT 3 ON EMP.DEPTNO = DEPT.DEPTNO; EMPNO ENAME JOB SAL DEPTNO DEPTNO DNAME ------- --------- ----------- ------- --------- --------- ----------- 7369 SMITH CLERK 800 20 20 RESEARCH 7499 ALLEN SALESMAN 1600 30 30 SALES 7782 CLARK MANAGER 2450 10 7788 SCOTT ANALYST 3000 20 20 RESEARCH 7839 KING PRESIDENT 5000 10 7902 FORD ANALYST 3000 20 20 RESEARCH 7934 MILLER CLERK 1300 10 40 OPERATIONS
1.4 CROSS JOIN
- 조인 조건을 사용하지 않고 테이블을 하나로 조인 한다.
- 조인구가 없기 때문에 카테시안 곱이 발생 한다.
- 조인 결과의 행은 테이블 행을 곱한 값으로 5개 행과 6개 행 테이블은 30개의 결과 행이 된다.
- CORSS JOIN은 FROM 절에 “CROSS JOIN” 구를 사용하거나 “,”를 사용 하면 된다.
아래 쿼리는 동일 한 결과를 얻는다.
SELECT * FROM EMP, DEPT; -- WHERE 조건이 없는 것과 동일한 결과. SQL> SELECT * FROM EMP CROSS JOINDEPT; EMPNO ENAME JOB SAL DEPTNO DEPTNO DNAME ------ ---------- ----------- -------- --------- --------- ------------ 7369 SMITH CLERK 800 20 10 ACCOUNTING 7499 ALLEN SALESMAN 1600 30 10 ACCOUNTING 7782 CLARK MANAGER 2450 10 10 ACCOUNTING 7788 SCOTT ANALYST 3000 20 10 ACCOUNTING 7839 KING PRESIDENT 5000 10 10 ACCOUNTING 7902 FORD ANALYST 3000 20 10 ACCOUNTING 7934 MILLER CLERK 1300 10 10 ACCOUNTING 7369 SMITH CLERK 800 20 20 RESEARCH 7499 ALLEN SALESMAN 1600 30 20 RESEARCH 7782 CLARK MANAGER 2450 10 20 RESEARCH 7788 SCOTT ANALYST 3000 20 20 RESEARCH 7839 KING PRESIDENT 5000 10 20 RESEARCH 7902 FORD ANALYST 3000 20 20 RESEARCH 7934 MILLER CLERK 1300 10 20 RESEARCH 7369 SMITH CLERK 800 20 30 SALES 7499 ALLEN SALESMAN 1600 30 30 SALES 7782 CLARK MANAGER 2450 10 30 SALES 7788 SCOTT ANALYST 3000 20 30 SALES 7839 KING PRESIDENT 5000 10 30 SALES 7902 FORD ANALYST 3000 20 30 SALES 7934 MILLER CLERK 1300 10 30 SALES 7369 SMITH CLERK 800 20 40 OPERATIONS 7499 ALLEN SALESMAN 1600 30 40 OPERATIONS 7782 CLARK MANAGER 2450 10 40 OPERATIONS 7788 SCOTT ANALYST 3000 20 40 OPERATIONS 7839 KING PRESIDENT 5000 10 40 OPERATIONS 7902 FORD ANALYST 3000 20 40 OPERATIONS 7934 MILLER CLERK 1300 10 40 OPERATIONS
1.5 UNION을 사용한 합집함 구현
1) UNION
SELECT 결과들을 하나로 합치는 것이다.
SELECT 문의 COLUMN이 모두 동일 해야 UNION으로 합칠 수 있다.
UNION 연산은 중복된 SELECT 결과를 제거 한다.
UNION 연산은 SORT(정렬) 과정이 발생 한다.
2) UNION ALL
SELECT 결과들을 하나로 합치는 것은 UNION과 동일 하다.
UNION ALL은 중복된 결과를 제거하지 않고 정렬 과정도 발생하지 않는다.
1.6 MINUS (차집함)
먼저 조회한 SELECT의 결과에서 다음 SELECT의 결과에서 동일한 부분을 삭제 한다.
즉, 앞의 SELECT에는 있고 뒤의 SELECT에는 없는 집합을 조회 하는것.
- EXCEPT도 동일한 연산을 한다.
2. 계층형 조회 (Connect by)
- Oracle 데이터베이스에서 지원
- 계층형으로 데이터를 조회할 수 있다.
- START WITH구로 시작조건, CONNECT BY PRIOR 는 조인 조건이다.
- MAX(LEVEL)을 사용하여 최대 계층 수를 구할 수 있다.
SQL> SELECT MAX(LEVEL) 2 FROM EMPLOYEES 3 START WITH MANAGER_ID IS NULL 4 CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID; MAX(LEVEL) ---------- 4
계층관계 즉 누가 각 직원의 메니져 인가를 구해 보면 MANAGER_ID 값이 NULL인 행이 최 상단 레벨 값(1)을 갖는다.
SQL> SELECT LEVEL, employee_id, manager_id, first_name 2 FROM EMPLOYEES 3 START WITH MANAGER_ID IS NULL 4 CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID; LEVEL EMPLOYEE_ID MANAGER_ID FIRST_NAME ---------- ----------- ---------- ------------------------- 1 100 Steven 2 101 100 Neena 3 108 101 Nancy 4 109 108 Daniel 4 110 108 John 4 111 108 Ismael 4 112 108 Jose Manuel 4 113 108 Luis 3 200 101 Jennifer 3 203 101 Susan 3 204 101 Hermann
계층형 구조를 시각적으로 보기 위하여 LPAD 함수를 추가하여 들여쓰기를 해보면 좀더 잘자.
SELECT LEVEL, LPAD(' ', 4 + (LEVEL -1)) || employee_id AS "계층 구조", manager_id, CONNECT_BY_ISLEAF, first_name FROM EMPLOYEES START WITH MANAGER_ID IS NULL CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID;
Connect by 키워드
키워드
설명
LEVEL
검색 항목의 깊이. 즉, 계층구조에서 가장 상위 레빌이 1이 된다.
CONNECT_BY_ROOT
계층구조에서 가장 최상위 값을 표시
CONNECT_BY_ISLEAF
계층구조에서 가장 최하위를 표시
SYS_CONNECT_BY_PATH
계층구조의 전체 전개 경로를 표시
NOCYCLE
순환구조가 발생지점 까지만 전개
CONNECT_BY_ISCYCLE
순환구조 발생 지점을 표시
3. SUBQUERY (서브쿼리)
3.1 Main Query (메인 쿼리)와 Subquery (서브 쿼리)
원래 조회하려는 SELECT 문을 사용하는 쿼리를 메인 쿼리라고 한다.
SELECT EMPNO, EMPNM FROM EMP WHRE EMPNO = ‘0001’;
메인 쿼리에 추가로 SELECT 나, FROM 또는 WHERE에 추가되는 SELECT 는 서브쿼리라 한다. Subquery는 SELECT 절, FROM 절, WHERE 절 어디에든 추가가 가능하다.
SELECT EMPNO, EMPNM, (SELECT ...) FROM EMP, (SELECT ...) WHRE EMPNO = 1000 AND ... (SELECT ...);
3.2 단일 행 서브쿼리와 다중 행 서브쿼리
- 반환되는 행의 수가 몇개 인가로 단일 행 서브쿼리, 멀티(다중) 행 서브쿼리로 분류한다.
서브쿼리 종류
설명
단일 행 서브쿼리 (Single Row Subquery
반환 행이 하나 비교 연산자(=, <,<=, >, >=, <>)를 사용.
다중 행 서브쿼리 (Multi Row Subquery)
반환 행이 여러 개 IN, ANY, ALL, EXISTS를 사용
3.3 다중 행 서브쿼리
다중 행 비교 연산자
다중 행 연산
설명
IN (Subquery)
(Subquery)의 결과 중 하나만 동일 하면 참
ALL (Subquery)
(Subquery)의 결과가 모두 동일하면 참 < ALL : 최소값을 반환 > ALL : 최대값을 반환
ANY (Subquery)
(Subquery)의 결과 중 하나 이상이 동일 하면 참
EXISTS (Subquery)
(Subquery)의 결과가 하나라도 존재하면 참
1) IN
Subquery가 반환하는 여러 개의 행 중에서 하나만 동일 해도 참이 되는 연산.
SQL> SELECT EMPNO, ENAME, SAL, DEPTNO 2 FROM EMP 3 WHERE SAL IN(SELECT SAL 4 FROM EMP 5 WHERE DEPTNO < 30); EMPNO ENAME SAL DEPTNO ------- ----------- ---------- ---------- 7369 SMITH 800 20 7782 CLARK 2450 10 7902 FORD 3000 20 7788 SCOTT 3000 20 7839 KING 5000 10 7934 MILLER 1300 10
SQL> SELECT SAL 2 FROM EMP 3 WHERE DEPTNO < 30; SAL ---------- 800 2450 3000 5000 3000 1300
2) ALL
Subquery의 결과가 모두 동일 하면 참.
SQL> SELECT EMPNO, ENAME, SAL, DEPTNO 2 FROM EMP 3 WHERE DEPTNO <= ALL(10, 20); EMPNO ENAME SAL DEPTNO ---------- -------------------- ---------- ---------- 7782 CLARK 2450 10 7839 KING 5000 10 7934 MILLER 1300 10
SQL> SELECT EMPNO, ENAME, SAL, DEPTNO 2 FROM EMP 3 WHERE DEPTNO <= ALL(20, 20); EMPNO ENAME SAL DEPTNO ---------- -------------------- ---------- ---------- 7369 SMITH 800 20 7782 CLARK 2450 10 7788 SCOTT 3000 20 7839 KING 5000 10 7902 FORD 3000 20 7934 MILLER 1300 10
3) EXISTS
Subquery에 결과 값의 존재 유무를 확인.
SQL> SELECT EMPNO, ENAME, DNAME 2 FROM EMP, DEPT 3 WHERE EMP.DEPTNO = DEPT.DEPTNO 4 AND EXISTS(SELECT 1 5 FROM EMP 6 WHERE SAL > 2000); EMPNO ENAME DNAME ---------- -------------------- ----------- 7369 SMITH RESEARCH 7499 ALLEN SALES 7782 CLARK ACCOUNTING 7788 SCOTT RESEARCH 7839 KING ACCOUNTING 7902 FORD RESEARCH 7934 MILLER ACCOUNTING
SQL> SELECT 1, SAL 2 FROM EMP 3 WHERE SAL > 2000; 1 SAL ---------- ---------- 1 2450 1 3000 1 5000 1 3000
4) Scala Subquery (스칼라 서브쿼리)
- Scala Subquery는 반드시 하나의 column 만 조회하고 결과도 하나의 행만 반환하는 쿼리.
- 여러 행이 반환(결과 값이 여러 개) 되면 오류가 발생 한다.
SQL> SELECT EMPNO, ENAME, 2 (SELECT AVG(SAL) 3 FROM EMP 4 ) AS "평균 급여" 5 FROM EMP 6 WHERE EMPNO = 7902; EMPNO ENAME 평균 급여 ---------- -------------------- ---------- 7902 FORD 2450
5) 연관 (Correlated Subquery)
- Subquery 내에서 Main Query의 column을 사용하는 것.
SQL> SELECT EMPNO, ENAME, 2 (SELECT DNAME 3 FROM DEPT 4 WHERE DEPTNO = EMP.DEPTNO 5 ) AS DEPTNM 6 FROM EMP 7 WHERE EMPNO = 7902; EMPNO ENAME DEPTNM ---------- -------------------- --------- 7902 FORD RESEARCH
4. 그룹 함수(GROUP FUNCTION)
4.1 ROLLUP
- GROUP BY에 사용되는 Column을 이용하여 Subtotal을 생성 한다.
- GROUP BY 구에 Column이 두 개 이상이면 순서에 따라 결과가 달라 진다.
SQL> SELECT DECODE(DEPTNO, NULL, '전체 합계', DEPTNO) AS DEPTNO, SUM(SAL) 2 FROM EMP 3 GROUP BY ROLLUP(DEPTNO); DEPTNO SUM(SAL) ---------- ---------- 10 8750 20 6800 30 1600 전체 합계 17150
SQL> SELECT DECODE(DEPTNO, NULL, '전체 합계', DEPTNO) AS DEPTNO, JOB, SUM(SAL) 2 FROM EMP 3 GROUP BY ROLLUP(DEPTNO, JOB); DEPTNO JOB SUM(SAL) ---------- ------------------ ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 20 CLERK 800 20 ANALYST 6000 20 6800 30 SALESMAN 1600 30 1600 전체 합계 17150
ROLLUP에 JOB을 추가한 경우 JOB 컬럼에 null이 찍힌 이유는 동일한 DEPTNO에 대한 Subtotal값을 출력한 것이다.
4.2 GROUPING 함수
- ROLLUP, CUBE, GROUPING SETS에서 생성되는 합계 값을 구분하기 위해 만들어진 함수.
- 소계, 합계 등을 계산할 때 일반 ROW에는 0을 소계, 합계에는 1을 반환한다.
GROUPING(column)을 사용하면 바로 전에 소개한 [4.1 ROLLUP]에서 Subtotal이 NULL로 표시되었는데 GROUPING 함수를 사용하면 column을 기준으로 SUM에 대하여 1로 나타낸다.
SELECT WINDOW_FUNCTION(ARGUMENTS) OVER (PARTITION BY 컬럼 ORDR BY WINDOWING절) FROM 테이블명;
1) 윈도우 함수 구조
구조
설명
ARGUMENTS(인수)
윈도우 함수에 따라서 0~N개의 인수를 설정한다.
PARTITION BY
전체 집합을 기준에 의해 소그룹으로 나눈다
ORDER BY
어떤 항목에 대해서 정렬한다
WINDOWING
- 행 기준 범위를 정한다 - ROWS는 물리적 결과의 행 수이고 RANGE는 논리적인 값에 의한 범위이다.
2) WINDOWING
구조
설명
ROWS
부분집합인 윈도우 크기를 물리적 단위로 행의 집합을 지정한다.
RANGE
논리적 주소에 의해 행 집합을 지정한다.
BETWEEN~AND
윈도우의 시작과 끝 위치를 지정한다.
UNBOUNDED PRECEDING
윈도우 시작 위치가 첫 번째 행임을 의미한다
UNBOUNDED FOLLOWING
윈도우 마지막 위치가 마지막 행임을 의미한다.
CURRENT ROW
윈도우 시작 위치가 현재 행임을 의미한다. (데이터가 인출된 현재 행을 의미한다.)
SQL> SELECT DEPTNO, ENAME, SAL, 2 SUM(SAL) OVER (ORDER BY SAL 3 ROWS BETWEEN UNBOUNDED PRECEDING 4 AND UNBOUNDED FOLLOWING) TOTSAL 5 FROM EMP; DEPTNO ENAME SAL TOTSAL ---------- -------------------- ---------- ---------- 20 SMITH 800 17150 10 MILLER 1300 17150 30 ALLEN 1600 17150 10 CLARK 2450 17150 20 SCOTT 3000 17150 20 FORD 3000 17150 10 KING 5000 17150
SAL로 ORDER BY 하고 처음부터 끝까지 합한 결과를 TOTSAL로 조회 한다.
SQL> SELECT DEPTNO, ENAME, SAL, 2 SUM(SAL) OVER (ORDER BY SAL 3 ROWS BETWEEN UNBOUNDED PRECEDING 4 AND CURRENT ROW) TOTSAL 5 FROM EMP; DEPTNO ENAME SAL TOTSAL ---------- -------------------- ---------- ---------- 20 SMITH 800 800 10 MILLER 1300 2100 30 ALLEN 1600 3700 10 CLARK 2450 6150 20 SCOTT 3000 9150 20 FORD 3000 12150 10 KING 5000 17150
SAL로 ORDER BY 하고 처음부터 현재 행까지 합한 결과를 TOTSAL로 조회 한다.
5.2 순위 함수(RANK Function)
1) 순위 관련 윈도우 함수
순위 함수
설명
RANK
- 특정항목 및 파티션에 대해서 순위를 계산한다. - 동일한 순위는 동일한 값이 부여된다.
DENSE_RANK
- 동일한 순위를 하나의 건수로 계산한다.
ROW_NUMBER
- 동일한 순위에 대해서 고유의 순위를 부여한다.
- RANK : 동일한 순위는 동일한 값이 부여된다.
SQL> SELECT ENAME, JOB, SAL, 2 RANK() OVER (ORDER BY SAL DESC) ALL_RANK, 3 RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK 4 FROM EMP; ENAME JOB SAL ALL_RANK JOB_RANK -------------------- ------------------ ---------- ---------- ---------- KING PRESIDENT 5000 1 1 SCOTT ANALYST 3000 2 1 FORD ANALYST 3000 2 1 CLARK MANAGER 2450 4 1 ALLEN SALESMAN 1600 5 1 MILLER CLERK 1300 6 1 SMITH CLERK 800 7 2
- ALL_RANK : SAL을 내림차순으로 정렬하고 순위를 부여하였다.
- JOB_RANK : SAL을 내림차순으로 정렬하고 동일한 JOB끼리 다시 순위를 부여 한다.
ANALYST는 SAL이 동일하니 모두 1순위. CLERK는 1300이 1순위, 800은 2순위
- DENSE_RANK : 동일한 순위를 하나의 건수로 계산한다.
SQL> SELECT ENAME, JOB, SAL, 2 RANK() OVER (ORDER BY SAL DESC) ALL_RANK, 3 DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK 4 FROM EMP; ENAME JOB SAL ALL_RANK DENSE_RANK -------------------- ------------------ ---------- ---------- ---------- KING PRESIDENT 5000 1 1 SCOTT ANALYST 3000 2 2 FORD ANALYST 3000 2 2 CLARK MANAGER 2450 4 3 ALLEN SALESMAN 1600 5 4 MILLER CLERK 1300 6 5 SMITH CLERK 800 7 6
- ROW_NUMBER : 동일한 순위에 대해서 고유의 순위를 부여한다.
SQL> SELECT ENAME, JOB, SAL, 2 RANK() OVER (ORDER BY SAL DESC) ALL_RANK, 3 ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUM 4 FROM EMP; ENAME JOB SAL ALL_RANK ROW_NUM ------------ ------------ -------- ---------- ---------- KING PRESIDENT 5000 1 1 SCOTT ANALYST 3000 2 2 FORD ANALYST 3000 2 3 CLARK MANAGER 2450 4 4 ALLEN SALESMAN 1600 5 5 MILLER CLERK 1300 6 6 SMITH CLERK 800 7 7
5.3 집계함수(AGGREGATE FUNCTION)
집계 함수
설명
SUM
파티션 별로 합계를 계산한다.
AVG
파티션 별로 평균을 계산한다.
COUNT
파티션 별로 행 수를 계산한다.
MAX와 MIN
파티션 별로 최대값과 최소값을 계산한다.
SQL> SELECT ENAME, JOB, SAL, 2 SUM(SAL) OVER (PARTITION BY JOB) SUM_JOB 3 FROM EMP; ENAME JOB SAL SUM_JOB -------------------- ------------------ ---------- ---------- SCOTT ANALYST 3000 6000 FORD ANALYST 3000 6000 MILLER CLERK 1300 2100 SMITH CLERK 800 2100 CLARK MANAGER 2450 2450 KING PRESIDENT 5000 5000 ALLEN SALESMAN 1600 1600
같은 JOB을 가진 직원들의 급여를 합한 값을 보여준다.
5.4 행 순서 관련 함수
- 행 순서 관련 함수는 상위 행 값을 하위에 출력하거나 하위 행 값을 상위 행에 출력할 수 있다.
- 특정 위치의 행을 출력할 수 있다.
행 순서
설명
FIRST_VALUE
- 파티션에서 가장 처음에 나오는 값을 구한다. - MIN 함수를 사용해서 같은 결과를 구할 수 있다.
LAST_VALUE
- 파티션에서 가장 나중에 나오는 값을 구한다.
LAG
- 이전 행을 가지고 온다.
LEAD
- 윈도우에서 특정 위치의 행을 가지고 온다. - 기본값은 1이다.
- FIRST_VALUE
SQL> SELECT DEPTNO, ENAME, SAL, 2 FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO 3 ORDER BY SAL DESC 4 ROWS BETWEEN UNBOUNDED PRECEDING 5 AND UNBOUNDED FOLLOWING) AS ENAME_A 6 FROM EMP; DEPTNO ENAME SAL ENAME_A ---------- -------------------- ---------- ------------------ 10 KING 5000 KING 10 CLARK 2450 KING 10 MILLER 1300 KING 20 SCOTT 3000 SCOTT 20 FORD 3000 SCOTT 20 SMITH 800 SCOTT 30 ALLEN 1600 ALLEN
부서로 파티션을 나누고 대상은 처음부터 마지막 행(BETWEEN...AND...)까지 선택하여, 급여가 가장 많은 직원으로 ORDER BY하여 처음에 나온 이름(제일 많은 급여를 받는)을 가져와 NAME_A에 출력 한다.
- LAST_VALUE
SQL> SELECT DEPTNO, ENAME, SAL, 2 LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO 3 ORDER BY SAL DESC 4 ROWS BETWEEN UNBOUNDED PRECEDING 5 AND UNBOUNDED FOLLOWING) AS ENAME_A 6 FROM EMP; DEPTNO ENAME SAL ENAME_A ---------- -------------------- ---------- -------------------- 10 KING 5000 MILLER 10 CLARK 2450 MILLER 10 MILLER 1300 MILLER 20 SCOTT 3000 SMITH 20 FORD 3000 SMITH 20 SMITH 800 SMITH 30 ALLEN 1600 ALLEN
부서로 파티션을 나누고 대상은 처음부터 마지막 행(BETWEEN...AND...)까지 선택하여, 급여가 가장 많은 직원으로 ORDER BY하여 마지막에 나온 이름(제일 적은 급여를 받는)을 가져와 NAME_A에 출력 한다.
- LAG
SQL> SELECT DEPTNO, ENAME, SAL, 2 LAG(SAL) OVER (PARTITION BY DEPTNO 3 ORDER BY SAL DESC) AS LAG_SAL 4 FROM EMP; DEPTNO ENAME SAL LAG_SAL ---------- -------------------- ---------- ---------- 10 KING 5000 10 CLARK 2450 5000 10 MILLER 1300 2450 20 SCOTT 3000 20 FORD 3000 3000 20 SMITH 800 3000 30 ALLEN 1600
Lag 함수는 이전 행의 값을 가지고 오는 함수로 바로 이전 행의 SAL 값을 출력 하며 DEPTNO로 파티션을 나누어서 SCOTT의 경우는 자신이 최초의 값으로 이전 값을 가져올 수 없다. PARTITION을 제외하여 이전 값을 가져오도록 해보자.
SQL> SELECT DEPTNO, ENAME, SAL, 2 LAG(SAL) OVER ( 3 ORDER BY SAL DESC) AS LAG_SAL 4 FROM EMP; DEPTNO ENAME SAL LAG_SAL ---------- -------------------- ---------- ---------- 10 KING 5000 20 SCOTT 3000 5000 20 FORD 3000 3000 10 CLARK 2450 3000 30 ALLEN 1600 2450 10 MILLER 1300 1600 20 SMITH 800 1300
파티션 구분 없이 SAL로 정렬하여 KING을 제외하고 모두 이전 값을 가지고 온다.
- LEAD
SQL> SELECT DEPTNO, ENAME, SAL, 2 LEAD(SAL, 3) OVER (ORDER BY SAL DESC) AS LEAD_SAL 3 FROM EMP; DEPTNO ENAME SAL LEAD_SAL ---------- -------------------- ---------- ---------- 10 KING 5000 2450 20 SCOTT 3000 1600 20 FORD 3000 1300 10 CLARK 2450 800 30 ALLEN 1600 10 MILLER 1300 20 SMITH 800
LEAD는 현재 행에서 몇 번째 아래에 있는 값을 가지고 오는 것으로 여기서는 3번행 아래에 있는 값을 가지고 오도록 하였다. ALLEN, MILLER, SMITH는 3번째 아래에는 아무것도 없으니 값을 가지고 오지 못한다.
5.5 비율 관련 함수
비율 함수
설명
CUME_DIST
- 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회한다. - 누적 분포상에 위치를 0~1사이의 값을 가진다.
PERCENT_RANK
파티션에서 제일 먼저 나온 것을 0으로 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서 별 백분율을 조회한다.
NTILE(ARGUMENT)
파티션 별로 전체 건수를 ARGUMENT 값으로 N등분한 결과를 조회한다.
RATIO_TO_REPORT
파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼 값의 백분율을 소수점까지 조회한다.
- CUME_DIST
SQL> SELECT DEPTNO, ENAME, SAL, 2 CUME_DIST() OVER (PARTITION BY DEPTNO 3 ORDER BY SAL) AS CUME_SAL 4 FROM EMP; DEPTNO ENAME SAL CUME_SAL ---------- -------------------- ---------- ---------- 10 MILLER 1300 .333333333 10 CLARK 2450 .666666667 10 KING 5000 1 20 SMITH 800 .333333333 20 SCOTT 3000 1 20 FORD 3000 1 30 ALLEN 1600 1
DEPTNO로 파티션을 나누고 자신보다 작거나 같은 급여를 받는 비율을 구하는 것으로 MILLER는 DEPTNO 10에서 자신보다 급여가 같거나 작은 사람은 혼자이고 비율은 33%, CLARK는 자신보다 급여가 같거나 작은 사람이 3명 중 2명으로 66%가 된다.
- PERCENT_RANK
SQL> SELECT DEPTNO, ENAME, SAL, 2 PERCENT_RANK() OVER (PARTITION BY DEPTNO 3 ORDER BY SAL DESC) AS PER_SAL 4 FROM EMP; DEPTNO ENAME SAL PER_SAL ---------- -------------------- ---------- ---------- 10 KING 5000 0 10 CLARK 2450 .5 10 MILLER 1300 1 20 SCOTT 3000 0 20 FORD 3000 0 20 SMITH 800 1 30 ALLEN 1600 0
PERCENT_RANK 함수는 파티션에서 등수의 퍼센트를 구하는 것이다. 1등은 0, 꼴등은 1로 설정을 하며 DEPTNO가 20인 경우 SCOTT, FORD는 급여 등수가 동일 하므로 둘 다 0을 갖는다.
- NTILE
SQL> SELECT DEPTNO, ENAME, SAL, 2 NTILE(3) OVER ( 3 ORDER BY SAL DESC) AS PRE_SAL 4 FROM EMP; DEPTNO ENAME SAL PRE_SAL ---------- -------------------- ---------- ---------- 10 KING 5000 1 20 SCOTT 3000 1 20 FORD 3000 1 10 CLARK 2450 2 30 ALLEN 1600 2 10 MILLER 1300 3 20 SMITH 800 3
NTILE(3)은 급여가 높은 순으로 1~3등분으로 분할한다. 급여가 가장 높은 등급에 속하면 1, 가장 낮은 등급에 속하면 3이 된다.
- RATIO_TO_REPORT
SQL> SELECT DEPTNO, ENAME, SAL, 2 RATIO_TO_REPORT(SAL) OVER () AS RATIO_SAL, 3 SUM(SAL) OVER() AS SUM_SAL, 4 SAL/SUM(SAL) OVER() AS SAL_PER 5 FROM EMP; DEPTNO ENAME SAL RATIO_SAL SUM_SAL SAL_PER -------- --------- ------ ---------- ---------- ---------- 20 SMITH 800 .04664723 17150 .04664723 30 ALLEN 1600 .093294461 17150 .093294461 10 CLARK 2450 .142857143 17150 .142857143 20 SCOTT 3000 .174927114 17150 .174927114 10 KING 5000 .29154519 17150 .29154519 20 FORD 3000 .174927114 17150 .174927114 10 MILLER 1300 .075801749 17150 .075801749
RATIO_TO_REPORT는 해당 컬럼의 총 합계에서 백분률을 구하는 것으로 실제 SUM을 구하고 SAL/SUM(SAL)을 사용한 방법과 비교를 해 보았다.
6. 테이블 파티션(Table Partition)
6.1 Partition 기능
- 대용량의 테이블 또는 인덱스 데이터를 파티션(Partition) 단위로 나누어 저장하는 것
지난번 “[SQL 개발자] 데이터 모델링의 이해 자격증 시험 요약”에 이러 SQL 기본에 대해 요약을 해보았습니다.
아래 내용은 SQL 개발자 자격증에 대한 서적 중 [이기적 SQL 개발자]를 참고하여 요약을 하였습니다.
1. 관계형 데이터베이스(Relation Database)
1.1 데이터베이스의 종류
데이터를 어떠한 형태의 자료구조로 사용하느냐에 따라 분류
데이터베이스
설명
계층형
- 트리(Tree) 구조로 데이터를 저장하고 관리 - 1:N 관계 형성
네트워크형
- 오너(Owner)와 멤버(Member) 형태로 데이터를 저장 - 1:N, M:N 표현도 가능 하다.
관계형
- 릴레이션에 데이터를 저장하고 관리 한다. - 집합연산과 관계연산이 가능 하다.
1) 집합 연산
집합 연산
설명
합집합(Union)
- 두 개의 릴레이션을 하나로 합치는것 - 중복된 행(튜플)은 한 번만 조회된다.
차집합(Difference)
본래 릴레이션에는 존재하고 다른 릴레이션에는 존재하지 않는것 조회
교집합(Intersection)
두 개의 릴레이션 간에 공통된 것을 조회
곱집합(
각 릴레이션에 존재하는 모든 데이터를 조합하여 연산
2) 관계 연산
관계 연산
설명
선택 연산(Selection)
릴레이션에서 조건에 맞는 행(튜플)만을 조회
투영 연산(Projection)
릴레이션에서 조건에 맞는 속성만을 조회
결합 연산(Join)
여러 릴레이션의 공통된 속성을 사용해서 새로운 릴레이션을 만들어 낸다.
나누기 연산(Division)
기준 릴레이션에서 나누는 릴레이션이 가지고 있는 속성과 동일한 값을 가지는 행(튜플)을 추출하고 나누는 릴레이션의 속성을 삭제한 후 중복된 행을 제거하는 연산
3) 테이블(Table)의 구조
구성 요소
설명
기본키 (Primary key)
하나의 테이블에서 유일성(Unique)과 최소성, Not Null을 만족하면서 해당 테이블을 대표하는 것
행 (Row)
하나의 테이블에 저장되는 값으로 튜플(Tuple)이라고도 한다.
칼럼 (Column)
어떤 데이터를 저장하기 위한 필드(Field)로 속성이라고도 한다.
외래키 (Foreign key)
- 다른 테이블의 기본키를 참조(조인)하는 칼럼 - 관계 연산 중에서 결합 연산(조인:Join)을 하기 위해서 사용
2. SQL(Structured Query Language) 종류
2.1 SQL(Structured Query Language) 종류
관계형 데이터베이스의 구조를 정의, 데이터 조작, 데이터 제어등을 할 수 있는 절차형 언어.
종류
설명
DDL : 데이터 정의어 (Data Definition Language)
관계형 데이터베이스의 구조를 정의하는 언어 (CREATE, ALTER, DROP, RENAME, TRUNCATE)
DML : 데이터 조작어 (Data Manipulation Language)
테이블에 저장된 데이터를 조작(입력,수정,삭제,조회) 한다. (SELECT, INSERT, UPDATE, DELETE)
DCL : 데이터 제어어 (Data Control Language)
데이터베이스의 접근 권한을 부여, 제거하는 언어 (GRANT, REVOKE)
TCL : 트랜잭션 제어어 (Transaction Control Language)
DML문에 의한 변경사항을 관리(트랜잭션 제어) (COMMIT, ROLLBACK, SAVEPOINT)
2.2 트랜잭션의 특성
특성
설명
원자성(Atomicity)
트랜잭션에 포함된 명령들은 모두 수행되거나, 모두 수행 안되어야 한다. (All or Nothing)
일관성(Consistancy)
- 트랜잭션 실행 결과로 테이터베이스의 상태가 모순되지 않아야 한다. - 트랜잭션 실행 후에도 일관성이 유지 되어야 한다.
고립성(Isolation)
- 트랜잭션은 다른 트랜잭션과 독립적으로 실행되어야 한다. - 부분적인 실행 결과를 다른 트랜잭션이 볼 수 없다.
지속성(Durability)
트랜잭션의 결과는 반드시 데이터베이스에 반영되어야 한다.
2.3 sql 실행순서
순서
설명
파싱(Parsing)
- SQL문의 문법 확인과 SQL 서버가 이해할 수 있는 단위들로 분해. - 분해, 분석된 SQL문을 Library Cache에 저장
실행(Execution)
Optimizer가 수립한 실행 계획에 따라 SQL 실행
추출(Fetch), 인출
실행 결과의 데이터를 읽어서 전송한다.
3. DDL(Data Definition Language): 관계형 데이터베이스의 구조를 정의하는 언어.
Table 관련 SQL 문
1) Table 생성 : Create Table [Table 명] ( Column 정보, ...); 2) Table 변경 : Alter Table - Table 명 변경 - Column 변경 3) Table 삭제 : Drop Table 4) View 생성 : Create View 5) View 삭제 : Drop View
Column 관련 SQL 문
1) Column 추가 : Alter Table ~ Add 2) Column 명 변경 : Alter Table ~ Rename Column ~ To 3) Column Type 변경 : Alter Table ~ Modify 4) Column 삭제 : Alter Table ~ Drop Column
3.1 Table 생성
Table 생성문의 구조
Create Table 문
설명
Create Table
Create Table [Table 명] ( Column 설정, );
Column 정보
- Column 명 - Data Type - 기본 값(Default)
Data Type
- Char : 고정된 크기의 문자 데이터 - Varchar2 : 가변길이의 문자열 데이터 - Number : 숫자 데이터 - Date : 날짜 데이터
ALTER TABLE [table 명] RENAME COLUMN [old column 명] TO [new Column 명];
ALTER TABLE emp RENAME COLUMN age TO na2;
4) Column 삭제
ALTER TABLE [table 명] DROP COLUMN [삭제할 column 명];
ALTER TABLE emp DROP COLUMN na2;
3.5 View 생성
View는 Table 전체를 보여주기 싫거나 여러 개의 Table에서 필요한 column 정보만 추출 하여 사용할 때 생성하는 가상의 Table 이다.
CREATEVIEW [View 명] AS SELECT * FROM [table 명];
CREATE VIEW vemp AS SELECT * FROM emp;
3.6 View 삭제
DROPVIEW [View 명];
DROP VIEW vemp;
3.6 View의 특징
- 뷰가 참조한 테이블의 내용이 변경 되면, 뷰도 자동적으로 변경 된다. - 민감한 column은 공개하지 않아도 되므로 보안성을 향상 시킨다. - 뷰는 가상의 테이블로 원본 Table에 영향을 받으므로 입력, 수정, 삭제에는 제약이 있다. - 뷰는 Table로 생성된 것이 아니므로 Alter문을 사용하여 변경할 수 없다. - 뷰의 제거는 Drop문을 사용하여 삭제한다. 즉, 생성과 삭제만 가능 하다.
4. DML(Data Manipulation Language) : 데이터 조작어
DDL에서 CREATE문으로 생성한 Table에 실제로 데이터를 입력하고 수정, 삭제, 조회 하는 명령어
1. Insert 문 : 테이블에 신규 데이트를 추가 한다. 2. Select 문 : 테이블에 들어있는 데이터를 조회 한다. 3. Update 문 : 테이블에 들어있는 데이터를 수정 한다. 4. Delete 문 : 테이블에 들어있는 데이터를 삭제 한다.
4.1 Insert 문
INSERT INTO [table 명](column1, column2, ...) VALUES(expression1, expression2, ...);
INSERT INTO DEPT(deptno, deptname) VALUES(‘0001’, ‘인사과’);
INSERT INTO DEPT(deptno, deptname) VALUES(‘0002’, ‘총무과’);
4.2 Select 문
SELECT * FROM [table 명]; SELECT column1, column2, ... FROM [table 명];
SELECT * FROM dept;
SELECT deptno, deptname FROM dept;
4.3 Update 문
UPDATE [table 명] SET column1 = expression1 , column2 = expression2 ... WHERE column1 = expression;
UPDATE dept
SET deptname = ‘기획실’
WHERE deptno = ‘0001’;
4.4 Delete 문
DELETE FROM [table 명] WHERE column1 = expression1 column2 = expression2 ...;
DELETE FROM dept
WHERE deptno = ‘0002’;
5. WHERE 문
프로그래밍 언어의 IF 문과 같은 조건 절에 해당하는 기능을 한다.
데이터를 조회, 수정, 삭제 하기 위한 선별 조건으로 사용된다.
5.1 연산자
- 비교 연산자
비교 연산자
설명
= (A = B)
A가 B와 같은 것 조회
< (A < B)
A가 B보다 작은 것 조회
<= (A <= B)
A가 B보다 작거나 같은 것 조회
> (A > B)
A가 B보다 큰 것 조회
>= (A >=B)
A가 B보다 크거나 같은 것 조회
- 부정 비교 연산자
부정 비교 연산자
설명
!= (A != B)
A와 B가 같지 않은 것 조회
^= (A ^= B)
A와 B가 같지 않은 것 조회
<> (A <> B)
A와 B가 같지 않은 것 조회
NOT [column 명] =(NOT A = B)
A와 B가 같지 않은 것 조회
NOT [column 명] > (NOT A > B)
A가 B 보다 크지 않은 것 조회
- 논리 연산자
논리 연산자
설명
AND (BL1 AND BL2)
조건 B1과 조건 B2가 모두 참이어야 true
OR B1 OR B2)
B1, B2 조건중 하나만 참이어도 true
NOT (NOT B1)
B1 조건이 참이면 false, 거짓이면 true
- SQL 연산자
SQL 연산자
설명
LIKE ‘%문자열%’ LIKE ‘_문자열_’
문자열을 포함하는 ‘%’ 위치의 모든 값 조회 문자열을 포함하고 ‘_’ 위치의 단일 문자 조회
BETWEEN A AND B
A와 B 사이의 값 조회
IN (list)
list와 일치하는 값(OR 조건) 조회
A IS NULL
A가 NULL 값 조회
- 부정 SQL 연산자
부정 SQL 연산자
설명
NOT BETWEEN A AND B
A와 B 사이에 해당하지 않는 값
NOT IN(list)
list와 일치하지 않는 값 조회
A IS NOT NULL
A가 NULL이 아닌 값 조회
5.2 LIKE 문
와일드 카드(%, _)를 이용하여 조회를 한다.
와일드 카드
설명
%
- 모든 것을 조회 한다. - ‘카%’ : ‘카’로 시작하는 모든 데이터 조회 - ‘%카’ : ‘카’로 끝나는 모든 데이터 조회
_(underscore)
- 한 개의 단일 문자를 대체 - ‘카_코딩’ : ‘_’에 한글자만 다른 모든 것 조회
5.3 BETWEEN AND 문
BETWEEN A AND B : A와 B 사이의 범위에 포함되는 값 조회
5.4 IN 문
IN (list) : list에 동일 값이 있으면 참인 조건.
5.5 NULL 관련 함수
IS NULL : Null 값 조회
IS NOT NULL : Null 아닌 값 조회
NULL 함수
설명
NVL(A, V1)
A가 NULL이면 V1 출력
NVL2(A, V1, V2)
A가 NULL이 아니면 V!, NULL 이면 V2 출력
NULLIF(A,exp1, exp2)
exp1과 exp2가 같으면 NULL, 다르면 exp1 출력
COALESCE(exp1, exp2, exp3, ...)
exp1부터 NULL이 아닌 첫번 째 값 출력
6. GROUP 연산
6.1 GROUP BY 문
Column에 동일한 값을 가진 행을 그룹화하여 합계, 평균, 최대값, 최소값 등을 계산 한다.
5 SELECT JOB_ID, SUM(SALARY) 1 FROM EMPLOYEES 2 WHERE SALARY > 5000 3 GROUP BY JOB_ID 4 HAVING SUM(SALARY) > 10000 6 ORDER BY SALARY;
7. 명시적(Explicit) 형변환과 암시적(Implicit) 형변환
숫자와 문자열의 비교, 문자열과 날짜의 비교 등 데이터타입이 서로 같지않을 때 사용한다.
두 개의 데이터의 데이터 타입이 일치하도록 변환하는 것이다.
7.1 명시적(Explicit) 형변환
개발자가 SQL을 사용할 때 직접 형변환 함수를 사용해서 변환 한다.
[형변환 함수]
형변환 함수
설명
TO_NUMBER(문자열)
문자열을 숫자로 변환한다.
TO_CHAR(숫자, 날짜,[ FORMAT])
숫자 혹은 문자를 지정된 FORMAT문자로 변환한다.
TO_DATE(문자열, FORMAT)
문자열을 지정된 FORMAT의 날짜형으로 변환한다.
7.2 암시적(Implicit) 형변환
개발자가 형변환을 하지 않은 경우 데이터베이스 관리시스템이 자동으로 형변환되는 것을 의미한다.
8. 내장형 함수
모든 데이터베이스 제품에는 제조사별로 조금씩 다르지만 비슷한 함수가 준비되어 사용자가 직접 구현하지 않고도 사용 가능하도록 되어 있다.
내장 함수는 형변환 함수, 문자열 함수, 숫자형 함수, 날짜형 함수 등이 있다.
8.1 문자열 함수
문자열 함수
설명
ASCII(문자)
문자 또는 숫자를 입력 하여 아스키코드 값으로 반환
CAHR(아스키 코드값)
아스키 코드값을 문자로 반환
SUBSTR(문자열, m, n)
컬럼 또는 문자열, m번 위치부터, n개를 골라낸다
CONCAT(문자열1,문자열2)
문자열1과 문자열2를 결합한다. ‘||’ 또는 ‘+’
LOWER(문자열)
영문자를 소문자로 변환
UPPER(문자열)
영문자를 대문자로 변환
LENGTH(문자열), LEN(문자열)
문자열의 길이를 반환
LTRIM(문자열, 제거할 문자)
- 왼쪽에 있는 지정된 문자를 제거 - 제거할 문자를 생략 하면 공백 제거
RTRIM(문자열, 제거할 문자)
- 오른쪽에 있는 지정된 문자를 제거 - 제거할 문자를 생략 하면 공백 제거
TRIM(문자열, 제거할 문자)
- 왼쪽 및 오른쪽에 있는 지정된 문자를 제거 - 제거할 문자를 생략 하면 양쪽 공백 제거
8.2 숫자형 함수
숫자형 함수
설명
ABS(숫자)
숫자의 절대값을 돌려준다
SING(숫자)
숫자가 양수인지. 음수인지, 0인지를 구별한다
MOD(숫자1, 숫자2)
- 숫자1을 숫자2로 나누어 나머지값을 리턴한다. - % 연산자를 사용해도 된다.
CEIL/CEILING(숫자)
숫자보다 크거나 같은 최소 정수를 리턴
FLOOR(숫자)
숫자보다 작거나 같은 최대 정수를 리턴
ROUND(숫자, m)
- 숫자를 소수점 m자리에서 반올림하여 리턴 - m을 생략하면 기본값은 0이다.
TRUNC(숫자, m)
- 숫자를 소수점 m자리에서 잘라서 버린다. - m을 생략하면 기본값은 0이다.
9. DECODE 문과 CASE 문
9.1 DECODE
프로그램 언어의 IF문과 동일한 기능으로 생각하면 된다.
두 개의 값을 비교하여 같으면 참을 다르면 거짓을 실행한다.
SQL> SELECT DECODE(EMPLOYEE_ID, 100, 'BOSS', 'WORKER') 2 FROM EMPLOYEES; DECODE(EMPLO ------------ BOSS WORKER WORKER WORKER WORKER WORKER WORKER WORKER
EMPLOYEE_ID가 100이면 BOSS, 아니면 WORKER를 출력한다.
9.2 CASE
프로그래밍 언어의 IF~THEN ~ELSE-END 조건문과 같이 사용된다.
CASE [expression] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE result END
SQL> SELECT 2 CASE 3 WHEN SALARY > 10000 THEN 'GOOD' 4 WHEN SALARY > 5000 THEN 'S0S0' 5 ELSE 'CHEAR UP' 6 END 7 FROM EMPLOYEES; CASEWHENSALARY>1 ---------------- GOOD GOOD GOOD S0S0 S0S0 CHEAR UP CHEAR UP
10. ROWNUM과 ROWID
Oracle이라는 특정 데이터베이스에 국한된 설명이지만 다른 데이터베이스에도 비슷한 기능이 있는 경우가 있다. (SQL Server : TOP(숫자), MySQL : LIMIT 숫자 등)
10.1 ROWNUM
- SELECT문 결과에 대해서 논리적인 일렬번호를 부여한다.(첫번째 검색된놈, 두번째 검색된놈...)
- 조회되는 행 수를 제한할 때 많이 사용된다. (ROWNUM <= 10 : 10개만 검색)
- 만약 ROWNUM을 사용해서 페이지 단위 출력을 하기 위해서는 인라인 뷰(Inline View)를 사용해야 한다.
- 검색 결과에 무조건 1,2,3,4... 번호를 부여하기 때문에 다음 페이지 번호도 1, 2, 3,...이 부여되고 해결책으로는 (1,2,3...마지막) 번호를 가지고 있는 검색 결과에서 다시 조회 하는 방법이 인라인 뷰 이다.
SQL> SELECT * 2 FROM (SELECT ROWNUM NUM, EMPLOYEE_ID, FIRST_NAME 3 FROM EMPLOYEES) 4 WHERE NUM BETWEEN 7 AND 10; NUM EMPLOYEE_ID FIRST_NAME ---------- ----------- ------------------------------- 7 167 Amit 8 172 Elizabeth 9 192 Sarah 10 151 David
10.2 ROWID
- ORACLE 데이터베이스 내에서 자동으로 생성되는 데이터를 구분할 수 있는 유일한 값이다.
- SELECT ROWID, EMPNO FROM EMPLOYEE와 같은 SELECT문으로 확인할 수 있다.
- 데이터가 어떤 데이터 파일, 어느 블록에 저장되어 있는지 알 수 있다.
11. WITH
- WITH 구문은 서브쿼리를 사용해서 임시 테이블이나 뷰처럼 사용할 수 있는 구문이다.
- 서브쿼리 블록에 별칭을 지정할 수 있다.
- 옵티마이저는 SQL을 인라인 뷰나 임시 테이블로 판단한다.
- 여러 테이블들을 JOINI으로 해결 하기 힘든 경우 사용 한다.
WITH (별칭) AS (SELECT 문장) SELECT 문장;
SQL> WITH EMP_WITH AS 2 (SELECT EMPLOYEE_ID, JOB_ID, DEPARTMENT_ID FROM EMPLOYEES 3 UNION ALL 4 SELECT EMPLOYEE_ID, JOB_ID, DEPARTMENT_ID FROM JOB_HISTORY) 5 SELECT EMPLOYEE_ID, JOB_ID, DEPARTMENT_ID 6 FROM EMP_WITH 7 WHERE EMPLOYEE_ID = 200; EMPLOYEE_ID JOB_ID DEPARTMENT_ID ----------- -------------------- ------------- 200 AD_ASST 10 200 AD_ASST 90 200 AC_ACCOUNT 90
12. DCL(Data Control Language)
GRANT와 REVOKE 명령어가 있다.
12.1 GRANT
- 데이터베이스 사용자(User)에게 접속권한, 오브젝트 생성권한, DBA 권한 등을 부여하는 명령어.
- 데이터베이스를 사용하기 위해서는 모든 명령어에 대해 권한이 필요하다.
GRANT privileges ON object TO user;
1) Privileges
권한
설명
SELECT
지정된 테이블에 대해 SELECT 권한을 부여 한다.
INSERT
지정된 테이블에 대해 INSERT 권한을 부여 한다.
UPDATE
지정된 테이블에 대해 UPDATE 권한을 부여 한다.
DELETE
지정된 테이블에 대해 DELETE 권한을 부여 한다.
REFERENCES
지정된 테이블을 참조하는 제약조건 생성 권한을 부여 한다.
ALTER
지정된 테이블을 수정 할 수 있는 권한을 부여 한다.
INDEX
지정된 테이블에 대해 INDEX를 생성하는 권한을 부여 한다.
ALL
지정된 테이블에 대해 모든 권한을 부여 한다.
2) WITH GRANT OPTION
옵션
설명
WITH GRANT OPTION
- 특정 사용자에게 권한을 부여할 수 있는 권한을 부여 - 권한을 부여 받은 사용자가 다른 사용자에게 권한을 부여한 경우 상위 권한자의 권한이 취소되면 하위도 취소된다.
WITH ADMIN OPTION
- 테이블에 대한 모든 권한을 부여 한다. - 권한을 부여 받은 사용자가 다른 사용자에게 권한을 부여한 경우 상위 권한자의 권한이 취소 되어도 하위는 권한이 유지된다.
12.2 REBOKE
- 데이터베이스 사용자(User)에게 부여한 권한을 회수 한다.
REVOKE privileges ON object FROM user;
13. TCL(Transaction Control Language)
일반적으로 쿼리를 실행하면 데이터베이스에 바로 적용되지 않는다. 이를 이용하여 쿼리 실행을 전체 또는 일정 부분 취소하여 잘못된 쿼리로 데이터베이스의 데이터가 변경되지 않도록 한다.
13.1 COMMIT
- 모든 쿼리(INSERT, UPDATE, DELETE)가 데이터베이스에 반영되어 데이터가 변경된다.
- 데이터 변경을 위해 LOCK으로 잡고 있던 부분을 UNLOCK 한다.
- COMMIT 완료 후 다른 사용자는 변경된 데이터를 볼 수 있고 조작할 수 있다.
- COMMIT이 실행되면 하나의 트랜잭션이 완료된 것이다.
COMMIT;
13.2 ROLLBACK
- 모든 쿼리(INSERT, UPDATE, DELETE)가 취소되고 트랜잭션이 종료된다.
- 바로 이전 COMMIT 한곳 까지만 취소된다.
ROLLBACK;
13.3 SAVEPOINT
- 트랜잭션을 모두 취소하지 않고 부분으로 나누어 취소하려는 경우 사용한다.
- SAVEPOINT에 이름을 부여하여 그 위치까지 취소 처리 한다.
INSERT ..... UPDATE .... SAVEPOINT S1; -- SAVEPOINT 설정 DELETE ..... SAVEPOINT S2; -- SAVEPOINT 설정 INSERT .... ROLLBACK TO S1; -- S1 까지 ROLLBACK
DBA는 아니어도 개발자로서 기본적인 소양을 갖추기 위해서 한번쯤 공부해도 좋은 SQL 개발자 자격증 시험에 대해 조금 정리를 해보았습니다. 시험과목과 점수는 아래와 같습니다.
과목
과목별 세부 항목
문항수
배점
데이터 모델링의 이해
데이터 모델링의 이해
10
20(문항당 2점)
데이터 모델과 성능
SQL 기본 및 활용
SQL 기본
40
80(문항당 2점) 주관식(8)
SQL 활용
SQL 최적화 기본 원리
- 검정 시간 90분(1시간 30분)
- 합격 기준 : 총점 60점 이상(과목별 40% 미만 과락)
- 50문제 중 30문제 맞추면 합격
- 20문제를 틀리면 합격. 포기할 부분은 포기(?)
SQL 활용 부분의 뒤쪽은 일반적인 프로젝트에 잘 사용하지 않는 부분이라 초보자에게는 어려울 수 있을 것 같습니다. 물론 이 책에 모든 쿼리가 다 들어있지 않기 때문에 사용하게 되는 데이터베이스에 맞추어 추가적인 학습을 해야 실전에서는 사용이 가능 하겠지만 기초를 쌓기에는 좋을 것 같습니다.
아래 내용은 SQL 개발자 자격증에 대한 서적 중 [이기적 SQL 개발자]를 참고하여 요약을 하였습니다.
I. 데이터 모델링
1.1 데이터 모델링의 특징
특징
설명
추상화(Abstraction)
현실세계의 특징을 간략하게 표현 한다.
단순화(Simplification)
복잡하지 않도록 누구나 이해할 수 있도록 표현 한다.
명확화(Clarity)
한가지 의미만 가지도록 명확하게 해석 한다.
1.2 데이터 모델링 단계
단계
설명
개념적 모델링 (Conceptual Data Modeling)
- 전사적 관점에서 기업의 데이터를 모델링 한다. - 추상화 수준이 가장 높은 수준의 모델링이다. - 업무 측면에서 모델링 한다.
논리적 모델링 (Logical Data Modeling)
- 개념적 모델링을 논리적 모델링으로 변환 - 특정 데이터베이스 모델에 종속 한다. - 식별자를 정의하고 관계, 속성 등을 모두 표현한다. - 정규화를 통해서 재사용성을 높인다.
물리적 모델링 (Physical Modeling)
- 데이터베이스를 실제 구축하고 테이블, 인덱스, 함수등을 생성. - 성능, 보안, 가용성 등을 고려하여 데이터베이스를 구축한다.
1.3 데이터 모델링 관점
관점(View)
설명
데이터
- 비즈니스 프로세스에서 사용되는 데이터 - 구조 분석, 정적 분석
프로세스
- 비즈니스 프로세스에서 수행하는 작업을 의미 - 시나리오 분석, 도메인 분석, 동적 분석
데이터와 프로세스
- 프로세스와 데이터 간의 관계를 의미 - CRUD(Create, Read, Update, Delete) 분석
1.4 ERD(Entity Relationship Diagram)
ERD 작성 절차
(1) Entity를 도출 하고 그린다.
(2) Entity를 배치 한다.
(3) Entity 간의 관계를 설정 한다.
(4) 관계 명을 서술 한다.
(5) 관계 참여도를 표현한다.
(6) 관계의 필수 여부를 표현한다.
2. 3층 스키마(3-Level Schema)
2.1 3층 스키마란
- 사용자, 설계자, 개발자가 데이터베이스를 보는 관점에 따라 기술한 ANSI 표준
- 데이터베이스의 독립성을 확보하기 위한 방법
- 독립성이 확보되면 대응력 향상, 관리 및 유지보수 비용 절감 등의 장점이 있다.
2.2 3층 스키마의 독립성
독립성
설명
논리적 독립성
개념 스키마가 변경 되어도 외부 스키마가 영향을 받지 않는다.
물리적 독립성
내부 스키마가 변경 되어도 개념 스키마가 영향을 받지 않는다.
2.3 3층 스키마 구조
3층 스키마 구조
구조
설명
외부 스키마 (External Schema)
- 응용 프로그램이 접근하는 데이터베이스를 정의 한다. - 사용자 관점. 업무상 관련이 있는 데이터에 대한 접근 - 관련 데이터베이스의 뷰(View)를 표시
개념 스키마 (Conceptual Schema)
- 설계자 관점. 사용자 전체 집단의 데이터베이스 구조이다. - 전체 데이터베이스 내의 규칙과 구조를 표현 - 통합 데이터베이스 구조
내부 스키마 (Internal Schema)
- 개발자 관점, 데이터베이스의 물리적 저장구조 - 데이터 저장 구조, 레코드 구조, 필드 정의, 인덱스 등을 의미한다.
3. 엔터티(Entity)
실체, 객체. 실세계에서 개별적으로 인식될 수 있는 것. Table을 의미 한다.
3.1 Entity에 대한 정의
변별할수 있는 사물 - Peter Chen (1976) -
데이터베이스 내에서 변별 가능한 객체 - C.J Date (1986) -
정보를 저장할 수 있는 어떤 것 - James Martin (1989) -
정보가 저장될 수 있는 사람, 장소, 물건, 사건 그리고 개념 등 - Thomas Bruce (1992) -
3.2 Entity 도출
엔터티는 사람, 장소, 물건, 사건, 개념 등의 명사에 해당한다.
엔터티는 업무상 관리가 필요한 관심사에 해당한다.
엔터티는 저장이 되기 위한 어떤 것(Thing)이다.
엔터티는 인스턴스의 집합
3.3 Entity 특징
특징
설명
식별자
- Entity는 유일한 식별자가 있어야 한다. (회원 ID, 계좌번호)
인스턴스 집합
- 2개 이상의 인스턴스가 있어야 한다. (고객정보는 2명 이상 있어야 한다.)
속성
- Entity는 반드시 속성을 가지고 있다. (고객 엔터티에는 회원ID, 패스워드, 이름, 주소 등이 있다)
관계
- Entity는 다른 Entity와 최소한 한 개 이상의 관계가 있어야 한다. (고객은 계좌를 개설 한다)
업무
- Entity는 업무에서 관리되어야 하는 집합이다. (고객, 계좌등)
3.4 Entity 종류
1) 유형과 무형에 따른 엔터티 종류
종류
설명
유형 엔터티
- 업무에서 도출되며 지속적으로 사용되는 엔터티, 물리적으로 존재. (고객, 강사, 사원 등)
개념 엔터티
- 물리적 형태가 없이 개념적으로 사용되는 엔터티 (거래소 종목, 코스닥 종목, 생명보험 상품)
사건 엔터티
- 비즈니스 프로세스를 실행하면서 생성되는 엔터티 (주문, 체결, 주문취소, 수수료 청구 등)
2) 발생 시점에 따른 엔터티 종류
종류
설명
기본 엔터티 (Basic Entity)
- 키 엔터티라고도 한다. - 다른 엔터티로부터 영향을 받지 않고 독립적으로 생성된다. (고객, 상품, 부서 등)
중심 엔터티 (Main Entity)
- 기본 엔터티와 행위 엔터티의 중간에 있다. - 기본 엔터티로 부터 발생되고 행위 엔터티를 생성한다. (계좌, 주문, 취소, 체결 등)
행위 엔터티 (Active Entity)
- 2개 이상의 엔터티로부터 발생된다 (주문 이력, 체결 이력 등)
4. 속성(Attribute)
4.1 속성
1) 사전적 의미
- 사물(事物)의 성질, 특징 또는 본질적인 성질, 그것이 없다면 실체를 생각할 수 없는 것
- 본질적 속성이란 어떤 사물 또는 개념에 없어서는 안 될 징표(徵表)의 전부
- 이 징표는 사물이나 개념이 어떤 것인지를 나타내고 그것을 다른 것과 구별하는 성질
- Table의 column을 의미 한다.
2) 데이터 모델링 관점
- 업무에서 필요로 하는 인스턴스로 관리하고자 하는 의미상 더 이상 분리되지 않는 최소의 데이터 단위
- 업무상 관리하기 위한 최소의 의미 단위
- 의미상 더 이상 분리되지 않는다.
- 엔터티를 설명하고 인스턴스의 구성요소가 된다.
4.2 속성의 종류
1) 분해 여부에 따른 속성의 종류
종류
설명
단일 속성
- 하나의 의미로 구성된 것으로 회원ID, 이름 등
복합 속성
- 여러개의 의미가 있는 것으로 주소 등이 있다. - 주소는 시, 군, 동 등으로 분해될 수 있다.
다중값 속성
- 속성에 여러개의 값을 가질 수 있는것으로 상품 리스트 등이 있다. - 다중값 속성은 Entity로 분해 된다.
2) 특성에 따른 속성의 종류
종류
설명
기본 속성
- 비즈니스 프로세스에서 도출되는 본래의 속성 (회원ID, 이름, 계좌번호, 주문 일자 등)
설계 속성
- 데이터 모델링 과정에서 발생되는 속성 - 유일한 값을 부여 한다. (상품코드, 지점코드 등)
파생 속성
- 다른 속성에 의해서 만들어지는 속성 (합계, 평균 등)
4.3 도메인
속성이 가질 수 있는 값의 범위
Attribute
Domain
학점
0.0~4.0 실수
주소
20자리 문자열
5. 관계(Relationship)
5.1 관계
Entity 간의 관련성을 의미하며 존재 관계와 행위 관계로 분류 된다.
5.2 관계의 분류
1) 존재 관계
- Entity간의 관계가 존재 상태로 연결 되는 것. (고객이 은행지점에서 회원가입 하면 관리지점이 할당되고 관리 된다.)
2) 행위 관계
- Entity간의 관계가 행위로서 연결 되는 것. (계좌를 이용해서 주문을 하면 주문이력이 발생)
5.3 관계 차수
1) 1:1(One To One)
종류
설명
완전 1대1
하나의 엔터티에 관계되는 엔터티의 관계가 하나인 경우. 반드시 존재
선택적 1대1
하나의 엔터티에 관계되는 엔터티가 하나 이거나 없을 수도 있다.
2) 1:M (One To Many)
- 하나의 엔터티에 관계된 엔터티에 값이 여러개 있는 관계.
- 고객 엔터티와 구매내역 엔터티의 경우 고객이 많은 구매내역을 가질 수 있다.
3) M:N (Many to Many)
- 두개의 엔터티가 서로 여러개의 관계를 가지고 있다.
- 학생과 과목 엔터티의 경우 한 명의 학생이 여러 개의 과목을 수강하거나, 하나의 과목을 여러 학생이 수강하는 경우 M대N 관계가 발생 한다.
- M대N의 관계 조인은 카데시안 곱이 발생 하며 1대N, N대1로 분리 하여야 한다.
4) 선택성
구분
설명
필수적 관계
Entity의 속성 하나에 대해 상대 Entity의 속성에 관계가 존재 해야 한다.
선택적 관계
Entity의 속성 하나에 대해 상대 Entity에 관계가 없을 수도 있다.
6. 엔터티 식별자(Entity Identifier)
식별자의 개념
Entity 내에서 Instance 들을 구분할 수 있는 구분자
6.1. 주식별자의 특징(기본키, Primary key)
특징
내용
대표성
주식별자는 Entity를 대표할 수 있어야 한다. (예: EMP 테이블 사원번호)
최소성
주식별자를 구성하는 속성의 수는 최소의 수가 되어야 한다.
유일성
주식별자는 Entity에 있는 Instance 들을 유일하게 구분한다.
불변성
주식별자로 지정되면 그 값은 변하지 않아야 한다.
존재성
주식별자는 반드시 데이터 값이 존재해야 한다.
6.2 데이터베이스 키의 종류
종류
설명
후보키(Candidate Key)
유일성과 최소성을 만족하는 키
기본키(Primary Key)
후보키 중에서 Entity를 대표할 수 있는 키
대체키(Alternate Key)
여러개의 후보키 중에서 기본키를 선정하고 남은 키
슈퍼키(Super Key)
유일성은 만족하지만 최소성을 만족하지 않는 키
외래키(Foregin Key)
관계를 맺고 있는 다른 Entity의 기본 키를 가리킨다.
6.3 식별자의 종류
1) 식별자의 대표성
종류
설명
주 식별자
- Entity를 대표하는 주 식별자 - 다른 Entity와 참조 관계로 연결될 수 있다.(다른 Entity에 외래 키)
보조 식별자
- 유일성과 최소성을 만족 하지만 대표성을 만족 못하는 식별자.
2) 생성 여부
종류
설명
내부 식별자
Entity 내부에서 스스로 만들어 지는 식별자
외부 식별자
다른 Entity와의 관계를 통해서 다른 Entity로부터 받아오는 식별자
3) 속성의 수
종류
설명
단일 식별자
하나의 속성으로 구성된 식별자
복합 식별자
둘 이상의 속성으로 구성된 식별자
4) 대체 여부
종류
설명
본질 식별자
비즈니스 프로세스에서 생성되는 식별자
인조 식별자
유일성을 만족하는 속성이 없는 경우 인위적으로 생성하는 식별자
II. 데이터 모델과 성능
1. 정규화
- 데이터의 일관성, 최소한의 데이터 중복, 최대한의 데이터 유연성을 위한 데이터 분해 과정
- 함수적 종속성을 이용하여 잘못 설계된 스키마를 쪼개어 분해하는 과정으로 하나의 릴레이션에 하나의 함수적 종속성만 존재하도록 정규화 한다.
- 정규화를 통해 추상화가 되면 비즈니스가 변화해도 수정사항이 최소화된다.
- 정규화는 1정규화에서 5정규화까지 존재. 실제 3정규화까지 수행
1.1 정규화 절차
정규화 절차
설명
제 1 정규화
- 속성(Attribute)의 원자성 확보 - 기본키(Primary key) 설정
제 2 정규화
- 기본키가 2개 이상의 속성으로 이루어진 경우, 부분 함수 종속성 제거
제 3 정규화
- 기본키를 제외한 컬럼 간 종속성 제거. 즉, 이행 함수 종속성 제거
BCNF
- 기본키를 제외하고 후보키가 있는 경우, 후보키가 기본키를 종속시키면 분해 한다.
제 4 정규화
여러 컬럼이 하나의 컬럼을 종속시킬 때 분해를 통해 다중값 종속성 제거
제 5 정규화
조인에 의해 종속성이 발생하는 경우 분해한다.
1.2 함수적 종속성(Functional Dependency)
- 데이터나 데이터베이스 구성요소가 다른 구성요소에 영향을 끼치는 현상
- 릴레이션의 어떤 속성 값에 의하여 다른 속성 값이 결정되는 경우 함수적 종속이라한다.
- 함수적 종속 관계는 X -> Y 로 표시하고 “Y는 X에 함수적으로 종속된다”라고 한다.
1) 제1정규화
- 기본키를 잡는것
- 하나의 속성이 단일 값을 갖도록 한다.
2) 제2정규화
- 부분 함수 종속성을 제거하기 위해 분해하는 과정
- 부분 함수 종속성 : 기본키가 2개 이상의 column으로 이루어진 경우에 발생.
- 종속 관계에 데이터의 중복 또는 불일치를 해결.
3) 제3정규화
- 이행 함수 종속성을 제거하기 위해 분해하는 과정.
- 이행 함수 종속성 : 기본키를 제외하고 column간에 종속성이 발생하는것.
- 주식별자가 아닌 속성들 중에 종속관계가 발생하면 분해하는 작업.
- 제3정규화는 제1정규화, 제2정규화를 수행하고 작업 한다.
4) BCNF(Boyce-Codd Normal Form)
복수의 후ㅗㅂ키가 있고, 후보키들이 복합 속성이어야 하고 서로 중첩되는 경우.
2. 정규화와 성능
2.1 정규화의 문제점
- 정규화는 데이터 조회(SELECT) 시 조인(JOIN)을 유발하여 자원(CUP와 메모리)을 많이 사용한다.
- 조회를 프로그램으로 구현하는 경우 중첩된 루프(Nested Loop)를 사용하게 된다.
- 정규화의 문제점 해결을 위해서 반정규화를 통해 테이블 하나에 값을 저장 하여 해결한다.
- 반정규화는 데이터를 중복시키기 때문에 또다른 문제점 발생.
- 정규화는 테이블을 분해하여 입출력 데이터의 양을 줄여서 성능을 향상 시켜야 한다.
3. 반정규화(De-Normalization)
- 데이터베이스의 성능 향상을 위해 데이터 중복을 허용하여 조인(Join)을 줄이는 방법.
- 반정규화는 조회(SELECT)의 속도를 향상 하지만 데이터 모델의 유연성은 낮아진다.
3.1 반정규화를 하는 경우
- 정규화를 통하여 수행 속도가 느려진 경우
- 다량의 범위를 자주 처리해야 하는 경우
- 특정 범위의 데이터만 자주 처리하는 경우
- 요약/집계 정보가 자주 요구되는 경우
3.2 반정규화 절차
반정규화 절차
설명
대상 조사 및 검토
데이터 처리 범위, 통계성 등을 확인해서 반정규화 대상을 조사한다.
다른 방법 검토
- 반정규화를 수행하기 전에 다른 방법이 있는지 검토한다. - 예를 들어 클러스터링, 뷰, 인덱스 튜닝, 응용 프로그램, 파티션 등을 검토한다.
반정규화 수행
테이블, 속성, 관계 등을 반정규화한다.
3.3 반정규화 기법
1) 계산된 칼럼 추가
배치 프로그램으로 총판매액, 평균잔고, 계좌평가 등을 미리 계산하고, 그 결과를 특정 칼럼에 추가한다.
2) 테이블 수직 분할
하나의 테이블의 두 개 이상의 테이블로 분할한다. 즉, 칼럼을 분할하여 새로운 테이블을 만드는 것이다.
3) 테이블 수평 분할
하나의 테이블에 있는 값을 기준으로 테이블을 분할하는 방법이다.
4) 테이블 병합 : 조인(Join) 발생 차단
- 1:1 관계의 테이블을 하나의 테이블로 병합해서 성능을 향상시킨다.
- 1:N 관계의 테이블을 병합하여 성능을 향상시킨다. 하지만 많은 양의 데이터 중복이 발생한다.
- 슈퍼 타입과 서브 타입 관계가 발생하면 테이블을 통합하여 성능을 향상시킨다.
5) 슈퍼 타입 및 서브 타입 변환 방법
변환 방법
설명
OneToOne Type
- 슈퍼 타입과 서브 타입을 개별 테이블로 도출한다. - 테이블의 수가 많아서 조인이 많이 발생하고 관리하기 어렵다.
Plus Type
- 슈퍼 타입과 서브 타입 테이블로 도출한다. - 조인이 발생하고 관리가 어렵다.
Single Type
- 슈퍼 타입과 서브 타입을 하나의 테이블로 도출한다. - 조인 성능이 좋고 관리가 편하지만, 입출력 성능이 나쁘다.
4. 분산 데이터베이스
4.1 분산 데이터베이스
- 중앙 집중형 데이터베이스 : 한 대의 물리적 시스템에 데이터베이스 관리 시스템을 설치하고 여러 명의 사용자가 데이터베이스 관리 시스템에 접속하여 데이터베이스를 사용하는 구조.
- 분산 데이터 베이스 : 물리적으로 떨어진 데이터베이스에 네트워크로 연결하여 단일 데이터베이스 이미지를 보여주고 분산된 작업 처리를 수행하는 데이터베이스.
- 투명성 제공 : 분산 데이터베이스를 사용하는 고객이, 시스템이 분산되어있는지 인식하지 못하면서, 자신만의 데이터베이스를 사용하는 것 처럼 사용
4.2 투명성 종류
투명성
설명
분할 투명성
고객은 하나의 논리적 릴레이션이 여러 단편으로 분할되어 각 단편의 사본이 여러 시스템에 저장되어있음을 인식할 필요가 없다.
위치 투명성
- 고객이 사용하려는 데이터의 저장 장소를 명시할 필요가 없다. - 고객은 데이터가 어느 위치에 있더라도 동일한 명령을 사용하여 데이터에 접근할 수 있어야 한다.
지역 사상 투명성
지역 DBMS와 물적 데이터베이스 사이의 사상이 보장됨에 따라 각 지역 시스템 이름과 무관한 이름이 사용 가능하다.
중복 투명성
데이터베이스 객체가 여러 시스템에 중복되어 존재함에도 고객과는 무관하게 데이터의 일관성이 유지된다.
장애 투명성
데이터베이스가 분산되어 있는 각 지역의 시스템이나 통신망에 이상이 발생해도, 데이터의 무결성은 보장된다.
병행 투명성
여러 고객의 응용 프로그램이 동시에 분산 데이터베이스에 대한 트랜잭션을 수행하는 경우에도 결과에 이상이 없다.
4.3 분산 데이터베이스의 설계 방식
1) 상향식 설계 방식
지역 스키마 작성 후 향후 전역 스키마를 작성하여 분산 데이터베이스를 구축한다.
2) 하향식 설계 방식
전역 스키마 작성 후 해당 지역 사상 스키마를 작성하여 분산 데이터베이스를 구축한다.
3) 분산 데이터베이스 장점과 단점
장점
단점
- 데이터베이스 신뢰성과 가용성이 높다 - 분산 데이터베이스가 병렬처리를 수행하기 때문에 빠른 응답이 가능하다. - 분산 데이터베이스를 추가하여 시스템 용량 확장이 쉽다.
- 데이터베이스가 여러 네트워크를 통해서 분리되어 있기 때문에 관리와 통제가 어렵다. - 보안관리가 어렵다. - 데이터 무결성 관리가 어렵다. - 데이터베이스 설계가 복잡하다.
