In-depth understanding of MySQL advanced drifting (2)

Operators

Arithmetic operators

MySQL supports most common arithmetic operators that allow us to perform calculations.
"+ (addition), - (subtraction), * (multiplication), / (division, return quotient), % (division, return remainder)", as in the following example:

select 1+2;//加法运算，结果为3 
select 2-1;//减法运算，结果为1 
select 3*2;//乘法运算，结果为6 
select 6/2;//除法运算，结果为3 
select 25%7;//除法运算，结果为4 
select 5/0;//除法运算，结果为null，MySQL除法的除数为0是不允许的

Please Note that all mathematical operations involving integers are performed with 64-bit precision.

Numbers represented as strings are automatically converted to strings wherever possible for calculation. Follow two conversion principles:
1: If a string whose first digit is a number is used in an arithmetic operation, then it is converted to the value of this number
2: If a character contains a mixture of characters and numbers The string cannot be converted to a number correctly and will be converted to 0

select ‘100’+’004’;//结果为104 
select ‘90AA’+’0’;//结果为90 
select ‘10x’*’qwe’;//结果0

Comparison operator

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

select 6=6,3.4=4.5,’a’=’b’;//判断两边是否相等 
select 7<>7,7<>8,’a’<>’b’;//判断两边是否不相等 
select 100>100,10<100;//判断左边是否大于右边，左边是否小于右边 
select 10>=100,10<=1;//判断左边是否大于等于右边，左边是否小于等于右边 
select 10 between 0 and 100;//检验一个值是否存在于一个指定的范围内 
select 10 not between 11 and 100;//检验一个值是否不存在于一个指定的范围内 
select 7 in (1,2,3,4,5,6,7,8,9);//检验一个值是否包含在一个指定的值集合中 
select 7 not in (1,2,3,4,5,6,7,8,9);//检验一个值是否不包含在一个指定的值集合中

By default, comparisons are case-insensitive. We can use the binary keyword to perform case-sensitive operations
select binary 'ross' in ('Chandler','Joey','Ross');

Logical operator

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

The simplest operator among logical operators is the not operator, which is the operator that follows it. The logical test judgment is inverted, turning true into false and false into true
select not 1,not 0,not (2=2),not (100>20);//The result is 0,1, 0,1

and operator can test the validity of two or more values ​​(or expression evaluation), if all its components are true and not null, it returns true , otherwise return false value
select (2=2) and (900
or operator, if the contained value or expression value is true and not null, it returns a true value, otherwise it returns a false value.
select (2=2) or (900
MySQL 4.x and later also includes an additional xor operator that returns true if one (not both) of its arguments is true.
select (1=1) xor (2=4),(1

Bit operation Operator

Bit operator
MySQL contains 6 operators specifically for bit operations
The "|" operator is used to perform a bit or operation, while & is used to perform a bit and operate.
Case:
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 > operators to shift bits to the left and right respectively.
select 1>1;//The result is 128,32

^operator performs bit xor operation
select 1^0,0^1 ,17^9,143^66;//The result is 1,1,24,205

~operator performs bit inversion operation and returns 64-bit integer result
select ~18446744073709551614,~1 ;
//The result is 1,18446744073709551614

##

The above is the detailed content of In-depth understanding of MySQL advanced drifting (2). For more information, please follow other related articles on the PHP Chinese website!