create table board(
id number primary key,
title varchar2(100),
contents varchar2(4000),
writer varchar2(100),
time varchar2(100)
);
desc sell;
select * from members;
select * from board;
desc board;
select * from user;
drop table board purge;
show user;
ALTER table members drop column SEQ;
ALTER table sell modify sell_id varchar2(20) references members(id) ON DELETE CASCADE;
commit;
SELECT * FROM sell;
DELETE members;
commit;
INSERT into members values('admin','admin','admin','na875k','@nate.com','ok',010,9501,6272,'남자',1987,5,9,'서울시',sysdate)
INSERT into members values('admin2','admin2','admin','na875k','@nate.com','ok',010,9501,6272,'남자',1987,5,9,'서울시',sysdate)
INSERT into members values('admin3','admin3','admin','na875k','@nate.com','ok',010,9501,6272,'남자',1987,5,9,'서울시',sysdate)
INSERT into members values('admin4','admin4','admin','na875k','@nate.com','ok',010,9501,6272,'남자',1987,5,9,'서울시',sysdate)
INSERT into members values('admin5','admin5','admin','na875k','@nate.com','ok',010,9501,6272,'남자',1987,5,9,'서울시',sysdate)
INSERT into members values('admin6','admin6','admin','na875k','@nate.com','ok',010,9501,6272,'남자',1987,5,9,'서울시',sysdate)
commit;
desc board;
desc members;
desc sell;
SELECT * FROM sell;
drop table sell purge;
create table noticeboard(
idx number(8) primary key,
writer varchar2(30) not null,
email varchar2(100),
pwd varchar2(20) not null,
subject varchar2(200), --제목
content varchar2(2000),--글 내용
writedate timestamp default systimestamp,--작성일
readnum number(8) default 0,--조회수
-- 답변형 게시판을 위해서 필요한 컬럼
refer number default 0, --답변형 게시판[글 그룹번호]
lev number default 0,--답변형 게시판[답변 레벨]
sortlist number default 0 --답변형 게시판[순서 정렬시 사용]
);
commit;
DROP sequence board_idx;
create sequence not_board_idx
start with 1
increment by 1
nocache;
DROP sequence not_board_idx;
create sequence sell_idx
start with 1
increment by 1
nocache;
drop table sell purge;
SELECT * FROM sell;
commit;
SELECT * FROM board;
SELECT * FROM noticeboard;
INSERT INTO sell(board_num, sell_id, category,
title, writer, company, isbn, regular, sale, stock, state, image, textarea,
bank, depositor, account, delivery, cost, changeterms)
values(sell_idx.nextval, '1', '1', '1', '1', '1', '1', '1', '1','1','1','1','1', '1', '1', '1', '1', '1', '1');
SELECT * FROM sell;
create table not_reply(
no number primary key,
writer varchar2(30),
userid varchar2(30),
pwd varchar2(20),
content varchar2(500),
writedate date default sysdate,
idx_fk references board(idx) ON DELETE CASCADE);
CREATE sequence not_reply_no nocache;
desc reply
ALTER table sell modify idx_fk;
SELECT board_idx.nextval from dual;
select * from members;
drop table reply purge;
DESC board;
desc reply;
create table user(
id varchar2(20) primary key,
password varchar2(20),
name varchar2(20)
);
SELECT *
FROM
(SELECT rownum r_id, temp.* FROM
(SELECT * FROM board order by time desc) temp)
WHERE r_id >=6 and r_id<=10;
select * from members;
UPDATE board SET writer='ab', time=sysdate, contents='xxx' WHERE id='11';
SELECT *
FROM
(SELECT ROWNUM rn, temp.* FROM
(SELECT * FROM board WHERE content like '%1234%' ORDER BY refer DESC, sortlist ASC) temp)
where rn >= 1 and rn<= 5;
desc sell;
create table sell(
board_num number,
sell_id varchar2(20) not null,
category varchar2(50) not null,
title varchar2(500) not null,
writer varchar2(50) not null,
company varchar2(100) not null,
isbn varchar2(50) not null,
regular varchar2(50) not null,
stock varchar2(50) not null,
sale varchar2(50) not null,
state varchar2(50) not null,
image varchar2(100),
textarea varchar2(2000) not null,
bank varchar2(50) not null,
depositor varchar2(50) not null,
account varchar2(50) not null,
delivery varchar2(50) not null,
cost varchar2(50) not null,
changeterms varchar2(2000) not null,
reg_date date default sysdate,
primary key (board_num)
);
desc sell;
drop table sell purge;
drop sequence sell_idx;