Mysql比较运算符实战

深藏阁楼爱情的钟 2021-07-24 23:36 468阅读 0赞

**一 使用'='进行相等判断**

mysql> SELECT 1=0, '2'=2, 2=2,'0.02'=0, 'b'='b', (1+3) = (2+2),NULL=NULL;
    +-----+-------+-----+----------+---------+---------------+-----------+
    | 1=0 | '2'=2 | 2=2 | '0.02'=0 | 'b'='b' | (1+3) = (2+2) | NULL=NULL |
    +-----+-------+-----+----------+---------+---------------+-----------+
    |   0 |     1 |   1 |        0 |       1 |             1 |      NULL |
    +-----+-------+-----+----------+---------+---------------+-----------+
    1 row in set (0.02 sec)

**二 使用'<=>'进行相等的判断**

mysql> SELECT 1<=>0, '2'<=>2, 2<=>2,'0.02'<=>0, 'b'<=>'b', (1+3) <=> (2+1),NULL<=>NULL;
    +-------+---------+-------+------------+-----------+-----------------+-------------+
    | 1<=>0 | '2'<=>2 | 2<=>2 | '0.02'<=>0 | 'b'<=>'b' | (1+3) <=> (2+1) | NULL<=>NULL |
    +-------+---------+-------+------------+-----------+-----------------+-------------+
    |     0 |       1 |     1 |          0 |         1 |               0 |           1 |
    +-------+---------+-------+------------+-----------+-----------------+-------------+
    1 row in set (0.00 sec)

由结果可以看到，'<=>'在执行比较操作时和'='的作用是相似的，唯一的区别是'<=>'可以用来对NULL进行判断，两者都为NULL时返回值为1。

**三 使用'<>'和'!='进行不相等的判断**

不等于运算符 <>或者 !=

'<>'或者'!='用于判断数字、字符串、表达式不相等的判断。如果不相等，返回值为1；否则返回值为0。这两个运算符不能用于判断空值NULL。

mysql> SELECT 'good'<>'god', 1<>2, 4!=4, 5.5!=5, (1+3)!=(2+1),NULL<>NULL;
    +---------------+------+------+--------+--------------+------------+
    | 'good'<>'god' | 1<>2 | 4!=4 | 5.5!=5 | (1+3)!=(2+1) | NULL<>NULL |
    +---------------+------+------+--------+--------------+------------+
    |             1 |    1 |    0 |      1 |            1 |       NULL |
    +---------------+------+------+--------+--------------+------------+
    1 row in set (0.00 sec)

由结果可以看到，两个不等于运算符作用相同，都可以进行数字、字符串、表达式的比较判断。

**四 使用'<='进行比较判断**

mysql> SELECT 'good'<='god', 1<=2, 4<=4, 5.5<=5, (1+3) <= (2+1),NULL<=NULL;
    +---------------+------+------+--------+----------------+------------+
    | 'good'<='god' | 1<=2 | 4<=4 | 5.5<=5 | (1+3) <= (2+1) | NULL<=NULL |
    +---------------+------+------+--------+----------------+------------+
    |             0 |    1 |    1 |      0 |              0 |       NULL |
    +---------------+------+------+--------+----------------+------------+
    1 row in set (0.00 sec)

由结果可以看到，左边操作数小于或者等于右边时，返回值为1，例如：4<=4；当左边操作数大于右边时，返回值为0，例如：'good'第3个位置的'o'字符在字母表中的顺序大于'god'中的第3个位置的'd'字符，因此返回值为0；同样比较NULL值时返回NULL。

**五 使用'<'进行比较判断**

mysql> SELECT 'good'<'god', 1<2, 4<4, 5.5<5, (1+3) < (2+1),NULL<NULL;
    +--------------+-----+-----+-------+---------------+-----------+
    | 'good'<'god' | 1<2 | 4<4 | 5.5<5 | (1+3) < (2+1) | NULL<NULL |
    +--------------+-----+-----+-------+---------------+-----------+
    |            0 |   1 |   0 |     0 |             0 |      NULL |
    +--------------+-----+-----+-------+---------------+-----------+
    1 row in set (0.00 sec)

**六 使用'>='进行比较判断**

mysql> SELECT 'good'>='god', 1>=2, 4>=4, 5.5>=5, (1+3) >= (2+1),NULL>=NULL;
    +---------------+------+------+--------+----------------+------------+
    | 'good'>='god' | 1>=2 | 4>=4 | 5.5>=5 | (1+3) >= (2+1) | NULL>=NULL |
    +---------------+------+------+--------+----------------+------------+
    |             1 |    0 |    1 |      1 |              1 |       NULL |
    +---------------+------+------+--------+----------------+------------+
    1 row in set (0.00 sec)

**七 使用'>'进行比较判断**

