테스트 데이터 환경 구축 연습

김소희·2025년 1월 12일

MySQL

목록 보기

17/17

게시판 요구사항

회원은 아이디/비밀번호로 로그인할 수 있다. 회원은 실명과 닉네임을 갖는다. 닉네임은 게시판에서 활동할 때 사용되는 이름으로 중복이 불가능하다.
게시판은 일단 공지사항, 자유게시판, 질문과 답변 총 3개를 운영할 것이며 추후 추가될 수 있다.
게시물은 번호, 제목, 내용, 작성자, 조회수, 수정일, 작성일로 이루어진다.
각 게시물은 좋아요를 체크할 수 있다. 한 회원이 한 게시물당 하나의 좋아요만 체크 가능하다. 좋아요는 해제 가능하다.
각 게시물에 여러개의 댓글을 작성할 수 있다.
한 회원이 한 게시물에 여러개의 댓글도 작성 가능하다.
게시물을 보여줄 때 각 게시물의 좋아요수, 댓글수도 보여주고 싶다.

진행중

DROP DATABASE IF EXISTS project;
CREATE DATABASE project;
USE project;

CREATE TABLE `member`(
 id INT PRIMARY KEY AUTO_INCREMENT
 , regDate DATETIME NOT NULL
 , updateDate DATETIME NOT NULL
 , loginId VARCHAR(10) NOT NULL UNIQUE
 , loginPw TEXT NOT NULL
 , `name` VARCHAR(20) NOT NULL
 , nickname VARCHAR(20) NOT NULL
);

SELECT * FROM `member`;

# 테스트데이터
INSERT INTO `member`
SET regDate = NOW()
	, updateDate = NOW()
	, loginId = 'user1'
	, loginpw = 'user1'
	, `name` = '박병찬'
	, nickname = '쿠크다스';

CREATE TABLE article (
id INT PRIMARY KEY AUTO_INCREMENT
, title VARCHAR(50) NOT NULL
, `body` TEXT NOT NULL
, regDate DATETIME NOT NULL
, updateDate DATETIME NOT NULL
, viewCount INT
);

CREATE TABLE `comment`(
id INT PRIMARY KEY AUTO_INCREMENT
, `body` TEXT NOT NULL
, regDate DATETIME NOT NULL
, updateDate DATETIME NOT NULL
);

CREATE TABLE `menu`(
id INT PRIMARY KEY AUTO_INCREMENT
, `title` VARCHAR(20) NOT NULL
, regDate DATETIME NOT NULL
, updateDate DATETIME NOT NULL
);

CREATE TABLE `comment`(
id INT PRIMARY KEY AUTO_INCREMENT
,vote BOOLEAN
);

테스트 데이터 구축 예시 코드

DROP DATABASE IF EXISTS AM;
CREATE DATABASE AM;
USE AM;

CREATE TABLE `member`(
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    updateDate DATETIME NOT NULL,
    loginId VARCHAR(50) NOT NULL UNIQUE,
    loginPw VARCHAR(100) NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    nickname VARCHAR(20) NOT NULL UNIQUE
);

INSERT INTO `member`
SET regDate = NOW(),
updateDate = NOW(),
loginId = 'user1',
loginPw = '1234',
`name` = '홍길동',
nickname = '신출귀몰';

INSERT INTO `member`
SET regDate = NOW(),
updateDate = NOW(),
loginId = 'user2',
loginPw = '4321',
`name` = '이순신',
nickname = '구국의영웅';

INSERT INTO `member`
SET regDate = NOW(),
updateDate = NOW(),
loginId = 'user3',
loginPw = '1423',
`name` = '임꺽정',
nickname = '힘센장사';

INSERT INTO `member`
SET regDate = NOW(),
updateDate = NOW(),
loginId = 'user4',
loginPw = '3131',
`name` = '유관순',
nickname = '독립열사';

CREATE TABLE board(
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(20) NOT NULL
);

INSERT INTO board
SET `name` = '공지사항';

INSERT INTO board
SET `name` = '자유게시판';

INSERT INTO board
SET `name` = '질문과 답변';

CREATE TABLE article(
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    updateDate DATETIME NOT NULL,
    title VARCHAR(200) NOT NULL,
    `body` TEXT NOT NULL,
    memberId INT UNSIGNED NOT NULL,
    viewCnt INT UNSIGNED NOT NULL DEFAULT 0
);

