하위^^

ROLLUP은 그룹별 산출한 결과값의 소계/누계를 구하는 함수이다.

GROUP BY와 함께 쓰이며 GROUP BY로 묶은 부분을 ROLLUP을 추가하여 () 괄호로 묶어 사용한다.

GROUP BY ROLLUP( [] );

DECODE는 자바의 Switch 문과 비슷하다고 생각하면 된다.

SELECT 문의 컬럼명에 쓰이며 다음과 같이 쓰인다.

DECODE (검사 할 조건 계산식(결과가 값으로 떨어져야 함), 값1, 처리할 식, 값2, 처리할 식, ..... , DEFAULT값(생략가능))

예제를 통해 실제로 어떻게 쓰이나 보자

--rollup
--그룹별 산출한 결과값의 소계/누계를 구하는 함수

SELECT depart_no, commision, COUNT(*)
FROM employees
GROUP BY depart_no, commision;

SELECT depart_no, commision, COUNT(*)
FROM employees
GROUP BY ROLLUP(depart_no, commision);

-- GROUPING 함수를 통해 (null)을 제거할 수 있다.
-- 실제 데이터와 집계 데이터를 구분하는 함수
-- 0 : 실제 데이터
-- 1 : 집계 데이터
SELECT GROUPING(depart_no), GROUPING(commision), COUNT(*)
FROM employees
GROUP BY ROLLUP(depart_no, commision);

-- DECODE()
-- 자바에서 switch 문과 유사한 선택 함수
-- DECODE(검사 할 조건 계산식(결과가 값으로 떨어져야 함), 값1, 처리할 식 1, 값2, 처리할 식 2 ...... , DEFAULT 값(생략 가능))

SELECT
    DECODE(GROUPING(depart_no), 0, depart_no, 1, ' ') 부서번호,
    DECODE(GROUPING(commision), 0, TO_CHAR(commision), 1, ' ') 커미션,
    COUNT(*)
FROM employees
GROUP BY ROLLUP(depart_no, commision);

숫자형의 함수로는 6가지를 소개한다.

ABS

CEIL

FLOOR

OMD

ROUND

TRUNC

각각의 기능을 예제와 주석을 통해 학인해보자

-- 1. 숫자형 함수 : 수 값을 인수로 받아 NUMBER 타입의 값을 반환하는 함수.
-- 여기서는 자주 사용 하는 함수 몇가지만 소개 한다.
-- DUAL table : function 이나 계산식을 테이블 생성 없이 수행하기 위한 일종의 dummy 테이블

-- 1) ABS(n) : 절대값을 계산하는 함수이다.
SELECT ABS (-1.234) absolute FROM DUAL;
-- 결과 : 1.234

-- 2) CEIL(n) : 올림값을 반환하는 함수이다.
SELECT CEIL(10.1234) "CEIL" FROM DUAL;
-- 결과 : 11
SELECT CEIL(-10.1234) "CEIL" FROM DUAL;
-- 결과 : -10

-- 3) FLOOR(n) : 버림값을 반환하는 함수이다.
SELECT FLOOR(10.1234) "FLOOR" FROM DUAL;
-- 결과 : 10
SELECT FLOOR(-10.1234) "FLOOR" FROM DUAL;
-- 결과 : -11 

-- 4) MOD(m, n) : m을 n으로 나눈 나머지를 반환 한다. n이 0일 경우 m을 반환 한다.
SELECT MOD(9, 4) "MOD" FROM DUAL ;
-- 결과 : 1

-- 5) ROUND(n, [m]) : 반올림, n 값을 반올림, m은 반올림 자릿수
SELECT ROUND(192.153, 1) "ROUND" FROM DUAL; 
-- 결과 : 192.2 소숫점 2자리를 반올림 하여 1자리 까지 표현
SELECT ROUND(192.153, -1) "ROUND" FROM DUAL;
-- 결과 : 190 숫자 2자리를 반올림 하여 1자리 까지 표현

-- 6) TRUNC(n, m) : n값을 절삭하는 함수로 m은 소숫점 아래 자릿수를 나타낸다.
SELECT TRUNC(7.5597, 2) "TRUNC" FROM DUAL;
-- 결과 : 7.55
 
-- 7) TRUNC 예제
SELECT TRUNC(789.5597, -2) "TRUNC" FROM DUAL;
-- 결과 : 700

DB에서 날짜를 표시할 때 쓰는 키워드를 소개한다.

SYSDATE

SYSTIMESTAMP, 

ADD_MONTHS, 

MONTHS_BETWEEN

LAST_DAY

NEXT_DAY

이와 같이 여러개가 있는데 차이점을 살펴보자.

-- 3.Datetime Functions (날짜 함수)
-- 날짜 함수는 오라클 날짜에 대해 연산을 한다.
-- 날짜 함수는 NUMBER형 값 또는 DATE형의 값을 RETURN 한다.

