DB 쿼리

회원 테이블 생성

CREATE TABLE MEMBER (
   USER_NO NUMBER(10) PRIMARY KEY,
   EMAIL VARCHAR2(60) NOT NULL UNIQUE,
   PASSWORD VARCHAR2(65) NOT NULL,
   USER_NAME VARCHAR2(30) NOT NULL,
   USER_TYPE VARCHAR2(10) DEFAULT 'USER' CHECK(USER_TYPE IN ('USER', 'ADMIN')),
   MY_POKEMON_SEQ NUMBER(11) DEFAULT 0,
   REG_DT DATE DEFAULT SYSDATE,
   MOD_DT DATE
);

회원 관리자 설정

UPDATE MEMBER SET USER_TYPE='ADMIN' WHERE EMAIL='user04@test.org';

COMMIT;

포켓몬 테이블 생성

CREATE TABLE POKEMON (
   SEQ NUMBER(10) PRIMARY KEY,
   NAME VARCHAR2(60) NOT NULL,
   NAME_KR VARCHAR2(60),
   TYPES1 VARCHAR2(20),
   TYPES2 VARCHAR2(20),
   WEIGHT NUMBER(7) DEFAULT 0,
   HEIGHT NUMBER(7) DEFAULT 0,
   BASE_EXPERIENCE NUMBER(10) DEFAULT 0,
   FRONT_IMAGE VARCHAR2(150),
   BACK_IMAGE VARCHAR2(150),
   RAW_DATA CLOB,
   DESCRIPTION VARCHAR2(1000)
);

마이포켓몬 테이블 생성(랜덤 포켓몬 뽑기 데이터 저장)

CREATE TABLE MY_POKEMON (
   USER_NO NUMBER(11),
   SEQ NUMBER(11),
   PRIMARY KEY(USER_NO, SEQ)
);

게시판 테이블 생성

CREATE TABLE BOARD (
   ART_NO NUMBER(10) PRIMARY KEY,
   ART_TITLE VARCHAR2(200) NOT NULL,
   USER_NO NUMBER(10) NOT NULL,
   ART_BODY CLOB,
   FILE_NAME VARCHAR2(50),
   READ_CNT NUMBER(10) DEFAULT 0,
   REG_DT DATE DEFAULT SYSDATE,
   MOD_DT DATE
);

CREATE SEQUENCE SEQ_BOARD;

게시판 파일 업로드 관련 쿼리

ALTER TABLE BOARD ADD gid varchar2(45);

CREATE TABLE FILE_INFO (
   SEQ number(11) PRIMARY KEY,
   GID VARCHAR2(45) NOT NULL,
   LOCATION VARCHAR2(45),
   FILE_NAME VARCHAR2(100) NOT NULL,
   EXTENSION VARCHAR2(30),
   CONTENT_TYPE VARCHAR2(65),
   DONE NUMBER(1) DEFAULT 0,
   REG_DT DATE DEFAULT SYSDATE
);

CREATE SEQUENCE seq_file_info;

UPDATE board SET gid = '11' WHERE gid is null;