쇼핑몰 SQL
--------------------------------------------------------
-- TABLE PRODUCT_CATEGORY
--------------------------------------------------------
CREATE TABLE PRODUCT_CATEGORY (
PC_IDX NUMBER PRIMARY KEY,
PC_NAME VARCHAR2(20)
);
--------------------------------------------------------
-- TABLE PRODUCT
--------------------------------------------------------
CREATE TABLE PRODUCT (
P_IDX NUMBER PRIMARY KEY,
PC_IDX NUMBER,
P_NAME VARCHAR2(100),
P_PRICE NUMBER,
P_STOCK NUMBER,
P_DESC VARCHAR2(3000),
P_REGDATE DATE,
P_VIEWS NUMBER,
S_IDX NUMBER,
P_ABLE CHAR(1),
P_TOTALCNT NUMBER,
P_FEE NUMBER,
P_IMG VARCHAR2(1000)
);
CREATE SEQUENCE P_IDX_SEQ START WITH 1 INCREMENT BY 1;
--------------------------------------------------------
-- TABLE PRODUCT_SELLER
--------------------------------------------------------
CREATE TABLE SELLER (
S_IDX NUMBER PRIMARY KEY,
S_ID VARCHAR2(20) NOT NULL,
S_PWD VARCHAR2(100) NOT NULL,
S_PHONE VARCHAR2(20) NOT NULL,
S_NAME VARCHAR2(20) NOT NULL,
S_ADDRESS VARCHAR2(1000) NOT NULL,
S_EMAIL VARCHAR2(50) NOT NULL,
S_BIZ VARCHAR(20)
S_REGDATE DATE DEFULAT AS SYSDATE
);
CREATE SEQUENCE S_IDX_SEQ START WITH 1 INCREMENT BY 1;
--------------------------------------------------------
-- TABLE ORDERS
--------------------------------------------------------
CREATE TABLE ORDERS
(
O_IDX NUMBER NOT NULL,
P_IDX NUMBER NOT NULL,
O_CNT NUMBER NOT NULL,
P_PRICE NUMBER NOT NULL,
O_STATUS NUMBER NOT NULL,
F_CHECK VARCHAR2(20) NOT NULL,
O_TOTPRICE NUMBER NOT NULL,
O_FEE NUMBER NOT NULL,
O_NUMBER NUMBER NOT NULL,
U_IDX NUMBER NOT NULL,
PA_IDX NUMBER NOT NULL,
O_DATE DATE NOT NULL,
S_IDX NUMBER NOT NULL,
CONSTRAINT PK_ORDERS PRIMARY KEY (O_IDX)
)
CREATE SEQUENCE O_NUMBER_SEQ
START WITH 1000
INCREMENT BY 1;
CREATE SEQUENCE O_IDX_SEQ
START WITH 1
INCREMENT BY 1;
--------------------------------------------------------
-- TABLE PRODUCT_IMG
--------------------------------------------------------
CREATE TABLE PRODUCT_IMG (
PI_IDX NUMBER PRIMARY KEY,
P_IDX NUMBER,
PI_ORIGIN VARCHAR2(1000),
PI_THUMB VARCHAR2(1000),
PI_SIZE NUMBER,
PI_REGDATE DATE,
PI_DELETE CHAR(1),
PI_SERVER1 VARCHAR2(1000),
PI_SERVER2 VARCHAR2(1000),
PI_SERVER3 VARCHAR2(1000),
PI_SERVER4 VARCHAR2(1000),
PI_SERVER5 VARCHAR2(1000)
);
--------------------------------------------------------
-- TABLE PRODUCT_REVIEW
--------------------------------------------------------
CREATE TABLE REVIEW (
R_IDX NUMBER PRIMARY KEY,
P_IDX NUMBER,
R_WRITE VARCHAR2(3000),
R_IMG VARCHAR2(1000),
R_REGDATE DATE,
R_STATUS CHAR(1),
U_IDX NUMBER,
R_RATING NUMBER
);
CREATE SEQUENCE R_IDX_SEQ START WITH 1 INCREMENT BY 1;
--------------------------------------------------------
-- TABLE PRODUCT_CART
--------------------------------------------------------
CREATE TABLE CART (
U_IDX NUMBER,
P_IDX NUMBER,
P_COUNT NUMBER,
CA_REGDATE DATE -- 장바구니에 물건 추가(등록)한 날짜(일정시간 나면 삭제되게)
);
--------------------------------------------------------
-- TABLE REFUND
--------------------------------------------------------
CREATE TABLE REFUND (
F_IDX NUMBER PRIMARY KEY,
O_NUMBER NUMBER,
P_IDX NUMBER,
F_REASON VARCHAR2(3000),
F_CATEGORY VARCHAR2(1000),
F_FEE NUMBER
);
--------------------------------------------------------
-- TABLE USER_ORDER
--------------------------------------------------------
CREATE TABLE USER_ORDER (
G_IDX NUMBER PRIMARY KEY,
O_ADDRESS1 VARCHAR2(2000),
O_ADDRESS2 VARCHAR2(2000),
O_PHONE VARCHAR2(100),
O_RECEIVER VARCHAR2(20),
O_ZIPCODE NUMBER,
U_IDX NUMBER
);
CREATE SEQUENCE G_IDX_SEQ
START WITH 1
INCREMENT BY 1;
--------------------------------------------------------
-- TABLE POINT
--------------------------------------------------------
CREATE TABLE POINT (
U_IDX NUMBER, -- 회원 IDX
PO_USED NUMBER, -- 사용한 포인트
PO_EARN NUMBER, -- 적립(획득)포인트
PO_DATE DATE DEFAULT SYSDATE ,-- 적립/획득 날짜
PO_WHERE VARCHAR2(1) -- 사용처
);
CREATE TABLE ORDERSTATUS(
OS_IDX NUMBER NOT NULL,
OS_NAME VARCHAR2(300) NOT NULL,
PRIMARY KEY (OS_IDX)
);
쇼핑몰 샘플 데이터(임시)
POINT
INSERT INTO "KNOCK2"."POINT" (U_IDX, PO_EARN, PO_DATE) VALUES ('1', '1000', TO_DATE('2021-07-01 16:47:09', 'YYYY-MM-DD HH24:MI:SS'))
INSERT INTO "KNOCK2"."POINT" (U_IDX, PO_USED, PO_DATE, PO_WHERE) VALUES ('1', '200', TO_DATE('2021-07-02 16:47:30', 'YYYY-MM-DD HH24:MI:SS'), '0')
PRODUCT
INSERT INTO PRODUCT(P_IDX, PC_IDX, P_NAME, P_PRICE, P_STOCK, P_DESC, P_REGDATE, P_VIEWS, S_IDX, P_ABLE, P_TOTALCNT, P_FEE, P_IMG)
VALUES (P_IDX_SEQ.nextval, 3,'에코수세미', 5000,10, '천연 소재로 만든 천연수세미! NO 색소! NO 미세플라스틱! 거품도 빵빵하게, 그릇은 깨끗하게~',SYSDATE,10,1,'1',12,3000,'tntpal.jpg');
INSERT INTO PRODUCT(P_IDX, PC_IDX, P_NAME, P_PRICE, P_STOCK, P_DESC, P_REGDATE, P_VIEWS, S_IDX, P_ABLE, P_TOTALCNT, P_FEE, P_IMG)
VALUES (P_IDX_SEQ.nextval, 3,'대나무칫솔(4P)', 8000,30, '내 치아를 깨끗하게, 환경도 깨끗하게! 자연의 소재로 만든 대나무 칫솔',SYSDATE,11,2,'1',5,3000,'dental-care.jpg');
INSERT INTO PRODUCT(P_IDX, PC_IDX, P_NAME, P_PRICE, P_STOCK, P_DESC, P_REGDATE, P_VIEWS, S_IDX, P_ABLE, P_TOTALCNT, P_FEE, P_IMG)
VALUES (P_IDX_SEQ.nextval, 3,'쫀득비누', 4000,20, '쫀득쫀득 거품이 참 잘나는 천연 친환경 비누. 주방에서도 사용할 수 있습니다!',SYSDATE,25,3,'1',10,3000,'soap.jpg');
INSERT INTO PRODUCT(P_IDX, PC_IDX, P_NAME, P_PRICE, P_STOCK, P_DESC, P_REGDATE, P_VIEWS, S_IDX, P_ABLE, P_TOTALCNT, P_FEE, P_IMG)
VALUES (P_IDX_SEQ.nextval, 3,'면행주(3P)', 5000,10, '국내산 순 면으로 만든 무표백 면 행주입니다. 한땀한땀 손으로 봉제한 도톰한 면행주에요. ',SYSDATE,33,4,'1',10,3000,'ernest.jpg');
INSERT INTO PRODUCT(P_IDX, PC_IDX, P_NAME, P_PRICE, P_STOCK, P_DESC, P_REGDATE, P_VIEWS, S_IDX, P_ABLE, P_TOTALCNT, P_FEE, P_IMG)
VALUES (P_IDX_SEQ.nextval, 2,'녹녹에코백', 10000,10, '이제 비닐봉지 사용은 줄이고 장보러 갈 때는 녹녹에코백을 사용해보시는건 어떨까요?',SYSDATE,19,5,'1',10,3000,'ecoback.jpg');
INSERT INTO PRODUCT(P_IDX, PC_IDX, P_NAME, P_PRICE, P_STOCK, P_DESC, P_REGDATE, P_VIEWS, S_IDX, P_ABLE, P_TOTALCNT, P_FEE, P_IMG)
VALUES (P_IDX_SEQ.nextval, 3,'유리병(2P)', 5000,16, '플라스틱은 이제 그만! 건강에 무해한 KNOCK유리병 사용해보세요!',SYSDATE,5,6,'1',10,3000,'markus.jpg');
INSERT INTO PRODUCT(P_IDX, PC_IDX, P_NAME, P_PRICE, P_STOCK, P_DESC, P_REGDATE, P_VIEWS, S_IDX, P_ABLE, P_TOTALCNT, P_FEE, P_IMG)
VALUES (P_IDX_SEQ.nextval, 2,'슈슈즈', 12000,19, '우리 아이에게도 건강한 신발을 신겨봐요! 신발이 건강해야 아이도 쑥쑥 자라겠죠?',SYSDATE,22,1,'1',10,3000,'booties.jpg');
INSERT INTO PRODUCT(P_IDX, PC_IDX, P_NAME, P_PRICE, P_STOCK, P_DESC, P_REGDATE, P_VIEWS, S_IDX, P_ABLE, P_TOTALCNT, P_FEE, P_IMG)
VALUES (P_IDX_SEQ.nextval, 1,'니니브레드', 4500,22, 'NO 설탕, NO 우유, NO 밀가루! 하지만 너무나 고소한 니니브레드! 한 번 맛보세요!',SYSDATE,12,3,'1',10,3000,'bread.jpg');
INSERT INTO PRODUCT(P_IDX, PC_IDX, P_NAME, P_PRICE, P_STOCK, P_DESC, P_REGDATE, P_VIEWS, S_IDX, P_ABLE, P_TOTALCNT, P_FEE, P_IMG)
VALUES (P_IDX_SEQ.nextval, 1,'비트몽', 5500,10, '설탕이요? 우유요? 밀가루요? 단 하나도 들어가지 않았습니다! 맛이 없을 것 같다구요? 일단 드셔보세요!',SYSDATE,12,3,'1',10,3000,'eclair.jpg');
INSERT INTO PRODUCT(P_IDX, PC_IDX, P_NAME, P_PRICE, P_STOCK, P_DESC, P_REGDATE, P_VIEWS, S_IDX, P_ABLE, P_TOTALCNT, P_FEE, P_IMG)
VALUES (P_IDX_SEQ.nextval, 2,'빈티지트리오', 20000,8, '쌓여만 가는 플라스틱으로 만든 제로웨이스트 셔츠!구두! 빈티지 트리오!',SYSDATE,35,1,'1',10,3000,'shirt.jpg');
INSERT INTO PRODUCT(P_IDX, PC_IDX, P_NAME, P_PRICE, P_STOCK, P_DESC, P_REGDATE, P_VIEWS, S_IDX, P_ABLE, P_TOTALCNT, P_FEE, P_IMG)
VALUES (P_IDX_SEQ.nextval, 2,'면 스웨터', 18000,14, '따뜻한 겨울을 나기 위해서는 역시 요만한게 없죠!',SYSDATE,5,4,'1',10,3000,'fashion.jpg');
INSERT INTO PRODUCT(P_IDX, PC_IDX, P_NAME, P_PRICE, P_STOCK, P_DESC, P_REGDATE, P_VIEWS, S_IDX, P_ABLE, P_TOTALCNT, P_FEE, P_IMG)
VALUES (P_IDX_SEQ.nextval, 2,'진자켓', 25000,14, '제로웨이스트라고 멋을 부릴 수 없다구요!? NO!',SYSDATE,5,1,'1',10,3000,'jeanjacket.jpg');
INSERT INTO PRODUCT(P_IDX, PC_IDX, P_NAME, P_PRICE, P_STOCK, P_DESC, P_REGDATE, P_VIEWS, S_IDX, P_ABLE, P_TOTALCNT, P_FEE, P_IMG)
VALUES (P_IDX_SEQ.nextval, 1,'베리산딸기', 10000,34, '저희 엄마하고 함께 산에 올라가 직접 채취한 산딸기! 우리동네 뒷동산 산딸기가 맛집이에요',SYSDATE,3,1,'1',10,3000,'raspberries.jpg');
INSERT INTO PRODUCT(P_IDX, PC_IDX, P_NAME, P_PRICE, P_STOCK, P_DESC, P_REGDATE, P_VIEWS, S_IDX, P_ABLE, P_TOTALCNT, P_FEE, P_IMG)
VALUES (P_IDX_SEQ.nextval, 1,'바니바니딸기딸기', 15000,24, '바니네 딸기는 싱싱하고 아주 달아요',SYSDATE,32,4,'1',10,3000,'strawberries.jpg');
PRODUCT CATEGORY
ORDERSSTATUS
INSERT INTO ORDERSTATUS
VALUES(0, '결제완료');
INSERT INTO ORDERSTATUS
VALUES(1, '배송준비');
INSERT INTO ORDERSTATUS
VALUES(2, '배송중');
INSERT INTO ORDERSTATUS
VALUES(3, '배송완료');
REVIEW
INSERT INTO REVIEW (R_IDX, P_IDX, R_WRITE, R_IMG, R_REGDATE, R_STATUS, U_IDX, R_RATING)
VALUES (R_IDX_SEQ.NEXTVAL, 1, '너무 마음에 들었어요~!', '사진1', SYSDATE, 1, 1, 5)
INSERT INTO REVIEW (R_IDX, P_IDX, R_WRITE, R_IMG, R_REGDATE, R_STATUS, U_IDX, R_RATING)
VALUES (R_IDX_SEQ.NEXTVAL, 2, '생각했던 그대로네요. 괜찮네요.', '사진1', SYSDATE, 1, 1, 4)
INSERT INTO REVIEW (R_IDX, P_IDX, R_WRITE, R_IMG, R_REGDATE, R_STATUS, U_IDX, R_RATING)
VALUES (R_IDX_SEQ.NEXTVAL, 2, '그냥 생각했던 그대로였어요. 또 구매하진 않을듯.', '사진1', SYSDATE, 1, 1, 3)
INSERT INTO REVIEW (R_IDX, P_IDX, R_WRITE, R_IMG, R_REGDATE, R_STATUS, U_IDX, R_RATING)
VALUES (R_IDX_SEQ.NEXTVAL, 2, '이걸 구매한 제 자신에게 원망해야할듯.', '사진1', SYSDATE, 1, 1, 2)
INSERT INTO REVIEW (R_IDX, P_IDX, R_WRITE, R_IMG, R_REGDATE, R_STATUS, U_IDX, R_RATING)
VALUES (R_IDX_SEQ.NEXTVAL, 2, '최악입니다. 몇번쓰다 버릴려고 했는데 지금 버릴듯.', '사진1', SYSDATE, 1, 1, 1)