MySQL - Logical Operators : !=(Not Equal), NOT LIKE

Udemy-Theフラッグシップ版MySQL Bootcamp:Go from SQL Beginner to Expertレッスンまとめ

!= (Not Equal)

一般的な他のプログラムで使用されるnotequalと同じです.
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ではなく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)