[데이터베이스] 14일차 - 테이블 생성.변경.삭제.관리, 제약조건, 데이터 삽입.수정.삭제,

2012/08/14 - [Dev. 데이터베이스/SQL 예제] - [ORACLE SQL 문법] 테이블생성.수정.삭제, 제약조건

테이블의 생성

테이블은 실제로 데이터들이 저장되는 곳 이라고 생각하면 쉽게 이해 할 수 있으며, CREATE TABLE 명령어를 이용해서 테이블을 생성 할 수 있다.

테이블이란?

- 테이블은 데이타베이스의 기본적인 데이타 저장 단위 이다.
- 데이타베이스 테이블은 사용자가 접근 가능한 모든 데이타를 보유하며 레코드와 컬럼으로 구성 된다.
- 관계형 데이타베이스가 아닌 예전의 데이타 베이스 용어에서는 파일과 테이블이, 필드와 컬럼이, 그리고 레코드와 행이 동일시 되었다.
- 테이블은 시스템내에서 독립적으로 사용되길 원하는 엔티티를 표현할수 있다. 예를 들면, 회사에서의 고용자나 제품에 대한 주문은 테이블로 표현 가능하다.
- 테이블은 두 엔티티간의 관계를 표현할 수 있다. 즉 테이블은 고용자와 그들의 작업 숙련도 혹은 제품과 주문과의 관계를 표현하는데 사용될 수 있다.
- 테이블내에 있는 외래 키 (ForeIgn Key)는 두 엔티티 사이의 관계를 표현하는데 사용 된다.
- 컬럼 : 테이블의 각 컬럼은 엔티티의 한 속성을 표현 한다
- 행(ROW, 레코드) : 테이블의 데이타는 행에 저장 된다

※ 테이블 생성시 제한사항과 고려할점

- 테이블 이름과 컬럼은 항상 알파벳 문자로 시작해야 하며 A~Z까지의 문자, 0~9까지의 숫자, 그리고 $,#,_(Under Bar)를 사용할 수 있다. (공백 사용 불가능)
- 테이블의 컬럼 이름은 30자를 초과할수 없고, 예약어를 사용할수 없다. (버전없이 되면서 컬럼수 제한 변경 됨)
- 오라클 테이블 한 계정안에서 테이블 이름은 다른 테이블 이름과 달리 유사해야 한다.
- 한 테이블 안에서 컬럼이름은 같을 수 없으며 다른 테이블에서의 컬럼이름과는 같을수 있다.

테이블 생성 문법

USER생성문법

- schema : 테이블의 소유자
- table_name : 테이블 이름
- column : 컬럼의 이름
- datatype : 컬럼의 데이터 유형
- TABLESPACE : 테이블이 데이터를 저장 할 테이블스페이스
- PCTFREE : 블럭내에 이미 존재하고 있는 Row에 Update가 가능하도록 예약시켜 놓는 블럭의 퍼센트 값을 지정 한다.
- PCTUSED : 테이블 데이터가 저장될 블록의 행 데이터 부분의 크기를 퍼센트지로 지정 한다. PCTFREE에 의해 지정된 크기만큼 Block이 차면 PCTUSED 값보다 작아져야 새로운 행 삽입이 가능 하다.
- INITRANS : 하나의 데이터 블록에 지정될 초기 트랜잭션의 값을 지정 한다.
- MAXTRANS : 하나의 데이터 블록에 지정될 수 있는 트랜잭션 최대 수를 지정 한다.
- STORAGE : 익스텐트 스토리지에 대한 값을 지정 한다.
- LOGGING : 테이블에 대해 이후의 모든 작업이 리두 로그 파일 내에 기록 되도록 지정 한다
- NOLOGGING : 리두 로그 파일에 테이블의 생성과 특정 유형의 데이터 로드를 기록하지 않도록 지정 한다.
- PCTFREE, PCTUSED에 대한 자세한 강좌는 오라클 어드민 강좌의 6. Storage Structure => PCTFREE와 PCTUSED를 참고해 주세요
오라클 버전없이 되면서 PCTFREE, PCTUSED 설정 및 관리가 많이 변경 되었습니다.

