chapter04_MVC / DB TABLE

create SEQUENCE seq_board;

create table tbl_board(
    bno number(10,2),
    title varchar2(200) not null,
    content varchar2(2000) not null,
    writer varchar2(50) not null,
    regdate date default sysdate,
    updatedate date default sysdate
);

   
ROLLBACK;
alter table tbl_board
add constraint pk_board
primary key(bno);

commit;

SELECT * FROM tbl_board;
SELECT * FROM tbl_board order by bno desc;
select * from tbl_board where bno > 2;

insert into tbl_board(bno, title, content, writer)
values(seq_board.nextval, '테스트제목', '테스트내용', 'user01');

insert into tbl_board(bno, title, content, writer)
values(seq_board.nextval, '테스트제목1', '테스트내용1', 'user02');

insert into tbl_board(bno, title, content, writer)
values(seq_board.nextval, '테스트제목2', '테스트내용2', 'user03');

insert into tbl_board(bno, title, content, writer)
values(seq_board.nextval, '테스트제목3', '테스트내용3', 'user04');

insert into tbl_board(bno, title, content, writer)
values(seq_board.nextval, '테스트제목4', '테스트내용4', 'user05');

commit;

select bno,title, content,writer,regdate,updatedate from 
(SELECT 
    /*+index_desc(tbl_board pk_board)*/
ROWNUM rn, bno,title, content,writer,regdate,updatedate from tbl_board
where rownum <= 20)   
where rn > 10;


create table tbl_reply(
    rno number(10,0),
    bno number(10,0) not null,
    reply varchar2(1000) not null,
    replyer varchar2(50) not null,
    replyDate date default sysdate,
    updateDate date default sysdate
);

create sequence seq_reply;

alter table tbl_reply add constraint pk_reply primary key (rno);

alter table tbl_reply add constraint fk_reply_board
foreign key (bno) references tbl_board(bno);

SELECT * FROM  tbl_reply;


create table tbl_sample1(
    col1 VARCHAR2(500)
);

create table tbl_sample2(
    col2 VARCHAR2(50)
);

SELECT * FROM tbl_sample1;
SELECT * FROM tbl_sample2;

DELETE  from tbl_sample1;

alter table tbl_board add(replycnt number default 0);

update tbl_board set replycnt = 
(select count(*) from tbl_reply  where tbl_board.bno=tbl_reply.bno);



select * from tbl_board  order by bno desc;


update tbl_board set replycnt = (select count(rno) from tbl_reply 
where tbl_reply.bno = tb1_board.bno);




COMMIT;




create table tbl_attach(
    uuid varchar2(200) not null,
    uploadPath varchar2(200) not null,
    fileName varchar2(200) not null,
    fileType char(1) default 'I',
    bno number(10,0)
);

alter table tbl_attach add constraint pk_attach primary key (uuid);

alter table tbl_attach add constraint fk_board_attach foreign key (bno) references tbl_board(bno);

select * from tbl_attach;





create table tbl_member(
      userid varchar2(50) not null primary key,
      userpw varchar2(100) not null,
      username varchar2(100) not null,
      regdate date default sysdate, 
      updatedate date default sysdate,
      enabled char(1) default '1');
 
 
create table tbl_member_auth (
     userid varchar2(50) not null,
     auth varchar2(50) not null,
     constraint fk_member_auth foreign key(userid) references tbl_member(userid)
);

SELECT * FROM tbl_member_auth;

SELECT * FROM tbl_member;

-- userid계정명, userpw비밀번호, username, enabled, regdate, updatedate, auth
SELECT
    	mem.userid, userpw, username, enabled, regdate, updatedate, auth
FROM
    	tbl_member mem LEFT JOIN tbl_member_auth auth ON mem.userid=auth.userid
WHERE
    	mem.userid='admin99';

create table persistent_logins(
    username VARCHAR2(64) not null,
    series VARCHAR2(64) primary key,
    token VARCHAR2(64) not null,
    last_used timestamp not null
);

SELECT * FROM persistent_logins;

COMMIT;