use mysql

SELECT Host,User,plugin,authentication_string FROM mysql.user;

create user 'kosta'@'%' identified by '1234';

create database dodream;

grant all privileges on *.* to 'kosta'@'%';

flush privileges;

SELECT Host,User,plugin,authentication_string FROM mysql.user; 

use dodream;
SQL
복사

CREATE TABLE `user` (
	`id`	bigint	NOT NULL	COMMENT 'Comment',
	`username`	varchar(50)	NOT NULL	COMMENT '사용자 이름 (소셜에 가입된 이름)',
	`profile_image`	varchar(5000)	NULL	COMMENT '프로필 이미지',
	`provider`	varchar(10)	NOT NULL	COMMENT '소셜 로그인 제공자 (google, kakao, naver)',
	`provider_id`	varchar(5000)	NOT NULL	COMMENT '소셜 로그인 제공자로부터 받은 고유 ID',
	`created_at`	DATETIME	NOT NULL	COMMENT '가입한 날짜',
	`updated_at`	DATETIME	NOT NULL	COMMENT '수정할 날짜 (프로필 수정, 회원 탈퇴)'
);

CREATE TABLE `book` (
	`id`	bigint	NOT NULL	COMMENT 'Comment',
	`title`	varchar(20)	NOT NULL	COMMENT '문제집 제목',
	`category`	varchar(20)	NOT NULL	COMMENT '문제집 카테고리 (CS, 자격증, 기타)',
	`secret`	boolean	NOT NULL	COMMENT '공개/비공개 여부',
	`user_id`	bigint	NOT NULL	COMMENT '문제집 생성한 사용자 ID',
	`created_at`	DATETIME	NOT NULL	COMMENT '문제집 생성 날짜',
	`updated_at`	DATETIME	NOT NULL	COMMENT '문제집 수정 날짜'
);

CREATE TABLE `study` (
	`id`	bigint	NOT NULL	COMMENT 'Comment',
	`user_id`	bigint	NOT NULL	COMMENT '스터디 생성한 사용자 ID',
	`title`	varchar(255)	NOT NULL	COMMENT '스터디 제목',
	`category`	varchar(20)	NOT NULL	COMMENT '스터디 카테고리',
	`description`	varchar(30)	NOT NULL	COMMENT '스터디 간단 설명',
	`created_at`	DATETIME	NOT NULL	COMMENT '스터디 생성 날짜',
	`updated_at`	DATETIME	NOT NULL	COMMENT '스터디 수정 날짜'
);

CREATE TABLE `question` (
	`id`	bigint	NOT NULL	COMMENT 'Comment',
	`question`	varchar(255)	NOT NULL	COMMENT '문제 질문',
	`model_answer`	varchar(1000)	NOT NULL	COMMENT '작성자가 작성한 문제 모범 답안',
	`book_id`	bigint	NOT NULL	COMMENT '문제가 포함된 문제집 ID',
	`created_at`	DATETIME	NOT NULL	COMMENT '문제 생성 날짜',
	`updated_at`	DATETIME	NOT NULL	COMMENT '문제 수정 날짜'
);

CREATE TABLE `user_answer` (
	`id`	bigint	NOT NULL	COMMENT 'Comment',
	`evaluation`	varchar(20)	NOT NULL	COMMENT '사용자의 문제 평가 (이해완료, 애매해요, 모르겠어요)',
	`answer`	varchar(1000)	NULL	COMMENT '사용자가 작성한 문제 답안',
	`question_id`	bigint	NOT NULL	COMMENT '사용자가 푼 문제 ID',
	`user_id`	bigint	NOT NULL	COMMENT '문제를 푼 사용자 ID',
	`created_at`	DATETIME	NOT NULL	COMMENT '문제를 푼 날짜'
);

CREATE TABLE `study_member` (
	`id`	bigint	NOT NULL	COMMENT 'Comment',
	`user_id`	bigint	NOT NULL	COMMENT '스터디 참여한 멤버 ID',
	`study_id`	bigint	NOT NULL	COMMENT '참여한 스터디 ID',
	`join_date`	DATETIME	NULL	COMMENT '스터디 가입 날짜',
	`role`	varchar(20)	NOT NULL	COMMENT '스터디 멤버 역할 (멤버, 리더,  대기)'
);

CREATE TABLE `bookmark` (
	`id`	bigint	NOT NULL	COMMENT 'Comment',
	`is_deleted`	boolean	NOT NULL	COMMENT '북마크 삭제 여부',
	`user_id`	bigint	NOT NULL	COMMENT '북마크한 사용자 ID',
	`book_id`	bigint	NOT NULL	COMMENT '북마크한 문제집 ID'
);

