search

KHAcademyProject2020 / Spring-Project-Trip2Reap

국내 여행 사이트
Apache License 2.0
0 stars 0 forks source link

[2020.12.10 ~ 2020.12.11 ] 테이블 정리 #11

Open loveAlakazam opened 3 years ago

loveAlakazam commented 3 years ago

2020.12.10 - 데이터베이스 공통 테이블 정리

1. 계정생성 및 권한 부여 (접속계정: SYSTEM)

CREATE USER TRIP2REAP IDENTIFIED BY TRIP2REAP;
GRANT RESOURCE, CONNECT TO TRIP2REAP;

GRANT CREATE VIEW TO TRIP2REAP; --VIEW 생성권한 부여

GRANT CREATE SYNONYM TO TRIP2REAP; --SYNONYM 생성권한 부여



> # 2. 기존테이블 & 시퀀스 삭제 (접속계정: TRIP2REAP) ```SQL -- TABLE 삭제 DROP TABLE MEMBER CASCADE CONSTRAINTS; DROP TABLE CATEGORY CASCADE CONSTRAINTS; DROP TABLE HOTEL CASCADE CONSTRAINTS; DROP TABLE HOTEL_RESERVATION CASCADE CONSTRAINTS; DROP TABLE HOTEL_REVIEW CASCADE CONSTRAINTS; DROP TABLE IMG_FILE CASCADE CONSTRAINTS; DROP TABLE REPLY CASCADE CONSTRAINTS; DROP TABLE LOCAL_INFO CASCADE CONSTRAINTS; DROP TABLE ROOM CASCADE CONSTRAINTS; DROP TABLE BOARD CASCADE CONSTRAINTS; DROP TABLE LIKES CASCADE CONSTRAINTS; DROP TABLE CERTIFICATION; DROP TABLE COURSE CASCADE CONSTRAINTS; DROP TABLE TRAVEL CASCADE CONSTRAINTS; --뷰삭제 DROP VIEW HOTEL_DETAIL_IMG_VIEW; DROP VIEW HOTEL_MINPRICE_ORDER_VIEW; DROP VIEW HOTEL_REVIEW_INFO_VIEW; DROP VIEW HOTEL_ROOM_INFO_VIEW; DROP VIEW HOTEL_THUMBNAIL_IMG_VIEW; DROP VIEW HOTEL_VIEW; DROP VIEW TLIST; DROP VIEW TDETAIL; --시퀀스 삭제 DROP SEQUENCE SEQ_BO_NO; DROP SEQUENCE SEQ_FILE_NO; DROP SEQUENCE SEQ_RE_NO; DROP SEQUENCE SEQ_TRNO; DROP SEQUENCE SEQ_ROOM_NO; DROP SEQUENCE SEQ_RESERVE_NO; DROP SEQUENCE SEQ_CO_NO; ```

# 3. 회원 테이블 (김기쁨) > # 3-1. MEMBER ```SQL CREATE TABLE MEMBER( MEMBER_ID VARCHAR2(30) PRIMARY KEY, MEMBER_PWD VARCHAR2(100), MEMBER_NAMES VARCHAR2(30), NICKNAME VARCHAR2(30) NOT NULL, GENDER VARCHAR2(10), PHONE VARCHAR2(30), MEMBER_EMAIL VARCHAR2(50), M_CATEGORY VARCHAR2(10) NOT NULL, M_STATUS VARCHAR2(10) NOT NULL, CHECK(GENDER IN('남자','여자')), CHECK(M_STATUS IN('Y','N')) ); ALTER TABLE MEMBER MODIFY (M_STATUS DEFAULT 'Y'); ALTER TABLE MEMBER ADD UNIQUE(PHONE); ```
> # 3-2. MEMBER 샘플데이터 삽입 ```sql INSERT INTO MEMBER VALUES('admin', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '관리자', '관리자', '여자', '01012345678','admin@naver.com','member' ,default); INSERT INTO MEMBER VALUES('soo', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '김수진', '수진k', '여자', '01095266236','sjrlaa@gmail.com','member' ,default); INSERT INTO MEMBER VALUES('ko1850', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '이규호', '규호', '남자', '01024593141','ko1804@naver.com','member' ,default); INSERT INTO MEMBER VALUES('TRIP', '$2a$10$a3KAdt7hoVH02YhsqIWszuXUumRpihW3kHnEe1z3hRMbcrs7.bAjK', '최은강', 'loveAlakazam', '여자', '01071685268','dmsrkd1216@gmail.com','member' ,default); INSERT INTO MEMBER VALUES('joy', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '김기쁨', 'joy', '여자', '01030987909','lcocvce@naver.com','member' ,default); ```
> # 3-3. CERTIFICATION ```SQL --인증번호 테이블 CREATE TABLE CERTIFICATION( EMAIL VARCHAR2(50) NOT NULL, RANDOMKEY VARCHAR2(50) NOT NULL, PRIMARY KEY(EMAIL, RANDOMKEY)); ```

> # 4. CATEGORY ```sql --카테고리 테이블 CREATE TABLE CATEGORY( CA_CODE NUMBER(5) NOT NULL, CA_NAME VARCHAR2(30) NOT NULL, PRIMARY KEY (CA_CODE), CHECK(CA_CODE BETWEEN 1 AND 5) --카테고리번호는 1~5로만 제한한다. ); --카테고리 데이터 삽입 INSERT INTO CATEGORY VALUES(1, '여행지'); INSERT INTO CATEGORY VALUES(2, '여행 코스'); INSERT INTO CATEGORY VALUES(3, '여행후기 사진'); INSERT INTO CATEGORY VALUES(4, '여행후기 목록'); INSERT INTO CATEGORY VALUES(5, '호텔'); ```

