[Oracle/SQL] 게시판 CRUD(Create, Read, Update, Delete)

Day31~32. 230613~14

게시판 CRUD(Create, Read, Update, Delete)

게시판 table 생성

create table 테이블명(
 컬럼명 데이터타입(크기)[default 기본값][constraint 제약조건명] 제약조건
)[tablespace 테이블스페이스];

create table board(
    b_no      number(4) constraint board_no_pk primary key, --글번호
    b_title   varchar2(100) constraint board_title_uk UNIQUE, --제목
    b_content varchar2(300) constraint board_content_nn NOT NULL, --글내용
    b_writer  varchar2(20), --작성자
    b_writeDate date default sysdate, --작성일
    b_hits    number(5) default 0 constraint board_hits_ck CHECK(b_hits>=0) --조회수
);
COMMENT ON COLUMN "SCOTT"."BOARD"."B_NO"IS 'Primary key of board table';

 

board 테이블의 글번호용 seq_bno 시퀀스생성

create sequence seq_bno 
start with 1
increment by 1;

글쓰기

insert into board
 values (seq_bno.NEXTVAL,'공지입니다','깨끗한 게시판환경을 조성합시다.','작성자1',sysdate,10);
 
insert into board(b_no,b_title,b_content,b_writer,b_writeDate,b_hits)
values (seq_bno.NEXTVAL,'','내용2','',null,null);
 
insert into board(b_no,b_title,b_content,b_writer)
values (seq_bno.NEXTVAL,'제목22','내용22','작성자22');
 
insert into board(b_no,b_title,b_content,b_writer)
values (seq_bno.NEXTVAL,'제목23','내용23','작성자22');

 

특정게시글의 상세보기

1) 조회수 증가

=>b_no가 1인 글의 조회수 증가 b_no가 1인 글의 조회수 증가

update board
set b_hits=b_hits+1
where b_no=1;

select * from board where b_no=1;

 

목록 조회(가장 최신글부터)

select * from board
order by b_writedate desc;

 

목록조회+현재페이지에 따른 게시물개수 반영

select * from
where b_no between? AND ?
order by b_no desc;

삭제하기

delete from board
where b_no=21;

---

댓글게시판 table 생성

create table replyboard(
    r_no      number(4) constraint replyboard_rno_pk primary key, --댓글번호
    b_no      number(4),  --원글번호
    r_content varchar2(270) constraint replyboard_rcontent_nn NOT NULL, --댓글내용
    r_writer  varchar2(20), --댓글작성자
    r_writeDate date default sysdate, --댓글작성일
   
    constraint replyboard_bno_fk foreign key(b_no)
     references board(b_no)
     [on delete cascade] --부모가 제거되면 자식도 제거
);

insert into replyboard(r_no,b_no,r_content,r_writer,r_writeDate)
 values (seq_rno.NEXTVAL,1,'동참합니다','댓글작성자1',sysdate);

select r_no,b_no,r_content,r_writer,r_writeDate
from replyboard
where b_no=1
order by r_writeDate desc;