좋아요 싫어요

댓글

DROP DATABASE IF EXISTS Spring_AM_01;
CREATE DATABASE Spring_AM_01;
USE Spring_AM_01;

article 테이블 생성

CREATE TABLE article(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
updateDate DATETIME NOT NULL,
title CHAR(100) NOT NULL,
body TEXT NOT NULL
);

member 테이블 생성

CREATE TABLE member(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
updateDate DATETIME NOT NULL,
loginId CHAR(20) NOT NULL,
loginPw CHAR(80) NOT NULL,
authLevel SMALLINT(2) UNSIGNED DEFAULT 3 COMMENT '권한 레벨 (3=일반,7=관리자)',
name CHAR(20) NOT NULL,
nickname CHAR(20) NOT NULL,
cellphoneNum CHAR(20) NOT NULL,
email CHAR(50) NOT NULL,
delStatus TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '탈퇴 여부 (0=탈퇴 전, 1=탈퇴 후)',
delDate DATETIME COMMENT '탈퇴 날짜'
);

article TD 생성

INSERT INTO article
SET regDate = NOW(),
updateDate = NOW(),
title = '제목1',
body = '내용1';

INSERT INTO article
SET regDate = NOW(),
updateDate = NOW(),
title = '제목2',
body = '내용2';

INSERT INTO article
SET regDate = NOW(),
updateDate = NOW(),
title = '제목3',
body = '내용3';

INSERT INTO article
SET regDate = NOW(),
updateDate = NOW(),
title = '제목4',
body = '내용4';

member TD 생성

(관리자)

INSERT INTO member
SET regDate = NOW(),
updateDate = NOW(),
loginId = 'admin',
loginPw = 'admin',
authLevel = 7,
name = '관리자',
nickname = '관리자',
cellphoneNum = '01012341234',
email = 'abcd@gmail.com';

(일반)

INSERT INTO member
SET regDate = NOW(),
updateDate = NOW(),
loginId = 'test1',
loginPw = 'test1',
name = '회원1',
nickname = '회원1',
cellphoneNum = '01043214321',
email = 'abcde@gmail.com';

(일반)

INSERT INTO member
SET regDate = NOW(),
updateDate = NOW(),
loginId = 'test2',
loginPw = 'test2',
name = '회원2',
nickname = '회원2',
cellphoneNum = '01056785678',
email = 'abcdef@gmail.com';

ALTER TABLE article ADD COLUMN memberId INT(10) UNSIGNED NOT NULL AFTER updateDate;

UPDATE article
SET memberId = 2
WHERE id IN (1,2);

UPDATE article
SET memberId = 3
WHERE id IN (3,4);

board 테이블 생성