테이블의 제약조건

제약조건 (Constraint) 이란?

제약조건이란 테이블에 부적절한 자료가 입력되는 것을 방지하기 위해서 여러 가지 규칙을 적용해 놓는거라 생각하면 된다. 간단하게 테이블안에서 테이터의 성격을 정의하는 것이 바로 제약조건 이다.

- 제약조건은 데이터의 무결성 유지를 위하여 사용자가 지정할 수 있는 성질 이다.
- 모든 제약조건은 데이터 사전(DICTIONARY)에 저장 된다.
- 의미있는 이름을 부여했다면 CONSTRAINT를 쉽게 참조할 수 있다.
- 표준 객체 명명법을 따르는 것이 좋다.
- 제약조건은 테이블을 생성할 당시에 지정할 수도 있고, 테이블 생성 후 구조변경(ALTER)명령어를 통해서도 추가가 가능하다.
- NOT NULL제약조건은 반드시 컬럼 레벨에서만 정의가 가능하다.

NOT NULL 조건

컬럼을 필수 필드화 시킬 때 사용 한다.

 
-- NOT NULL제약조건을 설정하면 ename 컬럼에는 꼭 데이터를 입력해야 한다.
-- emp_nn_ename을 제약조건 명으로 설정 하였다.
SQL> CREATE TABLE emp3(
     ename VARCHAR2(20)  CONSTRAINT emp_nn_ename NOT NULL );
        
-- 제약조건은 USER_CONSTRAINTS 뷰를 통해서 확인 할 수 있다.
SQL> SELECT CONSTRAINT_NAME
     FROM    USER_CONSTRAINTS
     WHERE  TABLE_NAME ='EMP3' ;

CONSTRAINT_NAME
-----------------------
emp_nn_ename

UNIQUE 조건

데이터의 유일성을 보장(중복되는 데이터가 존재할수 없다) 되고, 자동으로 인덱스가 생성 된다.

  	
--  deptno 컬럼에 UNIQUE 제약조건 생성
SQL> ALTER TABLE emp2
     ADD CONSTRAINT emp2_uk_deptno UNIQUE (deptno);

-- 제약 조건의 삭제
SQL> ALTER TABLE emp2
     DROP CONSTRAINT emp2_uk_deptno;

CHECK 조건

컬럼의 값을 어떤 특정 범위로 제한할 수 있다.

 	
-- comm 컬럼에 1에서 100까지의 값만을 가질수 있는 체크조건 생성	
SQL> ALTER TABLE emp2
     ADD CONSTRAINT emp2_ck_comm
     CHECK (comm >= 1 AND comm <= 100);

-- 제약 조건의 삭제
SQL> ALTER TABLE emp2
     DROP CONSTRAINT emp2_ck_comm;

-- 10000,20000,30000,40000,50000의 값만을 가질수 있는 체크조건 생성
SQL> ALTER TABLE emp2
     ADD CONSTRAINT emp2_ck_comm
     CHECK comm IN (10000,20000,30000,40000,50000);

DEFAULT(컬럼 기본값) 지정

데이터를 입력 하지 않아도 지정된 값이 기본으로 입력 된다.

 
-- hiredate 컬럼에 값을 입력하지 않아도 오늘 날짜가 입력된다.
SQL> CREATE TABLE emp4(
     ... (컬럼생략) ...,
     hiredate DATE DEFAULT SYSDATE );

PRIMARY KEY 지정

