mysql运算符大全及实例讲解

mysql运算符如下表：

运算符实例：

(1) =运算符：

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

(a, b) = (x, y)等价于(a = x) AND (b = y)

(2)<=>运算符

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

(a, b) <=> (x, y)等价于(a <=> x) AND (b <=> y)）

(3)<>, !=不等于

mysql> SELECT \'.01\' <> \'0.01\';
        -> 1
mysql> SELECT .01 <> \'0.01\';
        -> 0
mysql> SELECT \'zapp\' <> \'zappp\';
        -> 1

(a, b) <> (x, y)等价于(a <> x) or (b <> y)）

(4)<=小于或等于

mysql> SELECT 0.1 <= 2;
        -> 1

(a, b) <= (x, y)等价于(a < x) OR ((a = x) AND (b <= y))

(5)<小于运算符

mysql> SELECT 2 < 2;
        -> 0

(a, b) < (x, y)等价于(a < x) OR ((a = x) AND (b < y))

(6)>=大于或等于运算符

mysql> SELECT 2 >= 2;
        -> 1

(a, b) >= (x, y)等价于(a > x) OR ((a = x) AND (b >= y))

(7)is和is not运算符

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

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

(8)is null 和is not null

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

SELECT * FROM tbl_name WHERE auto_col IS NULL
/*  http://www.manongjc.com/article/1435.html */
mysql> SELECT 1 IS NOT NULL, 0 IS NOT NULL, NULL IS NOT NULL;
        -> 1, 1, 0

(9)BETWEEN AND和 NOT BETWEEN AND

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

(10)COALESCE()

mysql> SELECT COALESCE(NULL,1);
        -> 1
mysql> SELECT COALESCE(NULL,NULL,NULL);
        -> NULL

(11)GREATEST()

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\'

(12)in与not in 

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

(12)ISNULL()

mysql> SELECT ISNULL(1+1);
        -> 0
mysql> SELECT ISNULL(1/0);
        -> 1

(13)INTERVAL()

mysql> SELECT INTERVAL(23, 1, 15, 17, 30, 44, 200);
        -> 3
mysql> SELECT INTERVAL(10, 1, 10, 100, 1000);
        -> 2
mysql> SELECT INTERVAL(22, 23, 30, 44, 200);
        -> 0

(14)LEAST()

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\'