> # 5. BOARD ```SQL --BOARD 테이블 --생성시 제약조건 추가 CREATE TABLE BOARD( BO_NO NUMBER(10) NOT NULL, CA_CODE NUMBER(5) NOT NULL, BO_TITLE VARCHAR2(300) NOT NULL, BO_CONTENT LONG, MEMBER_ID VARCHAR2(30) NOT NULL, BO_COUNT NUMBER(10) DEFAULT 0 NOT NULL, BO_TAG VARCHAR2(300), BO_DELETE_YN VARCHAR2(10) DEFAULT 'N' NOT NULL, REGDATE DATE DEFAULT SYSDATE, PRIMARY KEY (BO_NO), FOREIGN KEY (CA_CODE) REFERENCES CATEGORY(CA_CODE), CONSTRAINT FK_BO_MID FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER(MEMBER_ID) ON DELETE CASCADE, --나중에 제약조건 추가 CHECK (CA_CODE BETWEEN 1 AND 5), --테이블 카테고리번호는 1,2,3,4,5 로 제한한다. CHECK (BO_DELETE_YN IN ('Y', 'N')) ); ```

> # 6. IMG_FILE ```sql CREATE TABLE IMG_FILE( FILE_NO NUMBER(10) NOT NULL, BO_NO NUMBER(10) NOT NULL, ORIGIN_NAME VARCHAR2(300) NOT NULL, CHANGE_NAME VARCHAR2(300) NOT NULL, FILE_LEVEL NUMBER(5) NOT NULL, FILE_PATH VARCHAR2(1000), FILE_DELETE_YN VARCHAR2(10) DEFAULT 'N' NOT NULL, -- 제약조건 PRIMARY KEY(FILE_NO), CONSTRAINT FK_IMG_BNO FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO) ON DELETE CASCADE, --나중에 제약조건 추가 CHECK(FILE_LEVEL IN (1,2) ), --파일레벨 1(썸네일) / 2(기본 이미지) CHECK(FILE_DELETE_YN IN ('Y', 'N')) ); ```

> # 7. LIKES ```SQL --LIKES 테이블 CREATE TABLE LIKES( BO_NO NUMBER(10) NOT NULL, LIKE_YN VARCHAR2(10) DEFAULT 'N' NOT NULL, MEMBER_ID VARCHAR2(30) NOT NULL, PRIMARY KEY(BO_NO, MEMBER_ID), CONSTRAINT FK_LK_MID FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID) ON DELETE CASCADE, --나중에 제약조건추가 CHECK(LIKE_YN IN ('Y', 'N')) ); ```

> # 8. REPLY ```sql --REPLY 테이블(은강/ 규호 공통) CREATE TABLE REPLY( RE_NO NUMBER(10) NOT NULL, BO_NO NUMBER(10) NOT NULL, MEMBER_ID VARCHAR2(30) NOT NULL, RE_DATE DATE DEFAULT SYSDATE NOT NULL, RE_CONTENT VARCHAR2(1500) NOT NULL, RE_DELETE_YN VARCHAR2(10) DEFAULT 'N' NOT NULL, PRIMARY KEY(RE_NO), --파일번호 CONSTRAINT RP_FK_BNO FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO) ON DELETE CASCADE, --게시판 번호+나중에제약조건추가 CONSTRAINT RP_FK_MID FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID) ON DELETE CASCADE, --작성자 아이디+나중에제약조건추가 CHECK(RE_DELETE_YN IN ('Y', 'N')) --파일 삭제여부 (Y/N) ); ```
loveAlakazam commented 3 years ago

시퀀스

1. SEQ_BO_NO (BOARD 테이블 컬럼: BO_NO)

CREATE SEQUENCE SEQ_BO_NO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;


2. SEQ_FILE_NO (IMG_FILE 테이블 컬럼: FILE_NO)

CREATE SEQUENCE SEQ_FILE_NO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;


3. SEQ_RE_NO (REPLY테이블 컬럼: RE_NO)

CREATE SEQUENCE SEQ_RE_NO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;


4. SEQ_TRNO (TRAVEL테이블 컬럼: TR_NO)

CREATE SEQUENCE SEQ_TRNO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;


5. SEQ_ROOM_NO (ROOM테이블: ROOM_NO)

CREATE SEQUENCE SEQ_ROOM_NO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;


6. SEQ_RESERVE_NO (RESERVATION테이블: RESERVE_NO)

CREATE SEQUENCE SEQ_RESERVE_NO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;


7. SEQ_CO_NO (COURSE테이블: CO_NO)

CREATE SEQUENCE SEQ_CO_NO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;
loveAlakazam commented 3 years ago

호텔 (최은강)

호텔 - 테이블

1. LOCAL_INFO

--LOCAL_INFO
CREATE TABLE LOCAL_INFO(
    LOCAL_CODE NUMBER(5) NOT NULL,      --지역번호
    LOCAL_NAME VARCHAR2(50) NOT NULL,    --지역이름
    PRIMARY KEY(LOCAL_CODE)
);

--지역정보 데이터 삽입
INSERT INTO LOCAL_INFO VALUES(1,'강원도');
INSERT INTO LOCAL_INFO VALUES(2,'경기도');
INSERT INTO LOCAL_INFO VALUES(3,'경상남도');
INSERT INTO LOCAL_INFO VALUES(4,'경상북도');
INSERT INTO LOCAL_INFO VALUES(5,'광주광역시');
INSERT INTO LOCAL_INFO VALUES(6, '대구광역시');
INSERT INTO LOCAL_INFO VALUES(7, '대전광역시');
INSERT INTO LOCAL_INFO VALUES(8, '부산광역시');
INSERT INTO LOCAL_INFO VALUES(9, '서울특별시');
INSERT INTO LOCAL_INFO VALUES(10, '세종특별자치시');
INSERT INTO LOCAL_INFO VALUES(11, '인천광역시');
INSERT INTO LOCAL_INFO VALUES(12, '울산광역시');
INSERT INTO LOCAL_INFO VALUES(13, '전라남도');
INSERT INTO LOCAL_INFO VALUES(14, '전라북도');
INSERT INTO LOCAL_INFO VALUES(15, '제주특별자치도');
INSERT INTO LOCAL_INFO VALUES(16, '충청남도');
INSERT INTO LOCAL_INFO VALUES(17, '충청북도');

