MariaDB 테이블 쿼리

[subutuo]

use daview;

drop table if exists blame cascade; drop table if exists comment_board cascade; drop table if exists board cascade; drop table if exists member cascade;

create table member( M_EMAIL varchar(50), #기본키 M_PW varchar(20), M_NICK varchar(20) not null, #유니크 M_REG_DATE timestamp default sysdate(), M_FLAG int default 0, #삭제,경고 여부 primary key (M_EMAIL), unique (M_NICK) );

create table board( B_NUM int AUTO_INCREMENT, #기본키 B_SUBJECT varchar(50) not null, B_CONTENT varchar(500)not null, B_RC int default 0, #조회수 B_REG_DATE timestamp default sysdate(), B_PART varchar(10), #구분 M_NICK varchar(20) not null, #외래키 B_STAR varchar(2), #별 B_C_TOTAL int default 0, #댓글 수 B_FLAG int default 0, #삭제여부membermembermember B_NOTICE int default 0, #공지사항 인지아닌지 PRIMARY KEY (B_NUM), FOREIGN KEY(M_NICK) REFERENCES MEMBER(M_NICK) ON DELETE CASCADE ON UPDATE CASCADE );

create table COMMENT_BOARD( C_NUM int AUTO_INCREMENT, #기본키 B_NUM int, #외래키 M_NICK varchar(20), #외래키 C_DATE timestamp default sysdate(), C_REF int default 0, C_RE_STEP int default 0, C_RE_LEVEL int default 0, C_CONTENT varchar(500) not null, C_FLAG int default 0, #삭제여부 primary key (C_NUM), FOREIGN KEY(B_NUM) references BOARD (B_NUM) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY(M_NICK) references MEMBER (M_NICK) ON DELETE CASCADE ON UPDATE CASCADE );

create table blame( blame_id int AUTO_INCREMENT, #blame테이블의 PK blame_type int, #신고 종류 target_id int, #B_NUM 또는 C_NUM이 될 수 있음 target_type int, #타겟ID는 TYPE이 0이면 테이블의 아이디,1이면 댓글의 아이디 target_nick varchar(20), #신고당한 닉네임 accuser_nick varchar(20), #신고한 닉네임 blame_date timestamp default sysdate(), primary key(blame_id), foreign key(target_nick) references Member(M_NICK) ON DELETE CASCADE ON UPDATE CASCADE, foreign key(accuser_nick) references Member(M_NICK) ON DELETE CASCADE ON UPDATE CASCADE );

select * from member;

show variables like 'c%';

insert into member (m_email,m_pw,m_nick) values ('a@a.com','1234','실험용');

select count(*) from member where m_email ='a@a.com' and m_pw='1234' and m_flag=0;

insert into board(b_subject,b_content,b_part,m_nick,b_star) values('실험제목','실험내용','TEST','실험용','5'); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); insert into board(b_subject,b_content,b_part,m_nick,b_star) (select b_subject,b_content,b_part,m_nick,b_star from board); select count(*) from board; commit;