1, Arithmetic operator
Arithmetic operators are mainly used for mathematical operations. They can connect two values or expressions before and after the operator, and perform addition (+), subtraction (-), multiplication (*), division (/) and modulus (%) operations on the values or expressions.
1.1 addition and subtraction operators
mysql> SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5 FROM dual;
The following conclusions can be drawn from the calculation results:
- An integer type value adds and subtracts an integer, and the result is still an integer;
- An integer type value adds and subtracts floating-point numbers, and the result is a floating-point number;
- The priority of addition and subtraction is the same. The result of adding before subtraction is the same as that of subtraction before addition;
- In Java, if there is a string on the left and right sides of +, it represents the splicing of strings. However, in mysql, + only indicates the addition of values. If a non numeric type is encountered, try to convert it to a numeric value first. If the conversion fails, press 0. (supplement: String splicing in MySQL should be realized by using string function CONCAT())
1.2 multiplication and division operators
SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,100 + 2 * 5 / 2,100 /3, 100 DIV 0 FROM dual;
The results are as follows:
#Calculate the annual basic salary of employees SELECT employee_id,salary,salary * 12 annual_sal FROM employees;
The following conclusions can be drawn from the calculation results:
- When a number is multiplied by integer 1 and divided by integer 1, the original number is still obtained;
- A number multiplied by floating point number 1 and divided by floating point number 1 becomes a floating point number, and the value is equal to the original number;
- After a number is divided by an integer, whether it can be divided or not, the result is a floating point number;
- When one number is divided by another number, the result will be a floating-point number and will be kept to 4 decimal places;
- The priority of multiplication and division is the same. The result of multiplication before division is the same as that of division before multiplication.
- In mathematical operations, 0 cannot be used as a divisor. In MySQL, a number divided by 0 is NULL.
1.3 modulus (remainder) operator
SELECT 12 % 3, 12 MOD 5 FROM dual;
The results are as follows:
Example:
#Filter out employees_ ID is an even number of employees SELECT * FROM employees WHERE employee_id MOD 2 = 0;
2, Comparison operator
The comparison operator is used to compare the operands on the left and right of the expression. If the comparison result is true, it will return 1, if the comparison result is false, it will return 0, and in other cases, it will return NULL.
Comparison operators are often used as conditions of SELECT query statements to return qualified result records.
2.1 equal sign operator
- The equal sign operator (=) breaks whether the values, strings or expressions on both sides of the equal sign are equal. If equal, it returns 1, and if not, it returns 0
- When using the equal sign operator, follow the following rules:
- If the value, string or expression on both sides of the equal sign are strings, MySQL will compare them according to the string, which compares whether the ANSI encoding of the characters in each string is equal.
- If the values on both sides of the equal sign are integers, MySQL will compare the size of the two values according to integers
- If one of the values on both sides of the equal sign is an integer and the other is a string, MySQL will convert the string into a number for comparison
- If one of the values, strings, or expressions on either side of the equal sign is NULL, the comparison result is NULL
- Comparison: use of assignment symbols in SQL:=
- In SQL, if + has no connection function, it means that the addition operation will implicitly convert the string into a numerical value, and 'a' is treated as 0
Example 1:
mysql> SELECT 1 = 1, 1 = '1', 1 = 0, 'a' = 'a', (5 + 3) = (2 + 6), '' = NULL , NULL = NULL; +-------+---------+-------+-----------+-------------------+-----------+-------------+ | 1 = 1 | 1 = '1' | 1 = 0 | 'a' = 'a' | (5 + 3) = (2 + 6) | '' = NULL | NULL = NULL | +-------+---------+-------+-----------+-------------------+-----------+-------------+ | 1 | 1 | 0 | 1 | 1 | NULL | NULL | +-------+---------+-------+-----------+-------------------+-----------+-------------+ 1 row in set (0.02 sec)
Example 2:
mysql> SELECT 1 = 2, 0 = 'abc', 1 = 'abc' FROM dual; +-------+-----------+-----------+ | 1 = 2 | 0 = 'abc' | 1 = 'abc' | +-------+-----------+-----------+ | 0 | 1 | 0 | +-------+-----------+-----------+ 1 row in set (0.00 sec)
Example 3:
mysql> SELECT 100 + '1', 100 + 'a', 100 + NULL FROM DUAL; +-----------+-----------+------------+ | 100 + '1' | 100 + 'a' | 100 + NULL | +-----------+-----------+------------+ | 101 | 100 | NULL | +-----------+-----------+------------+ 1 row in set (0.00 sec)
2.2 safety equals operator
- The functions of the safe equal operator (< = >) and the equal operator (=) are similar. The only difference is that '< = >' can be used to judge NULL
- When both operands are NULL, the return value is 1 instead of NULL; When an operand is NULL, its return value is 0 instead of NULL
Example 1:
mysql> SELECT 1 <=> '1', 1 <=> 0, 'a' <=> 'a', (5 + 3) <=> (2 + 6), '' <=> NULL,NULL <=> NULL FROM dual; +-----------+---------+-------------+---------------------+-------------+---------------+ | 1 <=> '1' | 1 <=> 0 | 'a' <=> 'a' | (5 + 3) <=> (2 + 6) | '' <=> NULL | NULL <=> NULL | +-----------+---------+-------------+---------------------+-------------+---------------+ | 1 | 0 | 1 | 1 | 0 | 1 | +-----------+---------+-------------+---------------------+-------------+---------------+ 1 row in set (0.00 sec)
Example 2:
#Commission in query table_ What are the data with PCT null SELECT employee_id,last_name,commission_pct FROM employees WHERE commission_pct <=> NULL;
2.3 not equal to operator
- The not equal operators (< > and! =) are used to judge whether the values of numbers, strings or expressions on both sides are not equal,
Returns 1 if not equal and 0 if equal - Not equal to operator cannot judge NULL value. If either of the values on both sides is NULL, or both sides are NULL, the result is NULL
Example:
mysql> SELECT 1 <> 1, 1 != 2, 'a' != 'b', (3+4) <> (2+6), 'a' != NULL, NULL <> NULL; +--------+--------+------------+----------------+-------------+--------------+ | 1 <> 1 | 1 != 2 | 'a' != 'b' | (3+4) <> (2+6) | 'a' != NULL | NULL <> NULL | +--------+--------+------------+----------------+-------------+--------------+ | 0 | 1 | 1 | 1 | NULL | NULL | +--------+--------+------------+----------------+-------------+--------------+ 1 row in set (0.00 sec)
2.4 air transport operator
The airlift operator (IS NULL or ISNULL) determines whether a value IS NULL. If it IS NULL, it returns 1, otherwise it returns 0.
Example 1:
mysql> SELECT NULL IS NULL, ISNULL(NULL), ISNULL('a'), 1 IS NULL;
+--------------+--------------+-------------+-----------+
| NULL IS NULL | ISNULL(NULL) | ISNULL('a') | 1 IS NULL |
+--------------+--------------+-------------+-----------+
| 1 | 1 | 0 | 0 |
+--------------+--------------+-------------+-----------+
1 row in set (0.00 sec)
Example 2:
#Query commission_pct equals NULL. Compare the following four ways of writing SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NULL; SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> NULL; SELECT employee_id,commission_pct FROM employees WHERE ISNULL(commission_pct); SELECT employee_id,commission_pct FROM employees WHERE commission_pct = NULL;
2.5 non air operator
The non air transport operator (IS NOT NULL) determines whether a value IS NOT NULL. If it IS NOT NULL, it returns 1, otherwise it returns 0
Back to 0.
Example 1:
mysql> SELECT NULL IS NOT NULL, 'a' IS NOT NULL, 1 IS NOT NULL; +------------------+-----------------+---------------+ | NULL IS NOT NULL | 'a' IS NOT NULL | 1 IS NOT NULL | +------------------+-----------------+---------------+ | 0 | 1 | 1 | +------------------+-----------------+---------------+ 1 row in set (0.00 sec)
Example 2:
#Query commission_pct is not equal to NULL SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NOT NULL; SELECT employee_id,commission_pct FROM employees WHERE NOT commission_pct <=> NULL; SELECT employee_id,commission_pct FROM employees WHERE NOT ISNULL(commission_pct);
2.6 minimum operator
Syntax format: leap (value 1, value 2,..., value n)
Where, the value n indicates that there are n values in the parameter list. Returns the minimum value when there are two or more parameters
Example:
mysql> SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);
+---------------+--------------------+-----------------+
| LEAST (1,0,2) | LEAST('b','a','c') | LEAST(1,NULL,2) |
+---------------+--------------------+-----------------+
| 0 | a | NULL |
+---------------+--------------------+-----------------+
1 row in set (0.00 sec)
The results show that:
- When the parameter is an integer or floating-point number, leap will return the smallest value
- When the parameter is a string, the character with the highest order in the alphabet is returned
- When there is NULL in the comparison value list, the size cannot be determined, and the return value is NULL
3.7 maximum operator
Syntax format: greatest (value 1, value 2,..., value n)
Where n indicates that there are n values in the parameter list. When there are two or more parameters, the return value is the maximum value. If any argument is NULL, the return value of GREATEST() is NULL
Example:
mysql> SELECT GREATEST(1,0,2), GREATEST('b','a','c'), GREATEST(1,NULL,2);
+-----------------+-----------------------+--------------------+
| GREATEST(1,0,2) | GREATEST('b','a','c') | GREATEST(1,NULL,2) |
+-----------------+-----------------------+--------------------+
| 2 | c | NULL |
+-----------------+-----------------------+--------------------+
1 row in set (0.00 sec)
The results show that:
- When the parameter is an integer or floating-point number, GREATEST will return the largest value
- When the parameter is a string, the last character in the alphabet is returned
- When there is NULL in the comparison value list, the size cannot be determined, and the return value is NULL
Operator between3.8
The format used by the BETWEEN operator is usually SELECT D FROM TABLE WHERE C BETWEEN A AND B. at this time, when C is greater than or equal to A and C is less than or equal to B, the result is 1, otherwise the result is 0.
Example 1:
mysql> SELECT 1 BETWEEN 0 AND 1, 10 BETWEEN 11 AND 12, 'b' BETWEEN 'a' AND 'c'; +-------------------+----------------------+-------------------------+ | 1 BETWEEN 0 AND 1 | 10 BETWEEN 11 AND 12 | 'b' BETWEEN 'a' AND 'c' | +-------------------+----------------------+-------------------------+ | 1 | 0 | 1 | +-------------------+----------------------+-------------------------+ 1 row in set (0.00 sec)
Example 2:
mysql> SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500; +-------------+---------+ | last_name | salary | +-------------+---------+ | Khoo | 3100.00 | | Baida | 2900.00 | | Tobias | 2800.00 | | Himuro | 2600.00 | ...
3.9 IN operator
The IN operator is used to determine whether a given value is a value IN the IN list. If yes, it returns 1, otherwise it returns 0. If the given value is null, or if NULL exists IN the IN list, the result is null.
Example 1:
mysql> SELECT 'a' IN ('a','b','c'), 1 IN (2,3), NULL IN ('a','b'), 'a' IN ('a', NULL);
+----------------------+------------+-------------------+--------------------+
| 'a' IN ('a','b','c') | 1 IN (2,3) | NULL IN ('a','b') | 'a' IN ('a', NULL) |
+----------------------+------------+-------------------+--------------------+
| 1 | 0 | NULL | 1 |
+----------------------+------------+-------------------+--------------------+
1 row in set (0.00 sec)
Example 2:
SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);
3.10 NOT IN operator
The NOT IN operator is used to determine whether the given value is not a value IN the IN list. If it is not a value IN the IN list
If there are two values, 1 is returned; otherwise, 0 is returned.
Example:
mysql> SELECT 'a' NOT IN ('a','b','c'), 1 NOT IN (2,3);
+--------------------------+----------------+
| 'a' NOT IN ('a','b','c') | 1 NOT IN (2,3) |
+--------------------------+----------------+
| 0 | 1 |
+--------------------------+----------------+
1 row in set (0.00 sec)
3.11 LIKE operator
The LIKE operator is mainly used to match strings and is usually used for fuzzy matching
- Returns 1 if the condition is met, otherwise returns 0
- If the given value or matching condition is NULL, the returned result is NULL
The LIKE operator usually uses the following wildcards:
- %: matches 0 or more characters
- _: Only one character can be matched
Example 1:
mysql> SELECT NULL LIKE 'abc', 'abc' LIKE NULL; +-----------------+-----------------+ | NULL LIKE 'abc' | 'abc' LIKE NULL | +-----------------+-----------------+ | NULL | NULL | +-----------------+-----------------+ 1 row in set (0.00 sec)
Example 2:
SELECT first_name FROM employees WHERE first_name LIKE 'S%';
Example 3:
SELECT last_name FROM employees WHERE last_name LIKE '_o%';
ESCAPE
- Avoiding special symbols: using escape characters
- For example: convert [%] to [$%], [] to [$], and then add [ESCAPE '$']
Example:
mysql> SELECT job_id FROM jobs WHERE job_id LIKE 'IT\_%'; +---------+ | job_id | +---------+ | IT_PROG | +---------+ 1 row in set (0.00 sec)
- If you use \ to indicate ESCAPE, omit ESCAPE. If it is not \, the ESCAPE keyword should be added
Example:
mysql> SELECT job_id FROM jobs WHERE job_id LIKE 'IT$_%' escape '$'; +---------+ | job_id | +---------+ | IT_PROG | +---------+ 1 row in set (0.00 sec)
3.12 REGEXP operator
REGEXP operator is used to match strings. The syntax format is: expr REGEXP matching condition. If expr meets the matching conditions, return 1; If not, 0 is returned.
If either expr or matching condition is NULL, the result is NULL.
The following wildcards are commonly used in REGEXP operator matching:
- ^Matches a string that begins with a character after that character
- $matches a string that ends with a character preceding that character
- . match any single character
- [...] Matches any character within square brackets
- For example, [abc] matches a or b or c.
- To name the range of characters, use a -. [a-z] matches any letter and [0-9] matches any number
- *Matches zero or more characters that precede it
- For example, x * matches any number of x characters, [0-9] * matches any number of numbers, and * matches any number of characters
Example 1:
mysql> SELECT 'shkstart' REGEXP '^s', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk'; +------------------------+------------------------+------------------------+ | 'shkstart' REGEXP '^s' | 'shkstart' REGEXP 't$' | 'shkstart' REGEXP 'hk' | +------------------------+------------------------+------------------------+ | 1 | 1 | 1 | +------------------------+------------------------+------------------------+ 1 row in set (0.02 sec)
Example 2:
mysql> SELECT 'xiaoyangren' REGEXP 'ya.g', 'xiaoyangren' REGEXP '[cai]'; +-----------------------------+------------------------------+ | 'xiaoyangren' REGEXP 'ya.g' | 'xiaoyangren' REGEXP '[cai]' | +-----------------------------+------------------------------+ | 1 | 1 | +-----------------------------+------------------------------+ 1 row in set (0.00 sec)
3.13 summary of non symbolic operators
