条件文には、LIKE, IS NULL, IN, BETWEENなどの演算子がありますが、
これらの条件に合わなかった場合という意味のNOT演算子をしようすることが
できます。
LIKE演算子にNOTをつけた場合
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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をつけた場合
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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をつけた場合
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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をつけた場合
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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 | 埼玉 | +----+----------+------+--------+---------+ |
/
