[SQL] 데이터베이스를 구성하는 객체와 DDL

---

1. 데이터베이스 객체의 종류
2. 테이블
3. 뷰
4. 인덱스
5. 시노님
6. 시퀀스
7. 파티션 테이블

---

 

 

1. 데이터베이스 객체의 종류

데이터베이스 객체	설 명
테이블	데이터를 담고 있는 객체
뷰	하나 이상의 테이블을 연결해 마치 테이블인 것처럼 사용하는 객체
인덱스	테이블에 있는 데이터를 빠르게 찾기 위한 객체
시노님	데이터베이스 객체에 대한 벼칭을 부여한 객체
시퀀스	일련번호 채번을 할 때 사용되는 객체
함수	특정 연산을 하고 값을 반환하는 객체
프로시저	함수와 비슷하지만 값을 반환하지는 않는 객체
패키지	용도에 맞게 함수나 프로시저를 하나로 묶어 놓은 객체

 

 

 

2. 테이블

 1) 테이블 생성

CREATE TABLE [스키마.]테이블명 (
컬럼1 컬럼1_데이터타입 [NULL, NOT NULL],
컬럼2 컬럼2_데이터타입 [NULL, NOT NULL],
...
) [TABLESPACE 테이블스페이스명];

--스키마명 생략 가능, 자신이 로그인한 스키마 이름으로 생성
--TABLESPACE 생략 가능, 해당 사용자의 디폴트 TABLESPACE에 생성

 

 2) 데이터 타입

  - 컬럼이 저장되는 데이터 유형

데이터 타입	설 명
CHAR (크기 [BYTE | CHAR])	고정길이 문자, 최대 2000byte, 디폴드 값 1byte
VARCARCHAR2 (크기 [BYTE | CHAR])	가변길이 문자, 최대 4000byte, 디폴드 값 1byte
NCHAR (크기)	고정길이 유니코드 문자(다국어O), 최대 2000byte, 디폴트 값 1byte
NCARCHAR2 (크기)	가변길이 유니코드 문자(다국어 O), 최대 4000byte, 디폴트 값 1byte
LONG	최대2GB 크기의 가변길이 문자형, 잘 사용하지 않음

 

 3) 숫자 데이터 타입

데이터 타입	설 명
NUMBER [(p, [s])]	가변숫자, p(1~38, 디폴트 값 38)와 s(-84~127, 디폴트 값 0)는 10진수 기준, 최대 22byte
FLOAT [(p)]	NUMBER의 하위 타입, p는 1~128, 이진수 기준, 최대 22byte
BINARY_FLOAT	32비트 부동소수점 수, 최대 4byte
BINARY_DOUBLE	64비트 부동소수점 수, 최대 8byte

 

 4) 날짜 데이터 타입

 : DATE 보다 TIMESTAMP의 날짜 정확도가 더 높음

데이터 타입	설 명
DATE	BC 4712년 1월 1일부터 9999년 12월 31일, 연, 월, 일, 시, 분, 초까지 입력 가능
TIMESTAMP [(fractional_seconds_precision)]	연도, 월, 일, 시, 분, 초는 물론 밀리초까지 입력 가능 fractional_seconds_precision은 0~9까지 입력 가능, 디폴트 값 6

 

 5) LOB 데이터타입

 : Large OBject 의 약자, 대용량 데이터를 저장할 수 있는 데이터 타입

 

 6) NULL

 : 값이 없음을 의미, 테이블을 생성할 때 컬럼 속성에 기술

 : 디폴트 값 NULL

 : 허용하지 않으려면 NOT NULL 구문 명시

 

 7) 제약 조건

 : 컬럼에 대한 속성 형태로 정의

 : 오라클 데이터베이스 객체 중 하나

 : 데이터 무결성을 보장하기 위한 용도로 사용

 

   ① NOT NULL

 : 반드시 갑이 들어 있어야 하는 컬럼에는 NOT NULL 제약조건을 만들어 사용

