23-02-08 데이터 베이스

※ 테이블

예제) 연락처 정보를 저장하기 위한 주소록 테이블을 생성

CREATE TABLE address
(id NUMBER(3),
name VARCHAR2(50),
addr VARCHAR2(100),
phone VARCHAR2(30),
email VARCHAR2(100));

2.DEFAULT

 2-1) 기능

  - 칼럼의 입력값이 생략될 경우에 NULL 대신에 입력되는 기본값을 지정하기 위한 기능

  - 기본값 : 리터럴값, 표현식, SQL함수, SYSDATE, USER사용

  - 칼럼이나 의사컬럼(NEXTVAL, CURRVAL)은 사용할 수 없음

 

 2-2) DESR[RIBE]명령어

  - 테이블의 생성 여부와 테이블의 구조를 확인하기 위한 명령어

  - 

 

 2-3) 서브쿼리를 이용한 테이블 생성

  -서브쿼리의 출력결과가 테이블의 초기 데이터로 삽입

  - CREATE TABLE 명령문에서 지정한 컬럼수와 데이터 타입과 반드시 일치

  - 무겨성 제약조건은 NOT NULL 조건만 복사

  - 디폴트 옵션에서 정의한 값은 그대로 복사

 

3) 테이블 구조 복사

 3-1) 기존테이블의 구조만 복사

  - 서브쿼리를 이용한 테이블 생성시 데이터는 복사하지않고 기존 테이블의 구조만 복사가능

  - 서브쿼리의 WHERE조건절에 거짓이 되는 조건을 지정하여 출력 결과 집합(공집합)이 생성되지 않도록 지정

 

예제) 주소록 테이블에서 ID,NAME 칼럼만 복사하여 ADDR_FOURTH테이블을 생성 단, 데이터는 복사하지않는다

CREATE TABLE ADDR_FOURTH AS
SELECT ID, NAME FROM ADDRESS WHERE 0 = 1;

DROP TABLE ADDR_FOURTH; -- 테이블을 잘못 만들었을경우

SELECT * FROM ADDR_FOURTH;

4, 테이블 구조변경

 1) 테이블 칼럼 추가

  1-1) 개요

  - 칼럼추가, 삭제, 타입이나 길이의 재정의와 같은 작업

 

 2) 테이블 컬럼 삭제

 

 3) 테이블 칼럼 변경

  - 테이블의 칼럼의 타입, 크기, 기본값 변경 가능

  - ALTER TABLE.....MODIFY 명령문 이용

 예제)주소록 테이블에서 PHONE 칼럼의 데이터 타입의 크기를 50으로 증가

ALTER TABLE ADDRESS
MODIFY PHONE VARCHAR2(50);

 

 4) 테이블 이름 변경

  4-1) 기능

    - RENAME 명령문 사용

    - 뷰, 시퀀스, 동의어 등과 같은 데이터베이스 객체의 이름 변경 가능

 

 5) 테이블 삭제

  5-1) 기능

  - 기존 테이블과 데이터 모두 삭제

  - DROP TABLE 명령문 사용

  - 삭제된 테이블 칼럼에 대해 생성된 인덱스도 함께 삭제

  - 삭제된 테이블과 관련된 뷰와 동의어 'invalid' 상태

  - 삭제할 테이블의 기본 키나 고유 키를 다른 테이블에서 참조하고 있는 경우 삭제 불가능

     - 참조하는 테이블(자식 테이블)을 먼저 삭제

     - drop table 명령문 마지막에 cascade constraints옵션을 사용하여 무결성 제약조건을 동시에 삭제

 

 6) TRUNCATE

  6-1) 기능

   - 테이블 구조는 그대로 유지하고, 테이블의 데이터와 할당된 공간만 삭제

 

 6-2) DELETE 명령문과의 차이

  -DELETE

    기존 테이터만 삭제하는 명령이며, ROLLBACK 가능

    WHERE 절을 이용하여 특정 행만 삭제 가능

 

   - TRUNCATE

      기존 데이터 삭제뿌만아니라, 물리적인 저장공간까지 반환

      DDL문이므로 ROLLBACK 불가능

      WHERE절을 이용하여 특정 행만 삭제하는 것이 불가능

 

 

7. DELETE, DROP, TRUNCATE 비교

 

p.497 표 삽입

 

8. 주석 추가

 8-1) 기능

  - 테이블, 칼럼에 주석 추가

  - 2000바이트까지 주석을 추가

  - comment on table ... is 명령문 이용

  - 추가된 주석 확인

    ALL_COL_COMMENTS, USER_COL_COMMENTS

 

예제) 

-- 주소록 테이블에서 '고객 주소록 관리하기 위한 테이블' 이라는 주석 추가
COMMENT ON TABLE ADDRESS IS '고객 주소록 관리하기 위한 테이블';

-- 주소록 테이블에 NAME칼럼에 '고객이름'이라는 주석을 추가
COMMENT ON COLUMN ADDRESS.NAME IS '고객이름';

 

※ 뷰

 1. 개념

 - 하나 이상의 기본 테이블이나 다른 뷰를 이용하여 생성되는 가상 테이블

  * 가상테이블

     - 테이블은 디스크에 공간이 할당되어 데이터를 저장

     - 뷰에 대한 정의 저장(텍스트)

     - 디스크 저장공간 할당이 안됨

 - 전체의 데이터중에서 일부만 접근할 수 있도록 제한

 - 테이블에서 파생된 객체 테이블과 유사하게 사용

 - 뷰에 대한 수정 결과는 뷰를 정의한 기본 테이블에 적용

 - 뷰를 정의한 기본 테이블에서 정의된 무결성 제약 조건 상속

 - 뷰는 읽기전용

 

 2. 장점

 - 데이터를 보호하기 위한 보안(security)

 - 사용자 편의성 (flexibillty)