COMMIT;


2. HOTEL

--HOTEL 테이블
CREATE TABLE HOTEL(
    BO_NO NUMBER(10) NOT NULL, --호텔번호
    HOTEL_ADDRESS VARCHAR2(1000) NOT NULL,--호텔주소

    HOTEL_LOCAL_CODE NUMBER(5) NOT NULL,--지역코드

    HOTEL_SITE VARCHAR2(1000),--호텔사이트
    HOTEL_TEL VARCHAR2(30) NULL,--전화번호
    HOTEL_REVIEW_SCORE NUMBER(5,1) DEFAULT 0 NOT NULL, --호텔 후기 평점
    HOTEL_RANK NUMBER(5),  --호텔등급(1~5, 없음)

    HOTEL_OPEN_TIME NUMBER(5) DEFAULT 0 NOT NULL,--호텔오픈시각
    HOTEL_CLOSE_TIME NUMBER(5) DEFAULT 23 NOT NULL,--호텔종료시각
    HOTEL_OPTION VARCHAR2(2000), --호텔옵션
    CHECK_IN NUMBER(5) DEFAULT 10 NOT NULL, --체크인
    CHECK_OUT NUMBER(5) DEFAULT 15 NOT NULL,--체크아웃

    -- 제약조건
    CONSTRAINT HT_PK PRIMARY KEY (BO_NO),       --기본키(BO_NO)
    CONSTRAINT HT_FK_BNO FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO) ON DELETE CASCADE,  --외래키(BO_NO) + 나중에 삭제제약추가
    FOREIGN KEY(HOTEL_LOCAL_CODE) REFERENCES LOCAL_INFO(LOCAL_CODE) --외래키(HOTEL_LOCAL_CODE)
);


3. ROOM

--ROOM 테이블
--호텔방(ROOM)테이블
CREATE TABLE ROOM(
    ROOM_NO NUMBER(10),                     --방번호
    BO_NO NUMBER(10) NOT NULL,              --호텔번호
    ROOM_TYPE VARCHAR2(200) NOT NULL,       --방종류
    ROOM_NAME VARCHAR2(1000) NOT NULL,      --방이름
    PRICE_PER_DAY NUMBER(10) NOT NULL,          --1인당 이용가격
    CONSTRAINT RO_PK PRIMARY KEY(ROOM_NO),  --기본키(ROOM_NO)
    CONSTRAINT RO_FK FOREIGN KEY(BO_NO) REFERENCES HOTEL(BO_NO) ON DELETE CASCADE --외래키(BO_NO)+나중에 삭제제약추가
);


4. HOTEL_REVIEW

--호텔리뷰 댓글
CREATE TABLE HOTEL_REVIEW(
    RE_NO NUMBER(10),   --댓글번호
    REVIEW_SCORE NUMBER(5) NOT NULL,
    CONSTRAINT HR_PK_RVN PRIMARY KEY(RE_NO),                            --기본키(RE_NO)
    CONSTRAINT HR_FK_RVN FOREIGN KEY(RE_NO) REFERENCES REPLY(RE_NO) ON DELETE CASCADE ,    --외래키(RE_NO)+외래키제약조건추가
    CONSTRAINT HR_CK_RVS CHECK(REVIEW_SCORE BETWEEN 0 AND 5)            --제약조건(REVIEW_SCORE 0~5점만 부여가능)
);


5. HOTEL_RESERVATION

CREATE TABLE HOTEL_RESERVATION(
    RESERVE_NO NUMBER(10),              --예약번호
    MEMBER_ID VARCHAR2(30) NOT NULL,    --예약자ID
    BO_NO NUMBER(10) NOT NULL,          --호텔번호
    ROOM_NO NUMBER(10) NOT NULL,        --방번호
    RESERVE_PRICE NUMBER(10) NOT NULL,  --예약가격(총가격)

    RESERVE_ROOM_CNT NUMBER(10) DEFAULT 1 NOT NULL,   --예약 객실 수(기본값:1)
    RESERVE_TOTAL_PERSON_CNT NUMBER(10) DEFAULT 2 NOT NULL,         --예약인원수(전체인원수: 기본값 2)
    RESERVE_PERSON_ADULT_CNT NUMBER(10) DEFAULT 2 NOT NULL,--성인수(기본값: 2)
    RESERVE_PERSON_CHILD_CNT NUMBER(10) DEFAULT 0 NOT NULL,--어린이수(기본값: 0)

    CHECK_IN_DATE DATE DEFAULT SYSDATE NOT NULL,    --체크인 날짜
    CHECK_OUT_DATE DATE DEFAULT SYSDATE NOT NULL,   --체크아웃 날짜
    RESERVE_CHECK VARCHAR2(5) DEFAULT 'N' NOT NULL, --예약확인
    REFUND_CHECK VARCHAR2(5)  DEFAULT 'N' NOT NULL, --환불확인

    CONSTRAINT RS_PK PRIMARY KEY(RESERVE_NO),                                   --기본키(RESERVE_NO)
    CONSTRAINT RS_FK_MID FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID) ON DELETE CASCADE ,   --외래키(MEMBER_ID)+나중에 제약조건추가
    CONSTRAINT RS_FK_BN FOREIGN KEY(BO_NO) REFERENCES HOTEL(BO_NO) ON DELETE CASCADE,   --외래키(BO_NO)+나중에 제약조건추가
    CONSTRAINT RS_FK_RN FOREIGN KEY(ROOM_NO) REFERENCES ROOM(ROOM_NO) ON DELETE CASCADE,   --외래키(ROOM_NO)
    CONSTRAINT RS_CK_RSC CHECK(RESERVE_CHECK IN ('Y', 'N')),                    --제약조건(RESERVE_CHECK)
    CONSTRAINT RS_CK_RFC CHECK(REFUND_CHECK IN('Y','N'))                        --제약조건(REFUND_CHECK)
);


