기타

실서비스 프로젝트 DB ERD와 입력자료 예시


### DB ERD

CREATE TABLE Users(
    userId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    phone varchar(100) NOT NULL UNIQUE,
    name varchar(50) NOT NULL,
    nickname varchar(50) NOT NULL UNIQUE,
    password varchar(255) NOT NULL,
    profileImg varchar(255),
    statusMessage varchar(255),
    rating int(11) NOT NULL,
    createdAt datetime NOT NULL DEFAULT NOW(),
    updatedAt datetime NOT NULL DEFAULT NOW()
);

CREATE TABLE Likes(
    likeId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userId int(11) NOT NULL,
    likeItem varchar(30) NOT NULL,
    createdAt datetime NOT NULL DEFAULT NOW(),
    updatedAt datetime NOT NULL DEFAULT NOW(),
    FOREIGN KEY (userId) REFERENCES Users(userId) ON DELETE CASCADE
);


CREATE TABLE Posts(
    postId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userId int(11) NOT NULL,
    title varchar(255) NOT NULL,
    postImg varchar(255),
    content varchar(1000),
    maxMember TINYINT UNSIGNED NOT NULL,
    startDate datetime NOT NULL,
    endDate datetime NOT NULL,
    location POINT,
    place varchar(255),
    bring varchar(255),
    createdAt datetime NOT NULL DEFAULT NOW(),
    updatedAt datetime NOT NULL DEFAULT NOW(),
    FOREIGN KEY (userId) REFERENCES Users(userId) ON DELETE CASCADE
);


CREATE TABLE Tags(
    tagId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    postId int(11) NOT NULL,
    tag varchar(30) NOT NULL,
    createdAt datetime NOT NULL DEFAULT NOW(),
    updatedAt datetime NOT NULL DEFAULT NOW(),
    FOREIGN KEY (postId) REFERENCES Posts(postId) ON DELETE CASCADE
);

CREATE TABLE Channels(
    channelId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    postId int(11) NOT NULL,
    userId int(11) NOT NULL,
    confirm TINYINT UNSIGNED NOT NULL DEFAULT 0,
    createdAt datetime NOT NULL DEFAULT NOW(),
    updatedAt datetime NOT NULL DEFAULT NOW(),
    FOREIGN KEY (postId) REFERENCES Posts(postId) ON DELETE CASCADE,
    FOREIGN KEY (userId) REFERENCES Users(userId) ON DELETE CASCADE
);

CREATE TABLE Messages(
    messageId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    postId int(11) NOT NULL,
    userId int(11),
    message varchar(255) NOT NULL,
    createdAt datetime NOT NULL DEFAULT NOW(),
    updatedAt datetime NOT NULL DEFAULT NOW(),
    FOREIGN KEY (postId) REFERENCES Posts(postId) ON DELETE CASCADE,
    FOREIGN KEY (userId) REFERENCES Users(userId) ON DELETE SET NULL
);

CREATE TABLE Friends(
    friendId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    giveUserId int(11) NOT NULL,
    receiveUserId int(11) NOT NULL,
    createdAt datetime NOT NULL DEFAULT NOW(),
    updatedAt datetime NOT NULL DEFAULT NOW(),
    FOREIGN KEY (giveUserId) REFERENCES Users(userId) ON DELETE CASCADE,
    FOREIGN KEY (receiveUserId) REFERENCES Users(userId) ON DELETE CASCADE
);

CREATE TABLE Auths(
    authId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    phone varchar(100) NOT NULL UNIQUE,
    authData varchar(255) NOT NULL,
    isAuth tinyint NOT NULL DEFAULT 0,
    createdAt datetime NOT NULL DEFAULT NOW(),
    updatedAt datetime NOT NULL DEFAULT NOW()
);

CREATE TABLE Invites(
    inviteId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    giveUserId int(11) NOT NULL,
    receiveUserId int(11) NOT NULL,
    postId int(11) NOT NULL,
    createdAt datetime NOT NULL DEFAULT NOW(),
    updatedAt datetime NOT NULL DEFAULT NOW(),
    FOREIGN KEY (giveUserId) REFERENCES Users(userId) ON DELETE CASCADE,
    FOREIGN KEY (receiveUserId) REFERENCES Users(userId) ON DELETE CASCADE,
    FOREIGN KEY (postId) REFERENCES Posts(postId) ON DELETE CASCADE
);


CREATE TABLE BlackLists(
    blackListId int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    userId int(11) NOT NULL,
    blackUserId int(11) NOT NULL,
    createdAt datetime NOT NULL DEFAULT NOW(),
    updatedAt datetime NOT NULL DEFAULT NOW(),
    FOREIGN KEY (userId) REFERENCES Users(userId) ON DELETE CASCADE,
    FOREIGN KEY (blackUserId) REFERENCES Users(userId) ON DELETE CASCADE
);



