예전 Postgresql 버전을 변경하기 위해 윈도우 10에 설치해 보았습니다. 그냥 프로그램을 다운받아 설치하면 되는데 여러 번 설치를 해야 하는 상황이라 매번 검색을 하기 귀찮아 설치 매뉴얼을 만들었습니다. 어려운 부분은 없지만 그래도 보면서 작업 하면 시간이 단축되더군요.
지난번 “[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
