Shulou(Shulou.com)05/31 Report--

This article mainly introduces "which operators are in the MySQL database". In the daily operation, I believe that many people have doubts about which operators are in the MySQL database. The editor consulted all kinds of information and sorted out the simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts about "which operators are in the MySQL database". Next, please follow the editor to study!

Foreword:

There are several operators in the MySQL database: arithmetic operator, comparison operator, logical operator, and bit operator.

Outline of the article:

1. Arithmetic operator

II. Comparison operator

Logical operators

4. Bit operator

Operator precedence

This article will introduce the role of these operators.

1. Arithmetic operator

Note: in division and modular operations, if the divisor is 0, it will be illegal division and the return result is NULL.

Example:

Mysql > create table T1 (num int); mysql > insert into T1 values (64); mysql > select num,num+10,num-3,num*3,num%3,num/3 from T1

The returned content is as follows:

II. Comparison operator

Comparison operators are often used in conditional statements in select statements. Through these comparison operators, you can determine which records in the table meet the conditions, and if the comparison result is true, it returns 1, false returns 0, and NULL returns if the comparison result is uncertain.

1. Equal to operator

The equals operator is used to determine whether numbers, strings, and expressions are equal, and if so, the return value is 1, otherwise the return value is 0, and if one value is NULL, the result of the comparison is NULL.

Mysql > select 1, 2, 2, (1, 3) = (2, 2), NULL=NULL

2. Strictly equal to operator ()

This operator is consistent with the equal operator (=), except that one more function is to determine the null value, as follows:

Mysql > select 10 NULLNULL 2, (1) (2) (2)

The returned result is as follows:

3. Not equal to operator (or! =)

The unequal operator is used to determine whether numbers, strings, and expressions are not equal. If not, 1 is returned, otherwise 0 is returned, but the NULL value cannot be determined.

Mysql > SELECT 'good''god', 12, 4, 5.5, NULLNULL

4 、 IS NULL 、 IS NOT NULL

IS NULL verifies whether a value is NULL. If it is NULL, the return value is 1, otherwise the return value is 0.

IS NOT NULL checks whether a value is not NULL, and if not NULL, the return value is 1, otherwise the return value is 0.

Mysql > SELECT NULL IS NULL, (10) IS NULL, 10 IS NOT NULL

The result returned is as follows:

5 、 BETWEEN AND

Used to determine whether a value falls between two values.

Mysql > select 4 between 1 and 5 between 4 and 6 12 between 9 and 10

The result returned is as follows:

6 、 LEAST 、 GREATEST

LEAST: returns the minimum value when there are two or more parameters. If one value is NULL, the result is NULL.

GREATEST: returns the maximum value when there are two or more parameters, and NULL if one of the values is NULL.

Mysql > select least (2jin0), least ('axiajiaozhongbaoyunui'), least (10penal null), greatest (2Zhone0), greatest (10dint null)

The result returned is as follows:

7 、 IN 、 NOT IN

IN: determines whether a value is any value in the IN list

NOT IN: determines whether a value is not any of the values in the IN list.

Mysql > select 2 in (3, 5, 8, 2), 2 not in, (1)

The result returned is as follows:

8 、 LIKE

The LIKE operator is used to match a string, returning 1 if it matches and 0 if it does not match

LIKE uses two wildcards:'%'to match any number of characters, including zero characters, and'_'to match only one character.

Mysql > select 'stud' like' stu_','stud' like'% d'. Students' like'% d'

The returned result is as follows:

9 、 REGEXP

1) the REGEXP operator is used to match a string, returning 1 if it matches, and 0 if it does not match

2) REGEXP uses the following wildcards:

'^': used to match a string that begins with what

'$': a string that matches what it ends with

'.: used to match any single string

'[.]: used to match any character in square brackets

'*' is used to match zero or more characters in front of it

Mysql > select 'ssky' regexp' ^ slots, 'ssky' regexp' yearly girls, 'ssky' regexp' .sky', 'ssky' regexp' [ab]'

The result returned is as follows:

Logical operators

Logical operators are used to determine whether an expression is true or false. If the expression is true, the result returns 1. If the expression is false, the result returns 0.

1. Logic is not (NOT or! )

When the Operand is 0, the resulting value is 1

When the Operand is non-0, the resulting value is 0

When the Operand is NULL, the resulting value is NULL.

Mysql > select not 10 recording 10 not null (1-1),! (1-1), not 1 recording 1 not null

2. Logic and (AND or & &)

When all operands are non-zero and not NULL, the resulting value is 1

When one or more operands are 0, the resulting value is 0

In other cases, the value is NULL.

Mysql > select 1 and-1 and 1 & & 0 and null,1 & & null

3. Logical or (OR or | |)

When both operands are non-NULL values and either Operand is non-zero, the result is 1, otherwise 0

When one Operand is NULL and the other Operand is non-zero, the result is 1, otherwise the result is NULL

When both operands are NULL, the result is NULL.

Mysql > select 1 or-1 or 0 or null,null 1 | | 2 or null,null | | null

The returned result is as follows:

4. Logical XOR (XOR)

The a XOR b calculation is equivalent to (an AND (NOT b)) or ((NOT a) AND b)

When any Operand is NULL, the return value is NULL

For non-NULL operands, if both operands are non-zero or both are 0, the return result is 0

If one is 0 and the other is non-0, the return result is 1.

Mysql > select 1 xor 1 xor 0 xor null,1 xor 1 xor 1

The returned result is as follows:

4. Bit operator

Bit operators are operators that compute on binary numbers. The bit operation first changes the Operand into a binary number to perform the bit operation. Then change the calculation result from binary to decimal.

1, bit or operator (|)

If one or two of the corresponding binary bits are 1, the operation result of that bit is 1, otherwise it is 0.

Mysql > select 10 | 15 sec 9 | 4 | 2 sec + | 10 | 15 | 9 | 4 | 2 | +-+-+ | 15 | 15 | +-+-+ 1 sec (0.00 sec) 2, bits and operators (&)

If the corresponding binary bit is 1, the operation result of that bit is 1, otherwise it is 0.

Mysql > select 10 & 15 sec 9 & 4 & 2 + + | 10 & 15 | 9 & 4 & 2 | +-+-+ | 10 | 0 | +-+-+ 1 sec (0.00 sec) 3, XOR operator (^)

The corresponding binary bits are not the same, the result is 1, otherwise it is 0.

Mysql > select 10 ^ 15 ^ 1 ^ 0, 1 ^ 1

4. The left shift operator (1 > > 2)

The result returned is as follows:

6. Bit inverse operator (~)

Reverse the corresponding binary number bit by bit, that is, 1 is inverted and changed into 0, 0 is inverted and changed into 1.

Mysql > select 5 & ~ 1

With regard to the above return value explanation: the SQL statement is to carry out the bit and operation (&) between 5 and the number 1 that carries on the bit inversion, and the principle of the operation is that the corresponding binary bit is 1, then the bit is 1, otherwise the binary number of 0101 Magi 1 for 0101Magi 5, the binary number 0001J1 for bit inversion is 1110, then the result of bit sum operation for 0101 and 1110 is 0100, that is, 4.

Operator precedence

Note: the lowest priority is ": ="

The highest priority is:!, BINARY, COLLATE.

At this point, the study of "which operators are in the MySQL database" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.