하위^^

INSERT 예

SET SERVEROUTPUT ON;
-- 특정 사원 추가
SELECT * FROM emp;
-- insert_date(ename, job, deptno)
INSERT INTO emp(ename, job, deptno) VALUES('kim', 'staff', 3);

CREATE OR REPLACE PROCEDURE insert_date(v_ename IN emp.ename%TYPE, v_job IN emp.job%TYPE, v_deptno IN emp.deptno%TYPE)
    IS
    BEGIN
        INSERT INTO emp(ename, job, deptno) VALUES(v_ename, v_job, v_deptno);
        COMMIT;
        DBMS_OUTPUT.PUT_LINE(v_ename||'/'||v_job||'/'||v_deptno);
        DBMS_OUTPUT.PUT_LINE('데이터 입력 성공');
    END;
/

EXEC insert_date('kim', 'staff', 3);

UPDATE 예

-- 특정 사원의 급여 올리기
-- 급여가 인상된 직원의 데이터 출력(사원번호, 이름, 급여)
-- 테이블 : employees
SELECT * FROM employees;
CREATE OR REPLACE PROCEDURE update_date(v_no IN NUMBER, v_rate IN NUMBER)
    IS
        v_emp employees%ROWTYPE;
    BEGIN
        UPDATE employees SET salary = salary + (salary * (v_rate / 100))WHERE emp_no = v_no;
        COMMIT;
        
        SELECT emp_no, family_name, first_name, salary
        INTO v_emp.emp_no, v_emp.family_name, v_emp.first_name, v_emp.salary
        FROM employees WHERE emp_no = v_no;
        
        DBMS_OUTPUT.PUT_LINE('사원번호 : '||v_emp.emp_no);
        DBMS_OUTPUT.PUT_LINE('이름 : '||v_emp.family_name||''||v_emp.first_name);
        DBMS_OUTPUT.PUT_LINE('봉급 : '||v_emp.salary);
    END;
/
EXEC update_date(117, 10);

DELETE 예

-- 특정 사원 번호의 데이터 삭제 프로시저 제작
-- 대상 테이블 : employees
SELECT * FROM employees;
CREATE OR REPLACE PROCEDURE delete_date(v_deptno IN employees.emp_no%TYPE)
    IS
    BEGIN
        DELETE FROM emp WHERE emp_no = v_deptno;
        COMMIT;
    END;
/

EXEC delete_date(127);

Function은 예전에 JAVA에서 사용하던 메소드, C에서 함수와 거의 비슷한 개념이다.

Procedure와 차이점은 Return값이 있다는 것으로 

반환값이 필요할 때 사용한다.

JAVA에서 메소드를 만들 때 처럼 역시 반환 타입을 반드시 Return문에 선언해주어야 한다, 생략할 수 없다.

또한 OUT, IN OUT을 쓸 수 없고 IN만 사용 가능하다.

예제를 볼 때 실행 부분에서 ':' 부분을 보면 msg 앞에도 붙어있는데

이것이 없으면 msg라는 함수를 찾으므로 msg가 변수임을 알리는 키워드이다.

나머지는 프로시져와 거의 비슷한 편

SET SERVEROUTPUT ON;
-- 함수 선언
CREATE OR REPLACE FUNCTION hello
    RETURN VARCHAR2
    IS
        msg VARCHAR2(20);
    BEGIN
        msg := 'hello, world';
        RETURN msg;
    END;
/

VARIABLE message VARCHAR2(20);

EXEC :message := hello;
PRINT message;


-- 사원번호를 받아서 해당 사원의 급여를 10% 올리고 출력 하기
CREATE OR REPLACE FUNCTION print1(emp_num IN employees.salary%TYPE)
    RETURN employees.salary%TYPE
    IS
        find_salary employees.salary%TYPE;
    BEGIN
        UPDATE employees SET salary = salary * 1.1 WHERE emp_no = emp_num;
        COMMIT;
        SELECT salary
        INTO find_salary
        FROM employees WHERE emp_no = emp_num;
        RETURN find_salary;
    END;
/

VARIABLE msg NUMBER;

EXEC :msg := print1(118);
PRINT msg;


-- Function 조회
SELECT object_name, object_type FROM USER_PROCEDURES WHERE object_type = 'FUNCTION';

-- Function 삭제
-- DROP FUNCTION [function name]
DROP FUNCTION hello;

%ROWTYPE은 전의 Scalar타입보다 약간 효율적인 데이터 타입 선언이다.

이 타입 선언문은 선언 시 지정한 테이블의 컬럼 선언 부분을 전부 가져와 사용한다.

예제에서 사용법을 확인하자.

-- 테이블의 모든 컬럼을 통채로 들고 온다.
-- 테이블명%ROWTYPE
SET SERVEROUTPUT ON;

