Mysql比较运算符详解

骑猪看日落 2022-07-15 00:02 180阅读 0赞

在mysql中，比较运算符是我们最常用的运算符，今天和大家分享的就是这部分内容，一起来看看吧。

** **

**比较运算符形式**

![Center][]

**示例**

比较运算符的结果不是为真就是假。

比较运算符“1=1”

mysql> SELECT 1=1;

\+-----+

| 1=1 |

\+-----+

|   1 |

\+-----+

1 row in set (0.00 sec)

比较运算符1=1,1='1'

mysql> SELECT 1=1,1='1';

\+-----+-------+

| 1=1 | 1='1' |

\+-----+-------+

|   1 |     1 |

\+-----+-------+

1 row in set (0.00 sec)

比较运算符1=1,1='1',1=2

mysql> SELECT 1=1,1='1',1=2;

\+-----+-------+-----+

| 1=1 | 1='1' | 1=2 |

\+-----+-------+-----+

|   1 |     1 |   0 |

\+-----+-------+-----+

1 row in set (0.00 sec)

查看学员表查询用户名，查看是否为真（=）

mysql> SELECT username,username='king' FROM student;

\+----------+-----------------+

| username | username='king' |

\+----------+-----------------+

| king     |               1 |

| king1    |               0 |

| king2    |               0 |

| king3    |               0 |

| king4    |               0 |

| king5    |               0 |

| king6    |               0 |

| king7    |               0 |

| king8    |               0 |

\+----------+-----------------+

9 rows in set (0.00 sec)

查看学员表查询用户名，查看是否为真（!=）

mysql> SELECT username,username!='king' FROM student;

\+----------+------------------+

| username | username!='king' |

\+----------+------------------+

| king     |                0 |

| king1    |                1 |

| king2    |                1 |

| king3    |                1 |

| king4    |                1 |

| king5    |                1 |

| king6    |                1 |

| king7    |                1 |

| king8    |                1 |

\+----------+------------------+

9 rows in set (0.00 sec)

查看用户表的记录

mysql> SELECT \* FROM cms\_user;

\+----+-----------+--------------+-------------+------------+----------+-------+------+--------+

\+----+-----------+--------------+-------------+------------+----------+-------+------+--------+

|  1 | 张三 | zhangsan| user@qq.com | 1419811708 | user.jpg |     1 |   21 | 男     |

|  2 | 张三丰 | zhangsanfeng | user@qq.com | 1419812708 | user.jpg |  2 |31 | 女     |

|  3 | 章子怡 | zhangsan| user@qq.com | 1419813708 | user.jpg | 3 |   43 | 男     |

|  4 | long      | long | user@qq.com | 1419814708 | user.jpg |     4 |   41 | 女     |

|  5 | ring      | ring  | user@qq.com | 1419815708 | user.jpg |     2 |    9 | 男     |

|  6 | queen | queen | user@qq.com | 1419861708 | user.jpg |     3 |   77 |  女     |

|  8 | blek      | blek  | user@qq.com | 1419818708 | user.jpg |     1 |   85 | 女     |

|  9 | rose      | rose | user@qq.com | 1419821708 | user.jpg |     2 |    9 | 男     |

| 10 | lily      | lily  | user@qq.com | 1419831708 | user.jpg |     2 |   39 | 女     |

| 11 | john      | john | user@qq.com | 1419841708 | user.jpg |     2 |   72 | 保密   |

| 12 | test1     | test1 | user@qq.com | 1419811708 | user.jpg |     1 | NULL | 保密   |

| 13 | TEST2     | TEST2 | user@qq.com | 1381203974 | user.jpg |    20 |   18 | NULL   |

| 14 | lll       | lll    | user@qq.com |  138212349 | user.jpg |     2 |   18 | NULL   |

| 15 | ttt       | lll  | user@qq.com |  138212349 | user.jpg |     2 |   18 | NULL   |

| 16 | ooo       | lll   | user@qq.com |  138212349 | user.jpg |     2 |   18 | NULL   |

\+----+-----------+--------------+-------------+------------+----------+-------+------+--------+

15 rows in set (0.00 sec)

