03. DB : SQL (create문)

○ CREATE 문

    - member_info (회원정보)

CREATE TABLE member_info (
     mem_id    VARCHAR(10) NOT NULL PRIMARY KEY
    ,mem_pw    VARCHAR(20) NOT NULL
    ,mem_phone VARCHAR(15) NOT NULL UNIQUE
    ,mem_email VARCHAR(30) NOT NULL
    ,mem_lv    VARCHAR(2)  NOT NULL DEFAULT 'B' CHECK (mem_lv IN('A','B','F'))
    ,mem_reg   DATETIME
    ,mem_birth VARCHAR(10) NOT NULL
);

    - payment_card (결제카드)

CREATE TABLE payment_card (
     mem_id     VARCHAR(10) NOT NULL
    ,card_exp	VARCHAR(5)  NOT NULL
    ,card_no	VARCHAR(20) NOT NULL PRIMARY KEY
    ,card_pw	TINYINT     NOT NULL
    ,card_com	VARCHAR(20) NOT NULL
    ,FOREIGN KEY(mem_id) REFERENCES member_info(mem_id)  
);

    - party_info (파티정보:파티장)

CREATE TABLE party_info (
     party_id       VARCHAR(20) NOT NULL PRIMARY KEY
    ,mem_id         VARCHAR(10) NOT NULL
    ,ott_name       VARCHAR(20) NOT NULL
    ,ott_price      INT         NOT NULL
    ,ott_id         VARCHAR(40) NOT NULL
    ,ott_pw         VARCHAR(20) NOT NULL
    ,ott_cdate      DATETIME    NOT NULL
    ,bank_name      VARCHAR(20) NOT NULL
    ,bank_account   VARCHAR(20) NOT NULL
    ,payback_amount INT			
    ,payback_result CHAR(2)     NOT NULL DEFAULT 'N' CHECK(payback_result IN('Y', 'N'))
    ,matching_no    TINYINT     NOT NULL DEFAULT 1   CHECK(matching_no BETWEEN 1 AND 4)
);

    - party_waiting (매칭 대기 파티원 목록)

CREATE TABLE party_waiting (
     mem_id       VARCHAR(10) NOT NULL
    ,ott_name     VARCHAR(20) NOT NULL
    ,waiting_no   INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,waiting_date DATETIME
    ,foreign key(mem_id) REFERENCES member_info(mem_id)
);

    - party_member (매칭 완료 파티원 목록)

CREATE TABLE party_member(
     mem_id          VARCHAR(10) NOT NULL
   , party_id        VARCHAR(20) NOT NULL
   , party_pcost     INT         DEFAULT 500
   , party_pdate     DATETIME
   , party_ordnumber VARCHAR(20) NOT NULL PRIMARY KEY
   , foreign key(mem_id)   REFERENCES member_info(mem_id)
   , foreign key(party_id) REFERENCES party_info(party_id)	
);

    - subscribe_info (구독OTT 정보)

CREATE TABLE subscribe_info (
     subscribe_no    VARCHAR(20) NOT NULL PRIMARY KEY
    ,mem_id          VARCHAR(10) NOT NULL
    ,party_id        VARCHAR(20) NOT NULL
    ,subscribe_start DATETIME    NOT NULL
    ,subscribe_end   DATETIME    NOT NULL
    ,party_role      VARCHAR(12) NOT NULL CHECK(party_role IN('파티장','파티원'))
    ,FOREIGN KEY(mem_id) REFERENCES member_info(mem_id)
    ,FOREIGN KEY(party_id) REFERENCES party_info(party_id)
);

    - contlist (컨텐츠 목록)

CREATE TABLE contlist (
     mcode      INT         NOT NULL AUTO_INCREMENT PRIMARY KEY 
    ,mtitle     VARCHAR(50) NOT NULL
    ,mtitle_eng VARCHAR(50)
    ,mthum      MEDIUMBLOB  NOT NULL
    ,mrate      FLOAT       NOT NULL DEFAULT 0   CHECK(mrate BETWEEN 0 AND 5)
    ,netflix    CHAR(1)     NOT NULL DEFAULT 'X' CHECK(netflix IN('O','X')),
    ,watcha     CHAR(1)     NOT NULL DEFAULT 'X' CHECK(watcha IN('O','X')),
    ,tving      CHAR(1)     NOT NULL DEFAULT 'X' CHECK(tving IN('O','X'))
    ,disney     CHAR(1)     NOT NULL DEFAULT 'X' CHECK(disney IN('O','X'))
    ,mdate      VARCHAR(5)  NOT NULL
    ,key_code   TEXT        NOT NULL
    ,cri_like   INT
    ,maudio     VARCHAR(20)
    ,director   VARCHAR(50)
    ,actor      VARCHAR(100)
);

    - search_key (컨텐츠 검색 키워드)

