Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글

LIMIT

SELECT로 선택하는 개수를 제한해준다.
먼저 그냥 SELECT를 썼을 때

mysql> SELECT title FROM books;
+-----------------------------------------------------+
| title                                               |
+-----------------------------------------------------+
| The Namesake                                        |
| Norse Mythology                                     |
| Interpreter of Maladies                             |
| A Hologram for the King: A Novel                    |
| The Circle                                          |
| The Amazing Adventures of Kavalier & Clay           |
| Just Kids                                           |
| A Heartbreaking Work of Staggering Genius           |
| Coraline                                            |
| What We Talk About When We Talk About Love: Stories |
| Where I'm Calling From: Selected Stories            |
| White Noise                                         |
| Cannery Row                                         |
| Oblivion: Stories                                   |
| Consider the Lobster                                |
| 10% Happier                                         |
| fake_book                                           |
| Lincoln In The Bardo                                |
+-----------------------------------------------------+
18 rows in set (0.00 sec)

LIMIT 와 함께 써보자.

mysql> SELECT title FROM books LIMIT 5;
+----------------------------------+
| title                            |
+----------------------------------+
| The Namesake                     |
| Norse Mythology                  |
| Interpreter of Maladies          |
| A Hologram for the King: A Novel |
| The Circle                       |
+----------------------------------+
5 rows in set (0.00 sec)

LIMIT을 5로 주니깐 결과값이 5개만 나오게 되었다.

좀 더 구체적인 자료를 얻어보자. 최신 책들 5개를 SELECT 해보자.

mysql> SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 5;
+----------------------------------+---------------+
| title                            | released_year |
+----------------------------------+---------------+
| Lincoln In The Bardo             |          2017 |
| Norse Mythology                  |          2016 |
| 10% Happier                      |          2014 |
| The Circle                       |          2013 |
| A Hologram for the King: A Novel |          2012 |
+----------------------------------+---------------+
5 rows in set (0.00 sec)

• SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 0,5;
  이 SQL문은 어떻게 실행될까?

mysql> SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 0,5;
+----------------------------------+---------------+
| title                            | released_year |
+----------------------------------+---------------+
| Lincoln In The Bardo             |          2017 |
| Norse Mythology                  |          2016 |
| 10% Happier                      |          2014 |
| The Circle                       |          2013 |
| A Hologram for the King: A Novel |          2012 |
+----------------------------------+---------------+
5 rows in set (0.00 sec)

This is a syntax that allows us to specify a starting poin and then how many we want to go from there.
LIMIT 뒤에 두개의 인자가 들어갈 때, 첫번째 인자는 starting point, 두번째 인자는 길이(몇개를 선택할 것인지)이다.
하지만 여기서는 앞에서 String Function들을 쓸때와는 조금 다른게 starting point가 0부터 이다. So the first row in a table is zero with row.

예를 들어

mysql> SELECT SUBSTRING('Hello World',1,5);
+------------------------------+
| SUBSTRING('Hello World',1,5) |
+------------------------------+
| Hello                        |
+------------------------------+
1 row in set (0.00 sec)

이렇게 하게 되면 'Hello World'가 0부터 시작하는 것이 아닌, 1부터 시작에서 H의 인덱스가 1이다. 하지만 위의 LIMIT에서는 0이 첫번째 row가 되는 것이다.
물론 String과 row는 다르긴 하지만 조금 헷갈릴 수 있는 부분이다.

그래서 이번에는 SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 2,5;이 문장을 실행해보면,

mysql> SELECT title, released_year FROM books ORDER BY released_year DESC LIMIT 2,5;
+----------------------------------+---------------+
| title                            | released_year |
+----------------------------------+---------------+
| 10% Happier                      |          2014 |
| The Circle                       |          2013 |
| A Hologram for the King: A Novel |          2012 |
| Just Kids                        |          2010 |
| Consider the Lobster             |          2005 |
+----------------------------------+---------------+
5 rows in set (0.00 sec)

이렇게 0,1,2 번째에 있던 10% Happier부터 시작해 5개의 책을 고르게 되었다.

mysql> SELECT title FROM books LIMIT 11,1;
+-------------+
| title       |
+-------------+
| White Noise |
+-------------+
1 row in set (0.00 sec)

11번째에 있는 책을 골랐다.

++추가로 또 신경써야 될 게 SQL을 작성하는 순서이다.
LIMIT은 ORDER BY뒤에 와야 한다.

mysql> SELECT title, released_year FROM books LIMIT 3,5 ORDER BY released_year;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY released_year' at line 1

mysql> SELECT title, released_year FROM books ORDER BY released_year LIMIT 3,5;
+-------------------------------------------+---------------+
| title                                     | released_year |
+-------------------------------------------+---------------+
| Where I'm Calling From: Selected Stories  |          1989 |
| Interpreter of Maladies                   |          1996 |
| The Amazing Adventures of Kavalier & Clay |          2000 |
| fake_book                                 |          2001 |
| A Heartbreaking Work of Staggering Genius |          2001 |
+-------------------------------------------+---------------+
5 rows in set (0.00 sec)

이런식으로 LIMIT이 ORDER BY앞에 오면 Syntax Error가 발생한다.