호텔 - 뷰

1. HOTEL_VIEW

CREATE OR REPLACE VIEW HOTEL_VIEW
AS
SELECT *
FROM BOARD JOIN HOTEL USING(BO_NO)
WHERE BO_DELETE_YN='N' AND CA_CODE=5;

COMMIT;


2. HOTEL_ROOM_INFO_VIEW

CREATE OR REPLACE VIEW HOTEL_ROOM_INFO_VIEW
AS
SELECT *
FROM BOARD
    JOIN HOTEL USING(BO_NO)
    JOIN ROOM USING(BO_NO)
WHERE BO_DELETE_YN='N';

COMMIT;


3. HOTEL_REVIEW_INFO_VIEW

CREATE OR REPLACE VIEW HOTEL_REVIEW_INFO_VIEW
AS
SELECT *
FROM REPLY JOIN HOTEL_REVIEW USING(RE_NO);


4. HOTEL_THUMBNAIL_IMG_VIEW

CREATE OR REPLACE VIEW HOTEL_THUMBNAIL_IMG_VIEW
AS
SELECT *
FROM BOARD
    JOIN IMG_FILE USING(BO_NO)
WHERE BO_DELETE_YN='N' AND FILE_DELETE_YN='N' AND FILE_LEVEL=1 AND CA_CODE=5;
COMMIT;


5. HOTEL_DETAIL_IMG_VIEW

CREATE OR REPLACE VIEW HOTEL_DETAIL_IMG_VIEW
AS
SELECT *
FROM BOARD
    JOIN IMG_FILE USING(BO_NO)
WHERE BO_DELETE_YN='N' AND FILE_DELETE_YN='N' AND FILE_LEVEL=2 AND CA_CODE=5;
COMMIT;


6. HOTEL_MINPRICE_ORDER_VIEW

CREATE OR REPLACE VIEW HOTEL_MINPRICE_ORDER_VIEW
AS
SELECT BO_NO, MIN(PRICE_PER_DAY) AS MIN_PRICE
FROM HOTEL_ROOM_INFO_VIEW
GROUP BY BO_NO;

COMMIT;
loveAlakazam commented 3 years ago

여행지 (김수진)

여행지 - 테이블

