MySQL Optimization-operators and mysql Operators

MySQL Optimization-operators and mysql Operators

Reprinted please indicate the source: http://blog.csdn.net/l1028386804/article/details/46560499

Security equals operator (<=>)

This operator and = Operator perform the same comparison operation, but <=> can be used to determine the NULL value.

When both operands are NULL, the return value is 1 instead of NULL;

When an operand is NULL, the return value is 0 instead of NULL.

The following are the execution results of select null <=> 1 SELECT1 <=> 0 SELECTNULL <=> NULL

When both operands are NULL, the return value is 1 instead of NULL;

LEAST Operator

Syntax format: LEAST (value 1, value 2,... value n), where value n indicates that there are n values in the parameter list. Returns the minimum value if two or more parameters exist.

If any independent variable is NULL, the return value of LEAST () is NULL.

Use the LEAST operator to determine the size. The SQL statement is as follows:

SELECT LEAST(2,0),LEAST('a','b','c'),LEAST(10,NULL)

The result shows that when the parameter is an integer or floating point number, LEAST returns the smallest value;

If the parameter is a string, return the first character in the letter sequence;

When there is NULL in the Compare Value List, the size cannot be determined and the return value is NULL.

GREATEST Operator

Syntax format: GREATEST (value 1, value 2,... value n), where n indicates that there are n values in the parameter list.

If two or more parameters exist, the maximum value is returned.

If any independent variable is NULL, the return value of GREATEST () is NULL.

Use the GREATEST operator to determine the size. The SQL statement is as follows:

SELECT GREATEST(2,0),GREATEST('a','b','c'),GREATEST(10,NULL)

The result shows that when the parameter is an integer or floating point number, GREATEST returns the maximum value;

When the parameter is a string, return the character with the lowest order in the letter;

When there is NULL in the Compare Value List, the size cannot be determined and the return value is NULL.

REGEXP Operator

There are no regular functions or operators in SQLSERVER, and MYSQL is indeed perfect in this respect.

Used to match strings. Syntax format: expr REGEXP match condition. If expr meets the match condition, 1 is returned;

If not, 0 is returned;

If any of the expr or matching conditions is NULL, the result is NULL.

Common wildcard characters:

(1) '^' matches a string starting with a character after the character

(2) '$' matches the string ending with the character following this character

(3) '.' matches any single character

(4) '[...]' matches any character in square brackets. For example, "[abc]" matches a, B, or c.

The character range can use a '-', "[a-z]" to match any letter, and "[0-9]" to match any number.

(5) '*' matches zero or multiple characters before it. For example, "x *" matches any number of '*' characters, and "[0-9] *" matches any number of numbers,

". *" Matches any number of characters.

Use the REGEXP operator to perform string matching. The SQL statement is as follows:

SELECT 'ssky' REGEXP '^s','ssky' REGEXP 'y$' ,'ssky' REGEXP '.sky','ssky' REGEXP '[ab]';

The result shows that the matching string is specified as ssky.

'^ S' indicates matching any string starting with the letter s. Therefore, if the matching condition is met, 1 is returned;

'Y $ 'indicates matching any string ending with the letter y. Therefore, if the matching condition is met, 1 is returned;

'. Sky' indicates matching any string ending with sky with a 4-character length. Therefore, if the matching condition is met, 1 is returned;

'^ S' indicates matching any string starting with the letter s. Therefore, if the matching condition is met, 1 is returned;

'[AB]' indicates matching any string containing letters a or B. The specified string does not contain letters a or B. Therefore, 0 is returned if the matching condition is not met;

Note: Regular Expressions are powerful tools for complex queries. Compared with LIKE string matching, regular expressions can use more wildcard types and make query results more flexible.

Logical operators

Logic AND operator: AND or &&

Logical OR operator: OR |

XOR operator: XOR

If any operand is NULL, the return value is NULL. For non-NULL operands, if both operands are non-0 values or both are 0 values, the return value is 0;

If one is 0 and the other is not 0, 1 is returned.

Use the exclusive or operator XOR for logical judgment. The SQL statement is as follows:

SELECT 1 XOR 1, 0 XOR 0,1 XOR 0,1 XOR NULL,1 XOR 1 XOR 1

The result shows that the operands on both sides of the '1 XOR 1' and '0 XOR 0' operators are non-zero values, or both are zero values. Therefore, 0 is returned;

Operands on both sides of '1 XOR 0'. One is 0, and the other is not 0. The returned result is 1;

One of the operands in '1 XOR null' is NULL, and the returned result is NULL;

'1 XOR 1 XOR 1' has multiple operands and the operators are the same. Therefore, the operators are calculated from left to right, and the result of '1 XOR 1' is 0. Then, an exclusive or operation is performed with 1, therefore, the result is 1.

Note: The calculation of a XOR B is equivalent to (a AND (NOT B) or (NOT a AND (B ))

Bitwise operators

Because it is rarely used, here is a brief introduction.

Bitwise operators are used to test, shift, or test bitwise in binary bytes.

The bitwise operations provided by MYSQL include:

By bit or (|)

Bitwise AND (&)

By bit or (^)

Shift left by bit (<)

Shift right by bit (>)

Bitwise inversion (~) : Reverse all bits

TIPS: You can use the BIN () = binary function to view the binary representation of a decimal number.

For example, the number 20 select bin (20)

Binary: 10100

Note

Some special characters in MYSQL must be escaped before they can be inserted into the database. Otherwise, unexpected results will be generated.

The following special characters must start with the backslash symbol during input.

Enter single quotes :\'

Double quotation marks must be :\''

Input backslash :\\

Enter the carriage return: \ r

Input line break: \ n

Input tab: \ tab

Enter the Escape Character \ B.

Escape before inserting these special characters into the database

For example, if a single quotation mark is inserted and a backslash is added, the insertion is successful.

INSERT INTO table_1(NAME) VALUES('\'')SELECT * FROM table_1