화면기획서를 보고 SQL문을 작성해보자!

 
 

1. 인스타 페이지를 활용한 실습

 
각 각에 맞는 foreing keys를  커럼에 맞게 설정해주어야 join 을 할 수 있다

 

포토 아이디가 72번인 사람의, 사진 url 주소, 작성자 이름 , 좋아요 수 를 가져오시오
 
이 경우에는 photo에서 where 을 통해서 photo_id 가 72 번은 찾을 수 는 있어도 사진의 url, 작성자의 이름 좋아요 수는 알 수 없다. 따라서 이 정보들을 갖고 있는 테이블과의 join이 필요한것이다.
 
 

포토아이디에 72번에 댓글을 남기고 싶다라 할 때 
 
insert into 를 활용하면 된다 이것은 추가하는 기능을 갖고 있기 때문에 내가 댓글을 쓰고 입력하면 추가가 되는 것이니깐 insert into를 활용함이 적절하다
 
 
photo_id 가 72번의 댓글 리스트경우
select 를 활용해서 comment 에서 users 와 조인하여 원하는 comment_text 와 username을 찾을 수 있다
그리고 where 을 쓴 이유는 photo_id 라는 지정값에서 찾기를 원하기에 where을 쓴것이다
 
첫번째꺼보다 두번째 쓴 것이 더 정확한 이유는 
comment_text가 쓰여진 날짜가 빠른 순으로 보통 나타내기 때문에 order by를 활용한 정렬을 해주는 것이 더 깔끔하기 때문이다.
 

2. 카페 정보를 모아둔 어플 화면기획서를 활용한 실습 

 
 
어렵게 생각하지 않아도 된다 회원가입은 말 그대로 user 의 정보를 데이터베이스에 저장하는 과정이기 때문에
insert 문을 활용 하여 user의 각 정보를 입력하면된다
 
로그인은 
 
방금 회원가입을 할때 데이터 베이스에서 저장된 데이터를 입력했을 때 불러오는 쿼리를 만들면된다
 
 

 
왼쪽 사진과 같이 카페 리스트가 있다면
필요한 정보가 무엇인지 생각해 보자
일단은 카페의 이름, 주소,카페한줄설명,별점 이 보인다 그렇다면 이것들을 불러오는 퀴리는 짜면되는 것이다
 
처음에 cafe 의 정보를 가져와보자. 하지만 내가 원하는 정보중 별점이라는 정보는 cafe 테이블 컬럼 안에 없기때문에 join을 이용해서 가져와야 한다.
 
이때 left join을 쓴 이유는 리뷰가 하나도 없는 가게도 분명 존재할 것이다. join을 쓰면 공통된 값이 있는것 끼리만 나오는 것이 어서 리뷰가 없는 가게가 존재해도 그 가게를 볼 수 없는 상황이 발생한다. 그렇기에 이러한 정보들도 같이 불러와야하기 때문에 left join을 쓰는 것이다.
 
group by를 한 이유는 같은 데이터가 여러개 일때 같은 것 끼리 뭉쳐주기 위해 쓴다. 그래서 주로 집계함수와 함께 쓰이는 경우가 많다.
 
위에도 카페의 별점의 평균을 보여주고 싶기 때문에 한 카페에 여러개의 별점 정보를 한카페로 묶은 다음에 그안에 있는 별점 정보값을 평균 내주는 avg를 활용하여 별점의 평균 값을 구해준 것이다. 
 
그리고 avg 앞에 ifnull을 쓴 이유는 처음에 left join을 할 경우 별점이 없는 곳은 null로 뜬다고 설명했다. 하지만 우리는 일상 생활에서 null이란 단어를 들어 본적이 없을 것이다. 그렇기 때문에 그냥도 알아 볼 수 있게 다른 것으로 대처해서 보여 주어야 하기에 ifull 즉 만약 null이라면 0으로 보여지게 작성한 것이다.
 
limit 는 만약에 1000개의 데이터가 입력이 되있는데 한번에 이 많은 데이터를 보여주게 하면 핸드폰이나 컴퓨터등이 과부화가 걸려 느려지는 현상이 발생하며 데이터를 많이 불러오는 만큼 용량의 낭비 또한 막심하다 
 
그렇기 때문에 우리는 한번에 불러 올 수 있는 양을 정하기 위해 limit를 활용하는 것이다.

