[SQL] 13. 데이터 정의 언어(DDL문)

13. 데이터 정의 언어(DDL문)

데이터 정의 언어인 DDL문은 데이터를 담고있는 구조와 연결되어 있으며,
실무에서 상당히 많이 사용하는 CTAS(create table as select)도 DDL문에 포함된다고 할 수 있다.

그렇다면 데이터를 담는 구조(객체)는 어떤 것이 있는지 알아보자.

※ 데이터를 담는 구조(Database Object)의 5가지 종류

1. table: 데이터를 저장하는 기본 저장 단위이고, 행과 열로 구성
2. view: 테이블을 바라보는 쿼리문이 출력되는 결과(하나 이상의 테이블에 있는 데이터의 부분 집합)
3. sequence: 일련번호 생성기(번호를 생성하는 db 오브젝트)
4. synonym: 테이블의 또 다른 이름을 부여
5. index: 검색 속도를 향상시키기 위한 db 오브젝트

지금은 정확히 이해가 가지 않겠지만, 우선 알아두고 넘어가자.
DDL 명령어로는 create, alter, drop, truncate, rename이 있다.

create

create는 말 그대로 만드는 명령문이다.
데이터를 저장하는 기본 저장 단위인 table을 생성하는 방법은 아래와 같다.
create table 테이블명
(컬럼명 데이터유형(길이),
컬럼명 데이터유형(길이),
컬럼명 데이터유형(길이) ) ;

이전에 몇 번 다뤄본적이 있지만, 필히 집고 나가야할 데이터의 기본 유형(type)은
크게 문자형 / 숫자형 / 날짜형 으로 구분할 수 있는데,
실제 기재해야하는 데이터 유형은 아래와 같다.

emp데이터와 같이 가벼운 Data Set의 문자데이터로는 주로 VARCHAR2를 사용하며,
큰 txt파일의 데이터를 한 번에 받을 때에는 LONG, CLOB을 사용하기도 한다.
데이터 유형에 대해서는 별도의 글로 더 알아보도록 하겠다.

예제) 문자형, 숫자형, 날짜형 컬럼이 하나씩 있는 테이블 emp88을 생성하시오!
create table emp88
( empno number(10),
ename varchar2(10),
hiredate date) ;

실제로 만들어졌는지 확인해보자.

→ 테이블만 생성하고 넣은 데이터가 없으므로 이러한 결과가 출력된다.

→ sqlplus가 아닌 다른 프로그램을 이용하면 컬럼만 있는 빈 테이블의 모습을 볼 수 있음

위에 만든 테이블에 바로 전 글에서 언급했던 DML문을 이용해 데이터를 넣어보자.

insert into emp88 values(01, '김영수', to_date('2018/01/01', 'RRRR/MM/DD'));
insert into emp88 values(02, '박철수', to_date('2017/12/01', 'RRRR/MM/DD'));

데이터가 정상적으로 들어간 것을 확인했다.

◈꿀팁◈

※ DB에 있는 테이블이 언제 생성되었는지를 확인하려면?
select object_name, created, last_ddl_time
from user_objects
order by CREATED desc;

alter

alter는 테이블의 특정 컬럼을 삭제하거나 추가하거나 변경할 때 사용하는 명령어이다.
주의할 점은 alter를 통해 변경하면 rollback, flashback을 통한 복구가 어려우니 신중하게 실행하자!

사용 방법은 아래와 같다.
alter table 테이블명
명령어(add, modify, drop 등....)문 입력 ;

하나씩 예를 들어 살펴보자.

예제) emp88 테이블에 월급(sal) 컬럼을 추가하시오!
alter table emp88
add sal number(10,2);
→ 소수점 위 10자리, 소수점 아래 2자리의 숫자형 데이터를 넣을 수 있는 sal 컬럼을 만들다.

update emp88
set sal = 2000 ;

제대로 데이터가 들어간 것을 확인할 수 있다.

예제) emp88 테이블 이름(ename) 컬럼의 길이를 300바이트로 늘리시오!
alter table emp88
modify ename varchar2(300);

→ 테이블의 각 데이터 타입 및 길이 확인하는 명령문: desc 테이블명

desc emp88;

modify는 주로 데이터 타입을 변경할 때 사용되며, 해당 컬럼의 크기를 늘릴수는 있지만 줄이지는 못한다.
(기존의 데이터가 훼손될 수 있으므로)

예제) emp88 테이블의 월급(sal) 컬럼을 삭제하시오!
alter table emp88
drop column sal;

◈꿀팁◈

alter table 구문을 통해 테이블을 읽기전용 테이블로 변환하기!
alter table emp88 READ ONLY;
위 명령문을 실행 후 데이터를 변경하고자 하면 아래와 같은 오류메세지가 출력된다.

다시 쓰기도 가능한 테이블로 변환하려면
alter table emp88 READ WRITE; 를 실행하면 된다.

drop, truncate

drop, truncate는 모두 데이터를 삭제하는 명령어이다.
분명 DML문에서 배운 delete도 있는데 왜 삭제하는 명령어가 두 가지나 더 있을까?
delete, truncate, drop 각각의 특징을 비교해보자.

큰 차이점은 rollback/flashback을 통한 삭제 명령 취소 가능 여부이다.
ORACLE SQL에는 휴지통과 같은 Recycle bin이라는 개념의 공간이 있다.
(삭제된 데이터가 모여있는 곳)

기본적인 취소 명령문인 rollback의 경우 commit;을 한 다음에는 복구하기가 어렵다.
이럴때 flashback 기능을 사용하여 데이터를 복구한다.

flashback table 테이블명 to before drop;
→ drop하기 전으로 돌려놔라!

혹은

flashback table 테이블명 to timestamp (systimestamp - interval '5' minute);
→ 5분 전으로 돌려놔라!

만약 테이블이 flashback이 불가능한 상태라면
Alter table 테이블명 enable row movement; 으로 falshback이 가능하도록 변경해라

허나, truncate는 flashback도 불가능한 삭제 명령어라는 차이점이 있다.
drop으로 삭제하는 테이블을 Recylce bin에 넣지 않고 truncate처럼 바로 삭제하여
flashback으로도 복구 불가하게 하려면
drop table emp88 purge; 를 사용하면 된다.

되도록이면 truncate와 purge 사용은 지양하는 것을 추천한다.
복구하려면 로그마이너로 변경 작업했던 로그를 분석해서 수행했던 DML문을 뽑아내는
번거로운 방법밖에 없기 때문이다.

rename

rename은 object의 이름을 변경하는 명령어로 이름을 변경할 객체가 있어야 한다.

예제) emp88 테이블의 이름을 emp99로 변경하시오!
rename emp88 to emp99;

=================이 글은 Markdown을 활용해 작성하였습니다=================