CREATE TABLE `book_comment` (
	`id`	bigint	NOT NULL	COMMENT 'Comment',
	`comment`	varchar(255)	NOT NULL	COMMENT '댓글 내용',
	`created_at`	DATETIME	NOT NULL	COMMENT '댓글 작성 날짜',
	`updated_at`	DATETIME	NOT NULL	COMMENT '댓글 수정 날짜',
	`book_id`	bigint	NOT NULL	COMMENT '댓글이 달린 문제집 ID',
	`user_id`	bigint	NOT NULL	COMMENT '댓글을 작성한 사용자 ID'
);

CREATE TABLE `study_notice` (
	`id`	bigint	NOT NULL	COMMENT 'Comment',
	`study_id`	bigint	NOT NULL	COMMENT '공지사항이 있는 스터디 ID',
	`content`	varchar(255)	NOT NULL	COMMENT '공지사항 내용',
	`created_at`	DATETIME	NOT NULL	COMMENT '공지사항 생성 날짜',
	`updated_at`	DATETIME	NOT NULL	COMMENT '공지사항 수정 날짜'
);

CREATE TABLE `notice_comment` (
	`id`	Bigint	NOT NULL	COMMENT 'Comment',
	`notice_id`	bigint	NOT NULL	COMMENT '댓글이 있는 공지사항 ID',
	`user_id`	bigint	NOT NULL	COMMENT '댓글을 작성한 사용자 ID',
	`content`	varchar(255)	NOT NULL	COMMENT '댓글 내용',
	`created_at`	DATETIME	NOT NULL	COMMENT '댓글 생성 날짜',
	`updated_at`	DATETIME	NOT NULL	COMMENT '댓글 수정 날짜'
);

CREATE TABLE `book_comment_like` (
	`id`	bigint	NOT NULL	COMMENT 'Comment',
	`is_deleted`	boolean	NOT NULL	COMMENT '좋아요 취소(삭제) 여부',
	`comment_id`	bigint	NOT NULL	COMMENT '좋아요한 댓글 ID',
	`user_id`	bigint	NOT NULL	COMMENT '좋아요 누른 작성자 ID'
);

CREATE TABLE `user_book` (
	`id`	bigint	NOT NULL	COMMENT 'Comment',
	`user_id`	bigint	NOT NULL	COMMENT '사용자의 ID',
	`book_id`	bigint	NOT NULL	COMMENT '문제집 ID',
	`question_id`	bigint	NOT NULL	COMMENT '문제 ID'
);

CREATE TABLE `question_comment` (
	`id`	bigint	NOT NULL	COMMENT 'Comment',
	`user_id`	bigint	NOT NULL	COMMENT '댓글을 작성한 사용자 ID',
	`question_id`	bigint	NOT NULL	COMMENT '스터디에 추가한 문제 ID',
	`content`	varchar(255)	NOT NULL	COMMENT '댓글 내용',
	`created_at`	DATETIME	NOT NULL	COMMENT '댓글 생성 날짜',
	`updated_at`	DATETIME	NOT NULL	COMMENT '댓글 수정 날짜'
);

CREATE TABLE `study_user_answer` (
	`id`	bigint	NOT NULL	COMMENT 'Comment',
	`question_id`	bigint	NOT NULL	COMMENT '사용자가 푼 문제 ID',
	`study_id`	bigint	NOT NULL	COMMENT '사용자의 답편+평가를 추가할 스터디 ID',
	`created_at`	DATETIME	NOT NULL	COMMENT '문제가 추가된 날짜'
);

CREATE TABLE `question_comment_like` (
	`id`	bigint	NOT NULL	COMMENT 'Comment',
	`is_deleted`	boolean	NOT NULL	COMMENT '좋아요 취소(삭제) 여부',
	`comment_id`	bigint	NOT NULL	COMMENT '좋아요한 댓글 ID',
	`user_id`	bigint	NOT NULL	COMMENT '좋아요 누른 작성자 ID'
);

CREATE TABLE `notice_comment_like` (
	`id`	bigint	NOT NULL	COMMENT 'Comment',
	`is_deleted`	boolean	NOT NULL	COMMENT '좋아요 취소(삭제) 여부',
	`comment_id`	bigint	NOT NULL	COMMENT '좋아요한 댓글 ID',
	`user_id`	bigint	NOT NULL	COMMENT '좋아요 누른 작성자 ID'
);

