MySQL can perform operations on data in a table, such as age by birth date, etc.
The operators include the four classes: the arithmetic, comparison, logical, and bitwise operators
Arithmetic operators
Add, subtract, multiply operations
Copy Code code 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, then you can also use the mixed operation, only need to pay attention to priority
Division and modulo operations
Copy Code code 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 division,% and MoD representative modulo
Note that if the divisor is 0, the result is null
Comparison operators
Numerical comparison
Copy Code code 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 the 1 represents true, 0 represents the false, need to explain is <> and <=>
<> representative is not equal to!=
<=> represents equal to =
In addition, equal and not equal to not only can compare the numeric value, but also can compare the string
String comparisons
Copy Code code 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
Copy Code code as follows:
Mysql> Select A,a is null, A is not null from T1;
+------+-----------+---------------+
| A | A is null | A is not NULL |
+------+-----------+---------------+
| 24 | 0 | 1 |
+------+-----------+---------------+
Row in Set (0.00 sec)
Here you can tell if it's empty, or null to compare it to null.
Between and and not between and
Copy Code code as follows:
Mysql> Select A,a between and 30,a not between;
+------+---------------------+-------------------------+
| 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
In
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
Copy Code code as follows:
Mysql> Select s,s like ' Beijing ', s like ' b%g ', s like ' bei____ ' and s like '%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 strings, _ to represent a single character, and% to represent multiple characters
logical operators
and operation
Copy Code code 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 && and meaning.
or operation
Copy Code code 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 | | The same as or means
Non-op
Copy Code code 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 of operation, is still useless to, and so on when used to make up
