[국비] SQL 내용정리 Day05

뷰(View)

뷰(view)는 테이블 또는 다른 뷰를 기초로 하는 논리적 테이블이다.
하나 이상의 테이블에서 원하는 모든 데이터를 선택해서 사용자 정의하여 나타낸 것이다.
제한적인 자료만 보기 위해 사용하는 가상테이블의 개념이다.
- 뷰는 기본 테이블로 유도된 가상 테이블이기 때문에 필요한 컬럼만 저장해 두면 관리가 용이해 진다.
- 뷰는 가상테이블로 실제 데이터가 물리적으로 저장된 형태는 아니다.
- 뷰를 통해서 데이터에 접근하면 원본 데이터는 안전하게 보호될 수 있다.

단순뷰: 한 개의 원본테이블을 사용해서 생성
복합 뷰: 두 개 이상의 테이블에 의해 생성(조인을 통해 생성한 view)

특징	단순 뷰	복합 뷰
테이블 수	하나	둘 이상
함수 포함	없음	있음
데이터 그룹 포함	없음	있음
뷰를 통한 DML	있음	없음

View를 사용하는 이유

접근제어를 통한 자동 보안이 제공되기 때문에 데이터베이스 액세스를 제한하기 위해 사용한다.
복잡한 질의를 쉽게 만들어주기 때문에 사용자의 데이터 관리를 간단하게 해준다.
논리적 데이터 독립성을 제공하기 때문에 데이터의 독립성을 허용한다.
동일한 데이터의 다른 뷰를 나타낼 수 있기 때문에 동일 데이터에 대해 동시에 여러 사용자의 상이한 응용이나 요구를 지원해준다.

-- 단순 뷰
SELECT 
    employee_id,
    first_name || ' ' || last_name AS name,
    job_id,
    salary
FROM employees
WHERE department_id = 60;

CREATE VIEW view_emp AS (
    SELECT 
        employee_id,
        first_name || ' ' || last_name AS name,
        job_id,
        salary
    FROM employees
    WHERE department_id = 60
);

SELECT * FROM view_emp;

뷰의 컬럼 이름은 함수 같은 가상 표현식이면 안 되며, 이름을 명확하게 지정해야 한다. (avg, sum과 같은 이름 X)

-- 복합 뷰
CREATE VIEW view_emp_dept_jobs AS (
    SELECT
        e.employee_id,
        e.first_name || ' ' || e.last_name AS name,
        d.department_name,
        j.job_title
    FROM employees e
    LEFT JOIN departments d
    ON e.department_id = d.department_id
    LEFT JOIN jobs j
    ON e.job_id = j.job_id
)
ORDER BY e.employee_id ASC;

SELECT * FROM view_emp_dept_jobs;

여러 테이블을 조인해서 필요한 데이터만 저장하고 빠른 확인을 위해 사용한다.

VIEW 수정 (CREATE OR REPLACE VIEW 구문)

동일이름으로 해당 구문을 사용하면 데이터가 변경되면서 새롭게 생성된다.

CREATE OR REPLACE VIEW view_emp_dept_jobs AS (
    SELECT
        e.employee_id,
        e.first_name || ' ' || e.last_name AS name,
        d.department_name,
        j.job_title,
        e.salary -- 컬럼 추가
    FROM employees e
    LEFT JOIN departments d
    ON e.department_id = d.department_id
    LEFT JOIN jobs j
    ON e.job_id = j.job_id
)
ORDER BY e.employee_id ASC;

SELECT * FROM view_emp_dept_jobs;

SELECT  
    job_title,
    AVG(salary)
FROM view_emp_dept_jobs
GROUP BY job_title
ORDER BY AVG(salary) DESC;

VIEW 삭제(DROP VIEW구문)

DROP VIEW view_emp;

View의 주의사항

VIEW에 INSERT를 하는 경우 실제 테이블에도 반영이 된다.
VIEW의 INSERT, UPDATE, DELETE는 많은 제약사항이 따른다.
원본 테이블이 NOT NULL인 경우 VIEW에 INSERT가 불가능하다.
VIEW에서 사용하는 컬럼이 가상열인 경우에도 INSERT가 안된다.

-- name은 가상열(virtual column)이기 때문에 INSERT가 안된다.
INSERT INTO view_emp_dept_jobs
VALUES(300, 'test', 'test', 10000); 

