sql_tables.txt

sql 테이블

sys
oracle as sysdba

alter session set "_oracle_script"=true;

create user billy identified by dream account unlock;

grant connect, resource to billy;

grant create table, create sequence to billy;

alter user billy default tablespace users quota unlimited on users;

--대분류 카테고리
drop table lcategory cascade constraints;
create table lcategory(
no number primary key, 
name varchar2(40),
order_num number
);

drop sequence lcate_seq;
create sequence lcate_seq
  increment by 1
  start with 1
  minvalue 1
  maxvalue 10000
  nocache;

insert into lcategory values(lcate_seq.nextval,'모바일',1);
insert into lcategory values(lcate_seq.nextval,'PC/노트북',2);
insert into lcategory values(lcate_seq.nextval,'카메라/DSLR',3);

--소분류 카테고리
drop table scategory cascade constraints;
create table scategory(
no number primary key, 
name varchar2(40),
lcate_no number,
order_num number,
search_count number 
);
drop sequence scate_seq;
create sequence scate_seq
  increment by 1
  start with 1
  minvalue 1
  maxvalue 10000
  nocache;

insert into scategory(no,name,lcate_no,order_num,search_count) values(scate_seq.nextval,'스마트폰',1,1,0);
insert into scategory(no,name,lcate_no,order_num,search_count) values(scate_seq.nextval,'스마트워치',1,2,0);
insert into scategory(no,name,lcate_no,order_num,search_count) values(scate_seq.nextval,'모니터',2,1,0);
insert into scategory(no,name,lcate_no,order_num,search_count) values(scate_seq.nextval,'노트북',2,2,0);
insert into scategory(no,name,lcate_no,order_num,search_count) values(scate_seq.nextval,'필름카메라',3,1,0);


-- 회원 테이블
drop table member cascade constraints;
create table member(
mno number primary key,
id varchar2(20),
pw varchar2(100),
name varchar2(20),
add1_sido varchar2(30),
add2_sigungu varchar2(30),
add3_eubmyeon varchar2(30),
add4_donglee varchar2(30),
email varchar2(30),
hp varchar2(11),
isblacklist char(1) default 0,
rating number default 0,
coupon varchar2(200)
);

col mno for 99;
col id for a10;
col pw for a5;
col name for a15;
col add1_sido for a10;
col add2_sigungu for a10;
col add3_eubmyeon for a10;
col add4_donglee for a10;
col email for a10;
col hp for 9999999999;
col isblacklist for 9;
col rating for 9;

drop sequence member_seq;
create sequence member_seq
  increment by 1
  start with 1
  minvalue 1
  maxvalue 10000
  nocache;

-- 아이피 테이블
drop table memberip cascade constraints;
create table memberip(
num number,
mno number references member(mno) on delete cascade,
ip varchar2(20)
);

drop sequence memberip_seq;
create sequence memberip_seq
  increment by 1
  start with 1
  minvalue 1
  maxvalue 10000
  nocache;
  
-- 상품 테이블
drop table products cascade constraints;
create table products(
	no number primary key,
	seller_no number not null references member(mno) on delete cascade,
	images varchar2(1400),
	name varchar2(130) not null,
	lcategory_no number not null,
	scategory_no number not null,
	add1_sido varchar2(30) not null,
	add2_sigungu varchar2(30) not null,
	add3_eubmyeon varchar2(30),
	add4_donglee varchar2(30)  not null,
	original_day_price number not null,
	discounted_day_price number not null,
	rentday_minimum number not null,
	components varchar2(700),
	description varchar2(900),
	end_day date not null,
	view_count number default 0,
	create_day date default sysdate,
	pulled_day date default sysdate,
	is_hidden char(1) default '0'
);

drop sequence products_seq;
create sequence products_seq
	increment by 1
	start with 1 
	minvalue 1
	maxvalue 10000000
	nocache;