컬럼명 데이터타입 NOT NULL

 

   ② UNIQUE

 : 해당 컬럼에 들어가는 값이 유일해야 함

 : 중복값을 허용하지 않음

컬럼명 데이터타입 UNIQUE
혹은
CONSTRAINTS 제약조건명 UNIQUE (컬럼명, ...)

 

   ③ 기본키

 : UNIQUE와 NOT NULL 속성을 동시에 가진 제약조건

 : 테이블 당 1개만 생성 가능

컬럼명 데이터타입 PRIMARY KEY
혹은
CONSTRAINTS 제약조건명 PRIMARY KEY (컬럼명, ...)

 

   ④ 외래키

 : 테이블 간의 참조 데이터 무결성을 위한 제약조건

CONSTRAINT 외래키명 FORENIGN KEY (컬럼명, ...)
REFERENCES 참조 테이블 (참조 테이블 컬럼명, ...)

 

   ⑤ CHECK

 : 컬럼에 입력되는 데이터를 체크해 특정 조건에 맞는 데이터만 입력 받고 그렇지 않으면 오류를 뱉음

CONSTRAINT 체크명 CHECK (체크 조건)

 

 

 8) 테이블 삭제

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

DROP TABLE [스키마.]테이블명 [CASCADE CONSTRAINTS]

 

 9) 테이블 변경

   ① 컬럼명 변경

ALTER TABLE [스키마.]테이블명 RENAME COLUMN 변경 전 컬럼명 TO 변경 후 컬럼명;

 

   ② 컬럼 타입 변경

ALTER TABLE [스키마.]테이블명 MODIFY 컬럼명 데이터타입;

 

   ③ 컬럼 추가

ALTER TABLE [스키마.]테이블명 ADD 데이터타입;

 

   ④ 컬럼 삭제

ALTER TABLE [스키마.]테이블명 DROP COLUMN 컬럼명;

 

   ⑤ 제약 조건 추가 : 기본 키 추가

ALTER TABLE [스키마.]테이블명 ADD CONSTRAINTS 제약조건명 PRIMARY KEY (컬럼명, ...);

 

   ⑥ 제약 조건 삭제

ALTER TABLE [스키마.]테이블명 DROP CONSTRAINTS 제약조건명;

 

 10) 테이블 복사 (CreateTableAS : 씨타스)

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

 

 

3. 뷰

 : 하나 이상의 테이블이나 다른 뷰의 데이터를 볼 수 있게 하는 데이터베이스 객체

 : 실제 데이터는 뷰를 구성하는 테이블에 있지만 테이블처럼 사용할 수 있음

 : 뷰를 참조해 새로운 뷰를 만들어 사용 가능

 : 데이터 보안 측면에서 유리

 

 1) 뷰 생성

 : 다른 테이블이나 뷰를 SELECT 하는 구문으로 구성

CREATE OR REPLACE VIEW [스키마.]뷰명 AS
SELECT 문장;

 

 2) 뷰 삭제

 : 뷰를 삭제해도 실제 데이터는 삭제되지 않음

DROP VIEW [스키마.]뷰명;

 

 

4. 인덱스

 : 테이블에 있는 데이터를 빨리 찾기 위한 용도의 데이터베이스 객체

  - 인덱스 구성 컬럼 개수에 따른 분류: 단일 인덱스와 결합 인덱스  (인덱스 속성에 따른 분류)

  - 유일성 여부에 따른 분류: UNIQUE 인덱스, NON-UNIQUE 인덱스 (인덱스 속성에 따른 분류)

  - 인덱스 내부 구조에 따른 분류: B-tree 인덱스, 비트맵 인덱스, 함수 기반 인덱스  (구조와 내부 알고리즘에 따른 분류)

 

 1) 인덱스 생성