ALTER TABLE article ADD COLUMN boardId INT UNSIGNED NOT NULL AFTER `body`;

INSERT INTO article
SET regDate = '2020-03-03 12:30:00',
updateDate = '2020-03-03 12:30:00',
title = '제목1',
`body` = '내용1',
boardId = 2,
memberId = 1,
viewCnt = 10;

INSERT INTO article
SET regDate = '2021-04-05 15:12:00',
updateDate = '2021-04-05 15:12:00',
title = '제목2',
`body` = '내용2',
boardId = 2,
memberId = 1,
viewCnt = 23;

INSERT INTO article
SET regDate = '2022-01-02 19:08:00',
updateDate = '2022-01-02 19:08:00',
title = '제목3',
`body` = '내용3',
boardId = 2,
memberId = 1,
viewCnt = 31;

INSERT INTO article
SET regDate = '2021-04-01 08:30:00',
updateDate = '2021-04-01 08:30:00',
title = '제목4',
`body` = '내용5',
boardId = 2,
memberId = 2,
viewCnt = 100;

INSERT INTO article
SET regDate = '2021-05-07 16:22:00',
updateDate = '2021-05-07 16:22:00',
title = '제목5',
`body` = '내용5',
boardId = 2,
memberId = 2,
viewCnt = 13;

INSERT INTO article
SET regDate = '2020-03-03 12:30:00',
updateDate = '2020-03-03 12:30:00',
title = '제목6',
`body` = '내용6',
boardId = 1,
memberId = 2,
viewCnt = 112;

INSERT INTO article
SET regDate = '2021-06-01 17:12:00',
updateDate = '2021-06-01 17:12:00',
title = '제목7',
`body` = '내용7',
boardId = 1,
memberId = 2,
viewCnt = 224;

INSERT INTO article
SET regDate = '2020-07-01 22:30:00',
updateDate = '2020-07-01 22:30:00',
title = '제목8',
`body` = '내용8',
boardId = 3,
memberId = 3,
viewCnt = 87;

INSERT INTO article
SET regDate = '2022-03-03 23:12:00',
updateDate = '2022-03-03 23:12:00',
title = '제목9',
`body` = '내용9',
boardId = 3,
memberId = 3,
viewCnt = 56;

INSERT INTO article
SET regDate = '2022-04-01 20:00:00',
updateDate = '2022-04-01 20:00:00',
title = '제목10',
`body` = '내용10',
boardId = 3,
memberId = 2,
viewCnt = 3;

INSERT INTO article
SET regDate = '2022-03-11 20:00:00',
updateDate = '2022-03-11 20:00:00',
title = '제목11',
`body` = '내용11',
boardId = 3,
memberId = 4,
viewCnt = 12;

INSERT INTO article
SET regDate = '2022-02-13 20:00:00',
updateDate = '2022-02-13 20:00:00',
title = '제목12',
`body` = '내용12',
boardId = 2,
memberId = 4,
viewCnt = 32;

CREATE TABLE goodPoint(
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    relTypeCode VARCHAR(20) NOT NULL,
    relId INT UNSIGNED NOT NULL,
    memberId INT UNSIGNED NOT NULL,
    `point` TINYINT UNSIGNED NOT NULL COMMENT '1 : 좋아요'
);

INSERT INTO goodPoint
SET relTypeCode = 'article',
relId = 4,
memberId = 2,
`point` = 1;

INSERT INTO goodPoint
SET relTypeCode = 'article',
relId = 1,
memberId = 1,
`point` = 1;

INSERT INTO goodPoint
SET relTypeCode = 'article',
relId = 1,
memberId = 4,
`point` = 1;

INSERT INTO goodPoint
SET relTypeCode = 'article',
relId = 7,
memberId = 1,
`point` = 1;

INSERT INTO goodPoint
SET relTypeCode = 'article',
relId = 7,
memberId = 4,
`point` = 1;

INSERT INTO goodPoint
SET relTypeCode = 'article',
relId = 10,
memberId = 2,
`point` = 1;

INSERT INTO goodPoint
SET relTypeCode = 'article',
relId = 10,
memberId = 1,
`point` = 1;

INSERT INTO goodPoint
SET relTypeCode = 'article',
relId = 10,
memberId = 4,
`point` = 1;

INSERT INTO goodPoint
SET relTypeCode = 'article',
relId = 10,
memberId = 3,
`point` = 1;

