TABLE 생성

create table user
(id int unsigned auto_increment,
email varchar(30) not null,
nick varchar(10) not null unique check(length(nick) > 1),
gender int not null default 0 check(gender in (1, 2)),
age varchar(8) not null,
joindated date default(current_date),
introduce varchar(100) not null,
sub varchar(50) not null,
primary key(id));

create table category
(id int unsigned auto_increment,
category varchar(10) not null default(''),
primary key(id));

create table sejulbook
(id int unsigned auto_increment,
bookname varchar(20) not null, 
writer varchar(20) not null, 
publication date not null, 
publisher varchar(20) not null, 
grade decimal(2, 1) not null default(0), // default 3
thumbnail varchar(250) not null, 
sejul varchar(200) not null, // default ''
sejulplus text not null default(''),
datecreated datetime default(current_time),
user_id int unsigned not null,
category_id int unsigned not null, // default 1
divide int unsigned not null check(divide in (0, 1)),
primary key(id),
key user_id_idx (user_id),
key category_id_idx (category_id));

create table likes
(id int unsigned auto_increment,
sejulbook_id int unsigned not null,
liker_id int unsigned not null,
primary key(id),
key sejulbook_id_idx (sejulbook_id),
key liker_id_idx (liker_id));

create table reply
(id int unsigned auto_increment,
reply text not null,
replydate datetime default(current_time),
sejulbook_id int unsigned not null,
replyer_id int unsigned not null,
primary key(id),
key sejulbook_id_idx (sejulbook_id),
key replyer_id_idx (replyer_id));

create table tag
(id int unsigned auto_increment,
tag varchar(15) not null default(''),
sejulbook_id int unsigned not null,
primary key(id),
key sejulbook_id_idx (sejulbook_id));

create table follow
(id int unsigned auto_increment,
follower_id int unsigned not null,
following_id int unsigned not null,
primary key(id),
key follower_id_idx (follower_id),
key following_id_idx (following_id));

~~create table sejulbook_revise
(id int unsigned auto_increment,
sejulbook_id int unsigned not null,
primary key(id),
key sejulbook_id_idx (sejulbook_id));~~

> <https://planetscale.com/docs/learn/operating-without-foreign-key-constraints>

쿼리 수정사항 (업데이트 04.16)

- 검색 (책) 10개
select max(id)
from sejulbook
where bookname = '' and divide = 1;

select S.thumbnail, S.sejul ,U.nick
from sejulbook as S
	inner join user as U
	on S.user_id = U.id
where S.bookname = "" and S.id < 20 and divide = 1
order by S.datecreated
limit 10;

- 검색 (저자) 10개
select max(id)
from sejulbook
where writer = '' and divide = 1;

select S.thumbnail, S.sejul, U.nick
from sejulbook as S
	inner join user as U
	on S.user_id = U.id
where S.writer = "" and S.id < 20 and S.divide = 1
order by S.datecreated
limit 10;

- 검색 (카테고리) 10개
select max(id)
from sejulbook
where categort_id = '' and divide = 1; 

select S.thumbnail, S.sejul, U.nicl
from sejulbook as S
	inner join user as U
	on S.user_id = U.id
where S.category_id = "" and S.id < 20 and S.divide = 1
order by S.datecreated
limit 10;

- 태그 (세 줄 독후감 id 반환) 10개
select max(id)
from tag
where tag = ""

select S.thumbnail, S.sejul, S.user_id
from sejulbook as S
	inner join tag as T
	on S.id = T.sejulbook_id
where tag = "" and T.id < 20 and S.divide = 1
order by S.datecreted
limit 10;

- 구독자 목록 최신순 기준 10개
select max(id)
from follow
where follower_id = 1

select F.id, S.thumbnaill, S.user_id, S.sejul
from sejulbook as S
	inner join follow as F
	on S.user_id = F.following_id
where F.follower_id = 1 and F.id < 20 and S.divide = 1
order by S.datecreated
limit 10;

-- 관심서재 최근 10개 보여주기
select user_id, bookname, thumbnail, sejul, datecreated
from sejulbook as S
	inner join follow as F
		on S.user_id = F.follower_id
where F.following_id = 1 and S.divide = 1
order by datecreated DESC
limit 10;

-- 한 달간 상위 10개의 좋아요 받은 책 쿼리 짜기
select S.id, S.bookname, S.sejul, S.user_id, S.datecreated, count(L.sejulbook_id) as likes_sum, S.thumbnail
from sejulbook as S
	inner join likes as L
    on S.id = L.sejulbook_id
where datecreated regexp '2023-06|2023-03' and S.divide = 1
group by L.sejulbook_id
order by S.id, likes_sum desc
limit 10;

쿼리 추가사항 (업데이트 03.26)

- 나의 관심서재 목록 (nick)
select id, nick, introduce
from user
where id in (select F.following_id
from user as U
	inner join follow as F
	on U.id = F.follower_id
where U.id = ""); -- U.id에는 나의 id 입력

