프로그래머스 SQL 고득점 Kit - SELECT

프로그래머스에서 SQL 고득점 Kit을 풀고 추가적으로 조사하여 얻은 지식을 정리하는 문서다. 문제를 풀 때는 익숙한 MySQL을 활용하였다.

SELECT

기본적으로 SELECT는 레코드를 조회하는 명령어기 때문에 특정 조건(또는 모든)의 칼럼 값이나 문자열뿐 아니라 함수 실행의 결괏값을 얻어올 수 있다. 대표적인 예로 현재 시간을 반환하는 NOW() 함수를 SELECT 하면 다음과 같은 결과를 얻을 수 있다.

MariaDB [(none)]> SELECT NOW();
+---------------------+
| NOW()               |
+---------------------+
| 2021-02-01 23:27:53 |
+---------------------+
1 row in set (0.000 sec)

보통 특정 테이블에서 레코드를 조회하는 것이 목적이지만 테이블이 필요 없는 경우 'DUAL'이라는 더미 테이블을 활용할 수 있다. 이는 FROM 명령어가 무조건 포함되어야 한다거나 하는 제약사항이 있을 때 활용할 수 있다.

MariaDB [vulnerable_db]> SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA="vulnerable_db";
+------------+
| table_name |
+------------+
| user_table |
+------------+
1 row in set (0.010 sec)

MariaDB [vulnerable_db]> SELECT NOW() FROM DUAL;
+---------------------+
| NOW()               |
+---------------------+
| 2021-02-01 23:33:56 |
+---------------------+
1 row in set (0.000 sec)

위의 쿼리에서는 현재 'vulnerable_db' 데이터베이스에 'user_table' 테이블만 존재하고 있지만 FROM 명령어에 존재하지 않는 DUAL 테이블을 넘겨줘도 문제없이 쿼리가 실행되는 것을 볼 수 있다.

WHERE

FROM 명령어로 지정된 테이블에서 특정 조건에 해당되는 레코드만 조회하고 싶다면 WHERE 명령어를 사용할 수 있다. 이 명령어에 주어지는 조건을 모두 만족시키는 레코드만 SELECT로 조회되며 Boolean 값인 true, false를 활용할 수 있다.

MariaDB [vulnerable_db]> select * from user_table;
+------+-------+
| name | pw    |
+------+-------+
| aaaa | 11111 |
| bbbb | 'cde  |
| cccc | edf'# |
+------+-------+
3 rows in set (0.000 sec)

MariaDB [vulnerable_db]> select * from user_table where name='aaaa';
+------+-------+
| name | pw    |
+------+-------+
| aaaa | 11111 |
+------+-------+
1 row in set (0.000 sec)

3개의 레코드 중 'name' 칼럼의 값이 'aaaa'인 레코드만 조회된 것을 볼 수 있다. 모든 레코드를 조회하고 싶다면 WHERE 명령어에 항상 참이 되는 조건인 true를 넘기거나 아무런 조건도 넘기지 않으면 된다.

MariaDB [vulnerable_db]> select * from user_table where true;
+------+-------+
| name | pw    |
+------+-------+
| aaaa | 11111 |
| bbbb | 'cde  |
| cccc | edf'# |
+------+-------+
3 rows in set (0.000 sec)

MariaDB [vulnerable_db]> select * from user_table;
+------+-------+
| name | pw    |
+------+-------+
| aaaa | 11111 |
| bbbb | 'cde  |
| cccc | edf'# |
+------+-------+
3 rows in set (0.000 sec)

AS

SELECT로 조회할 수 있는 대상은 위에서 언급했듯이 특정 테이블의 칼럼이나 표현식, 함수 호출만 될 수 있다. 이는 레코드 출력 시 AS 명령어를 사용하여 다른 이름으로 출력될 수 있다.

MariaDB [vulnerable_db]> select count(name) as 'How many users?' from user_table;
+-----------------+
| How many users? |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.000 sec)

위의 쿼리에서는 레코드의 개수를 세는 COUNT() 함수를 사용하여 'user_table' 테이블의 'name' 칼럼의 레코드의 개수를 세어 'How many users?'라는 이름의 칼럼으로 바꿔서 제공하고 있다. 비슷하게 다른 테이블과 연계하여 데이터를 추출할 때 테이블 이름을 변경해서 참조할 수도 있다.

MariaDB [vulnerable_db]> select * from user_table;
+------+---------+-------------+
| id   | name    | pw          |
+------+---------+-------------+
|    1 | Kwonkyu | p@ssw0rd    |
|    2 | Jason   | Bourne_NO.1 |
|    3 | guest   | guest       |
+------+---------+-------------+
3 rows in set (0.000 sec)

