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