12月 272012
 

条件文には、LIKE, IS NULL, IN, BETWEENなどの演算子がありますが、
これらの条件に合わなかった場合という意味のNOT演算子をしようすることが
できます。

LIKE演算子にNOTをつけた場合

mysql> select * from member where name LIKE 't%';
+----+--------+------+--------+---------+
| id | name   | age  | weight | address |
+----+--------+------+--------+---------+
|  1 | tanaka |   30 |     57 | 東京  |
|  6 | takai  |   25 |     63 | 千葉  |
|  7 | tadano |   26 |     43 | 千葉  |
+----+--------+------+--------+---------+
 
mysql> select * from member where name NOT LIKE 't%';
+----+----------+------+--------+---------+
| id | name     | age  | weight | address |
+----+----------+------+--------+---------+
|  2 | suzuki   |   32 |     77 | 東京  |
|  3 | nakamura |   33 |     52 | 群馬  |
|  4 | miura    |   31 |     67 | 埼玉  |
|  5 | kimura   |   27 |     62 | 茨城  |
|  8 | murakami |   35 |     42 | 茨城  |
|  9 | kikuchi  |   32 |     41 | 茨城  |
| 10 | suzuki   |   31 |     51 | 茨城  |
| 11 | saeki    |   29 |     49 | 埼玉  |
| 12 | ooyama   |   29 |     48 | 埼玉  |
| 13 | kamata   |   22 |     47 | 埼玉  |
| 14 | mizumoto |   35 |     55 | NULL    |
+----+----------+------+--------+---------+

IS NULL演算子にNOTをつけた場合

mysql> select * from member where address IS NULL;
+----+----------+------+--------+---------+
| id | name     | age  | weight | address |
+----+----------+------+--------+---------+
| 14 | mizumoto |   35 |     55 | NULL    |
+----+----------+------+--------+---------+
 
mysql> select * from member where address IS NOT NULL;
+----+----------+------+--------+---------+
| id | name     | age  | weight | address |
+----+----------+------+--------+---------+
|  1 | tanaka   |   30 |     57 | 東京  |
|  2 | suzuki   |   32 |     77 | 東京  |
|  3 | nakamura |   33 |     52 | 群馬  |
|  4 | miura    |   31 |     67 | 埼玉  |
|  5 | kimura   |   27 |     62 | 茨城  |
|  6 | takai    |   25 |     63 | 千葉  |
|  7 | tadano   |   26 |     43 | 千葉  |
|  8 | murakami |   35 |     42 | 茨城  |
|  9 | kikuchi  |   32 |     41 | 茨城  |
| 10 | suzuki   |   31 |     51 | 茨城  |
| 11 | saeki    |   29 |     49 | 埼玉  |
| 12 | ooyama   |   29 |     48 | 埼玉  |
| 13 | kamata   |   22 |     47 | 埼玉  |
+----+----------+------+--------+---------+

BETWEEN演算子にNOTをつけた場合

mysql> select * from member where age between 31 AND 35;
+----+----------+------+--------+---------+
| id | name     | age  | weight | address |
+----+----------+------+--------+---------+
|  2 | suzuki   |   32 |     77 | 東京  |
|  3 | nakamura |   33 |     52 | 群馬  |
|  4 | miura    |   31 |     67 | 埼玉  |
|  8 | murakami |   35 |     42 | 茨城  |
|  9 | kikuchi  |   32 |     41 | 茨城  |
| 10 | suzuki   |   31 |     51 | 茨城  |
| 14 | mizumoto |   35 |     55 | NULL    |
+----+----------+------+--------+---------+
 
mysql> select * from member where age NOT between 31 AND 35;
+----+--------+------+--------+---------+
| id | name   | age  | weight | address |
+----+--------+------+--------+---------+
|  1 | tanaka |   30 |     57 | 東京  |
|  5 | kimura |   27 |     62 | 茨城  |
|  6 | takai  |   25 |     63 | 千葉  |
|  7 | tadano |   26 |     43 | 千葉  |
| 11 | saeki  |   29 |     49 | 埼玉  |
| 12 | ooyama |   29 |     48 | 埼玉  |
| 13 | kamata |   22 |     47 | 埼玉  |
+----+--------+------+--------+---------+

IN演算子にNOTをつけた場合

mysql> select * from member where weight IN (49, 51, 52, 55);
+----+----------+------+--------+---------+
| id | name     | age  | weight | address |
+----+----------+------+--------+---------+
|  3 | nakamura |   33 |     52 | 群馬  |
| 10 | suzuki   |   31 |     51 | 茨城  |
| 11 | saeki    |   29 |     49 | 埼玉  |
| 14 | mizumoto |   35 |     55 | NULL    |
+----+----------+------+--------+---------+
 
mysql> select * from member where weight NOT IN (49, 51, 52, 55);
+----+----------+------+--------+---------+
| id | name     | age  | weight | address |
+----+----------+------+--------+---------+
|  1 | tanaka   |   30 |     57 | 東京  |
|  2 | suzuki   |   32 |     77 | 東京  |
|  4 | miura    |   31 |     67 | 埼玉  |
|  5 | kimura   |   27 |     62 | 茨城  |
|  6 | takai    |   25 |     63 | 千葉  |
|  7 | tadano   |   26 |     43 | 千葉  |
|  8 | murakami |   35 |     42 | 茨城  |
|  9 | kikuchi  |   32 |     41 | 茨城  |
| 12 | ooyama   |   29 |     48 | 埼玉  |
| 13 | kamata   |   22 |     47 | 埼玉  |
+----+----------+------+--------+---------+

 Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>