-- 1) SYSDATE : 현재 일자와 시간(시스템기준)을 얻을 수 있다.(최소단위 1초)
-- [뺄 숫자]/[24시간중]/[60분 중]/[60 초 중]
-- HH:MM:SS 는 안된다. MM 은 MONTH 의 의미로 사용
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') "지금시간" FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-1,'YYYY-MM-DD HH24:MI:SS') "하루전지금시간" FROM DUAL ;

-- AS 나 그냥 컬럼명을 쓸 수 있지만 앞에 숫자가 있는 경우 필히 더블쿼터 사용 
SELECT TO_CHAR(SYSDATE-1/24,'YYYY-MM-DD HH24:MI:SS') "1시간전시간" FROM DUAL ;
 
SELECT TO_CHAR(SYSDATE-1/24/60,'YYYY-MM-DD HH24:MI:SS') "1분전시간" FROM DUAL ;

SELECT TO_CHAR(SYSDATE-(5/24 + 30/24/60 + 10/24/60/60),'YYYY-MM-DD HH24:MI:SS') 
"5시간 30분 10초전" FROM DUAL ;

-- 2) SYSTIMESTAMP : 현재 일자와 시간(시스템기준)을 얻을 수 있다.(최소단위 10억분의 1초)
-- SYSTIMESTAMP 예제

-- 초 단위 이상 표기 할 시에는 날짜 계산이 안된다.
SELECT TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL ;
SELECT TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS.FF9') FROM DUAL ;
 
SELECT TO_CHAR(SYSTIMESTAMP -1/24,'YYYY-MM-DD HH24:MI:SS') "1시간전시간" FROM DUAL ;
 
SELECT TO_CHAR(SYSTIMESTAMP -1/24/60,'YYYY-MM-DD HH24:MI:SS') "1분전시간" FROM DUAL ;

-- 3)ADD_MONTHS(a, b) : a의 날짜에 b의 달을 더한 값을 반환 한다.
-- 오늘 날짜에 3달을 추가
SELECT TO_CHAR(ADD_MONTHS(SYSDATE,3),'RRRR-MM-DD')  "date" FROM DUAL;   

SELECT TO_CHAR(ADD_MONTHS(SYSTIMESTAMP,3),'RRRR-MM-DD')  "date" FROM DUAL;

-- 4) MONTHS_BETWEEN(a1, a2) : a1과 a2 사이의 달의 수를 NUMBER형 타입으로 반환 한다.
-- 2016년01월01일 과 2016년 11월 01일 사이의 달 수
SELECT MONTHS_BETWEEN(TO_DATE('2016-11-01','RRRR-MM-DD'), 
                      TO_DATE('2016-01-01','RRRR-MM-DD')) "month" FROM DUAL;
 
-- 2016년 06월 05일 과 2016년 15월 01일 사이의 일 수
SELECT TO_DATE('2016-06-05','RRRR-MM-DD') - TO_DATE('2016-05-01','RRRR-MM-DD') "Day" FROM DUAL;

-- 5) LAST_DAY(d) : 달의 마지막 날의 날짜를 반환 한다
SELECT SYSDATE today, LAST_DAY(SYSDATE) lastday FROM DUAL;
 
-- 6) NEXT_DAY(d, c1) : 돌아오는 명시된 요일의 일자를 반환한다.
-- 요일은 1(일) ~ 7(토)
-- 4이므로 수요일
SELECT NEXT_DAY(SYSDATE, 4) "Next Wednesday" FROM DUAL;

SELECT NEXT_DAY(TO_DATE('20110501','RRRRMMDD'), 2) "5월 첫째주 월요일" FROM DUAL;

문자형 함수는 데이터타입 CHAR, VARCHAR2의 데이터를 이용해 다양한 기능을 하는 함수들로

아래와 같은 종류가 있다.

CONCAT

INITCAP

LOWER

UPPER

LPAD

RPAD

SUBSTR

SUBSTRB

LENGTH

LENGTHB

REPLACE

INSTR

TRIM

LTRIM

RTRIM

과 같이 여러개가 있다.

아래의 예제를 통해 쓰임을 파악해보자

-- 2. 문자형 함수: CHAR, VARCHAR2 타입을 인수로 받아 VARCHAR2 타입의 값을 반환하는 함수이다.
-- 1) CONCAT(char1, char2)
-- CONCAT 함수는 Concatenation의 약자로 두 문자를 결합하는 역할을 한며, "||" 연산자와 같은 역할을 한다.
SELECT CONCAT('www.', 'oracleclub')||'.com' name FROM DUAL;
 
  
-- 2) INITCAP(char), LOWER(char), UPPER(char)
-- INITCAP : 주어진 문자열의 첫 번째 문자를 대문자로 변환시켜 준다.
-- LOWER : 주어진 문자열을 소문자로 변환시켜 준다.
-- UPPER : 주어진 문자열을 대문자로 변환시켜 준다.

