6장 - SQL 기본 <SELECT 문 2>

🚨 SELECT 문을 한번에 정리하려고 했었는데 생각보다 양이 너무 많아서 나눠서 정리할 필요성을 느꼈다. 이번시간에는 SELECT 부가적인 옵션 ORDER BY와 GROUP BY에 대해 배워볼 것이다.

ORDER BY

기본 사용법

ORDER BY절은 결과가 출력되는 순서를 조절하는 구문이다. 먼저 가입한 순서로 회원들을 출력해보자.

select name, mDate from usertbl order by mDate;

ORDER BY는 기본적으로 오름차순(Ascending)으로 정렬된다. 내림차순(Descending)으로 정렬하기 위해서는 열 이름 뒤에 DESC라고 적어주면 된다.

select name, mDate from usertbl order by mDate DESC;

이번에는 여러개로 정렬해보자. 키가 큰 순서로 정렬하되 키가 같은경우 이름순으로 정렬되도록 하려면?

select name, height from usertbl order by height DESC, name ASC;

마지막으로 ORDER BY는 SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY 중에서 가장 맨 뒤에 와야 한다는 사실을 잊지말자. 그리고 ORDER BY는 MySQL 성능을 상당히 떨어뜨릴 소지가 있으니 꼭 필요한 경우가 아니라면 사용하지 말자.

LIMIT

출력하는 개수를 제한할때 사용한다. 딱 원하는 개수만 지정해서 출력할 수 있기 때문에 MySQL의 부담을 많이 줄여주는 방법이다.

상위 10개만 출력하고 싶을 때는 LIMIT N구문을 사용하면 된다.

select emp_no, hire_date from employees order by hire_date ASC, emp_no ASC limit 10;

만약 5번째 부터(=앞에 5개 건너뛰고) 10개만 출력하고 싶을때는 LIMIT 시작, 개수 또는 LIMIT 개수 OFFSET 시작 형식으로 사용할 수 있다.

select emp_no, hire_date from employees order by hire_date ASC, emp_no ASC limit 5, 10;

GROUP BY

기본 사용법

GROUP BY는 말 그대로 그룹으로 묶어주는 역할을 한다. 그리고 GROUP BY는 집계함수와 주로 같이 쓰인다.

집계함수에는 다음과 같은 함수가 존재한다.

• SUM() : 합계, 총합을 구한다.
• AVG() : 평균을 구한다.
• MIN()/MAX() : 최솟값/최댓값을 구한다.
• COUNT() : 행의 개수를 센다.
• COUNT(DISTINCT) : 행의 개수를 센다(중복은 1개만 인정)
• STDEV() : 표준편차를 구한다.
• VAR_SAMP() : 분산을 구한다.

실습 1

자. 이제 예를 들어 사용자별로 구매 개수를 구하는 SQL문을 작성해서 실행시켜보겠다.

select userID, sum(amount) from buytbl
group by userID;

SUM(amount)의 결과 열에 함수 이름이 그대로 나와서 보기가 조금 안좋다. 이는 전에 배운 as를 사용하면 쉽게 해결할 수 있다.

select userID as `사용자 아이디`, sum(amount) as `총 구매 개수` from buytbl
group by userID;

이번에는 구매액의 총합을 출력해보자. 구매액은 가격*수량이므로 이 역시 SUM()을 사용하면 된다.

select userID as `사용자 아이디`, sum(price * amount) as `총 구매액`
from buytbl group by userID;

실습2

다른 예를 살펴보자. 가장 큰 키와 가장 작은 키의 회원 이름과 키를 출력하는 쿼리를 만들어서 실행해보자.

select name, MAX(height), MIN(height)
from usertbl;

가장 큰키와 가장 작은키는 나왔으나 이름은 하나뿐이라서 원하는 결과가 아니다. 다음과 같이 고쳐보면 어떨까?

select name, MAX(height), MIN(height)
from usertbl group by name;

이 역시 내가 원하는 결과가 아니다. 그냥 모두 다 나왔다. 이런 경우는 앞에서 배운 서브쿼리와 조합을 하는 것이 제일 수월하다.

select name, height
from usertbl
where height = (select max(height) from usertbl) or height = (select min(height) from usertbl)

실습3

이번에는 휴대폰이 있는 사용자의 수를 카운트해보자.

select count(*) from usertbl; -- 전체 사용자 수는 10명
select count(mobile1) AS `휴대폰이 있는 사용자 수` from usertbl; -- 휴대폰이 있는 사용자수는 8명

휴대폰이 있는 회원만 카운트하려면 휴대폰 열이름을 지정해야 한다. 그럴 경우 NULL값은 제외하고 카운트해준다.

HAVING 절

앞서 사용자별 총 구매액을 구해봤는데 이번에는 총 구매액이 1000이상인 사용자에게만 사은품을 증정하고 싶다. 그럼 WHERE을 쓰면 될까? 🤔

select userID as `사용자 아이디`, sum(price * amount) as `총 구매액`
from buytbl 
where sum(price * amount) > 1000
group by userID;

🚨 Error Code: 1111. Invalid use of group function 0.000 sec 이런식으로 에러가 뜰텐데 이는 집계함수는 WHERE절과 같이 사용하는 것이 불가능하기 때문에 나타난다. 이럴때 HAVING을 사용하면 된다. WHERE과 비슷한 개념으로 조건을 제한하지만, 집계함수에 대해서 조건을 제한하는 것이라고 생각하면 된다.

그리고 HAVING은 GROUP BY 뒤에 써줘야 한다.

select userID as `사용자 아이디`, sum(price * amount) as `총 구매액`
from buytbl 
group by userID
having sum(price * amount) > 1000;

ROLLUP

order by와 group by는 많이 사용해봤는데 rollup은 정처기 시험 준비 이후로는 실습은 처음이다. 총합과 중간 합계가 필요하다면 group by와 함께 WITH ROLLUP문을 사용하면 된다고 한다. 만약 분류별로 합계 및 그 총합을 구하고 싶다면 다음의 구문을 사용하자.

select num, groupName, SUM(price * amount) AS `비용`
from buytbl
group by groupName, num
with rollup;

중간에 num열이 NULL로 되어 있는 추가된 행이 각 그룹의 소합계를 의미하고 마지막 행은 각 소합계의 합계인 총합계가 결과로 나왔다.

위 구문에서 num은 각 항목이 보이는 효과를 위해서 넣어 준 것이며 만약 소합계및 총합계만 필요하다면 num을 빼면 된다.

select num, groupName, SUM(price * amount) AS `비용`
from buytbl
group by groupName
with rollup;

✍ 이로써 SELECT문 기본은 모두 살펴봤다. SELECT문은 가장 많이 사용되는 쿼리문이며 가장 쉬운 동시에 가장 어려운 부분이기도 하다.