MariaDB [vulnerable_db]> select * from data_table;
+------+-------------------------------------+
| id   | data                                |
+------+-------------------------------------+
|    1 | This data is secret!                |
|    2 | Thus nobody should able to see this |
|    3 | Knock Knock                         |
+------+-------------------------------------+
3 rows in set (0.000 sec)

MariaDB [vulnerable_db]> select user.name, data.data from user_table as user, data_table as data where user.id=data.id;
+---------+-------------------------------------+
| name    | data                                |
+---------+-------------------------------------+
| Kwonkyu | This data is secret!                |
| Jason   | Thus nobody should able to see this |
| guest   | Knock Knock                         |
+---------+-------------------------------------+
3 rows in set (0.000 sec)

'user_table'과 'data_table' 테이블에서 같은 id를 가지는 레코드끼리 묶어서 출력하고 있다. 이때 'user_table' 테이블은 AS 명령어로 'user'로, 'data_table' 테이블은 비슷하게 'data'로 이름을 변경하여 사용하고 있다. 한번 FROM 절에서 선언해서 사용하면 이전이든 이후든 쿼리 내에서는 해당 테이블 이름으로 사용할 수 있다.

ORDER BY

SELECT 명령어로 레코드를 조회할 때는 ORDER BY 명령어를 사용하여 특정 칼럼을 기준으로 오름차순(ASC) 또는 내림차순(DESC)으로 출력되는 순서를 지정할 수 있다. 기본적으로는 오름차순으로 정렬하고 있으며 DESC를 명시하면 다음처럼 내림차순으로 정렬되는 것을 볼 수 있다.

MariaDB [vulnerable_db]> select * from user_table order by id;
+------+---------+-------------+
| id   | name    | pw          |
+------+---------+-------------+
|    1 | Kwonkyu | p@ssw0rd    |
|    2 | Jason   | Bourne_NO.1 |
|    3 | guest   | guest       |
+------+---------+-------------+
3 rows in set (0.000 sec)

MariaDB [vulnerable_db]> select * from user_table order by id desc;
+------+---------+-------------+
| id   | name    | pw          |
+------+---------+-------------+
|    3 | guest   | guest       |
|    2 | Jason   | Bourne_NO.1 |
|    1 | Kwonkyu | p@ssw0rd    |
+------+---------+-------------+
3 rows in set (0.000 sec)

그렇다면 중복되는 값을 갖는 레코드는 어떻게 정렬될까? 이때는 다른 칼럼을 기준으로 한번 더 정렬시킬 수 있다.

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE ORDER BY NAME;
+------+---------+-------------+
| id   | name    | pw          |
+------+---------+-------------+
|    3 | guest   | guest       |
|    2 | Jason   | Bourne_NO.1 |
|    4 | Jason   | Mraz        |
|    5 | Jason   | Friday 13th |
|    1 | Kwonkyu | p@ssw0rd    |
+------+---------+-------------+
5 rows in set (0.000 sec)

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE ORDER BY NAME, ID;
+------+---------+-------------+
| id   | name    | pw          |
+------+---------+-------------+
|    3 | guest   | guest       |
|    2 | Jason   | Bourne_NO.1 |
|    4 | Jason   | Mraz        |
|    5 | Jason   | Friday 13th |
|    1 | Kwonkyu | p@ssw0rd    |
+------+---------+-------------+
5 rows in set (0.000 sec)

레코드 특성상 위의 쿼리에서는 'NAME' 칼럼으로만 정렬했을 때와 'NAME', 'ID' 칼럼으로 정렬했을 때의 차이가 없지만 실제로는 'NAME' 칼럼으로 정렬한 후 중복되는 레코드에 한해서 'ID' 칼럼을 기준으로 다시 정렬된 레코드가 반환되었다. 이를 확인하기 위해 역순으로 정렬해보면 다음과 같다.

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE ORDER BY NAME, ID DESC;
+------+---------+-------------+
| id   | name    | pw          |
+------+---------+-------------+
|    3 | guest   | guest       |
|    5 | Jason   | Friday 13th |
|    4 | Jason   | Mraz        |
|    2 | Jason   | Bourne_NO.1 |
|    1 | Kwonkyu | p@ssw0rd    |
+------+---------+-------------+
5 rows in set (0.000 sec)

기존에 'id' 칼럼에서 2, 4, 5로 정렬되던 'Jason' 레코드가 이번에는 내림차순으로 5, 4, 2로 정렬된 것을 볼 수 있다. 이를 제외한 나머지 'guest', 'Kwonkyu' 레코드는 아무런 변동이 없는 것으로 보아 'ID' 칼럼으로 정렬할 때 지정한 내림차순 명령은 'NAME' 칼럼에는 적용되지 않는 것을 볼 수 있다.

GROUP BY

레코드를 특정 칼럼 값으로 묶어서 다루고 싶다면 어떨까? 이때는 GROUP BY 명령어를 사용할 수 있다.