查看用户表的id,username,age,sex,age=null的字段

mysql> SELECT id,username,age,sex,age=null FROM cms\_user;

\+----+-----------+------+--------+----------+

| id | username  | age  | sex    | age=null |

\+----+-----------+------+--------+----------+

|  1 | 张三      |   21 | 男     |     NULL |

|  2 | 张三丰    |   31 | 女     |     NULL |

|  3 | 章子怡    |   43 | 男     |     NULL |

|  4 | long      |   41 | 女     |     NULL |

|  5 | ring      |    9 | 男     |     NULL |

|  6 | queen     |   77 | 女     |     NULL |

|  8 | blek      |   85 | 女     |     NULL |

|  9 | rose      |    9 | 男     |     NULL |

| 10 | lily      |   39 | 女     |     NULL |

| 11 | john      |   72 | 保密   |     NULL |

| 14 | lll       |   18 | NULL   |     NULL |

| 15 | ttt       |   18 | NULL   |     NULL |

| 16 | ooo       |   18 | NULL   |     NULL |

\+----+-----------+------+--------+----------+

15 rows in set (0.00 sec)

查看用户表的id,username,age,sex,age<=>null的字段

mysql> SELECT id,username,age,sex,age<=>null FROM cms\_user;

\+----+-----------+------+--------+------------+

| id | username  | age  | sex    | age<=>null |

\+----+-----------+------+--------+------------+

|  1 | 张三      |   21 | 男     |          0 |

|  2 | 张三丰    |   31 | 女     |          0 |

|  3 | 章子怡    |   43 | 男     |          0 |

|  4 | long      |   41 | 女     |          0 |

|  5 | ring      |    9 | 男     |          0 |

|  6 | queen     |   77 | 女     |          0 |

|  8 | blek      |   85 | 女     |          0 |

|  9 | rose      |    9 | 男     |          0 |

| 10 | lily      |   39 | 女     |          0 |

| 11 | john      |   72 | 保密   |          0 |

| 12 | test1     | NULL | 保密   |          1 |

| 13 | TEST2     |   18 | NULL   |          0 |

| 14 | lll       |   18 | NULL   |          0 |

| 15 | ttt       |   18 | NULL   |          0 |

| 16 | ooo       |   18 | NULL   |          0 |

\+----+-----------+------+--------+------------+

15 rows in set (0.03 sec)

查看用户表的id,username,age,sex, ,sex<=>NULL的字段

mysql> SELECT id,username,age,sex,sex<=>NULL FROM cms\_user;

\+----+-----------+------+--------+------------+

| id | username  | age  | sex    | sex<=>NULL |

\+----+-----------+------+--------+------------+

|  1 | 张三      |   21 | 男     |          0 |

|  2 | 张三丰    |   31 | 女     |          0 |

|  3 | 章子怡    |   43 | 男     |          0 |

|  4 | long      |   41 | 女     |          0 |

|  5 | ring      |    9 | 男     |          0 |

|  6 | queen     |   77 | 女     |          0 |

|  8 | blek      |   85 | 女     |          0 |

|  9 | rose      |    9 | 男     |          0 |

| 10 | lily      |   39 | 女     |          0 |

| 11 | john      |   72 | 保密   |          0 |

| 12 | test1     | NULL | 保密   |          0 |

| 13 | TEST2     |   18 | NULL   |          1 |

| 14 | lll       |   18 | NULL   |          1 |

| 15 | ttt       |   18 | NULL   |          1 |

| 16 | ooo       |   18 | NULL   |          1 |

\+----+-----------+------+--------+------------+

15 rows in set (0.00 sec)

查看用户表的id,username, score,score>=70的字段（取得奖学金的学员）

mysql> SELECT id,username,score,score>=70 FROM student;

\+----+----------+-------+-----------+

\+----+----------+-------+-----------+

|  1 | king     |    95 |         1 |

|  2 | king1    |    35 |         0 |

|  3 | king2    |    45 |         0 |

|  4 | king3    |    55 |         0 |

|  5 | king4    |    65 |         0 |

|  6 | king5    |    75 |         1 |

|  7 | king6    |    80 |         1 |