DECLARE
    v_emp employees%ROWTYPE;
    BEGIN
        SELECT emp_no, first_name, family_name, reg_date
        INTO v_emp.emp_no, v_emp.first_name, v_emp.family_name, v_emp.reg_date
        FROM employees WHERE emp_no = 118;
        DBMS_OUTPUT.PUT_LINE('사원번호 : '||v_emp.emp_no);
        DBMS_OUTPUT.PUT_LINE('이름 : '||v_emp.family_name||''||v_emp.first_name);
        DBMS_OUTPUT.PUT_LINE('입사일 : '||v_emp.reg_date);
    END;
/

CREATE OR REPLACE PROCEDURE emp_row(ref_emp_no IN employees.emp_no)
    IS
    v_emp employees%ROWTYPE;
    BEGIN
        SELECT emp_no, first_name, family_name, reg_date
        INTO v_emp.emp_no, v_emp.first_name, v_emp.family_name, v_emp.reg_date
        FROM employees WHERE emp_no = ref_emp_no;
        DBMS_OUTPUT.PUT_LINE('사원번호 : '||v_emp.emp_no);
        DBMS_OUTPUT.PUT_LINE('이름 : '||v_emp.family_name||''||v_emp.first_name);
        DBMS_OUTPUT.PUT_LINE('입사일 : '||v_emp.reg_date);
    END;
/

EXEC emp_row(117);

스칼라 데이터 타입은 일반 단일 데이터타입 변수와 %TYPE 데이터형 변수가 있다.

일반 단일 데이터 타입은 타입을 직접 지정하고, 알고 있어야 하고, :=으로 초기값을 부여함

%TYPE 데이터형은 %TYPE만 사용하면 되고 데이터 타입을 몰라도 된다.

특정 테이블의 컬럼에 변수를 담는다.

SET SERVEROUTPUT ON;

-- 일반 단일 데이터 타입
-- 변수명 [CONSTANT] [DataType] [NOT NULL] := [초기값];

DECLARE
    var_emp_no NUMBER(3, 0);
    var_emp_name VARCHAR2(40);
    var_emp_grade CHAR(1) := 'A';
    BEGIN
        var_emp_no := 123;
        var_emp_name := 'kim';
        DBMS_OUTPUT.PUT_LINE('사원 번호 : '||var_emp_no);
        DBMS_OUTPUT.PUT_LINE('사원 이름 : '||var_emp_name);
        DBMS_OUTPUT.PUT_LINE('사원 등급 : '||var_emp_grade);
    END;
/
-- 초기 값을 생성 시 넣어도 되고 BEGIN, 시작하고 나서 넣어도 별 차이는 없다.

-- 사원번호 115에 대한 이름, 급여
DESC employees;
DECLARE
    var_emp_no NUMBER(3);
    var_emp_fn NVARCHAR2(8);
    var_emp_ln NVARCHAR2(2);
    var_emp_sal NUMBER;
    BEGIN
        SELECT emp_no, family_name, first_name, salary
        INTO var_emp_no, var_emp_ln, var_emp_fn, var_emp_sal
        FROM employees
        WHERE emp_no = 115;
        DBMS_OUTPUT.put_line('사원번호 : '||var_emp_no);
        DBMS_OUTPUT.put_line('이름 : '||var_emp_ln||''||var_emp_fn);
        DBMS_OUTPUT.put_line('급여 : '||var_emp_sal);
    END;
/






-- %TYPE 변수
-- [변수명] 테이블명.컬럼명%TYPE
-- 변수에 테이블, 컬럼을 입력해서 타입을 가져옴
DECLARE
    var_emp_no employees.emp_no%TYPE;
    var_emp_fn employees.first_name%TYPE;
    var_emp_ln employees.family_name%TYPE;
    var_emp_sal employees.salary%TYPE;
    BEGIN
        SELECT emp_no, family_name, first_name, salary
        INTO var_emp_no, var_emp_ln, var_emp_fn, var_emp_sal
        FROM employees
        WHERE emp_no = 115;
        DBMS_OUTPUT.put_line('사원번호 : '||var_emp_no);
        DBMS_OUTPUT.put_line('이름 : '||var_emp_ln||''||var_emp_fn);
        DBMS_OUTPUT.put_line('급여 : '||var_emp_sal);
    END;
/


-- %TYPE을 이용한 프로시저
CREATE OR REPLACE PROCEDURE emp_in(ref_emp_no IN employees.emp_no%TYPE)
    IS
    var_emp_no employees.emp_no%TYPE;
    var_emp_fn employees.first_name%TYPE;
    var_emp_ln employees.family_name%TYPE;
    var_emp_sal employees.salary%TYPE;
    BEGIN
        SELECT emp_no, family_name, first_name, salary
        INTO var_emp_no, var_emp_ln, var_emp_fn, var_emp_sal
        FROM employees
        WHERE emp_no = ref_emp_no;
        DBMS_OUTPUT.put_line('사원번호 : '||var_emp_no);
        DBMS_OUTPUT.put_line('이름 : '||var_emp_ln||''||var_emp_fn);
        DBMS_OUTPUT.put_line('급여 : '||var_emp_sal);
    END;
/

EXEC emp_in(120);

PL/SQL은 Procedual Language SQL의 약자로

프로시저라고 부른다.

