MySQL note operators using the detailed

The operators include four classes: arithmetic, comparison, logical, and bitwise operators

MySQL can use operators to perform calculations on data in tables, such as age by birth date, etc.

The operators include four classes: arithmetic, comparison, logical, and bitwise operators

Arithmetic operators
Add, subtract, multiply operations

The code is as follows:mysql> select a,a+5,a*2 from T1; +------+------+------+ | A | a+5 | a*2 |   +------+------+------+ |   24 |   29 | 48 | +------+------+------+ row in Set (0.00 sec) The original value here is 24, you can use the blending operation later, you only need to pay attention to the priority level

Division and modulo operations

The code is as follows:mysql> Select A,a/3,a Div 3,a%5,mod (a,5) from T1; +------+--------+---------+------+----------+ | A | A/3 | A Div 3 | a%5 | MoD (a,5) |   +------+--------+---------+------+----------+ | 24 |       8.0000 |    8 |        4 | 4 | +------+--------+---------+------+----------+ row in Set (0.00 sec)
Here/and div represents divisible,% and mod represent modulo

Note that if the divisor is 0, then the result of the calculation is null

Comparison operator Numeric comparison

The code is as follows:mysql> select a,a=24,a<12,a>40,a>=24,a<=24,a!=24,a<>24,a<=>24 from T1; +------+------+------+------+-------+-------+-------+-------+--------+ | A | a=24 | a<12 | a>40 | a>=24 | a<=24 | a!=24 | a<>24 | a<=>24 |   +------+------+------+------+-------+-------+-------+-------+--------+ |    24 |    1 |    0 |     0 |     1 |     1 |     0 |      0 | 1 | +------+------+------+------+-------+-------+-------+-------+--------+ row in Set (0.00 sec)

Here's 1 for true, 0 for fake, need to explain <> and <=>

<> represents not equal to! =

<=> equals equal to =

In addition, equal to and not equal to not only can compare values, but also compare strings

string comparison

The code is as follows:mysql> select A,a= ' ", ' ha ' <> ' ha ', ' xa ' = ' xa ', ' b '! = ' B ' from T1; +------+--------+------------+-----------+----------+ | A | A= ' 24 ' | ' Ha ' <> ' ha ' | ' Xa ' = ' xa ' | ' B '! = ' B ' |   +------+--------+------------+-----------+----------+ |      24 |          1 |         0 |        1 | 0 | +------+--------+------------+-----------+----------+ row in Set (0.00 sec)

Is null and is not NULL

The code is as follows:mysql> Select A,a is null, and A is isn't null from T1; +------+-----------+---------------+ | A | A is null | A is not NULL |   +------+-----------+---------------+ |         24 |             0 | 1 | +------+-----------+---------------+ row in Set (0.00 sec)
This can be used to determine if it is null, or NULL to compare with NULL

Between and and not between and

The code is as follows:mysql> select A,a between and 30,a not between with and from T1; +------+---------------------+-------------------------+ | A | A between and 30 | A not between and 30 |   +------+---------------------+-------------------------+ |                   24 |                       1 | 0 | +------+---------------------+-------------------------+ row in Set (0.00 sec)
Between and and not between and can determine whether a value is within a range inch
Mysql> Select A,a in (1,2,23), A in (24,12,22) from T1;
+------+--------------+----------------+
| A | A In (1,2,23) | A In (24,12,22) |
+------+--------------+----------------+
|            24 |              0 | 1 |
+------+--------------+----------------+
Row in Set (0.00 sec)
Determines whether the operand is within a set

Like

The code is as follows:mysql> select s,s like ' Beijing ', the s ' similar to ' b%g ', s as ' bei____ ', and S as '%jing ' from T2; +---------+------------------+--------------+------------------+----------------+ | s | s like ' Beijing ' | s like ' b%g ' | s like ' bei____ ' | s like '%jing ' | +---------+------------------+--------------+------------------+----------------+ |                Beijing |            1 |                1 |              1 | 1 | +---------+------------------+--------------+------------------+----------------+ row in Set (0.00 sec)

IKE can be used to match a string, _ represents a single character, and% represents more than one character

logical operators and operations

The code is as follows:mysql> select 2&&2,2&&null,2 and 3,2 and 2; +------+---------+---------+---------+ | 2&&2 | 2&&null | 2 and 3 | 2 and 2 |    +------+---------+---------+---------+ |    1 |       NULL |       1 | 1 | +------+---------+---------+---------+ row in Set (0.00 sec)
Here && is the same as and meaning

or arithmetic

The code is as follows:

Mysql> Select 2| | 2,2| | null,2 or 3,2 or 0; +------+---------+--------+--------+ | 2| | 2 | 2| | null | 2 or 3 | 2 or 0 |    +------+---------+--------+--------+ |       1 |      1 |      1 | 1 | +------+---------+--------+--------+ row in Set (0.00 sec)
here | | As the meaning of OR.

Non-operational

The code is as follows:mysql> select!1,!2,!null; +----+----+-------+ |! 1 |!2 |!null |  +----+----+-------+ |  0 |  0 | NULL | +----+----+-------+ row in Set (0.00 sec)
In addition, there is a bit operation, it is useless, and so on when used to fill

MySQL note operators using the detailed