|  8 | king7    |    90 |         1 |

|  9 | king8    |    25 |         0 |

\+----+----------+-------+-----------+

9 rows in set (0.00 sec)

查看用户表的id,username, ,age,age IS NULL的字段（检测是否为空）

mysql> SELECT id,username,age,age IS NULL FROM cms\_user;

\+----+-----------+------+-------------+

| id | username  | age  | age IS NULL |

\+----+-----------+------+-------------+

|  1 | 张三      |   21 |          0 |

|  2 | 张三丰    |   31 |           0 |

|  3 | 章子怡    |   43 |           0 |

|  4 | long      |   41 |           0 |

|  5 | ring      |    9 |           0 |

|  6 | queen     |   77 |           0 |

|  8 | blek      |   85 |           0 |

|  9 | rose      |    9 |           0 |

| 10 | lily      |   39 |           0 |

| 11 | john      |   72 |           0 |

| 12 | test1     | NULL |           1 |

| 13 | TEST2     |   18 |           0 |

| 14 | lll       |   18 |           0 |

| 15 | ttt       |   18 |           0 |

| 16 | ooo       |   18 |           0 |

\+----+-----------+------+-------------+

15 rows in set (0.00 sec)

查看用户表的id,username, ,age,age IS NULL的字段（检测是否为空）

mysql> SELECT id,username,age,age IS NOT NULL FROM cms\_user;

\+----+-----------+------+-----------------+

| id | username  | age  | age IS NOT NULL |

\+----+-----------+------+-----------------+

|  1 | 张三      |   21 |               1 |

|  2 | 张三丰    |   31 |               1 |

|  3 | 章子怡    |   43 |               1 |

|  4 | long      |   41 |               1 |

|  5 | ring      |    9 |               1 |

|  6 | queen     |   77 |               1 |

|  8 | blek      |   85 |               1 |

|  9 | rose      |    9 |               1 |

| 10 | lily      |   39 |               1 |

| 11 | john      |   72 |               1 |

| 12 | test1     | NULL |               0 |

| 13 | TEST2     |   18 |               1 |

| 14 | lll       |   18 |               1 |

| 15 | ttt       |   18 |               1 |

| 16 | ooo       |   18 |               1 |

\+----+-----------+------+-----------------+

15 rows in set (0.00 sec)

判断年龄在10到30是否在范围内

mysql> SELECT id,username,age,age BETWEEN 10 AND 30 FROM cms\_user;

\+----+-----------+------+-----------------------+

| id | username  | age  | age BETWEEN 10 AND 30 |

\+----+-----------+------+-----------------------+

|  1 | 张三      |   21 |                     1 |

|  2 | 张三丰    |   31 |                     0 |

|  3 | 章子怡    |   43 |                     0 |

|  4 | long      |   41 |                     0 |

|  5 | ring      |    9 |                     0 |

|  6 | queen     |   77 |                     0 |

|  8 | blek      |   85 |                     0 |

|  9 | rose      |    9 |                     0 |

| 10 | lily      |   39 |                     0 |

| 11 | john      |   72 |                     0 |

| 13 | TEST2     |   18 |                     1 |

| 14 | lll       |   18 |                     1 |

| 15 | ttt       |   18 |                     1 |

| 16 | ooo       |   18 |                     1 |

\+----+-----------+------+-----------------------+

15 rows in set (0.02 sec)

检测年龄是否有(21,31,41,51)内的人

mysql> SELECT id,username,age,age IN(21,31,41,51) FROM cms\_user;

\+----+-----------+------+---------------------+

| id | username  | age  | age IN(21,31,41,51) |

\+----+-----------+------+---------------------+

|  1 | 张三      |   21 |                   1 |

|  2 | 张三丰    |   31 |                   1 |

|  3 | 章子怡    |   43 |                   0 |

|  4 | long      |   41 |                   1 |

|  5 | ring      |    9 |                   0 |

|  6 | queen     |   77 |                   0 |

|  8 | blek      |   85 |                   0 |

|  9 | rose      |    9 |                   0 |

| 10 | lily      |   39 |                   0 |

