게시판

create table memberInfo(

memNum number(5) primary key,

memId varchar2(12) not null,

memPwd varchar2(12) not null,

memName varchar(10) not null,

birthday date,

zipcode number(5),

addr1 varchar2(50),

addr2 varchar2(30),

email varchar2(20) not null,

tel varchar2(13) not null,

writedate date default sysdate,

lastvisitdate date default sysdate,

memLevel char(1) default 'D');

alter table memberInfo add constraint uk_memid_memberInfo unique (memId);

create sequence memberInfosqu start with 1 increment by 1;

create table freeBoard(

boardNum number(5) primary key,

memId varchar2(12) not null,

subject varchar2(100) not null,

content clob not null,

hit number(3) default 0,

writedate date default sysdate);

alter table freeBoard add constraint fk_memId_freeBoard foreign key (memId) references memberInfo (memId);

create sequence freeBoardsqu start with 1 increment by 1;

-- 오라클을 이용한 paging

-- 시퀀스 확인

select * from user_sequences;

select * from freeboard;

@z://filefolder/freeboard.sql;

select * from user_constraints;

alter table freeboard

drop constraint fk_memId_freeboard;

select * from freeboard;

commit;

select * from freeboard order by boardnum desc;

selcelt * from

 (select * from

  (select * from 

   (select boardnum, memid, subject, hit, to_char(writedate, 'mm:ss') from freeboard order by boardnum desc)

  where rownum <= 15 order by rownum desc)

 where rownum <= 5)

order by boardNum desc;

-- 한 페이지에 4개씩 출력할 경우 1page 선택

selcelt * from

 (select * from

  (select * from 

   (select boardnum, memid, subject, hit, to_char(writedate, 'mm:ss') from freeboard order by boardnum desc)

  where rownum <= 4*1 order by rownum desc)

 where rownum <= 4)

order by boardNum desc;