[MySQL] 演算子

○比較演算子

　比較演算子(=, !=, <, >, <=, >=, <=>, between, in, like)
　結果：true(1) or false(0) を返す。

例)
mysql> select 100=100, 90<100, 90>=100;
+---------+--------+---------+
| 100=100 | 90<100 | 90>=100 |
+---------+--------+---------+
|       1 |      1 |       0 |
+---------+--------+---------+

mysql> select 100 between 90 and 120 as between_1,
    -> 100 in (100,200,300) as in_1,
    -> 90 in (100,200,300) as in_2;
+-----------+------+------+
| between_1 | in_1 | in_2 |
+-----------+------+------+
|         1 |    1 |    0 |
+-----------+------+------+

mysql> select 'abcde' like '%bcd%', 'abdce' like '%bcd%';
+----------------------+----------------------+
| 'abcde' like '%bcd%' | 'abdce' like '%bcd%' |
+----------------------+----------------------+
|                    1 |                    0 |
+----------------------+----------------------+

mysql> select 100=null, null=null, 100<=>null, null<=>null;
+----------+-----------+------------+-------------+
| 100=null | null=null | 100<=>null | null<=>null |
+----------+-----------+------------+-------------+
|     NULL |      NULL |          0 |           1 |
+----------+-----------+------------+-------------+

○論理演算子

　論理演算子(and または &&, or または ||, xor, not または !)
　結果：true(1) or false(0) を返す。

例)
mysql> select 1 and 1, 0 && 1, 0 or 1, 1 xor 1;
+---------+--------+--------+---------+
| 1 and 1 | 0 && 1 | 0 or 1 | 1 xor 1 |
+---------+--------+--------+---------+
|       1 |      0 |      1 |       0 |
+---------+--------+--------+---------+

mysql> select not 1 or 1, not (1 or 1), ! (1 or 1);
+------------+--------------+------------+
| not 1 or 1 | not (1 or 1) | ! (1 or 1) |
+------------+--------------+------------+
|          1 |            0 |          0 |
+------------+--------------+------------+

○数値演算子

　数値演算子(+, -, *, /, div, % または mod)
　結果：計算結果を返す。

例)
mysql> select 1+3, 13/4, 13 div 4, 19 % 5;
+-----+--------+----------+--------+
| 1+3 | 13/4   | 13 div 4 | 19 % 5 |
+-----+--------+----------+--------+
|   4 | 3.2500 |        3 |      4 |
+-----+--------+----------+--------+

●実用編

・テスト用のテーブル

mysql> select * from test1;
+------+--------+
| id   | moji   |
+------+--------+
|    1 | abc    |
|    2 | abcde  |
|    3 | test1  |
|    4 | test2  |
|    5 | test3  |
|    6 | hoge1  |
|    7 | hoge2  |
|    8 | 1_test |
|    9 | 2_test |
|   10 | NULL   |
+------+--------+

○比較演算子

・"id"が8の行を表示
mysql> select * from test1 where id = 8;
+------+----------+
| id   | moji |
+------+----------+
|    8 | 1_test   |
+------+----------+

・"id"が4～6の行を表示
mysql> select * from test1 where id between 4 and 6;
+------+----------+
| id   | moji |
+------+----------+
|    4 | test2    |
|    5 | test3    |
|    6 | hoge1    |
+------+----------+

・"moji"に"test"を含む行を表示
mysql> select * from test1 where moji like '%test%';
+------+--------+
| id   | moji   |
+------+--------+
|    3 | test1  |
|    4 | test2  |
|    5 | test3  |
|    8 | 1_test |
|    9 | 2_test |
+------+--------+

・"moji"がnullの行を表示
mysql> select * from test1 where moji <=> null;
+------+------+
| id   | moji |
+------+------+
|   10 | NULL |
+------+------+

○論理演算子

・"id"が2～4かつ"moji"が"abc"で始まる行を表示
mysql> select * from test1 where id between 2 and 4
    -> and moji like 'abc%';
+------+-------+
| id   | moji  |
+------+-------+
|    2 | abcde |
+------+-------+

・「"id"が2～4」と「moji"が"abc"で始まる」のXORの行を表示
mysql> select * from test1 where id between 2 and 4
    -> xor moji like 'abc%';
+------+-------+
| id   | moji  |
+------+-------+
|    1 | abc   |
|    3 | test1 |
|    4 | test2 |
+------+-------+

○数値演算子

・"id"を4で割った余りが1になる行を表示
mysql> select * from test1 where id mod 4 = 1;
+------+--------+
| id   | moji   |
+------+--------+
|    1 | abc    |
|    5 | test3  |
|    9 | 2_test |
+------+--------+

・"moji"に"test"を含む行について、
　"id"×2+1の値と"moji"を表示
mysql> select id * 2 + 1, moji from test1
    -> where moji like '%test%';
+------------+--------+
| id * 2 + 1 | moji   |
+------------+--------+
|          7 | test1  |
|          9 | test2  |
|         11 | test3  |
|         17 | 1_test |
|         19 | 2_test |
+------------+--------+