[Oracle] 테이블 생성 및 조작(CREATE, ALTER, DROP TABLE)

테이블 생성

CREATE TABLE table_name
(
 column datatype [DEFAULT expression] [column_constraint_claues]
 [, ...]
);

• table_name : 생성될 테이블 이름
• column : 생성될 칼럼 이름
• datatype : 칼럼에 대한 데이터 타입과 길이
• DEFAULT expression : 데이터 입력 시 값이 생략된 경우에 입력되는 기본 값
• column_constraint_clause : 칼럼에 대해 정의되는 무결성 제약조건 ( NOT NULL, UNIQUE 등등.. )

 

EX) 주소룩 테이블 생성

CREATE TABLE address
(id NUMBER(3),
 name VARCHAR2(50),
 addr VARCHAR2(100),
 phone VARCHAR2(30),
 email VARCHAR2(100)
);

 

DEFAULT 옵션(기본값)

칼럼의 입력 값이 생략된 경우 NULL 대신 기본값이 들어간다.

기본값으로 리터럴값, 표현식, SQL함수, SYSDATE, USER를 사용한다.

칼럼이나 의사칼럼(NEXTVAL< CURRVAL)은 불가능함.

addr VARCHAR2(100) DEFAULT 'KOREA'

 

EX)

CREATE TABLE example_table
(
 name VARCHAR2(10),
 addr VARCHAR2(100) DEFAULT 'KOREA'
);

INSERT INTO example_table(name)
VALUES ('테스트');

 

테이블 생성 확인

DESC [테이블이름];

테이블의 생성 여부, 구조, 칼럼 이름, 데이터 타입과 크기, NOT NULL 무결성 제약조건 확인 가능하다.

 

EX)

DESC example_table;

-개인 예제-

CREATE TABLE lecture
(
 lec_no NUMBER(2),
 name VARCHAR2(20) NOT NULL,
 nickname VARCHAR2(20) UNIQUE,
 major VARCHAR2(20),
 address VARCHAR2(500),
 PRIMARY KEY (lec_no)
);

COMMENT ON COLUMN lecture.lec_no IS '강사번호';
COMMENT ON COLUMN lecture.name IS '강사이름';
COMMENT ON COLUMN lecture.nickname IS '강사닉네임';
COMMENT ON COLUMN lecture.major IS '전공';
COMMENT ON COLUMN lecture.address IS '주소';

CREATE TABLE class_schedule
(
 schedule_no NUMBER(2),
 lec_no NUMBER(2),
 name VARCHAR2(50) NOT NULL,
 sdate DATE NOT NULL,
 edate DATE NOT NULL,
 loc VARCHAR2(50),
 total_time NUMBER(3),
 reg_cnt NUMBER(3),
 application_edate DATE NOT NULL,
 PRIMARY KEY (schedule_no),
 FOREIGN KEY (lec_no) REFERENCES lecture(lec_no)
);

COMMENT ON COLUMN class_schedule.schedule_no IS '교육일정번호';
COMMENT ON COLUMN class_schedule.lec_no IS '교육강사번호(외래키)';
COMMENT ON COLUMN class_schedule.name IS '교육명';
COMMENT ON COLUMN class_schedule.sdate IS '교육시작일';
COMMENT ON COLUMN class_schedule.edate IS '교육마감일';
COMMENT ON COLUMN class_schedule.loc IS '교육장소';
COMMENT ON COLUMN class_schedule.total_time IS '총교육시간';
COMMENT ON COLUMN class_schedule.reg_cnt IS '교육수강정원';
COMMENT ON COLUMN class_schedule.application_edate IS '교육신청마감일';

 

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

테이블의 복사 생성이 가능하다.

• CREATE TABLE 명령문에서 서브 쿼리를 이용해 다른 테이블의 구조와 데이터를 복사 가능.
• CREATE TABLE 명령문에서 지정한 칼럼의 수와 데이터 타입이 일치해야 함.
• 칼럼 이름을 명시하지 않을 경우, 서브 쿼리 칼럼 이름과 동일
• 무결성 제약조건은 NOT NULL 조건만 복사된다.
  • 기본키, 참조 키와 같은 제약조건은 재정의 해야 함.
  • default 옵션에서 정의한 값은 복사된다.

CREATE TABLE table_name
    [column [, column2, ...]]
AS subquery;

 

EX)

