[SQL/DB] SQL SELECT 기초 문법

1. SELECT (줘)

2. WHERE (조건에 맞는 절로)

3. GROUP BY (묶어서)

   • HAVING

   -- 국가별 고객수가 10명 이상인 나라만 조회
    SELECT Country, Count(CustomerID)
     FROM Customers
     GROUP BY Country
     HAVING (Count(CustomerID) >= 10)

4. JOIN (얘랑 엮어서)

   • INNER JOIN (교집합)

      -- 고객 테이블과 주문 테이블 중 
       -- 고객아이디가 일치하는 정보를 합쳐 모두 조회
       SELECT C.CustomerID, OrderID
        FROM Customers AS C
        INNER JOIN Orders AS O
        ON O.CustomerID = C.CustomerID	

   • LEFT JOIN

      -- 고객 테이블과 주문 테이블 중 고객아이디가 일치하는 정보를 합치는데 고객 정보는 냅 두고 가져올 것
       SELECT C.CustomerID, OrderID
        FROM Customers AS C
        LEFT JOIN Orders AS O
        ON O.CustomerID = C.CustomerID

5. SUBQUERY

   -- 고객아이디 기준 배송아이디별 배송 횟수 구하기
   SELECT
       C.CustomerID
       ifnull(o1.cnt, 0) AS cnt1,
       ifnull(o2.cnt, 0) AS cnt2,
       ifnull(o3.cnt, 0) AS cnt3
   
   FROM Customers AS C
   
   LEFT JOIN (
       select CustomerID, count(orderid) AS cnt
       from order
       where shipperid = 1
       group by CustomerID
   ) as o1
   on o1.CustomerID = C.CustomerID
   
   LEFT JOIN (
       select CustomerID, count(orderid) AS cnt
       from order
       where shipperid = 2
       group by CustomerID
   ) AS O2
   ON O2.CustomerID = C.CustomerID
   
   LEFT JOIN (
     select CustomerID, count(orderid) AS cnt
     from order
     where shipperid = 3
     group by CustomerID
   ) as o3.CustomerID = C.CustomerID
   

Reference

• SQL 쿼리문 실행 순서
• SQL 튜토리얼, 연습, 실습 사이트 모음