[Oracle] DDL(테이블 생성, 삭제, 관리)

DDL(Data Definition Language)

• CREATE 생성
• ALTER 수정
• DROP 테이블 자체를 삭제
• RENAME 테이블 이름 변경
• TRUNCATE 테이블 데이터와 공간을 삭제

DELETE(DML), DROP, TRUNCATE의 차이점

원본 테이블

이름	지역	나이
홍길동	인천	18
김길동	서울	19

DELETE - 조건을 통해 일부만 삭제 가능

이름	지역	나이

TRUNCATE - 조건 사용 불가 (테이블은 남아있음)

이름

지역

나이

DROP - 테이블 자체를 삭제

X

테이블 생성과 삭제

CREATE TABLE 테이블명(
컬럼 데이터_타입 [DEFAULT 값] [컬럼 레벨 제약조건]
.....
[테이블 레벨 제약조건],
.....);

DROP TABLE 테이블명
[CASCADE CONSTRAINT]; -- CASCDE CONSTRAINT 제약조건을 지우는 명령어
DROP TABLE 테이블명 PURGE; -- 휴지통에 넣지 않고 지우기

PURGE RECYCLEBIN; --휴지통 비우기

테이블 생성 규칙

• 테이블명 : 문자로 시작, 30자이내 (영문,숫자,_,$,#,한글 사용가능) - 한글은 지양한다.
• 테이블의 이름은 동일한 유저(데이타베이스,스키마) 안에서 유일해야 한다.
• 예약어 사용불가
• 대소문자 구분X (사용한 문자와 관계없이 대문자로 정의됌)

데이터 타입

VARCHAR(n) VARCHAR2(n)	가변 길이 문자 타입 (1 < n < 4000 byte)
CHAR(n)	고정 길이 문자 타입 (1 < n < 2000 byte)
NUMBER(n,p)	숫자 타입, n은 전체 자리수 p는 소수점 이하 자리수 전체 자리수를 초과할 경우에는 입력 거부 소수점 자리수를 초과할 경우 해당 자리수까지 반올림하여 입력
DATE	날짜 타입, 출력이나 입력형식과 무관하게 YYYY/MM/DD:HH24:MI,SS 형태로 저장
LONG	가변 길이 문자 타입(2GB까지 저장 가능), 조건 검색 불가능 한 테이블에의 한 개의 컬럼에만 LONG타입만 정의가능
CLOB	위의 LONG을 개선한 가변 길이 문자 타입, 최대 4GB까지 저장 가능하고 한 테이블의 여러 컬럼에 정의가능
BLOB	가변길이 이진 타입 (4GB까지 저장 가능)
ROW(n)	가변길이 이진 타입
BFILE	4G 이내의 외부 파일 저장을 위한 이진 타입
ROWID	ROWID를 저장하기 위한 데이터 타입, 각 문자는 64진수로 엔코딩 되어 있다.

ROWID ?

• 테이블에서 행의 위치를 지정하는 논리적인 주소값
• DB 전체에서 중복 되지 않는 유일한 값으로 새로운 행이 삽입 되면 테이블 내부에서 의사 컬럼 형태로 자동 생성
• 특정 레코드를 랜덥하게 접근하기 위해 사용

테이블 생성 관련 명령어

SELECT * FROM tab; -- 이 줄을 작성해서 실행하면 내부적으로 밑 라인의 명령어를 실행한다.
SELECT table_name FROM user_tables; 
-- 스키마에 테이블 목록을 검색

DESC 테이블; -- 이 줄을 작성해서 실행하면 내부적으로 밑 라인의 명령어들을 실행한다.
SELECT table_name, column_name, data_type, data_length
FROM user_tab_columns
[WHERE table_name = '테이블'];
-- 테이블 컬럼 구성을 확인

table_name, user_table, column_name -- 이러한 얘들을 보고 딕셔너리 라고 칭한다.

테이블 생성과 확인

다음과 같은 구조의 테이블을 생성하고 확인한다.
테이블명 : board(게시판)
구성 컬럼 : no(게시물번호), name(작성자), sub(제목), content(내용), hdate(입력일시)

CREATE TABLE board(
no NUMBER,
name VARCHAR2(50),
sub VARCHAR2(100),
content VARCHAR2(4000),
hdate DATE DEFAULT sysdate); --생성

SELECT * FROM tab; -- 테이블 목록 확인

SELECT table_name FROM user_tables
WHERE table_name = 'BOARD'; -- 테이블 이름 확인 (테이블명 : 대문자로 작성)

DESC board; -- 컬럼의 데이터 타입과 이름 확인

	  -- 테이블명 ,   컬럼명 , 데이터 타입, 데이터 길이
SELECT table_name, column_name, data_type, data_length 
FROM user_tab_columns
WHERE table_name = 'BOARD';
--테이블 이름과함께 데이터 확인

INSERT INTO board(no,name,sub,content) VALUES (1,NULL,NULL,NULL);
COMMIT;

SELECT * FROM BOARD;
1,'','','',24/01/31	-- 날짜엔 default값이 들어간다

삭제 테이블 복구

SELECT object_name, original_name FROM recyclebin;

FLASHBACK TABLE ORIGINAL_NAME TO BEFORE DROP
[RENAME TO NEW_NAME];

DROP TABLE test;
SELECT * FROM tab;
SELECT object_name, original_name FROM recyclebin;
FLASHBACK TABLE test TO BEFORE DROP; -- 같이 드랍된 데이터 까지 복구
SELECT * FROM tab;

서브 쿼리를 이용한 테이블 생성

CREATE TABLE 테이블명 [(컬럼 [DEFAULT 값], ... )] AS (SELECT 문장 : Sub Query 문);

• 서브 쿼리 실행 결과를 테이블로 생성
• 컬럼 목록이 생략되면 서브쿼리의 열 이름이 생성될 테이블 컬럼 명이 된다.
• 컬럼에 DEFAULT 항목을 지정할 수 있다.
• 테이블을 복사하거나 일부를 별도로 저장하는 용도로 사용된다.

emp, dept 테이블의 사번, 이름, 업무, 부서 정보만을 갖는 테이블을 생성한다.
CREATE TABLE emp2 AS
SELECT eno 사번, ename, 이름, job 업무, d.dno 부서번호, dname 부서
FROM emp e, dept d
WHERE d.dno=e.dno;

DESC emp2;

SELECT * FROM emp2;

테이블 관리(ALTER TABLE)

ALTER TABLE 테이블
(ADD (컬럼명 데이터_타입 [DEFAULT 값], ....);)              -- 컬럼 추가
|| (MODIFY (컬럼명 데이터_타입 [DEFAULT 값], ...);)      -- 컬럼 변경
|| (DROP COLUMN 컬럼명;)                                             -- 컬럼 삭제

• 컬럼 추가 (ADD)
  
  • 추가되는 열을 마지막에 위치(위치 지정 불가)
  • 추가된 열에는 NULL이 저장된다.
• 컬럼 변경(MODIFY)
  • 기존 데이터 수용이 가능하면 데이터 타입, 길이 수정도 가능 (행이 없으면 보다 더 자유롭다)
  • 수정된 컬럼은 이후 행 입력부터 적용된다
• 컬럼 삭제 (DROP)
  • 데이터 유무와 상관없이 삭제 가능
  • 한번에 하나씩만 삭제가 가능하다.
  • 테이블에 반드시 하나 이상의 컬럼이 존재해야 한다.
  • 삭제된 컬럼은 복구할 수 없다.
  • sys 계정의 테이블에서는 작업이 불가능하다.

- 컬럼의 변경과 삭제는 할 필요가 없어야  정상이다.

테이블 내용 완전 삭제 : TRUNCATE TABLE 테이블명;

테이블 이름 변경 : RENAME 테이블명 TO 변경할_테이블명;