반응형

[MYSQL] 테이블 생성 (CREATE TABLE)


--------------------------------------------------------------------------------
CREATE TABLE T_TEST1
(
    IDX INT AUTO_INCREMENT PRIMARY KEY ,
    TITLE VARCHAR(100) NOT NULL ,
    CONTENTS TEXT NOT NULL ,
    WRITER VARCHAR(50) NOT NULL ,
    WDATE DATETIME DEFAULT NOW() ,
    READCNT INT DEFAULT 0
)

;


--------------------------------------------------------------------------------


[문법]

1. 테이블 생성

CREATE TABLE [테이블명]
(
    컬럼1 데이터타입(자리수) OPTION ,
    컬럼2 데이터타입(자리수) OPTION ,
    컬럼3 데이터타입(자리수) OPTION
)
;

-데이터타입 : VARCHAR, INT, DATETIME 등

-자릿수 : VARCHAR(10), VARCHAR(20), INT, DATETIME 등


위 명령문을 실행하면 테이블이 생성된다.




반응형
저작자표시
반응형

[ORACLE] 테이블 생성 예제


--------------------------------------------------------------------------------
-- 테이블 : 통합코드 이력
-- 테이블ID : WEX001H
--------------------------------------------------------------------------------
CREATE TABLE WEX001H
(
    INTG_CD VARCHAR2(10) ,
    INTG_GRCD VARCHAR2(10) ,
    INTG_CDNM VARCHAR2(10) ,
    INTG_CD_DESC VARCHAR2(500) ,
    REG_DT DATE ,
    RGR_ID VARCHAR2(10) ,
    UPD_DT DATE ,
    UPDR_ID VARCHAR2(10)
)
;

COMMENT ON TABLE WEX001H IS '통합코드 이력';

COMMENT ON COLUMN WEX001H.INTG_CD IS '통합코드';
COMMENT ON COLUMN WEX001H.INTG_GRCD IS '통합그룹코드';
COMMENT ON COLUMN WEX001H.INTG_CDNM IS '통합코드명';
COMMENT ON COLUMN WEX001H.INTG_CD_DESC IS '통합코드설명';
COMMENT ON COLUMN WEX001H.REG_DT IS '등록일자';
COMMENT ON COLUMN WEX001H.RGR_ID IS '등록자ID';
COMMENT ON COLUMN WEX001H.UPD_DT IS '수정일자';
COMMENT ON COLUMN WEX001H.UPDR_ID IS '수정자ID';
--------------------------------------------------------------------------------
-- 테이블 : 통합그룹코드
-- 테이블ID : WEX002D
--------------------------------------------------------------------------------
CREATE TABLE WEX002D
(
    INTG_GRCD VARCHAR2(10) ,
    FNC_SNO VARCHAR2(10) NOT NULL ,
    INTG_GRP_CDNM VARCHAR2(10) ,
    INTG_GRCD_DESC VARCHAR2(500) ,
    UP_CD_EXST_YN VARCHAR2(1) DEFAULT 'N' ,
    FNC_ISU_NO NUMBER(10) DEFAULT 1 ,
    LOAN_AMT NUMBER(10) DEFAULT 0 NOT NULL ,
    EFVU_STDT DATE DEFAULT SYSDATE NOT NULL ,
    EFVU_ENDT DATE DEFAULT SYSDATE NOT NULL ,
    REG_DT DATE DEFAULT SYSDATE NOT NULL ,
    RGR_ID VARCHAR2(10) DEFAULT 'ADMIN' NOT NULL ,
    UPD_DT DATE DEFAULT SYSDATE NOT NULL ,
    UPDR_ID VARCHAR2(10) DEFAULT 'ADMIN' NOT NULL
)
;

CREATE UNIQUE INDEX PK_WEX002D ON WEX002D
 (INTG_GRCD,FNC_SNO)
;

ALTER TABLE WEX002D ADD (
 CONSTRAINT PK_WEX002D
 PRIMARY KEY (INTG_GRCD,FNC_SNO)
);

CREATE UNIQUE INDEX IX_WEX002D_N01 ON WEX002D
(FNC_SNO,FNC_ISU_NO)
;

CREATE INDEX IX_WEX002D_N02 ON WEX002D
(FNC_SNO,UP_CD_EXST_YN)
;

COMMENT ON TABLE WEX002D IS '통합그룹코드';

COMMENT ON COLUMN WEX002D.INTG_GRCD IS '통합그룹코드';
COMMENT ON COLUMN WEX002D.FNC_SNO IS '금융일련번호';
COMMENT ON COLUMN WEX002D.INTG_GRP_CDNM IS '통합그룹코드명';
COMMENT ON COLUMN WEX002D.INTG_GRCD_DESC IS '통합그룹코드설명';
COMMENT ON COLUMN WEX002D.UP_CD_EXST_YN IS '상위코드존재여부';
COMMENT ON COLUMN WEX002D.FNC_ISU_NO IS '금융발급번호';
COMMENT ON COLUMN WEX002D.LOAN_AMT IS '대출금액';
COMMENT ON COLUMN WEX002D.EFVU_STDT IS '유효값시작일자';
COMMENT ON COLUMN WEX002D.EFVU_ENDT IS '유효값종료일자';
COMMENT ON COLUMN WEX002D.REG_DT IS '등록일자';
COMMENT ON COLUMN WEX002D.RGR_ID IS '등록자ID';
COMMENT ON COLUMN WEX002D.UPD_DT IS '수정일자';
COMMENT ON COLUMN WEX002D.UPDR_ID IS '수정자ID';
--------------------------------------------------------------------------------
-- 테이블 : 통합코드
-- 테이블ID : WEX001M
--------------------------------------------------------------------------------
CREATE TABLE WEX001M
(
    INTG_CD VARCHAR2(10) ,
    INTG_GRCD VARCHAR2(10) ,
    FNC_SNO VARCHAR2(10) NOT NULL ,
    INTG_CDNM VARCHAR2(10) ,
    INTG_CD_DESC VARCHAR2(500) ,
    REG_DT DATE DEFAULT SYSDATE NOT NULL ,
    RGR_ID VARCHAR2(10) DEFAULT 'ADMIN' NOT NULL ,
    UPD_DT DATE DEFAULT SYSDATE NOT NULL ,
    UPDR_ID VARCHAR2(10) DEFAULT 'ADMIN' NOT NULL
)
;

