MySQL 인스타그램 관련 데이터 관리 실습 문제(order by, group by, join, limit ...)

 

728x90

 

 

 

 

 

인스타그램 등과 같은 sns의 데이터를 MySQL에서 어떻게 데이터 처리하는지 실습을 진행해봅니다.

 

 

보통 게시물을 업로드하게 되면 사진, 본문 텍스트 등의 관련 정보를 서버에 보내게 됩니다. 

사진은 서버에 보내서 스토리지에 저장하고(실제 사진 파일)

본문 멘트와 같은 문자열은 데이터베이스에 저장됩니다.(사진 url, 유저정보(ID, Email 등), 게시물 본문 멘트, 태그 등)

 

 

 

주어진 데이터는 아래와 같습니다. 

 

SELECT * 
FROM ig_clone.users;

 

 

 

SELECT * 
FROM ig_clone.tags;

 

 

 

SELECT * 
FROM ig_clone.photos;

 

 

 

 

SELECT * 
FROM ig_clone.photo_tags;

 

 

 

SELECT * 
FROM ig_clone.likes;

 

 

 

 

 

SELECT * 
FROM ig_clone.follows;

 

 

 

 

SELECT * 
FROM ig_clone.comments;

 

 

 

 

---

실습문제

 

 

1. 가장 오래된 회원 5명을 찾으세요 

 

select *
from users
order by created_at asc ;

 

 

 

 

 

2. 회원 가입을 가장 많이 하는 요일?

 

select *, dayname(created_at) daynamed, count(*) count
from users
group by dayname(created_at)
order by count desc;

 

 

 

 

 

3. 회원 가입은 했지만 사진은 한번도 올리지 않은 유령회원들의 데이터를 가져오시오

 

select u.*
from users u
left join photos p
on u.id = p.user_id
where p.id is null;

 

 

 

 

 

4. 가장 유명한 사진은 무엇인지 찾아서 유저이름, image_url, 좋아요 수를 나타내시오

 

select *, count(photo_id) total
from likes l
join users u
on l.user_id = u.id
join photos p
on l.photo_id = p.id
group by photo_id
order by total desc
limit 0, 1;

 

 

 

 

 

5. 가장 많이 사용된 해시태그의 이름은 무엇이며, 몇 개인지 나타내시오

 

select t.tag_name, count(tag_id) as cnt
from photo_tags pt
join tags t
on pt.tag_id = t.id
group by tag_id
order by cnt desc
limit 0,1;

 

 

 

 

 

6. 특정 포스팅 : 포토스 테이블의 아이디 72번의 작성자, 사진 url, 좋아요 수를 가져오시오

 

select u.username, p.image_url, count(l.id) count
from photos p
join users u
on p.user_id = u.id
left join likes l
on p.id = l.photo_id
where p.id = 72;

 

 

 

 

 

7. 포토 아이디 72번의 해시태그 이름을 가져오세요

select t.id, t.tag_name
from photo_tags pt
join tags t
on pt.tag_id = t.id
where photo_id = 72;

 

 

 

 

 

8. 포토 아이디 72번의 댓글 리스트를 가져오시오

(댓글 작성 이름, 댓글 내용, 댓글 작성 날짜, 댓글은 최신 순으로)

 

select u.username, c.comment_text, c.created_at
from comments c
join users u
on c.user_id = u.id
where c.photo_id = 72
order by c.created_at desc;

 

 

 

 

 

9. 유저 아이디 2인 사람이 몇개의 포스팅을 올렸는지 가져오시오

 

select u.username, count(p.id) photo_cnt
from photos p
join users u
on u.id = p.user_id
where user_id = 2;

 

 

 

 

 

10. 유저아이디 2인 사람의 팔로워 수와 팔로잉 수를 가져오시오

팔로워 수

 

select count(*) as follower_cnt
from follows f
where followee_id = 2;

 

 

 

팔로잉 수

 

select count(*) as following_cnt
from follows
where follower_id = 2;

 

 

 

 

 

11. 유저 아이디 2번이 팔로잉하는 사람들의 포스팅 리스트 가져오기, 25개씩 가져오기

(작성자 이름, 이미지 주소, 좋아요 수)

 

select u.username, p.image_url, count(l.id) as like_cnt
from follows f
join photos p
on f.followee_id = p.user_id
join users u
on p.user_id = u.id
left join likes l
on p.id = l.photo_id 
where f.follower_id = 2
group by p.id
order by p.created_at desc
limit 0, 25;

-- limit 25, 25;
-- limit 50, 25;

 

 

 

 

 

---

 

좋아요 sql

 

insert into likes
(user_id, photo_id)
values
(2,1);

 

 

 

좋아요 해제 sql

 

delete from likes
where user_id = 2 and photo_id = 1;

 

 

---

 

팔로우 sql

 

insert into follows
(follower_id, followee_id)
values
(2,1);

 

 

 

언팔로우 sql

delete from follows
where id = 7624;

 

 

---

 

회원 탈퇴 sql

 

delete from users
where id = 1;

 

 

---

 

유저 아이디 1번 사진 삭제 sql

delete from photos
where user_id = 1;

 

 

 

 

 

반응형