MySQL - 인스타그램 데이터베이스 클론

인스타그램 데이터베이스 스키마 만들기

DATABASE

CREATE DATABASE instagram_clone;

USE instagram_clone;

users

CREATE TABLE users (
	id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO users (username) values ('BlueTheCat'), ('CharlieBrown'), ('ColtSteele');

photos

CREATE TABLE photos (
	id INT AUTO_INCREMENT PRIMARY KEY,
    image_url VARCHAR(255) NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(user_id) REFERENCES users(id)
);

INSERT INTO photos (image_url, user_id) VALUES (1,1);

comments

CREATE TABLE comments (
	id INT AUTO_INCREMENT PRIMARY KEY,
    comment_text VARCHAR(255) NOT NULL,
    user_id INT NOT NULL,
    photo_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(photo_id) REFERENCES photos(id)
);

INSERT INTO comments (comment_text, user_id, photo_id) VALUES ('Good Post', 1, 1);

likes

CREATE TABLE likes (
	user_id INT NOT NULL,
    photo_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(user_id) REFERENCES users(id),
    FOREIGN KEY(photo_id) REFERENCES photos(id),
    PRIMARY KEY(user_id, photo_id)
);

INSERT INTO likes (user_id, photo_id) VALUES (1,1);

follows

CREATE TABLE follows (
	follower_id INT NOT NULL,
    followee_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY(follower_id) REFERENCES users(id),
    FOREIGN KEY(followee_id) REFERENCES users(id),
	PRIMARY KEY(follower_id, followee_id)
);

INSERT INTO follows (follower_id, followee_id) VALUES (1,2), (1,3);

tags

CREATE TABLE tags (
	id INT AUTO_INCREMENT PRIMARY KEY,
    tag_name VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE photo_tags (
	photo_id INT NOT NULL,
    tag_id INT NOT NULL,
    FOREIGN KEY(photo_id) REFERENCES photos(id),
    FOREIGN KEY(tag_id) REFERENCES tags(id),
    PRIMARY KEY(photo_id, tag_id)
);

INSERT INTO tags (tag_name) VALUES ('adorable'), ('cute');

INSERT INTO photo_tags (photo_id, tag_id) VALUES (1,1), (1,2);

[참고] 태그를 처리하는 다양한 방법에 대한 속도 테스트

---

데이터로 작업하기

가장 오래된 유저 5명 찾기

SELECT username, created_at FROM users ORDER BY created_at LIMIT 5;

---

회원가입이 가장 많은 요일 찾기

SELECT DAYNAME(created_at) AS day, count(*) FROM users GROUP BY day; // 화요일

---

게시물을 한번도 올리지 않은 사람 찾기

SELECT username FROM users LEFT JOIN photos ON users.id = photos.user_id 
WHERE ISNULL(image_url);

---

좋아요가 가장 많은 사진의 유저 찾기

내 답안
SELECT username, COUNT(*) as count FROM likes
JOIN users ON likes.user_id = users.id
JOIN photos ON likes.photo_id = photos.id GROUP BY username ORDER BY count DESC LIMIT 1;

정답
SELECT username, photos.id, photos.image_url, COUNT(*) AS total FROM photos
JOIN likes ON likes.photo_id = photos.id
JOIN users ON photos.user_id = users.id GROUP BY photos.id ORDER BY total DESC LIMIT 1;

> likes가 아닌 photos를 기준으로 잡아야 했다.

---

유저 한 명당 올리는 게시물의 평균 구하기

정답
SELECT (SELECT COUNT(*) FROM photos) / (SELECT COUNT(*) FROM users) AS avg;

---

가장 많이 사용되는 해시태그 5개 찾기

내 답안
SELECT tag_name, COUNT(*) AS count FROM photo_tags
JOIN tags ON photo_tags.photo_id = tags.id GROUP BY tag_name ORDER BY count DESC;

정답
SELECT tag_name, COUNT(*) AS total FROM photo_tags
JOIN tags ON photo_tags.tag_id = tags.id GROUP BY tag_name ORDER BY total DESC LIMIT 5;

> photo_tags.photo_id가 아닌 photo_tags.tag_id로 했어야했다.

---

모든 사진에 좋아요를 누른 유저 찾기

정답
SELECT users.id, username, COUNT(*) AS num_likes FROM users
JOIN likes ON users.id = likes.user_id GROUP BY users.id
HAVING num_likes = (SELECT COUNT(*) FROM photos) - 1;

> -1을 한 이유 : SELECT COUNT(*) FROM photos 값이 257이 나와야 하는데 데이터가 변형됐는지 258이 나와서.