MySQL 이론2
1.집계함수
1-1. COUNT() : 컬럼의 개수를 세준다.
1-2. AVG() : 컬럼이 평균을 구해준다.
1-3. SUM(), MAX(), MIN()의 함수도 있다.
2. GROUP BY, HAVING
WHERE은 GROUP BY위에 HAVING은 밑에 적어준다.
WHERE은 GROUP BY에 들어가기 전에 필터를 걸어준다.
HAVING은 GROUP BY로 나온 결과에 필터를 걸어준다.
SELECT SupplierID, CategoryID, ROUND(AVG(Price))
FROM Products
WHERE Price >= 20
GROUP BY SupplierID, CategoryID
HAVING ROUND(AVG(Price)) >= 40
ORDER BY ROUND(AVG(Price)) DESC
GROUP BY 1, 2로 해줄 수 있지만 이런 방식은 지양해야 한다.
SELECT SupplierID, CategoryID, ROUND(AVG(Price))
FROM Products
GROUP BY 1, 2
ORDER BY ROUND(AVG(Price)) DESC
3. CASE문
CASE
WHEN 조건 THEN 값
WHEN 조건 THEN 값
ELSE
END
의 형식을 가진다.
SELECT *,
CASE
WHEN categoryid = 1 THEN '음료'
WHEN categoryid = 2 THEN '조미료'
ELSE '기타' END AS CategoryID_Name
FROM Products| ProductID | ProductName | SupplierID | CategoryID | Unit | Price | CategoryID_Name |
| 1 | Chais | 1 | 1 | 10 boxes x 20 bags | 18 | 음료 |
| 2 | Chang | 1 | 1 | 24 - 12 oz bottles | 19 | 음료 |
| 3 | Aniseed Syrup | 1 | 2 | 12 - 550 ml bottles | 10 | 조미료 |
| 4 | Chef Anton's Cajun Seasoning | 2 | 2 | 48 - 6 oz jars | 22 | 조미료 |
| 5 | Chef Anton's Gumbo Mix | 2 | 2 | 36 boxes | 21.35 | 조미료 |
| 6 | Grandma's Boysenberry Spread | 3 | 2 | 12 - 8 oz jars | 25 | 조미료 |
| 7 | Uncle Bob's Organic Dried Pears | 3 | 7 | 12 - 1 lb pkgs. | 30 | 기타 |
아래처럼 WHEN의 조건문에 조건을 2개 줄 수도 있다.
SELECT *,
CASE
WHEN categoryid = 1 AND SupplierID = 1 THEN '음료'
WHEN categoryid = 2 THEN '조미료'
ELSE '기타' END AS CategoryID_Name
FROM Products
아래처럼 CASE문으로 직업 Product_Name이라는 칼럼을 새롭게 추가한 다음에 Product_Name으로 GROUP BY도 가능하다.
SELECT CASE WHEN CategoryID = 1 THEN '음료'
WHEN CategoryID = 2 THEN '조미료'
WHEN CategoryID = 3 THEN '과자'
WHEN CategoryID = 4 THEN '주류'
ELSE '기타'
END AS Product_Name
, ROUND(AVG(Price))
FROM Products
GROUP BY Product_Name
CASE문을 활용한 피벗테이블
CategoryID = 1이면 Price 칼럼을 출력, 아니면 NULL값을 출력하는 category1 칼럼을 생성
SELECT CASE WHEN CategoryID = 1 THEN Price ELSE NULL END AS category1, *
FROM Products
LIMIT 4| category1 | ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
| 18 | 1 | Chais | 1 | 1 | 10 boxes x 20 bags | 18 |
| 19 | 2 | Chang | 1 | 1 | 24 - 12 oz bottles | 19 |
| null | 3 | Aniseed Syrup | 1 | 2 | 12 - 550 ml bottles | 10 |
| null | 4 | Chef Anton's Cajun Seasoning | 2 | 2 | 48 - 6 oz jars | 22 |
category1칼럼의 평균을 구해줄라면 AVG(CASE ~ END)처럼 AVG로 CASE문을 감싸준다.
CASE WHEN CategoryID = 1 THEN AVG(Price) ELSE NULL END처럼 THEN뒤를 AVG()로 감싸주면 안된다.
CASE ~ END문으로 category1 칼럼을 구한 다음에 그 칼럼의 모든 값을 더하고 데이터 수로 나누어줘서 평균을 구하는 원리여서 AVG로 CASE문 전체를 감싸주어야 한다.
SELECT AVG(CASE WHEN CategoryID = 1 THEN Price ELSE NULL END) AS category1_AvgPrice, *
FROM Products| category1_AvgPrice | ProductID | ProductName | SupplierID | CategoryID | Unit | Price |
| 37.97916667 | 1 | Chais | 1 | 1 | 10 boxes x 20 bags | 18 |
AVG(CASE~END)문을 여러개 적어주면 카테고리별 평균가격을 구할 수 있다.
SELECT AVG(CASE WHEN CategoryID = 1 THEN Price ELSE NULL END) AS category1_AvgPrice,
AVG(CASE WHEN CategoryID = 2 THEN Price ELSE NULL END) AS category2_AvgPrice,
AVG(CASE WHEN CategoryID = 3 THEN Price ELSE NULL END) AS category3_AvgPrice
FROM Products| category1_AvgPrice | category2_AvgPrice |
category3_AvgPrice
|
| 37.97916667 | 23.0625 | 25.16 |
4. JOIN
4-1.INNER JOIN
INNER JOIN은 두 테이블을 교집합으로 묶어준다.
SELECT *
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
여러개의 테이블을 묶어줄 수도 있다.
SELECT *
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
JOIN 조건을 2개 이상 줄 수도 있다.
SELECT f1.X, f1.Y
FROM Functions f1
INNER JOIN Functions f2 ON f1.X = f2.Y AND f1.Y = f2.X
자기 자신과 JOIN하는 Self Join도 가능하다. 이때는 테이블에 Alias를 줘야한다.
SELECT e.name AS Employee
FROM Employee e
INNER JOIN Employee m ON e.managerID = m.id
WHERE e.salary > m.salary
4-2.OUTER JOIN
LEFT(RIGHT) JOIN
Customers테이블에 있는 것을 모두 출력하면서 두 테이블을 join한다.
SELECT *
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustoemrID
UNION JOIN
UNION만 사용하면 중복값은 빼고 합해주며, UNION ALL을 사용하면 중복값을 빼지 않고 모두 합해준다.
SELECT *
FROM Products
WHERE Price <= 5
UNION
SELECT *
FROM Products
WHERE Price > 40
FULL OUTER JOIN
LEFT조인과 RIGHT조인을 모두 사용해서 합집합으로 두 테이블을 묶어준다.
SELECT *
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
UNION
SELECT *
FROM Custoemrs
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustoemrID
5. 시간 더하기, 빼기 함수
