SQL 명령어 정리(2)_Day44(5)

집합연산

- GROUP BY

 

SELECT * FROM customers;

SELECT * FROM customers GROUP BY State;

SELECT State, count(*) FROM customers GROUP BY State;

 

- ORDER BY

 

SELECT * FROM customers;

SELECT * FROM customers GROUP BY State;

SELECT State, count(*) FROM customers GROUP BY State;

SELECT State, count(*) FROM customers GROUP BY State ORDER BY count(*) DESC

 

- HAVING

(HAVING 은 GROUP BY 로 조회된 결과에 대한 필터)

 

SELECT CustomerId, AVG(Total) FROM invoices GROUP BY CustomerId HAVING AVG(Total) > 6.00

 

HAVING 은 그룹화한 결과에 대한 필터라고 생각한다면 WHERE 는 그룹화하기 전에 조회되는 레코드를 필터

 

- COUNT

SELECT State, COUNT(*) FROM customers GROUP BY State;

 

- SUM

SELECT InvoiceId, SUM(UnitPrice) FROM invoice_items GROUP BY InvoiceId;

 

- AVG

SELECT TrackId, AVG(UnitPrice) FROM invoice_items GROUP BY TrackId;

 

- MAX , MIN

SELECT CustomerId, MIN(Total) FROM invoices GROUP BY CustomerId

SELECT CustomerId, MAX(Total) FROM invoices GROUP BY CustomerId

 

---

SELECT 실행 순서

쿼리문이 적힌 순서가 아닌 정해진 순서대로 작동

• FROM
• WHERE
• GROUP BY
• HAVING
• SELECT
• ORDER BY

---

CASE 사용하기

파이썬의 if문 같은 기능

 

CASE

  WHEN - THEN

  ELSE

END

 

 

---

SUBQUERY

실행되는 쿼리에 중첩으로 위치해 정보를 전달

서브쿼리는 소괄호로 감싸져 있음

 

 

1) IN, NOT IN

 

2) EXISTS , NOT EXISTS

 

3) FROM