MariaDB [vulnerable_db]> select * from user_table;
+------+---------+-----------------------------+
| id   | name    | company                     |
+------+---------+-----------------------------+
|    1 | Kwnokyu | Koreatech                   |
|    2 | Jason   | Shadow Company              |
|    3 | Prodo   | The Fellowship of the Rings |
|    4 | Sauron  | The Fellowship of the Rings |
|    5 | Saruman | The Fellowship of the Rings |
|    6 | John    | Shadow Company              |
|    7 | Tyler   | Shadow Company              |
|    8 | Jaemin  | Koreatech                   |
+------+---------+-----------------------------+
8 rows in set (0.000 sec)

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE GROUP BY COMPANY;
+------+---------+-----------------------------+
| id   | name    | company                     |
+------+---------+-----------------------------+
|    1 | Kwnokyu | Koreatech                   |
|    2 | Jason   | Shadow Company              |
|    3 | Prodo   | The Fellowship of the Rings |
+------+---------+-----------------------------+
3 rows in set (0.000 sec)

이때 GROUP BY로 묶은 데이터는 해당 그룹의 첫 번째 레코드만 출력하는 것을 볼 수 있다. 이를 활용하는 건 집계 함수(SUM, COUNT 등)를 활용할 때 유용한데 다음처럼 'company' 칼럼으로 그룹화하여 COUNT 함수를 사용함으로써 각 그룹에 몇 개의 레코드가 포함되어 있는지 확인할 수 있다.

MariaDB [vulnerable_db]> SELECT COUNT(*) AS 'GROUP_SIZE' FROM USER_TABLE GROUP BY COMPANY;
+------------+
| GROUP_SIZE |
+------------+
|          2 |
|          3 |
|          3 |
+------------+
3 rows in set (0.000 sec)

자세한 활용은 추후 GROUP BY 관련 포스팅에서 다루겠다.

LIKE

SELECT로 레코드를 조회할 때 특정 값과 완전히 일치하는 게 아니라 일부분 일치하는 레코드를 조회할 때 LIKE 명령어를 사용할 수 있다. 이는 다른 검색 시스템에서도 많이 활용되는 와일드카드('*', '?') 문자를 활용하는 것과 비슷한데 MySQL에서는 '%'와 '_'를 활용한다. 각각 n개의 문자와 1개의 문자를 대체하는 용도로 사용된다.

MariaDB [vulnerable_db]> select * from user_table;
+------+---------+-----------------------------+
| id   | name    | company                     |
+------+---------+-----------------------------+
|    1 | Kwnokyu | Koreatech                   |
|    2 | Jason   | Shadow Company              |
|    3 | Prodo   | The Fellowship of the Rings |
|    4 | Sauron  | The Fellowship of the Rings |
|    5 | Saruman | The Fellowship of the Rings |
|    6 | John    | Shadow Company              |
|    7 | Tyler   | Shadow Company              |
|    8 | Jaemin  | Koreatech                   |
+------+---------+-----------------------------+
8 rows in set (0.000 sec)

예를 들어 위와 같은 테이블에서 S로 시작하는 이름의 레코드를 찾고 싶다면 몇 개의 글자가 오든 상관없는 '%' 와일드카드를 사용하여 'S%'처럼 LIKE 명령어를 활용할 수 있다. 이는 맨 처음 글자가 'S'면 뒤에 어떤 글자가 오든(또는 아예 없어도) 상관없이 해당 레코드를 출력한다.

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE WHERE NAME LIKE 'S%';
+------+---------+-----------------------------+
| id   | name    | company                     |
+------+---------+-----------------------------+
|    4 | Sauron  | The Fellowship of the Rings |
|    5 | Saruman | The Fellowship of the Rings |
+------+---------+-----------------------------+
2 rows in set (0.000 sec)

이름의 4번째 글자가 'o'인 레코드는 정확히 한 글자를 대체하는 '_' 와일드카드를 사용하여 '___o%'처럼 활용할 수 있다. 이는 첫 세 글자('___')는 아무거나 와도 좋지만 네 번째 글자는 'o'여야 하며 이후 다섯 번째 글자부터는 아무 글자나 와도 상관없다('%')는 것을 의미한다.

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE WHERE NAME LIKE '___o%';
+------+---------+----------------+
| id   | name    | company        |
+------+---------+----------------+
|    1 | Kwnokyu | Koreatech      |
|    2 | Jason   | Shadow Company |
+------+---------+----------------+
2 rows in set (0.000 sec)

4번째 글자가 'o'인 'Kwnokyu'와 'Jason'이 조회된 것을 볼 수 있다.

LIMIT

