The operator for MySQL notes USES detail
- 2020-05-17 06:39:53
- OfStack
Mysql can operate on the data in the table through the operator, such as the age by the date of birth and so on
There are four types of operators: arithmetic operators, comparison operators, logical operators, and bit operators
Arithmetic operator
Addition, subtraction and multiplication
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, but you can also use the mixed operation later, just pay attention to the priority
Division and modular operations
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 are divisible, and % and mod are taking modules
Note that if the dividend is 0, the result is NULL
Comparison operator
The numerical comparison
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)
The 1 is true, and the 0 is false < > and < = >
< > Is not equal to, is equal to! =
< = > Is equal to, is equal to
In addition, equals and non-equals compare not only numeric values, but also strings
String comparison
mysql> select a,a='24','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
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 is empty, and NULL can also be compared with NULL
between and and not between and
mysql> select a,a between 15 and 30,a not between 15 and 30 from t1;
+------+---------------------+-------------------------+
| a | a between 15 and 30 | a not between 15 and 30 |
+------+---------------------+-------------------------+
| 24 | 1 | 0 |
+------+---------------------+-------------------------+
row in set (0.00 sec)
between and and not between and can determine whether the value is within a range of 1
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 if the operand is in a set of 1
like
mysql> select s,s like 'beijing',s like 'b%g',s like 'bei____',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, with _ representing a single character and % representing multiple characters
Logical operator
With the operation
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 am& & means the same as and
Or operation
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, |, | and or mean the same thing
The operation
mysql> select !1,!2,!null;
+----+----+-------+
| !1 | !2 | !null |
+----+----+-------+
| 0 | 0 | NULL |
+----+----+-------+
row in set (0.00 sec)
And then there's the bit operation, which I haven't used yet, but I'll make up when I use it