SELECT INITCAP('oracleclub') name FROM DUAL UNION ALL
SELECT UPPER('oracleclub') name FROM DUAL UNION ALL
SELECT LOWER('oracleclub') name FROM DUAL;
-- UNION 은 합집합이며, UNION ALL 은 중복을 포함하는 합집합 이다.


-- 3) LPAD(char1, n [,char2]), RPAD(char1, n [,char2])
-- LPAD : 왼쪽에 문자열을 끼어 넣는 역할을 한다.
-- RPAD : 오른쪽에 문자열을 끼어 넣는 역할을 한다.
-- n은 반환되는 문자열의 전체 길이를 나타내며, char1의 문자열이 n보다 클 경우 char1을 n개 문자열 만큼 반환 한다.
SELECT LPAD('oracleclub', 12, '*') name FROM DUAL UNION ALL
SELECT RPAD('oracleclub', 12, '*') name FROM DUAL;


-- 4) SUBSTR(char, m ,[n]), SUBSTRB(char, m ,[n])
-- SUBSTR 함수는 m 번째 자리부터 길이가 n개인 문자열을 반환 한다.
-- m이 음수일 경우에는 뒤에서 m 번째 문자부터 n개의 문자를 반환한다.
-- SUBSTRB 함수에서 B는 Byte단위로 처리하겠다는 의미이다.
-- 세번째 이후 문자열 반환.
SELECT SUBSTR('oracleclub', 3) name FROM DUAL;  
-- 세번째 이후 네개의 문자열 반환.
SELECT SUBSTR('oracleclub', 3, 4) name FROM DUAL;
-- 뒤에서 세번째아후 두개의 문자열 반환.
SELECT SUBSTR('oracleclub', -3, 2) name FROM DUAL;
 
-- DB가 UTF-8인 경우 아래 SQL의 결과는?(이경우 한글 한 글자는 3바이트 이다.)
SELECT SUBSTRB('오라클클럽',1) name FROM DUAL  UNION ALL
SELECT SUBSTRB('오라클클럽',3) name FROM DUAL;


-- 5) LENGTH(char), LENGTHB(char)
-- 문자열의 길이를 반환 한다.
SELECT LENGTH('오라클클럽') len FROM DUAL UNION ALL
SELECT LENGTHB('오라클클럽') len FROM DUAL;
 

-- 6) REPLACE(char1, str1, str2)
-- REPLACE는 문자열의 특정 문자를 다른 문자로 변환 한다.
SELECT REPLACE('oracleclub','oracle','db') name FROM DUAL;
 
-- 대소문자를 구분한다는 것을 알수 있다.
SELECT REPLACE('OracleClub','oracle','DB') name FROM DUAL;
SELECT REPLACE('OracleClub','Oracle','DB') name FROM DUAL;
 
 
-- 7) INSTR (char1, str1, m, n)
-- 문자열이 포함되어 있는지를 조사하여 문자열의 위치를 반환 한다. 
-- 지정한 문자열이 발견되지 않으면 0이 반환 된다.
-- char1 : 지정문자, str1 : 검색문자, m:시작위치, n:검색순위
-- 지정한 문자 OK가 발견되지 않아 0을 반환.
SELECT INSTR('CORPORATE FLOOR','OK') idx FROM DUAL; -- 0 반환
SELECT INSTR('CORPORATE FLOOR','OR') idx FROM DUAL; -- 2 반환
 
-- 왼쪽에서 세 번째부터 시작을 해서 검색된 OR의 위치를 반환 한다.
SELECT INSTR('CORPORATE FLOOR','OR', 3) idx FROM DUAL; -- 5 반환 
 
-- 왼쪽에서 세 번째부터 시작을 해서 비교를 하여 OR이 두 번째 검색되는 지점의 위치를 반환 한다.
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) idx FROM DUAL;
 
-- 8) TRIM (char1 FROM char2), LTRIM (char1[,char2]), RTRIM (char1[,char2])
-- 앞(왼쪽) 또는 뒤(오른쪽)
-- 9) TRIM : 특정한 문자를 제거 한다. 제거할 문자를 입력하지 않으면 기본적으로 공백이 제거 된다.
-- 10) LTRIM : 왼쪽부터 문자를 제거 한다. 제거할 문자를 입력하지 않으면 기본적으로 왼쪽 공백이 제거 된다.
-- 11) RTRIM : 오른쪽부터 문자를 제거 한다. 제거할 문자를 입력하지 않으면 기본적으로 오른쪽 공백이 제거 된다.

-- o와 공백을 제거 하는 TRIM 예제
SELECT TRIM('o' FROM 'oracleclub') name FROM DUAL; -- racleclub

-- 왼쪽의 문자열을 TRIM하는 예제 
-- 공백의 경우 왼쪽 공백만 제거 되는 것을 확인 할 수 있다.
SELECT LTRIM('oracleclub','oracle') name FROM DUAL;
SELECT REPLACE(LTRIM(' oracleclub '),' ','*') name FROM DUAL;
 