CREATE TABLE `notification` (
	`id`	bigint	NOT NULL	COMMENT 'Comment',
	`user_id`	bigint	NOT NULL	COMMENT '알림을 받을 유저 ID',
	`book_comment_id`	bigint	NULL	COMMENT '문제집에 달린 댓글  ID',
	`question_comment_id`	bigint	NULL	COMMENT '문제에 달린 댓글 ID',
	`study_user_id`	bigint	NULL	COMMENT '스터디 방장 탈퇴/변경 ID',
	`study_id`	bigint	NULL	COMMENT '스터디 ID',
	`type`	varchar(20)	NOT NULL	COMMENT '여러가지 알림종류 ...',
	`is_read`	boolean	NOT NULL	COMMENT '알림 읽음 여부',
	`created_at`	DATETIME	NOT NULL	COMMENT '알림이 온 날짜'
);

ALTER TABLE `user` ADD CONSTRAINT `PK_USER` PRIMARY KEY (
	`id`
);

ALTER TABLE `book` ADD CONSTRAINT `PK_BOOK` PRIMARY KEY (
	`id`
);

ALTER TABLE `study` ADD CONSTRAINT `PK_STUDY` PRIMARY KEY (
	`id`
);

ALTER TABLE `question` ADD CONSTRAINT `PK_QUESTION` PRIMARY KEY (
	`id`
);

ALTER TABLE `user_answer` ADD CONSTRAINT `PK_USER_ANSWER` PRIMARY KEY (
	`id`
);

ALTER TABLE `study_member` ADD CONSTRAINT `PK_STUDY_MEMBER` PRIMARY KEY (
	`id`
);

ALTER TABLE `bookmark` ADD CONSTRAINT `PK_BOOKMARK` PRIMARY KEY (
	`id`
);

ALTER TABLE `book_comment` ADD CONSTRAINT `PK_BOOK_COMMENT` PRIMARY KEY (
	`id`
);

ALTER TABLE `study_notice` ADD CONSTRAINT `PK_STUDY_NOTICE` PRIMARY KEY (
	`id`
);

ALTER TABLE `notice_comment` ADD CONSTRAINT `PK_NOTICE_COMMENT` PRIMARY KEY (
	`id`
);

ALTER TABLE `book_comment_like` ADD CONSTRAINT `PK_BOOK_COMMENT_LIKE` PRIMARY KEY (
	`id`
);

ALTER TABLE `user_book` ADD CONSTRAINT `PK_USER_BOOK` PRIMARY KEY (
	`id`
);

ALTER TABLE `question_comment` ADD CONSTRAINT `PK_QUESTION_COMMENT` PRIMARY KEY (
	`id`
);

ALTER TABLE `study_user_answer` ADD CONSTRAINT `PK_STUDY_USER_ANSWER` PRIMARY KEY (
	`id`
);

ALTER TABLE `question_comment_like` ADD CONSTRAINT `PK_QUESTION_COMMENT_LIKE` PRIMARY KEY (
	`id`
);

ALTER TABLE `notice_comment_like` ADD CONSTRAINT `PK_NOTICE_COMMENT_LIKE` PRIMARY KEY (
	`id`
);

ALTER TABLE `notification` ADD CONSTRAINT `PK_NOTIFICATION` PRIMARY KEY (
	`id`
);

ALTER TABLE `book` ADD CONSTRAINT `FK_user_TO_book_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `user` (
	`id`
);

ALTER TABLE `study` ADD CONSTRAINT `FK_user_TO_study_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `user` (
	`id`
);

ALTER TABLE `question` ADD CONSTRAINT `FK_book_TO_question_1` FOREIGN KEY (
	`book_id`
)
REFERENCES `book` (
	`id`
);

ALTER TABLE `user_answer` ADD CONSTRAINT `FK_question_TO_user_answer_1` FOREIGN KEY (
	`question_id`
)
REFERENCES `question` (
	`id`
);

ALTER TABLE `user_answer` ADD CONSTRAINT `FK_user_TO_user_answer_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `user` (
	`id`
);

ALTER TABLE `study_member` ADD CONSTRAINT `FK_user_TO_study_member_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `user` (
	`id`
);

ALTER TABLE `study_member` ADD CONSTRAINT `FK_study_TO_study_member_1` FOREIGN KEY (
	`study_id`
)
REFERENCES `study` (
	`id`
);

