MySQL Operators(比较操作符,逻辑运算符)

比较操作符

比较操作符返回TRUE(1)和FALSE(0)或者NULL
= > < >= <= <> !=这几个不仅可以用于常量比较,还可以用于行比较

Name	Description
BETWEEN … AND …	expr BETWEEN min AND max=(min <= expr AND expr <= max),取非只要在前面加NOT就行
COALESCE(value,...)	返回第一个非NULL的值,如果全为NULL则返回NULL
=	Equal operator
<=>	NULL-safe equal to operator
>	Greater than operator
>=	Greater than or equal operator
GREATEST(value1,value2,...)	返回最大的值
IN (value,...)	SELECT 2 IN (0,3,5,7)-> 0,另外,in用来where后的选择范围
INTERVAL()	Return the index of the argument that is less than the first argument
IS	Test a value against a boolean
IS NOT	Test a value against a boolean
IS NOT NULL	NOT NULL value test
IS NULL	NULL value test
ISNULL()	Test whether the argument is NULL
LEAST(value1,value2,...)	返回最小的参数
<	Less than operator
<=	Less than or equal operator
LIKE	Simple pattern matching
NOT BETWEEN … AND …	Whether a value is not within a range of values
!=, <>	Not equal operator
NOT IN()	Whether a value is not within a set of values
NOT LIKE	Negation of simple pattern matching
STRCMP()	Compare two strings

=号操作符:
mysql中字符串格式会自动转型为数字格式

mysql> SELECT 1 = 0;
        -> 0
mysql> SELECT '0' = 0;
        -> 1
mysql> SELECT '0.0' = 0;
        -> 1
mysql> SELECT '0.01' = 0;
        -> 0
mysql> SELECT '.01' = 0.01;
        -> 1

IS boolean_value

mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN;
        -> 1, 1, 1

IS NOT boolean_value

mysql> SELECT 1 IS NOT UNKNOWN, 0 IS NOT UNKNOWN, NULL IS NOT UNKNOWN;
        -> 1, 1, 0

IS NULL

mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
        -> 0, 0, 1

IS NOT NULL

mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
        -> 1, 1, 0

逻辑运算符

Name	Description
AND, &&	Logical AND
NOT, !	Negates value
OR, ||	Logical OR
XOR	Logical XOR

参考

https://dev.mysql.com/doc/refman/8.0/en/comparison-operators.html