MySQL 각종 쿼리문 작성하기

1. 테이블 설정

drop table if exists member_table;
create table member_table (
	id bigint auto_increment,
    member_email varchar(50) not null,
    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),
    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,
    board_created_time DATETIME,
    board_updated_tme DATETIME,
    board_file_attached Int,
    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),
    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),
    comment_created_time DATETIME,
    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),
    constraint fk_good_table foreign key(member_id) references member_table(id) on delete cascade
    );

 

1 - 1 ERD 설정

예제로 쓸 데이터를 입력하였다.

 

 

2. 예제문 작성하기

2-1 회원가입 및 로그인 수정 삭제

-- 1. 회원가입    
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');
    
-- 2. 이메일 중복체크
alter table member_table add constraint unique(member_email);
-- 가입된 이메일로 가입하려고 한다면
select member_email from member_table where member_email = 'a@gmail.com';
desc member_table;
-- 가입되어 있지 않은 이메일로 가입하려고 한다면
select member_email from member_table where member_email = 'kasdas@gmail.com';
    
-- 로그인
select * from member_table where member_email = 'a@gmail.com' and member_password = '1234';
select * from member_table where member_email = 'a@gmail.com' and member_password = '5678'; -- X
-- 전체 회원 목록 조회
select * from member_table;

-- 특정 회원만 조회 예) 이름이 이쿼리인사람
select * from member_table where member_name = '이쿼리';

-- 회원정보 수정화면 요청
select * from member_table;
-- 회원정보 수정처리(비밀번호 변경)
set sql_safe_updates=0;
update member_table set member_password = '5678' where id=1;
-- 회원 삭제 또는 탈퇴 
delete from member_table where id=1;

모든 데이터 삭제나 수정은 id ( PK ) 를 기준으로 한다.

DB는 데이터를 가공하지 않고 수동적으로 제약조건을 확인해 리턴을 수행한다.

 

 

2-2 게시판 테이블 작성

 

-- 게시글 카테고리
-- 게시판 카테고리는 자유게시판 , 공지사항 , 가입인사
insert into category_table(category_name)
	values ('자유게시판');
insert into category_table(category_name)
	values ('공지사항');
insert into category_table(category_name)
	values ('가입인사');

select * from category_table order by id;

-- 게시판 기능
-- 1. 게시글 작성(파일첨부 X) 3개이상
-- 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_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);

-- 2. 게시글 목록조회
-- 2.1 전체글 목록 조회
select * from board_table;

-- 2.2 자유게시판 목록 조회
select * from board_table where category_id=1;

-- 2.3 공지사항 목록 조회
select * from board_table where category_id=2;

-- 2.4 목록 조회시 카테고리 이름도 함께 나오게 조회
select *, category_id as '자유게시판' from board_table where category_id=1;
select *, category_id as '공지사항' from board_table where category_id=2;
select *, category_id as '가입인사' from board_table where category_id=3;

카테고리가 자유게시판 , 공지사항 , 가입인사로 나눠있고

유저마다 각 카테고리에 맞는 글을 작성하였다.

 

자유게시판 글 목록

공지사항 글 목록

가입인사 글 목록

board_file attached가 0이면 파일첨부가 없고 1이면 파일첨부가 있다.

카테고리 id로 각 자유게시판 , 공지사항 , 가입인사를 확인할 수 있도록 하였다.