### 트리거 생성

#Users 테이블 변경시 Likes의 동일한 userId 데이터들을 전부 삭제하는 트리거

DROP TRIGGER IF EXISTS TR_Users ;

DELIMITER $$
CREATE TRIGGER TR_Users
AFTER UPDATE ON Users
FOR EACH ROW
BEGIN
    DELETE FROM Likes WHERE userId = OLD.userId;
END $$
DELIMITER ;



# Posts 테이블 변경시 Tags의 동일한 postId 데이터들을 전부 삭제하는 트리거

DROP TRIGGER IF EXISTS TR_Posts_UPDATE ;

DELIMITER $$
CREATE TRIGGER TR_Posts_UPDATE
AFTER UPDATE ON Posts
FOR EACH ROW
BEGIN
    DELETE FROM Tags WHERE postId = OLD.postId;
END $$

DELIMITER ;



# Posts 테이블에 데이터 생성시 Channels에 생성한 userId를 삽입하는 트리거

DROP TRIGGER IF EXISTS TR_Posts_INSERT ;

DELIMITER $$
CREATE TRIGGER TR_Posts_INSERT
AFTER INSERT ON Posts
FOR EACH ROW
BEGIN
    INSERT INTO Channels (postId, userId) VALUES (NEW.postId, NEW.userId);
END $$

DELIMITER ;



### PROCEDURE 생성

DROP PROCEDURE IF EXISTS SP_Auths_INSERT ; 

DELIMITER $$
CREATE PROCEDURE SP_Auths_INSERT
(
 IN auth_phone varchar(100),
 IN auth_authData varchar(255) 
)
BEGIN
    IF EXISTS (SELECT phone FROM Auths WHERE phone = auth_phone) THEN
        UPDATE Auths SET authData = auth_authData, updatedAt = NOW()
        WHERE phone = auth_phone;
    ELSE 
        INSERT INTO Auths (phone, authData) VALUES (auth_phone, auth_authData);
    END IF;
    
END $$

DELIMITER ;
# Auths 메일 인증 테이블에 데이터를 생성 및 갱신하는 프로시저


# CALL SP_Auths_INSERT("01049119038", "3333");
# 프로시저를 사용하는 명령어 


### VIEW 

DROP VIEW IF EXISTS POSTS_VW ; 

CREATE VIEW POSTS_VW
AS
    SELECT p.postId, p.userId, p.title, p.postImg, 
        (SELECT COUNT(confirm) FROM Channels WHERE postId = p.postId AND confirm = 1) AS confirmCount,
        COUNT(*) AS currentMember, p.maxMember, p.startDate, p.endDate, p.place,
        ST_Y(location) AS lat, ST_X(location) AS lng,
        (SELECT GROUP_CONCAT(tag ORDER BY tag ASC SEPARATOR ', ') FROM Tags WHERE postId = p.postId GROUP BY postId) AS tagItem
    FROM Channels  AS c
    JOIN Posts AS p
    ON p.postId = c.postId
    GROUP BY c.postId
;


# Posts의 모든 데이터를 보는 View


### Event Scheduler 생성


SET GLOBAL event_scheduler = ON;
# Event Scheduler를 허용하는 설정


CREATE EVENT ES_DELETE_Posts_endDate
    ON SCHEDULE EVERY 1 HOUR
    STARTS '2021-01-01 00:00:00'
DO
    DELETE FROM FinalProject.Posts WHERE endDate <= NOW();
# 1시간마다 endDate가 지난 모임을 삭제하는 Event Scheduler


### DB 임시 데이터 생성

# 비밀번호 !@#4qwer
# DB Users 생성 