CREATE UNIQUE INDEX PK_WEX001M ON WEX001M
 (INTG_CD,INTG_GRCD,FNC_SNO)
;

ALTER TABLE WEX001M ADD (
 CONSTRAINT PK_WEX001M
 PRIMARY KEY (INTG_CD,INTG_GRCD,FNC_SNO)
);

COMMENT ON TABLE WEX001M IS '통합코드';

COMMENT ON COLUMN WEX001M.INTG_CD IS '통합코드';
COMMENT ON COLUMN WEX001M.INTG_GRCD IS '통합그룹코드';
COMMENT ON COLUMN WEX001M.FNC_SNO IS '금융일련번호';
COMMENT ON COLUMN WEX001M.INTG_CDNM IS '통합코드명';
COMMENT ON COLUMN WEX001M.INTG_CD_DESC IS '통합코드설명';
COMMENT ON COLUMN WEX001M.REG_DT IS '등록일자';
COMMENT ON COLUMN WEX001M.RGR_ID IS '등록자ID';
COMMENT ON COLUMN WEX001M.UPD_DT IS '수정일자';
COMMENT ON COLUMN WEX001M.UPDR_ID IS '수정자ID';

--------------------------------------------------------------------------------


테이블 생성 3개


기본 테이블 : WEX001M

이력 테이블 : WEX001H

상세 테이블 : WEX002D



반응형
저작자표시

'데이터베이스 > ORACLE' 카테고리의 다른 글

[ORACLE 6장] 테이블 삭제 (DROP TABLE)  (0) 2017.10.29
[ORACLE 5장] PK인덱스, UNIQUE인덱스, 인덱스 삭제 (DROP INDEX)  (0) 2017.10.29
[ORACLE 3장] 인덱스 생성 (CREATE INDEX)  (0) 2017.10.29
[ORACLE 2장] 테이블 수정 (DEFAULT, NOT NULL 설정)  (1) 2017.10.29
[ORACLE 1장] 테이블 생성 (CREATE TABLE)  (0) 2017.10.28
반응형

[ORACLE] 테이블 생성 (CREATE TABLE)


--------------------------------------------------------------------------------

-- 테이블 : 통합코드
-- 테이블ID : WEX001M
--------------------------------------------------------------------------------
CREATE TABLE WEX001M
(
    INTG_CD VARCHAR2(10) ,
    INTG_GRCD VARCHAR2(10) ,
    FNC_SNO VARCHAR2(10) ,
    INTG_CDNM VARCHAR2(10) ,
    INTG_CD_DESC VARCHAR2(500) ,
    REG_DT DATE ,
    RGR_ID VARCHAR2(10) ,
    UPD_DT DATE ,
    UPDR_ID VARCHAR2(10)
)

;


COMMENT ON TABLE WEX001M IS '통합코드' ;

COMMENT ON COLUMN WEX001M.INTG_CD IS '통합코드';
COMMENT ON COLUMN WEX001M.INTG_GRCD IS '통합그룹코드';
COMMENT ON COLUMN WEX001M.FNC_SNO IS '금융일련번호';
COMMENT ON COLUMN WEX001M.INTG_CDNM IS '통합코드명';
COMMENT ON COLUMN WEX001M.INTG_CD_DESC IS '통합코드설명';
COMMENT ON COLUMN WEX001M.REG_DT IS '등록일자';
COMMENT ON COLUMN WEX001M.RGR_ID IS '등록자ID';
COMMENT ON COLUMN WEX001M.UPD_DT IS '수정일자';

COMMENT ON COLUMN WEX001M.UPDR_ID IS '수정자ID';

--------------------------------------------------------------------------------


[문법]

1. 테이블 생성

CREATE TABLE [테이블명]
(
    컬럼1 데이터타입(자리수) ,
    컬럼2 데이터타입(자리수) ,
    컬럼3 데이터타입(자리수)
)
;

-데이터타입 : VARCHAR2, NUMBER, SYSDATE 등

-자릿수 : NUMBER(5), NUMBER(10), NUMBER(10,5), VARCHAR(10), VARCHAR2(2000) 등


2. 테이블명 설정

COMMENT ON TABLE [테이블명] IS '[명명할 테이블 이름]' ;

3. 컬럼명 설정
COMMENT ON COLUMN [테이블명].[컬럼명] IS '[명명할 컬럼 이름]';

위 명령문을 실행하면 테이블 생성 및 테이블, 컬럼 COMMENT가 생성된다.


반응형
저작자표시

'데이터베이스 > ORACLE' 카테고리의 다른 글

[ORACLE 6장] 테이블 삭제 (DROP TABLE)  (0) 2017.10.29
[ORACLE 5장] PK인덱스, UNIQUE인덱스, 인덱스 삭제 (DROP INDEX)  (0) 2017.10.29
[ORACLE 4장] 테이블 생성 예제  (0) 2017.10.29
[ORACLE 3장] 인덱스 생성 (CREATE INDEX)  (0) 2017.10.29
[ORACLE 2장] 테이블 수정 (DEFAULT, NOT NULL 설정)  (1) 2017.10.29

+ Recent posts

티스토리툴바