ALTER TABLE `bookmark` ADD CONSTRAINT `FK_user_TO_bookmark_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `user` (
	`id`
);

ALTER TABLE `bookmark` ADD CONSTRAINT `FK_book_TO_bookmark_1` FOREIGN KEY (
	`book_id`
)
REFERENCES `book` (
	`id`
);

ALTER TABLE `book_comment` ADD CONSTRAINT `FK_book_TO_book_comment_1` FOREIGN KEY (
	`book_id`
)
REFERENCES `book` (
	`id`
);

ALTER TABLE `book_comment` ADD CONSTRAINT `FK_user_TO_book_comment_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `user` (
	`id`
);

ALTER TABLE `study_notice` ADD CONSTRAINT `FK_study_TO_study_notice_1` FOREIGN KEY (
	`study_id`
)
REFERENCES `study` (
	`id`
);

ALTER TABLE `notice_comment` ADD CONSTRAINT `FK_study_notice_TO_notice_comment_1` FOREIGN KEY (
	`notice_id`
)
REFERENCES `study_notice` (
	`id`
);

ALTER TABLE `notice_comment` ADD CONSTRAINT `FK_user_TO_notice_comment_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `user` (
	`id`
);

ALTER TABLE `book_comment_like` ADD CONSTRAINT `FK_book_comment_TO_book_comment_like_1` FOREIGN KEY (
	`comment_id`
)
REFERENCES `book_comment` (
	`id`
);

ALTER TABLE `book_comment_like` ADD CONSTRAINT `FK_user_TO_book_comment_like_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `user` (
	`id`
);

ALTER TABLE `user_book` ADD CONSTRAINT `FK_user_TO_user_book_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `user` (
	`id`
);

ALTER TABLE `user_book` ADD CONSTRAINT `FK_book_TO_user_book_1` FOREIGN KEY (
	`book_id`
)
REFERENCES `book` (
	`id`
);

ALTER TABLE `user_book` ADD CONSTRAINT `FK_question_TO_user_book_1` FOREIGN KEY (
	`question_id`
)
REFERENCES `question` (
	`id`
);

ALTER TABLE `question_comment` ADD CONSTRAINT `FK_user_TO_question_comment_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `user` (
	`id`
);

ALTER TABLE `question_comment` ADD CONSTRAINT `FK_study_user_answer_TO_question_comment_1` FOREIGN KEY (
	`question_id`
)
REFERENCES `study_user_answer` (
	`id`
);

ALTER TABLE `study_user_answer` ADD CONSTRAINT `FK_user_answer_TO_study_user_answer_1` FOREIGN KEY (
	`question_id`
)
REFERENCES `user_answer` (
	`id`
);

ALTER TABLE `study_user_answer` ADD CONSTRAINT `FK_study_TO_study_user_answer_1` FOREIGN KEY (
	`study_id`
)
REFERENCES `study` (
	`id`
);

ALTER TABLE `question_comment_like` ADD CONSTRAINT `FK_question_comment_TO_question_comment_like_1` FOREIGN KEY (
	`comment_id`
)
REFERENCES `question_comment` (
	`id`
);

ALTER TABLE `question_comment_like` ADD CONSTRAINT `FK_user_TO_question_comment_like_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `user` (
	`id`
);

ALTER TABLE `notice_comment_like` ADD CONSTRAINT `FK_notice_comment_TO_notice_comment_like_1` FOREIGN KEY (
	`comment_id`
)
REFERENCES `notice_comment` (
	`id`
);

ALTER TABLE `notice_comment_like` ADD CONSTRAINT `FK_user_TO_notice_comment_like_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `user` (
	`id`
);

ALTER TABLE `notification` ADD CONSTRAINT `FK_user_TO_notification_1` FOREIGN KEY (
	`user_id`
)
REFERENCES `user` (
	`id`
);

ALTER TABLE `notification` ADD CONSTRAINT `FK_book_comment_TO_notification_1` FOREIGN KEY (
	`book_comment_id`
)
REFERENCES `book_comment` (
	`id`
);

ALTER TABLE `notification` ADD CONSTRAINT `FK_question_comment_TO_notification_1` FOREIGN KEY (
	`question_comment_id`
)
REFERENCES `question_comment` (
	`id`
);

ALTER TABLE `notification` ADD CONSTRAINT `FK_study_member_TO_notification_1` FOREIGN KEY (
	`study_user_id`
)
REFERENCES `study_member` (
	`id`
);

ALTER TABLE `notification` ADD CONSTRAINT `FK_study_TO_notification_1` FOREIGN KEY (
	`study_id`
)
REFERENCES `study` (
	`id`
);

SQL
복사