-- JOIN된 뷰의 경우 한번에 수정할 수 없다.
INSERT INTO view_emp_dept_jobs
(employee_id, department_name, job_title, salary)
VALUES (300, 'test', 'test', 'test'); 

-- 원본 테이블의 null을 허용하지 않는 컬럼 때문에 INSERT가 안된다.
INSERT INTO view_emp
(employee_id, job_id, salary)
VALUES (300, 'test', 10000);

WITH CHECK OPTION (조건 제약 컬럼)

조건에 사용되어진 컬럼값은 뷰를 통해서 변경할 수 없게 해주는 키워드
view WITH CHECK OPTION (변경을 막아주는 에러)

CREATE VIEW view_emp_test AS (
    SELECT
        employee_id,
        first_name,
        last_name,
        hire_date,
        job_id,
        department_id
    FROM employees
    WHERE department_id = 60
)
WITH CHECK OPTION CONSTRAINT view_emp_test_ck;

UPDATE view_emp_test
SET department_id = 100
WHERE employee_id = 105; -- 에러: where-clause violation

-- 읽기 전용 뷰 (WITH READ ONLY (DML 연산을 막는다.))
CREATE OR REPLACE VIEW view_emp_test AS (
    SELECT
        employee_id,
        first_name,
        last_name,
        hire_date,
        job_id,
        department_id
    FROM employees
    WHERE department_id = 60
)
WITH READ ONLY;

INSERT INTO view_emp_test
VALUES (300, 'test', 'test', sysdate, 'IT_PROG', 100); -- 에러: cannot perform a DML operation on a read-only view

UPDATE view_emp_test
SET last_name = 'kim'
WHERE employee_id = 103; -- 에러: cannot perform a DML operation on a read-only view

뷰의 규칙

단순 뷰에서 DML 연산을 수행할 수 있다.
그룹함수, GROUP BY절, DISTINCT 키워드가 포함된 경우 행을 제거할 수 없다.
제거할 수 없는 조건, 표현식으로 정의된 열, ROWNUM이 포함된 경우 데이터를 수정할 수 없다.
제거와 수정할 수 없는 조건, 뷰에 의해 선택되지 않은 NOT NULL 열이 기본 테이블에 있을 때 추가할 수 없다.

SEQUENCE (순차적으로 증가하는 값)

자동적으로 유일 번호를 생성한다.
공유 가능한 객체이다.
주로 기본 키 값을 생성하기 위해 사용한다.
어플리케이션 코드를 대체한다.
메모리에 캐시(Cache)되면 시퀀스 값을 액세스 하는 효율성을 향상 시킨다.

