[SQL] oracle 테이블 생성(CREATE TABLE), 삭제(DROP), 변경/수정(ALTER)

안녕하세요. 소다맛사탕 입니다.

 

데이터베이스의 객체로서 데이터를 저장해 놓고 꺼내 쓰는 가장 기본적인 객체인 테이블 생성, 삭제, 변경/수정 그리고 테이블 복사까지 알아 보겠습니다.

테이블(table) : 데이터를 담고 있는 객체

 

먼저 테이블은 엑셀과 구조가 같다고 보시면 편합니다.

SQL을 이용해 데이터를 조회, 삭제, 입력 수정할 대상이며 그 결과를 담고 있습니다.

 

1. 테이블 생성(CREATE)

CREATE TABLE [스키마.]테이블명 (
    컬럼1 컬럼1의데이터타입 [NULL, NOT NULL],
                .
                .
                .
    컬럼n 컬럼n의데이터타입 [NULL, NOT NULL]
 );
 
 -- ex)
 CREATE TABLE TEST_EXAMPLE_TB (
    seq_no NUMBER NOT NULL,
    data1 VARCHAR2(100),
    data2 VARCHAR2(100)
);

• 테이블, 컬럼명 최대 크기는 30 BYTE
• 테이블, 컬럼명으로 예약어 사용 X  (예약어 : SELECT, UPDATE 문 등)
• 테이블, 컬럼명으로 문자, 숫자, _ , $... 사용할 수 있지만, 첫 글자는 문자만
• 한 테이블에 사용 가능한 컬럼은 최대 255개까지

1-1. 문자 데이터 타입

CHAR(크기[ BYTE | CHAR ]) : 고정길이 문자, 최대 2000 BYTE, 디폴트 값은 1 BYTE

VARCHAR2(크기[ BYTE | CHAR ]) : 가변길이 문자, 최대 4000 BYTE, 디폴트 값은 1 BYTE

NCHAR(크기) : 고정길이 유니코드 문자(다국어 입력 가능), 최대 2000 BYTE, 디폴트 1

NVARCHAR2(크기) : 가변길이 유니코드 문자(다국어 입력 가능), 최대 4000 BYTE, 디폴트 1LONG : 최대 2GB 크기의 가변길이 문자형

 

1-2. 숫자 데이터 타입

NUMBER[(P,[S])] : 가변숫자, P(1~38, 디폴트값 38), S(-84~127, 디폴트값 0), 십진수 기준, 최대 22 BYTE

FLOAT[(P)] : NUMBER의 하위타입, P(1~128, 디폴트값 128), 이진수 기준, 최대 22 BYTEBINARY_FLOAT : 32비트 부동소수점 수, 최대 4 BYTEBINARY_DOUBLE : 64비트 부동소수점 수, 최대 8 BYTE※ 다른 DBMS의 경우 정수형(INTEGER),  실수형(DECIMAL)을 제공

 

1-3. 날짜 데이터 타입

DATE : BC 4712년 1월 1일부터 9999년 12월 31일, 연,월,일,시,분,초까지 입력 가능.

TIMESTAMP : 연도, 월,일,시,분,초, 밀리초까지 입력 가능

 

1-4. LOB 데이터 타입(Large Object)

CLOB : 문자형 대용량 객체. 고정길이와 가변길이 문자 집합 지원.

최대 크기 (4GB-1)*(데이터베이스 블록 사이즈)

NCLOB : 유니코드(다국어지원)를 포함한 문자형 대용량 객체.

최대 크기 (4GB-1)*(데이터베이스 블록 사이즈)

BLOB : 이진형 대용량 객체. 최대 크기 (4GB-1)*(데이터베이스 블록 사이즈)

BFILE : 대용량 이진 파일에 대한 로케이터(위치, 이름)저장, 최대 크기 4GB

 

 

제약조건(contraints)

데이터 무결성을 보장하기 위한 용도로 사용. 제약조건에는 NOT NULL, UNIQUE, 기본키, 외래키, CHECK 등이 있음.

