Usage of MySQL notes Operators

Mysql can use operators to perform operations on data in tables, such as age calculation by birth date.

Operators include Arithmetic Operators, comparison operators, logical operators, and bitwise operators.

Arithmetic Operators
Addition, subtraction, multiplication

Copy codeThe Code is as follows: mysql> select a, a + 5, a * 2 from t1;
+ ------ +
| A + 5 | a * 2 |
+ ------ +
| 24 | 29 | 48 |
+ ------ +
Row in set (0.00 sec)

Here, the original value is 24, and you can use a hybrid operation later. You only need to pay attention to the priority.

Division and modulo operation

Copy codeThe Code is as follows: mysql> select a, a/3, a div 3, a % 5, mod (a, 5) from t1;
+ ------ + -------- + --------- + ------ + ---------- +
| 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 represent the division, and % and mod represent the modulo.

Note that if the divisor is 0, the calculation result is NULL.

Comparison Operators
Numerical Comparison

Copy codeThe Code is as follows: mysql> select a, a = 24, a <12, a> 40, a> = 24, a <= 24,! = 24, a <> 24, a <=> 24 from t1;
+ ------ + ------- + -------- +
| A = 24 | a <12 | a> 40 | a> = 24 | a <= 24 |! = 24 | a <> 24 | a <=> 24 |
+ ------ + ------- + -------- +
| 24 | 1 | 0 | 0 | 1 | 1 | 0 | 0 | 1 |
+ ------ + ------- + -------- +
Row in set (0.00 sec)

Here, 1 represents truth, and 0 represents false. You need to describe <> and <=>

<> Not equal! =

<=> Equals, equivalent to =

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

String comparison

Copy codeThe Code is as follows: mysql> select a, a = '24', 'ha' <> 'ha', 'xa '= 'xa', 'B '! = 'B' from t1;
+ ------ + -------- + ------------ + ----------- + ---------- +
| 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 codeThe Code is as follows: mysql> select a, a is null, a is not null from t1;
+ ------ + ----------- + --------------- +
| A is null | a is not null |
+ ------ + ----------- + --------------- +
| 24 | 0 | 1 |
+ ------ + ----------- + --------------- +
Row in set (0.00 sec)

It can be determined whether it is NULL or compared with NULL.

Between and not between and

Copy codeThe Code is as follows: mysql> select a, a between 15 and 30, a not between 15 and 30 from t1;
+ ------ + --------------------- + ------------------------- +
| A between 15 and 30 | a not between 15 and 30 |
+ ------ + --------------------- + ------------------------- +
| 24 | 1 | 0 |
+ ------ + --------------------- + ------------------------- +
Row in set (0.00 sec)

Between and not between and can be used to determine whether the value is within a certain range.

In
Mysql> select a, a in (1, 2, 23), a in (24, 12, 22) from t1;
+ ------ + -------------- + ---------------- +
| A in (1, 2, 23) | a in (24, 12, 22) |
+ ------ + -------------- + ---------------- +
| 24 | 0 | 1 |
+ ------ + -------------- + ---------------- +
Row in set (0.00 sec)
Determines whether the operand is in a set.

Like

Copy codeThe Code is as follows: mysql> select s, s like 'Beijing ', s like' B % G', s like 'bei ____ ', s like' % jing 'from t2;
+ --------- + ------------------ + -------------- + -------------------- + ---------------- +
| S like 'being ing '| 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. _ represents a single character, and % Represents multiple characters.

Logical operators
And operations

Copy codeThe Code is as follows: mysql> select 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 & the same as and means

Or operation

Copy codeThe 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 | the same as or

Non-operation

Copy codeThe Code is as follows: mysql> select! 1 ,! 2 ,! Null;
+ ---- + ------- +
|! 1 |! 2 |! Null |
+ ---- + ------- +
| 0 | 0 | NULL |
+ ---- + ------- +
Row in set (0.00 sec)

In addition, there are bitwise operations, which are not used yet, and can be supplemented when used.