-- paging test
-- insert into products(no, seller_no, images, name, lcategory_no, scategory_no, add1_sido, add2_sigungu, add3_eubmyeon, add4_donglee, original_day_price, discounted_day_price, rentday_minimum, end_day) values(products_seq.nextval, 5, 'xx.jsp', '오큘러', 2, 1, 'as', 'as', 'as', 'as', 100, 100, 5, '2022-10-10');
commit;



-- 찜목록 테이블
drop table wishlist cascade constraints;
create table wishlist(
	no number primary key,
	product_no number not null references products(no) on delete cascade,
	member_no number not null references member(mno) on delete cascade
);

drop sequence wishlist_seq;
create sequence wishlist_seq
	increment by 1
	start with 1 
	minvalue 1
	maxvalue 10000000
	nocache;


-- reservation테이블
drop table reservation cascade constraints;
create table reservation(
	no number primary key not null,
	product_no number references products(no) on delete cascade,
	buyer_no number references member(mno) on delete cascade,
	start_date date not null,
	end_date date not null,
	is_accepted char(1),
	accepted_date date,
	status varchar2(20),
	amount number,
	notice varchar2(210)
	);


drop sequence reservation_seq;
create sequence reservation_seq
	increment by 1
	start with 1 
	minvalue 1
	maxvalue 10000000
	nocache;

--쿠폰 테이블
drop table coupon cascade constraints;
create table coupon(
   no number primary key,
   code varchar2(20) not null,
   name varchar2(40),
   amount number,
   unit varchar2(9),
   startdate date default sysdate,
   enddate date
);

drop sequence coupon_seq;
create sequence coupon_seq
  increment by 1
  start with 1
  minvalue 1
  maxvalue 10000
  nocache;

drop table couponCount cascade constraints;
create table couponCount(
   cno number,
   reg_date date
);

-- 문의하기 테이블
drop table contect cascade constraints;
create table contect( 
   no number primary key not null,
   mno number references member(mno) on delete cascade,
   id varchar2(20),
   reg_date date default sysdate,
   category_num number,
   title varchar2(50),
   content varchar2(210),
   is_reply number default 0, --원글인지 답글인지 구분
   is_replied number default 0, --답글남겼는지 체크
   readcount number default 0	
   );

drop sequence contect_seq;
create sequence contect_seq
	increment by 2
	start with 2 
	minvalue 1
	maxvalue 10000000
	nocache;

drop table room cascade constraints;
create table room(
	no number default 0,
	room_no number not null primary key,
	mno1 number references member(mno) on delete cascade,
	mno2 number references member(mno) on delete cascade,
	pno number references products(no) on delete cascade
	);

drop sequence room_seq;
create sequence room_seq
	increment by 1
	start with 1 
	minvalue 1
	maxvalue 10000000
	nocache;
	
	
drop table chat;
create table chat(
	no number not null,
	room_no number references room(room_no) on delete cascade,
	send_mno number references member(mno) on delete cascade,
	read_mno number references member(mno) on delete cascade,
	time date not null,
	content varchar2(1000) not null,
	pno number references  products(no) on delete cascade
	);

drop sequence chat_seq;
create sequence chat_seq
	increment by 1
	start with 1 
	minvalue 1
	maxvalue 10000000
	nocache;
	


drop table custumer_review cascade constraints;
create table custumer_review(
	no number not null,
	pno number references products(no) on delete cascade,
	mno number references member(mno) on delete cascade,
	title varchar2(1000) not null,
	rating number not null,
	content varchar2(1000) not null
	);

drop sequence custumer_review_seq;
create sequence custumer_review_seq
	increment by 1
	start with 1 
	minvalue 1
	maxvalue 10000000
	nocache;

drop table event cascade constraints;
create table event(
   num number primary key,
   title varchar2(200) , 
   writer varchar2(200) ,
    eventdate date default sysdate ,
    eventImage varchar2(200) ,
    write varchar2(900) ,
   readcount number default 0
);

drop sequence event_seq;
create sequence event_seq 
minvalue 1
start with 1
nocache;

insert into event(num,title,writer,eventdate,eventImage,write,readcount)
values(event_seq.nextval,'조던신발','관리자','2022-01-01','test.jpg','수정중입니다','2');


commit;
	
--