Oracle Chapter8. 테이블 생성 및 변경, 삭제하기(데이터 정의어 DDL) (오라클 입문자용)

현 블로그는 모바일 환경이 아닌 PC환경에 최적화 되어있습니다.

Mir의 운영환경

지금지 미리 만들어진 연습용 테이블을 이용하여 자신이 원하는 정보를 여려형식으로 출력하는 방법을 알아보았다.
그럼 이번 Chapter에서는 DDL(데이터 정의어)를 이용하여 테이블을 직접 생성 및 변경, 삭제까지 해보도록 하자.

DDL이란 데이터베이스의 스키마객체를 생성(CREATE), 변경(ALTER), 제거(DROP)을 하거나
권한의 부여(GRANT)나 박탈(REVOKE), 주석(COMMENT) 및 자료의 버림(TRUNCATE)등을 수행하는 문장의 집단이다.

각 명령어는 CREATE, ALTER, DROP, TRUNCATE, GRANT, REVOKE, COMMENT등으로 시작하는데
이 밖에도 많은 종류의 DDL이 존재하지만
우선 이번 Chapter에서는  CREATE(생성), ALTER(변경), DROP(제거), TRUNCATE(버림)를 알아보자.


CREATE (생성)
CREATE문으로 테이블을 생성할때는 
" CREATE TABLE ((컬럼명 자료형), (컬럼명 자료형), (컬럼명 자료명), ...... ) "
형식으로 사용하며 (컬럼명 자료명)이 각각 하나의 컬럼이 된다.
그럼 CREATE문으로 우리가 사용했던 emp테이블과 유사한 구조의
empno, ename, sal 3개의 컬럼으로 구성된 test1 테이블을 생성하여 보자.

CREATE TABLE test1
             (empno NUMBER(4),
              ename VARCHAR2 (20),

               sal NUMBER(7, 2) );

Table created. (테이블이 생성되었습니다.) 라고 뜬다.
생성된 테이블을 한번 확인해보자.

테이블 목록에 TEST1이라는 테이블이 새로 생성되어 있는것이 확인된다.
테이블을 만들때 empno, ename, sal이라는 컬럼까지 같이 생성하였는데 제대로 만들어졌는지 확인해보자.

test1 테이블의 컬럼이 내가 지정한 형식(Type)으로 제대로 만들어 졌다.
이렇게 테이블을 생성한뒤 각각의 자료를 DML(테이터 조작어)로 추가시키면 된다.

DML은 다음파트에서 배울것이고 테이블과 컬럼을 생성할때의 몇가지 주의사항을 살펴보자.
1. 시작은 반드시 영문자이여야 한다.
2. 예약어(SELECT 등)는 사용할수 없다.
3. 유일한 단어이어야 한다. (중복이 되면 안된다)
4. _, $, #은 사용가능하다.
위 4가지 사항을 지키지 않고 테이블명을 사용하면 오류가 발생한다.
꼭 기억해두기 바란다.

자 CREATE문으로 기본적인 테이블을 생성해보았다.
이번엔 서브 쿼리 문을 이용하여 테이블의 생성과 동시에 다른 테이블의 구조와 데이터까지 복사해보자.
CREATE(생성할때) TALBE test2 (test2라는 테이블을) 
AS(.. 처럼 하여라) SELECT * FROM emp; (emp의 모든 컬럼처럼)

위 명령어를 그대로 해석하면 test2라는 테이블을 만들때 emp의 모든 컬럼과 같이 만들어라고 하였다.
이렇게 명령을 내리면 test2라는 테이블의 생성과 동시에 emp테이블의 구조와 테이터까지 복사하게 된다.
자 그럼 확인해 보자.
SELECT * FROM test2

test2의 모든 컬럼을 출력시키면 emp테이블과 똑같은 구조에 데이터까지 똑같이 생성되어 있는것을 확인할수 있다.
(필자의 hiredate는 YY-MON-DD 형식으로 출력되게 해놔서 출력문이 조금 다르다.  따지고 보면 같은 날짜니 신경쓰지말자!)

서브 쿼리 문을 이용하여 테이블을 생성하는 방법을 응용하면 테이블의 구조만 복사하던지
원하는 구조, 혹은 원하는 데이터만 복사할수 있다.
아래 3개의 CREATE문을 실행하여서 각각 구조와 데이터를 확인해 보자.
 
1. CREATE TABLE test3 AS SELECT * FROM emp WHERE 1 = 0;
2. CREATE TABLE test4 AS SELECT ename, sal FROM emp;
3. CREATE TABLE test5 AS SELECT * FROM emp WHERE deptno = 10;