-- 오른쪽의 문자열을 TRIM 하는 예제 
-- 공백의 경우 오른쪽 공백만 제거 되는 것을 확인 할 수 있다.
SELECT RTRIM('oracleclub','club') name FROM DUAL
UNION ALL
SELECT REPLACE(RTRIM(' oracleclub '),' ','*') name FROM DUAL;

집계함수, aggregate는 db에 저장된 컬럼의 값의 평균을 나타낸다던가 합계, 최소, 최대값을 보여주는

결과값을 출력하는 함수이다.

보통 GROUP BY 절과 함께 쓰인다.

예시 코드를 확인해보자

-- 집계 함수 : 여러행 또는 테이블 전체 행으로부터 하나의 결과값을 반환하는 함수

-- 집계함수(Aggregate function)의 이해
-- GROUP BY절을 이용하여 그룹 당 하나의 결과로 그룹화 할 수 있다.
-- HAVING절을 사용하여 집계함수를 이용한 조건 비교를 할 수 있다.
-- MIN, MAX 함수는 모든 자료형에 사용 할 수 있다.
-- 일반적으로 가장 많이 사용하는 집계함수에는 AVG(평균), COUNT(개수), MAX(최대값), MIN(최소값), SUM(합계) 등이 있다.

--1) COUNT : 검색된 행의 수를 반환 한다.
-- 검색된 행의 총 수 4개를 반환. 즉 4개의 부서가 존재한다.
SELECT COUNT(deptno) FROM dept;
 

-- 2) MAX : 컬럼값 중에서 최대값을 반환 한다.
-- sal 컬럼값 중에서 제일 큰값을 반환. 즉 가장 큰 급여를 반환.
SELECT MAX(salary) salary FROM employees;
 
 
-- 3) MIN : 컬럼값 중에서 최소값을 반환 한다.
-- sal 컬럼값 중에서 가장 작은 값 반환. 즉 가장 적은 급여를 반환
SELECT MIN(salary) salary FROM employees;
  
  
-- 4) AVG : 평균 값을 반환 한다.
SELECT AVG(salary) salary FROM employees;

-- 부서번호 30의 사원 평균 급여를 소수점 1자리 이하에서 반올림
SELECT ROUND(AVG(salary),1) salary FROM emp WHERE deptno = 30;
 
 
-- 5) SUM : 검색된 컬럼의 합을 반환 한다.
SELECT SUM(salary) salary FROM employees;

-- 부서번호 30의 사원 급여 합계를 조회.
SELECT SUM(sal) salary FROM emp WHERE deptno = 30;

Sequence는 자동으로 증가하는 Object이다.

데이터에 번호를 붙일 때 사용하며 키값으로 자주 사용한다.

-- 1) 시퀀스 생성

-- CREATE SEQUENCE [sequence name]

-- [START WITH n] [INCREMENT BY n]

-- [MAXVALUE n | NOMAXVALUE]

-- [MINVALUE n | NOMINVALUE]

-- [CYCLE | NOCYCLE]

-- [CACHE | NOCACHE]

-- START WITH n : 시퀀스의 시작값 n 설정(default : 1)

-- INCREMENT BY n : 시퀀스 증가값 n 설정(default:1)

-- MAXVALUE n : 최대값 n 설정(지정하지 않으면 9999....,n을 넘으면 에러)

-- MINVALUE n : 최소값 n 설정(default:1)

-- CYCLE:최대값 도달시 순환 여부(default:nocycle)

-- cycle 선언시 cache 필요

-- CACHE : 원하는 숫자만큼 미리 만들어 저장

-- 1-1) 일반적인 시퀀스 생성

CREATE SEQUENCE seq_empno;

-- 1-2) 8000 부터 시작하고 2만큼 증가하며 20개씩 저장하는 시퀀스

CREATE SEQUENCE seq_empno 

START WITH 8000 INCREMENT BY 2 CACHE 20;

-- 시퀀스 증가

SELECT seq_empno.NEXTVAL FROM DUAL;

-- 현재 시퀀스 값 확인

SELECT seq_empno.CURRVAL FROM DUAL;

-- 2) 기타 옵션들

CREATE SEQUENCE seq_test_max MAXVALUE 10;

-- MAX VALUE가 10이므로 11번째 에러가 나타난다.

SELECT seq_test_max.NEXTVAL FROM DUAL;

-- CYCLE 옵션 + CACHE가 필요

CREATE SEQUENCE seq_test_cycle START WITH 5 MAXVALUE 10 CYCLE CACHE 5;

SELECT seq_test_cycle.NEXTVAL FROM DUAL;

-- CYCLE 옵션 + CACHE + MINVALUE

CREATE SEQUENCE seq_test_min START WITH 5 MINVALUE 5 MAXVALUE 10 CYCLE CACHE 5;

SELECT seq_test_min.NEXTVAL FROM DUAL;

-- 3) 시퀀스의 실제 활용 예시

SELECT * FROM emp;

-- emp 테이블을 활용에 적절하게 변경

ALTER TABLE emp ADD(emp_no NUMBER(5));

