mysql---运算符

1, 算术运算符+ - * / %

mysql> select 0.1+0.3333, 0.1-0.3333, 0.1*0.3333, 1/2, 1%2;
+------------+------------+------------+--------+------+
| 0.1+0.3333 | 0.1-0.3333 | 0.1*0.3333 | 1/2    | 1%2  |
+------------+------------+------------+--------+------+
|     0.4333 |    -0.2333 |    0.03333 | 0.5000 |    1 |
+------------+------------+------------+--------+------+
1 row in set (0.00 sec)

mysql> select 1/0, 100%0;
+------+-------+
| 1/0  | 100%0 |
+------+-------+
| NULL |  NULL |
+------+-------+
1 row in set, 2 warnings (0.01 sec)

mysql> select 3%2, mod(3, 2);
+------+-----------+
| 3%2  | mod(3, 2) |
+------+-----------+
|    1 |         1 |
+------+-----------+
1 row in set (0.00 sec)

2，比较运算符

mysql> select 1=1, 0=1, Null=0,Null=Null;
+-----+-----+--------+-----------+
| 1=1 | 0=1 | Null=0 | Null=Null |
+-----+-----+--------+-----------+
|   1 |   0 |   NULL |      NULL |
+-----+-----+--------+-----------+
1 row in set (0.00 sec)

=运算符用于比较两个数值是否相等，相等结果为1，不想等结果为0 null不能进行比较

mysql> select 1<>1, 1<>0,null<>null;
+------+------+------------+
| 1<>1 | 1<>0 | null<>null |
+------+------+------------+
|    0 |    1 |       NULL |
+------+------+------------+
1 row in set (0.01 sec)

<>运算符用来比较2个数值是否不想等，不想等为1，相等为0，null不能进行比较

mysql> select 1<=>2, 2<=>2, null<=>null;
+-------+-------+-------------+
| 1<=>2 | 2<=>2 | null<=>null |
+-------+-------+-------------+
|     0 |     1 |           1 |
+-------+-------+-------------+
1 row in set (0.00 sec)

<=>安全的等于运算符，比较数值是否相等，相等为1，不想等为0，null可以进行比较

mysql> select 1<0, 1<2, null<1;
+-----+-----+--------+
| 1<0 | 1<2 | null<1 |
+-----+-----+--------+
|   0 |   1 |   NULL |
+-----+-----+--------+
1 row in set (0.00 sec)

<小于运算符，比较左侧数值小于右侧数值，小于为1，否则为0，null不能进行计算

mysql> select 1<= 1, 2<=1, null <=null;
+-------+------+-------------+
| 1<= 1 | 2<=1 | null <=null |
+-------+------+-------------+
|     1 |    0 |        NULL |
+-------+------+-------------+
1 row in set (0.00 sec)

<=运算符，用于表示左侧数值小于等于右侧数值，左侧数值小于等于右侧数值结果为1，否则为0，null不能进行比较

mysql> select 1>0, 1>2, null > null;
+-----+-----+-------------+
| 1>0 | 1>2 | null > null |
+-----+-----+-------------+
|   1 |   0 |        NULL |
+-----+-----+-------------+
1 row in set (0.00 sec)

大于>运算符用来比较左边数值大于右边数值，大于结果为1，否则为0，null不参与计算

mysql> select 1>=1, 1>=2, null>=null;
+------+------+------------+
| 1>=1 | 1>=2 | null>=null |
+------+------+------------+
|    1 |    0 |       NULL |
+------+------+------------+
1 row in set (0.00 sec)

大于等于>=运算符用来判断左侧数值大于等于右侧数值，大于等于右侧数值结果为1，否则为0，null不参与计算

mysql> select 1 between 1 and 2, 2 between 3 and 4, null between 1 and null;
+-------------------+-------------------+-------------------------+
| 1 between 1 and 2 | 2 between 3 and 4 | null between 1 and null |
+-------------------+-------------------+-------------------------+
|                 1 |                 0 |                    NULL |
+-------------------+-------------------+-------------------------+
1 row in set (0.00 sec)

between 用来判断某个值大于等于min 小于等于max范围内结果为1，否则为0，null不参与计算

mysql> select 1 in(2,3,4), 't' in ('a', 'b', 'c', 't'), null in (null);
+-------------+-----------------------------+----------------+
| 1 in(2,3,4) | 't' in ('a', 'b', 'c', 't') | null in (null) |
+-------------+-----------------------------+----------------+
|           0 |                           1 |           NULL |
+-------------+-----------------------------+----------------+
1 row in set (0.01 sec)

in用来判断某个值是否在某个列表中，是返回1否则返回0， null不参与计算

mysql> select 0 is null, null is null;
+-----------+--------------+
| 0 is null | null is null |
+-----------+--------------+
|         0 |            1 |
+-----------+--------------+
1 row in set (0.00 sec)

