Learning about Mysql In contrast to MSSQL (v)--operator _mysql

Security equals operator (<=>)

This operator performs the same comparison operation as the = operator, although <=> can be used to determine null values.

When two operands are null, the return value is 1 and NOT null;

When one operand is null, it returns a value of 0 instead of NULL.

The following are the execution results of the Select null <=>1 select 1<=>0 Select null <=>null

When two operands are null, the 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 argument list. Returns the minimum value in the case of two or more parameters.

If any of the arguments are null, the return value of least () is null

Using the least operator for size estimation, the SQL statement is as follows:

Copy Code code as follows:

SELECT least (2,0), least (' A ', ' B ', ' C '), least (10,null)

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

When the parameter is a string, returns the character with the highest order in the letter;

When NULL is in the list of comparison values, the size cannot be judged and the return value is null

Greatest operator

The syntax format is: Greatest (value 1, value 2, ...). Value N, where n indicates that there are n values in the argument list.

Returns the maximum value in the case of two or more parameters.

If any of the arguments are null, the return value of greatest () is null

Using the greatest operator for size estimation, the SQL statement is as follows:

Copy Code code as follows:

SELECT Greatest (2,0), greatest (' A ', ' B ', ' C '), Greatest (10,null)

The result shows that when the argument is an integer or a floating-point number, the greatest returns the largest value;

When the parameter is a string, returns the character in the order of the letter;

When NULL is in the list of comparison values, the size cannot be judged and the return value is null

REGEXP operator

There are no regular functions or operators in SQL Server, and MySQL is quite perfect in this respect.

Used to match a string, the syntax format is: expr REGEXP match condition, if expr satisfies match condition, returns 1;

Returns 0 if it is not satisfied;

If either expr or a matching criterion is null, the result is null

Several common wildcard characters:

(1) ' ^ ' matches a string that begins with the character following the character

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

(3) '. ' Match any single word character

(4) ' [...] ' Matches any character within the square brackets. For example, "[ABC]" matches a, B, or C.

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

(5) ' * ' matches 0 or more characters in front of him. For example, "x*" matches any number of ' * ' characters, "[0-9]*" matches any number of numbers,

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

Using the regexp operator for string matching operations, the SQL statement is as follows:

Copy Code code as follows:

SELECT ' Ssky ' REGEXP ' ^s ', ' Ssky ' REGEXP ' y$ ', ' Ssky ' REGEXP '. Sky ', ' Ssky ' REGEXP ' [ab] ';

As the result can see, the specified match string is Ssky.

' ^s ' means matching any string that begins with the letter S, so it satisfies the matching criteria and returns 1;

' y$ ' means matching any string ending with the letter Y, so it satisfies the matching criteria and returns 1;

'. Sky ' means matching any string ending in sky with a character length of 4, thus satisfying the matching criteria and returning 1;

' ^s ' means matching any string that begins with the letter S, so it satisfies the matching criteria and returns 1;

' [ab] ' means matching any string containing the letter A or B, specifying that there is no letter A in the string and no letter B, and therefore does not meet the matching criteria, returning 0;

TIPS: Regular expressions are a powerful tool for complex queries and can be used with more wildcard types than like string matches

The query results are more flexible.

logical operators

Logic and operators: and OR &&

Logical OR Operator: OR OR | |

The above two operators do not say, but more than SQL Server && and | | The wording

XOR Operator: Exclusive

When either operand is null, the return value is null, and for non-null operands, the result is 0 if the two operands are either 0 or 0 values;

If one is a value of 0 and the other is not 0, the result returns 1

Use XOR to make logical judgments with the SQL statement as follows

Copy Code code as follows:

SELECT 1 XOR 1, 0 XOR 0,1 xor 0,1 xor null,1 XOR 1 XOR 1

From the result, you can see that the operands on either side of the ' 1 XOR 1 ' and ' 0 XOR 0 ' are not 0, or all 0, so return 0;

The operands on either side of the ' 1 XOR 0 ' are 0 and the other is a non 0 value, and the result is 1;

' 1 XOR null ' has an operand of null and the result is null;

' 1 XOR 1 XOR 1 ' has multiple operands, the operators are the same, so the operators are computed from left to right, the result of ' 1 XOR 1 ' is 0, and then the 1 is different or operation, so the result is 1.

Tips:a XOR B is computed equivalent to (A and (not)) or (not A and (b))

Bitwise operators

As a result of less use, here is only a simple introduction, the same bit operator is not in SQL Server

Bitwise operators are used to test, shift, or test the bits in a binary byte

The bit operations provided in MySQL have

Bitwise OR (|)

by Bit and (&)

Bitwise XOR or (^)

Move left (<<) by bit

Bitwise right SHIFT (>>)

Bitwise REVERSE (~): Reverse All Bits

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

For example 20 this number SELECT BIN (20)

The binary representation is: 10100

Special Tips

Some MySQL special characters need to use the escape character to insert the database, otherwise produce unexpected results.

The following special characters need to be preceded by a backslash sign

To enter single quotes: \ '

Entering double quotes requires: \ '

Enter back slash: \

Enter return character: \ r

Enter line breaks: \ n

Input tabs: \tab

Input backspace: \b

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

In SQL Server, these special characters are not preceded by a backslash \, but by a single quote '

For example, insert a single quote, plus a backslash, and insert a successful

Copy Code code as follows:

INSERT into Table_1 (NAME) VALUES (' \ ')

SELECT * from Table_1

Summarize

This section briefly describes some of the operators and special characters in MySQL, and compares the differences with SQL Server