-- 시작 기준 20개 반환 / 그 뒤에 20개 반환 > 이렇게 되려면 / (id)의 값을 반환해야 한다.

- 나의 구독자들 목록 (nick)
select id, nick, introduce
from user
where id in (select F.follower_id
from user as U
	inner join follow as F
    on U.id = F.following_id
where U.id = ""); -- U.id에는 나의 id입력

following_id -> follower_id 
팔로우 받는 사람  / 팔로우 하는 사람

- 나의 관심서재 목록 (nick)
SELECT MAX(id) 
FROM follow
where follower_id = 1; -- 첫 조회시 가장 큰 id를 조회하여 사용

select F.id as follow_id, U.id, nick, introduce
from user as U
	inner join follow as F
		on U.id = F.following_id
where F.follower_id = 1 and F.id < 20
order by desc
limit 20;

- 나의 구독자들 목록 (nick)
SELECT MAX(id) 
FROM follow
where follower_id = 1; -- 첫 조회시 가장 큰 id를 조회하여 사용

select F.id as follow_id, U.id, nick, introduce
from user as U
	inner join follow as F
		on U.id = F.following_id
where F.follower_id = 1 and F.id < 20
limit 20;

- 검색 (책) 10개
select max(id)
from sejulbook
where bookname = '' and divide = 1;

select S.thumbnail, S.sejul ,U.nick
from sejulbook as S
	inner join user as U
	on S.user_id = U.id
where S.bookname = "" and S.id < 20 and divide = 1
order by S.datecreated
limit 10;

- 검색 (저자) 10개
select max(id)
from sejulbook
where writer = '' and divide = 1;

select S.thumbnail, S.sejul, U.nick
from sejulbook as S
	inner join user as U
	on S.user_id = U.id
where S.writer = "" and S.id < 20 and divide = 1
order by S.datecreated
limit 10;

- 검색 (카테고리) 10개
select max(id)
from sejulbook
where categort_id = '' and divide = 1; 

select S.thumbnail, S.sejul, U.nicl
from sejulbook as S
	inner join user as U
	on S.user_id = U.id
where S.category_id = "" and S.id < 20 and divide = 1
order by S.datecreated
limit 10;

- 태그 (세 줄 독후감 id 반환) 10개
select max(id)
from tag
where tag = ""

select S.thumbnail, S.sejul, S.user_id
from sejulbook as S
	inner join tag as T
	on S.id = T.sejulbook_id
where tag = "" and T.id < 20 and S.divide = 1
order by S.datecreted
limit 10;

- 구독자 목록 최신순 기준 10개
select max(id)
from follow
where follower_id = 1

select F.id, S.thumbnaill, S.user_id, S.sejul
from sejulbook as S
	inner join follow as F
	on S.user_id = F.following_id
where F.follower_id = 1 and F.id < 20 and S.divide = 1
order by S.datecreated
limit 10;

TABLE 수정사항

-- 책 제목 길이 수정 (20 > 50)
alter table sejulbook modify bookname varchar(50) not null;

-- 저자 이름 길이 수정 (20 > 50)
alter table sejulbook modify writer varchar(50) not null;

-- user테이블 소개란 (100 > 200)
alter table user modify introduce varchar(200) not null;

-- sejulbook 테이블 origin_thumbnail 칼럼 추가
alter table sejulbook add origin_thumbnail varchar(250) not null;

-- sejulbook 테이블 datecreated 칼럼 not null
alter table sejulbook modify datecreated date default(current_date) not null;

3월 9일 수정사항
-- 평점 default 1에서 3으로 수정
alter table sejulbook modify grade decimal(2,1) not null default(3);

-- sejul default 빈값 추가
alter table sejulbook modify sejul varchar(200) not null default('');

-- 카테고리 default 1로 추가 (1 = 빈값) 
alter table sejulbook modify category_id int unsigned not null default(1);

-- grade 데이터 형식 변환 decimal > int
alter table sejulbook modify grade int unsigned not null default(3);

3월 16일 수정사항
-- sejulbook default date에서 datetime으로 수정
alter table sejulbook modify datecreated datetime default (current_time);

검색 기능 설정 및 SELECT

-- Full Text Index 설정
alter table sejulbook add fulltext (bookname) with parser ngram;
alter table sejulbook add fulltext (writer) with parser ngram;
alter table user add fulltext (nick) with parser ngram;
alter table tag add fulltext (tag) with parser ngram;
alter table category add fulltext (category) with parser ngram;

-- 검색 쿼리 (* 앞에 쓰면 관련된 데이터 다 나옴 ex)'최*'는 최준철, 최소 등..)
# order by 1로 설정해서 ㄱㄴㄷ 순으로 나옴
# limit 10으로 설정해서 검색어 10개만 나옴
~~1.책이름
select bookname from sejulbook where match(bookname) against('*' in boolean mode) order by 1 limit 10;~~