| 11 | john      |   72 |                   0 |

| 13 | TEST2     |   18 |                   0 |

| 14 | lll       |   18 |                   0 |

| 15 | ttt       |   18 |                   0 |

| 16 | ooo       |   18 |                   0 |

\+----+-----------+------+---------------------+

15 rows in set (0.00 sec)

查看在1（1,2,3）内的

mysql> SELECT 1 IN (1,2,3);

\+--------------+

| 1 IN (1,2,3) |

\+--------------+

|            1 |

\+--------------+

1 row in set (0.00 sec)

查看在11（1,2,3）内的

mysql> SELECT 11 IN (1,2,3);

\+---------------+

| 11 IN (1,2,3) |

\+---------------+

|             0 |

\+---------------+

1 row in set (0.00 sec)

查询用户名s为1的（错的）

mysql> SELECT s LIKE '\_';

ERROR 1054 (42S22): Unknown column 's' in 'field list'

查询用户名s为1的（字符串）

mysql> SELECT 's' LIKE '\_';

\+--------------+

| 's' LIKE '\_' |

\+--------------+

|            1 |

\+--------------+

1 row in set (0.00 sec)

查询用户名sD为1的（字符串）

mysql> SELECT 'sD' LIKE '\_';

\+---------------+

| 'sD' LIKE '\_' |

\+---------------+

|             0 |

\+---------------+

1 row in set (0.00 sec)

查看用户表的id,username,username LIKE '\_\_\_\_'

mysql> SELECT id,username,username LIKE '\_\_\_\_' FROM cms\_user;

\+----+-----------+----------------------+

| id | username  | username LIKE '\_\_\_\_' |

\+----+-----------+----------------------+

|  8 | blek      |                    1 |

| 11 | john      |                    1 |

| 10 | lily      |                    1 |

| 14 | lll       |                    0 |

|  4 | long      |                    1 |

| 16 | ooo       |                    0 |

|  6 | queen     |                    0 |

|  5 | ring      |                    1 |

|  9 | rose      |                    1 |

| 12 | test1     |                    0 |

| 13 | TEST2     |                    0 |

| 15 | ttt       |                    0 |

|  1 | 张三      |                    0 |

|  2 | 张三丰    |                    0 |

|  3 | 章子怡    |                    0 |

\+----+-----------+----------------------+

15 rows in set (0.00 sec)

查看用户表的id,username,username REGEXP '^t'

mysql> SELECT id,username,username REGEXP '^t' FROM cms\_user;

\+----+-----------+----------------------+

| id | username  | username REGEXP '^t' |

\+----+-----------+----------------------+

|  8 | blek      |       、            0 |

| 11 | john      |                    0 |

| 10 | lily      |                    0 |

| 14 | lll       |                    0 |

|  4 | long      |                    0 |

| 16 | ooo       |                    0 |

|  6 | queen     |                    0 |

|  5 | ring      |                    0 |

|  9 | rose      |                    0 |

| 12 | test1     |                    1 |

| 13 | TEST2     |                    1 |

| 15 | ttt       |                    1 |

|  1 | 张三      |                    0 |

|  2 | 张三丰    |                    0 |

|  3 | 章子怡    |                    0 |

\+----+-----------+----------------------+

15 rows in set (0.00 sec)

原文链接：[http://www.maiziedu.com/wiki/mysql/conpare/][http_www.maiziedu.com_wiki_mysql_conpare]

[Center]: /images/20220715/66eb587cc186435fb00e22adced00911.png
[http_www.maiziedu.com_wiki_mysql_conpare]: http://www.maiziedu.com/wiki/mysql/conpare/

Mysql比较运算符详解

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 MySQL运算符详解

相关比较运算符(ComparisonOperator)

相关 bilibiliC++20运算符-比较运算符

相关 mongoDB 比较运算符

相关 Mysql比较运算符详解

相关 Javascript比较运算符详解

相关 mysql运算符，比较运算符

相关第四章 1 mysql中的运算符，算数运算符 mysql运算符，比较运算符 mysql运算符，逻辑运算符，布尔运算符

相关 java运算符之比较运算符

相关 Mysql比较运算符实战