CREATE TABLE search_key(
    key_name VARCHAR(50) NOT NULL  
   ,key_code VARCHAR(10) NOT NULL 
);

    - people (감독/배우 목록)

CREATE TABLE people (
     pno       VARCHAR(10) NOT NULL PRIMARY KEY 
    ,pname     VARCHAR(20) NOT NULL
    ,pphoto    VARCHAR(50)
    ,pname_eng VARCHAR(60)
);

    - watch_list (컨텐츠 시청 목록)

CREATE TABLE watch_list(
    mem_id      VARCHAR(10) NOT NULL
   ,mcode       INT         NOT NULL
   ,watch_time  DATETIME    NOT NULL
   ,watch_reg   VARCHAR(20) NOT NULL PRIMARY KEY   
   ,FOREIGN KEY(mem_id) REFERENCES member_info(mem_id)    
   ,FOREIGN KEY(mcode)  REFERENCES contlist(mcode) 
);

    - review (컨텐츠 리뷰)

CREATE TABLE review(
    rev_code  INT          NOT NULL AUTO_INCREMENT PRIMARY KEY
   ,mem_id    VARCHAR(10)  NOT NULL 
   ,mcode     INT          NOT NULL   
   ,rev_title VARCHAR(100) NOT NULL      
   ,rev_reg   DATETIME     NOT NULL   
   ,rev_cont  TEXT         NOT NULL
   ,rev_spo   CHAR(1)      NOT NULL DEFAULT 'X' CHECK(rev_spo IN('O', 'X'))
   ,rev_rate  TINYINT(1)   NOT NULL DEFAULT '1' CHECK(rev_rate BETWEEN 0 AND 5)
   ,FOREIGN KEY(mcode)  REFERENCES contlist(mcode)
   ,FOREIGN KEY(mem_id) REFERENCES member_info(mem_id)
);

    - content_critic (컨텐츠 평가)

CREATE TABLE content_critic (
     cri_code  INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,mcode     INT         NOT NULL
    ,cri_like  TINYINT     DEFAULT 0
    ,cri_watch TINYINT     DEFAULT 0
    ,cri_point TINYINT     DEFAULT 0
    ,mem_id    VARCHAR(10) NOT NULL
   ,FOREIGN KEY(mcode)  REFERENCES contlist(mcode)
   ,FOREIGN KEY(mem_id) REFERENCES member_info(mem_id)
);

    - themes (컨텐츠 추천 테마)

CREATE TABLE `themes` (
     t_num   INT          NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,t_title VARCHAR(100) NOT NULL
    ,t_photo VARCHAR(100) NOT NULL
);

    - rec_theme (컨텐츠 추천글)

CREATE TABLE rec_theme (
     r_num     INT          NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,r_title   VARCHAR(100) NOT NULL
    ,r_date    DATETIME     NOT NULL
    ,t_num     INT          NOT NULL
    ,r_content TEXT         NOT NULL
    ,r_photo   VARCHAR(100) NOT NULL
    ,mcodes    VARCHAR(100) NOT NULL
    ,FOREIGN KEY(mcode) REFERENCES contlist(mcode)
);

    - notice (공지사항)

CREATE TABLE notice (
     n_num     INT          NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,n_title   VARCHAR(100) NOT NULL
    ,n_date    DATETIME     NOT NULL
    ,n_content TEXT         NOT NULL
    ,n_readcnt INT          NOT NULL DEFAULT 0
);

    - tb_qna (문의사항)

CREATE TABLE tb_qna (
     qna_num     INT          NOT NULL AUTO_INCREMENT PRIMARY KEY
    ,qna_title   VARCHAR(100) NOT NULL
    ,qna_date    DATETIME     NOT NULL
    ,qna_content TEXT         NOT NULL
    ,mem_id      VARCHAR(10)  NOT NULL
    ,qna_pw      VARCHAR(15)  NOT NULL
    ,qna_readcnt INT          NOT NULL DEFAULT 0
    ,qna_grpno   TINYINT      NOT NULL
    ,qna_indent  TINYINT      NOT NULL DEFAULT 0
    ,qna_ansnum  TINYINT      NOT NULL DEFAULT 0
    ,ip          VARCHAR(15)  NOT NULL
    ,FOREIGN KEY(mem_id) REFERENCES member_info(mem_id) 
);