[MySQL] SELECT 문법

확인 순서
1. 테이블 (FROM, JOIN)
2. 기준 (WHERE, ON)

기초 개념

# 완전 기초
SELECT 								//SELECT : 가져올 컬럼들을 모두 선택한다
CustomerID as "고객 아이디",	
CustomerName as "고객명",	
City as "도시",	
Country as "국가" 
FROM Customers						//FROM : 본 데이터셋을 선택한다
WHERE City='London' or Country='UK' //WHERE : 가져올 로우들을 선택한다
ORDER BY CustomerName 
LIMIT 2,5 							//두번째 아이템부터 5개만 보여줌

# GROUP BY : 순서쌍으로 집계한다
SELECT CategoryID FROM Products
GROUP BY CategoryID;

# GROUP BY : 순서쌍으로 집계한다
SELECT 
  Country, City,
  CONCAT_WS(', ', City, Country)
FROM Customers
GROUP BY Country, City;				//(Country, City) 순서쌍이 unique한 것들을 남김

# WHERE : 그룹하기 전
# HAVING : 그룹한 후
SELECT
  CategoryID,
  MAX(Price) AS MaxPrice, 
  MIN(Price) AS MinPrice,
  TRUNCATE((MAX(Price) + MIN(Price)) / 2, 2) AS MedianPrice,	//소수점 둘째자리까지만 남김
  TRUNCATE(AVG(Price), 2) AS AveragePrice
FROM Products
WHERE CategoryID > 2
GROUP BY CategoryID
HAVING
  AveragePrice BETWEEN 20 AND 30
  AND MedianPrice < 40;

# IF/CASE : 알기 쉬운 이름으로 바꾸기 용이
SELECT
  Price,
  IF (Price > 30, 'Expensive', 'Cheap'),
  CASE
    WHEN Price < 20 THEN '저가'
    WHEN Price BETWEEN 20 AND 30 THEN '일반'
    ELSE '고가'
  END
FROM Products;

# 날짜 관련 함수
SELECT
  OrderDate,
  YEAR(OrderDate) AS YEAR,
  MONTHNAME(OrderDate) AS MONTHNAME,
  MONTH(OrderDate) AS MONTH,
  WEEKDAY(OrderDate) AS WEEKDAY,
  DAYNAME(OrderDate) AS DAYNAME,
  DAY(OrderDate) AS DAY
FROM Orders;

서브쿼리

# 비상관 서브쿼리 : 서브쿼리가 단독으로 실행될 수 있는가?
SELECT CategoryID, CategoryName, Description
FROM Categories
WHERE
  CategoryID IN
  (SELECT CategoryID FROM Products
  WHERE Price > 50);

# 상관 서브쿼리 (+ EXISTS 연산자)
SELECT CategoryID, CategoryName
FROM Categories C				
WHERE EXISTS (					//Category 돌면서(for문 돌듯이) EXISTS가 참인 것만 출력
  SELECT * FROM Products P
  WHERE P.CategoryID = C.CategoryID
  AND P.Price > 80
);

JOIN

# 내부 JOIN
SELECT 
  C.CategoryID, C.CategoryName, 
  P.ProductName, 
  O.OrderDate,
  D.Quantity
FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID
JOIN OrderDetails D
  ON P.ProductID = D.ProductID
JOIN Orders O
  ON O.OrderID = D.OrderID;

# 외부 JOIN (LEFT/RIGHT : 선택된 방향에 있으면 출력)
SELECT
  IFNULL(C.CustomerName, '-- NO CUSTOMER --'),
  IFNULL(S.SupplierName, '-- NO SUPPLIER --'),
  IFNULL(C.City, S.City),
  IFNULL(C.Country, S.Country)
FROM Customers									// Customers -> Left
LEFT JOIN Suppliers S							// Suppliers -> Right
ON C.City = S.City AND C.Country = S.Country;

# CROSS JOIN
SELECT E1.LastName, E2.FirstName
FROM Employees E1
CROSS JOIN Employees E2
ORDER BY E1.EmployeeID;