MySQL 이론2

1.집계함수

1-1. COUNT() : 컬럼의 개수를 세준다. 

 

1-2. AVG() : 컬럼이 평균을 구해준다.

AVG()는 컬럼의 NULL값은 빼고 계산한다.

 

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. 시간 더하기, 빼기 함수