[MySQL] 관계 DB 실습: SNS DB, 일정공유 앱 개발, UNIQUE

SNS DB

 

대부분의 소셜 네트워크 서비스는 좋아요 기능이 있다.

사진에 한명의 유저가 좋아요를 누를때 더 추가 할수없는 하나의 고유한 데이터로 남기게 된다.

이러한 데이터를 unique설정한다.

 

테이블을 설정할때 인덱스로가서 인덱스로 관계성이 있는 두개의 id 컬럼을 정하고 unique로 설정한다.

 

# 이 테이블과 비슷한 속성을 가지고 있는 팔로워와 포토태그 테이블도 이처럼 설정해줘야 한다.

 

 

실습 1. 가장 오래된 회원 5명은 누구입니까?

 

in:

select *
from users
order by created_at
limit 5;

out:

 

실습 2. 회원가입을 가장 많이 하는 요일은 무슨요일이며, 몇명입니까?

 

in:

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

out:

# dayname을 사용하여 요알을 나타내고 날짜별로 그룹바이 해서 사람수를 내림차순하였다.

실습 3. 회원가입은 했지만, 사진은 한번도 올리지 않는 유저들의 데이터를 가져오세요.

 

in:

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

out:

# join으로 유저와 사진테이블을 연결하고 사진테이블의 유저 id가 없는 유저 이름을 출력했다.

 

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

in:

select l.photo_id, u.username, p.image_url, count(l.photo_id) as likes
from users u
join photos p
on u.id = p.user_id
join likes l
on p.id = l.photo_id
group by l.photo_id
order by likes desc;

out:

# 유저이름을 나타내기 위해 유저 테이블을 연결

# 사진 URL을 나타내기 위해 사진 테이블을 연결

# 좋아요가 눌린 사진의 id와 좋아유 수를 나타내기 위해 좋아요 테이블을 연결했다.

 

실습 5. 가장 많이 사용된 해시태그의 tag_name을 나타내고 해시태그가 몇개인지 조회하세요.

 

in:

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

out:

 

실습 6. 좋아요를 80개 이상 한 사람들의 이름과 좋아요 수를 조회하세요.

 

in:

select u.username, count(l.user_id) as count
from users u
join likes l
on u.id = l.user_id
group by username having count >= 80
order by count desc, u.username asc;

out:

 

 

일정공유 앱 개발

 

어떤 테이블을 만들고 컬럼을 만들어야 하는지,

또 어떻게 foreign key를 설정해야 하고

어떤 컬럼과 unique설정을 해야할지 직접 구상하여 만들어보자

 

users 테이블:

 

 

 

memos 테이블:

 

 

 

follow 테이블:

 

 

 

내 할일내용 표시 25개씩 가장 먼 미래의 일정부터 차례로 보여주자

 

in:

select content as 일정, finish_at as date, if (is_done=0,'완료되지 않음','완료') as 완료여부
from memos
where user_id = 2
order by finish_at desc
limit 25;

out:

# 유저 id 2번 기준으로 나타냈다.

 

일정완료여부를 세팅해보자

 

in:

update memos
set is_done = 1
where id =3;

select * from memos;

out:

 

 

이것을 유저가 알아볼수있도록 나타내자

 

in:

select content as 일정, finish_at as date, if (is_done=0,'완료되지 않음','완료') as 완료여부
from memos
where user_id = 2
order by finish_at desc
limit 25;

out:

 

 

친구추가하면 친구들의 일정을 확인할수 있도록 만들어야 한다.

내 친구들인 김나나와 마이크의 일정을 가져오자.

 

in:

select u.username as 이름, m.content as 일정, m.finish_at as date, if (m.is_done=0,'완료되지 않음','완료') as 완료여부
from follow f
join users u
on f.followee_id =u.id
join memos m
on m.user_id = u.id
where follower_id = 1

order by u.username, finish_at desc;

out:

 

 

현재시간을 기준으로 지나지않은 일정을 가져오자

 

in:

select u.username as 이름, m.content as 일정, m.finish_at as date, if (m.is_done=0,'완료되지 않음','완료') as 완료여부
from follow f
join users u
on f.followee_id =u.id
join memos m
on m.user_id = u.id
where follower_id = 1 and finish_at>now()

order by u.username, finish_at desc;

out:

# where by에 유저id를 1인 것을 가져오도록 설정하고 현재 시간보다 큰 데이터를 가져왔다.

 

 

현재 시간 이전의 지난 일정을 가져오자

 

in:

select u.username as 이름, m.content as 일정, m.finish_at as date, if (m.is_done=0,'완료되지 않음','완료') as 완료여부
from follow f
join users u
on f.followee_id =u.id
join memos m
on m.user_id = u.id
where follower_id = 1 and finish_at<now()

order by u.username, finish_at desc;

out: