[MySQL] (20200902) TIL

TIL - 인프런 강의를 듣고 공부한 내용을 정리(강의 링크) - 20200902 해당 강의 완강

• 공부한 내용

  1. JOIN 구문
  2. INNSER JOIN
  3. OUTER JOIN(참고만 하기)
  4. 서브쿼리(MySQL SubQuery)
  5. 다양한 복합쿼리 연습문제

• 이 글에서 크롤링으로 DB에 저장한 데이터들을 바탕으로 실습.

1. JOIN 구문

• JOIN : 두 개 이상의 테이블로부터 필요한 데이터를 연결해 하나의 포괄적인 구조로 결합시키는 연산이다.
  • JOIN은 출력 결과에 여러 테이블의 컬럼이 필요한 경우에 유용하다.
  • JOIN을 크게 분류하면 아래와 같다. 자주 사용되는 것은 INNER JOIN이다.
    • INNER JOIN(일반적인 JOIN) : 두 테이블간에 서로 필드 값이 매칭되는 레코드(두 테이블의 모든 필드로 구성된)만 가져온다.
    • OUTER JOIN(자세한 것은 3번 참고)
      • LEFT OUTER JOIN : 왼쪽 테이블의 모든 레코드와 함께, 오른쪽 테이블에서 왼쪽 테이블의 레코드와 매칭되는(ON절을 만족하는) 레코드를 왼쪽 테이블에 붙여서 가져온다.
      • RIGHT OUTER JOIN : 오른쪽 테이블의 모든 레코드와 함께, 왼쪽 테이블에서 오른쪽 테이블의 레코드와 매칭되는(ON절을 만족하는) 레코드를 오른쪽 테이블에 붙여서 가져온다.

2. INNER JOIN

• ON절의 조건과 일치하는 데이터만 출력한다.

• 사용법

  FROM 테이블1 INNER JOIN 테이블2 ON 테이블1과 테이블2의 매칭 조건

  • 예제

    SELECT * FROM items INNER JOIN ranking ON items.item_code = ranking.item_code WHERE ranking.main_category = 'ALL';

    

  • 테이블 이름 다음에 한칸 띄고 새로운 이름을 쓰면, SQL구문 안에서 해당 이름으로 해당 테이블을 가리킬 수 있다.
    (예를 들어, 테이블 이름이 너무 길 경우, SQL구문 안에서 일일이 테이블 이름을 적는 것이 힘들 때 사용한다.)

    SELECT * FROM items A INNER JOIN ranking B ON A.item_code = B.item_code WHERE B.main_category = 'ALL';

• 연습 문제1) 전체 베스트상품(ALL 메인 카테고리)에서 판매자별 상품 개수 구하기

  SELECT items.provider, COUNT(*) FROM ranking INNER JOIN items ON ranking.item_code = items.item_code WHERE ranking.main_category = 'ALL' GROUP BY items.provider;

  

• 연습 문제2) 메인 카테고리가 서브 카테고리인 상품 중에서 판매자별 상품 개수가 5개 이상인 판매자와 상품 개수 구하기

  SELECT items.provider, COUNT(*) FROM ranking INNER JOIN items ON ranking.item_code = items.item_code WHERE ranking.main_category = '패션의류' GROUP BY items.provider HAVING COUNT(*) >= 5;

  

• 연습 문제3) 메인 카테고리가 신발/잡화인 상품 중에서 판매자별 상품 개수가 10이상인 판매자와 상품 개수를 내림차순으로 출력하기

  SELECT items.provider, COUNT(*) FROM ranking INNER JOIN items ON ranking.item_code = items.item_code WHERE ranking.main_category = '신발/잡화' GROUP BY items.provider HAVING COUNT(*) >= 10 ORDER BY COUNT(*) DESC;

  

• 연습 문제4) 메인 카테고리가 화장품/헤어인 상품 중에, 전체 상품들의 평균/최대/최소 가격 구하기

  SELECT AVG(dis_price), MAX(dis_price), MIN(dis_price) FROM ranking INNER JOIN items ON ranking.item_code = items.item_code WHERE ranking.main_category = '화장품/헤어';

  

3. OUTER JOIN(참고만 하기)

• OUTER JOIN은 한 쪽 테이블의 데이터를 전부 가져온다. (참고> INNER JOIN은 두 테이블에서 매칭되는 데이터만 가져옴.)

• OUTER JOIN에는 LEFT OUTER JOIN, RIGHT OUTER JOIN이 있다.

  • 예제1) LEFT OUTER JOIN

    SELECT * FROM ranking LEFT OUTER JOIN items ON items.ori_price = ranking.item_code;

    • 위의 SQL문에서 LEFT OUTER JOIN문의 왼쪽에 위치한 ranking 테이블이 아래 결과에서도 왼쪽에 위치한다.
      ON절을 만족하는 데이터가 단 한 개도 없음에도 불구하고, 왼쪽에 위치한 ranking테이블의 데이터를 전부 가져오고,
      items테이블의 필드 값들은 전부 null로 출력된 것을 확인할 수 있다.
      

      

  • 예제2) LEFT OUTER JOIN

    SELECT * FROM cutomer_table C LEFT OUTER JOIN order_table O ON C.customer_id = O.customer_id;

    

  • 예제3) RIGHT OUTER JOIN

    SELECT * FROM customer_table C RIGHT OUTER JOIN order_table O ON C.customer_id = O.customer_id;

    

