오라클 DB 용어, 명령문 정리06 -테이블 명령어, 뷰, 인라인뷰

2019. 05. 24 필기

 

<어제자 복습>

(참고) 오라클 DB 용어, 명령문 정리05 - http://bitly.kr/1wvYSb 

>> 눌러서 내용보기 <<

DDL(Data Definition Language)

: 데이터 객체를 관리하기 위한 명령어(※ 데이터베이스 객체 : 테이블, 뷰, 인덱스, 시퀀스, 동의어, 사용자 등)

- create, drop, alter, rename, truncate 등의 명령어가 있다.

>> CREATE TABLE table_name (column datatype(size) DEFAULT default_value condition, ...)

 

DEFAULT 옵션
- 해당 테이블에 행을 입력할 때, 해당 컬럼에 값을 지정하지 않은 경우 자동으로 디폴트 값이 입력되어 NULL값이 저장되는 것을 방지할 수 있다.
- 고정된 값만을 가지는 컬럼에 대해서도 사용 가능하다.

 

자료형 -- MySQL의 경우, 숫자형은 integer 또는 double / 문자형은 varchar 또는 text / 날짜는 date 등, 오라클과 조금씩 다른 자료형을 쓴다.

 

제약조건 - 컬럼 수준과 테이블 수준이 있다. 한 테이블에 여러 개의 제약조건이 존재할 수 있다.(그러므로 이름을 붙여주는 게 좋다.)

C(not null & check) -- not null의 경우 컬럼수준, check의 경우 테이블수준으로 걸어주길 권장

U(unique) -- 컬럼, 테이블 수준 모두 OK. 동시에 줄 수도 있다.

P**(primary key) -- 컬럼의 값들에 고유성을 부여하여 겹치는 게 없도록 해줌. 기본키라고도 함. 한 테이블당 한 개의 기본키 존재 가능

R*(foreign key) -- 다른 테이블을 참조하기 위해 사용. 각 테이블에 PK가 없으면 참조 자체가 불가능하다. 테이블을 사용할 때마다 참조 테이블을 거쳐야 하기 때문에 속도 면에서 불리함. 개발자들이 싫어한다.

 

 

< 테이블 삭제 관련 명령어 >

DROP

: 테이블을 삭제하는 명령어

>> [형식] DROP TABLE table;

-- 테이블에 저장된 행, 객체 등등도 함께 삭제되기 때문에 조심해야 한다.

-- 한 번에 여러개 테이블을 삭제할 수 없다.(콤마도 소용 없어!)

 

 

FLASHBACK

: 휴지통에 버려졌던 테이블을 복구하는 명령어

>> [형식] FLASHBACK TABLE table TO BEFORE DROP;

-- 오라클에서는 테이블을 삭제할 경우 휴지통(RECYCLEBIN)에 테이블과 테이블 관련 객체를 일시적으로 저장

-- 삭제 테이블(휴지통에 저장된 테이블) 복구 가능 >> 종속된 개체는 복구되면 recyclebin name으로 복구되기 때문에 다시 이름을 설정해주어야 한다.

 

 

PURGE

: 휴지통에 존재하는 특정 테이블 또는 모든 테이블을 싹 밀어버리는 명령어

 

>> [형식] PURGE TABLE table;

: 오라클 휴지통에 존재하는 특정 테이블 제거  -- 테이블에 종속된 객체도 같이 제거

>> [형식] PURGE RECYCLEBIN;

: 오라클 휴지통 비우기 --오라클 휴지통에 존재하는 모든 객체 제거

 

>> [형식] DROP TABLE table PURGE;

: 오라클 휴지통으로 이동하지 않고 삭제

 

< 휴지통과 관련된 기타 명령어 >

SELECT * FROM TABS; -- 현재 테이블 목록 확인 

SELECT * FROM TAB; -- 휴지통 목록까지 보여줌(테이블명이 BIN으로 시작)

SHOW RECYCLEBIN; -- 휴지통에 존재하는 객체 목록 확인(삭제한 테이블에 종속된 객체 목록)

 

 

TRUNCATE

: 테이블 초기화 명령어. 테이블 생성 직후의 상태로 구현하기 위한 명령

-- 즉, 테이블에 저장된 행을 삭제하는 명령어
>> [형식] TRUNCATE TABLE table;

 

 

DELETE
--BONUS 테이블에 저장된 모든 행 삭제
>> [형식] DELETE FROM table;

 

 

 

< 테이블 변경 관련 명령어 >

RENAME

: 테이블 이름을 변경하는 명령어

