My MySQL learning experience (v) operator

There are many operators in MySQL, this one is mainly about MySQL, and SQL Server does not have operators

Security equals operator (<=>)

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

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

When an operand is null, its return value is 0 and not null.

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

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

If either argument is null, the return value of least () is null

Using the least operator for sizing, SQL statements are as follows:

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

As the result can be seen, when an integer or floating-point number is in the parameter, the least returns the smallest value;

When the argument is a string, returns the first character in the alphabetical order;

The size cannot be determined when there is null in the comparison value list, 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 parameter list.

Returns the maximum value in cases where there are two or more parameters.

If either argument is null, the return value of greatest () is null

Using the greatest operator for sizing, SQL statements are as follows:

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

As the result can be seen, when an integer or floating-point number is in the parameter, the greatest will return the maximum value;

When the argument is a string, returns the most sequential character in the letter;

The size cannot be determined when there is null in the comparison value list, and the return value is null

REGEXP operator

There is no regular function or operator in SQL Server, MySQL is quite perfect in this respect.

Used to match strings in the syntax format: expr REGEXP match condition, if expr satisfies the match condition, returns 1;

Returns 0 if it is not satisfied;

If either expr or the match condition 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 that ends with the character following the character

(3) '. ' Match any single 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, while ' [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:

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

As can be seen by the result, the specified match string is Ssky.

' ^s ' means matching any string that begins with the letter S, thus satisfying the match condition and returning 1;

' y$ ' means matching any string ending with the letter Y, thus satisfying the match condition, returning 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, thus satisfying the match condition and returning 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, so the match condition is not met, returning 0;

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

More flexible query results.

logical operators

Logic and operators: and OR &&

Logical OR Operator: OR OR | |

The above two operators will not say

in SQL Server, the operator is and or &; or operator is OR or |

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

View Code

Xor operator:

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

If one is a value of 0 and the other is a value other than 0, the return result is 1

The SQL statement is logically judged by using XOR operators, as follows

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

Results can be seen in the ' 1 XOR 1 ' and ' 0 XOR 0 ' operands on both sides of the operator are not 0 values, or both are 0 values, so return 0;

' 1 XOR 0 ' on both sides of the operand, one is 0 value, the other is a non-0 value, the return result is 1;

' 1 XOR null ' has an operand that is null and returns a null result;

' 1 XOR 1 XOR 1 ' has more than one operand, the operator is the same, so the operator evaluates from left to right, the result of ' 1 XOR 1 ' is 0, and 1 is an XOR operation, so the result is 1.

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

Bitwise operators

As a result of less use, here only to do a brief introduction

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

The bit operations provided in MySQL are

Bitwise OR (|)

Bitwise AND (&)

Bitwise XOR or (^)

Bitwise left SHIFT (<<)

Bitwise right SHIFT (>>)

Bitwise inverse (~): Reverses all bits

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

For example 20 this number SELECT BIN (a)

Binary representation is: 10100

SQL Server also has a bitwise operator, but not as many as MySQL, as follows

DECLARE @i BITSET @i=0select @i |  3 as ' bitwise OR ' SELECT @i & 3 as ' bitwise with ' SELECT @i ^ 3 as ' bitwise XOR ' SELECT [email protected] as ' non ' SET @i=1select [email protected] As ' non '

Special Tips

Some special characters in MySQL need an escape character to insert the database, otherwise unexpected results are generated.

The following special characters need to be preceded by a backslash symbol at input

Enter single quotation marks required: \ '

Entering double quotes requires: \ '

Input backslash: \ \

Enter a carriage return: \ r

Enter line break: \ n

Input tab: \tab

Input backspace: \b

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

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

For example, insert a single quotation mark, add a backslash, insert a successful

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

My MySQL learning experience (v) operator