# INSERT INTO Users (phone, name, nickname, password, profileImg) values
# ("dddd@naver.com","Chris John", "KKK", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
#     NULL);
# INSERT INTO Users (phone, name, nickname, password, profileImg, rating, createdAt, updatedAt) values
# ("qwer@naver.com","Riot", "Riot", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
#     NULL, 70, NOW(), NOW() );
# INSERT INTO Users (phone, name, nickname, password, profileImg, rating, createdAt, updatedAt) values
# ("qqe22@naver.com","karis", "Karin", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
#     NULL, 70, NOW(), NOW() );
# INSERT INTO Users (phone, name, nickname, password, profileImg, rating, createdAt, updatedAt) values
# ("ggenter@naver.com","Zero Two", "Kuran", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
#     NULL, 70, NOW(), NOW() );
# INSERT INTO Users (phone, name, nickname, password, profileImg, rating, createdAt, updatedAt) values
# ("kkkk@naver.com","Metro", "Sawadicap", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
#     NULL, 70, NOW(), NOW() );
# INSERT INTO Users (phone, name, nickname, password, profileImg, rating, createdAt, updatedAt) values
# ("qqwwee@naver.com","qqwwee", "qqwwee", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
#     "https://wikibook.co.kr/images/cover/s/9791158392376.jpg", 70, NOW(), NOW() );
# INSERT INTO Users (phone, name, nickname, password, profileImg, statusMessage, rating, createdAt, updatedAt) values
# ("qqwwe@naver.com","qqwwe", "qqwwe", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
#     "https://wikibook.co.kr/images/cover/s/9791158392376.jpg", "Hello Status Message", 70, NOW(), NOW() );
# INSERT INTO Users (phone, name, nickname, password, profileImg, rating, createdAt, updatedAt) values
# ("newhi@naver.com","newhi", "newhi", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
#     "https://wikibook.co.kr/images/cover/s/9791158392376.jpg", 70, NOW(), NOW() );
# INSERT INTO Users (phone, name, nickname, password, profileImg, statusMessage, rating, createdAt, updatedAt) values
# ("newhihi@naver.com","newhihi", "newhihi", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
#     "https://wikibook.co.kr/images/cover/s/9791158392376.jpg", "new HIHI Status Message", 70, NOW(), NOW() );
# INSERT INTO Users (phone, name, nickname, password, profileImg, statusMessage, rating, createdAt, updatedAt) values
# ("archepro84@gmail.com","YoungWoo", "Archpro", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
#     "", "Play to the Arche", 70, NOW(), NOW() );


INSERT INTO Users (phone, name, nickname, password, profileImg) values
("01011111111","Chris John", "KKK", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
    NULL);
INSERT INTO Users (phone, name, nickname, password, profileImg, rating, createdAt, updatedAt) values
("01022222222","Riot", "Riot", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
    NULL, 70, NOW(), NOW() );
INSERT INTO Users (phone, name, nickname, password, profileImg, rating, createdAt, updatedAt) values
("01033333333","karis", "Karin", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
    NULL, 70, NOW(), NOW() );
INSERT INTO Users (phone, name, nickname, password, profileImg, rating, createdAt, updatedAt) values
("01044444444","Zero Two", "Kuran", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
    NULL, 70, NOW(), NOW() );
INSERT INTO Users (phone, name, nickname, password, profileImg, rating, createdAt, updatedAt) values
("01055555555","Metro", "Sawadicap", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
    NULL, 70, NOW(), NOW() );
INSERT INTO Users (phone, name, nickname, password, profileImg, rating, createdAt, updatedAt) values
("01066666666","qqwwee", "qqwwee", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
    "https://wikibook.co.kr/images/cover/s/9791158392376.jpg", 70, NOW(), NOW() );
INSERT INTO Users (phone, name, nickname, password, profileImg, statusMessage, rating, createdAt, updatedAt) values
("01077777777","qqwwe", "qqwwe", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
    "https://wikibook.co.kr/images/cover/s/9791158392376.jpg", "Hello Status Message", 70, NOW(), NOW() );
INSERT INTO Users (phone, name, nickname, password, profileImg, rating, createdAt, updatedAt) values
("01088888888","newhi", "newhi", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
    "https://wikibook.co.kr/images/cover/s/9791158392376.jpg", 70, NOW(), NOW() );
INSERT INTO Users (phone, name, nickname, password, profileImg, statusMessage, rating, createdAt, updatedAt) values
("01099999999","newhihi", "newhihi", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
    "https://wikibook.co.kr/images/cover/s/9791158392376.jpg", "new HIHI Status Message", 70, NOW(), NOW() );
INSERT INTO Users (phone, name, nickname, password, profileImg, statusMessage, rating, createdAt, updatedAt) values
("01049119038","YoungWoo", "Archpro", "SE7vYnBwximuz/hD1hsjM1n83RTUOIn8wLmwwFh2TdDAwQdLAZlqz3Vcfaxg/AtAYMK5+RGpCoIFiImpzzLnCw==",
    "", "Play to the Arche", 70, NOW(), NOW() );




# DB Likes 생성
INSERT INTO Likes (userId, likeItem) values (1, "Game");
INSERT INTO Likes (userId, likeItem) values (1, "EDM");
INSERT INTO Likes (userId, likeItem) values (1, "Travel");
INSERT INTO Likes (userId, likeItem) values (2, "Game");
INSERT INTO Likes (userId, likeItem) values (2, "Music");
INSERT INTO Likes (userId, likeItem) values (3, "Board");
INSERT INTO Likes (userId, likeItem) values (4, "Board");
INSERT INTO Likes (userId, likeItem) values (4, "Game");
INSERT INTO Likes (userId, likeItem) values (4, "walking");