4. 서브 쿼리(MySQL SubQuery)

• SQL문 안에 포함되어 있는 SQL문이다. 주로 테이블과 테이블간의 검색 시, 검색 범위를 좁히기 위해 사용한다.(테이블 중 필요한 부분만 먼저 가져올 수 있도록)

  • SQL문 안에서 괄호()를 사용하여 서브 쿼리문을 추가할 수 있다.
  • 대부분의 서브쿼리는 JOIN문으로 처리가 가능하기 때문에, 사용에 익숙치 않다면 JOIN문을 사용하면 된다.

• 예제1) 서브 카테고리가 '여성신발'인 상품들의 타이틀만 가져오기

  • 1 - JOIN SQL을 사용하는 방법

    SELECT title FROM items I INNER JOIN ranking R ON I.item_code = R.item_code WHERE R.sub_category = '여성신발';   

  • 2 - 서브 쿼리를 사용하는 방법

    SELECT title FROM items WHERE item_code IN (SELECT item_code FROM ranking WHERE sub_category = '여성신발');

• 예제2) 서브 카테고리가 '여성신발'인 상품중 할인 후의 가격이 가장 높은 상품의 할인 후의 가격 가져오기

  • 1 - JOIN SQL을 사용하는 방법

    SELECT MAX(dis_price) FROM items I INNER JOIN ranking R ON I.item_code = R.item_code WHERE R.sub_category = '여성신발'; 

  • 2 - 서브 쿼리를 사용하는 방법

    SELECT MAX(dis_price) FROM items WHERE item_code IN (SELECT item_code FROM ranking WHERE sub_category = '여성신발');

• 참고) 다양한 서브 쿼리 삽입 위치

  • 위의 예제들에서 처럼 WHERE ~ IN 형태가 가장 흔하게 쓰임.

  • 비교 : 아래와 같이 비교를 위한 위치에도 삽입할 수 있다.

    SELECT category_id, COUNT(*) AS film_count FROM film_category WHERE film_category.category_id > (SELECT category.category_id FROM category WHERE category.name = 'Comedy') GROUP BY film_category.category_id;

    • 단, 서브 쿼리문(괄호 안의)의 SELECT문의 실행 결과가 단 하나의 값으로 특정되는 경우에만 사용 가능하다. (당연한 이야기지만)

      • 예를 들어 위의 SQL문 안의 서브 쿼리문의 실행 결과가 2라고 하면, film_category 테이블에서 3이상의 category_id값을 갖는 데이터들의 개수가 각 category_id별로 각각 카운트되어 출력 될 것이다. 예를 들어 아래의 표 처럼.

        category_id	COUNT(*)
        3	2
        4	1
        5	4

  • FROM절

    SELECT a, b, c FROM (SELECT * FROM atoz_table);

• 연습문제1) 메인 카테고리별로 할인 가격이 10만원 이상인 상품이 몇개 있는 지를 출력해보기

  • 1 - JOIN문 사용

    SELECT main_category, COUNT(*) FROM ranking R INNER JOIN items I ON R.item_code = I.item_code WHERE I.dis_price >= 100000 GROUP BY R.main_category;

  • 2 - 서브 쿼리 사용

    SELECT main_category, COUNT(*) FROM ranking WHERE item_code IN (SELECT item_code FROM items WHERE dis_price >= 100000) GROUP BY main_category;

5. 다양한 복합쿼리 연습문제

• 1번) 메인 카테고리와 서브 카테고리에 대해 평균 가격(할인 후)과 평균 할인율 출력 해보기

SELECT R.main_category, R.sub_category, AVG(I.dis_price), AVG(I.discount_percent) FROM items I INNER JOIN ranking R ON I.item_code = R.item_code GROUP BY R.main_category, R.sub_category;

• 2번) 판매자별로 상품 개수, 평균 가격(할인 후), 평균 할인율을 상품 개수가 높은 순으로 출력 해보기

SELECT provider, COUNT(*), AVG(dis_price), AVG(discount_percent) FROM items I INNER JOIN ranking R ON I.item_code = R.item_code GROUP BY I.provider ORDER BY COUNT(*) DESC;

• 3번) 각 메인 카테고리에서 상품 개수가 20개 이상인 판매자의 판매자별 평균 가격(할인 후), 평균 할인율, 상품 개수 출력해보기

SELECT R.main_category, provider, AVG(dis_price), AVG(discount_percent), COUNT(*) FROM items I INNER JOIN ranking R ON I.item_code = R.item_code GROUP BY I.provider, R.main_category HAVING COUNT(*) >= 20;