INSERT INTO emp (emp_no, ename, job, deptno, hiredate) VALUES (seq_empno.NEXTVAL, 'oh', 'manager', 3, SYSDATE);

SELECT * FROM emp;

-- 4) 시퀀스 수정(START WITH n을 제외한 나머지 옵션 수정 가능)

ALTER SEQUENCE seq_empno INCREMENT BY 10 CACHE 40;

SELECT seq_empno.NEXTVAL FROM DUAL;

-- 5) 시퀸스 검색

SELECT * FROM USER_SEQUENCES;

-- 6) 시퀸스 삭제

DROP SEQUENCE seq_text_max;

뷰 = 가상의 테이블

뷰는 가상의 테이블을 만들어 사용 할 때 쓴다.

만약 정규화를 하여 6개의 테이블로 분리가 되었는데 이를 조인해서 여러번 사용한다고 치자

그러면 사용할 때 마다 3~4줄이 되는 SELECT 문이 만들어진다.

너무 길어지기 때문에 간단하게 사용하기 위해서 나온게 뷰이다.

뷰에 SELECT같이 조회한 내용을 넣어 묶음으로 임시로 가상 테이블을 생성하여

간단하게 뷰만 호출하는 방식이다.

뷰는 본질적으로 보기만 하기 위해서 만들어진 기능이다.

하지만 수정이 가능하게 되었는데 아래 코드의 주석을 통해 알아보자.

또한 뷰가 어떻게 쓰이는지도 알아보자.

12_view

-- View

-- 여러 테이블의 값을 서브쿼리나 조인으로 가져오기 힘들다

-- 가상의 테이블에 여러 테이블의 값을 담도록 한다.

-- 이후 가상 테이블을 부르기만 하면 된다.

-- 0) 뷰의 제한 조건

-- 테이블 데이터에 NULL인 컬럼은 뷰에 포함 될 수 없다.

-- WITH READ ONLY 옵션을 사용 하면 뷰 데이터 갱신이 불가하다.

-- WITH CHECK OPTION을 사용하면 해당 데이터 수정 가능

-- 하지만 ROWID, ROWNUM, NEXTVAL, CURRVAL 등과 같은 가상컬럼에

-- 대한 참조를 포함하는 뷰에는 INSERT 할 수 없다.

-- 뷰는 원래 수정이 불가능 했지만 요즘에는 바뀌었다고 함

-- 하지만 퍼져있는 데이터가 묶여있기 때문에

-- 뷰가 현재 어디를 참조하는지 예측하기 어렵고 만약 수정한다면

-- 데이터가 어디가 변경됫는지 파악하기 힘들게 된다.

-- 그러므로 수정은 매우 비추

-- 1) 뷰의 생성 문법

-- CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW [view name] AS [sub-query]

-- [WHERE 조건 WITH CHECK OPTION] [WITH READ ONLY]

-- OR REPLACE : 뷰 생성시 기존 뷰가 있으면 갱신한다.

-- 이름이 같은게 있다면 데이터를 새로은 쿼리문으로 바꾸는것.

-- 뼈대를 남기고 다시 건물을 짓는것처럼 리모델링 하는 것

-- 자원 상 절약이 되므로 습관적으로 붙이는걸 추천

-- FORCE : 기존 테이블 없이 일단 만든다.

-- WITH CHECK OPTION : 조건에 따라 뷰를 수정 할 수 있는 옵션

-- WITH READ ONLY : 읽기 전용으로 지정

CREATE OR REPLACE VIEW name_qry AS

    SELECT e.ename, d.deptname FROM emp e, dept d WHERE e.deptno = d.deptno

        WITH READ ONLY;

-- 아래의 오류가 발생하면 권한이 없기 때문임.

-- ORA-01031: insufficient privileges

-- 01031. 00000 -  "insufficient privileges"

-- 우측 상단의 WEB_USER 접속을 SYSTEM으로 바꿔서 접속

GRANT CREATE VIEW TO web_user;

-- 이것을 실행 한 후 다시 접속을 WEB_USER로 바꿔주고 위의 CREATE OR REPLACE VIEW를 다시 실행한다.

SELECT * FROM name_qry;

-- 이것으로 조회 할 수 있다.

-- 뷰는 테이블의 구조같은 중요한 정보를 알 수 없기 때문에

-- 다른 사람에게 보여주기 위한 테이블이다.

-- 또한 이러한 특성으로 보안에도 효과적이다.

UPDATE name_qry SET ename = 'oh' WHERE deptname = 'delivery';

-- with read only 옵션 때문에 데이터 수정이 불가

-- SQL 오류: ORA-01779: cannot modify a column which maps to a non key-preserved table

-- 01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"

CREATE OR REPLACE VIEW chk_option AS 

    SELECT ename, job, deptno FROM emp

        WHERE deptno = 1 WITH CHECK OPTION;

SELECT * FROM chk_option;

UPDATE chk_option SET job = 'manager' WHERE ename = 'lee';

