서론
기본적인 DML 문법들은 끝이 난 것 같고 데이터를 SELECT, INSERT, UPDATE, DELETE 한다는 것은 특정 테이블의 데이터를 조작한다는 것이다. 조작을 하려면 당연히 테이블이 존재해야 하는데 이제 DDL 중에 하나인 CREATE TABLE.. 에 대해서 알아보고자 한다.
본론
기본 문법
--CREATE(생성하기) --컬럼명 데이터형 뒤에 DEFAULT SYSDATE 라고 하면 기본 값으로 들어간다. --TABLE SPACE USERS; --테이블을 저장하기 위해서 테이블을 묶어놓은 공간, 폴더 같은 공간, 논리적인 공간이다. --테이블이 만들어 질때 하나의 테이블스페이스 소속이 되어있어야 한다. --테이블들의 성격을 구분짓기 위한 테이블들의 집합체..? 성능상의 차이는 거의 없다. --두개의 테이블 스페이스에 하나의 테이블을 저장 할 순 없다, 테이블스페이스가 없는 테이블은 없다. --테이블을 만들때 테이블스페이스를 지정하지 않으면 디폴트 테이블스페이스로 지정된다. --디폴트 테이블스페이스는 유저별로 다르다. create table ddl_test (no number(3) primary key, name varchar2(10) not null, birth date default sysdate ) tablespace users; --테이블이 꽉차서 insert가 안되요, 하면 해당 테이블의 테이블스페이스 크기를 확인해야됨 --table 생성시 주의 사항 --1. 테이블이름은 반드시 문자로 시작해야 한다. --숫자로 시작할 수 없고, 특수문자로 시작되는 경우 ""(쌍따옴표)를 이용해서 만들 수 있으나 권고x -- --2. 테이블 이름이나 컬럼이름은 최대 30bytes까지 -- --3. 한글이 지원되는 DB라면 한글이름의 테이블, 컬럼이름 가능 -- --4. 같은 계정 내에서는 동일한 오브젝트명으로 생성할 수 없다. --(다른계정일 경우 상관x) -- --5. DB에서 쓰는 명령어나 함수 등 약속된 단어에 대해서는 테이블명이나 컬럼명으로 사용하는 것을 피한다. --테이블의 각 컬럼에 default 값 확인 select * from dba_tab_columns where owner in ('SCOTT', 'HR'); --DATA_PRECISION, DATA_SCALE 은 NUMBER 타입 컬럼에만 값이 있음 --DATA_PRECISION은 NUMBER 형일때 괄호안의 숫자임 --문자나 날짜타입이면 DATA_LENGTH로 사용하면 됨 --넘버타입일때 DATQA_PRECISION 비어있으면 그냥 NUMBER 타입 --DATA_SCALE이 0일때는 NUMBER(4) 이런식임, 있으면 NUMBER(2,2)이런식 --DEFAULT 가 설정되어있으면 DATA_DEFAULT 값을 보면 된다. --NULLABLE로 NULL 여부체크
CREATE TABLE [스키마].테이블명 (컬럼명 데이터타입) [TABLESPACE 테이블스페이스명]; 형식으로 테이블을 생성한다. 테이블을 생성할 때 이 테이블이 저장되는 논리적인 공간을 제시 해주어야 하는데 이 공간이 테이블 스페이스이다. 나중에 유저생성 쪽에서 다루겠지만 유저를 생성하게 되면 기본 테이블스페이스를 지정하는 부분이 있는데 테이블스페이스 쪽을 생략하면 이 기본 테이블스페이스로 지정이 된다. 또한 스키마도 생략하고 테이블 명만 적어주면 해당 유저명의 스키마로 생성이 된다.
여기서 스키마는 논리적인 구조를 다루는 것으로써 스키마가 다르면 동일한 테이블명을 사용 할 수가 있다. EX) scott.test, hr.test 당연히 select * from test라고 쳐보면 해당 유저의 스키마에 속한 테이블이 검색이 되며 권한이 있다면 다른 스키마의 테이블을 조회 할 수도 있다. 혹은 스키마명을 생략하고도 다른 스키마의 테이블을 검색 할 수도 있는데 이것은 추후에 다룰 시노님(SYNONYM)에서 정의가 가능하다.
정리하자면 테이블 생성은 특정 테이블스페이스에 어떠한 컬럼들을 가지고 있는 테이블을 생성 할 것이고 이것을 특정 스키마로 관리하겠다 라고 생각하면 될 것 같다. 보통은 CREATE 하는 유저의 스키마와 기본 테이블스페이스로 생성될 것이다. 오라클 11g 버전부터는 테이블 생성 시 세그먼트가 즉시 할당되지 않기 때문에 ANY CREATE TABLE 권한만 있다면 특정 스키마에 테이블 생성은 모두 가능하다. (단 해당 유저가 특정 테이블스페이스에 QUOTA가 없다면 INSERT는 안 될 것이다)
컬럼 데이터 타입
문자형 데이터 타입
데이터 유형 | 정의 |
CHAR(n) | 고정 길이 데이터 타입(최대 2000byte)- 지정된 길이보다 짦은 데이터 입력될 시 나머지 공간 공백으로 채워진다. |
VARCHAR2(n) | 가변 길이 데이터 타입(최대 4000byte)- 지정된 길이보다 짦은 데이터 입력될 시 나머지 공간은 채우지 않는다. |
NCHAR(n) | 고정 길이 유니코드 데이터 타입(최대 2000byte) |
NVARCHAR2(n) | 가변 길이 유니코드 데이터 타입(최대 4000byte) |
LONG | 가변 길이 데이터 타입(최대 2Gbyte) |
CLOB | 대용량 텍스트 데이터 타입(최대 4Gbyte) |
NCLOB | 대용량 텍스트 유니코드 데이터 타입(최대 4Gbyte) |
숫자형 데이터 타입
데이터 유형 | 정의 |
BINARY_FLOAT | 부동 소수형 데이터 타입(4byte) -32bit 부동 소수 |
BINARY_DOUBLE | 부동 소수형 데이터 타입(8byte) -64bit 부동 소수 |
NUMBER(P,S) | P, S로 표현 숫자 데이터 타입 p: 1 ~ 38, s: -84 ~ 127 p(precision): 유효자리수, s(scale): 소수점 유효자리 |
날짜형 데이터 타입
데이터 유형 | 정의 |
DATE | 고정 길이 날짜 |
INTERVAL_YEAR | 날짜(년도, 월)형태의 기간 표현 데이터 타입() |
INTERVAL_DAY | 날짜 및 시간(요일, 시, 분 초)형태의 기간 표현 데이터 타입 |
TIMESTAMP | 밀리초(ms)까지 표현 데이터 타입 |
TIMESTAMP_WITH TIME ZONE | 날짜 및 시간대 형태의 데이터 타입 |
TIMESTAMP_WITH LOCAL TIME ZONE | 저장 시 데이터베이스 시간대를 준수, 조회시 조회하는 클라이언트 시간 표현 데이터 타입 |
이진 데이터 타입
데이터 유형 | 정의 |
RAW(n) | 가변 길이 이진 데이터 타입(최대 2Gbyte) |
LONGRAW | 가변 길이 이진 데이터 타입(최대 4Gbyte) |
BLOB | 대용량의 바이너리 데이터를 저장하기 위한 데이터 타입(최대 4Gbyte) |
BFILE | 대용량의 바이너리 데이터를 파일형태로 저장하기 위한 데이터 타입(최대 4Gbyte) |
출처 - http://www.incodom.kr/
보통은 문자형은 가변길이 데이터 타입인 VARCHAR2를 주로 사용하고 데이터 크기에 맞춰서 사용하는 편인 것 같다. 날짜형의 경우 NUMBER 타입을 주로 사용하는데 뒤의 괄호를 생략하면 최대 값은 38로 지정이 된다. 특히나 앞에 숫자인 P부분은 총 자리수를 뜻하며 뒤의 S부분은 소수점 자리 숫자를 의미 한다. NUMBER(5,3) 이라면 총 자리수는 5자리, 소수점 4번째 자리에서 반올림하여 3자리까지 표시하겠다라는 의미이다. 이 타입에 12.321 를 INSERT 하면 정상적으로 그대로 들어가지고 12.3335라면 반올림 되어 12.334 으로 들어가진다. 하지만 123.333 이라고 입력하면 총 자리수인 5를 넘어서기 때문에 오류가 난다. 앞에는 총 자리 수, 뒤에는 소수점 어느자리까지 표시할 것인가로 기억하면 좋을 것 같다.
제약조건
--컬럼명 데이터타입 NOT NULL --특정 컬럼에 NULL 값을 허용하지 않은 제약조건이다. 데이터를 입력하거나 수정할 경우에 해당 컬럼에 NULL 값이 들어간다면 제약조건 위배가 되어 오류가 난다. --컬럼명을 제시 해줄 때 바로 제약조건을 넣어줄 수 있으며 이럴 경우에 제약 조건명은 오라클 내부 특정 이름으로 지정된다. --CONSTRAINTS를 이용하여 제약조건을 명시해 줄 수 있다. CREATE TABLE TB (A VARCHAR2(10) NOT NULL); CREATE TABLE TB (A VARCHAR2(10) CONSTRAINTS NN_A NOT NULL(A)) --컬럼명 데이터타입 UNIQUE --유니크 제약조건은 중복이 불가능, 즉 들어가는 값이 유일해야 한다는 조건이다 --역시나 동일한 값을 입력할 경우에는 제약조건 위배가 되며 자동으로 인덱스가 생성된다. --중복값만 체크하기 때문에 공백이 허용되고 공백도 넣고 싶지 않다면 NOT NULL도 넣거나 PRIMARY KEY로 지정해주는게 좋다. --모든 제약조건들은 컬럼 옆에 즉시 제약 조건을 달 경우 오라클 내부 특정 이름으로 지정되며 CONSTRAINTS를 이용하여 따로 제약 조건명을 지정해줄수 있다. CREATE TABLE TB (A VARCHAR2(10) UNIQUE); CREATE TABLE TB (A VARCHAR2(10) CONSTRAINTS UNIQUE_A UNIQUE(A) ) --컬럼명 데이터타입 PRIMARY KEY --기본키 NOT NULL 이며 중복값도 허용되지 않는다. (NOT NULL + UNIQUE) --테이블당 하나의 기본키를 설정해 줄 수 있으며 자동으로 인덱스가 생성된다. CREATE TABLE TB (A VARCHAR2(10) PRIMARY KEY); CREATE TABLE TB (A VARCHAR2(10) CONSTRAINTS PK_A PRIMARY KEY(A) ) --컬럼명 REFERENCES 참조테이블(참조테이블 컬렴명) --참조키 특정 테이블의 컬럼을 참조하여 해당 컬럼에 있는 값들만 입력이 가능하다. 참조 무결성이라고도 한다 --참조하는 테이블의 컬럼은 기본키만 가능하다. CREATE TABLE TB (A VARCHAR2(10) REFERENCES TB2(A) ); CREATE TABLE TB (A VARCHAR2(10) CONSTRAINTS RK_A FOREIGN KEY(A) REFERENCES TB2(A)) --CHECK --특정값만 입력 받게끔 조건을 걸 때 사용한다 CREATE TABLE TB (A VARCHAR2(10) CHECK(A IN ('콜라', '사이다')); CREATE TABLE TB (A VARCHAR2(10) CONSTRAINTS CK_A CHECK(A IN ('콜라', '사이다')) --DEFAULT --데이터를 입력 받지 않아도 INSERT시 기본으로 입력되는 값 CREATE TABLE TB (A VARCHAR2(10) DEFAULT '식혜'); CREATE TABLE TB (A VARCHAR2(10) CONSTRAINTS DE_A DEFAULT '식혜') --제약 조건 확인하는 쿼리 SELECT M.CONSTRAINT_NAME AS 제약조건이름, M.CONSTRAINT_TYPE AS 제약조건타입, M.TABLE_NAME AS 제약조건테이블명, D.COLUMN_NAME AS 제약조건컬럼명, R.TABLE_NAME AS 참조테이블명, O.COLUMN_NAME AS 참조컬럼명 FROM DBA_CONSTRAINTS M, DBA_CONS_COLUMNS D, DBA_CONSTRAINTS R, DBA_CONS_COLUMNS O WHERE M.CONSTRAINT_NAME = D.CONSTRAINT_NAME AND M.R_CONSTRAINT_NAME = R.CONSTRAINT_NAME AND R.CONSTRAINT_NAME = O.CONSTRAINT_NAME AND M.OWNER = 'SCOTT'
제약조건들은 컬럼명 들을 명시 할때 바로 넣을 수도 있는데 이럴경우 제약조건명이 자동으로 생성된다. 제약조건 명을 관리하기 좋게 따로 명명하려면 컬렴 명시 후CONSTRAINTS 명령어를 통해 지정해주면 된다. 위의 예시 중 CONSTRAINTS 뒤에 있는 이름들이 명명한 이름들이다.
결론
CREATE TABLE과 제약조건에 대해서 알아보았다. CREATE TABLE 할때 제약조건이 가장 중요하기 때문에 잘 확인하도록 하자.