확인해보았는가? 그럼 하나씩 하나씩 살펴보자.
1번 CREATE문에서는 emp테이블처럼 생성하라고 명령을 주었는데 where 1 = 0 이라는 조건문을 주었다.
그래서 모든 컬럼명(구조)를 생성하고 난뒤에 값을 넣을려고 하니 조건에 맞는 값(true값)이 없다.
(1 = 0 이라는 조건은 항상 false가 되기때문에) 결국 데이터는 하나도 들어가지 않고 구조만 가지고 오게 된다.

 select * from test3; 으로 모든 컬럼값을 출력시켜라고 하였지만
컬럼값이 없기때문에 no rows selected(선택된 값이 한개도 없다.) 라고 출력한다.
하지만 desc test3; 로 테이블 구조를 보면 구조는 복사되어 있는게 보인다.

2번 CREATE문에서는 emp테이블의
모든 컬럼(*)이 아닌 ename과 sal컬럼만 선택해 주었다.
그 이외의 조건이 없기때문에
테이블 생성시 ename컬럼과 sal컬럼만 복사하여
생성되어 진다.











3번 CREATE문에서는 emp테이블의 모든컬럼을 가져오는데 where 조건절을 주었다.
바로 deptno=10 이라는 조건이다. 1번 테이블에서는 1 = 0 이라는 조건을 주어 테이블을 생성하였지만
1 = 0의 조건은 항상 false이기 때문에 데이터가 하나도 들어가지 않았다.
하지만 deptno =10이란 조건은 부서번호(daptno)가 10인 데이터는 true가 되기 때문에
부서번호 10을 가진 데이터만 복사되는 것이다.

자 그럼 여기서 문제 하나만 풀고 가보자.

test6이라는 테이블을 생성하는데 직업(job)이 매니져인 사람의 이름(ename)과 월급(sal)만 복사해서 생성해 보자.

위 3개의 CREATE문을 이해하였다면 금방 만들수 있을것이다.

그럼 확인해보도록 하자.

직업이 매니져인사람  :  where job = 'MANAGER'
이름과 월급만 복사 : select ename, sal
그리고 테이블 생성문 : CREATE TABLE test6
이 세개의 명령을 합치면 된다. 

CREATE TABLE test6
AS SELECT ename, sal
FROM emp
WHERE job = 'MANAGER'; 

ALTER(변경)
CREATE문으로 테이블을 생성하는법을 알아보았다. 이번엔 ALTER문으로 테이블을 구조를 변경하는 방법에 대해 알아보자.
구조변경이란 컬럼을 추가(ADD), 삭제(DROP), 수정(MODIFY)한다는 뜻이다.
ALTER문으로 테이블의 구조를 변경할때에는 
ALTER TABLE 테이블이름
추가는 ADD ((컬럼명 자료형), (컬럼명 자료형), (컬럼명 자료형)......)
삭제는 DROP column((컬럼명 자료형), (컬럼명 자료형), (컬럼명 자료형)......)
수정은 MODIFY ((컬럼명 자료형), (컬럼명 자료형), (컬럼명 자료형)......)
형식으로 사용한다. 
 
그러면 우선 emp테이블을 모두 복사한 test2테이블에 email이라는 컬럼을 추가해보자.
ALTER TABLE test2
ADD (email VARCHAR2(20));

EMAIL이라는 컬럼이 VARCHAR2(20)이라는 Type으로 생성된것을 확인할수 있다.
(필자가 컬럼을 추가하면서 값까지 같이 가져오는 방법이 있을줄 알고 헤멨는데 일단은 없는거 갇다;;
 혹시나 ALTER ADD를 사용하여 컬럼추가와 동시에 다른테이블에서의 값을 가져올수 있으면
그리고 그 방법을 알면 필자에게도 가르쳐주길 바란다 ㅠㅠ)

자 컬럼을 추가시켰는데 메일주소를 20자로 제한할려닌깐 너무 작다.
그럼 emanil컬럼의 Type을 수정해보자.
ALTER TABLE test2
MODIFY (email VARCHAR2(40));
 

email컬럼의 Type이 VARCHAR2(40)으로 변경된것을 확인할수 있다.

보통 MODIFY로 Type수정을 많이 하며 컬럼명은 한번정하면 잘 바꾸지 않는 편인데 
혹시나 컬럼명을 수정할때에는 RENAME를 사용하여 바꿔주면 된다.
ALTER TABLE test2
RENAME column email TO e_mail ;

컬럼의 속성을 변경할때 주의사항이 있는데 혹시 숫자형(NUMBER)에서 문자형(CHAR)으로
속성을 변경할때 컬럼안에 데이터가 있으면 변경할수가 없다.
이미 숫자형의 데이터가 들어가있는데 속성을 문자형으로 쓸수 없기 때문이다.
유일하게 데이터가 들어가있을때 속성을 바꿀수 있는것은 CHAR형과 VARCHAR2형 사이의
타입변경이며 다른 형끼리 속성변환을 할때에는 데이터가 있으면 변경할수 없다.
물론 크기만 늘리는것은 데이터가 있어도 가능하다. ex) CHAR(5) - > CHAR(10)
(반대로 크기를 줄이는것은 최대크기 데이터보다 작게 줄일수가 없다.)