CREATE VIEW VIEW_STU AS
SELECT STUDNO, S.NAME SNAME, PROFNO, P.NAME PNAME, S.DEPTNO, DNAME
FROM STUDENT S
LEFT JOIN PROFESSOR P USING(PROFNO)
JOIN DEPARTMENT D ON S.DEPTNO = D.DEPTNO
ORDER BY 1;

 

권한이 없음

system 에서 권한부여

GRANT CREATE VIEW TO SAMPLE;

 

CREATE VIEW VIEW_STU AS
SELECT STUDNO, S.NAME SNAME, PROFNO, P.NAME PNAME, S.DEPTNO, DNAME
FROM STUDENT S
LEFT JOIN PROFESSOR P USING(PROFNO)
JOIN DEPARTMENT D ON S.DEPTNO = D.DEPTNO
ORDER BY 1;

SELECT * FROM VIEW_STU;

SELECT * FROM USER_VIEWS;

 

↓OR REPLACE 구문 삽입 (없으면 만들고 있으면 변경하라는)

CREATE OR REPLACE VIEW VIEW_STU AS
SELECT STUDNO, S.NAME SNAME, PROFNO, P.NAME PNAME, S.DEPTNO, DNAME
FROM STUDENT S
LEFT JOIN PROFESSOR P USING(PROFNO)
JOIN DEPARTMENT D ON S.DEPTNO = D.DEPTNO
ORDER BY 1 DESC;

 3. 뷰의 개념도

 

4. 뷰의 종류

 4-1) 단순뷰

  - 하나의 기본 테이블에 의해 정의한 뷰

  - 단순 뷰에 DML명령문의 실행 결과 기본 테이블에 반영

  4-2) 복합 뷰

  - 두개이상의 기본 테이블로 구성한 뷰

 

5. 뷰 생성

 5-1) 개념

  - CREATE VIEW 명령문 사용

  - 뷰 생성시 칼럼 이름을 명시하지 않으면 기본 테이블의 칼럼 이름을 상속

  - 함수나 표현식에 의해 정의된 칼럼은 별도로 이름을 명시(별명 사용)

 

OR REPLACE : 기존 뷰와 동일한 이름으로 뷰를 재생성 하는 경우

FORCE : 기본 테이블의 존재 여부에 상관없이 강제로 뷰 생성

ALIAS : 기본 테이블의 칼럼 이름과 다르게 지정한 뷰의 칼럼 이름

 

6. 인라인 뷰

 6-1) 개요

  - FROM절에서 서브쿼리를 사용하여 생성한 임시 뷰

SELECT * FROM 
(SELECT STUDNO, S.NAME SNAME, PROFNO, P.NAME PNAME, S.DEPTNO, DNAME
FROM STUDENT S
LEFT JOIN PROFESSOR P USING(PROFNO)
JOIN DEPARTMENT D ON S.DEPTNO = D.DEPTNO
ORDER BY 1);

7. 뷰의 내부 처리 과정

  7-1) 뷰에 대한 질의의 내부적인 처리과정

    - USER_VIEW데이터 딕셔너리에서 뷰에 대한 정의를 ㅈ회

    - 기본 테이블에 대한 뷰의 접근 권한을 확인

    - 뷰에 대한 질의를 기본 테이블에 대한 질의로 변환

    - 기본 테이블에 대한 질의를 통해 데이터 검색

    - 검색된 결과를 출력

 

 7-2) 뷰의 변경

 

 7-3 뷰에 대한 데이터 조작

 

 7-4) 뷰의 삭제

  DROP VIEW view;

 

※ 테이블 관리

 1. 데이터 사전

  1-1) 개요

    - 사용자와 데이터베이스 자원을 효율저긍로 관리하기 위한 다양한 정보를 저장하는 시스템 테이블의 집합

    - 사전 내용의 수정은 오라클 서버만 가능

    - 데이터 사전은 읽기전용 뷰에 의해 데이터 사전의 내용을 조회만 가능

    - 실무에서는 테이블, 칼럼, 뷰 등과 같은 정보를 조회하기 위해 사용

 

 1-2) 데이터 사전의 관리 정보

  - 데이터베이스의 물리적 구조와 객체의 논리적 구조

  - 사용자에게 부여된 접근 권한과 롤(권한의 묶음)

  - 무결성 제약조건에 대한 정보

  - 칼럼별로 지정된 기본값

  - 

 

2. 데이터 사전의 종류

 2-1) 개요

  - 다수의 사용자가 데이터를 공유

  - 읽기 전용 뷰로 구성

  - 데이터베이스 관리자나 사용자에게 데이터 사전에 저장된 정보 조회 허용

  -용도에 따라 user, all, dba접두어를 사용하여 분류

 2-2) USER_ 데이터 사전 뷰

  - 일반 사용자가 가장 밀접하게 관련된 뷰

  - 자신이 생성한 테이블, 인덱스, 뷰, 동의어 등의 객체나 해당 사용자에게 부여된 권한 정보 조회

 

 2-3) ALL_데이터 사전 뷰

  - 데이터베이스 전체 사용자와 관련된 뷰

  - 해당 객체의 소유자를 확인 가능 (OWNER칼럼 존재

  - 사용자는 ALL_ 사전뷰를 이용하여 접근할 수 있는 모든 객체에 대한 정보 조회 가능

 

[데이터베이스 관리를 위해 자주 사용하는 데이터 사전 뷰]