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

一、算术运算符

1、加

<code class="language-sql">mysql> select 1+2;+-----+| 1+2 |+-----+|   3 |+-----+</code>

2、减

<code class="language-sql">mysql> select 1-2;+-----+| 1-2 |+-----+|  -1 |+-----+</code>

3、乘

<code class="language-sql">mysql> select 2*3;+-----+| 2*3 |+-----+<b>本文来源gao@dai!ma.com搞$代^码!网7</b>|   6 |+-----+</code>

4、除

<code class="language-sql">mysql> select 2/3;+--------+| 2/3    |+--------+| 0.6667 |+--------+</code>

5、商

<code class="language-sql">mysql> select 10 DIV 4;+----------+| 10 DIV 4 |+----------+|        2 |+----------+</code>

6、取余

<code class="language-sql">mysql> select 10 MOD 4;+----------+| 10 MOD 4 |+----------+|        2 |+----------+</code>

二、比较运算符

1、等于

<code class="language-sql">mysql> select 2=3;+-----+| 2=3 |+-----+|   0 |+-----+mysql> select NULL = NULL;+-------------+| NULL = NULL |+-------------+|        NULL |+-------------+</code>

2、不等于

<code class="language-sql">mysql> select 23;+------+| 23 |+------+|    1 |+------+</code>

3、安全等于

与“=”的区别在于当两个操作码均为NULL时，其所得值为1而不为NULL，而当一个操作码为NULL时，其所得值为0而不为NULL。

<code class="language-sql">mysql> select 23;+-------+| 23 |+-------+|     0 |+-------+mysql> select null=null;+-----------+| null=null |+-----------+|      NULL |+-----------+        mysql> select nullnull;+-------------+| nullnull |+-------------+|           1 |+-------------+</code>

4、小于

<code class="language-sql">mysql> select 2<3;+-----+| 2<3 |+-----+|   1 |+-----+</code>

5、小于等于

<code class="language-sql">mysql> select 2<=3;+------+| 2<=3 |+------+|    1 |+------+</code>

6、大于

<code class="language-sql">mysql> select 2>3;+-----+| 2>3 |+-----+|   0 |+-----+</code>

7、大于等于

<code class="language-sql">mysql> select 2>=3;+------+| 2>=3 |+------+|    0 |+------+</code>

8、BETWEEN

<code class="language-sql">mysql> select 5 between 1 and 10;+--------------------+| 5 between 1 and 10 |+--------------------+|                  1 |+--------------------+</code>

9、IN

<code class="language-sql">mysql> select 5 in (1,2,3,4,5);+------------------+| 5 in (1,2,3,4,5) |+------------------+|                1 |+------------------+</code>

10、NOT IN

<code class="language-sql">mysql> select 5 not in (1,2,3,4,5);+----------------------+| 5 not in (1,2,3,4,5) |+----------------------+|                    0 |+----------------------+</code>

11、IS NULL

<code class="language-sql">mysql> select null is NULL;+--------------+| null is NULL |+--------------+|            1 |+--------------+mysql> select 'a' is NULL;+-------------+| 'a' is NULL |+-------------+|           0 |+-------------+</code>

12、IS NOT NULL

<code class="language-sql">mysql> select null IS NOT NULL;+------------------+| null IS NOT NULL |+------------------+|                0 |+------------------+        mysql> select 'a' IS NOT NULL;+-----------------+| 'a' IS NOT NULL |+-----------------+|               1 |+-----------------+</code>

13、LIKE

<code class="language-sql">mysql> select '12345' like '12%';+--------------------+| '12345' like '12%' |+--------------------+|                  1 |+--------------------+mysql> select '12345' like '12_';+--------------------+| '12345' like '12_' |+--------------------+|                  0 |+--------------------+</code>

14、REGEXP

<code class="language-sql">mysql> select 'beijing' REGEXP 'jing';+-------------------------+| 'beijing' REGEXP 'jing' |+-------------------------+|                       1 |+-------------------------+mysql> select 'beijing' REGEXP 'xi';+-----------------------+| 'beijing' REGEXP 'xi' |+-----------------------+|                     0 |+-----------------------+</code>

三、逻辑运算符

1、与

<code class="language-sql">mysql> select 2 and 0;+---------+| 2 and 0 |+---------+|       0 |+---------+        mysql> select 2 and 1;   +---------+     | 2 and 1 |      +---------+      |       1 |      +---------+</code>

2、或

<code class="language-sql">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 |+--------+</code>

3、非

<code class="language-sql">mysql> select not 1;+-------+| not 1 |+-------+|     0 |+-------+mysql> select !0;+----+| !0 |+----+|  1 |+----+</code>

4、异或

<code class="language-sql">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 |+-------+</code>

四、位运算符

1、按位与

<code class="language-sql">mysql> select 3&5;+-----+| 3&5 |+-----+|   1 |+-----+</code>

2、按位或

<code class="language-sql">mysql> select 3|5;+-----+| 3|5 |+-----+|   7 |+-----+</code>

3、按位异或

<code class="language-sql">mysql> select 3^5;+-----+| 3^5 |+-----+|   6 |+-----+</code>

4、按位取反

<code class="language-sql">mysql> select ~18446744073709551612;+-----------------------+| ~18446744073709551612 |+-----------------------+|                     3 |+-----------------------+</code>

5、按位右移

<code class="language-sql">mysql> select 3>>1;+------+| 3>>1 |+------+|    1 |+------+</code>

6、按位左移

<code class="language-sql">mysql> select 3<<1;+------+| 3<<1 |+------+|    6 |+------+</code>

五、运算符优先级顺序

最高优先级 :=

1 ||, OR, XOR

2 &&, AND

3 BETWEEN, CASE, WHEN, THEN, ELSE

4 =, , >=, >, <=, <, , !=, IS, LIKE, REGEXP, IN

5 |

6 &

7 <>

8 -, +

9 *, /, DIV, %, MOD

10 ^

11 – (unary minus), ~ (unary bit inversion)

12 !, NOT

最低优先级 BINARY, COLLATE

喜欢 (0)赏

[搞代码]

分享 (0)