CREATE[UNIQUE] INDEX [스키마명.]인덱스명
ON [스키마명.]테이블명 (컬럼1, 컬럼2 ...);

 

 2) 인덱스 삭제

DROP INDEX [스키마명.]인덱스명;

 

 

5. 시노님

 : 동의어라는 뜻

 : 데이터베이스 객체는 각자 고유한 이름이 있고 이에 대한 동의어를 만드는 것

 : PUBLIC / PRIVATE 시노님

 

  1) 시노님 생성

CREATE OR REPLACE [PUBLIC] SYNONYM [스키마명.]시노님명
FOR [스키마명.]객체명;

--PUBLIC을 생략하면 PRIVATE 시노님이 만들어짐
--PUBLIC 시노님은 DBA 권한이 있는 사용자만 생성 및 삭제 가능
--FOR절 이하의 객체에는 테이블, 뷰, 프로시저, 함수, 패키지, 시퀀스 등이 올 수 있음

 

  2) 시노님 삭제

DROP [PUBLIC] SYNONYM [스키마명.]시노님명;

--PRIVATE 시노님을 제거할 때 DROP SYNONYM, DROP ANY SYNONYM 권한이 있어야 함
--PUBLIC 시노님을 제거할 때 DROP PUBLIC SYNONYM 권한이 있어야 함, PUBLIC이라는 키워드 명시

 

 

6. 시퀀스

 : 자동 순번을 반환하는 데이터베이스 객체

 

 

  1) 시퀀스 생성

CREATE SEQUENCE [스키마명.]시퀀스명
INCREMENT BY 증감숫자				
START WITH 시작숫자					
NOMINVALUE | MINVALUE 최솟값		 
NOMAXVALUE | MAXVALUE 최댓값		 
NOCYCLE | CYCLE						
NOCACHE | CACHE;

시 퀀 스	설 명
INCREMENT BY 증감숫자	0이 아닌 정수, 양수면 증가, 음수면 감소, 디폴트 값 1
START WITH 시작숫자	디폴트 값 증가일 때 MINVALUE, 감소일 경우 MAXVALUE
NOMINVALUE	디폴트 값으로 증가일 때 1, 감소일 경우 -(1027-1)
MINVALUE 최솟값	최솟값은 시작 숫자와 작거나 같아야 하고 MAXVALUE보다 작아야 함
NOMAXVALUE	디폴트 값으로 증가할 때 1028-1, 감소의 경우 -1
MAXVALUE 최댓값	최댓값은 시작 숫자와 같거나 커야 하고 MINVALUE보다 커야 함
NOCYCLE	디폴트 값으로 최대나 최솟값에 도달하면 생성 중지
CYCLE	증가는 최댓값에 도달하면 다시 최솟값부터 시작, 감소는 그 반대
NOCACHE	디폴트로 메모리에 시퀀스 값을 미리 할당해 놓지 않으며, 디폴트 값 20
CACHE	메모리에 시퀀스 값을 미리 할당

 

  2) 시퀀스 삭제

DROP SEQUENCE [스키마명.]시퀀스명;

 

 

7. 파티션 테이블

 : 파티션으로 테이블 생성 가능

 : 테이블에 있는 특정 컬럼값을 기준으로 데이터를 분할해 저장해 놓는 것

 : 논리적인 테이블은 1개, 물리적으로 분할한 만큼 파티션별로 데이터가 저장

 

CRATE TABLE 테이블명 (
컬럼1, 컬럼1_데이터타입 [NULL, NOT NULL],
컬럼2, 컬럼2_데이터타입 [NULL, NOT NULL],
...
)

PARTITION BY RANGE(컬럼명) (
...
PARTITION 파티션명 VALUES LESS THAN ('파티션 기준') TABLESPACE MYTS,
PARTITION 파티션명 VALUES LESS THAN ('파티션 기준') TABLESPACE MYTS,
PARTITION 파티션명 VALUES LESS THAN ('파티션 기준') TABLESPACE MYTS
);