출력되는 레코드의 개수를 제한하기 위해 LIMIT 명령어를 사용할 수 있다. 이는 조회된 레코드에서 처음부터 일정 부분만 출력하는데 레코드의 중간부터 출력할 수도 있다. 이 때는 두 개의 파라미터를 사용한다. 예를 들어 위에서 사용한 예시 테이블의 'The Fellowship of the Rings'라는 'company'에서 1명만 출력하고 싶다고 할 때 다음과 같이 LIMIT 명령어 뒤에 개수를 붙여서 사용할 수 있다.

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE WHERE COMPANY='The Fellowship of the Rings' LIMIT 1;
+------+-------+-----------------------------+
| id   | name  | company                     |
+------+-------+-----------------------------+
|    3 | Prodo | The Fellowship of the Rings |
+------+-------+-----------------------------+
1 row in set (0.014 sec)

현재 테이블에는 해당 'company'에 3개의 레코드가 존재한다. 이 중 두 번째 레코드부터 두 개를 출력하고 싶다면 다음처럼 LIMIT 명령어 뒤에 개수와 시작점을 붙여서 사용할 수 있다.

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE WHERE COMPANY='The Fellowship of the Rings' LIMIT 1,2;
+------+---------+-----------------------------+
| id   | name    | company                     |
+------+---------+-----------------------------+
|    4 | Sauron  | The Fellowship of the Rings |
|    5 | Saruman | The Fellowship of the Rings |
+------+---------+-----------------------------+
2 rows in set (0.000 sec)

이때 위의 명령과 비슷하게 LIMIT 1을 했는데 위의 명령어는 맨 처음 레코드가 나오고 두 번째 명령어는 두 번째 레코드부터 두 개가 출력될까? 이는 파라미터가 두 개인 경우 첫 번째로 오는 파라미터(1)의 역할이 바뀌기 때문이다. LIMIT 1처럼 한 개의 파라미터가 사용됐다면 이는 1개의 레코드만 출력하라는 의미지만 LIMIT 1, 2처럼 두 개의 파라미터가 사용됐다면 이는 두 번째 레코드에서 2개의 레코드만 출력하라는 의미다. 파라미터로 1을 넘겼는데 왜 두 번째 레코드일까? 이는 OFFSET 명령어가 0부터 세는 Zero-based numbering을 사용하기 때문이다. 즉 OFFSET 1은 두 번째 레코드부터 LIMIT 하라는 것과 동일하다.

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE WHERE COMPANY='The Fellowship of the Rings' LIMIT 2 OFFSET 1;
+------+---------+-----------------------------+
| id   | name    | company                     |
+------+---------+-----------------------------+
|    4 | Sauron  | The Fellowship of the Rings |
|    5 | Saruman | The Fellowship of the Rings |
+------+---------+-----------------------------+
2 rows in set (0.000 sec)

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE WHERE COMPANY='The Fellowship of the Rings' LIMIT 2 OFFSET 0;
+------+--------+-----------------------------+
| id   | name   | company                     |
+------+--------+-----------------------------+
|    3 | Prodo  | The Fellowship of the Rings |
|    4 | Sauron | The Fellowship of the Rings |
+------+--------+-----------------------------+
2 rows in set (0.000 sec)

명확하게 하기 위해 위처럼 OFFSET을 따로 명시할 수도 있다. Prodo, Sauron, Saruman 세 개의 레코드에서 LIMIT 2 OFFSET 1을 하면 두 번째 레코드부터 2개의 레코드가 출력되는 것을 볼 수 있으며 LIMIT 2 OFFSET 0을 하면 첫 번째 레코드부터 2개의 레코드가 출력되는 것을 볼 수 있다.

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE LIMIT 1 OFFSET 4;
+------+---------+-----------------------------+
| id   | name    | company                     |
+------+---------+-----------------------------+
|    5 | Saruman | The Fellowship of the Rings |
+------+---------+-----------------------------+
1 row in set (0.000 sec)

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE LIMIT 1 OFFSET 5;
+------+------+----------------+
| id   | name | company        |
+------+------+----------------+
|    6 | John | Shadow Company |
+------+------+----------------+
1 row in set (0.000 sec)

MariaDB [vulnerable_db]> SELECT * FROM USER_TABLE LIMIT 1 OFFSET 6;
+------+-------+----------------+
| id   | name  | company        |
+------+-------+----------------+
|    7 | Tyler | Shadow Company |
+------+-------+----------------+
1 row in set (0.000 sec)

이는 SQL Injection에서 테이블의 특정 레코드를 추출하기 위해 위처럼 사용되기도 한다. 출력 레코드는 1개로 제한하면서 그 OFFSET을 변경시켜 테이블 이곳저곳에 있는 레코드를 하나씩 추출할 수 있는 것이다. 이는 웹 애플리케이션에서 쿼리 결과로 한 개의 레코드만 받아들일 때 활용할 수도 있다.

 

 

JOIN이나 GROUP BY, PARTITION 같은 명령어들은 추후 포스팅에서 다루겠다.