- 기본키는 UNIQUE 와 NOT NULL의 결합과 같다.
- 기본키는 그 데이터 행을 대표하는 컬럼으로서의 역할을 수행하여 다른 테이블에서 외래키들이 참조할 수 있는 키로서의 자격을 가지고 있다. 이를 참조 무결성이라 한다.
- UNIQUE 조건과 마찬가지로 기본키를 정의하면 자동으로 인덱스를 생성하며, 그 이름은 기본 키 제약 조건의 이름과 같다.
- INDEX: 검색 키로서 검색 속도를 향상 시킨다.(UNIQUE,PRIMARY KEY 생성시 자동적으로 생긴다.)

 
-- PRIMARY KEY 생성 예제
SQL> CREATE TABLE emp5(
     empno NUMBER CONSTRAINT emp5_pk_empno PRIMARY KEY );

 
-- ALTER TABLE 명령어로 PRIMARY KEY 생성 예제
ALTER TABLE emp2
ADD CONSTRAINT emp2_pk_empno  PRIMARY KEY (empno) ;

FOREIGN KEY(외래 키)지정

- 기본키를 참조하는 컬럼 또는 컬럼들의 집합이다.
- 외래키를 가지는 컬럼의 데이터 형은 외래키가 참조하는 기본키의 컬럼과 데이터형과 일치해야 한다. 이를 어기면 참조무결성 제약에의해 테이블을 생성할수 없다.
- 외래키에 의해 참조되고 있는 기본 키는 삭제 할 수 없다.
- ON DELETE CASCADE 연산자와 함께 정의된 외래키의 데이터는 그 기본키가 삭제 될 때 같이 삭제 된다.

 
-- emp 테이블의 deptno 컬럼이 dept 테이블에 deptno 컬럼을 참조하는 외래키 생성
SQL> ALTER TABLE emp2 ADD CONSTRAINT emp2_fk_deptno
     FOREIGN  KEY (deptno) REFERENCES dept(deptno);

제약 조건의 확인

- USER_CONS_COLUMNS : 컬럼에 할당된 제약 조건 조회.
- USER_CONSTRAINTS　: 유저가 소유한 모든 제약 조건 조회.

  
SQL> SELECT SUBSTR(A.COLUMN_NAME,1,15) COLUMN_NAME,　　
            DECODE(B.CONSTRAINT_TYPE,
                'P','PRIMARY KEY',
                'U','UNIQUE KEY',
                'C','CHECK OR NOT NULL',
                'R','FOREIGN KEY') CONSTRAINT_TYPE,　　
            A.CONSTRAINT_NAME　　
     FROM　　USER_CONS_COLUMNS A, USER_CONSTRAINTS B　　
     WHERE A.TABLE_NAME = UPPER('&table_name')　
       AND A.TABLE_NAME = B.TABLE_NAME　　
       AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME　　
     ORDER BY 1;　　

-- 테이블 명을 입력 하면 됩니다.
table_name의 값을 입력하십시오: emp2
 
COLUMN_NAME         CONSTRAINT_TYPE    CONSTRAINT_NAME
------------------- -----------------  --------------
DEPTNO              CHECK OR NOT NULL  SYS_C001362   
                    FOREIGN KEY        EMP2_FK_DEPTNO
EMPNO               PRIMARY KEY        EMP2_PK_EMPNO
ENAME               CHECK OR NOT NULL  EMP2_NN_ENAME
MGR                 UNIQUE KEY         EMP2_UP_MGR

테이블의 관리

테이블 컬럼의 관리

테이블의 컬럼은 ADD, MODIFY, DROP연산자를 통해서 관리 할 수 있습니다.

ADD 연산자

테이블에 새로운 컬럼을 추가 할 때 사용 한다.

 
-- VARCHAR2의 데이터 형을 가지는 addr 컬럼을 emp 테이블에 추가
SQL> ALTER TABLE emp ADD (addr VARCHAR2(50));

MODIFY 연산자

테이블의 컬럼을 수정 하거나 NOT NULL컬럼으로 변경 할 수 있으며, 컬럼이 이미 테이터를 가지고 있을 경우 다른 데이터형으로 변경이 불가능 하다.

 
-- ename 컬럼을 VARCHAR2 50자리로 수정한 예제.
SQL> ALTER TABLE emp MODIFY (ename VARCHAR2(50));
SQL> ALTER TABLE emp MODIFY (ename VARCHAR2(50) NOT NULL);

