그룹함수
# 그룹 함수는 데이터를 하나로 압축한다.
# *의 정보는 행 중에 랜덤으로 선택된다.
select *, count(id)
from emp;그룹함수 예시
- MAX: 최댓값 반환
- MIN: 최솟값 반환
- AVG: 평균값 반환
- COUNT: 행의 수 반환
- GROUP_CONCAT: 값을 concat(이어붙이기)함
group by
group by가 있다 -> 그룹별로 나눠진 상태에서 그룹함수 실행
group by가 없다 -> 전체 테이블에 대해 그룹함수 실행
결과의 행의 개수가 달라서 오류가 나는 경우
예시
# count는 결과를 하나로 압축하지만 *은 행의 개수만큼의 결과가 나온다.
select *, count(id)
from emp;만약 에러가 나온다면 아래의 코드를 실행시켜본다.
SET SESSION sql_mode = 'NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES'; SQL 실행순서
from,joinon,where(여기까지 실행되면 2차 테이블 완성)group by(여기까지 실행되면 3차 테이블 완성)- 그룹함수
havingorder bylimit
Having
그룹화 된 상태에서 조건을 걸 때 사용
서브쿼리
쿼리의 실행결과 또한 다른 쿼리의 입력 테이블이 될 수 있다.
create table ids(
id int
);
insert into ids
set id = 1;
insert into ids
set id = 2;
insert into ids
set id = 3;
select *
from ids;
select *
from (
select 1 as `id`
union
select 2
union
select 3
) as ids_temp;ids_temp라는 alias를 가진 쿼리 결과가 ids와 같은 테이블처럼 취급될 수 있음을 알 수 있다.
Union
테이블아래에 테이블을 그대로 갖다 붙임
구조가 다르면 에러난다
union이 안되는 예시
| id | name |
|---|---|
| 1 | Kim |
| deptId | salary | bossId |
|---|---|---|
| 8 | 5000 | 3 |
위의 두 테이블은 컬럼 구조가 다르기 때문에 UNION할 수 없다.
튜플 값이 같은 것들을 union하면 하나로 퉁쳐진다
이럴 땐 union all을 이용한다.
# 하나의 쿼리로 최고액연봉자와 최저역연봉자의 이름과 연봉
(
select name as `사원명`, salary as `연봉`
order by salary desc
limit 1
)
union all
(
select name as `사원명`, salary as `연봉`
from emp
order by salary asc
limit 1
);Outer join
inner join에서는 매칭되지 않은 튜플은 검색에서 제외되지만
outer join에서는 매칭되지 않은 튜플도 검색목록에 나온다.
left outer join: 조인을 출력하되, 모든 왼쪽튜플을 출력한다. 오른쪽 튜플은 null이 될 수도 있다.right outer join: 조인을 출력하되, 모든 오른쪽튜플을 출력한다. 왼쪽 튜플은 null이 될 수도 있다.
예시
| id | name |
|---|---|
| 1 | 홍보 |
| 2 | 기획 |
| 3 | IT |
| id | name | deptId | salary |
|---|---|---|---|
| 1 | 홍길동 | 1 | 6000 |
| 2 | 홍길순 | 1 | 6000 |
| 3 | 임꺽정 | 2 | 4000 |
이전의 dept테이블에 IT 부서가 새로 생겼다고 가정해보자.
아직 IT부서에는 직원이 배정되지 않았다.
# 전 사원에 대하여, [부서명, 사원번호, 사원명] 양식으로 출력
## IT부서는 [IT, NULL, NULL] 으로 출력
select d.name as `부서명`, e.id as `사원번호`, e.name as `사원명`
from emp as e right join dept as d on deptId = d.id;
# 전 사원에 대하여, [부서명, 사원번호, 사원명] 양식으로 출력
## IT부서는 [IT, 0, -] 으로 출력
select d.name as `부서명`, ifnull(e.id,0) as `사원번호`, ifnull(e.name,'-') as `사원명`
from emp as e right join dept as d on deptId = d.id;
# 모든 부서별, 최고연봉, IT부서는 0원으로 표시
select d.name as `부서명`, ifnull(max(salary), 0) as `최고연봉`
from emp as e right join dept as d on deptId = d.id
group by deptId;
# 모든 부서별, 최저연봉, IT부서는 0원으로 표시
select d.name as `부서명`, ifnull(min(salary), 0) as `최저연봉`
from emp as e right join dept as d on deptId = d.id
group by deptId;
# 모든 부서별, 평균연봉, IT부서는 0원으로 표시
select d.name as `부서명`, ifnull(avg(salary), 0) as `평균연봉`
from emp as e right join dept as d on deptId = d.id
group by deptId;
ifnull(val, default)은 val이 null인 경우 default값을 출력한다.
추가)쿼리 옵티마이저
쿼리를 최적화하려함
# 번대로 최고연봉자가 2명이상이면 입사일이 늦고 번호가 낮은 순에서 1명을 뽑는다고 하자
select *
from(
select
d2.name as `부서명`,
e.name as `사원명`,
date(e.regDate) as `사원입사일`,
format(salary, 0) as `연봉`
from (
select
deptId, max(salary) as max_salary
from emp as e
inner join dept as d on deptId = d.id
group by deptId) as m
inner join emp as e on m.deptId = e.deptId
inner join dept as d2 on e.deptId = d2.id
where salary = max_salary
order by e.regDate, e.id desc) as e2 #여기를 반대로 설정해보자
group by e2.`부서명`;이건 제대로 된 결과가 나오지 않음
마지막 group by 이후로 order by asc 가 나올 수도 있다고 쿼리 옵티마이저가 예측을 해서 order by desc가 의미없는 문장이라고 함부로 판단하고 실행하지 않는다.
대신
order by order by e.regDate, e.id desc
limit 10 # 리밋추가이런 식으로 하면 제대로 된 값이 나온다.
limit 때문에 이전의 order by 를 무시하면 안되는 상황이 되었기 때문이다.
금일의 과제
각 부서별 사원수
# 각 부서별 사원수
select d.name as `부서명`, count(e.id) as `사원수`
from dept as d left outer join emp as e on d.id = e.deptId
group by d.id;더하기 계산기
package org.example;
public class Polynomial {
String exp;
public Polynomial(String exp) {
this.exp = exp.replaceAll("\\p{Z}", "");
}
int calc() {
char prevOp = '+';
int res = 0;
StringBuilder sb = new StringBuilder();
for (int i = 0; i < exp.length(); i++) {
if (Character.isDigit(exp.charAt(i))) {
sb.append(exp.charAt(i));
} else {
res += Long.parseLong(sb.toString());
sb = new StringBuilder();
}
}
res += Long.parseLong(sb.toString());
return res;
}
}
백엔드 희망