Shulou(Shulou.com)06/02 Report--

This article mainly introduces "what operators does MYSQL have". In daily operation, I believe many people have doubts about what operators MYSQL has. The editor consulted all kinds of data and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the questions of "what operators does MYSQL have?" Next, please follow the editor to study!

1. Arithmetic operator

Arithmetic operators supported by MySQL:

Operator

Action

+

Addition

-

Subtraction

*

Multiplication

/, DIV

Division, return quotient

%, MOD

Divide, return the remainder

Example 1: +, -, *, /,%

Mysql > select 0.1-0.333-0.333-0.333-0.333-0.333 +-+ | 0. 1% 0. 333 | 0. 1-0.333 | 0. 1% 0. 333 | 1 hand 2 | 1% 2 | +-+ -+-+ | 0.433 |-0.233 | 0.0333 | 0.5000 | 1 | +-+ 1 row in set (sec)

Example 2: in the division operation, if the divisor is 0, it will return NULL

Mysql > select 1 row in set row in set + | 1 sec 0 | +-+ | NULL | +-+ 1 sec)

Example 3: another form of modular operation, MOD (a _ r _ b) is the same as a% b

Mysql > select 1% 2 row in set mod (1Mague 2); +-+-+ | 1% 2 | mod (1Mague 2) | +-+-+ | 1 | 1 | +-+-+ 1 row in set (0.00 sec) 2, comparison operator

Comparison operators supported by MySQL:

Operator

Action

=

Equal to

Or! =

Not equal to

NULL secure equals (NULL-safe)

=

Greater than or equal to

BETWEEN

Existence and specified range

IN

Exists in the specified collection

IS NULL

For NULL

IS NOT NULL

Not for NULL

LIKE

Wildcard matching

REGEXP or RLIKE

Regular expression matching

Example 1: "" and "=" on the contrary, if the two operations are not equal, the result is 1, otherwise the result is 0, and "NULL" cannot be used for "" comparison.

Mysql > select 10, 10, 10, 11, 10, 10, 11, nullnull, nullnull, 1, 0, NULL | +-1 row in set (0. 00 sec)

Example 2: "Security equals operator and" = "operation return 1, except that"can also be compared when the value is null."

Mysql > select 11, 10, 10, nullnull, nullnullability, row in set + | 11 | 10 | nullnull | +-+ | 1 | 0 | 1 | +-+ 1 sec)

Example 3: "select not 0 not 1 not null +-+ | not 0 |! 0 | not 1 | not null | +-+-+ | 1 | 1 | 0 | NULL | +-+-+ 1 row in set (0.00 sec)

Example 2: "AND" and "& &" denote logic and operation. If the operands are non-zero, return result 1, otherwise return 0, if any one of the operands is NULL, if one of the values is 0, return 0; other values are all values > 0, and return NULL if there is NULL

Mysql > select (1 and 1), (0 and 1), (3 and 1), (0 and null), (1 and null) +-+ | (1 and 1) | (0 and 1) | (3 and 1) | (0 and null) | (1 and null) | +-+- +-+ | 1 | 0 | 1 | 0 | NULL | +- -+-+ 1 row in set (0.00 sec) mysql > select 1 and NULL and 0 +-+ | 1 and NULL and 0 | +-+ | 0 | +-+ 1 row in set (0.00 sec) mysql > select 1 and NULL and 3 +-+ | 1 and NULL and 3 | +-+ | NULL | +-+ 1 row in set (0.00 sec)

Example 3: "OR" or "| |" indicates logic or operation. When neither Operand is NULL, the result is 1, otherwise it is 0; if one Operand is NULL, if the other Operand is non-zero, the result is 1, otherwise the result is NULL

Mysql > select (1 or 0), (0 or 0), (1 or NULL), (0 or NULL), (NULL or NULL) +-+ | (1 or 0) | (0 or 0) | (1 or NULL) | (0 or NULL) | (NULL or NULL) | +-+-+ -+ | 1 | 0 | 1 | NULL | NULL | +- -+-+ 1 row in set (0.00 sec)

The example 4:XOR represents logical XOR. When any Operand is NULL, the return value is NULL. For non-NULL operands, result 1 is returned if the logical true and false values of the two are different; otherwise, 0 is returned.

Mysql > select (0 xor 0), (1 xor 0), (1 xor 1), (1 xor null), (0 xor null), (null xor null) +-+-+ | (0 xor 0) | (1 xor 0) | (1 xor 1) | (1 xor null) | (0 xor null) | (null xor null) | | +-+ | 0 | 1 | 0 | NULL | NULL | NULL | +-+-+ 1 row in set (0.00 sec) 4. Bit operator

Bit operators supported by MySQL:

Operator

Action

&

Bit and (bit AND)

| |

Bit OR (bit OR)

^

Bit XOR (bit XOR)

~

Inversion of position

> >

Bit shift to the right

Select 2 | 3 row in set + | 2 | 3 | +-+ | 3 | +-+ 1 sec

Example 3: bit XOR makes an exception or operation on the binary bits of multiple operands. 2 ^ 3, the binary number of 2 is 10, the binary number of 2 is 11, the result of 10 ^ 11 is 01, and the result of converting to decimal is 1.

Mysql > select 2 ^ 3; +-+ | 2 ^ 3 | +-+ | 1 | +-+ 1 row in set (0.01sec)

Example 4: bit inversion does the NOT operation on the binary bit of the Operand. Here the Operand can only be one bit. Explain: in mysql, the always bright number is represented by 8 bytes by default, 8 bytes is 64 bits, and the binary of constant 1 is the first 63 zeros. After bit inversion, there are 63 1cals and 1 zeros, and when converted to binary, it is 1844674407709551614.

Mysql > select ~ 1 Magi 18446744073709551614-> +-+ | ~ 1 | ~ 18446744073709551614 | +-+ | 18446744073709551614 | 1 | +-- +-- + 1 row in set (0.01 sec) mysql > select bin (18446744073709551614) +-+ | bin (18446744073709551614) | +- -- + | 11111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111

Example 5: "bit shift to the right" moves the left Operand to the right by the specified number of digits. For example, 0001100100 > > 3, move 3 digits to the right for the binary number 0000001100, and the conversion to the binary number is 12:

Mysql > select 100 > > 3 row in set + | 100 > > 3 | +-+ | 12 | +-+ 1 sec)

Example 6: "bit shift left" moves the number of digits specified by the left Operand to the left. Such as 100

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.