이번 글에서는 DDL에 관해 정리해보겠습니다.
DDL이란 Data Definition Language의 줄임말로써, 데이터 정의어 입니다.
데이터 정의어는 이렇게 있습니다.
1) CREATE
2) ALTER
3) RENAME
4) TRUNCATE
5) DROP
그럼 하나하나 정리해보겠습니다.
0. 데이터 이름 지정 규칙 및 데이터 정의어 유의사항
데이터 이름은 사실 아무렇게나 막 지어도 오류는 나지 않습니다.
다만 하나의 관례가 있죠.
그 규칙은 이러합니다.
1. 알파벳, 숫자, _ (언더바), $, # 사용 가능
2. 첫 글자는 영문자
3. 예약어 사용 금지
4. 이름은 항상 의미있게 생성
그리고 이 DDL문 또한 트랜잭션을 종료 시키기 때문에 유의해서 사용해야합니다.
1. CREAT
CREAT는 이름답게 테이블을 생성하는 문입니다.
그치만 몇가지 지켜져야 할 사항이 있습니다.
테이블을 생성하려면 개발자는 CREATE TABLE 권한이 있어야 합니다.
객체를 생성할 저장 영역이 있어야 합니다.
위 두 사항만 지켜지면 테이블을 만들 수 있습니다.
그러면 테이블을 만들어보겠습니다.
CREATE TABLE emp_test
(EMPNO NUMBER(4),
ENAME CHAR(또는 VARCHAR2) (20)
JOB CHAR(또는 VARCHAR2) (20),
SAL NUMBER(7, 2),
HIREDATE DATE)
위 형식처럼 행 데이터가 없는 빈 데이터를 만들 수 있습니다.
각 줄의 첫번째는 컬럼명, 즉 HEADING을 뜻하고 그 옆에 NUMBER, CHAR, VARCHAR2 는 각 컬럼에 지정되는 자료형을 뜻합니다.
그리고 자료형 옆에 괄호는 자릿수를 의미합니다.
NUMBER(4)는 4자리 숫자를 뜻하고 NUMBER(7, 2)는 7자리에 소숫점 두자리까지, CHAR 또는 VARCHAR2(20)은 20자리를 의미합니다.
여기서 CHAR와 VARCHAR2의 차이는, 고정형이냐 가변형이냐의 차이입니다.
CHAR(8)이라고 하면, 한 글자만 넣어도 8바이트를 차지합니다. 즉, 고정형입니다.
VARCHAR2(8)은 한 글자를 넣으면 1바이트, 두 글자를 넣으면 2바이트 식으로 변합니다. 즉, 가변형입니다.
그러면 위 명령을 실행하면 어떻게 되는지 보겠습니다.
테이블이 만들어졌네요. 저는 ENAME 컬럼은 VARCHAR2 형으로, JOB은 CHAR형으로 선언했습니다.
SQL Developer로 확인해본 결과, 각 HEADING까지 잘 만들어진 것을 볼 수 있습니다.
현재 아무 값도 없는 테이블입니다.
그래서 SQL plus에서 실행하면 'no rows selected' 라고 뜹니다.
이제 DML문을 통해 값을 이렇게 추가할 수 있겠죠.
그러면 이제 테이블을 생성할 때 부터 값을 다른 테이블에서 가져와보겠습니다.
즉, 서브 쿼리를 사용할 수 있습니다.
CREATE TABLE emps AS SELECT * FROM emp;
기존 emp 테이블의 내용을 전부 emps 라는 테이블로 그대로 가져다가 새로운 테이블을 만드는 쿼리입니다.
emps 테이블이 잘 만들어졌네요.
그런데 위 컬럼과 자료형을 그대로 가져다가 빈 테이블로 만들 수 있습니다.
위에 다뤘던거 아니냐구요?
물론 처음에 다뤘던 방법으로 빈 테이블을 만들 수 있겠지만, 일일이 다 쳐야하는 번거로움이 있죠.
그럴 때, WHERE절을 이용해서 빈 테이블을 형성할 수 있습니다.
CREATE TABLE emps2 AS SELECT * FROM emp WHERE empno = 9999;
혹은 아래와 같이 만들 수도 있습니다.
CREATE TABLE emps3 AS SELECT * FROM emp WHERE 1 <> 1;
2. ALTER
ALTER 명령어는 이미 생성된 오라클 데이터베이스 객체를 변경할 때 사용합니다.
테이블의 새 열을 추가 / 삭제, 열의 자료형 / 길이를 변경 등 테이블 구조 변경과 관련된 기능을 수행합니다.
1) 새 열 추가
ALTER TABLE emps ADD hp VARCHAR2(20);
ADD 명령어를 이용해서 열을 추가합니다.
마지막 열을 보시면 hp라는 열이 추가된 것을 볼 수 있습니다.
2) 열 이름 바꾸기
열 이름을 바꾸기 위해 'RENAME COLUMN 기존 이름 TO 바꿀 이름' 형식으로 사용합니다.
ALTER TABLE emps RENAME COLUMN hp TO mp;
마지막 열을 보시면 hp 라는 HEADING이 mp라고 바뀐것을 볼 수 있습니다.
3) 기존 열 자료형 변경
먼저 mp 컬럼의 자료형을 보겠습니다.
컬럼의 자리가 너무 길게 나타나서 제대로 안나온다면,
set line 100 명령을 입력해서 위와같이 출력할 수 있습니다.
MP 컬럼을 보시면, 타입이 VARCHAR2(20)인 것을 볼 수 있습니다.
그러면 자료형을 바꿔보겠습니다.
'MODIFY 컬럼명 자료형' 형태로 바꿀 수 있습니다.
ALTER TABLE emps MODIFY mp VARCHAR2(15);
위와 같이 자료형이 수정된 것을 볼 수 있습니다.
3) 열 삭제
열 삭제는 'DROP COLUMN 컬럼명;' 형태로 사용하시면 됩니다.
ALTER TABLE emps DROP COLUMN mp;
mp 컬럼이 사라진것을 볼 수 있습니다.
3. 읽기전용모드 READ ONLY / 읽기쓰기전용모드 READ WRITE
READ ONLY 명령을 사용하면 테이블을 읽기전용모드로 둘 수 있습니다.
읽기전용모드로 두면 테이블에 영향을 주는 DML을 실행할 수 없게 됩니다.
테이블이 데이터를 수정하지 않는 한 DDL 문은 실행할 수 있습니다.
ALTER TABLE emps READ ONLY;
이제 여기서 DML문을 사용해볼까요?
UPDATE 명령을 사용해서 deptno이 10인 행을 50으로 바꾸려고 했는데
아래와 같이 오류가 납니다.
다시 쓰기권한도 주고싶다면 READ WRITE 명령을 사용하시면 됩니다.
ALTER TABLE emps READ WRITE;
이제 위 UPDATE 명령을 다시 사용해보겠습니다.
위와 같이 DML문이 사용가능한것을 볼 수 있습니다.
혹시모르니 ROLLBACK; 명령을 통해 처음으로 되돌려놓읍시다.
※참고사항
읽기전용모드 테이블은 삭제할 수 있습니다.
테이블 삭제(DROP TABLE) 명령은 데이터 딕셔너리에서만 실행되므로 테이블 내용에 엑세스 할 필요가 없기 때문입니다.
DROP TABLE emps;
4. TRUNCATE 와 DELETE
TRUNCATE와 DELETE는 둘 다 row(행)를 삭제하는 명령어입니다.
근데 두 개가 있다는 뜻은 차이점이 있다는 뜻이겠죠.
DELETE : 테이블의 row들을 지워주는 명령어. WHERE절로 조건 삭제를 하거나 WHERE절을 생략함으로써 모든 행 삭제.
TRUNCATE : 테이블의 row(행)을 지워주는 명령어이지만, DELETE와 달리 ROLLBACK이 되지 않습니다. 즉, WHERE절 또한 사용할 수 없습니다.
5. DROP과 휴지통
위에서 잠깐 봤죠.
DROP은 테이블을 삭제하는 명령어입니다.
정들었던 emps 테이블을 버려볼까요?
DROP TABLE emps;
emps 테이블을 삭제함으로써 더 이상 불러올 수도 없습니다.
그런데 이는 완전히 삭제된 것이 아닙니다.
DROP TABLE을 하면 우리가 컴퓨터를 쓰는 것 처럼, DBMS도 휴지통(RECYCLBIN)에 보류처리합니다.
SHOW RECYCLEBIN; => 휴지통 확인
자꾸 실수해서 몇번 지웠던 흔적입니다 ㅎㅎㅎ;;;
이렇게 삭제했던 테이블을 확인할 수 있고 아래 명령어로 꺼내올 수 있습니다.
FLASHBACK TABLE 테이블명 TO BEFORE DROP;
그리고 아래처럼 휴지통 비우는 명령도 가능합니다.
PURGE RECYCLEBIN;
완전히 비움으로써 SHOW RECYCLEBIN 명령어도 반응이 없네요.
또한 아래와 같이 영구 삭제가 가능합니다. 우리가 파일을 삭제할 때, Shift+Delete를 하거나 Shift삭제를 하면 영구삭제 되지요. 똑같은겁니다. (따라서 복원이 불가합니다.)
DROP TABLE 테이블명 PURGE;
6. 테이블 이름 변경
'RENAME 바꾸고싶은테이블명 TO 바꿀이름' 명령어로 테이블 이름을 바꿀 수 있습니다.
emps 테이블은 현재 없습니다.
RENAME copy_emp TO emps;
copy_emp 테이블은 이제 없고
emps 테이블로 이름이 바뀐것을 볼 수 있습니다.
여기까지 데이터 정의어에 관한 정리글이였습니다.