이때 첫번재 부터 5개만 불러와줘 라고 가정했을 때 limit(1,5) 라고 쉽사리 생각 할 수 있다.
하지만 컴퓨터에서 1은 0부터이기 때문에 limit(0,5) 라고 쓴 것이 적절하다.
 

 
이번에는 카페 상세화면을 만들어보자 이때는 두가지 쿼리를 만들어야한다. 첫번째는 특정카페의 정보이고 두번째는 리뷰에 대한 정보이다.
 
첫번째부터 시작하겠다
 
카페 상세 페이지에 필요한것은 카페 이름, 카페 주소, 카페운영 시간, 전화번호, 별점이 있다.
 
카페정보를 가져오면 대부분의 정보는 있지만 별점 정보는 없기 때문에 review 테이블과 join을 해준다.
그리고 그냥 join 이 아니라 left join을 해주어야한다. 위에서도 설명했듯 리뷰가 존재하지 않는 카페도 존재하기때문이다.
 
여기서 유의 할 점은 시간이다 처음에 테이블을 생성하고 컬럼을 입력했을 때 시간 관련해서는 timestamp를 썻기 때문에 초단위까지 나올 것이다. 그렇기에 이 시간을 시 분만 나오게 하고 싶다면 substr(ing)를 사용하여 원하는 순서에서 원하는 순서까지 불러올 수 있다. 
이부분은 프론트엔드 개발자가 만들때 조정하는 경우도 많지만 연습을 위해 좀 더 정확하게 작성했다.
 
두번째 리뷰 불러오기는 review테이블에 user의 정보가 없기 때문에 join을 했다. 이때 left join이 아닌 그냥 join을 쓴 이유는 review를 쓸때 user의 정보가 안뜰리 없다 왜냐하면 우리는 처음에 foreing keys 를 설정하여 두개의 정보를 이어줬기 때문이다.
 
이후 남은 정보를 입력한 뒤에 마지막에 order by해서 이쁘게 빠른 날짜 순으로 정렬해주는 것이 좋다.
 

 
리뷰 작성 화면에서 필요한 것은 정보를 남기는 유저의 아이디와 어떤카페인지의 카페 아이디, 별점과 리뷰멘트이다
 
입력을 위한 sql 언어인 insert 를 활용하여 만들 수 있다.
 

 
내가 작성한 리뷰 리스트 화면을 만들때에 필요한 것을 생각해보자
일단 카페이름, 작성날짜, 내가 남긴 리뷰(코멘트), 별점 이 필요하다.
 
먼저 review 정보를 불러온다면 카페아이디,작성날짜, 별점, 코멘트까지는 있지만 cafe의 정보는 없다
그렇기에 cafe와 join을 해주면된다.
위에 퀴리 처럼 user의 테이블을 join해도 되지만 사실상 review 테이블에 cafe_id 정보가 들어가서 굳이 안해줘도 된다.
만약 user의 정보를 join 하지 않을 것 같으면 where의 u.id ->r.cafe_id 로 바꿔주면 된다
 
그리고 위에 보면 리뷰삭제 버튼도 있다  이때는 delete from을 사용해주면 되는데 
이때 유의점은 where을 쓸때 user_id와 cafe_id 를 적어줘야한다.

(카페 한개에 대한 평가 이기며 리뷰를 중복해서 쓸 일이 없기 때문에 굳이 고유값 아이디를 쓰지 않은 상태에서 상세정보만 입력해도 지울 수 있는것이다.)

만약 한쪽만 쓴다면 한쪽 데이터에 해당되는 정보들이 전부 날라가기 때문에 누가 어디에 를 알려줘야한다.
 
 

 
리뷰수정은 정보를 바꾸는 것이기에 update를 쓴다. 
첫번째 쓴 퀴리와 두번째 쓴 쿼리 모두 update가 된다. 하지만 첫번째것은 오류가 생길 수 있다.
where 절에 user_id =1 이고 cafe_id =50 이라는 중복값이 발생할 수 있기 때문이다
아래 사진을 보면 user_id의 중복을 확인 할 수 있다. 이런식으로 중복값이 생겼을 경우 모든 content가 변경될 수 도 있기때문에 
고유값인 review 의 id 값을 where 로써야한다