is null判断某个值是否为null,是返回1 否则返回0；

mysql> select 0 is not null, - is not null, null is not null;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'is not null, null is not null' at line 1
mysql> select 0 is not null , 't' is not null, null is not null;
+---------------+-----------------+------------------+
| 0 is not null | 't' is not null | null is not null |
+---------------+-----------------+------------------+
|             1 |               1 |                0 |
+---------------+-----------------+------------------+
1 row in set (0.00 sec)

is not null 用来判断某个值是否为非null 是返回1 否则返回0

mysql> select 'ab' like '%abc%', 'abcd' like '%ab%', 123 like '%12%';
+-------------------+--------------------+-----------------+
| 'ab' like '%abc%' | 'abcd' like '%ab%' | 123 like '%12%' |
+-------------------+--------------------+-----------------+
|                 0 |                  1 |               1 |
+-------------------+--------------------+-----------------+
1 row in set (0.00 sec)

like用来判断某个值中是否包含like后面的值，包含返回1，否则返回0

mysql> select 'abcdefg' regexp 'abcdefghijk', 123 regexp 12, 'ghj' regexp 'abc'; 
+--------------------------------+---------------+--------------------+
| 'abcdefg' regexp 'abcdefghijk' | 123 regexp 12 | 'ghj' regexp 'abc' |
+--------------------------------+---------------+--------------------+
|                              0 |             1 |                  0 |
+--------------------------------+---------------+--------------------+
1 row in set (0.00 sec)

regexp用来判断某个值是否含有regexp后面的值，是为1，否则为0；

3，逻辑运算符

mysql> select not 1, not 0 , not 2, not null;
+-------+-------+-------+----------+
| not 1 | not 0 | not 2 | not null |
+-------+-------+-------+----------+
|     0 |     1 |     0 |     NULL |
+-------+-------+-------+----------+
1 row in set (0.00 sec)

not ：not 0 为1， not其他为0 not null 为null

mysql> select 1 and 2, 0 and 3, null and null;
+---------+---------+---------------+
| 1 and 2 | 0 and 3 | null and null |
+---------+---------+---------------+
|       1 |       0 |          NULL |
+---------+---------+---------------+
1 row in set (0.01 sec)

当所有运算数都为1时为1 有一个为0时为0 有一个为null时为null

mysql> select 1 or 0, 1 or 1, 0 or 0, 0 or null, null or null;
+--------+--------+--------+-----------+--------------+
| 1 or 0 | 1 or 1 | 0 or 0 | 0 or null | null or null |
+--------+--------+--------+-----------+--------------+
|      1 |      1 |      0 |      NULL |         NULL |
+--------+--------+--------+-----------+--------------+
1 row in set (0.00 sec)

全部为0结果为0 有null为null否则为1

mysql> select 0 xor 0, 1 xor 1, null xor null, 1 xor null, 1 xor 0;
+---------+---------+---------------+------------+---------+
| 0 xor 0 | 1 xor 1 | null xor null | 1 xor null | 1 xor 0 |
+---------+---------+---------------+------------+---------+
|       0 |       0 |          NULL |       NULL |       1 |
+---------+---------+---------------+------------+---------+
1 row in set (0.00 sec)

有null为null 相同为0 相异位1

4，位运算符

mysql> select 2 & 3, 1 & 1, null & null;
+-------+-------+-------------+
| 2 & 3 | 1 & 1 | null & null |
+-------+-------+-------------+
|     2 |     1 |        NULL |
+-------+-------+-------------+
1 row in set (0.00 sec)

先转换成2进制 在进行逻辑与运算，左后转成十进制输出，null参与的计算为null

mysql> select 2|3, null |3;
+-----+---------+
| 2|3 | null |3 |
+-----+---------+
|   3 |    NULL |
+-----+---------+
1 row in set (0.00 sec)

先转换成2进制，再进行逻辑或运算，最后转成十进制输出，null参与的返回null；

mysql> select 2^3, null ^3, null ^null;
+-----+---------+------------+
| 2^3 | null ^3 | null ^null |
+-----+---------+------------+
|   1 |    NULL |       NULL |
+-----+---------+------------+
1 row in set (0.01 sec)

先转换成2进制，再进行逻辑异或运算，最后转成十进制输出，null参与的返回null；

mysql> select ~1, ~18446744073709551614, ~null;
+----------------------+-----------------------+-------+
| ~1                   | ~18446744073709551614 | ~null |
+----------------------+-----------------------+-------+
| 18446744073709551614 |                     1 |  NULL |
+----------------------+-----------------------+-------+
1 row in set (0.00 sec)

先转换成2进制，再进行位取反运算，最后转成十进制输出，null参与的返回null；