~~2.책저자
select writer from sejulbook where match(writer) against('*' in boolean mode) order by 1 limit 10;~~

3.사용자
select nick from user where match(nick) against('*' in boolean mode) order by 1 limit 10;

4.태그 (sejulbook_id는 제외)
select tag, count(tag) from tag where match(tag) against('*' in boolean mode) group by tag order by 1 limit 10;

5.카테고리
select id, category from category where match(category) against('*' in boolean mode) order by 1 limit 10;

1. USER

-- 회원가입 할 때
insert into user (Null, email, gender, age, joindated, sub) values ();

-- 기본키 전달하기 위한 쿼리
select id
from user
where sub = "받은 값";

-- 회원가입 후 소개글 입력
update user set introduce = '수정 소개글' where id = "";

-- 회원가입 후 닉네임 입력
update user set nick = '수정할 닉네임' where id = "";

-- 한 유저가 읽은 책 이름들과 세 줄 독후감 내용들
select S.bookname, S.sejul
from user as U
	inner join sejulbook as S 
	on U.id = S.id
where U.id = "";

-- 유저 정보 가져오는 쿼리
select nick, introduce
from user
where id = "";

~~-- 나의 관심서재 목록(nick)
select U.nick
from user as U
	inner join follow as F
	on U.id = F.follower_id
where U.id = "";~~

~~-- 나의 구독자 목록(nick)
select U.nick
from user as U
	inner join follow as F
	on U.id = F.following_id
where U.id = "";~~

-- '회원탈퇴'는 외래키 먼저 삭제하고 나중에 기본키인 user 테이블의 id 지우기

-- 세 줄 독후감의 태그 정보 삭제
delete from tag where sejulbook_id = "";

-- 세 줄 독후감의 댓글 정보 삭제
delete from reply where replyer_id = "";

-- 세 줄 독후감의 좋아요 정보 삭제
delete from likes where liker_id = "";

-- user가 작성한 모든 세 줄 독후감 삭제
delete from sejulbook where user_id = "";

-- follow 정보 삭제
delete from follow where follower_id = "" or following_id = "";

-- user 정보 삭제
delete from user where id = "";

2. SEJULBOOK (SEJULBOOK_REVISE 포함)

* 세 줄 독후감 작성 / default값이 있는 것 평점 (0), 추가 독후감 내용 (''), 작성일 (현재 날짜) / divide는 0 = 수정, 1 = 발행으로 구별

-- 세 줄 독후감 발행 전 임시저장
insert into sejulbook values (null, "책이름", "저자", "출판일", "출판사", "평점", "세 줄 독후감 내용", "추가 독후감 내용", "작성 날짜", "user_id FK", "category_id FK", "divide" = 0);

-- 세 줄 독후감 발행 후
insert into sejulbook values (null, "책이름", "저자", "출판일", "출판사", "평점", "세 줄 독후감 내용", "추가 독후감 내용", "작성 날짜", "user_id FK", "category_id FK", "divide" = 1);

~~-- 세 줄 독후감 발행 후 수정
insert into sejulbook_revise values (null, "sejulbook_id");~~

-- 세 줄 독후감 발행된 내용 불러오기
select bookname, datecreated, sejul, sejulplus, grade, name, publisher, publication
from sejulbook
where id = "" and divide = 1;

-- 세 줄 독후감 발행 전 임시저장된 내용 불러오기
select bookname, datecreated, sejul, sejulplus, grade, name, publisher, publication
from sejulbook
where id = "" and divide = 0;

-- 세 줄 독후감 user 닉네임 불러오기
select nick
from user
where id = "";

~~-- 세 줄 독후감 발행 후 수정본을 불러오기
select *
from sejulbook as S
	inner join sejulbook_revise as SR
	on S.id = SR.sejulbook
where S.id = "";~~

-- 책의 좋아요 수 / 댓글 수
select count(L.id), count(R.id)
from sejulbook as S
	left join likes as L
    on S.id = L.sejulbook_id
    left join reply as R
    on S.id = R.sejulbook_id
where S.user_id = ""
group by S.id

-- 읽은 책 수
select count(bookname)
from sejulbook
where user_id = "";

-- 세 줄 독후감 내용 수정
update sejulbook
	set sejul = "세 줄 독후감 수정 내용"
	where id = "수정하는 책 id";

-- 세 줄 독후감 plus 내용 수정
update sejulbook
	set sejulplus = "플러스 수정 내용"
	where id = "수정하는 책 id"

-- 세 줄 독후감 임시저장 후 발행
update sejulbook
	set divide = 1
	where id = "수정하는 책 id"

-- 세 줄 독후감 삭제
delete from sejulbook where id = "삭제할 세 줄 독후감 PK";
> 근데 세 줄 독후감 삭제하려면 세 줄 독후감의 외래키 먼저 삭제해야 됨
> 세 줄 독후감을 외래키 갖고 있는 테이블 [댓글 / 태그 / 좋아요 / 수정 테이블(있을 경우)]