마지막으로 컬럼삭제(DROP)를 해보자. (잠시 따라하지 말고 보기만 하자)
ALTER TABLE test2
DROP column e_mail;

지정한 e_mail컬럼이 삭제된것을 확인할수 있다.

여기서 주의할점이 특정테이블에서 컬럼을 삭제할때 위처럼 무작정 삭제하는건 위험하다.
왜냐하면 DROP자체가 서버에 부하가 많이 걸리는 작업이며 만약 100만개의 데이터를 지우게 된다면
그 DROP명령이 끝날때까지 테이블 전체를 사용하지 못하기때문이다.

그래서 오라클에서는 SET UNUSED라는 명령어를 제공하는데 (이제부턴 따라해도 된다.)
ALTER TABLE test2
SET UNUSED(e_mail);

실행결과를 보면 e_mail컬럼이 사라져있다.
SET UNUSED는 물리적인 데이터는 그대로 두고 논리적인 데이터만 삭제하게 된다.
즉 데이터는 그대로 남아있는데 접근을 막아버리는 것이다.

이렇게 SET UNUSED를 사용하고 난뒤 사용빈도가 가장적은 시간에 (새벽시간때나 점검시간)
DROP명령어로 물리적인 데이터까지 완전히 삭제해주는것이다.

ALTER TABLE test2
DROP UNUSED columns;

물론 지금은 혼자서 사용하기때문에 전혀 필요없지만 후에 혹시 서버관리를 하게 되면
아무생각없이 무작정 지웠다가 바로 항의전화가 날라올수도 있다는거! 꼭 기억하자! 이런기능이 있다는걸 


DROP (제거)

위에서 컬럼을 삭제할때도 쓰였지만 테이블을 제거할때도 DROP 문을 사용한다.
DROP TABLE 테이블이름;
형식으로 사용되며 한번 지우면 다시 복구하기가 힘들기 때문에 항상 조심해야 한다.
자 여태껏 만들었던 test테이블을 1,2,3번까지만 지워보자!
DROP TABLE test1
DROP TABLE test2
DROP TABLE test3

DROP문을 사용하고 난뒤 TABLE목록을 보니 test1,2,3는 지워져있는데 이상한 이름의 테이블이 3개가 생겨났다.
오라클10G에서 새로운 기능이 추가되었는데 바로 휴지통(RECYCLE BIN)기능이다.
윈도우의 휴지통과 마찬가지로 지운테이블을 임시로 보관했다가 혹시 잘못지운것이면 복원까지 가능하게 해두었다.
이상한 이름의 테이블은 현재 휴지통에서 보관하고 있는 테이블을 나타낸다.

아래는 간단한 휴지통명령어다.
show recyclebin;     -> 휴지통에 있는 테이블을 조회

방금 삭제했던 테이블들이 보인다.

flashback table 테이블명 to before drop;  -> 삭제된 테이블을 복원

test3을 복원 시켜 보았다.

DROP table 테이블명 purge;   -> 휴지통에 남가지 않고 완전 삭제

test3이 완전 삭제 되었다. (휴지통에 테이블이 남아있으면 이상한 문자가 생긴다.)

purge table 테이블명;   -> 휴지통에 있는 테이블을 완전 삭제

purge recyclebin;   -> 휴지통 비우기

휴지통에 아무 테이블이 없으면 show recyclebin(휴지통보기)명령을 내려도 아무런 창이 뜨지 않는다.


test1부터 3까지의 테이블을 이제 깔끔하게 지웠다.
테이블을 삭제할때도 주의할점이 있는데 삭제하고자 하는 테이블이 다른테이블과 연결
(기본 키나 고유 키를 다른 테이블에서 참조할때)되어 있으면 테이블 삭제가 불가능하다.
이럴경우 우선 연결된 테이블을 삭제한뒤에 테이블을 지워줘야 한다.
기본 키나 고유 키는 후에 다시한번 배울것인데 아직 무슨뜻인지 잘몰라도 그때 되면 왜 삭제가 안되는지 알게 될것이다.



TRUNCATE (버림)

마지막 TRUNCATE만 남앗는데 TRUNCATE는 해당 테이블의 모든 데이터를 제거할때 사용한다.
테이블을 삭제하는것이 아니라 값을 버리는것이다.
TRUNCATE TABLE 테이블명;
형식으로 사용된다.

그럼 test4에 있는 모든 데이터를 한번 지워보자.
TRUNCATE TABLE test4;

옆의 그림처럼 test4에 있던 모든 데이터가
TRUNCATE를 하고 난 뒤
사라지는것을 확인할수 있다.




















자 이번 Chapter에서 DDL(데이터 정의어)의 CREATE, DROP, ALTER, TRUNCATE를 배워보았다.
다음 Chapter에서는 DML(데이터 조작어)을 배워서 생성한 테이블에 값을 넣어보도록 하자.