1. TRAVEL

 CREATE TABLE "TRIP2REAP"."TRAVEL" 
   (    "TR_NO" NUMBER(10,0), 
    "BO_NO" NUMBER(10,0) NOT NULL ENABLE, 
    "TR_ADDR" VARCHAR2(300 BYTE) NOT NULL ENABLE, 
    "TR_THEME" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
    "TR_REG" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
    "TR_TITLE" VARCHAR2(100 BYTE), 
    "TR_PHONE" VARCHAR2(20 BYTE), 
     PRIMARY KEY ("TR_NO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE, 
     FOREIGN KEY ("BO_NO")
      REFERENCES "TRIP2REAP"."BOARD" ("BO_NO") ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;



여행지 - 뷰

1. TLIST

CREATE OR REPLACE FORCE VIEW "TRIP2REAP"."TLIST" ("BO_NO", "CA_CODE", "BO_TITLE", "BO_CONTENT", "MEMBER_ID", "BO_COUNT", "BO_TAG", "BO_DELETE_YN", "TR_ADDR", "TR_THEME", "CHANGE_NAME")
AS
SELECT BOARD.BO_NO, BOARD.CA_CODE, BO_TITLE, BO_CONTENT, BOARD.MEMBER_ID, BO_COUNT, BO_TAG, BO_DELETE_YN, TR_ADDR, TR_THEME, CHANGE_NAME
FROM BOARD
    JOIN TRAVEL T ON (T.BO_NO = BOARD.BO_NO)
    JOIN MEMBER M ON (M.MEMBER_ID = BOARD.MEMBER_ID)
    JOIN CATEGORY C ON (C.CA_CODE = BOARD.CA_CODE)
    JOIN IMG_FILE I ON (BOARD.BO_NO = I.BO_NO)
WHERE BO_DELETE_YN = 'N'
ORDER BY REGDATE DESC;


2. TDETAIL

CREATE OR REPLACE FORCE VIEW "TRIP2REAP"."TDETAIL" ("BO_NO", "CA_CODE", "BO_TITLE", "BO_CONTENT", "MEMBER_ID", "BO_COUNT", "BO_TAG", "BO_DELETE_YN", "REGDATE", "TR_ADDR", "TR_THEME", "CHANGE_NAME")
AS
SELECT BOARD.BO_NO, BOARD.CA_CODE, BO_TITLE, BO_CONTENT, BOARD.MEMBER_ID, BO_COUNT, BO_TAG, BO_DELETE_YN, REGDATE, TR_ADDR, TR_THEME, CHANGE_NAME
FROM BOARD
  JOIN TRAVEL T ON (T.BO_NO = BOARD.BO_NO)
  JOIN MEMBER M ON (M.MEMBER_ID = BOARD.MEMBER_ID)
  JOIN CATEGORY C ON (C.CA_CODE = BOARD.CA_CODE)
  JOIN IMG_FILE I ON (BOARD.BO_NO = I.BO_NO)
WHERE BO_DELETE_YN = 'N'
ORDER BY REGDATE DESC;
loveAlakazam commented 3 years ago

여행코스 (김기쁨)

여행코스 - 테이블

1. COURSE

CREATE TABLE COURSE(
    COURSE_NO NUMBER(10) PRIMARY KEY,                     
    BO_NO NUMBER(10) NOT NULL,             
    THEME VARCHAR2(50) NOT NULL,
    SCHEDULE VARCHAR2(50) NOT NULL,
    COURSE_DAY VARCHAR2(100),
    COURSE_NAME VARCHAR2(1000),
    COURSE_X VARCHAR2(1000),
    COURSE_Y VARCHAR2(1000),  
    COURSE_DISTANCE VARCHAR2(100),
    CONSTRAINT CO_FK_BNO FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO)
);


loveAlakazam commented 3 years ago

2020.12.10~2020.12.11 - 여행지/후기/호텔 및 공통테이블


-- TABLE 삭제
DROP TABLE MEMBER CASCADE CONSTRAINTS;
DROP TABLE CATEGORY CASCADE CONSTRAINTS;
DROP TABLE HOTEL CASCADE CONSTRAINTS;
DROP TABLE HOTEL_RESERVATION CASCADE CONSTRAINTS;
DROP TABLE HOTEL_REVIEW CASCADE CONSTRAINTS;
DROP TABLE IMG_FILE CASCADE CONSTRAINTS;
DROP TABLE REPLY CASCADE CONSTRAINTS;
DROP TABLE LOCAL_INFO CASCADE CONSTRAINTS;
DROP TABLE ROOM CASCADE CONSTRAINTS;
DROP TABLE BOARD CASCADE CONSTRAINTS;
DROP TABLE LIKES CASCADE CONSTRAINTS;
DROP TABLE CERTIFICATION;
DROP TABLE COURSE CASCADE CONSTRAINTS;
DROP TABLE COURSE_DETAIL CASCADE CONSTRAINTS;
DROP TABLE TRAVEL CASCADE CONSTRAINTS;

--뷰삭제
DROP VIEW HOTEL_DETAIL_IMG_VIEW;
DROP VIEW HOTEL_MINPRICE_ORDER_VIEW;
DROP VIEW HOTEL_REVIEW_INFO_VIEW;
DROP VIEW HOTEL_ROOM_INFO_VIEW;
DROP VIEW HOTEL_THUMBNAIL_IMG_VIEW;
DROP VIEW HOTEL_VIEW;
DROP VIEW TLIST;
DROP VIEW TDETAIL;

--시퀀스 삭제
DROP SEQUENCE SEQ_BO_NO;
DROP SEQUENCE SEQ_FILE_NO;
DROP SEQUENCE SEQ_RE_NO;
DROP SEQUENCE SEQ_TRNO;
DROP SEQUENCE SEQ_ROOM_NO;
DROP SEQUENCE SEQ_RESERVE_NO;

COMMIT;

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

CREATE TABLE MEMBER(
   MEMBER_ID VARCHAR2(30) PRIMARY KEY,
   MEMBER_PWD VARCHAR2(100),
   MEMBER_NAMES VARCHAR2(30),
   NICKNAME VARCHAR2(30) NOT NULL,
   GENDER VARCHAR2(10),
   PHONE VARCHAR2(30),
   MEMBER_EMAIL VARCHAR2(50),
   M_CATEGORY VARCHAR2(10) NOT NULL,
   M_STATUS VARCHAR2(10) NOT NULL,
      CHECK(GENDER IN('남자','여자')),
      CHECK(M_STATUS IN('Y','N'))
);

ALTER TABLE MEMBER MODIFY (M_STATUS DEFAULT 'Y');
ALTER TABLE MEMBER ADD UNIQUE(PHONE);

INSERT INTO MEMBER VALUES('admin', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '관리자', '관리자', '여자', '01012345678','admin@naver.com','member' ,default);
INSERT INTO MEMBER VALUES('soo', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '김수진', '수진k', '여자', '01095266236','sjrlaa@gmail.com','member' ,default);
INSERT INTO MEMBER VALUES('ko1850', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '이규호', '규호', '남자', '01024593141','ko1804@naver.com','member' ,default);
INSERT INTO MEMBER VALUES('TRIP', '$2a$10$a3KAdt7hoVH02YhsqIWszuXUumRpihW3kHnEe1z3hRMbcrs7.bAjK', '최은강', 'loveAlakazam', '여자', '01071685268','dmsrkd1216@gmail.com','member' ,default);
INSERT INTO MEMBER VALUES('joy', '$2a$10$D3RWlUNOhYRLaCGFSYbJg.YXwHYGfHvhr6BhZ8UH9sgHWI0Vn4kLO', '김기쁨', 'joy', '여자', '01030987909','lcocvce@naver.com','member' ,default);

--인증번호 테이블
CREATE TABLE CERTIFICATION(
EMAIL VARCHAR2(50) NOT NULL,
RANDOMKEY VARCHAR2(50) NOT NULL,
PRIMARY KEY(EMAIL, RANDOMKEY)
);
--------------------------------------

--카테고리 테이블
CREATE TABLE CATEGORY(
    CA_CODE NUMBER(5) NOT NULL,
    CA_NAME VARCHAR2(30) NOT NULL,
    PRIMARY KEY (CA_CODE),
    CHECK(CA_CODE BETWEEN 1 AND 5) --카테고리번호는 1~5로만 제한한다.
);

--카테고리 데이터 삽입
INSERT INTO CATEGORY VALUES(1, '여행지');
INSERT INTO CATEGORY VALUES(2, '여행 코스');
INSERT INTO CATEGORY VALUES(3, '여행후기 사진');
INSERT INTO CATEGORY VALUES(4, '여행후기 목록');
INSERT INTO CATEGORY VALUES(5, '호텔');

--2020.12.13 - 이규호 - 카테고리수정
UPDATE CATEGORY
SET CA_NAME='여행지후기'
WHERE CA_CODE=3;

UPDATE CATEGORY
SET CA_NAME='맛집후기'
WHERE CA_CODE=4;

COMMIT;
--------------------------------------

--BOARD 테이블
--생성시 제약조건 추가
CREATE TABLE BOARD(
    BO_NO NUMBER(10) NOT NULL,
    CA_CODE NUMBER(5) NOT NULL,
    BO_TITLE VARCHAR2(300) NOT NULL,
    BO_CONTENT LONG NOT NULL,
    MEMBER_ID VARCHAR2(30) NOT NULL,
    BO_COUNT NUMBER(10) DEFAULT 0 NOT NULL,
    BO_TAG VARCHAR2(300),
    BO_DELETE_YN VARCHAR2(10) DEFAULT 'N' NOT NULL,
    REGDATE DATE DEFAULT SYSDATE,

    PRIMARY KEY (BO_NO),
    FOREIGN KEY (CA_CODE) REFERENCES CATEGORY(CA_CODE),
    CONSTRAINT  FK_BO_MID  FOREIGN KEY (MEMBER_ID) REFERENCES MEMBER(MEMBER_ID) ON DELETE CASCADE, --나중에 제약조건 추가
    CHECK (CA_CODE BETWEEN 1 AND 5), --테이블 카테고리번호는 1,2,3,4,5 로 제한한다.
    CHECK (BO_DELETE_YN IN ('Y', 'N'))
);

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

CREATE TABLE IMG_FILE(
    FILE_NO NUMBER(10) NOT NULL,
    BO_NO NUMBER(10) NOT NULL,
    ORIGIN_NAME VARCHAR2(300) NOT NULL,
    CHANGE_NAME VARCHAR2(300) NOT NULL,
    FILE_LEVEL NUMBER(5) NOT NULL,
    FILE_PATH VARCHAR2(1000) NOT NULL,
    FILE_DELETE_YN VARCHAR2(10) DEFAULT 'N' NOT NULL,

    -- 제약조건
    PRIMARY KEY(FILE_NO),
    CONSTRAINT FK_IMG_BNO FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO)ON DELETE CASCADE, --나중에 제약조건 추가
    CHECK(FILE_LEVEL IN (1,2) ), --파일레벨 1(썸네일) / 2(기본 이미지)
    CHECK(FILE_DELETE_YN IN ('Y', 'N'))
);

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

