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;