Heim >Datenbank >MySQL-Tutorial >mysql运算符大全、优先级及实例讲解

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

WBOY
WBOYOriginal
2016-06-01 09:56:511197Durchsuche

一、算术运算符

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 |
+-----+
|   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</code>

5、小于等于

<code class="language-sql">mysql> select 2</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</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

Stellungnahme:
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn