The MySQL operator

Once the table structure in the database is established, the meaning of the data represented in the table is determined. By using the MySQL operator, you can get another data outside the table structure. For example, there is a birth field in the student table that represents the year the student was born. While the arithmetic operator of MySQL uses the current year to reduce the year of the student's birth, the actual age data of the student is obtained. This is the MySQL operator, so being familiar with and mastering the application of the operator is very useful for manipulating the data in the MySQL database. Here's what you can do with the 4 types of operators supported by MySQL.

Arithmetic operators: Perform arithmetic operations such as add, subtract, multiply, divide, and so on.

Comparison operators: include greater than, less than, equal to, or not equal to, and so on. It is mainly used for the comparison of numerical value, string matching and so on. For example: like, in, between and and are null are both comparison operators, and regexp that include regular expressions are also comparison operators.

Logical operators: Include logical operators such as with, or, non-and XOR. Its return value is Boolean, True Value (1) and False (0 or false).

Bitwise operators: Include bitwise-AND, bitwise-OR, bitwise-NEGATION, bitwise-XOR, bitwise-LEFT, and bitwise-right-shift operators. bit operations must first convert the data to binary and then operate in binary format, and after the operation is completed, the binary value is converted to the original type and returned to the user.

Arithmetic operators

The arithmetic operator is the most common type of operator in MySQL. The arithmetic operators supported by MySQL include: add, subtract, multiply, divide, and remainder.

Plus (+), minus (-), and multiply (*) can count multiple operands at the same time. The Division sign (/) and the remainder operators (%) can also compute multiple operands at the same time, but these two symbols are not very good at calculating multiple operands. Div and mod These two operators have only two parameters. For division and Redundancy operations, if the X2 parameter is 0 or NULL, the result will be a null value (NULL).

Comparison operators

The comparison operator is the most commonly used type of operator when querying data. A conditional statement in a SELECT statement often uses a comparison operator. These comparison operators allow you to determine which records in a table are eligible.

Below are some of the more commonly used comparison operators.

1. The operator "=", "=" is used to determine whether the number, string, and expression are equal. Returns 1 if equal, otherwise 0 is returned.

Note: When using the "=" operator to determine whether the two characters are the same, the database system is judged by the ASCII code of the characters. If the ASCII code is equal, the two characters are the same . If the ASCII code is not equal, the two characters are different. Note that a null value (NULL) cannot be judged by using "=".

2. Operator "<>" and "! = "," <> "and"! = "To determine whether numbers, strings, expressions, etc. are not equal." If not equal, 1 is returned; otherwise, 0 is returned. Neither of these symbols can be used to determine the null value (NULL).

3. The operator ">", ">" is used to determine whether the left operand is greater than the right operand. If greater than, returns 1; otherwise, 0 is returned. Similarly, a null value (NULL) cannot be judged using ">".

4. The operator is null, and "is null" to determine whether the operand is a null value (NULL). When the operand is null, the result returns 1; otherwise, 0 is returned. Is isn't null just as opposed to is null.

Description: "=", "<>", "! Operators such as = "," "," ">=", "" "," "<=" cannot be used to determine null values (NULL). Once used, the result will return NULL. If you want to determine whether a value is a null value, you can use is null and is not NULL to determine. Note: null and ' null ' are different, the former represents a null value, and the latter represents a 4-letter string.

5. The operator "between and", "between and" is used to determine whether the data is within a certain value range.

The expression is as follows:

X1 between M and N

If X1 is greater than or equal to M, and less than or equal to N, the result will return 1, otherwise 0 will be returned.

6. The operator "in" and "in" is used to determine whether the data exists in a collection.

The expression is as follows:

X1 in (value 1, value 2, ..., value N)

If X1 equals the value 1 to any one of the values in N, the result returns 1. If not, the result will return 0.

7. The operator is "like" and "like" is used to match the string.

The expression is as follows:

X1 like S1

If X1 matches the string s1, the result returns 1. Otherwise, 0 is returned.

8. The operator "REGEXP", REGEXP "is also used to match a string, but it uses a regular expression to match.

Its expression format is as follows:

X1 REGEXP ' matching method '

If X1 satisfies the match, the result will return 1; otherwise 0 will be returned.

Description: Using the regexp operator to match a string is a simple way to use it. The regexp operator is often associated with "^", "$", and "." Used together. "^" is used to match the starting part of the string; "$" is used to match the end of the string; "." Used to represent one character in a string.

logical operators

A logical operator is used to determine whether an expression is true or false. If the expression is true, the result returns 1. If the expression is false, the result returns 0. Logical operators are also called Boolean operators. MySQL supports 4 logical operators, respectively, with, or, non-and XOR.

1. The "and" Operation

"&&" or "and" are two expressions of "and" operations. If all data is not 0 and is not null (NULL), the result returns 1, if any one of the data is 0, the result returns 0, and if there is one data that is null and no data is 0, the result returns NULL. the "and" operator supports simultaneous operation of multiple data.

2. "or" Operation

"| |" or "or" means "or". When any one of the data is a number other than 0, the result returns 1, if the data does not contain a number other than 0, but contains null, the result returns NULL, and if only 0 o'clock in the operand, the result returns 0. "or" operator "| |" You can manipulate multiple data at the same time.

3. "Non" operation

“！ "Or not" means "non" operation. The "non" Operation returns the opposite result from the operation data. If the operation data is a number other than 0, the result returns 0; If the operation data is 0, the result returns 1; If the manipulation data is NULL, the result returns NULL.

4. "XOR" Operation

XOR denotes an "XOR" operation. When one of the expressions is true and the other expression is false, the result returned by the expression is true, and the result of the two expression is false when the result of the evaluation is true or FALSE.

Bitwise operators

A bitwise operator is an operator that evaluates on a binary number. Bitwise operations will first turn the operand into a binary number and perform a bitwise operation. The result of the calculation is then changed from the binary number back to the decimal number. 6 bit operators are supported in MySQL: bitwise-AND, bitwise-OR, bitwise-REVERSED, bitwise-XOR, bitwise-shifted left, and bitwise-Right.

Precedence of Operators

The MySQL operator