UPDATE chk_option SET deptno = 2 WHERE ename = 'lee';

-- ORA-01402: view WITH CHECK OPTION where-clause violation 발생

-- deptno = 1일 경우에만 바뀌게 되어있으므로 deptno = 2는 바뀌어선 안된다.

-- 2) 뷰 삭제

-- 어떤 뷰가 있는지 조회

SELECT * FROM USER_VIEWS;

-- 삭제할 뷰를 입력

DROP VIEW name_qry;

DROP VIEW chk_option;

-- 뷰는 수정하는것 보단 새로 만드는것을 지향하자

ROWID, ROWNUM, NEXTVAL, CURRVAL 등과 같은 가상컬럼은 아래 테이블을 조회했을때 DB에서 임시로 지정한 번호같은걸 말한다.

사진상의 회색 박스 좌측의 번호는 어디서 나온걸까??

이러한게 가상 컬럼이다.

어려운 개념으로 상세히는 배우지 않았으므로 이정도로만 설명

1.정규화, 비정규화

정규화는 DB 설계 자체에서 데이터의 중복을 최소화하여 이상현상을 최소화하는 것이 목표이다.

이상현상으로는 아래와 같이 3가지가 있다.

삭제 이상 - 원치 않는 데이터도 같이 삭제

삽입 이상 - 원치 않는 데이터가 같이 삽입

갱신 이상 - 데이터의 일부만 갱신

정규화를 위한 기본 폼(NF, Nomal Form), 규칙같은게 있는데 아래와 같다.

1NF - 모든 항목에 값이 있어야 하며(NOT NULL), 중복열이 없어야 한다.

2NF - 개체의 속성이 한 식별자에 종속 되어야 한다.(부분 함수적 종속 제거)

3NF - X로 인해 Y가 결정 되고, Y로 인해 Z가 결정되는 경우(이행 함수 종속성)

~

4NF...

5NF...

.....

4NF부턴 이론적으로만 존재

이 NF 규칙을 봤을땐 바로 이해가지 않을 것이다.

솔직히 말을 되게 어렵게 써서 어떻게 간단하게 쓸지도 모르겠다.

고로 예제를 통해서 이해해보자.

데이터 표의 밑줄은 항목을 키로 지정했음을 표시하는것

1NF

우측 그림은 비식별관계여야 함, 잘못 표시

2NF

복합키는 NAME 항목을 보면 중복값이 많기 때문에 어쩔 수 없이 묶어 복합키를 사용했다.

하지만 쪼갤 수 있으므로 분리해서 사용할 수 있을 땐 분리하자

쉽게 말하면 복합키를 구지 사용하지 말자.

3NF

만약 두 데이터가 아래와 같이 합쳐져 있었다면

연도  우승국  감독

1998    X        X

2002    X        X

2006    X        X

2010    X        X

2014    X        X

만약 우승국이 바뀐다면 감독도 바뀌어야 한다.

고로 위와같이 분리하였으니 우승국만 바꾸면 된다.

위와같은 데이터는 항목, 컬럼이 한두개 밖에 없으니 바꾸는 작업이 별로 없을것 같지만

실제로는 컬럼이 훨씬 많이 지정되므로 바꾸지 않으면 매우 번거로운 작업이 된다.

NF에서 요구하는건 대부분 테이블 규모를 키우지 말고 되도록 쪼개라는 말이 된다.

하지만 테이블을 지나치게 쪼개면, 정규화를 하면 쿼리가 복잡해지고, 복잡해지면 성능이 저하된다.

예를들어 테이블 참조를 여러개, 만약 6개를 한다면??, 속도가 매우 느려질것.

그래도 이해가 안간다면 정규화의 예시를 구글에서 찾아보자

역정규화는 말 그대로 정규화의 반대.

쪼갠 테이블을 합치는 것이다.

2. INDEX (색인)

색인은 검색을 빠르게 하기위한 수단

기본키(Primary Key), 유니크 키(Unique Key)가 지정되면 자동으로 Index가 생성된다.

색인의 예로는 대표적으로 사전이나 책이 있다.(가나다라, ABCD~ 기준으로 나열된다.)

장점은 검색 속도가 빨라지고 시스템의 부하가 줄어든다.

단점으로는 색인 생성에 시간, 공간이 요구되고, 데이터가 삽입/갱신/삭제가 빈번히 일어나면 시스템에 부하가 걸린다.

색인은 키값을 지정할 때 자동으로 생성 되게 끔 되있는데

임의로 지정할 수도 있다. 아래의 예시를 확인해보자.

색인을 생성하는 것을 Indexing이라고 한다.

-- 1) 비트맵 인덱스

-- bit, 각 컬럼에 대해 적은 갯수의 값이 있을 경우 사용()

-- (예 : 남|여, 1|0, True|False)

-- 데이터가 어디 있는지 BIT로 표시, 속도가 빠르지만 컬럼이 추가 될 때 마다 인덱스를 다시 만든다.

-- 수정/변경/추가가 적은 데이터에 적합

