Mysql - select문 응용

Select문 응용

mysql의 select문의 응용방법을 살펴보도록 하겠습니다. 최근 모 기업의 코딩테스트를 보았는데. 마지막 문제에서 간단한 쿼리 문제가 나왔었습니다. 근데 시간도 시간이지만 자신이 없어서 인지 뺑뺑돌다가 결국 시간이 다되어 제대로 풀어보지도 못했습니다. 때문에 sql에 대한 공부도 조금씩 다시 해나아갈 예정입니다.

  

예제 DB 자료

이번 포스팅의 예제에서 사용될 Sampl DB는 Mysql에서 제공하는 World.db입니다. 아래의 경로는 mysql 홈페이지에서 제공하는 world.db 설치방법 document 링크입니다.

https://dev.mysql.com/doc/world-setup/en/world-setup-installation.html

 

1. 검색결과의 정렬(order by)

x
mysql> select * from city where countrycode='KOR' order by population (ASC 생략);
+------+------------+-------------+---------------+------------+
| ID   | Name       | CountryCode | District      | Population |
+------+------------+-------------+---------------+------------+
| 2400 | Mun-gyong  | KOR         | Kyongsangbuk  |      92239 |
| 2399 | Tonghae    | KOR         | Kang-won      |      95472 |
| 2398 | Namwon     | KOR         | Chollabuk     |     103544 |
...

검색 결과를 정렬하기 위해서는 select, from, where 이후 order by '필드명' 을 이용하면 됩니다. 기본적으로 order by는 오름차순(ASC)으로 정렬됩니다.

 

mysql> select * from city where countrycode='KOR' order by population desc;
+------+------------+-------------+---------------+------------+
| ID   | Name       | CountryCode | District      | Population |
+------+------------+-------------+---------------+------------+
| 2331 | Seoul      | KOR         | Seoul         |    9981619 |
| 2332 | Pusan      | KOR         | Pusan         |    3804522 |
| 2333 | Inchon     | KOR         | Inchon        |    2559424 |

내림차순의 경우에는 뒤에 desc를 붙혀주면 됩니다.

 

 

2. 테이블 집약(count)

mysql> select count(*) from city where countrycode = 'KOR';
+----------+
| count(*) |
+----------+
|       70 |
+----------+
1 row in set (0.00 sec)

테이블에 포함된 row(행)의 개수를 세기 위해서는 select count(*)를 이용하면 됩니다.

 

 

3. 테이블 연산(min, max, sum, avg)

x
mysql> select min(population), max(population), sum(population), avg(population) from city where countrycode='KOR';
+-----------------+-----------------+-----------------+-----------------+
| min(population) | max(population) | sum(population) | avg(population) |
+-----------------+-----------------+-----------------+-----------------+
|           92239 |         9981619 |        38999893 |     557141.3286 |
+-----------------+-----------------+-----------------+-----------------+

테이블의 특정 필드의 최소값, 최대값, 합계, 평균등을 알기 위해서는 select min('필드(number)')를 이용하면 됩니다.

 

 

4. 결과를 하나의 행으로 집약(group_concat)

mysql> select group_concat(name) from city where countrycode = 'KOR' and district = 'chollabuk';
+-------------------------------------------+
| group_concat(name)                        |
+-------------------------------------------+
| Chonju,Iksan,Kunsan,Chong-up,Kimje,Namwon |
+-------------------------------------------+

select 문 사용시 원래는 하나의 열로 표시되는 것을 select group_concat('필드')를 사용하여 하나의 행으로 출력할 수 있습니다.

 

 

5. 결과를 특정 필드를 기준으로 그룹화(GROUP BY)

mysql> select count(*) from city where countrycode='kor';
+----------+
| count(*) |
+----------+
|       70 |
+----------+

집약함수 이용시 위와 같이 대상이 되는 테이블의 데이터 전체를 하나로 집약했습니다. 이를 테이블 안의 특정 필드를 기준으로 그룹화 하여 출력을 할 수도 있습니다. 바로 GROUP BY를 사용하면 됩니다.

 

mysql> select district, count(*) from city where countrycode = 'KOR' group by district;
+---------------+----------+
| district      | count(*) |
+---------------+----------+
| Seoul         |        1 |
| Pusan         |        1 |
| Inchon        |        1 |
| Taegu         |        1 |
| Taejon        |        1 |
| Kwangju       |        1 |
| Kyongsangnam  |       11 |
| Kyonggi       |       18 |
| Chollabuk     |        6 |
| Chungchongbuk |        3 |
| Kyongsangbuk  |       10 |
| Chungchongnam |        6 |
| Cheju         |        1 |
| Chollanam     |        5 |
| Kang-won      |        4 |
+---------------+----------+

위는 행의 개수를 출력하는 쿼리의 결과를 도시를 기준으로 그룹화 하여 개수를 출력한 결과입니다. 가독성을 높이기 위해 district를 같이 출력하였습니다.

 

xxxxxxxxxx
mysql> select countrycode, count(*) 
       from city 
       where countrycode = 'kor' or countrycode = 'chn' or countrycode = 'usa' 
       group by countrycode;
+-------------+----------+
| countrycode | count(*) |
+-------------+----------+
| CHN         |      363 |
| KOR         |       70 |
| USA         |      274 |
+-------------+----------+

위의 쿼리는 한국과 중국 그리고 미국의 국가별 도시의 개수를 출력하기 위한 쿼리문입니다.

 

 

6. 결과를 특정 필드를 기준으로 그룹화한것에 조건부여(HAVING)

xxxxxxxxxx
mysql> select countrycode, count(*) 
       from city 
       where countrycode = 'kor' or countrycode = 'chn' or countrycode = 'usa' 
       group by countrycode 
       having count(*) > 100;
+-------------+----------+
| countrycode | count(*) |
+-------------+----------+
| CHN         |      363 |
| USA         |      274 |
+-------------+----------+

2.2(GROUP BY)에 조건을 부여하기 위해서는 HAVING을 추가적으로 사용하면 됩니다. 위는 countrycode를 기준으로 결과를 그룹화한것에 count(*) 가 100을 초과하는 경우에만 출력하라는 조건을 부여한 쿼리문입니다. 아쉽게도 한국은 탈락이네요..

 

 

7. select, from, where, group by, having, order by

select from where group by having order by는 반드시 이 순서를 지켜 작성해야 합니다.

 

mysql> select district, count(*)
       from city 
       where countrycode='kor' 
       group by district 
       having count(*) > 5 
       order by count(*);
+---------------+----------+
| district      | count(*) |
+---------------+----------+
| Chungchongnam |        6 |
| Chollabuk     |        6 |
| Kyongsangbuk  |       10 |
| Kyongsangnam  |       11 |
| Kyonggi       |       18 |
+---------------+----------+

위의 쿼리는 countrycode가 kor인 데이터를 district별로 그룹화한 뒤 각 개수가 5개를 초과하는 city 테이블로 부터 가져와 count를 기준으로 오름차순으로 정렬해 가져온 뒤 district와 count(*)를 출력하기 위한 쿼리입니다.