-- NOT NULL, UNIQUE
CREATE TABLE TEST_EXAMPLE_CONT(
  DATA1_NOT_NULL VARCHAR2(100) NOT NULL,
  DATA2_UNIQUE VARCHAR2(100) UNIQUE NOT NULL,
  DATA3_UNIQUE VARCHAR2(100),
  CONSTRAINTS set1_unique UNIQUE (DATA3_UNIQUE)
);

-- 기본키(PK)
CREATE TABLE TEST_EXAMPLE_CONT1(
  NM_SEQ NUMBER PRIMARY KEY,
  DATA1 VARCHAR2(100) NOT NULL
);

CREATE TABLE TEST_EXAMPLE_CONT2(
  NM_SEQ NUMBER,
  PK_DATA1 VARCHAR2(10),
  DATA1 VARCHAR2(100) NOT NULL,
  CONSTRAINTS set1_pk PRIMARY KEY(NM_SEQ, PK_DATA1)
);
-- PK는 한테이블에 다중으로 설정 가능

-- 외래키(반드 참조테이블 먼저생성, 참조키가 참조테이블의 기본키로)
-- CONSTRAINT 외래키명 FOREIGN KEY(컬럼명, ...)
-- REFERENCES 참조테이블(참조테이블 컬럼명, ...)

-- CHECK(특정조건에 맞는 데이터만 입력받고, 아니면 오류를 뱉음)
CREATE TABLE TEST_EXAMPLE_CHK (
  NM_SEQ NUMBER,
  CONSTRAINTS check1 CHECK(NM_SEQ BETWEEN 1 AND 10),
  GENDER VARCHAR2(10),
  CONSTRAINTS check2 CHECK(GENDER IN ('MAN', 'WOMAN'))
);

---

2. 테이블 삭제(DROP)

DROP TABLE [스키마.]테이블명 [CASCADE CONSTRAINTS];
 
-- ex)
DROP TABLE TEST_EXAMPLE_TB;

CASCADE CONSTRAINTS를 붙이면 삭제할 테이블의 기본키와 UNIQUE 키를 참조하는 참조 무겨성 제약조건도 자동으로 삭제된다.

---

3. 테이블 변경/수정(ALTER)

3-1. 컬럼명 변경

ALTER TABLE [스키마.]테이블명 RENAME COLUMN 변경전컬럼명 TO 변경후컬럼명;
 
-- ex)
ALTER TABLE TEST_EXAMPLE_TB RENAME COLUMN seq_no TO seq_num;

 

3-2. 컬럼 타입 변경

ALTER TABLE [스키마.]테이블명 MODIFY 컬럼명 데이터타입;
 
-- ex)
ALTER TABLE TEST_EXAMPLE_TB MODIFY seq_num VARCHAR2(30);

 

3-3. 컬럼 추가

ALTER TABLE [스키마.]테이블명 ADD 컬럼명 데이터타입;
 
-- ex)
ALTER TABLE TEST_EXAMPLE_TB ADD DATA3 VARCHAR2(100);

 

3-4. 컬럼 삭제

ALTER TABLE [스키마.]테이블명 DROP COLUMN 컬럼명;
 
-- ex)
ALTER TABLE TEST_EXAMPLE_TB DROP COLUMN DATA3;

 

3-5. 제약조건 추가 및 삭제

-- 제약조건 추가
ALTER TABLE [스키마.]테이블명 ADD CONSTRAINTS 제약조건명 PRIMARY KEY (컬럼명, ...);
 
-- ex)
ALTER TABLE TEST_EXAMPLE_TB ADD CONSTRAINTS EXAMPLE_TB_PK PRIMARY KEY(seq_num);

-- 제약조건 삭제
ALTER TABLE [스키마.]테이블명 DROP CONSTRAINTS 제약조건명;
 
-- ex)
ALTER TABLE TEST_EXAMPLE_TB DROP CONSTRAINTS EXAMPLE_TB_PK;

---

4. 테이블 복사

CREATE TABLE [스키마.]테이블명 AS
SELECT 컬럼1, 컬럼2, ...
FROM 복사할 테이블명;

-- ex)
CREATE TABLE TEST_EXAMPLE_TB2 AS
SELECT * FROM TEST_EXAMPLE_TB;

 

 

참조 - 강북쌍용교육센터 교육중..