-- Standard Edition 버전(유료버전)에서만 사용 가능

-- CREATE BITMAP INDEX [index name] ON table(column)

CREATE BITMAP INDEX emp_idx_deptno ON emp(deptno);

-- 위 구문은 유료 버전이 아니라면 오류가 발생한다.

-- 2) UNIQUE INDEX

-- 기본키와 유니크키 설정 시 생성되는 인덱스

-- 중복되지 않은 데이터에게만 걸 수 있는 인덱스

-- CREATE UNIQUE INDEX [index name] ON table(column)

CREATE UNIQUE INDEX emp_idx_ename ON emp(ename);

-- 위와 같이 중복된 데이터가 있을 경우 에러 발생

-- ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

-- 01452. 00000 -  "cannot CREATE UNIQUE INDEX; duplicate keys found"

-- 기본키와 비슷해서 사용할 일이 별로 없음

-- 3) NON-UNIQUE INDEX

-- 컬럼에 중복이 있어도 인덱스 생성이 가능

-- 가장 일반적으로 사용하는 INDEX 구문

-- CREATE INDEX [index name] ON table(column)

CREATE INDEX emp_idx_ename ON emp(ename);

-- 4)결합 인덱스

-- 16개 까지 컬럼을 조합하여 인덱스 생성

-- INDEX앞에 UNIQUE를 붙일 수 있다. (중복되는 컬럼 비허용)

-- CREATE INDEX [index name] ON table(column, column, ...)

CREATE INDEX emp_combi_idx ON emp(ename, job);

-- 5)인덱스 검색

-- 어떤 인덱스가 있나 검색

SELECT * FROM USER_INDEXES;

SELECT index_name, table_name, uniqueness FROM USER_INDEXES;

-- 6)인덱스 삭제

DROP INDEX EMP_IDX_ENAME;

수학시간에 배운 집합 개념을 DB에 적용한것

합집합 교집합 차집합 개념 자체를 따로 설명할 필요는 없을것 같아서 생략

-- [Query1] [UNION | UNION ALL | INTERSECT | MINUS] [Query2]

-- 1. 합집합(UNION)
-- 두개의 테이블의 데이터를 모두 보여준다.(중복 제거)
-- 성능상 계산이 많아 별로 좋지 않다.
-- 동일한 컬럼이 최소 한개 이상 있어야 한다.
SELECT Deptno FROM emp UNION SELECT deptno FROM dept;
-- ALL을 사용하여 중복 포함
SELECT Deptno FROM emp UNION ALL SELECT deptno FROM dept;



-- 교집합(INTERSECT)
SELECT Deptno FROM emp INTERSECT SELECT deptno FROM dept;







-- 차집합(MINUS), 6 - 1 = 5 처럼 왼쪽이 기준
-- emp에서 dept를 빼고 남은 값을 출력
SELECT Deptno FROM emp MINUS SELECT deptno FROM dept;
-- dept에서 emp를 빼고 남은 값을 출력
SELECT Deptno FROM dept MINUS SELECT deptno FROM emp;

DB는 다른 여러 프로그램이 존재하지만 Oracle에서 만든 DB와 MySQL 두가지를 대표적으로 사용한다.

DB에서 사용하는 쿼리문은 거의 비슷한데 몇몇 코드들은 약간씩 차이가 있거나 쓰지 않거나 한다.

예를 들어, 오라클 DB는 Boolean형식이 없는것 처럼..

이번에 게시하는 Join도 마찬가지로 약간 차이가 있으니

이곳에서 본 내용을 MySQL에 바로 쓴다면 오류가 나올것이다.

Join은 두 개의 테이블을 같이 검색할 때 쓰는것이다.

서브 쿼리와의 차이점이라면, 서브쿼리는 한 테이블의 데이터를 가공하여 검색하고

조인은 두 테이블 그 자체를 검색한다.

종류로는 여러가지로

Cross Join(잘 안씀), Equi Join (등가 조인, 내부 조인, 가장 잘 씀)

Non-Equi Join (거의 사용하지 않음)

Self Join, Outer Join

등 그 외에도 몇개가 있다.

잘 사용하지 않고, 어? 이걸 왜쓰지?? 라는 느낌을 받아도 용도가 있긴 있어서 만들어진 기능이다.

-- Join 이란?
-- 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법이다.

-- 두개의 테이블을 SELECT 문장 안에서 조인하려면 적어도 하나의 컬럼이
-- 그 두 테이블 사이에서 공유 되어야 한다.

-- 보통 둘 이상의 행들의 공통된 값 Primary Key 및 Foreign Key 값을
-- 사용하여 조인 한다.

-- Join 방법
-- 조인 방법 : Cross Join, Equi Join (등가 조인, 내부 조인)
-- Non-Equi Join (거의 사용하지 않음: 교재에는 수록)
-- Self Join, Outer Join