CREATE TABLE board(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
updateDate DATETIME NOT NULL,
code CHAR(50) NOT NULL UNIQUE COMMENT 'notice(공지사항), free(자유), QnA(질의응답) ...',
name CHAR(20) NOT NULL UNIQUE COMMENT '게시판 이름',
delStatus TINYINT(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '삭제 여부 (0=삭제 전, 1=삭제 후)',
delDate DATETIME COMMENT '삭제 날짜'
);

board TD 생성

INSERT INTO board
SET regDate = NOW(),
updateDate = NOW(),
code = 'NOTICE',
name = '공지사항';

INSERT INTO board
SET regDate = NOW(),
updateDate = NOW(),
code = 'FREE',
name = '자유';

INSERT INTO board
SET regDate = NOW(),
updateDate = NOW(),
code = 'QnA',
name = '질의응답';

ALTER TABLE article ADD COLUMN boardId INT(10) UNSIGNED NOT NULL AFTER memberId;

UPDATE article
SET boardId = 1
WHERE id IN (1,2);

UPDATE article
SET boardId = 2
WHERE id = 3;

UPDATE article
SET boardId = 3
WHERE id = 4;

ALTER TABLE article ADD COLUMN hitCount INT(10) UNSIGNED NOT NULL DEFAULT 0 AFTER body;

reactionPoint 테이블 생성

CREATE TABLE reactionPoint(
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
updateDate DATETIME NOT NULL,
memberId INT(10) UNSIGNED NOT NULL,
relTypeCode CHAR(50) NOT NULL COMMENT '관련 데이터 타입 코드',
relId INT(10) NOT NULL COMMENT '관련 데이터 번호',
point INT(10) NOT NULL
);

reactionPoint 테스트 데이터 생성

1번 회원이 1번 글에 싫어요

INSERT INTO reactionPoint
SET regDate = NOW(),
updateDate = NOW(),
memberId = 1,
relTypeCode = 'article',
relId = 1,
point = -1;

1번 회원이 2번 글에 좋아요

INSERT INTO reactionPoint
SET regDate = NOW(),
updateDate = NOW(),
memberId = 1,
relTypeCode = 'article',
relId = 2,
point = 1;

2번 회원이 1번 글에 싫어요

INSERT INTO reactionPoint
SET regDate = NOW(),
updateDate = NOW(),
memberId = 2,
relTypeCode = 'article',
relId = 1,
point = -1;

2번 회원이 2번 글에 싫어요

INSERT INTO reactionPoint
SET regDate = NOW(),
updateDate = NOW(),
memberId = 2,
relTypeCode = 'article',
relId = 2,
point = -1;

3번 회원이 1번 글에 좋아요

INSERT INTO reactionPoint
SET regDate = NOW(),
updateDate = NOW(),
memberId = 3,
relTypeCode = 'article',
relId = 1,
point = 1;

article 테이블에 좋아요 관련 컬럼 추가

ALTER TABLE article ADD COLUMN goodReactionPoint INT(10) UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE article ADD COLUMN badReactionPoint INT(10) UNSIGNED NOT NULL DEFAULT 0;

update join -> 기존 게시물의 good,bad RP 값을 RP 테이블에서 가져온 데이터로 채운다

UPDATE article AS A
INNER JOIN (
SELECT RP.relTypeCode,RP.relId,
SUM(IF(RP.point > 0, RP.point, 0)) AS goodReactionPoint,
SUM(IF(RP.point < 0, RP.point * -1, 0)) AS badReactionPoint
FROM reactionPoint AS RP
GROUP BY RP.relTypeCode, RP.relId
) AS RP_SUM
ON A.id = RP_SUM.relId
SET A.goodReactionPoint = RP_SUM.goodReactionPoint,
A.badReactionPoint = RP_SUM.badReactionPoint;

reply 테이블 생성

CREATE TABLE reply (
id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
regDate DATETIME NOT NULL,
updateDate DATETIME NOT NULL,
memberId INT(10) UNSIGNED NOT NULL,
relTypeCode CHAR(50) NOT NULL COMMENT '관련 데이터 타입 코드',
relId INT(10) NOT NULL COMMENT '관련 데이터 번호',
bodyTEXT NOT NULL
);

2번 회원이 1번 글에 댓글 작성

INSERT INTO reply
SET regDate = NOW(),
updateDate = NOW(),
memberId = 2,
relTypeCode = 'article',
relId = 1,
body = '댓글 1';

2번 회원이 1번 글에 댓글 작성

INSERT INTO reply
SET regDate = NOW(),
updateDate = NOW(),
memberId = 2,
relTypeCode = 'article',
relId = 1,
body = '댓글 2';

3번 회원이 1번 글에 댓글 작성

INSERT INTO reply
SET regDate = NOW(),
updateDate = NOW(),
memberId = 3,
relTypeCode = 'article',
relId = 1,
body = '댓글 3';

3번 회원이 1번 글에 댓글 작성

INSERT INTO reply
SET regDate = NOW(),
updateDate = NOW(),
memberId = 2,
relTypeCode = 'article',
relId = 2,
body = '댓글 4';

###############################################

SELECT * FROM article;

SELECT * FROM member;

SELECT * FROM board;

SELECT * FROM reactionPoint;

SELECT * FROM reply;

SELECT goodReactionPoint
FROM article
WHERE id = 1

INSERT INTO article
(
regDate, updateDate, memberId, boardId, title, body
)
SELECT NOW(),NOW(), FLOOR(RAND() 2) + 2, FLOOR(RAND() 3) + 1, CONCAT('제목',RAND()), CONCAT('내용',RAND())
FROM article;

SELECT IFNULL(SUM(RP.point),0)
FROM reactionPoint AS RP
WHERE RP.relTypeCode = 'article'
AND RP.relId = 3
AND RP.memberId = 1;

UPDATE article
SET title = '제목5'
WHERE id = 5;

UPDATE article
SET title = '제목11'
WHERE id = 6;

UPDATE article
SET title = '제목45'
WHERE id = 7;

SELECT FLOOR(RAND() * 2) + 2

SELECT FLOOR(RAND() * 3) + 1

SHOW FULL COLUMNS FROM member;
DESC member;

SELECT LAST_INSERT_ID();

SELECT *
FROM article AS A
WHERE 1

AND boardId = 1

		AND A.title LIKE CONCAT('%','0000','%')
		OR A.body LIKE CONCAT('%','0000','%')

ORDER BY id DESC

SELECT COUNT(*)
FROM article AS A
WHERE 1
AND boardId = 1
AND A.title LIKE CONCAT('%','0000','%')
OR A.body LIKE CONCAT('%','0000','%')
ORDER BY id DESC

SELECT hitCount
FROM article
WHERE id = 374;

SELECT A.*
FROM article AS A
WHERE A.id = 1

SELECT A.*, M.nickname AS extra__writer
FROM article AS A
INNER JOIN member AS M
ON A.memberId = M.id
WHERE A.id = 1

LEFT JOIN

SELECT A.*, M.nickname AS extra__writer, RP.point
FROM article AS A
INNER JOIN member AS M
ON A.memberId = M.id
LEFT JOIN reactionPoint AS RP
ON A.id = RP.relId AND RP.relTypeCode = 'article'
group by A.id
order by A.id desc;

서브쿼리

SELECT A.,
ifnull(sum(RP.point),0) as extrasumReactionPoint,
IFNULL(SUM(if(RP.point > 0, RP.point, 0)),0) AS extragoodReactionPoint,
IFNULL(SUM(IF(RP.point < 0, RP.point, 0)),0) AS extra__badReactionPoint
FROM (
SELECT A., M.nickname As extra__writer
from article AS A
inner join member as M
on A.memberId = M.id
) As A
LEFT JOIN reactionPoint AS RP
ON A.id = RP.relId AND RP.relTypeCode = 'article'
GROUP BY A.id
ORDER BY A.id DESC;

조인

SELECT A.*, M.nickname AS extrawriter,
IFNULL(SUM(RP.point),0) AS extrasumReactionPoint,
IFNULL(SUM(IF(RP.point > 0, RP.point, 0)),0) AS extragoodReactionPoint,
IFNULL(SUM(IF(RP.point < 0, RP.point, 0)),0) AS extrabadReactionPoint
from article as A
INNER JOIN member AS M
ON A.memberId = M.id
LEFT JOIN reactionPoint AS RP
ON A.id = RP.relId AND RP.relTypeCode = 'article'
GROUP BY A.id
ORDER BY A.id DESC;

select , count()
from reactionPoint as RP
group by RP.relTypeCode,RP.relId

SELECT if(RP.point > 0, '큼','작음')
FROM reactionPoint AS RP
GROUP BY RP.relTypeCode,RP.relId

각 게시물의 좋아요, 싫어요 갯수

SELECT RP.relTypeCode, RP.relId,
sum(if(RP.point > 0,RP.point,0)) as goodReactionPoint,
SUM(IF(RP.point < 0,RP.point * -1,0)) AS badReactionPoint
FROM reactionPoint AS RP
GROUP BY RP.relTypeCode,RP.relId