>> [형식] RENAME table_origin TO table_change;

 

 

ALTER

: 테이블 구조를 변경하는 명령어 -- 애초에 설계 단계에서 잘 설계하는 게 중요하다.

>> [형식] ALTER TABLE table change_order;

           -- 컬럼 추가-제거, 컬럼의 자료형&크기&기본값 변경, 제약조건 추가-제거 가능

           -- 참고로 UPDATE 명령어는 '컬럼값' 변경이니 헷갈리지 말 것

 

< 테이블 구조 변경명령어 - chage_order에 올 수 있는 명령어들 >

컬럼 추가

ADD(column datatype [DEFAULT default] [constraint])

          -- 테이블의 제약조건 설정 : 테이블 수준의 제약조건은 ADD 기능 이용

 

< 컬럼의 성질 변경 - chage_order에 올 수 있는 명령어들  >

: 컬럼의 자료형 또는 크기, 기본값 변경 또는 컬럼 수준의 제약조건 설정

자료형 종류변경 >> MODIFY(column datatype_new(size))

          -- 변경하고자 하는 컬럼에 이미 값이 존재하는 경우엔 자료형을 변경할 수 없다.

자료형 크기변경 >> MODIFY(column datatype_origin(size))

          -- 변경하고자 하는 컬럼에 이미 값이 존재해도 저장크기는 변경 가능하다.

          -- 하지만, 이미 존재하는 값보다 더 작은 사이즈로는 변경 불가능하다.

컬럼 수준 제약조건 설정 >> MODIFY column datatype CONSTRAINT constrt_name constrt_type;

테이블 수준 제약조건 설정 >> DROP {PRIMARY KEY|CONSTRAINT constraint};

 

컬럼명 변경 >> RENAME COLUMN column_origin TO column_chage;

컬럼 제거 >> DROP COLUMN column;

 

 

DISABLE constraint;

: 제약조건을 비활성화 처리하는 명령어

-- FOREIGN KEY 제약조건은 부모테이블의 PK 제약조건이 설정된 컬럼을 반드시 참조
-- 부모테이블의 PK 제약조건을 비활성화 처리할 경우 에러 발생
-- 부모테이블에 부여된 PK 제약조건을 비활성화 처리하고자 할 경우 CASCADE 키워드를 사용하면 자식테이블의 FK 제약조건이 자동으로 비활성되어 처리
-- 부모테이블에 설정된 비활성화된 PK 제약조건을 활성화 처리 후 자식테이블에 설정된 비활성화된 FK 제약조건을 활성화 처리
-- 부모테이블의 PK 제약조건을 제거하고자 할 경우 자식테이블에 설정된 FK 제약조건에 의해 에러 발생
-- CASCASE 키워드를 이용하여 부모테이블의 PK 제약조건을 제거할 경우 자식테이블의 FK 제약조건 제거

 

 

 

< 뷰 VIEW >

뷰 VIEW란?

: 테이블을 바탕으로 생성되는 가상의 테이블(물리적인 테이블은 무조건 'TABLE' 한 가지 뿐)

-- 원하는 결과를 더 쉽게 검색해주기 때문에 사용한다.

 

뷰를 사용하는 이유

1) 자주 사용하는 검색 명령을 뷰로 생성하여 손쉽게 검색 -- 마치 단축키 같은 느낌

    -- 테이블 조인 검색 결과의 복합뷰 사용

2) 뷰를 이용하여 보다 쉬운 보안 설정 가능(권한관리가 쉬워짐) -- 특정 행만 따와서 만든 뷰만 공개할 수 있음
    -- 테이블은 기본적으로 사용자 권한을 제공하지 않고 뷰를 이용하여 사용자 권한 제공

 

단순뷰(SIMPLE VIEW)

: 하나의 테이블을 바탕으로 생성된 뷰

-- 행 검색 뿐만 아니라 삽입, 삭제, 변경 가능 >> 실제 테이블에 적용

 

복합뷰(COMPLEX VIEW)

: 여러 개의 테이블을 바탕으로 생성된 뷰

-- 행 검색(JOIN 검색)

-- DISTINCT 및 그룹함수 사용 가능(단순뷰는 불가능)

 

 

■ 뷰 관련 시스템 권한 부여 >> 뷰 관리 가능

** 바탕화면의 시작 버튼 > cmd 입력 > 아래 명령어 차례대로 입력

** SYSDBA 사용자(SYS)로 접속하여 현재 접속 사용자(SCOTT)에서 뷰 관련 시스템 권한(CREATE VIEW) 부여 

