Description of mysql operators, priority, and instances

Mysql and other languages also have many operators, including arithmetic operators, comparison operators, logical operators, bitwise operators, etc. This article introduces the mysql operators, their priorities, and examples to coders, for more information, see. I. arithmetic operators

1. add

mysql> select 1+2;+-----+| 1+2 |+-----+|   3 |+-----+

2. subtraction

mysql> select 1-2;+-----+| 1-2 |+-----+|  -1 |+-----+

3. multiplication

mysql> select 2*3;+-----+| 2*3 |+-----+|   6 |+-----+

4. Division

mysql> select 2/3;+--------+| 2/3    |+--------+| 0.6667 |+--------+

5. vendors

mysql> select 10 DIV 4;+----------+| 10 DIV 4 |+----------+|        2 |+----------+

6. obtain the remainder

mysql> select 10 MOD 4;+----------+| 10 MOD 4 |+----------+|        2 |+----------+

II. Comparison operators

1. equal

mysql> select 2=3;+-----+| 2=3 |+-----+|   0 |+-----+mysql> select NULL = NULL;+-------------+| NULL = NULL |+-------------+|        NULL |+-------------+

2. not equal

mysql> select 2<>3;+------+| 2<>3 |+------+|    1 |+------+

3. security equals

The difference with "=" is that when both operation codes are NULL, the obtained value is 1 instead of NULL. When an operation code is NULL, the value is 0 instead of NULL.

mysql> select 2<=>3;+-------+| 2<=>3 |+-------+|     0 |+-------+mysql> select null=null;+-----------+| null=null |+-----------+|      NULL |+-----------+        mysql> select null<=>null;+-------------+| null<=>null |+-------------+|           1 |+-------------+

4. less

mysql> select 2<3;+-----+| 2<3 |+-----+|   1 |+-----+

5. less than or equal

mysql> select 2<=3;+------+| 2<=3 |+------+|    1 |+------+

6. greater

mysql> select 2>3;+-----+| 2>3 |+-----+|   0 |+-----+

7. greater than or equal

mysql> select 2>=3;+------+| 2>=3 |+------+|    0 |+------+

8.

mysql> select 5 between 1 and 10;+--------------------+| 5 between 1 and 10 |+--------------------+|                  1 |+--------------------+

9. IN

mysql> select 5 in (1,2,3,4,5);+------------------+| 5 in (1,2,3,4,5) |+------------------+|                1 |+------------------+

10. NOT IN

mysql> select 5 not in (1,2,3,4,5);+----------------------+| 5 not in (1,2,3,4,5) |+----------------------+|                    0 |+----------------------+

11. IS NULL

mysql> select null is NULL;+--------------+| null is NULL |+--------------+|            1 |+--------------+mysql> select 'a' is NULL;+-------------+| 'a' is NULL |+-------------+|           0 |+-------------+

12. IS NOT NULL

mysql> select null IS NOT NULL;+------------------+| null IS NOT NULL |+------------------+|                0 |+------------------+        mysql> select 'a' IS NOT NULL;+-----------------+| 'a' IS NOT NULL |+-----------------+|               1 |+-----------------+

13. LIKE

mysql> select '12345' like '12%';+--------------------+| '12345' like '12%' |+--------------------+|                  1 |+--------------------+mysql> select '12345' like '12_';+--------------------+| '12345' like '12_' |+--------------------+|                  0 |+--------------------+

14. REGEXP

mysql> select 'beijing' REGEXP 'jing';+-------------------------+| 'beijing' REGEXP 'jing' |+-------------------------+|                       1 |+-------------------------+mysql> select 'beijing' REGEXP 'xi';+-----------------------+| 'beijing' REGEXP 'xi' |+-----------------------+|                     0 |+-----------------------+

III. logical operators

1.

mysql> select 2 and 0;+---------+| 2 and 0 |+---------+|       0 |+---------+        mysql> select 2 and 1;   +---------+     | 2 and 1 |      +---------+      |       1 |      +---------+

2. or

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. non-

mysql> select not 1;+-------+| not 1 |+-------+|     0 |+-------+mysql> select !0;+----+| !0 |+----+|  1 |+----+

4. exclusive or

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

IV. bitwise operators

1. bitwise AND

mysql> select 3&5;+-----+| 3&5 |+-----+|   1 |+-----+

2. by bit or

mysql> select 3|5;+-----+| 3|5 |+-----+|   7 |+-----+

3. bitwise OR

mysql> select 3^5;+-----+| 3^5 |+-----+|   6 |+-----+

4. bitwise inversion

mysql> select ~18446744073709551612;+-----------------------+| ~18446744073709551612 |+-----------------------+|                     3 |+-----------------------+

5. shift right by bit

mysql> select 3>>1;+------+| 3>>1 |+------+|    1 |+------+

6. shift left by bit

mysql> select 3<<1;+------+| 3<<1 |+------+|    6 |+------+

5. operator priority order

Highest priority: =

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

Lowest priority BINARY, COLLATE