국비 30-1

SQL에 Member 테이블 생성

JDBC

ALTER SESSION SET "_ORACLE_SCRIPT"=true;

-- [관리자 계정] MEMBER 계정 생성
CREATE USER khj_member IDENTIFIED BY member1234;

-- [관리자 계정] CONNECT, RESOURCE + CREATE VIEW 권한 부여 + 객체 생성 공간 할당
GRANT CONNECT, RESOURCE, CREATE VIEW TO khj_member;
ALTER USER khj_member DEFAULT TABLESPACE SYSTEM QUOTA UNLIMITED ON SYSTEM;

-- khj_member 계정 접속 방법 추가

-- [멤버 계정]
CREATE TABLE MEMBER(
    MEMBER_NO NUMBER PRIMARY KEY,
    MEMBER_ID VARCHAR2(20) NOT NULL,
    MEMBER_PW VARCHAR2(20) NOT NULL,
    MEMBER_NM VARCHAR2(30) NOT NULL,
    MEMBER_GENDER CHAR(1) CHECK (MEMBER_GENDER IN ('M','F')),
    ENROLL_DATE DATE DEFAULT SYSDATE,
    SECESSION_FL CHAR(1) DEFAULT 'N' CHECK (SECESSION_FL IN ('Y','N'))
);
COMMENT ON COLUMN MEMBER.MEMBER_NO IS '회원번호(PK)';
COMMENT ON COLUMN MEMBER.MEMBER_ID IS '회원 아이디';
COMMENT ON COLUMN MEMBER.MEMBER_PW IS '회원 비밀번호';
COMMENT ON COLUMN MEMBER.MEMBER_NM IS '회원 이름';
COMMENT ON COLUMN MEMBER.MEMBER_GENDER IS '회원 성별(M/F)';
COMMENT ON COLUMN MEMBER.ENROLL_DATE IS '회원 가입일';
COMMENT ON COLUMN MEMBER.SECESSION_FL IS '탈퇴여부(Y/N)';


-- 게시판 테이블
DROP TABLE BOARD;

CREATE TABLE BOARD(
    BOARD_NO NUMBER PRIMARY KEY,
    BOARD_TITLE VARCHAR2(200) NOT NULL,
    BOARD_CONTENT VARCHAR2(4000) NOT NULL,
    CREATE_DATE DATE DEFAULT SYSDATE,
    READ_COUNT NUMBER DEFAULT 0,
    MEMBER_NO NUMBER REFERENCES MEMBER -- MEMBER 테이블 PK 값 참조

);

COMMENT ON COLUMN BOARD.BOARD_NO IS '게시글 번호';
COMMENT ON COLUMN BOARD.BOARD_TITLE IS '게시글 제목';
COMMENT ON COLUMN BOARD.BOARD_CONTENT IS '게시글 내용';
COMMENT ON COLUMN BOARD.CREATE_DATE IS '게시글 작성일';
COMMENT ON COLUMN BOARD.READ_COUNT IS '조회수';
COMMENT ON COLUMN BOARD.MEMBER_NO IS '회원번호 (작성자)';

-- 댓글 테이블
DROP TABLE REPLY;

CREATE TABLE REPLY (
    REPLY_NO NUMBER PRIMARY KEY,
    REPLY_CONTENT VARCHAR2(500) NOT NULL,
    CREATE_DATE DATE DEFAULT SYSDATE,
    MEMBER_NO NUMBER REFERENCES MEMBER, -- MEMBER 테이블 PK 참조
    BOARD_NO NUMBER REFERENCES BOARD -- BOARD 테이블 PK 참조

);

COMMENT ON COLUMN REPLY.REPLY_NO IS '댓글 번호(PK)';
COMMENT ON COLUMN REPLY.REPLY_CONTENT IS '댓글 내용';
COMMENT ON COLUMN REPLY.CREATE_DATE IS '댓글 작성일';
COMMENT ON COLUMN REPLY.MEMBER_NO IS '회원번호(작성자)';
COMMENT ON COLUMN REPLY.BOARD_NO IS '게시글 번호(어떤 게시글의 댓글인지 확인)';

-- 각 테이블 PK 생성용 시퀀스 생성
CREATE SEQUENCE SEQ_MEMBER_NO; -- 1부터 1씩 증가, 반복X
CREATE SEQUENCE SEQ_BOARD_NO;
CREATE SEQUENCE SEQ_REPLY_NO;