mysql运算符大全、优先级及实例讲解

一、算术运算符1、加 mysql> select 1+2;+-----+| 1+2 |+-----+|   3 |+-----+登录后复制2、减mysql> select 1-2;+-----+| 1-2 |+-----+|  -1 |+-----+登录后复制3、乘mysql> select 2*3;+-----+| 2*3 |+-----+|   6 |+-----+登录后复制4、除mysql> select 2/3;+--------+| 2/3    |+--------+| 0.6667 |+--------+登录后复制5、商mysql> select 10 DIV 4;+----------+| 10 DIV 4 |+----------+|        2 |+----------+登录后复制6、取余mysql> select 10 MOD 4;+----------+| 10 MOD 4 |+----------+|        2 |+----------+登录后复制 二、比较运算符1、等于mysql> select 2=3;+-----+| 2=3 |+-----+|   0 |+-----+mysql> select NULL = NULL;+-------------+| NULL = NULL |+-------------+|        NULL |+-------------+登录后复制2、不等于mysql> select 23;+------+| 23 |+------+|    1 |+------+登录后复制3、安全等于与“=”的区别在于当两个操作码均为NULL时，其所得值为1而不为NULL，而当一个操作码为NULL时，其所得值为0而不为NULL。mysql> select 23;+-------+| 23 |+-------+|     0 |+-------+mysql> select null=null;+-----------+| null=null |+-----------+|      NULL |+-----------+        mysql> select nullnull;+-------------+| nullnull |+-------------+|           1 |+-------------+登录后复制4、小于mysql> select 2登录后复制5、小于等于mysql> select 2登录后复制6、大于mysql> select 2>3;+-----+| 2>3 |+-----+|   0 |+-----+登录后复制7、大于等于mysql> select 2>=3;+------+| 2>=3 |+------+|    0 |+------+登录后复制8、BETWEENmysql> select 5 between 1 and 10;+--------------------+| 5 between 1 and 10 |+--------------------+|                  1 |+--------------------+登录后复制9、INmysql> select 5 in (1,2,3,4,5);+------------------+| 5 in (1,2,3,4,5) |+------------------+|                1 |+------------------+登录后复制10、NOT INmysql> select 5 not in (1,2,3,4,5);+----------------------+| 5 not in (1,2,3,4,5) |+----------------------+|                    0 |+----------------------+登录后复制11、IS NULLmysql> select null is NULL;+--------------+| null is NULL |+--------------+|            1 |+--------------+mysql> select 'a' is NULL;+-------------+| 'a' is NULL |+-------------+|           0 |+-------------+登录后复制12、IS NOT NULLmysql> select null IS NOT NULL;+------------------+| null IS NOT NULL |+------------------+|                0 |+------------------+        mysql> select 'a' IS NOT NULL;+-----------------+| 'a' IS NOT NULL |+-----------------+|               1 |+-----------------+登录后复制13、LIKEmysql> select '12345' like '12%';+--------------------+| '12345' like '12%' |+--------------------+|                  1 |+--------------------+mysql> select '12345' like '12_';+--------------------+| '12345' like '12_' |+--------------------+|                  0 |+--------------------+登录后复制14、REGEXPmysql> select 'beijing' REGEXP 'jing';+-------------------------+| 'beijing' REGEXP 'jing' |+-------------------------+|                       1 |+-------------------------+mysql> select 'beijing' REGEXP 'xi';+-----------------------+| 'beijing' REGEXP 'xi' |+-----------------------+|                     0 |+-----------------------+登录后复制 三、逻辑运算符1、与mysql> select 2 and 0;+---------+| 2 and 0 |+---------+|       0 |+---------+        mysql> select 2 and 1;   +---------+     | 2 and 1 |      +---------+      |       1 |      +---------+登录后复制2、或mysql> select 2 or 0;+--------+| 2 or 0 |+--------+|      1 |+--------+mysql> select 2 or 1;+--------+| 2 or 1 |+--------+|      1 |+--------+mysql> select 0 or 0;+--------+| 0 or 0 |+--------+|      0 |+--------+mysql> select 1 || 0;+--------+| 1 || 0 |+--------+|      1 |+--------+登录后复制3、非mysql> select not 1;+-------+| not 1 |+-------+|     0 |+-------+mysql> select !0;+----+| !0 |+----+|  1 |+----+登录后复制4、异或mysql> select 1 xor 1;+---------+| 1 xor 1 |+---------+|       0 |+---------+mysql> select 0 xor 0;+---------+| 0 xor 0 |+---------+|       0 |+---------+mysql> select 1 xor 0;+---------+| 1 xor 0 |+---------+|       1 |+---------+mysql> select null or 1;+-----------+| null or 1 |+-----------+|         1 |+-----------+mysql> select 1 ^ 0;+-------+| 1 ^ 0 |+-------+|     1 |+-------+登录后复制 四、位运算符1、按位与mysql> select 3&5;+-----+| 3&5 |+-----+|   1 |+-----+登录后复制2、按位或mysql> select 3|5;+-----+| 3|5 |+-----+|   7 |+-----+登录后复制3、按位异或mysql> select 3^5;+-----+| 3^5 |+-----+|   6 |+-----+登录后复制4、按位取反mysql> select ~18446744073709551612;+-----------------------+| ~18446744073709551612 |+-----------------------+|                     3 |+-----------------------+登录后复制5、按位右移 mysql> select 3>>1;+------+| 3>>1 |+------+|    1 |+------+登录后复制6、按位左移mysql> select 3登录后复制 五、运算符优先级顺序最高优先级 :=1 ||, OR, XOR2 &&, AND3 BETWEEN, CASE, WHEN, THEN, ELSE4 =, , >=, >, , !=, IS, LIKE, REGEXP, IN5 |6 &7 >8 -, +9 *, /, DIV, %, MOD10 ^11 - (unary minus), ~ (unary bit inversion)12 !, NOT最低优先级 BINARY, COLLATE