MySQL 각종 쿼리 문 예제

https://swkn.tistory.com/94에서 이어지는 내용이다.

데이터는 아래와 같다.

drop table if exists member_table;
create table member_table (
	id bigint auto_increment,
    member_email varchar(50) not null unique,
    member_name varchar(20) not null,
    member_password varchar(20) not null,
    constraint pk_member_table primary key(id)
    );

drop table if exists category_table;
create table category_table (
	id bigint auto_increment,
    category_name varchar(20) not null unique,
    constraint pk_category_table primary key(id)
    );

drop table if exists board_table;    
create table board_table (
	id bigint auto_increment,
    board_title varchar(50) not null,
    board_writer varchar(20) not null,
    board_contents varchar(500),
    board_hits Int default 0,
    board_created_time DATETIME default now(),
    board_updated_tme DATETIME on update now(),
    board_file_attached Int default 0, -- 파일 첨부여부(0 있으면 1)
    member_id bigint,
    category_id bigint,
    constraint pk_board_table primary key(id),
    constraint board_table foreign key(member_id) references member_table(id) on delete cascade,
    constraint fk_board_table foreign key(category_id) references category_table(id) on delete set null
    );
    
drop table if exists board_file_table;
create table board_file_table (
	id bigint auto_increment,
    original_file_name varchar(100), -- 사용자가 업로드한 파일의 이름
    stored_file_name varchar(100), -- 관리용 파일 이름(파일이름 생성 로직은 backend에서)
    -- 증명사진.jpg 546516651-증명사진.jpg
    board_id bigint,
    constraint pk_board_file_table primary key(id),
    constraint fk2_board_table foreign key(board_id) references board_table(id) on delete cascade
    );

drop table if exists comment_table;
create table comment_table (
	id bigint auto_increment,
    comment_writer varchar(20) not null,
    comment_contents varchar(200) not null,
    comment_created_time DATETIME default now(),
    board_id bigint,
    member_id bigint,
    constraint pk_comment_table primary key(id),
    constraint comment_table foreign key(board_id) references board_table(id) on delete cascade,
    constraint fk_comment_table foreign key(member_id) references member_table(id) on delete cascade
    );
    
drop table if exists good_table;
create table good_table (
	id bigint auto_increment,
    comment_id bigint,
    member_id bigint,
    constraint pk_good_table primary key(id),
    constraint good_table foreign key(comment_id) references comment_table(id) on delete cascade,
    constraint fk_good_table foreign key(member_id) references member_table(id) on delete cascade
    );
   
insert into member_table(member_email,member_name,member_password)
	values('a@gmail.com','김쿼리','1234');
insert into member_table(member_email,member_name,member_password)
	values('b@gmail.com','이쿼리','1234');
insert into member_table(member_email,member_name,member_password)
	values('c@gmail.com','잡쿼리','1234');
    
insert into category_table(category_name)
	values ('자유게시판');
insert into category_table(category_name)
	values ('공지사항');
insert into category_table(category_name)
	values ('가입인사');
    
-- 3번회원이 자유게시판 글2개 공지사항 글1개 작성
insert into board_table(board_title, board_writer, board_contents, member_id, category_id)
	values ('유저3의 제목3','유저3','유저3의 내용3',3,1);
-- 3번회원이 자유게시판 글2개 공지사항 글1개 작성
insert into board_table(board_title, board_writer, board_contents, member_id, category_id)
	values ('유저3의 제목33','유저3','유저3의 내용33',3,1);
insert into board_table(board_title, board_writer, board_contents, member_id, category_id)
	values ('유저3의 제목3','유저3','유저3의 내용3',3,2);

-- 4번회원이 자유게시판 글 3개 작성
insert into board_table(board_title, board_writer, board_contents, member_id, category_id)
	values ('유저4의 제목4','유저4','유저4의 내용4',4,1);
insert into board_table(board_title, board_writer, board_contents, member_id, category_id)
	values ('유저4의 제목44','유저4','유저4의 내용44',4,1);
insert into board_table(board_title, board_writer, board_contents, member_id, category_id)
	values ('유저4의 제목444','유저4','유저4의 내용444',4,1);

-- 4번회원이 가입인사 글 1개 작성
insert into board_table(board_title, board_writer, board_contents, member_id, category_id)
	values ('유저4의 제목4','유저4','유저4의 내용4',4,3);
    
