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.
1. Equal sign operator (=)
- The equal sign operator (=) determines whether the values, strings or expressions on both sides of the equal sign are equal. If they are equal, it returns 1, and if they are not equal, 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:=
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.00 sec)
mysql> SELECT 1 = 2, 0 = 'abc', 1 = 'abc' FROM dual; +-------+-----------+-----------+ | 1 = 2 | 0 = 'abc' | 1 = 'abc' | +-------+-----------+-----------+ | 0 | 1 | 0 | +-------+-----------+-----------+ 1 row in set, 2 warnings (0.00 sec)
- Query salary=10000. Note that in Java, the comparison is = = SELECT employee_id,salary
FROM employees WHERE salary = 10000;SELECT 1 = 2,1 != 2,1 = '1',1 = 'a',0 = 'a'
FROM DUAL;SELECT 'a' = 'a','ab' = 'ab','a' = 'b' FROM DUAL;
- =Use of There is an implicit conversion to the string. If the value conversion is unsuccessful, it is regarded as 0
- If both sides are strings, compare according to ANSI comparison rules.
- As long as null participates in the judgment, the result is null
SELECT 1 = NULL,NULL = NULL # FROM DUAL;
- At this time, there will be no results
SELECT last_name,salary,commission_pct FROM employees #where salary = 6000; WHERE commission_pct = NULL;
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.
- < = >: safety equals. Memory skills: born for NULL.
SELECT 1 <=> 2,1 <=> '1',1 <=> 'a',0 <=> 'a' FROM DUAL;
SELECT 1 <=> NULL, NULL <=> NULL FROM DUAL;
- Commission in query table_ What are the data with PCT null
SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct <=> NULL;
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)
- Query commission_pct equals 0.40SELECT employee_id,commission_pct FROM employees WHERE commission_pct = 0.40; SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> 0.40;
- You can see that when using the safe equal operator, when the values of operands on both sides are NULL, the returned result is 1 instead of NULL, and other returned results are the same as the equal operator.
3. Not equal to operator (< > and! =)
- The not equal operators (< > and! =) are used to judge whether the values of numbers, strings or expressions on both sides are not equal. If they are not equal, they return 1, and if they are equal, they return 0. 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.
- Examples of SQL statements are as follows:
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)
In addition, there are non symbolic operators:
SELECT 3 <> 2,'4' <> NULL, '' != NULL,NULL != NULL FROM DUAL;
4. Air transport operator (IS NULL or ISNULL)
- The airlift operator (is null or isnull) determines whether a value is null. If it is null, it returns 1, otherwise it returns 0. 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)
- Examples of SQL statements are as follows:
- IS NULL \ ISNULL
- Commission in query table_ What are the data with PCT null
SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct IS NULL; or SELECT last_name,salary,commission_pct FROM employees WHERE ISNULL(commission_pct);
- Commission in query table_ What data does PCT not have null
SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct IS NOT NULL; or SELECT last_name,salary,commission_pct FROM employees WHERE NOT commission_pct <=> NULL;
#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;
SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;
5. Non air transport operator (IS NOT NULL)
- 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.
- Examples of SQL statements are as follows:
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.01 sec)
- #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);
6. Minimum operator (least (value 1, value 2,..., value n))
- The syntax format is: leap (value 1, value 2,..., value n). Where, "value n" means there are n values in the parameter list. Returns the minimum value when there are two or more parameters.
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)SELECT LEAST('g','b','t','m'),GREATEST('g','b','t','m')
FROM DUAL;
SELECT LEAST(first_name,last_name),LEAST(LENGTH(first_name),LENGTH(last_name)) FROM employees;
- As can be seen from the results, 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.
7. Maximum operator (greatest (value 1, value 2,..., value n))
- The syntax format is 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.
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)- It can be seen from the results 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.
8. BETWEEN AND operator
- 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.
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)
- BETWEEN condition lower bound 1 AND condition upper bound 2 (query data within condition 1 AND condition 2, including boundary)
SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500;
- Query employee information with salary between 6000 and 8000
SELECT employee_id,last_name,salary FROM employees where salary between 6000 and 8000; or WHERE salary >= 6000 && salary <= 8000;
- After exchanging 6000 and 8000, no data can be queried
SELECT employee_id,last_name,salary FROM employees WHERE salary BETWEEN 8000 AND 6000;
- Query the information of employees whose salary is not between 6000 and 8000
SELECT employee_id,last_name,salary FROM employees WHERE salary NOT BETWEEN 6000 AND 8000; or where salary < 6000 or salary > 8000;
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.
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)SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);
10. NOT IN operator
- NOT IN operator is used to judge whether the given value is not a value IN the IN list. If it is not a value IN the IN list, it returns 1, otherwise it returns 0.
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)- in (set) \ not in (set)
- Query the employee information of departments 10, 20 and 30
SELECT last_name,salary,department_id FROM employees where department_id = 10 or department_id = 20 or department_id = 30; or WHERE department_id IN (10,20,30);
- Query employee information whose salary is not 60007008000
SELECT last_name,salary,department_id FROM employees WHERE salary NOT IN (6000,7000,8000);
11. LIKE operator
- The LIKE operator is mainly used to match strings, usually used for fuzzy matching. If the conditions are met, it returns 1, otherwise it returns 0. If the given value or matching condition is NULL, the returned result is NULL.
- %: characters representing an indefinite number (0, 1, or more)
- The LIKE operator usually uses the following wildcards:
"%": Matches 0 or more characters. "_": Only one character can be matched.
- Examples of SQL statements are as follows:
mysql> SELECT NULL LIKE 'abc', 'abc' LIKE NULL; +-----------------+-----------------+ | NULL LIKE 'abc' | 'abc' LIKE NULL | +-----------------+-----------------+ | NULL | NULL | +-----------------+-----------------+ 1 row in set (0.00 sec)
SELECT first_name FROM employees WHERE first_name LIKE 'S%';
SELECT last_name FROM employees WHERE last_name LIKE '_o%';
- LIKE: fuzzy query
- Query last_ Employee information with character 'a' in name
SELECT last_name FROM employees WHERE last_name LIKE '%a%';
- Query last_ Employee information starting with character 'a' in name
SELECT last_name FROM employees WHERE last_name LIKE 'a%';
- Query last_ Employee information with character 'a' and character 'e' in name
#Writing method 1: SELECT last_name FROM employees WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
#Writing method 2: SELECT last_name FROM employees WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';
- _ : Represents an indeterminate character
- Query the employee information whose third character is' a '
SELECT last_name FROM employees WHERE last_name LIKE '__a%';
- The second character of the query is_ And the third character is the employee information of 'a' Escape characters required:\
SELECT last_name FROM employees WHERE last_name LIKE '_\_a%';
- Use other characters to replace the default \ escape character. The following $represents the custom escape character SELECT last_name FROM employees WHERE last_name LIKE '_$_a%' ESCAPE '$';
- ESCAPE
- Avoiding special symbols: use ESCAPE characters. For example: convert [%] to [$%], [] to [$], and then add [ESCAPE '$'].
SELECT job_id FROM jobs WHERE job_id LIKE 'IT\_%';
- If you use \ to indicate ESCAPE, omit ESCAPE. If it is not \, add ESCAPE.
SELECT job_id FROM jobs WHERE job_id LIKE 'IT$_%' escape '$';
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:
(1)'^'Matches a string that begins with a character after that character. (2)'$'Matches a string that ends with a character that precedes the character. (3)'.'Match any single character. (4)"[...]"Matches any character within square brackets. For example“[abc]"Match“ a"Or“ b"Or“ c". To name the range of characters, use a'-'. "[a-z]"Match any letter, and“[0-9]"Match any number. (5)'*'Matches zero or more characters that precede it. For example“ x*"Match any number of'x'Characters“[0-9]*"Match any number of numbers, and“*"Matches any number of characters.
- Examples of SQL statements are as follows: MySQL > select 'xdrstart' regexp '^ X', 'xdrstart' regexp't $, 'xdrstart' regexp 'Dr'; mysql> SELECT 'xdr630' REGEXP 'dr.30', REGEXP '[xq]';
- Starts with x and ends with t, with dr in the middle
- . represents an indeterminate single character and contains x or q characters