对比操作符详解 1、=号对比操作符 ~~~ mysql> select '0.01'=1; ###不相等结果为0，相当于false +----------+ | '0.01'=1 | +----------+ | 0 | +----------+ ~~~ ~~~ mysql> select 1=1; ####相当结果为1，相当于true +-----+ | 1=1 | +-----+ | 1 | +-----+ ~~~ 2、!=/<> 不等号操作符 ~~~ mysql> SELECT '.01' <> '0.02'; +-----------------+ | '.01' <> '0.02' | +-----------------+ | 1 | +-----------------+ 1 row in set (0.00 sec) mysql> SELECT '0.01' <> '0.01'; +------------------+ | '0.01' <> '0.01' | +------------------+ | 0 | +------------------+ 1 row in set (0.01 sec) ~~~ 3、is操作符 is操作符 ~~~ mysql> SELECT 1 IS TRUE, 0 IS FALSE, NULL IS UNKNOWN; -> 1, 1, 1 ~~~ Is not操作符 ~~~ 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 ~~~ 4、Expr between min and max对比操作符 相当于min <= expr AND expr <= max ~~~ mysql> SELECT 2 BETWEEN 1 AND 3, 2 BETWEEN 3 and 1; -> 1, 0 mysql> SELECT 1 BETWEEN 2 AND 3; -> 0 mysql> SELECT 'b' BETWEEN 'a' AND 'c'; -> 1 mysql> SELECT 2 BETWEEN 2 AND '3'; -> 1 mysql> SELECT 2 BETWEEN 2 AND 'x-3'; -> 0 ~~~ expr NOT BETWEEN min AND max 相当于NOT (expr BETWEEN min AND max) 5、COALESCE(value,...)对比操作符 返回第一个非NULL的值，如果没有非null值，则返回NULL ~~~ mysql> SELECT COALESCE(NULL,2); -> 2 mysql> SELECT COALESCE(NULL,NULL,NULL); -> NULL ~~~ 6、GREATEST(value1,value2,...) 返回其中最大的值 ~~~ mysql> SELECT GREATEST(2,0); -> 2 mysql> SELECT GREATEST(34.0,3.0,5.0,767.0); -> 767.0 mysql> SELECT GREATEST('B','A','C'); -> 'C' ~~~ 7、expr IN (value,...)对比操作符 当expr值能在values中找到，则返回1，否则返回0 ~~~ mysql> SELECT 2 IN (0,3,5,7); -> 0 mysql> SELECT 'wefwf' IN ('wee','wefwf','weg'); -> 1 mysql> SELECT (3,4) IN ((1,2), (3,4)); -> 1 mysql> SELECT (3,4) IN ((1,2), (3,5)); -> 0 ~~~ expr NOT IN (value,...)对比操作符 8、Isnull(expr)操作符 如果expr是null，则返回1，否则返回0 ~~~ mysql> SELECT ISNULL(1+1); -> 0 mysql> SELECT ISNULL(1/0); -> 1 ~~~ 9、LEAST(value1,value2,...) 返回最小值，如果其中有值为null，则返回null ~~~ mysql> SELECT LEAST(2,0); -> 0 mysql> SELECT LEAST(34.0,3.0,5.0,767.0); -> 3.0 mysql> SELECT LEAST('B','A','C'); -> 'A' ~~~