SQLPLUS /NOLOG 
SQL> CONN SYS/SYS AS SYSDBA 
SQL> GRANT CREATE VIEW TO USERNAME;

※ 띄어쓰기까지 동일하게 입력할 것

 

■ 뷰 생성

[형식] CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view [(column, ...)]

        AS SELECT 검색대상 FROM table [WHERE condition] [WITH CHECK OPTION] [WITH READ ONLY];

-- 뷰 관련 시스템 권한을 부여 받으면 뷰 관리 가능

CREATE OR REPLACE : 생성하고자 하는 뷰가 존재하지 않으면 생성, 존재하면 삭제 후 재생성

FORCE : 검색결과가 존재하지 않아도 강제로 뷰를 생성하는 키워드 

WITH CHECK OPTION : 해당 뷰를 생성한 조건식의 컬럼값을 변경하지 못하도록 설정하는 기능

WITH READ ONLY : 검색기능만 제공. 즉 읽기전용.

 

■ 뷰 목록 확인

[형식] SELECT 검색대상 FROM USER_VIEWS;

-- USER_VIEWS : 현재 접속 사용자에게 뷰 정보를 제공하는 딕셔너리

 

■ 뷰 행 검색

[형식] SELECT * FROM view_name;

-- 테이블의 검색행 이용 

 

■ 뷰 행 삽입

[형식] INSERT INTO view_name VALUES(v1, v2, v3, ...)

-- 뷰 생성 테이블에 행 삽입 (단순뷰) 
-- 원래 테이블의 컬럼 중 뷰에서 사용하지 않는 컬럼에는 컬럼 기본값이 자동 저장

 

■ 뷰 행 삭제

[형식] DROP VIEW view_name;

-- 뷰를 생성한 테이블을 삭제해도 뷰는 삭제되지 않는다.

-- 하지만 이 경우, 뷰를 사용할 수 없다.

 

■ 뷰 행 정렬하여 검색(다중행 검색)

ROWNUM : 검색된 결과행에 차례대로 행번호를 부여하는 키워드 >> 기존 테이블과 다른 기준으로 나열 가능

ROW_NUM() OVER() 윈도우 함수를 이용 >> 행번호 제공하여 정렬

 

--EMP 테이블에서 모든 사원의 사원번호,사원이름,급여를 급여로 내림차순 정렬하여 검색 - 행번호 제공
--테이블에서 검색행에 행번호를 제공한 후 정렬 - 급여순으로 행번호가 차례대로 제공되지 않는다.

--EMP 테이블에서 행번호가 5보다 작은 사원의 행번호,사원번호,사원이름,급여를 급여로 내림차순 정렬하여 검색 
-- 윈도우 함수는 WHERE의 조건식에서 사용할 경우 에러 발생

--EMP_VIEW 검색 - 행번호 제공
SELECT ROWNUM, 다른 검색대상 FROM view_name;
-- table.* 또는 view.* : 테이블(뷰)의 모든 컬럼 표현

-- * 기호를 다른 검색대상과 같이 사용

 

INLINE VIEW ** 개발자에게 엄청중요!

: FROM문에서 서브쿼리를 이용해 일시적으로 뷰를 생성해 사용하는 기능

-- 일시적으로 만드는 것이기 때문에 실제로 만들어지는 뷰가 아니며, 권한 없이도 만들어 쓸 수 있다.

-- 인라인 뷰에도 얼리어스 부여 가능

 

< 인라인뷰를 사용하는 이유? >

-- 실제 테이블로 구현할 수 없는 기능을 인라인 뷰에서 기능할 수 있다.

-- 뷰라는 객체를 굳이 만들지 않고 명령에서만 사용할 수 있는 뷰를 만들어주기 위해 생성

   (자바의 anonymous 클래스와 비슷한 기능)

-- 오라클 사용자에게는 뷰를 만들 수 있는 권한이 없으므로(관리자만 가능) 인라인 뷰를 사용하는 경우가 있다.

   따라서 명령에서만 임시로 사용할 인라인뷰를 만든다.

-- 행번호를 부여해 비교할 수 있도록

        ** ROWNUM 키워드는 조건식에서

           <, <=  ---------- 사용 가능
           >, >=, =, <> --- 사용불가

           ==> 따라서, 인라인뷰를 만들어 미리 만들어진 row number를 기준으로 정렬하면 해결됨

           (rownum에는 반드시 컬럼명을 만들어줘야 한다.) ※MySQL은 limit 구문 사용