Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글
WHERE 조건에 >, < 등 부등호로 조건을 줄 수 있다.
'>'(GREATER THAN) 부터 봐보자.
예를 보자.
-- 책 전부
mysql> SELECT title, released_year FROM books;
+-----------------------------------------------------+---------------+
| title | released_year |
+-----------------------------------------------------+---------------+
| The Namesake | 2003 |
| Norse Mythology | 2016 |
| Interpreter of Maladies | 1996 |
| A Hologram for the King: A Novel | 2012 |
| The Circle | 2013 |
| The Amazing Adventures of Kavalier & Clay | 2000 |
| Just Kids | 2010 |
| A Heartbreaking Work of Staggering Genius | 2001 |
| Coraline | 2003 |
| What We Talk About When We Talk About Love: Stories | 1981 |
| Where I'm Calling From: Selected Stories | 1989 |
| White Noise | 1985 |
| Cannery Row | 1945 |
| Oblivion: Stories | 2004 |
| Consider the Lobster | 2005 |
| 10% Happier | 2014 |
| fake_book | 2001 |
| Lincoln In The Bardo | 2017 |
+-----------------------------------------------------+---------------+
18 rows in set (0.00 sec)
-- 2000년도 이후에 나온책
mysql> SELECT title, released_year FROM books WHERE released_year > 2000;
+-------------------------------------------+---------------+
| title | released_year |
+-------------------------------------------+---------------+
| The Namesake | 2003 |
| Norse Mythology | 2016 |
| A Hologram for the King: A Novel | 2012 |
| The Circle | 2013 |
| Just Kids | 2010 |
| A Heartbreaking Work of Staggering Genius | 2001 |
| Coraline | 2003 |
| Oblivion: Stories | 2004 |
| Consider the Lobster | 2005 |
| 10% Happier | 2014 |
| fake_book | 2001 |
| Lincoln In The Bardo | 2017 |
+-------------------------------------------+---------------+
12 rows in set (0.01 sec)'>=' Greater Than Or Equal To
mysql> SELECT title, released_year FROM books;
+-----------------------------------------------------+---------------+
| title | released_year |
+-----------------------------------------------------+---------------+
| The Namesake | 2003 |
| Norse Mythology | 2016 |
| Interpreter of Maladies | 1996 |
| A Hologram for the King: A Novel | 2012 |
| The Circle | 2013 |
| The Amazing Adventures of Kavalier & Clay | 2000 |
| Just Kids | 2010 |
| A Heartbreaking Work of Staggering Genius | 2001 |
| Coraline | 2003 |
| What We Talk About When We Talk About Love: Stories | 1981 |
| Where I'm Calling From: Selected Stories | 1989 |
| White Noise | 1985 |
| Cannery Row | 1945 |
| Oblivion: Stories | 2004 |
| Consider the Lobster | 2005 |
| 10% Happier | 2014 |
| fake_book | 2001 |
| Lincoln In The Bardo | 2017 |
+-----------------------------------------------------+---------------+
18 rows in set (0.00 sec)
mysql> SELECT title, released_year FROM books WHERE released_year >= 2003;
+----------------------------------+---------------+
| title | released_year |
+----------------------------------+---------------+
| The Namesake | 2003 |
| Norse Mythology | 2016 |
| A Hologram for the King: A Novel | 2012 |
| The Circle | 2013 |
| Just Kids | 2010 |
| Coraline | 2003 |
| Oblivion: Stories | 2004 |
| Consider the Lobster | 2005 |
| 10% Happier | 2014 |
| Lincoln In The Bardo | 2017 |
+----------------------------------+---------------+
10 rows in set (0.00 sec),,
mysql> SELECT title, stock_quantity FROM books;
+-----------------------------------------------------+----------------+
| title | stock_quantity |
+-----------------------------------------------------+----------------+
| The Namesake | 32 |
| Norse Mythology | 43 |
| Interpreter of Maladies | 97 |
| A Hologram for the King: A Novel | 154 |
| The Circle | 26 |
| The Amazing Adventures of Kavalier & Clay | 68 |
| Just Kids | 55 |
| A Heartbreaking Work of Staggering Genius | 104 |
| Coraline | 100 |
| What We Talk About When We Talk About Love: Stories | 23 |
| Where I'm Calling From: Selected Stories | 12 |
| White Noise | 49 |
| Cannery Row | 95 |
| Oblivion: Stories | 172 |
| Consider the Lobster | 92 |
| 10% Happier | 29 |
| fake_book | 287 |
| Lincoln In The Bardo | 1000 |
+-----------------------------------------------------+----------------+
18 rows in set (0.00 sec)
mysql> SELECT title, stock_quantity FROM books WHERE stock_quantity >= 100;
+-------------------------------------------+----------------+
| title | stock_quantity |
+-------------------------------------------+----------------+
| A Hologram for the King: A Novel | 154 |
| A Heartbreaking Work of Staggering Genius | 104 |
| Coraline | 100 |
| Oblivion: Stories | 172 |
| fake_book | 287 |
| Lincoln In The Bardo | 1000 |
+-------------------------------------------+----------------+
6 rows in set (0.00 sec)
Logical Operator, 즉 논리 연산자라서 이렇게도 쓸 수 있다.
mysql> SELECT 99 > 1;
+--------+
| 99 > 1 |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT 99 < 1;
+--------+
| 99 < 1 |
+--------+
| 0 |
+--------+
1 row in set (0.00 sec)99는 1보다 크다. 그러므로 true이다. 그래서 값이 1이 나왔다. 99 < 1은 거짓(false)이다.그러므로 0이 나왔다. MySQL에서true는 1, false는 0으로 나타낸다.
몇가지 실험을 해보자
- 100 > 5
- -15 > 15
- 9 > -10
- 1 > 1
- 'a' > 'b'
- 'A' > 'a'
mysql> SELECT 99 > 1;
+--------+
| 99 > 1 |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT 99 < 1;
+--------+
| 99 < 1 |
+--------+
| 0 |
+--------+
1 row in set (0.00 sec)
mysql> SELECT 100 > 5;
+---------+
| 100 > 5 |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
mysql> SELECT -15 > 15;
+----------+
| -15 > 15 |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT 9 > -10;
+---------+
| 9 > -10 |
+---------+
| 1 |
+---------+
1 row in set (0.00 sec)
mysql> SELECT 1 > 1;
+-------+
| 1 > 1 |
+-------+
| 0 |
+-------+
1 row in set (0.00 sec)
mysql> SELECT 'a' > 'b';
+-----------+
| 'a' > 'b' |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT 'A' > 'a';
+-----------+
| 'A' > 'a' |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)여기서 주목해서 봐야할 부분은 'a' > 'b','A' > 'a'이부분이다. MySQL에서는 뒤에 나온 문자가 앞에 나온 문자보다 큰걸로 표시된다. 또 대문자와 소문자는 크기가 같은걸로 분류된다.
mysql> SELECT 'a' > 'b';
+-----------+
| 'a' > 'b' |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT 'b' > 'a';
+-----------+
| 'b' > 'a' |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT 'A' > 'a';
+-----------+
| 'A' > 'a' |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT 'a' > 'A';
+-----------+
| 'a' > 'A' |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
mysql> SELECT 'a' >= 'A';
+------------+
| 'a' >= 'A' |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT 'A' >= 'a';
+------------+
| 'A' >= 'a' |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT 'A' = 'a';
+-----------+
| 'A' = 'a' |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)mysql> SELECT 'ㅁ' >= 'a';
+--------------+
| 'ㅁ' >= 'a' |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT 'ㅁ' >= 'A';
+--------------+
| 'ㅁ' >= 'A' |
+--------------+
| 1 |
+--------------+
1 row in set (0.01 sec)++한글끼리도 크기비교를 한다. 예를들어 'ㄴ'은 'ㄱ'보다 뒤에 나오기 때문에 'ㄴ'이 'ㄱ'보다 크다고 나온다.
mysql> SELECT 'ㄱ' > 'ㄴ';
+---------------+
| 'ㄱ' > 'ㄴ' |
+---------------+
| 0 |
+---------------+
1 row in set (0.00 sec)
mysql> SELECT 'ㄴ' > 'ㄱ';
+---------------+
| 'ㄴ' > 'ㄱ' |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)하지만 확실하지 않고, 여러 에러가 나올 수 있기 때문에 문자열 끼리 비교는 쓰지말자.
'<' (Less Than)
그냥 보이듯이 '>'Greater Than의 반대다.
mysql> SELECT title, released_year FROM books;
+-----------------------------------------------------+---------------+
| title | released_year |
+-----------------------------------------------------+---------------+
| The Namesake | 2003 |
| Norse Mythology | 2016 |
| Interpreter of Maladies | 1996 |
| A Hologram for the King: A Novel | 2012 |
| The Circle | 2013 |
| The Amazing Adventures of Kavalier & Clay | 2000 |
| Just Kids | 2010 |
| A Heartbreaking Work of Staggering Genius | 2001 |
| Coraline | 2003 |
| What We Talk About When We Talk About Love: Stories | 1981 |
| Where I'm Calling From: Selected Stories | 1989 |
| White Noise | 1985 |
| Cannery Row | 1945 |
| Oblivion: Stories | 2004 |
| Consider the Lobster | 2005 |
| 10% Happier | 2014 |
| fake_book | 2001 |
| Lincoln In The Bardo | 2017 |
+-----------------------------------------------------+---------------+
18 rows in set (0.00 sec)
mysql> SELECT title, released_year FROM books WHERE released_year < 2000;
+-----------------------------------------------------+---------------+
| title | released_year |
+-----------------------------------------------------+---------------+
| Interpreter of Maladies | 1996 |
| What We Talk About When We Talk About Love: Stories | 1981 |
| Where I'm Calling From: Selected Stories | 1989 |
| White Noise | 1985 |
| Cannery Row | 1945 |
+-----------------------------------------------------+---------------+
5 rows in set (0.00 sec)여기서도 몇가지 실험을 해보자.
- 3 < -10
- -10 < -9
- 42 <= 42
- 'h' < 'p'
- 'Q' <= 'q'
mysql> SELECT 3 < -10;
+---------+
| 3 < -10 |
+---------+
| 0 |
+---------+
1 row in set (0.00 sec)
mysql> SELECT -10 < -9;
+----------+
| -10 < -9 |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT 42 <= 42;
+----------+
| 42 <= 42 |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> SELECT 'h' <= 'q';
+------------+
| 'h' <= 'q' |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
mysql> SELECT 'Q' <= 'q';
+------------+
| 'Q' <= 'q' |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
임재현입니다.