230309
🦁 SELECT SQL 각 구문별 실행 순서
- FROM, JOIN
- ON, WHERE
— 2차 테이블 완성 —
- GROUP BY
- 그룹 함수
— 3차 테이블 완성 —
- HAVING
— 4차 테이블 완성 —
- ORDER BY
- LIMIT
- 고객에게 전달
🦁 그룹 함수
테이블의 전체 행을 하나 이상의 컬럼을 기준으로 기준에 따라 그룹화하여 그룹별로 결과를 출력하는 함수
그룹 함수 규칙
- 반드시 하나의 값만을 반환한다.
- NULL 값은 무시된다.
그룹 함수의 종류
| COUNT() | 행의 총 개수 출력 |
|---|---|
| MIN() | NULL을 제외한 모든 행에서 최소값 출력 |
| MAX() | NULL을 제외한 모든 행에서 최대값 출력 |
| SUM() | NULL을 제외한 모든 행의 합계 출력 |
| AVG() | NULL을 제외한 모든 행의 평균값 |
(중복된 값을 제외하려면 필드 이름 앞에 DISTINCT 키워드를 사용하면 된다.)
예제 1
# 사원 수 출력
SELECT COUNT(*)
FROM emp;
# 가장 큰 사원 번호 출력
SELECT MAX(id)
FROM emp;
# 가장 고액 연봉
SELECT MAX(salary)
FROM emp;
# 가장 저액 연봉
SELECT MIN(salary)
FROM emp;
# 회사에서 1년 고정 지출(인건비)
SELECT SUM(salary)
FROM emp;🦁 GROUP BY 구문
특정 컬럼(속성)을 기준으로 그룹화하여 조회할 때 주로 사용된다.
컬럼별로 특정 값을 검색하고 싶거나 특정 컬럼에서 어떠한 그룹이 형성되는지 확인할 수 있다.
기본 구조
SELECT [DISTINCT] 컬럼, 그룹 함수(컬럼)
FROM 테이블명
[WHERE 조건]
GROUP BY 그룹 대상
[ORDER BY 정렬대상 [ASC/DESC]]예제 2
# 부서별, 1년 고정 지출(인건비)
SELECT deptId, SUM(salary)
FROM emp
GROUP BY deptId;
# 부서별, 최고연봉
SELECT deptId, MAX(salary)
FROM emp
GROUP BY deptId;
# 부서별, 최저연봉
SELECT deptId, MIN(salary)
FROM emp
GROUP BY deptId;
# 부서별, 평균연봉
SELECT deptId, AVG(salary)
FROM emp
GROUP BY deptId;🦁 제어문
기본적으로 제어문은 무언가 조회하는 것 보다는, 새로운 필드를 생성해서 뷰로 만드는데 목적을 둔다.
새로운 필드를 생성하고 각 필드값을 제어문으로 조건을 줘서 값을 결정하는 방식이다.
IF 문
첫 번째 매개변수로 전달된 조건이 참이면 두 번째 매개변수로 전달된 값을 반환한다.
거짓이면 세 번째 매개변수로 전달된 값을 반환한다.
예제 3
SELECT IF(deptId = 1, '홍보', '기획') AS `부서명`,
GROUP_CONCAT(`name` ORDER BY id DESC SEPARATOR ', ') AS `사원리스트`,
CONCAT(TRUNCATE(AVG(salary), 0), '만원') AS `평균연봉`,
CONCAT(MAX(salary), '만원') AS `최고연봉`,
CONCAT(MIN(salary), '만원') AS `최소연봉`,
CONCAT(COUNT(*), '명') AS `사원수`
FROM emp
GROUP BY deptId;CASE 문
JAVA의 Switch Case문과 비슷한데, WHEN과 THEN이 쌍을 이루어야 하며 여러 번 사용할 수 있다.
마지막의 ELSE는 모든 조건을 만족하지 않을 때 출력되는 기본값이다.
예제 4
### CASE문을 사용하고, GROUP_CONCAT의 결과에서 중복제거도 처리도 한 버전
SELECT CASE
WHEN deptId = 1
THEN '홍보'
WHEN deptId = 2
THEN '기획'
ELSE '무소속'
END AS `부서명`,
GROUP_CONCAT(DISTINCT `name` ORDER BY id DESC SEPARATOR ', ') AS `사원리스트`,
TRUNCATE(AVG(salary), 0) AS `평균연봉`,
MAX(salary) AS `최고연봉`,
MIN(salary) AS `최소연봉`,
COUNT(*) AS `사원수`
FROM emp
GROUP BY deptId;🦁 HAVING
HAVING은 간단하게 생각해서 GROUP BY 결과에 조건을 붙이고 싶을 때, 즉 GROUP BY의 WHERE 절과도 같다고 볼 수 있다.
예제 5
## V3(V2에서 평균연봉이 5000이상인 부서로 추리기)
SELECT D.name AS 부서,
GROUP_CONCAT(E.name) AS 사원리스트,
TRUNCATE(AVG(E.salary), 0) AS 평균연봉,
MAX(E.salary) AS 최고연봉,
MIN(E.salary) AS 최소연봉,
COUNT(*) AS 사원수
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id
GROUP BY E.deptId
HAVING `평균연봉` >= 5000;🦁 서브쿼리(Subquery)
하나의 SQL 문 안에 포함되어 있는 또 다른 SQL문을 말한다.
#메인쿼리
SELECT *
FROM db_table
WHERE table_fk IN (
#서브쿼리
SELECT table_fk FROM db_table_other WHERE ..
)📌 서브쿼리 사용시 주의사항
- 서브쿼리는 괄호로 감싸서 사용한다.
- 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용할 수 있다.
- 서브쿼리에서는 ORDER BY를 사용하지 못한다. 단, LIMIT을 함께 사용할 경우 가능하다.
예제 6
## 예제 5를 HAVING을 사용하지 않고 구현하는 방법
SELECT *
FROM (
SELECT D.name AS `부서명`,
GROUP_CONCAT(E.`name`) AS `사원리스트`,
TRUNCATE(AVG(E.salary), 0) AS `평균연봉`,
MAX(E.salary) AS `최고연봉`,
MIN(E.salary) AS `최소연봉`,
COUNT(*) AS `사원수`
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id
WHERE 1
GROUP BY E.deptId
) AS D
WHERE D.`평균연봉` >= 5000;🦁 JOIN
JOIN에는 INNER JOIN과 OUTER JOIN이 있다.
이 둘의 차이는 INNER JOIN은 교집합, OUTER JOIN은 합집합 이라고 할 수 있다.
INNER JOIN - 교집합
말 그대로 교집합을 나타낸다.
따라서 겹치지 않는 행이 존재할 경우, 즉 값이 NULL인 경우 결과에서 제외된다.
예제 7
# 전 사원에 대하여, [부서명, 사원번호, 사원명] 양식으로 출력(IT 부서는 안나옴)
SELECT D.name AS `부서명`,
E.id AS `사원번호`,
E.name AS `사원명`
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id;OUTER JOIN - 합집합
OUTER JOIN에서 가장 많이 쓰이는 것은 LEFT JOIN이다.
LEFT JOIN은 왼쪽에 적힌 테이블의 모든 행을 조회한다.
따라서 테이블의 어떤 값이 NULL일 때, 출력하지 않는 INNER JOIN과는 다르게 NULL로 채워진다.
예제 8
# 전 사원에 대하여, [부서명, 사원번호, 사원명] 양식으로 출력(IT 부서가 아직 사원이 없더라도, 1줄이라도 나오도록 해주세요, LEFT JOIN 필요)
## IT부서는 [IT, NULL, NULL] 으로 출력
SELECT D.name AS `부서명`,
E.id AS `사원번호`,
E.name AS `사원명`
FROM dept AS D
LEFT JOIN emp AS E
ON E.deptId = D.id;