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

 

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

 

테이블의 생성

 

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

 

 

테이블이란?

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

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

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

 

 

테이블 생성 문법

• - 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 문법

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

 

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

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

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

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

 

 

 

 

 

 

 

 

 

 

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