-- 1.1 게시글 작성(파일첨부 o)
-- 3번 회원이 파일있는 자유게시판 글 2개 작성
insert into board_table(board_title, board_writer, board_contents, board_file_attached, member_id, category_id)
	values ('유저3의 제목3파일첨부o','유저3','유저3의 내용4파일첨부o',1,3,1);
insert into board_file_table(original_file_name, stored_file_name, board_id)
	values ('유저3의 파일이름3','유저3의 파일이름3',9);
insert into board_table(board_title, board_writer, board_contents, board_file_attached, member_id, category_id)
	values ('유저3의 제목3파일첨부o2','유저3','유저3의 내용4파일첨부o2',1,3,1);
insert into board_file_table(original_file_name, stored_file_name, board_id)
	values ('유저3의 파일이름3,2','유저3의 파일이름3,2',10);

 

1. 게시글 목록에서의 쿼리

 

-- 2.4 목록 조회시 카테고리 이름도 함께 나오게 조회
-- 전체출력
select * from board_table b, category_table c where c.id = category_id order by b.id asc;
-- 자유게시판 출력
select * from board_table b, category_table c where b.category_id=1 and c.id = category_id;
-- 자유게시판에서 파일있는 글만 출력
select * from board_table board_t , board_file_table board_f where category_id=1 and board_t.id = board_f.board_id;
-- 공지사항 출력
select * from board_table b, category_table c where b.category_id=2 and c.id = category_id;
-- 공지사항에서 파일있는 글만 출력
select * from board_table board_t , board_file_table board_f where category_id=2 and board_t.id = board_f.board_id;
-- 가입인사 출력
select * from board_table where category_id=3;
-- 가입인사에서 파일있는 글만 출력
select * from board_table board_t , board_file_table board_f where category_id=3 and board_t.id = board_f.board_id;

-- 3.1 2번 게시글 조회(조회수 처리 필요함)
update board_table set board_hits = board_hits+1 where id=2;
select * from board_table where id=2;

board_hits를 수정해 조회수처리를 하였다.

-- 3.2 파일 첨부된 게시글 조회(게시글 내용과 파일을 함께)
update board_table set board_hits = board_hits+1 where id=9;
select * from board_table t , board_file_table f where t.id = f.board_id and t.id= 9;
-- 쿼리 두번 실행
select * from board_table where id=9;
select * from board_file_table where board_id=9;

파일 첨부가 된 게시글은 board_table에서 id = 9 , id = 10에 해당하기에 id = 9번의 조회수를 올렸다.

 

-- 4. 3번회원이 자유게시판에 첫번째로 작성한 게시글의 제목 , 내용 수정
update board_table set board_title = '수정된 제목', board_contents = '수정된 내용'
	where id= 2;

3번 회원이 첫 번째로 작성한 글이 board_table의 id. 가 2번이기 때문에 

update 쿼리문으로 제목과 내용을 수정하였다.

 

-- 5. 4번회원이 자유게시판에 첫번째로 작성한 게시글 삭제
delete from board_table where id = 5;

4번 회원이 첫 번째로 작성한 글이 board_table의 id가 5번이기 때문에 

delete 쿼리문으로 글을 삭제하였다

 

-- 6. 페이징 처리(한페이지당 글3개씩)
select * from board_table order by id desc;
-- 6.1 첫번째페이지
select * from board_table orders order by id desc limit 3;
-- 6.2 두번째페이지
select * from board_table orders order by id desc limit 3 offset 3;
-- 3부터 3개를 보겠다
select * from board_table orders order by id desc limit 3,3;
-- 6.3 세번째 페이지
select * from board_table orders order by id desc limit 3 offset 6;
-- 6부터 3개를 보겠다
select * from board_table orders order by id desc limit 6,3;
-- 전체 글 갯수
select count(id) from board_table;
-- 정렬기준은 조회수, 한페이지당 글 5개씩 볼때 페이지


select * from board_table order by board_hits desc limit 0, 5;
-- 게시글 갯수 : 10개 , 한페이지당 4개씩:3 , 한페이지당 3개씩:4

1페이지

2페이지

3페이지

 

페이징 처리에 대해서는 아래에서 서술하겠다.

https://swkn.tistory.com/96

 

-- 7-1. 검색(글 제목 기준)
select * from board_table where board_title like '%3%' order by board_title asc;