DROP 연산자

테이블 컬럼을 삭제 하거나, 테이블의 제약 조건을 삭제 할 때 사용 한다.

 
-- 컬럼의 삭제 문법 
SQL> ALTER TABLE table_name DROP COLUMN column_name

-- 제약 조건의 삭제 예제
SQL> ALTER TABLE emp DROP PRIMARY KEY ;

-- CASCADE 연산자와 함께 사용하면 외래키에 의해 참조되는 기본키도 삭제 할 수 있다.
SQL> ALTER TABLE emp DROP CONSTRAINT emp_pk_empno CASCADE;

기존 테이블의 복사

- 기존 테이블을 부분, 또는 완전히 복사할 때에 서브쿼리를 가진 CREATE TABLE 명령어를 사용해서 쉽게 복사 할 수 있다.

- 하지만 제약 조건, 트리거, 그리고 테이블 권한은 새로운 테이블로 복사되지 않는다.

- 제약조건은 NOT NULL 제약 조건만 복사 된다.

기존 테이블의 복사

  
-- 한번 실습해 보세요.
SQL> CREATE TABLE emp2
     AS SELECT * FROM emp;

테이블의 테이블스페이스 변경

Oracle 8i부터는 ALTER TABLE ~ MOVE TABLESPACE 명령어로 쉽게 테이블의 테이블스페이스를 변경 할 수 있다.

테이블의 테이블스페이스 변경

  
-- 한번 실습해 보세요. (test라는 테이블스페이스가 있어야 겠죠)
SQL> ALTER TABLE emp
     MOVE TABLESPACE test;

데이터의 삽입, 수정, 삭제

INSERT

INSERT명령어는 테이블 안에 데이터를 삽입하는 역할을 한다.

INSERT 문법

INSERT

- 실제 데이터는 VALUES 괄호()안에 입력하고 문자열은 단일 따옴표(' ')로 둘러싼다.
- 각각의 테이터 구분은 ","로 한다.
- 테이블 이름 옆에 ()생략시에는 자동으로 모든 컬럼을 VALUES()안에 입력 시킨다.

UPDATE

테이블 안의 데이터를 수정 한다.

UPDATE

  
-- 사원번호가 7902번인 사원의 부서 번호를 30으로 수정
SQL> UPDATE emp
     SET deptno = 30
     WHERE empno = 7902;

-- 부서번호 20의 사원들 급여가 10% 인상됨
SQL> UPDATE emp
     SET sal = sal * 1.1
     WHERE deptno = 20;

-- 모든 사원의 입사일을 오늘로 수정
SQL> UPDATE emp
     SET hiredate = SYSDATE

DELETE

사용하지 않는 데이터를 삭제 한다.

DELETE

-- 사원번호가 7902번인 사원의 데이터를 삭제.      
SQL> DELETE FROM emp
     WHERE empno = 7902 ;

-- 평균급여보다 적게 받는 사원 삭제
SQL> DELETE FROM emp
     WHERE sal < (SELECT AVG(sal) FROM emp);
        
-- 모든 행이 삭제
SQL> DELETE FROM emp;

[출처] 오라클클럽 / 오라클강좌 / SQL강좌

저작자표시 비영리

'Dev. 640시간 뭉개기 > 강의내용정리' 카테고리의 다른 글

[데이터베이스] 16일차 - 함수(숫자, 문자), 정규식 (0)	2012.08.17
[데이터베이스] 15일차 - select(where,like,order by,in,between), 함수 (0)	2012.08.16
[데이터베이스] 13일차 - 오라클 설치, 명령어, 권한, 롤 (0)	2012.08.13
[자바] 11일차 - 열거형(Enumeration), 인코딩(Encoding), 파일 입출력 (0)	2012.08.08
[자바] 10일차 - 배열, 컬렉션(ArrayList, HashMap), 예외처리(try~catch, throw, 사용자예외클래스) (0)	2012.08.07

소림사의 홍반장!