mysql> SELECT 'good'>'god', 1>2, 4>4, 5.5>5, (1+3) > (2+1),NULL>NULL;
    +--------------+-----+-----+-------+---------------+-----------+
    | 'good'>'god' | 1>2 | 4>4 | 5.5>5 | (1+3) > (2+1) | NULL>NULL |
    +--------------+-----+-----+-------+---------------+-----------+
    |            1 |   0 |   0 |     1 |             1 |      NULL |
    +--------------+-----+-----+-------+---------------+-----------+
    1 row in set (0.00 sec)

**八 使用IS NULL、ISNULL和IS NOT NULL判断NULL值和非NULL值**

mysql> SELECT NULL IS NULL, ISNULL(NULL),ISNULL(10), 10 IS NOT NULL;
    +--------------+--------------+------------+----------------+
    | NULL IS NULL | ISNULL(NULL) | ISNULL(10) | 10 IS NOT NULL |
    +--------------+--------------+------------+----------------+
    |            1 |            1 |          0 |              1 |
    +--------------+--------------+------------+----------------+
    1 row in set (0.00 sec)

**九 使用BETWEEN AND 进行值区间判断**

mysql> SELECT 4 BETWEEN 4 AND 6, 4 BETWEEN 4 AND 6,12 BETWEEN 9 AND 10;
    +-------------------+-------------------+---------------------+
    | 4 BETWEEN 4 AND 6 | 4 BETWEEN 4 AND 6 | 12 BETWEEN 9 AND 10 |
    +-------------------+-------------------+---------------------+
    |                 1 |                 1 |                   0 |
    +-------------------+-------------------+---------------------+
    1 row in set (0.01 sec)
    
    mysql> SELECT  'x' BETWEEN 'f' AND 'g', 'b' BETWEEN 'a' AND 'c';
    +-------------------------+-------------------------+
    | 'x' BETWEEN 'f' AND 'g' | 'b' BETWEEN 'a' AND 'c' |
    +-------------------------+-------------------------+
    |                       0 |                       1 |
    +-------------------------+-------------------------+
    1 row in set (0.00 sec)

**十 使用LEAST运算符进行大小判断**

**十一 使用GREATEST运算符进行大小判断**

mysql> SELECT greatest(2,0), greatest(20.0,3.0,100.5), greatest('a','c','b'),greatest(10,NULL);
    +---------------+--------------------------+-----------------------+-------------------+
    | greatest(2,0) | greatest(20.0,3.0,100.5) | greatest('a','c','b') | greatest(10,NULL) |
    +---------------+--------------------------+-----------------------+-------------------+
    |             2 |                    100.5 | c                     |              NULL |
    +---------------+--------------------------+-----------------------+-------------------+
    1 row in set (0.00 sec)

**十二 使用IN、NOT IN运算符进行判断**

mysql> SELECT 2 IN (1,3,5,'thks'), 'thks' IN (1,3,5,'thks');
    +---------------------+--------------------------+
    | 2 IN (1,3,5,'thks') | 'thks' IN (1,3,5,'thks') |
    +---------------------+--------------------------+
    |                   0 |                        1 |
    +---------------------+--------------------------+
    1 row in set, 2 warnings (0.00 sec)

**十三 存在NULL值时的IN查询**

mysql> SELECT NULL IN (1,3,5,'thks'),10 IN (1,3,NULL,'thks');
    +------------------------+-------------------------+
    | NULL IN (1,3,5,'thks') | 10 IN (1,3,NULL,'thks') |
    +------------------------+-------------------------+
    |                   NULL |                    NULL |
    +------------------------+-------------------------+
    1 row in set, 1 warning (0.00 sec)

**十四 使用运算符LIKE进行字符串匹配运算**

mysql> SELECT 'stud' LIKE 'stud', 'stud' LIKE 'stu_','stud' LIKE '%d','stud' LIKE 't_ _ _', 's' LIKE NULL;
    +--------------------+--------------------+------------------+----------------------+---------------+
    | 'stud' LIKE 'stud' | 'stud' LIKE 'stu_' | 'stud' LIKE '%d' | 'stud' LIKE 't_ _ _' | 's' LIKE NULL |
    +--------------------+--------------------+------------------+----------------------+---------------+
    |                  1 |                  1 |                1 |                    0 |          NULL |
    +--------------------+--------------------+------------------+----------------------+---------------+
    1 row in set (0.00 sec)

**十五 使用运算符REGEXP进行字符串匹配运算**

mysql> SELECT 'ssky' REGEXP '^s', 'ssky' REGEXP 'y$', 'ssky' REGEXP '.sky', 'ssky' REGEXP '[ab]';
    +--------------------+--------------------+----------------------+----------------------+
    | 'ssky' REGEXP '^s' | 'ssky' REGEXP 'y$' | 'ssky' REGEXP '.sky' | 'ssky' REGEXP '[ab]' |
    +--------------------+--------------------+----------------------+----------------------+
    |                  1 |                  1 |                    1 |                    0 |
    +--------------------+--------------------+----------------------+----------------------+
    1 row in set (0.00 sec)