[Oracle] 데이터 정의어 (DDL : Data Definition Language)

2019. 9. 29. 21:47

공부했던 자료 정리하는 용도입니다.

재배포, 수정하지 마세요.

예제는 Oracle Database에서 기본으로 제공되는 SCOTT계정 데이터로 진행됩니다.

데이터 정의어(DDL : Data Definition Language)

DDL (Data Definition Language)은 데이터베이스 데이터를 보관하고 관리하기 위해 제공되는 여러 객체(object)의 생성·변경·삭제 관련 기능을 수행한다. DDL 은 DML (데이터 조작어)과 달리 명령어를 수행하자마자 데이터베이스에 수행한 내용이 바로 반영되는 특성(자동으로 COMMIT )이 있다. ROLLBACK 을 통한 실행 취소가 불가능하기 때문에 사용할 때 주의해야 한다.

1. CREATE

CREATE TABLE 소유 계정.테이블이름(	-- 소유계정은 생략가능(생략하면 혅 접속해 있는 계정소유의 테이블이 만들어짐)
    열1 이름 열1 자료형,
    열2 이름 열2 자료형,
    ...
    열N 이름 열N 자료형
);

CREATE 문은 오라클 데이터베이스 객체를 생성하는 데 사용하는 명령어이다. 그 중에서도 CREATE TABLE 명령어는 테이블을 만들 때 사용하는 대표적인 DDL (데이터 정의어)이다. 소유 계정 이름은 생략 가능하다.(생략하면 현재 접속해 있는 계정 소유의 테이블이 만들어진다.)

테이블 생성 방법

자료형을 정의하여 생성
기존 테이블의 열 구조와 데이터를 복사하여 생성(데이터 전체 복사)
기존 테이블의 열 구조와 일부 데이터만 복사하여 생성
데이터 없이 기존 테이블의 열 구조만 복사하여 생성

테이블 이름 생성 규칙

1. 테이블 이름은 문자로 시작해야 한다. (한글은 가능, 숫자로 시작할 수 X)

2. 테이블 이름은 30byte 이하여야 한다. (영어는 30글자, 한글은 15자까지)

3. 같은 사용자 소유의 테이블 이름은 중복될 수 없다. → 한 계정에 이름이 중복되는 테이블은 존재할 수 X

4. 테이블 이름은 영문자(한글 가능), 숫자( 0 - 9 )와 특수문자 $ , # , _ 를 사용할 수 있다.

5. SQL 키워드는 테이블 이름으로 사용 불가능하다.

ex) SELECT , FROM 등은 테이블 이름으로 사용불가

테이블 이름을 지정할 때는 대/소문자 구분을 하지 않지만 구별하여 사용하려면 " " 를 사용한다.

열 이름 생성 규칙

1. 열 이름은 문자로 시작해야 한다.

2. 열 이름은 30byte 이하여야 한다.

3. 한 테이블의 열 이름은 중복될 수 없다. → 한 테이블에 중복되는 열은 존재할 수 X

4. 열 이름은 영문자(한글 가능), 숫자( 0 - 9 )와 특수문자 $ , # , _ 를 사용할 수 있다.

5. SQL 키워드는 열 이름으로 사용 불가능하다.

■ 자료형을 정의하여 새 테이블 생성

CREATE TABLE EMP_DDL(
    EMPNO       NUMBER(4),
    ENAME       VARCHAR2(10),
    JOB         VARCHAR2(9),
    MGR         NUMBER(4),
    HIREDATE    DATE,
    SAL         NUMBER(7,2),	--소수점 이하 2자리를 포함한 7자리 숫자(=실수부 5자리, 소수부 2자리)
    COMM        NUMBER(7,1),
    DEPTNO      NUMBER(2)
);


-- DESC 명령어로 생성된 테이블의 열 구조 확인
DESC EMP_DDL;

제약조건도 지정 가능하며, DESC 명령어로 생성한 테이블의 열 구조를 확인할 수 있다.

■ 기존 테이블의 열 구조와 데이터를 복사하여 새 테이블 생성

CREATE TABLE DEPT_DDL
    AS SELECT * FROM DEPT;


-- DESC 명령어로 생성된 테이블의 열 구조 확인
DESC DEPT_DDL;

특정 테이블과 같은 열 구조로 테이블을 만들고 싶을 때 CREATE 문에 서브쿼리를 사용해서 테이블을 생성한다. AS 키워드를 사용하며, 생성하면 서브쿼리에 명시한 테이블과 같은 열 구조를 가지면서 데이터까지 그대로 저장한 테이블이 생성된다.

서로 같은 열 구조에 데이터까지 그대로 저장된 것을 확인할 수 있다.

■ 기존 테이블 열 구조와 일부 데이터만 복사하여 새 테이블 생성

CREATE TABLE EMP_DDL_30
    AS SELECT *
         FROM EMP
        WHERE DEPTNO = 30;

특정 테이블과 열 구조는 같지만, 전체가 아닌 일부 데이터만 복사하여 테이블을 만들어야 한다면 서브쿼리에 WHERE 절을 사용하여 저장될 데이터를 조건식으로 지정할 수 있다. 위의 SQL문은 EMP_DDL_30 이라는 새 테이블을 생성하고, EMP 테이블에서 DEPTNO (부서번호)가 30 인 사원들만 저장하는 예시이다.