# DB Posts 생성
INSERT INTO Posts (userId, title, postImg, content, maxMember, startDate, endDate, place, bring) values 
(1, "Title Hello", NULL, "content!", 5, '2022-08-24 00:00:00', '2022-08-25 00:00:00', "Daegue", "No!");
INSERT INTO Posts (userId, title, postImg, content, maxMember, startDate, endDate, place, bring) values 
(1, "Board Game Group", NULL, "Like BoardGame", 3,  '2022-08-24 00:00:00', '2022-08-25 00:00:00', "Seoul", "30$");
INSERT INTO Posts (userId, title, postImg, content, maxMember, startDate, endDate, place, bring) values 
(2, "GG!", NULL, "GG!", 10, '2022-08-24 00:00:00', '2022-08-25 00:00:00', "Online", "No");
INSERT INTO Posts (userId, title, postImg, content, maxMember, startDate, endDate, place, location, bring) values 
(2, "HighSchool Meeting!", NULL, "GG!", 2, NOW(), NOW(), "HighSchool", 
ST_GeomFromText('POINT(126.93676177044385 37.571935859817934)', 4326), "No");
INSERT INTO Posts (userId, title, postImg, content, maxMember, startDate, endDate, place, location, bring) values 
(3, "MiddleSchool Meeting!", NULL, "!MSM!", 2, NOW(), NOW(), "MiddleschoolMeeting", 
ST_GeomFromText('POINT(126.92753399970545 37.56827251556156)', 4326), "No");



#DB Tags 생성
INSERT INTO Tags (postId, tag) values (1, "Hello");
INSERT INTO Tags (postId, tag) values (1, "No");
INSERT INTO Tags (postId, tag) values (1, "Play");
INSERT INTO Tags (postId, tag) values (2, "Game");
INSERT INTO Tags (postId, tag) values (3, "Board");


#DB Channels 생성
INSERT INTO Channels (postId, userId) values (1,2);
INSERT INTO Channels (postId, userId) values (1,3);
INSERT INTO Channels (postId, userId) values (2,2);
INSERT INTO Channels (postId, userId) values (2,3);
INSERT INTO Channels (postId, userId) values (2,6);
INSERT INTO Channels (postId, userId) values (3,3);
INSERT INTO Channels (postId, userId) values (1,5);


#DB Messages 생성
INSERT INTO Messages (postId, userId, message) values (1,1, "Hello");
INSERT INTO Messages (postId, userId, message) values (1,2, "Hello");
INSERT INTO Messages (postId, userId, message) values (1,1, "Who Are You??");
INSERT INTO Messages (postId, userId, message) values (1,3, "Hello");
INSERT INTO Messages (postId, userId, message) values (1,1, "Hello");
INSERT INTO Messages (postId, userId, message) values (1,2, "Watson");
INSERT INTO Messages (postId, userId, message) values (1,1, "Where ?");
INSERT INTO Messages (postId, userId, message) values (1,2, "Um....");
INSERT INTO Messages (postId, userId, message) values (1,1, "Jun");
INSERT INTO Messages (postId, userId, message) values (1,3, "Sung");

#DB Friends 생성
INSERT INTO Friends (giveUserId, receiveUserId) values (1,2);
INSERT INTO Friends (giveUserId, receiveUserId) values (1,3);
INSERT INTO Friends (giveUserId, receiveUserId) values (2,3);
INSERT INTO Friends (giveUserId, receiveUserId) values (3,1);
INSERT INTO Friends (giveUserId, receiveUserId) values (2,1);
INSERT INTO Friends (giveUserId, receiveUserId) values (4,1);
INSERT INTO Friends (giveUserId, receiveUserId) values (5,1);
INSERT INTO Friends (giveUserId, receiveUserId) values (1,4);
INSERT INTO Friends (giveUserId, receiveUserId) values (1,5);

#DB Invites 생성
INSERT INTO Invites (giveUserId, receiveUserId, postId) VALUES (1,5,2);
INSERT INTO Invites (giveUserId, receiveUserId, postId) VALUES (1,6,2);
INSERT INTO Invites (giveUserId, receiveUserId, postId) VALUES (1,7,2);
INSERT INTO Invites (giveUserId, receiveUserId, postId) VALUES (2,1,3);
INSERT INTO Invites (giveUserId, receiveUserId, postId) VALUES (2,3,3);

'기타' 카테고리의 다른 글

EC2 접속하기  (0) 2021.08.04
HTTPS 적용하기  (0) 2021.08.04
실서비스 프로젝트 DB ERD와 입력자료 예시  (0) 2021.07.26
http GET에서 req.body로 값을 받을 때  (0) 2021.07.19
jwt로 암호화, 해독할 때 구조분해 할당을 쓰는 이유  (0) 2021.07.19
정규식  (0) 2021.07.18