--LIKES 테이블
CREATE TABLE LIKES(
    BO_NO NUMBER(10) NOT NULL,
    LIKE_YN VARCHAR2(10) DEFAULT 'N' NOT NULL,
    MEMBER_ID VARCHAR2(30) NOT NULL,
    PRIMARY KEY(BO_NO, MEMBER_ID),
    CONSTRAINT FK_LK_MID FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID) ON DELETE CASCADE, --나중에 제약조건추가
    CHECK(LIKE_YN IN ('Y', 'N'))
);

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

--REPLY 테이블(은강/ 규호 공통)
CREATE TABLE REPLY(
    RE_NO NUMBER(10) NOT NULL,
    BO_NO NUMBER(10) NOT NULL,
    MEMBER_ID VARCHAR2(30) NOT NULL,
    RE_DATE DATE DEFAULT SYSDATE NOT NULL,
    RE_CONTENT VARCHAR2(1500) NOT NULL,
    RE_DELETE_YN VARCHAR2(10) DEFAULT 'N' NOT NULL,

    PRIMARY KEY(RE_NO), --파일번호
    CONSTRAINT RP_FK_BNO FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO) ON DELETE CASCADE, --게시판 번호+나중에제약조건추가
    CONSTRAINT RP_FK_MID FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID) ON DELETE CASCADE, --작성자 아이디+나중에제약조건추가
    CHECK(RE_DELETE_YN IN ('Y', 'N')) --파일 삭제여부 (Y/N)
);

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

-- 시퀀스

CREATE SEQUENCE SEQ_BO_NO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;

CREATE SEQUENCE SEQ_FILE_NO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;

CREATE SEQUENCE SEQ_RE_NO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;

CREATE SEQUENCE SEQ_TRNO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;

CREATE SEQUENCE SEQ_ROOM_NO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;

CREATE SEQUENCE SEQ_RESERVE_NO
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCYCLE
NOCACHE;

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

--LOCAL_INFO
CREATE TABLE LOCAL_INFO(
    LOCAL_CODE NUMBER(5) NOT NULL,      --지역번호
    LOCAL_NAME VARCHAR2(50) NOT NULL,    --지역이름
    PRIMARY KEY(LOCAL_CODE)
);

--지역정보 데이터 삽입
INSERT INTO LOCAL_INFO VALUES(1,'강원도');
INSERT INTO LOCAL_INFO VALUES(2,'경기도');
INSERT INTO LOCAL_INFO VALUES(3,'경상남도');
INSERT INTO LOCAL_INFO VALUES(4,'경상북도');
INSERT INTO LOCAL_INFO VALUES(5,'광주광역시');
INSERT INTO LOCAL_INFO VALUES(6, '대구광역시');
INSERT INTO LOCAL_INFO VALUES(7, '대전광역시');
INSERT INTO LOCAL_INFO VALUES(8, '부산광역시');
INSERT INTO LOCAL_INFO VALUES(9, '서울특별시');
INSERT INTO LOCAL_INFO VALUES(10, '세종특별자치시');
INSERT INTO LOCAL_INFO VALUES(11, '인천광역시');
INSERT INTO LOCAL_INFO VALUES(12, '울산광역시');
INSERT INTO LOCAL_INFO VALUES(13, '전라남도');
INSERT INTO LOCAL_INFO VALUES(14, '전라북도');
INSERT INTO LOCAL_INFO VALUES(15, '제주특별자치도');
INSERT INTO LOCAL_INFO VALUES(16, '충청남도');
INSERT INTO LOCAL_INFO VALUES(17, '충청북도');