-- 7-2. 검색(결과를 오래된 순으로 조회)
select * from board_table where board_title like '%3%' order by board_created_time asc;

-- 7-3. 검색(조회수 내림차순으로 조회)
select * from board_table where board_title like '%%' order by board_hits desc;

-- 7.4 검색결과 페이징 처리 ( 검색결과 중 첫페이지(한페이지당 글 2개씩 나온다고 가정))
select * from board_table where board_title like '%3%' order by board_title asc limit 0,2;
select * from board_table where board_title like '%3%' order by board_title asc limit 2,4;

7-1 검색( 글 제목 기준 )

like문으로 %3% ( 3이 앞뒤로 자릿수 상관없이 포함하는 제목을 모두 출력 ) 결과를 제목순으로 오름차순

글제목에 3이 들어갈경우 출력

 

7-2 검색( 결과를 오래된 순으로 조회 )

like문으로 %3% ( 3이 앞뒤로 자릿수 상관없이 포함하는 제목을 모두 출력 ) 결과를 작성시간으로 오름차순

글제목에 3이 들어갈 경우에서 가장 오래된 순으로 출력

 

7-3 검색( 조회수 내림차순으로 조회 )

like문으로 %% ( 결과를 모두 출력 ) 결과를 조회수로 내림차순

결과를 모두출력에서 조회수가 내림차순으로 조회

 

7-4 검색결과 페이징 처리 ( 검색결과 중 첫 페이지 ( 한 페이지당 글 2개씩 나온다고 가정 ) )

limit 문으로 ( a , b ) 페이징 처리

a = 어디서 시작할 것인지 ( 행 )

b = 몇 행을 출력할 것인지

모두 출력 결과

1페이지

2페이지

 

2. 댓글 목록에서의 쿼리

 

-- 댓글 기능 
-- 1. 댓글 작성 
-- 1.1. 3번 회원이 2번 게시글에 댓글 작성 
insert comment_table( comment_writer, comment_contents, board_id, member_id)
	values ('3번회원의 댓글1','3번유저',2,3);
-- 1.2. 4번 회원이 2번 게시글에 댓글 작성 
insert comment_table( comment_writer, comment_contents, board_id, member_id)
	values ('4번회원의 댓글1','4번유저',2,4);

comment_table의 board_id와 member_id는 foreign key로 이어져 있다.

 

insert  into 쿼리문으로 글작성자, 글내용과 댓글이니 그에 맞는 글 번호에 댓글작성자의 id 데이터를 넣는다.

 

1.1 3번 회원이 2번 게시글에 댓글을 작성할 경우

글작성자는 '3번 회원의 댓글 1' , 글내용은 '3번 유저' , 글 번호는 2 , 댓글작성자의 id는 3이다.

 

1-2 4번 회원이 2번 게시글에 댓글을 작성할 경우

글작성자는 '4번 회원의 댓글 1' , 글내용은 '4번 유저' , 글 번호는 2 , 댓글작성자의 id는 4이다.

 

 

3. 좋아요 테이블에서의 쿼리

-- 3. 댓글 좋아요 
select * from good_table;
-- 3.1. 4번 회원이 3번 회원이 작성한 댓글에 좋아요 클릭
-- 좋아요 하기전 체크
select id from good_table where comment_id=1 and member_id=4;
-- 좋아요 한적이 없다면 좋아요
insert into good_table(comment_id, member_id) values (1,4);
-- 좋아요 한적이 있다면 좋아요 취소
delete from good_table where id=4;

아직 데이터를 입력받는 게 아니므로 임의의 번호를 부여하겠다.

 

만약 comment_table의 1번 ( 4번 회원이 3번 회원에게 작성한 댓글 )에 좋아요를 했는지 확인하려면

comment_id=1와 member_id=4 가 같은지 확인, 

확인 결과가 없으면  good_table에 insert info 쿼리문으로 데이터를 입력한다.

만약 결과가 있다면 좋아요 취소므로 delete 쿼리문으로 good_table에 있는 데이터를 삭제한다.

 

댓글을 모두 출력

-- 4. 댓글 조회시 좋아요 갯수도 함께 조회
select count(id) from good_table where comment_id=1;

good_table에 comment_id ( 댓글 번호 )에 얼마나 있는지 count()로 확인가능하다.

댓글에 좋아요가 얼마나 쌓였는지 count()로 개수를 알아내면 된다.

댓글이 2개있다