응용) MySQL로 인스타그램 따라 테이블 설계해보기

# 테이블을 생성하기 위해서 필요한 정보

 

# 복수 테이블 사용을 위한 참조 관계 확인 하기 (테이블 설계)

 

# MySQL로 테이블 직접 생성해보기

• users 테이블

 

• photos 테이블
  • Foreign Keys
    • user_id -> users.id 참조

 

 

• comments 테이블
  • Foreign Keys
    • user_id -> users.id 참조
    • photo_id -> photos.id 참조

 

 

 

• likes 테이블
  • Foreign Keys
    • user_id -> users.id 참조
    • photo_id -> photos.id 참조
  • Indexes
    • unique -> user_id, photo_id 

 

 

 

 

• follows 테이블
  • Foreign Keys
    • follower_id -> users.id 참조
    • followee_id -> users.id 참조
  • Indexes
    • unique -> follower_id, followee_id

 

 

 

 

• tags 테이블

 

• photo_tags
  • Foreign Keys
    • tag_id -> tags.id 참조
    • photo_id -> photos.id 참조
  • Indexes
    • unique -> tag_id, photo_id

 

 

 

 

# Indexes에서 설정하는 unique란?

• unique : 고유 값, 중복이 되지 않게 함

 

• unique 미설정 : 같은 이메일을 기입해도 고유 값 설정을 하지 않으면 같은 값을 넣을 수 있음

<unique 미설정시 같은 email 추가>

 

• unique 설정 : 같은 이메일을 기입하면 에러 출력, 중복되므로 값 추가 불가

<unique 설정, 혹은 처음 컬럼 생성시 UQ를 체크하여 설정 할 수도 있다.>

 

<unique 컬럼에 갑 입력시 오류 출력 된 화면>

 

 

# 예시의 문제를 풀기 위한 값 입력

• 아래의 값을 복사 붙여넣기 하여 MySQL에서 한줄씩 실행하여 각 테이블의 값 입력

ex_insta_insert_data.txt

0.42MB

 

# 직접 문제 풀면서 DB 복수 테이블의 관계 학습하기

1. 가장 오래된 회원 5명은 누구일까요?

select username, created_at from users order by created_at limit 5;

 

2. 회원가입이 가장 많은 요일은 무엇이고 몇 명일까요?

select dayname(created_at) as day, count(dayname(created_at)) as count
from users group by dayname(created_at) limit 1;

 

3. 사진을 한번도 올리지 않은 유저들은?

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

 

4. 가장 유명한 사진을 올린 유저의 이름과 image_url, 좋아요 수는?

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

 

5. 가장 많이 사용된 해시태그의 내용과 사용 횟수는?

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

 

6. 좋아요를 80개 이상 한 사람의 이름과 좋아요 수는?

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