COMMIT;

--HOTEL 테이블
CREATE TABLE HOTEL(
    BO_NO NUMBER(10) NOT NULL, --호텔번호
    HOTEL_ADDRESS VARCHAR2(1000) NOT NULL,--호텔주소

    HOTEL_LOCAL_CODE NUMBER(5) NOT NULL,--지역코드

    HOTEL_SITE VARCHAR2(1000),--호텔사이트
    HOTEL_TEL VARCHAR2(30) NULL,--전화번호
    HOTEL_REVIEW_SCORE NUMBER(5,1) DEFAULT 0 NOT NULL, --호텔 후기 평점
    HOTEL_RANK NUMBER(5),  --호텔등급(1~5, 없음)

    HOTEL_OPEN_TIME NUMBER(5) DEFAULT 0 NOT NULL,--호텔오픈시각
    HOTEL_CLOSE_TIME NUMBER(5) DEFAULT 23 NOT NULL,--호텔종료시각
    HOTEL_OPTION VARCHAR2(2000), --호텔옵션
    CHECK_IN NUMBER(5) DEFAULT 10 NOT NULL, --체크인
    CHECK_OUT NUMBER(5) DEFAULT 15 NOT NULL,--체크아웃

    -- 제약조건
    CONSTRAINT HT_PK PRIMARY KEY (BO_NO),       --기본키(BO_NO)
    CONSTRAINT HT_FK_BNO FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO),  --외래키(BO_NO) + 나중에 삭제제약추가
    FOREIGN KEY(HOTEL_LOCAL_CODE) REFERENCES LOCAL_INFO(LOCAL_CODE) --외래키(HOTEL_LOCAL_CODE)
);

--ROOM 테이블
--호텔방(ROOM)테이블
CREATE TABLE ROOM(
    ROOM_NO NUMBER(10),                     --방번호
    BO_NO NUMBER(10) NOT NULL,              --호텔번호
    ROOM_TYPE VARCHAR2(200) NOT NULL,       --방종류
    ROOM_NAME VARCHAR2(1000) NOT NULL,      --방이름
    PRICE_PER_DAY NUMBER(10) NOT NULL,          --1인당 이용가격
    CONSTRAINT RO_PK PRIMARY KEY(ROOM_NO),  --기본키(ROOM_NO)
    CONSTRAINT RO_FK FOREIGN KEY(BO_NO) REFERENCES HOTEL(BO_NO) ON DELETE CASCADE --외래키(BO_NO)+나중에 삭제제약추가
);

--호텔리뷰 댓글
CREATE TABLE HOTEL_REVIEW(
    RE_NO NUMBER(10),   --댓글번호
    REVIEW_SCORE NUMBER(5) NOT NULL,
    CONSTRAINT HR_PK_RVN PRIMARY KEY(RE_NO),                            --기본키(RE_NO)
    CONSTRAINT HR_FK_RVN FOREIGN KEY(RE_NO) REFERENCES REPLY(RE_NO) ON DELETE CASCADE,    --외래키(RE_NO)
    CONSTRAINT HR_CK_RVS CHECK(REVIEW_SCORE BETWEEN 0 AND 5)            --제약조건(REVIEW_SCORE 0~5점만 부여가능)
);

--호텔예약
CREATE TABLE HOTEL_RESERVATION(
    RESERVE_NO NUMBER(10),              --예약번호
    MEMBER_ID VARCHAR2(30) NOT NULL,    --예약자ID
    BO_NO NUMBER(10) NOT NULL,          --호텔번호
    ROOM_NO NUMBER(10) NOT NULL,        --방번호
    RESERVE_PRICE NUMBER(10) NOT NULL,  --예약가격(총가격)

    RESERVE_ROOM_CNT NUMBER(10) DEFAULT 1 NOT NULL,   --예약 객실 수(기본값:1)
    RESERVE_TOTAL_PERSON_CNT NUMBER(10) DEFAULT 2 NOT NULL,         --예약인원수(전체인원수: 기본값 2)
    RESERVE_PERSON_ADULT_CNT NUMBER(10) DEFAULT 2 NOT NULL,--성인수(기본값: 2)
    RESERVE_PERSON_CHILD_CNT NUMBER(10) DEFAULT 0 NOT NULL,--어린이수(기본값: 0)

    CHECK_IN_DATE DATE DEFAULT SYSDATE NOT NULL,    --체크인 날짜
    CHECK_OUT_DATE DATE DEFAULT SYSDATE NOT NULL,   --체크아웃 날짜
    RESERVE_CHECK VARCHAR2(5) DEFAULT 'N' NOT NULL, --예약확인
    REFUND_CHECK VARCHAR2(5)  DEFAULT 'N' NOT NULL, --환불확인

    CONSTRAINT RS_PK PRIMARY KEY(RESERVE_NO),                                   --기본키(RESERVE_NO)
    CONSTRAINT RS_FK_MID FOREIGN KEY(MEMBER_ID) REFERENCES MEMBER(MEMBER_ID)ON DELETE CASCADE,   --외래키(MEMBER_ID)+나중에 제약조건추가
    CONSTRAINT RS_FK_BN FOREIGN KEY(BO_NO) REFERENCES HOTEL(BO_NO) ON DELETE CASCADE,             --외래키(BO_NO)+나중에 제약조건추가
    CONSTRAINT RS_FK_RN FOREIGN KEY(ROOM_NO) REFERENCES ROOM(ROOM_NO) ON DELETE CASCADE,          --외래키(ROOM_NO)
    CONSTRAINT RS_CK_RSC CHECK(RESERVE_CHECK IN ('Y', 'N')),                    --제약조건(RESERVE_CHECK)
    CONSTRAINT RS_CK_RFC CHECK(REFUND_CHECK IN('Y','N'))                        --제약조건(REFUND_CHECK)
);

Commit;

--뷰
CREATE OR REPLACE VIEW HOTEL_VIEW
AS
SELECT *
FROM BOARD JOIN HOTEL USING(BO_NO)
WHERE BO_DELETE_YN='N' AND CA_CODE=5;

CREATE OR REPLACE VIEW HOTEL_REVIEW_INFO_VIEW
AS
SELECT *
FROM REPLY JOIN HOTEL_REVIEW USING(RE_NO);

CREATE OR REPLACE VIEW HOTEL_ROOM_INFO_VIEW
AS
SELECT *
FROM BOARD
    JOIN HOTEL USING(BO_NO)
    JOIN ROOM USING(BO_NO)
WHERE BO_DELETE_YN='N';

CREATE OR REPLACE VIEW HOTEL_THUMBNAIL_IMG_VIEW
AS
SELECT *
FROM BOARD
    JOIN IMG_FILE USING(BO_NO)
WHERE BO_DELETE_YN='N' AND FILE_DELETE_YN='N' AND FILE_LEVEL=1 AND CA_CODE=5;

CREATE OR REPLACE VIEW HOTEL_DETAIL_IMG_VIEW
AS
SELECT *
FROM BOARD
    JOIN IMG_FILE USING(BO_NO)
WHERE BO_DELETE_YN='N' AND FILE_DELETE_YN='N' AND FILE_LEVEL=2 AND CA_CODE=5;

CREATE OR REPLACE VIEW HOTEL_MINPRICE_ORDER_VIEW
AS
SELECT BO_NO, MIN(PRICE_PER_DAY) AS MIN_PRICE
FROM HOTEL_ROOM_INFO_VIEW
GROUP BY BO_NO;

COMMIT;

--------------------------------------
--여행지

CREATE TABLE "TRIP2REAP"."TRAVEL" 
   (    "TR_NO" NUMBER(10,0), 
    "BO_NO" NUMBER(10,0) NOT NULL ENABLE, 
    "TR_ADDR" VARCHAR2(300 BYTE) NOT NULL ENABLE, 
    "TR_THEME" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
    "TR_REG" VARCHAR2(50 BYTE) NOT NULL ENABLE, 
    "TR_TITLE" VARCHAR2(100 BYTE), 
    "TR_PHONE" VARCHAR2(20 BYTE), 
     PRIMARY KEY ("TR_NO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"  ENABLE, 
     FOREIGN KEY ("BO_NO")
      REFERENCES "TRIP2REAP"."BOARD" ("BO_NO") ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM" ;

--여행지뷰
CREATE OR REPLACE FORCE VIEW "TRIP2REAP"."TLIST" ("BO_NO", "CA_CODE", "BO_TITLE", "BO_CONTENT", "MEMBER_ID", "BO_COUNT", "BO_TAG", "BO_DELETE_YN", "TR_ADDR", "TR_THEME", "CHANGE_NAME")
AS
SELECT BOARD.BO_NO, BOARD.CA_CODE, BO_TITLE, BO_CONTENT, BOARD.MEMBER_ID, BO_COUNT, BO_TAG, BO_DELETE_YN, TR_ADDR, TR_THEME, CHANGE_NAME
FROM BOARD
    JOIN TRAVEL T ON (T.BO_NO = BOARD.BO_NO)
    JOIN MEMBER M ON (M.MEMBER_ID = BOARD.MEMBER_ID)
    JOIN CATEGORY C ON (C.CA_CODE = BOARD.CA_CODE)
    JOIN IMG_FILE I ON (BOARD.BO_NO = I.BO_NO)
WHERE BO_DELETE_YN = 'N'
ORDER BY REGDATE DESC;

CREATE OR REPLACE FORCE VIEW "TRIP2REAP"."TDETAIL" ("BO_NO", "CA_CODE", "BO_TITLE", "BO_CONTENT", "MEMBER_ID", "BO_COUNT", "BO_TAG", "BO_DELETE_YN", "REGDATE", "TR_ADDR", "TR_THEME", "CHANGE_NAME")
AS
SELECT BOARD.BO_NO, BOARD.CA_CODE, BO_TITLE, BO_CONTENT, BOARD.MEMBER_ID, BO_COUNT, BO_TAG, BO_DELETE_YN, REGDATE, TR_ADDR, TR_THEME, CHANGE_NAME
FROM BOARD
  JOIN TRAVEL T ON (T.BO_NO = BOARD.BO_NO)
  JOIN MEMBER M ON (M.MEMBER_ID = BOARD.MEMBER_ID)
  JOIN CATEGORY C ON (C.CA_CODE = BOARD.CA_CODE)
  JOIN IMG_FILE I ON (BOARD.BO_NO = I.BO_NO)
WHERE BO_DELETE_YN = 'N'
ORDER BY REGDATE DESC;

COmmit;
joyeeek commented 3 years ago

마이페이지 (김기쁨)

내가담은 여행지 - 테이블

1. MYTRAVEL

CREATE TABLE MYTRAVEL(
    BO_NO NUMBER(10) PRIMARY KEY,
    MT_STATUS VARCHAR2(10) NOT NULL,
    CONSTRAINT MT_FK_BNO FOREIGN KEY(BO_NO) REFERENCES BOARD(BO_NO)
);