Mysql operator learning notes (arithmetic, comparison, logic, bit operation)

Arithmetic operators

MySQL supports most common arithmetic operators that allow us to execute computation.

"+ (Addition),-(subtraction), * (multiplication),/(Division, return operator), % (Division, return remainder)", for example:

Select 1 + 2; // addition operation. The result is 3.
Select 2-1; // subtraction. The result is 1.
Select 3*2; // multiplication, with 6 results
Select 6/2; // Division operation. The result is 3.
Select 25% 7; // Division operation. The result is 4.

Select 5/0; // Division operation. The result is null. The division of MySQL is 0.
Note that all mathematical operations involving integers are calculated with 64-bit precision.

The number represented by a string is automatically converted to a string wherever it is easy to calculate. There are two conversion principles:

1: If the string whose first digit is a number is used in arithmetic operations, it is converted to the value of this number.

2: If a string that contains a mix of characters and numbers cannot be correctly converted to a number, it will be converted to 0

Select '200' + '004 '; // The result is 100.
Select '90a' + '0'; // The result is 90.
Select '10x' * 'qi'; // The result is 0.

Calculate the value of 89 divided by 44, and the value of 65, 34, and 5.

Comparison Operators

The comparison operator allows us to compare the left and right sides of an expression. The result of a comparison operation is always 1 (true), 0 (false), or null (uncertain ).

Select 6 = 6, 3.4 = 4.5, 'a' = 'B'; // Determine whether the two sides are equal
Select 7 <> 7, 7 <> 8, 'A' <> 'B'; // Determine whether the two sides are not equal
Select 100> 100 <; // Determine whether the left side is greater than the right side, and whether the left side is smaller than the right side
Select 10> =, 10 <= 1; // Determine whether the left side is greater than or equal to the right side, and whether the left side is less than or equal to the right side
Select 10 between 0 and 100; // checks whether a value exists in a specified range.
Select 10 not between 11 and 100; // checks whether a value does not exist in a specified range.
Select 7 in (,); // checks whether a value is included in a specified value set.
Select 7 not in (, 9); // checks whether a value is not included in a specified value set.

By default, the comparison is case-insensitive. We can use the binary keyword to perform case-sensitive operations.

Select binary 'ros' in ('Chandler', 'Joey ', 'ros ');

Logical operators

Logical operators can test the logical validity of one or more expressions (or expression sets. The calculation result containing these operators is always 1 (true), 0 (false), or null (uncertain)

The simplest operator in a logical operator is the not operator. It tests and judges the logic behind it, turning the truth into false, and turning the false into true.

Select not 1, not 0, not (2 = 2), not (100> 20); // The result is

The and operator can test the validity of two or more values (or evaluate the expression). If all its components are true and not null, it returns the true value. Otherwise, it returns the false value.

Select (2 = 2) and (900 <100), ('a' = 'a') and ('C' <'D'); // The result is.

Or operator. If the included value or expression value is true and not null, it returns the true value. Otherwise, it returns the false value.

Select (2 = 2) or (900 <100), ('a' = 'a') or ('C' <'D'); // The result is.

MySQL4.x and later versions also include an additional xor operator. If one (not two) of its parameters is true, it returns the true value.

Select (1 = 1) xor (2 = 4), (1 <2) xor (9 <10); // The Returned result is 1, 0.

Bitwise operators

MySQL contains six operators dedicated to bit operations.

"|" Operator is used to perform a single or operation, while "&" is used to perform a single and operation.

Select 16 | 32, 9 | 4; // The result is 48, 13
Select 30 & 10, 8 & 16; // The result is 10, 0

You can also use the <and> operators to move the bits left and right respectively.

Select 1 <> 1; // The result is, 32

^ The operator executes the xor operation.

Select 1 ^ 9,143 0 ^ 24,205 ^ 66; // The result is

~ Returns the result of a 64-bit integer.

Select ~ 18446744073709551614 ,~ 1; // The result is 1,18446731673709551614.