MySQL比较运算符详解
比较运算符用于比较运算,包括大于(>)、小于(<)、等于(=)、大于或等于(>=)、小于或等于(<=)、不等于(!=),以及IN、BETWEEN AND、IS NULL、GREATEST、LEAST、LIKE、REGEXP等。
一个比较运算符的结果总是 1、0 或者是 NULL。比较运算符经常在 SELECT 的查询条件子句中使用,用来查询满足指定条件的记录。
下表罗列了 MySQL 中的比较运算符。
下面分别讨论不同比较运算符的使用方法。
【实例】使用“=”进行相等判断,SQL 语句如下:
数值比较时有如下规则:
【实例】使用“<=>”进行相等的判断,SQL 语句如下:
【实例】使用“>”和“!=”进行不相等的判断,SQL 语句如下:
【实例】使用“<=”进行比较判断,SQL 语句如下:
【实例】使用“<”进行比较判断,SQL 语句如下:
【实例】使用“>=”进行比较判断,SQL 语句如下:
【实例】使用“>”进行比较判断,SQL 语句如下:
【实例】使用 IS NULL、ISNULL 和 IS NOT NULL 判断 NULL 值和非 NULL 值,SQL 语句如下:
【实例】使用 BETWEEN…AND… 进行值区间判断,输入 SQL 语句如下:
【实例】使用 LEAST 运算符进行大小判断,SQL 语句如下:
【实例】使用 GREATEST 运算符进行大小判断,SQL 语句如下:
NOT IN 运算符用来判断表达式是否为 IN 列表中的其中一个值:如果不是,返回值为 1,否则返回值为 0。
【实例】使用 IN、NOT IN 运算符进行判断,SQL 语句如下:
在左侧表达式为 NULL 的情况下,或是表中找不到匹配项并且表中一个表达式为 NULL 的情况下,IN的返回值均为 NULL。
【实例】存在 NULL 值时的 IN 查询,SQL 语句如下:
LIKE 运算符在进行匹配时,可以使用下面的两种通配符:
【实例】使用运算符 LIKE 进行字符串匹配运算,SQL 语句如下:
REGEXP运算符在进行匹配时,常用的有下面几种通配符:
【实例】使用运算符 REGEXP 进行字符串匹配运算,SQL 语句如下:
正则表达式是一个可以进行复杂查询的强大工具。相对于 LIKE 字符串匹配,它可以使用更多的通配符类型,查询结果更加灵活。读者可以参考相关的书籍或资料,深入学习正则表达式的写法,在这里就不详细介绍了。
一个比较运算符的结果总是 1、0 或者是 NULL。比较运算符经常在 SELECT 的查询条件子句中使用,用来查询满足指定条件的记录。
下表罗列了 MySQL 中的比较运算符。
运算符 | 作用 |
---|---|
= | 等于 |
<=> | 安全等于 |
<>、!= | 不等于 |
<= | 小于或等于 |
>= | 大于或等于 |
> | 大于 |
IS NULL | 判断一个值是否为 NULL |
IS NOT NULL | 判断一个值是否不为 NULL |
LEAST | 在有两个或多个参数时,返回最小值 |
GREATEST | 当有两个或多个参数时,返回最大值 |
BETWEEN AND | 判断一个值是否落在两个值之间 |
ISNULL | 与 IS NULL 作用相同 |
IN | 判断一个值是 IN 列表中的任意一个值 |
NOT IN | 判断一个值不是 IN 列表中的任意一个值 |
LIKE | 通配符匹配 |
REGEXP | 正则表达式匹配 |
1、等于运算符(=)
等号(=)用来判断数字、字符串和表达式是否相等:如果相等,返回值为 1,否则返回值为 0。【实例】使用“=”进行相等判断,SQL 语句如下:
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 | +-----+-----+-----+----------+---------+-----------------+-------------+由结果可以看到,在进行判断时,2=2 和 '2'=2 的返回值相同,都为 1。因为在进行判断时,MySQL 自动进行了转换,把字符 '2' 转换成了数字 2;'b'='b' 为相同的字符比较,所以返回值为 1;表达式 1+3 和表达式 2+2 的结果都为 4,因此结果相等,返回值为 1;由于“=”不能用于空值 NULL 的判断,所以返回值为 NULL。
数值比较时有如下规则:
- 若有一个或两个参数为 NULL,则比较运算的结果为 NULL。
- 若同一个比较运算中的两个参数都是字符串,则按照字符串进行比较。
- 若两个参数均为整数,则按照整数进行比较。
- 若用字符串和数字进行相等判断,则 MySQL 可以自动将字符串转换为数字。
2、安全等于运算符(<=>)
这个操作符和 = 操作符执行相同的比较操作,不过 <=> 可以用来判断 NULL 值。在两个操作数均为 NULL 时,其返回值为 1,而不为 NULL;当一个操作数为 NULL 时,其返回值为 0,而不为 NULL。【实例】使用“<=>”进行相等的判断,SQL 语句如下:
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 | +-----+-------+-----+----------+---------+---------------+------------+由结果可以看到,“<=>”在执行比较操作时和“=”的作用相似,唯一的区别是“<=>”可以用来对 NULL 进行判断,两者都为 NULL 时返回值为 1。
3、不等于运算符(<>或者 !=)
“<>”或者“!=”用于判断数字、字符串、表达式是否相等:如果不相等,返回值为 1,否则返回值为 0。这两个运算符不能用于判断空值 NULL。【实例】使用“>”和“!=”进行不相等的判断,SQL 语句如下:
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 | +---------------+------+------+--------+--------------+------------+由结果可以看到,上面两个不等于运算符的作用相同,都可以进行数字、字符串、表达式的比较判断。
4、小于或等于运算符(<=)
“<=”用来判断左边的操作数是否小于或等于右边的操作数:如果小于或等于,返回值为 1,否则返回值为 0。“<=”不能用于判断空值 NULL。【实例】使用“<=”进行比较判断,SQL 语句如下:
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,例如 4<=4;当左边操作数大于右边时,返回值为 0,例如 'good'<='god'('good' 第 3 个位置的“o”字符在字母表中的顺序大于'god' 中第 3 个位置的“d”字符,因此返回值为 0);比较两个 NULL 值时将返回 NULL。
5、小于运算符(<)
“<”运算符用来判断左边的操作数是否小于右边的操作数:如果是,则返回值为 1,否则返回值为 0。“<”不能用于判断空值 NULL。【实例】使用“<”进行比较判断,SQL 语句如下:
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,例如 1<2;当左边操作数大于右边时,返回值为0,例如 'good'<'god'('good' 第 3 个位置的“o”字符在字母表中的顺序大于 'god' 中第 3 个位置的“d”字符,因此返回值为 0);比较两个 NULL 值时将返回 NULL。
6、大于或等于运算符(>=)
“>=”运算符用来判断左边的操作数是否大于或等于右边的操作数:如果是,则返回值为 1;否则返回值为 0。“>=”不能用于判断空值 NULL。【实例】使用“>=”进行比较判断,SQL 语句如下:
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,例如 4>=4;当左边操作数小于右边时,返回值为 0,例如 1>=2;比较两个 NULL 值时将返回 NULL。
7、大于运算符(>)
“>”运算符用来判断左边的操作数是否大于右边的操作数:如果是,则返回值为 1;否则返回值为 0。“>”不能用于判断空值 NULL。【实例】使用“>”进行比较判断,SQL 语句如下:
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,例如 5.5>5;左边操作数小于右边时,返回 0,例如 1>2;比较两个 NULL 值时将返回 NULL。
8、IS NULL(ISNULL)和IS NOT NULL运算符
IS NULL 和 ISNULL 检验一个值是否为 NULL:如果为 NULL,返回值为 1,否则返回值为 0。IS NOT NULL 检验一个值是否为非 NULL:如果是非 NULL,返回值为 1,否则返回值为 0。【实例】使用 IS NULL、ISNULL 和 IS NOT NULL 判断 NULL 值和非 NULL 值,SQL 语句如下:
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 | +--------------+--------------+------------+----------------+由结果可以看到,IS NULL 和 ISNULL 的作用相同,只是格式不同。ISNULL 和 IS NOT NULL 的返回值正好相反。
9、BETWEEN…AND…运算符
语法格式为:expr BETWEEN min AND max假如 expr 大于等于 min 且小于等于 max,则 BETWEEN 的返回值为 1,否则返回值为 0。
【实例】使用 BETWEEN…AND… 进行值区间判断,输入 SQL 语句如下:
mysql> SELECT 4 BETWEEN 2 AND 5, 4 BETWEEN 4 AND 6,12 BETWEEN 9 AND 10; +-------------------+-------------------+---------------------+ | 4 BETWEEN 2 AND 5 | 4 BETWEEN 4 AND 6 | 12 BETWEEN 9 AND 10 | +-------------------+-------------------+---------------------+ | 1 | 1 | 0 | +-------------------+-------------------+---------------------+ mysql> SELECT 'x' BETWEEN 'f' AND 'g', 'b' BETWEEN 'a' AND 'c'; +-------------------------+-------------------------+ | 'x' BETWEEN 'f' AND 'g' | 'b' BETWEEN 'a' AND 'c' | +-------------------------+-------------------------+ | 0 | 1 | +-------------------------+-------------------------+由结果可以看到,4 在端点值区间内或者等于其中一个端点值时,BETWEEN…AND… 表达式返回值为 1;12 并不在指定区间内,因此返回值为 0;对于字符串类型的比较,按字母表中字母顺序进行比较,“x”不在指定的字母区间内,因此返回值为 0,而“b”位于指定字母区间内,因此返回值为 1。
10、LEAST运算符
语法格式为:LEAST(值1,值2,...,值n)其中,“值n”表示参数列表中有 n 个值。在有两个或多个参数的情况下,返回最小值。假如任意一个自变量为 NULL,则 LEAST() 的返回值为 NULL。
【实例】使用 LEAST 运算符进行大小判断,SQL 语句如下:
mysql> SELECT least(2,0), least(20.0,3.0,100.5), least('a','c','b'),least(10,NULL); +----------+---------------------+------------------+--------------+ |least(2,0)|least(20.0,3.0,100.5)|least('a','c','b')|least(10,NULL)| +----------+---------------------+------------------+--------------+ | 0 | 3.0 | a | NULL | +----------+---------------------+------------------+--------------+由结果可以看到,当参数是整数或者浮点数时,LEAST 将返回其中最小的值;当参数为字符串时,返回字母表中顺序最靠前的字符;当比较值列表中有 NULL 时,不能判断大小,返回值为 NULL。
11、GREATEST (value1,value2,...)
语法格式为:GREATEST(值1, 值2,...,值n)其中,n 表示参数列表中有 n 个值。当有两个或多个参数时,返回值为最大值。假如任意一个自变量为 NULL,则 GREATEST() 的返回值为 NULL。
【实例】使用 GREATEST 运算符进行大小判断,SQL 语句如下:
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 | +-------------+------------------------+---------------------+------------------+由结果可以看到,当参数中是整数或者浮点数时,GREATEST 将返回其中最大的值;当参数为字符串时,返回字母表中顺序最靠后的字符;当比较值列表中有 NULL 时,不能判断大小,返回值为 NULL。
12、IN、NOT IN运算符
IN 运算符用来判断操作数是否为 IN 列表中的其中一个值:如果是,返回值为 1,否则返回值为 0。NOT IN 运算符用来判断表达式是否为 IN 列表中的其中一个值:如果不是,返回值为 1,否则返回值为 0。
【实例】使用 IN、NOT IN 运算符进行判断,SQL 语句如下:
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 | +---------------------+--------------------------+ mysql> SELECT 2 NOT IN (1,3,5,'thks'), 'thks' NOT IN (1,3,5,'thks'); +-------------------------+------------------------------+ | 2 NOT IN (1,3,5,'thks') | 'thks' NOT IN (1,3,5,'thks') | +-------------------------+------------------------------+ | 1 | 0 | +-------------------------+------------------------------+由结果可以看到,IN 和 NOT IN 的返回值正好相反。
在左侧表达式为 NULL 的情况下,或是表中找不到匹配项并且表中一个表达式为 NULL 的情况下,IN的返回值均为 NULL。
【实例】存在 NULL 值时的 IN 查询,SQL 语句如下:
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 | +------------------------+-------------------------+IN() 语法也可用于在 SELECT 语句中进行嵌套子查询,在后面的章节中将会讲到。
13、LIKE
LIKE 运算符用来匹配字符串,语法格式为:expr LIKE 匹配条件如果 expr 满足匹配条件,则返回值为 1(True);如果不匹配,则返回值为 0(False)。expr 或匹配条件中任何一个为 NULL,则结果为 NULL。
LIKE 运算符在进行匹配时,可以使用下面的两种通配符:
- “%”:匹配任何数目的字符,甚至包括零字符。
- “_”:只能匹配一个字符。
【实例】使用运算符 LIKE 进行字符串匹配运算,SQL 语句如下:
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 | +------------------+------------------+---------------+--------------------+--------------+由结果可以看到,指定匹配字符串为“stud”。“stud”表示直接匹配“stud”字符串,满足匹配条件,返回 1;“stu_”表示匹配以 stu 开头的长度为 4 个字符的字符串,“stud”正好是 4 个字符,满足匹配条件,因此匹配成功,返回 1;“%d”表示匹配以字母“d”结尾的字符串,“stud”满足匹配条件,匹配成功,返回 1;“t _ _ _”表示匹配以“t”开头的长度为 4 个字符的字符串,“stud”不满足匹配条件,因此返回 0;当字符“s”与 NULL 匹配时,结果为 NULL。
14、REGEXP
REGEXP 运算符用来匹配字符串,语法格式为:expr REGEXP 匹配条件如果 expr 满足匹配条件,则返回 1;如果不满足,则返回 0。若 expr 或匹配条件任意一个为 NULL,则结果为 NULL。
REGEXP运算符在进行匹配时,常用的有下面几种通配符:
- “^”:匹配以该字符后面的字符开头的字符串。
- “$”:匹配以该字符后面的字符结尾的字符串。
- “.”:匹配任何一个单字符。
- “[...]”:匹配在方括号内的任何字符。例如:“[abc]”匹配“a”“b”或“c”。为了命名字符的范围,使用一个“-”。“[a-z]”匹配任何字母,而“[0-9]”匹配任何数字。
- “*”:匹配零个或多个在它前面的字符。例如:“x*”匹配任何数量的“x”字符,“[0-9]*”匹配任何数量的数字,而“*”匹配任何数量的任何字符。
【实例】使用运算符 REGEXP 进行字符串匹配运算,SQL 语句如下:
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 | +------------------+------------------+--------------------+--------------------+由结果可以看到,指定匹配字符串为“ssky”:
- “^s”表示匹配任何以字母“s”开头的字符串,因此满足匹配条件,返回 1;
- “y$”表示任何以字母“y”结尾的字符串,因此满足匹配条件,返回 1;
- “.sky”匹配任何以“sky”结尾、字符长度为 4 的字符串,满足匹配条件,返回 1;
- “[ab]”匹配任何包含字母“a”或者“b”的字符串,指定字符串中既没有字母“a”也没有字母“b”,因此不满足匹配条件,返回 0。
正则表达式是一个可以进行复杂查询的强大工具。相对于 LIKE 字符串匹配,它可以使用更多的通配符类型,查询结果更加灵活。读者可以参考相关的书籍或资料,深入学习正则表达式的写法,在这里就不详细介绍了。