Udemy - The Ultimate MySQL Bootcamp: Go from SQL Beginner to Expert를 수강하며 정리하는 글
!= (Not Equal)
일반적인 다른 프로그램에서 쓰는 not equal과 같다.
Q. Select all books NOT published in 2017
-- Not Equal을 쓰지 않은 검색결과
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)
-- Not Equal을 쓴 검색결과
mysql> SELECT title, released_year FROM books WHERE released_year != 2017;
+-----------------------------------------------------+---------------+
| 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 |
+-----------------------------------------------------+---------------+
17 rows in set (0.00 sec)
Q.author_lname이 'Harris'가 아닌 사람들을 출력해라
-- 먼저 작가들의 전체 이름
mysql> SELECT author_lname FROM books;
+----------------+
| author_lname |
+----------------+
| Lahiri |
| Gaiman |
| Lahiri |
| Eggers |
| Eggers |
| Chabon |
| Smith |
| Eggers |
| Gaiman |
| Carver |
| Carver |
| DeLillo |
| Steinbeck |
| Foster Wallace |
| Foster Wallace |
| Harris |
| Harris |
| Saunders |
+----------------+
18 rows in set (0.00 sec)
-- Harris를 뺀 사람들의 이름
mysql> SELECT author_lname FROM books WHERE author_lname != 'Harris';
+----------------+
| author_lname |
+----------------+
| Lahiri |
| Gaiman |
| Lahiri |
| Eggers |
| Eggers |
| Chabon |
| Smith |
| Eggers |
| Gaiman |
| Carver |
| Carver |
| DeLillo |
| Steinbeck |
| Foster Wallace |
| Foster Wallace |
| Saunders |
+----------------+
16 rows in set (0.00 sec)NOT LIKE
LIKE의 반대. 먼저LIKE의 사용을 다시한번 봐보자.
mysql> SELECT title FROM books WHERE title LIKE '%w%';
+-----------------------------------------------------+
| title |
+-----------------------------------------------------+
| A Heartbreaking Work of Staggering Genius |
| What We Talk About When We Talk About Love: Stories |
| Where I'm Calling From: Selected Stories |
| White Noise |
| Cannery Row |
+-----------------------------------------------------+
5 rows in set (0.00 sec)이런식으로 주로 와일드카드(%나 _)와 함께 쓰여 조건을 정의하는데 쓰인다.
NOT LIKE의 사용을 봐보자.
mysql> SELECT title FROM books WHERE title NOT LIKE '%w%';
+-------------------------------------------+
| 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 |
| Coraline |
| Oblivion: Stories |
| Consider the Lobster |
| 10% Happier |
| fake_book |
| Lincoln In The Bardo |
+-------------------------------------------+
13 rows in set (0.00 sec)이렇게 LIKE와는 반대의 결과를 출력해 주는 것을 볼 수 있다. ❗️여기서 NOT LIKE는 NOT EQUAL과 달리 !가 아니라 NOT이라고 문자로 붙여주는 것을 주의해야한다. 물론 '_'와일드카드도 사용가능하다.
mysql> SELECT title FROM books WHERE title LIKE '_________';
+-----------+
| title |
+-----------+
| Just Kids |
| fake_book |
+-----------+
2 rows in set (0.00 sec)
mysql> SELECT title FROM books WHERE title NOT LIKE '_________';
+-----------------------------------------------------+
| title |
+-----------------------------------------------------+
| The Namesake |
| Norse Mythology |
| Interpreter of Maladies |
| A Hologram for the King: A Novel |
| The Circle |
| The Amazing Adventures of Kavalier & Clay |
| 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 |
| Lincoln In The Bardo |
+-----------------------------------------------------+
16 rows in set (0.00 sec)
임재현입니다.