PL은 SQL에서 변수 정의, 조건문, 제어문 등 프로그래밍 언어에서 사용하던 비슷한 기능, 함수들을 사용할 수 있다.

장점은 SQL문을 한번에 DB에 보내 처리하여 속도가 향상된다.

단점으로는 실행 주기 설정이 어렵고 SQL과 PL/SQL 형태만 실행 가능, DB 이외의 프로그램을 실행 할 수 없다.

실행할 작업과 이름을 지정한 부분을 PL/SQL BLOCK이라고 한다.

함수와 똑같이 매개변수를 받고 재사용 할 수 있는 BLOCK이다.

대개 반복 실행, 구현이 복잡한 트랜잭션을 실행하기 위해 생성한다.

CREATE OR REPLACE PROCEDURE [임의의 이름]

    IS

 -- 초기 변수 선언

        [변수 이름] [변수 타입] := [초기값];

    BEGIN

        [실행 명령어];

    END;

/

/, 슬래시로 닫아 마무리하여 PL/SQL BLOCK을 만든다.

가끔 프로시저는 주석문을 위 아래 같은줄 어디든 영향받을 때가 있는거 같은데 왜 그런지는 정확히 모르겠다.

가장 큰 특징으로는 Return 값이 따로 없기 때문에 UPDATE같은 구문으로 수정을 바로 해야함

그 외의 실행이라던지 접속 연결이라던지는 다음 코드에서 설명한다.

-- 1) 매개변수가 없는 프로시저
CREATE OR REPLACE PROCEDURE hello
    IS
        msg VARCHAR2(20) := 'Hello, world';
 -- 초기 변수 선언
    BEGIN
        DBMS_OUTPUT.PUT_LINE(msg);
    END;
/

-- 프로시저 종료
-- DBMS_OUTPUT 출력을 위한 구문(WEB_USER 연결시 딱 한 번만 설정 해주면 접속 종료할 때 까지 유지됨)
SET SERVEROUTPUT ON;

-- 프로시저 실행
-- EXEC [프로시저 이름]
EXEC hello;


-- 2) IN 매개변수가 있는 경우
-- IN 매개변수 : 인자값을 받는 변수, 변수형에는 크기를 따로 입력하지 않음.
-- 사원번호를 받아 특정 사원의 급여를 10% 올려주는 프로시저 생성
SELECT * FROM employees;
DESC employees;

SELECT * FROM employees;
CREATE OR REPLACE PROCEDURE upSalary(eno IN NUMBER)
    IS
    BEGIN
        UPDATE employees SET salary = salary * 1.1 WHERE emp_no = eno;
        COMMIT;
    END;
/

EXEC upSalary(118);



-- 3) OUT 매개변수
-- 프로시저에는 반환(Return 값)이 없기 때문에 OUT 매개변수를 활용한다.
-- 사원 번호를 넣으면 사원명과 급여를 반환
-- 115번의 이름과 급여 출력
CREATE OR REPLACE PROCEDURE find_emp(v_eno IN NUMBER, v_fname OUT NVARCHAR2, v_sal OUT NUMBER)
    IS
    BEGIN
        SELECT first_name, salary
        INTO v_fname, v_sal
        FROM employees 
        WHERE emp_no = v_eno;
    END;
/
-- SELECT 인자들에 맞춰 INTO에 빼낼 값들을 타입과 자리를 맞춰서 기입
-- 실행은 기존과 다름
-- OUT 매개변수는 실행 전 받아 줄 변수(BIND, 바인드 변수)를 선언해야 한다.
-- 바인드 변수 선언 시 실제 컬럼 속성 보다 조금 더 여유있게 선언하는게 좋다.
VARIABLE v_fname NVARCHAR2(10);
VARIABLE v_sal NUMBER;
-- 실행
EXEC find_emp(115, :v_fname, :v_sal);

PRINT v_fname;
PRINT v_sal;



-- IN/OUT 매개변수
-- 매개변수로 시작하지만 종료시 OUT 변수 기능을 함
-- 일반적으로 프로시저가 다른 프로시저 호출 후 반환값을 사용 할 경우 활용
-- 사원번호를 넣으면 해당 사원의 급여 출력
CREATE OR REPLACE PROCEDURE find_sal(v_eno IN OUT NUMBER)
    IS
    BEGIN
        SELECT salary INTO v_eno FROM employees WHERE emp_no = v_eno;
    END;
/
-- 익명 프로시저, 일회용 프로시저, 저장이 안되고 한번 쓰고 사라짐
-- 위에 선언한 find_sal을 다시 이용
DECLARE 
    v_eno NUMBER := 115;
    BEGIN
        DBMS_OUTPUT.PUT_LINE('eno = '||v_eno);
        find_sal(v_eno);
        DBMS_OUTPUT.PUT_LINE('salary = '||v_eno);
    END;
/

-- 프로시저 조회
SELECT object_name,object_type FROM USER_PROCEDURES;

-- 프로시저 삭제
DROP PROCEDURE hello;

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문을 허용한다.