CREATE SEQUENCE sequence_name
	[INCREMENT BY n]
    [START WITH n]
    [{MAXVALUE BY n | NOMAXVALUE}]
    [{MINVALUE BY n | NOMINVALUE]
    [{CYCLE | NOCYCLE}]
    [{CACHE n | NOCACHE}]

-- Sequence 생성
CREATE SEQUENCE dept3_seq 
    START WITH 1 -- 시작값 (기본값이 증가할 때는 최소값, 감소할 때는 최대값)
    INCREMENT BY 1 -- 증가값 (기본값이 양수면 증가 음수면 감소, 기본값 1) 2, 10, 100 등 설정가능
    -- MINVALUE 1 -- 최소값 (기본값이 증가할 때 1, 감소할 때 -1028)
    MAXVALUE 10 -- 최대값 (기본값이 증가할 때 1027, 감소할 때 -1)
    NOCYCLE -- 순환 여부 CYCLE / NOCYCLE (기본값이 NOCYCLE)
    NOCACHE; -- 캐시메모리 여부 CACHE / NOCACHE (기본값이 CACHE 기본)

-- Sequence 사용 (NEXTVAL, CURRVAL)
INSERT INTO dept3
VALUES (dept3_seq.NEXTVAL, 'test', 'test', sysdate );

SELECT dept3_seq.CURRVAL FROM dual;

SELECT dept3_seq.NEXTVAL FROM dual; -- NEXTVAL를 사용하면 값이 증가한다.

-- Sequence 수정 (직접 수정 가능)
-- START WITH은 수정이 불가능하다.
ALTER SEQUENCE dept3_seq MAXVALUE 9999; -- 최개값 증가
ALTER SEQUENCE dept3_seq  INCREMENT BY -1; -- 최대값
ALTER SEQUENCE dept3_seq MINVALUE 1; -- 최소값

INDEX

테이블이나 클러스트에서 쓰이는 선택적인 객체이다.
오라클 데이터베이스 테이블 내에 원하는 레코드를 빠르게 찾을 수 있도록 만들어진 데이터 구조이다.
포인터를 사용해서 행의 검색을 촉진하기 위해 오라클 서버가 사용된다.
빠르게 데이터를 찾기 위해 빠른 경로 액세스 방법을 사용해서 디스크 I/O를 경감시킨다.
자동 인덱스는 Primary Key 또는 Unique 제한 규칙에 의해 자동적으로 생성된다.
수동 인덱스는 CREATE INDEX 명령을 실행해서 만들어진다.

index의 장/단점

index는 primary key, unique 제약 조건에서 자동으로 생성되기 때문에 조회를 빠르게 해주는 hint 역할을 한다.
index는 조회를 빠르게 하지만 무작위하게 많은 인덱스를 생성해서 사용하면 오히려 성능 부하를 일으킬 수 있다.

-- index 추가
CREATE INDEX emp_first_name_idx ON employees(first_name);
-- index 삭제
DROP INDEX emp_first_name_idx;

-- Sequence와 index를 사용하는 hint 방법
CREATE SEQUENCE board_seq
    START WITH 1
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

CREATE TABLE tbl_board (
    bno NUMBER(10) PRIMARY KEY,
    writer VARCHAR(20)
);

INSERT INTO tbl_board
VALUES(board_seq.NEXTVAL, 'test');

INSERT INTO tbl_board
VALUES(board_seq.NEXTVAL, 'admin');

INSERT INTO tbl_board
VALUES(board_seq.NEXTVAL, 'hong');

INSERT INTO tbl_board
VALUES(board_seq.NEXTVAL, 'kim');

INSERT INTO tbl_board
VALUES(board_seq.NEXTVAL, 'test');

INSERT INTO tbl_board
VALUES(board_seq.NEXTVAL, 'admin');

INSERT INTO tbl_board
VALUES(board_seq.NEXTVAL, 'hong');

INSERT INTO tbl_board
VALUES(board_seq.NEXTVAL, 'kim');

SELECT * FROM tbl_board;

-- 인덱스 이름 변경
ALTER INDEX SYS_C007333
RENAME TO tbl_board_idx;

인덱스를 사용할 때 권장되는 부분

컬럼이 WHERE 또는 JOIN 조건에서 자주 사용되는 경우
열이 광범위한 값을 포함하는 경우
테이블이 대형인 경우
타겟 컬럼이 많은 수의 null 값을 포함하는 경우
테이블이 자주 수정되고, 이미 하나 이상의 인덱스를 가지고 있는 경우 권장하지 않는다.

권한(Privilege)

권한은 특정 SQL 문장을 실행하기 위한 권리이다.

사용자는 데이터베이스에 액세스하기 위해 시스템 권한(System Privilege)이 필요하고,
데이터베이스에서 객체의 내용을 조작하기 위해 객체 권한(Object Privilege)이 필요하다.

시스템 권한	승인 작업
CREATE USER	다른 오라클 사용자 생성을 피 수여자에게 허용(DBA 역할을 위해 필요한 권한)
DROP USER	다른 사용자 제거
DROP ANY TABLE	임의의 스키마에서 테이블 제거
BACKUP ANY TABLE	export 유틸리티로 임의의 스키마에서 임의의 테이블 백업

-- 사용자 계정 확인
SELECT * FROM all_users;

-- 계정 생성 명령
CREATE USER user1 IDENTIFIED BY user1;

DCL

GRANT(권한 부여)
REVOKE(권한 회수)

CREATE USER : 데이터베이스 유저 생성 권한
CREATE SESSION : 데이터베이스 접속 권한
CREATE TABLE : 테이블 생성 권한
CREATE VIEW : 뷰 생성 권한
CREATE SEQUENCE : 시퀀스 생성 권한
ALTER ANY TABLE : 어떠한 테이블을 수정할 수 있는 권한
SELECT ON [table name] TO [user]; : 특정 테이블만 조회할 수 있는 권한
RESOURCE, CONNECT, DBA TO [user]

GRANT CREATE SESSION TO user1;
GRANT CONNECT, RESOURCE, DBA TO user1;

ALTER USER user1 IDENTIFIED BY abc1234;

테이블 스페이스는 데이터베이스 객체 내 실제 데이터가 저장되는 공간
테이블 스페이스를 생성하면 지정된 경로에 실제 파일로 정의한 용량만큼의 파일이 생성이 되고, 데이터가 물리적으로 저장된다.
테이블 스페이스의 용량이 초과되면 프로그램이 비정상적으로 동작한다.

-- USERS 테이블 스페이스를 기본 사용 공간으로 지정.
ALTER USER user1 DEFAULT TABLESPACE USER_TABLESPACE
QUOTA UNLIMITED ON USER_TABLESPACE;

-- 테이블 스페이스 내의 객체를 전체 삭제
DROP TABLESPACE USER_TABLESPACE INCLUDING CONTENTS;
-- 테이블 스페이스 파일까지 한번에 삭제
DROP TABLESPACE USER_TABLESPACE INCLUDING CONTENTS AND DATAFILES;

-- 사용자 계정 삭제 (종속된 객체나 데이터가 존재 시 삭제 불가능)
DROP USER user1;
-- 계정 삭제 시 테이블, 시퀀스 등 종속되어 있는 모든 객체도 함께 삭제
DROP USER user1 CASCADE;

데이터베이스 모델링

업무 파악
- 제공하려는 서비스나 무엇을 만들지 파악한다.
개념적 데이터 모델링
- 어떤 개념과 속성이 있는지, 개념들이 어떻게 상호작용을 하는지 모델링 한다.(스케치)
- ER 다이어그램을 통해 전체적인 흐름을 파악한다.
논리적 데이터 모델링
- 관계형 데이터 베이스에 맞게 표로 전환하는 작업을 한다.
물리적 데이터 모델링
- 데이터 베이스를 선택하고, 실제 테이블을 생성한다.

Entity: Table이 된다.
Attribute(속성): Column이 된다.
Relation: 엔티티들의 관계로 pk, fk으로 연결 된다.

정규화(Normalization)

정제되지 않은 데이터를 관계형 데이터베이스와 어울리게 만들어주는 방법이다.

정규화는 1 정규화 ~ 5 정규화까지 있으며, 실무에서는 대체로 1~3 정규화까지의 과정을 거친다.

제 1 정규화(First Nomal Form)

도메인이 원자값

제 2 정규화(Second Nomal Form)

부분적 함수 종속 제거

제 3 정규화(Thired Nomal Form)

이행적 함수 종속 제거

BCNF(Boyce-codd Nomal Form)

결정자가 키 값이어야 한다. (보통 1정규화 과정에서 Cardinality를 생각하면 완료 된다.)

제 4 정규화

다치 종속 제거

제 5 정규화

조인 종속성에 의해 정규화

'Database > Oracle' 카테고리의 다른 글

[국비] SQL 내용정리 Day07 (0)	2021.12.14
[국비] SQL 내용정리 Day06 (0)	2021.12.13
[국비] SQL 내용정리 Day04 (0)	2021.12.09
[국비] SQL 내용정리 Day03 (0)	2021.12.08
[국비] SQL 내용정리 Day02 (0)	2021.12.07

이한 blog입니다. 방문해 주셔서 감사합니다.

[국비] SQL 내용정리 Day05

뷰(View)

View를 사용하는 이유

VIEW 수정 (CREATE OR REPLACE VIEW 구문)

VIEW 삭제(DROP VIEW구문)

View의 주의사항

WITH CHECK OPTION (조건 제약 컬럼)

뷰의 규칙

SEQUENCE (순차적으로 증가하는 값)

INDEX

index의 장/단점

인덱스를 사용할 때 권장되는 부분

권한(Privilege)

DCL

데이터베이스 모델링

정규화(Normalization)

'Database > Oracle' 카테고리의 다른 글

댓글

티스토리툴바

[국비] SQL 내용정리 Day05

뷰(View)

View를 사용하는 이유

VIEW 수정 (CREATE OR REPLACE VIEW 구문)

VIEW 삭제(DROP VIEW구문)

View의 주의사항

WITH CHECK OPTION (조건 제약 컬럼)

뷰의 규칙

SEQUENCE (순차적으로 증가하는 값)

INDEX

index의 장/단점

인덱스를 사용할 때 권장되는 부분

권한(Privilege)

DCL

데이터베이스 모델링

정규화(Normalization)

'Database > Oracle' 카테고리의 다른 글

관련글

댓글

티스토리툴바