EMP 테이블에서 DEPTNO 가 30 인 사원들만 EMP_DDL_30 테이블에 저장된 것을 확인할 수 있다.

■ 데이터 없이 기존 테이블의 열 구조만 복사하여 새 테이블 생성

CREATE TABLE EMPDEPT_DDL
    AS SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM,
              D.DEPTNO, D.DNAME, D.LOC
         FROM EMP E, DEPT D
        WHERE 1 <> 1;	-- 1 <> 1은 항상 false이기 때문에 모든 결과 행이 출력대상에서 제외됨.

특정 테이블과 열 구성이 같지만 데이터는 없는 빈 테이블을 생성하고 싶을 때는 WHERE 절 조건식의 결과 값이 언제나 false 가 나오는 방법을 사용할 수 있다. CREATE 문에 서브쿼리를 사용하는 방식은 자주 쓰이며 여러 테이블을 조인한 SELECT 문도 활용할 수 있다.

테이블을 조회해보면 데이터 없이 열 구성만 복사된 것을 확인할 수 있다.

2. ALTER

이미 생성된 오라클 데이터베이스 객체를 변경할 때 ALTER 명령어를 사용하며, 테이블에 새 열을 추가/삭제하거나 열의 자료형 또는 길이를 변경하는 등 테이블 구조 변경과 관련된 기능을 수행한다.

ALTER 종류

ADD
RENAME
MODIFY
DROP

■ ADD

ALTER TABLE EMP_ALTER
  ADD HP VARCHAR2(20);

ALTER TABLE 명령어에 ADD 키워드를 사용하면 테이블에 새 열을 추가할 수 있다. ADD 뒤에 열 이름과 자료형을 명시하면 된다. 위의 SQL문은 EMP_ALTER 라는 테이블에 자료형이 VARCHAR2 이고 20byte 까지 입력 가능한 HP 열을 추가하는 예시이다.

EMP_ALTER 테이블에 HP 열이 추가된 것을 확인할 수 있다.

■ RENAME

ALTER TABLE EMP_ALTER
RENAME COLUMN HP TO TEL;

ALTER TABLE 명령어에 RENAME 키워드를 사용하면 테이블의 열 이름을 변경할 수 있다. 위의 SQL문은 HP 열 이름을 TEL 로 변경하는 예시이다.

HP 열의 이름이 TEL 로 바뀐 것을 확인할 수 있다.

■ MODIFY

ALTER TABLE EMP_ALTER
MODIFY EMPNO NUMBER(5);

테이블의 특정 열의 자료형이나 길이를 변경할 때는 ALTER TABLE 명령어와 MODIFY 키워드를 사용한다.

※ ALTER 명령어로 열의 자료형과 길이를 변경하는 것은 테이블에 저장된 데이터에 문제가 생기지 않는 범위 내에서만 허용된다. 그래서 길이를 늘이는 것은 괜찮지만 길이를 줄이거나 기존 열의 자료형을 다른 자료형으로 변경하는 것은 저장된 데이터 상태에 따라 결정된다.

EMPNO 의 길이가 4 에서 5 로 늘어난 것을 확인할 수 있다.

■ DROP

ALTER TABLE EMP_ALTER
DROP COLUMN TEL;

DROP 키워드를 사용하면 테이블의 특정 열을 삭제할 수 있다.

열을 삭제하면 해당 열의 데이터도 같이 삭제되므로 신중하게 사용해야 한다.

3. RENAME

RENAME EMP_ALTER TO EMP_RENAME;

테이블 이름을 변경할 때는 RENAME 명령어를 사용한다.

4. TRUNCATE

TRUNCATE TABLE EMP_RENAME;

TRUNCATE 명령어는 특정 테이블의 모든 데이터를 삭제한다.

(데이터만 삭제하므로 테이블 구조에는 영향을 주지 않는다.)

■ TRUNCATE와 DELETE

DELETE : DML , COMMIT 이나 ROLLBACK 이 가능
TRUNCATE : DDL , COMMIT 이나 ROLLBACK 이 불가능 (삭제 이후 복구 불가능)

테이블 데이터 삭제는 DML 의 WHERE 절을 명시하지 않은 DELETE 문도 같은 결과를 낸다. 그러나 DELETE 문은 DML 이라 ROLLBACK 이 가능하지만 TRUNCATE 는 DDL 이라 ROLLBACK 이 불가능하다는 차이점이 있다.

5. DROP

DROP TABLE EMP_RENAME;

DROP 명령어는 데이터베이스 객체를 삭제하는 데 사용한다.

테이블이 삭제되면 테이블에 저장된 데이터도 모두 함께 삭제된다. 또한 DDL 이라 복구가 불가능하니 사용에 주의한다.

오라클 10g부터는 윈도우의 휴지통과 같은 FLASHBACK 기능을 이용해서 DROP 명령어로 삭제된 테이블을 복구할 수 있다.

저작자표시 비영리 변경금지

'Back end > Database' 카테고리의 다른 글

[Oracle] 제약조건(constraint) (0)	2019.10.08
[Oracle] 객체 종류 (0)	2019.09.30
[Oracle] 트랜잭션(transaction)과 TCL, 세션(session) (0)	2019.09.24
[Oracle] 데이터 조작어 (DML : Data Manipulation Language) (0)	2019.09.18
[Oracle] 서브쿼리(Subquery) (1)	2019.09.17

pridiot