-- 주소록 테이블의 구조와 데이터를 복사하여 addr_second 테이블을 생성
CREATE TABLE addr_second(id, name, phone, email)
AS SELECT * FROM address;

CREATE TABLE addr_second
AS SELECT * FROM address;

-- 주소록 테이블에서 id, name 칼럼만 복사하여 addr_third 테이블을 생성
CREATE TABLE addr_third
AS SELECT id, name FROM address;

 

테이블 구조 복사

테이블 생성 시에 데이터는 복사하지 않고 구조만 복사한다.

서브 쿼리의 WHERE 조건절에 거짓이 되는 조건을 설정하여 출력 결과 집합이 없도록 한다.

CREATE TABLE table_name
AS SELECT * FROM source_table WHERE condition;

condition : 거짓이 되는 조건을 설정하자. (EX, WHERE 1=2)

 

EX)

-- 주소록 테이블에서 id, name 칼럼 구조만 복사하여 addr_fourth 테이블을 생성 (데이터는 X)
CREATE TABLE addr_fourth
AS SELECT * FROM address WHERE 1=2;

 

테이블 구조 변경

ALTER TABLE 명령문을 쓴다.

칼럼 추가, 삭제, 타입이나 길이의 변경 작업을 한다.

 

테이블 칼럼 추가

ALTER TABLE table_name
ADD (column datatype [DEFAULT expression]
     [, column datatype] ... );

• ALTER TABLE ... ADD 구조이다.
• 추가된 칼럼은 테이블의 마지막 부분에 추가되며, 위치 지정은 불가능하다.

EX)

-- 주소록 테이블에 문자 타입을 가지는 comments 칼럼을 추가하라. (기본값: 'No Comment')
ALTER TABLE address
ADD (comments DATE DEFAULT 'No Comment');

 

테이블 칼럼 삭제

ALTER TABLE ... DROP COLUMN 명령문을 쓴다.

2개 이상의 칼럼이 존재하는 테이블에 대해서만 삭제 가능하다.

하나의 칼럼만 삭제 가능하다.

ALTER TABLE table_name DROP COLUMN column_name;

 

EX)

-- 주소록 테이블에서 comments 칼럼을 삭제
ALTER TABLE address
DROP COLUMN comments;

 

테이블 칼럼 변경

테이블 칼럼의 타입, 크기, 기본 값 변경 가능하다.

ALTER TABLE ... MODIFY 명령문을 사용한다.

• 기존 칼럼에 데이터가 없는 경우
  • 칼럼 타입이나 크기 변경이 자유롭다.
• 기존 칼럼에 데이터가 있는 경우
  • 타입 변경은 CHAR와 VARCHAR2 내에서만 가능하다.
  • 저장된 데이터의 크기보다 같거나 클 경우에만 변경이 가능하다.
  • 숫자 타입에서 정밀도 증가 가능
• 기본 값 변경하면 입력되는 데이터부터 적용된다.

ALTER TABLE table_name
MODIFY (column datatype [DEFAULT expression]
        [, column datatype]... );

 

EX)

-- 주소록 테이블에서 phone 칼럼의 크기를 50으로 증가
ALTER TABLE address
MODIFY phone VARCHAR2(50);

/*
만약 기존의 데이터가 있으면 아래의 쿼리는 작게 지정할 수 없기에 오류 발생.
ALTER TABLE address
MODIFY phone VARCHAR2(5);
*/

 

테이블 칼럼 이름 변경

-- lecture 테이블의 hp 칼럼 이름을 cellphone으로 변경
ALTER TABLE lecture
RENAME COLUMN hp to cellphone;

 

테이블 이름 변경

RENAME 명령문 사용한다.

RENAME old_table TO new_table;

 

EX)

-- addr_second 테이블 이름을 client_address로 변경
RENAME addr_second TO client_address;

 

테이블 삭제

• 테이블과 데이터를 모두 삭제한다.
• DROP TABLE 명령문 사용한다.
• 삭제할 테이블의 기본 키나 고유 키를 다른 테이블에서 참조하고 있는 경우 삭제 불가능하다.
  • 참조하는 테이블을 우선 삭제해야 함.
  • DROP TABLE 명령문 끝에 CASCADE CONSTRAINTS 옵션을 사용하면 제약조건을 동시에 삭제한다.

DROP TABLE table_name [CASCADE CONSTRAINTS]

 

EX) 

-- addr_third 테이블을 삭제
DROP TABLE addr_third;