-- 0. CROSS JOIN
-- 카다시안 곱을 수행
-- 카다시안 곱은 2개의 테이블을 곱한 행을 반환, 카디시안 곱은 경우의 수를 추출할 때 사용
-- emp(10), dept(4) = 40, emp는 10개의 행, dept는 4개의 행
-- FROM [tableA] CROSS JOIN [tableB]
SELECT ename, deptname FROM emp e CROSS JOIN dept d;
-- 생략도 가능
SELECT e.ename, d.deptname FROM emp e, dept d;


-- 1. Equi JOIN
-- 가장 일반적으로 사용하는 JOIN
-- (=)를 사용 하므로 Equi(등가) 조인이라고 불린다.


-- 1-1) equi join
-- ename과 deptname을 묶어서 보여준다.
-- Cross Join에 생략할 거 하고 조건을 단 형태
SELECT e.ename, d.deptname FROM dept d, emp e WHERE e.deptno = d. deptno;

INSERT INTO dept VALUES(5, 'dev02', 'FLOLIDA');
-- 추가된 데이터는 deptno = 5가 해당되는 데이터가 없으므로 출력되지 않는다.
SELECT e.ename, d.deptname FROM dept d, emp e WHERE e.deptno = d. deptno;

-- 1-2) inner join
-- INNER JOIN이 붙는다.
-- WHERE절 대신 ON을 사용
SELECT e.ename, d.deptname FROM dept d INNER JOIN emp e ON e.deptno = d. deptno;
-- INNER 생략가능
SELECT e.ename, d.deptname FROM dept d JOIN emp e ON e.deptno = d. deptno;
-- equi 조인과 다른 점
-- ON 대신 USING을 사용 할 수 있다.
--공통되는 컬럼명이나 뷰, 서브쿼리 명시가 가능, 좀 더 조건 수행에 이용 할 수 있다.
SELECT e.ename, d.deptname FROM dept d JOIN emp e USING (deptno);

-- 1-3) NATURAL JOIN
-- 특정 조건이 아니라 자연스럽게 컬럼에 추가되어서 출력
SELECT deptno, e.ename, d.deptname FROM dept d NATURAL JOIN emp e;
-- deptno를 앞에 명시하여 일치하는것에 알아서 따라서 출력됨



-- 2. Self JOIN (자기 조인), 잘 안씀
-- 기본적으로는 등가조인과 같다.
-- 1개 테이블을 대상으로 한다.

SELECT a.ename AS name, b.ename AS manager_name FROM emp a, emp b;
-- 실행해 보면 결과가 100개 나온다.
-- 자기 조인을 하면 내부적으로 데이터간 카디션 곱이 일어난다.


-- 3. Outer JOIN (외부 조인)
-- 등가 조인은 두 테이블 모두 존재해야만 보여준다.
-- 둘 중 하나가 없는 값도 보기 위해서는 Outer JOIN을 사용해야 한다.

-- Equi JOIN = AND, Outer JOIN = OR
-- Outer JOIN은 (+)를 사용하는 방법이 있다.(Oracle 한정)
-- 공통적으로는 LEFT Outer JOIN, RIGHT Outer JOIN을 사용한다.

-- 테스트를 위한 삽입
INSERT INTO emp VALUES('kim', 'assistant', 6, to_date('14/06/02', 'YY/MM/DD'));

-- 내부, 위에서 삽입한 정보는 나오지 않음
SELECT e.ename, d.deptname FROM dept d INNER JOIN emp e ON e.deptno = d. deptno;

-- 외부, +가 붙은쪽에 없는 자료를 null로 해서 합친다음 출력(오라클에서만 사용 가능)
SELECT DISTINCT (e.deptno), d.deptname FROM dept d, emp e WHERE e.deptno(+) = d.deptno;
SELECT DISTINCT (e.deptno), d.deptname FROM dept d, emp e WHERE e.deptno = d.deptno(+);
-- 단점 : 쓰기는 편하지만 양쪽에 (+)를 붙일 수 없다.

-- 외부, 다른 프로그램의 경우(Left, Right Join)
-- 있는 쪽을 지목
-- FROM[table1] [LEFT|RIGHT] OUTER JOIN [table2]
-- LEFT OUTER JOIN : 왼쪽에 값이 더 있다.
-- RIGHT OUTER JOIN : 오른쪽에 값이 더 있다.
-- FULL OUTER JOIN : 양쪽에 각기 다른 값이 있다.
SELECT DISTINCT (e.deptno), d.deptname FROM dept d LEFT OUTER JOIN emp e ON e.deptno = d.deptno;
SELECT DISTINCT (e.deptno), d.deptname FROM dept d RIGHT OUTER JOIN emp e ON e.deptno = d.deptno;
SELECT DISTINCT (e.deptno), d.deptname FROM dept d FULL OUTER JOIN emp e ON e.deptno = d.deptno;

이번에 Oracle에서만 단독으로 사용하는 구문이 있다고 했는데 각각의 유사한 프로그램마다

자기만의 구문이 있다.

하지만 ANSI SQL은 거의 모든 sql문을 허용한다.