[UMC 5주차] 데이터베이스 실전

실습 내용

Aquery Tool을 이용한 인스타그램 erd 설계

 

유저 피드 조회 쿼리 작성 실습

SELECT name, nickName, profileImgUrl, introduce, website
FROM User
WHERE userIdx=2;

SELECT COUNT(postIdx)
FROM Post
WHERE status='ACTIVE' and userIdx=2;

=> 하나로 합치기

SELECT name, nickName, profileImgUrl, introduce, website, postCount
FROM User
        left join(SELECT userIdx, COUNT(postIdx) as postCount
                  FROM Post
                  WHERE status='ACTIVE'
                  group by userIdx) p on p.userIdx=User.userIdx
WHERE USER.userIdx=2;

SELECT name, nickName, profileImgUrl, introduce, website, 
        IF(postCount is null, 0, postCount) as postCount, //null 값이면 0으로 변환
        IF(followerCount is null, 0, followerCount) as followerCount,
        IF(followingCount is null, 0, followingCount) as followingCount

FROM User
        left join(SELECT userIdx, COUNT(postIdx) as postCount
                  FROM Post
                  WHERE status='ACTIVE'
                  group by userIdx) p on p.userIdx=User.userIdx

        left join(SELECT followerIdx, COUNT(followIdx) as followerCount
                  FROM Follow
                  WHERE status='ACTIVE'
                  group by followerIdx) f1 on f1.followerIdx=User.userIdx

        left join(SELECT followeeIdx, COUNT(followIdx) as followingCount
                  FROM Follow
                  WHERE status='ACTIVE'
                  group by followeeIdx) f2 on f2.followeeIdx=User.userIdx

WHERE USER.userIdx=3;

 

챌린지 과제

게시물 조회 쿼리문 작성

use umc;
SELECT User.userIdx, User.nickName, User.profileImgUrl, pi.imgUrl, p.content, p.createdAt,
       IF(postLike is null, 0, postLike) as postLike,
       IF(commentCount is null, 0, commentCount) as commentCount
       
FROM User
       left join(SELECT userIdx, postIdx, content, createdAt
				 FROM Post
				 WHERE status='ACTIVE') p on p.userIdx=User.userIdx
         
	   left join(SELECT postIdx, imgUrl
                 FROM PostImgUrl
                 WHERE status='ACTIVE'
                 group by postIdx) pi on pi.postIdx=p.postIdx
                 
	   left join(SELECT postIdx, COUNT(postLikeIdx) as postLike
                 FROM PostLike
                 WHERE status='ACTIVE'
				 group by postIdx) pl on pl.postIdx=p.postIdx
                 
	   left join(SELECT postIdx, COUNT(commentIdx) as commentCount
                 FROM Comment
                 WHERE status='ACTIVE'
                 group by postIdx) c on c.postIdx=p.postIdx
       
WHERE User.nickName='seoul.taste';

 

워크북 및 추가 개념

ERD

Entity Relationship Diagram의 약자로, 존재하고 있는 것(Entity)들의 관계(Relationship)을 나타낸 도표(Diagram)

 

IE 표기법

 

Query

데이터베이스에 정보를 요청하는 것

 

후기

인스타그램 erd를 설계해보았는데 erd를 작성하면서 고쳐야하는 부분들이 자꾸 튀어나왔다.

나중에 개발 시에 수정할 부분이 없도록 erd를 최대한 잘 설계하고자 하였다. 

쿼리문 작성 실습도 여러 테이블의 join을 많이 활용하는 것을 보며, 테이블 간의 join을 자유자재로 할 수 있도록 연습이 많이 필요함을 느꼈다.