INSERT INTO goodPoint
SET relTypeCode = 'article',
relId = 6,
memberId = 4,
`point` = 1;

INSERT INTO goodPoint
SET relTypeCode = 'article',
relId = 3,
memberId = 4,
`point` = 1;

CREATE TABLE reply(
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    updateDate DATETIME NOT NULL,
    relTypeCode VARCHAR(20) NOT NULL,
    relId INT UNSIGNED NOT NULL,
    memberId INT UNSIGNED NOT NULL,
    `body` VARCHAR(200) NOT NULL
);

INSERT INTO reply
SET regDate = '2021-04-05 15:12:00',
updateDate = '2021-04-05 15:12:00',
relTypeCode = 'article',
relId = 1,
memberId = 3,
`body` = '댓글내용1';

INSERT INTO reply
SET regDate = '2021-05-02 19:08:00',
updateDate = '2021-05-02 19:08:00',
relTypeCode = 'article',
relId = 1,
memberId = 3,
`body` = '댓글내용2';

INSERT INTO reply
SET regDate = '2020-07-05 13:32:00',
updateDate = '2020-07-05 13:32:00',
relTypeCode = 'article',
relId = 8,
memberId = 3,
`body` = '댓글내용9';

INSERT INTO reply
SET regDate = '2020-07-07 11:15:00',
updateDate = '2020-07-07 11:15:00',
relTypeCode = 'article',
relId = 8,
memberId = 3,
`body` = '댓글내용10';

INSERT INTO reply
SET regDate = '2022-06-05 15:22:00',
updateDate = '2020-06-05 15:22:00',
relTypeCode = 'article',
relId = 9,
memberId = 3,
`body` = '댓글내용11';

INSERT INTO reply
SET regDate = '2022-07-04 17:14:00',
updateDate = '2022-07-04 17:14:00',
relTypeCode = 'article',
relId = 9,
memberId = 3,
`body` = '댓글내용12';

INSERT INTO reply
SET regDate = '2022-03-05 17:02:00',
updateDate = '2022-03-05 17:02:00',
relTypeCode = 'article',
relId = 9,
memberId = 2,
`body` = '댓글내용13';

INSERT INTO reply
SET regDate = '2022-03-07 12:32:00',
updateDate = '2022-03-07 12:32:00',
relTypeCode = 'article',
relId = 9,
memberId = 2,
`body` = '댓글내용14';

INSERT INTO reply
SET regDate = '2022-03-07 16:15:00',
updateDate = '2022-03-07 16:15:00',
relTypeCode = 'article',
relId = 9,
memberId = 2,
`body` = '댓글내용15';

INSERT INTO reply
SET regDate = '2020-03-03 12:30:00',
updateDate = '2020-03-03 12:30:00',
relTypeCode = 'article',
relId = 6,
memberId = 1,
`body` = '댓글내용3';

INSERT INTO reply
SET regDate = '2020-03-05 15:12:00',
updateDate = '2020-03-05 15:12:00',
relTypeCode = 'article',
relId = 6,
memberId = 1,
`body` = '댓글내용4';

INSERT INTO reply
SET regDate = '2020-03-12 19:08:00',
updateDate = '2020-03-12 19:08:00',
relTypeCode = 'article',
relId = 6,
memberId = 1,
`body` = '댓글내용5';

INSERT INTO reply
SET regDate = '2021-06-03 12:30:00',
updateDate = '2021-06-03 12:30:00',
relTypeCode = 'article',
relId = 7,
memberId = 1,
`body` = '댓글내용6';

INSERT INTO reply
SET regDate = '2021-06-05 15:12:00',
updateDate = '2021-06-05 15:12:00',
relTypeCode = 'article',
relId = 7,
memberId = 1,
`body` = '댓글내용7';

INSERT INTO reply
SET regDate = '2022-04-01 19:08:00',
updateDate = '2022-04-01 19:08:00',
relTypeCode = 'article',
relId = 7,
memberId = 1,
`body` = '댓글내용8';

SELECT * FROM `member`;
SELECT * FROM board;
SELECT * FROM article;
SELECT * FROM goodPoint;
SELECT * FROM reply

김소희

이전 포스트

테스트 데이터 환경 구축 연습

MySQL

게시판 요구사항

진행중

테스트 데이터 구축 예시 코드

서브쿼리 기본 문제 풀이

0개의 댓글