MySQL Learning Series 4: Operators

There are many operators in MYSQL. This section mainly talks about the operators that are in MYSQL but not in SQLSERVER

Safe equal operator ()

This The operator performs the same comparison as the = operator, but can be used to determine NULL values.

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

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

The following are the execution results of SELECT NULL 1 SELECT 10 SELECT NULL NULL

When both operands are NULL , its return value is 1 and not NULL;

##LEAST operator

The syntax format is: LEAST (value 1, value 2,...value n), where the value n indicates that there are n values ​​in the parameter list. In the case of two or more arguments, returns the minimum value.

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:

As you can see from the results, when the parameter is an integer or floating point number, LEAST will return the smallest value;

When the parameter is a string, the alphabetical order will be returned in the highest order characters;

When there is NULL in the comparison value list, the size cannot be determined, and the return value is NULL

GREATEST operator

The syntax format is: GREATEST(value 1, value2,...valuen), where n means there are n values ​​in the parameter list.

In the case of two or more parameters, return the maximum value.

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:

As can be seen from the results, when the parameter is an integer or floating point number, GREATEST will return the largest value;

When the parameter is a string, the lowest order of letters will be returned. characters;

When there is NULL in the comparison value list, the size cannot be determined, and the return value is NULL

REGEXP operator

There is no regular function or operation in SQLSERVER MYSQL is indeed relatively complete in this regard.

is used to match strings. The syntax format is: expr REGEXP matching conditions. If expr meets the matching conditions, 1 is returned;

If it does not meet, then returns 0;

If either expr or the matching condition is NULL, the result is NULL

Several commonly used wildcard characters:

(1) '^' matches A string that begins with the character after this character

(2) '$' matches a string that ends with the character after this character

(3) '.' matches any single character

(4) '[...]' matches any characters within square brackets. For example, "[abc]" matches a, b, or c.

The range of characters can use a '-', "[a-z]" matches any letter, and "[0-9]" matches any number

(5) '*' matches zero one or more characters preceding it. For example, "x*" matches any number of '*' characters, "[0-9]*" matches any number of digits,

and ".*" matches any number of any characters.

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

As you can see from the results, the specified matching string is ssky.

'^s' means matching any string starting with the letter s, so the matching condition is met and 1 is returned;

'y$' means matching any string ending with the letter y, Therefore, the matching condition is met and 1 is returned;

'.sky' means that it matches any string ending with sky and the character length is 4, so the matching condition is met and 1 is returned;

'^s ' means matching any string starting with the letter s, so the matching condition is met and 1 is returned;

'[ab]' means matching any string containing the letter a or b, and there is no letter a in the specified string There is no letter b, so the matching condition is not met and 0 is returned;

TIPS: Regular expressions are a powerful tool for complex queries. Compared with LIKE string matching, they can use more wildcard types.

Query results are more flexible.

Logical operator

Logical AND operator: AND or &&

Logical OR operator: OR or ||

The above two operators are Not to mention

In SQLSERVER, the AND operator is AND or &; the OR operator is OR or |

http://sqlfool.com/2009/02/bitwise-operations/

Exclusive OR operator: , the return result is 0;

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

Use the exclusive OR operator XOR for logical judgment, the SQL statement is as follows

You can see from the results that the operands on both sides of the operators in '1 XOR 1' and '0 XOR 0' are both non-zero values, or both are zero values, so Returns 0;

'1 XOR 0′ has two operands, one is 0 and the other is non-0, and the return result is 1;

'1 XOR NULL' has One operand is NULL, and the return result is NULL;

'1 XOR 1 XOR 1' has multiple operands, and the operators are the same, so the operators are calculated from left to right, '1 XOR 1' The result is 0, and then XOR operation is performed with 1, so the result is 1.

TIPS: The calculation of a Used, here is just a brief introduction

Bit operators are used to test, shift or test bits in binary bytes

The bit operations provided in MYSQL are

Bitwise OR (|)

Bitwise AND (&)

Bitwise XOR (^)

Bitwise left shift (
Bitwise right shift (>>)

Bitwise inversion (~): Invert all bits

TIPS: You can use BIN()=binary The function checks the binary representation of a decimal number

For example, the number 20 SELECT BIN(20)

The binary representation is: 10100




Similarly SQLServer also has bit operators, but not as many as MySQL, as follows

Special Tips

Some special characters in MYSQL require escape characters must be inserted into the database, otherwise unexpected results will occur.

The following special characters need to be preceded by a backslash when entering

Inputting single quotes requires: \'

Inputting double quotes requires:\”

Enter backslash: \\

Enter carriage return: \r

Enter newline: \n

Enter tab: \tab

Enter the backspace character:\b

Be sure to escape these special characters before inserting them into the database

In SQLSERVER, these special characters are not preceded by a backslash\ , but add single quotes '

For example, insert a single quote, add a backslash, and the insertion is successful




Summary

This This section briefly introduces some operators and special characters in MYSQL, and compares the differences with SQLSERVER

The above is the content of MySQL Learning Series 4: Operators. For more related content, please pay attention to the PHP Chinese website ( www.php.cn)!

##