![](https://img-blog.csdnimg.cn/db3bd83deaa1469abcce105518f1dafa.png)
目录
MySQL中的比较运算符
比较运算符是SQL查询中最常用的运算符,与关键字 WHERE
结合,用于查询满足某些特点条件的数据。比较运算符的运算结果有3种:如果为真则为 1 ,假为 0 ,其他情况均为 (NULL) 。SQL中的比较运算符主要分为两大类:比较符号和比较关键字。
1.符号类
运算符 | 名称 | 作用 |
---|---|---|
= | 等于运算符 | 判断两个值、字符串或表达式是否相等 |
<=> | 安全等于运算符 | 安全地判断两个值、字符串或表达式是否相等 |
<> != | 不等于运算符 | 判断两个值、字符串或表达式是否不相等 |
< | 小于运算符 | 判断前面的值、字符串或表达式是否小于后面的值、字符串或表达式 |
<= | 小于等于运算符 | 判断前面的值、字符串或表达式是否小于等于后面的值、字符串或表达式 |
> | 大于运算符 | 判断前面的值、字符串或表达式是否大于后面的值、字符串或表达式 |
>= | 大于等于运算符 | 判断前面的值、字符串或表达式是否大于等于后面的值、字符串或表达式 |
1.1 等于运算符
- 在SQL中,若整数与字符串作比较运算,字符串会自动转换为数值 (隐式转换) 。但是如果字符串中是字母,就转换失败,自动把字符串转换为 0 。如下代码所示:
SELECT 1 = 3, 1 != 3, 1 = '1', 1 = 'a', 0 = 'a'
FROM DUAL;
查询结果:
- 如果比较运算符的两边的字符串都是字母,则不会触发隐式转换机制。SQL将按字符的ASCII值来进行比较运算。如下代码所示:
SELECT 'a' = 'a', 'ab' = 'ab', 'a' = 'b'
FROM DUAL;
查询结果:
- 只要有 (NULL) 参与的运算,结果一定为 (NULL) 。
SELECT 1 = NULL, NULL = NULL
FROM DUAL;
查询结果:
1.2 安全等与运算符
安全等于运算符 <=>
是为了让空值 (NULL) 参与比较运算而生的。
上面提到, (NULL) 参与的任何运算都为 (NULL) 。如果有这样一个需求:希望能查询员工表 employees
中奖金比率 commission_pct
为 (NULL) 的员工。很多新手会编写下面这样错误的代码:
SELECT employee_id, last_name, commission_pct
FROM employees
WHERE commission_pct = NULL;
查询结果:
因为 (NULL) 参与的任何运算都为 (NULL) ,所以采用这样查询不会显示任何结果。为了解决 (NULL) 参与的任何运算都为 (NULL) 这个令人头疼的问题,安全等于运算符就应运而生了。
- <=> 安全等于运算符的作用与等于运算符一样,唯一的区别是,当有 (NULL) 参与运算时,其结果不会为 (NULL) 。因此,其可以用于判断字段是否为 (NULL) 。如下代码所示:
SELECT employee_id, last_name, commission_pct
FROM employees
WHERE commission_pct <=> NULL;
查询结果:
有了安全等于运算符 <=>
的帮助,现在我们就可以正确地查询员工表 employees
中奖金比率 commission_pct
为 (NULL) 的员工了。
【例子】
SELECT 1 <=> NULL, NULL <=> NULL
FROM DUAL;
查询结果:
2.关键字
关键字 | 名称 | 作用 |
---|---|---|
IS NULL | 为空运算符 | 判断值、字符串或表达式是否为空 |
IS NOT NULL | 不为空运算符 | 判断值、字符串或表达式是否不为空 |
LEAST | 最小值运算符 | 在多个值中返回最小值 |
GREATEST | 最大值运算符 | 在多个值中返回最大值 |
BETWEEN AND | 两值之间的运算符 | 判断一个值是否在两个值之间 |
ISNULL() | 为空运算函数 | 判断一个值、字符串或表达式是否为空 |
IN | 属于运算符 | 判断一个值是否为列表中的任意一个值 |
NOT IN | 不属于运算符 | 判断一个值是否不是一个列表中的任意一个值 |
LIKE | 模糊匹配运算符 | 判断一个值是否符合模糊匹配规则 |
REGEXP | 正则表达式运算符 | 判断一个值是否符合正则表达式的规则 |
RLIKE | 正则表达式运算符 | 判断一个值是否符合正则表达式的规则 |
2.1 IS NULL / IS NOT NULL / ISNULL的区别
IS NULL
和<=> NULL
的作用一样,都是查询字段为 (NULL) 的元组。
SELECT employee_id, last_name, commission_pct
FROM employees
WHERE commission_pct IS NULL;
查询结果:
IS NOT NULL
是IS NULL
的逆运算,是查询字段不为 (NULL) 的元组。
SELECT employee_id, last_name, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
查询结果:
ISNULL
和IS NULL
一个空格之差,它们的用法就是不一样的。但是它们的功能是一样的。如果想把ISNULL
作为关键字用来查询字段为 (NULL) 的元组。SQL就会报错,如下代码所示:
SELECT employee_id, last_name, commission_pct
FROM employees
WHERE commission_pct ISNULL;
查询结果:
【正确用法】ISNULL()
是一个函数,正确的用法是把要判断是否为空的字段名作为参数传入ISNULL()
函数中,如果传入的参数为 (NULL) ,就返回 1 。如下代码所示:
SELECT employee_id, last_name, commission_pct
FROM employees
WHERE ISNULL(commission_pct);
查询结果:
2.2 LEAST / GREATEST 的使用
LEAST()
和GREATEST()
也是函数。通过往括号中传入多个需要比较的参数来返回最小 (或最大) 值。如下代码所示:
SELECT LEAST('a', 'b', 'c'), GREATEST('a', 'b', 'z')
FROM DUAL;
查询结果:
可见是比较字符串的ASCII码值来返回函数的结果的。
2.3 BETWEEN…AND…
BETWEEN 条件1 AND 条件2
就不是函数了,就是关键字,直接使用即可。作用是查询字段在某个范围内的元组 (区间是左闭右闭,即包括边界值) 。- 【注意】
条件1
是上限,条件2
是下限。如果两者交换了,将查询不到任何结果。 - 例如,查询员工表
employees
中工资在 6000~8000 元的员工的employee_id
、last_name
和salary
。如下代码所示:
SELECT employee_id, last_name, salary
FROM employees
WHERE salary BETWEEN 6000 AND 8000;
查询结果:
从查询结果中可以看到是包含边界值的。即区间左闭右闭。
2.4 IN (set) / NOT IN(set)
- 上面的
BETWEEN…AND…
查询的是在连续值范围的元组。而IN (set) / NOT IN(set)
就是查询离散值的。 - 例如,查询员工表
employees
中部门IDdepartment_id
为 10,20,30 的员工的employee_id
、last_name
、salary
和department_id
信息。如下代码所示:
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE department_id IN(10, 20, 30);
查询结果:
- 相反,
NOT IN
就是IN
的逆运算。例如,查询员工表employees
中部门IDdepartment_id
不是 10,20,30 的员工的employee_id
、last_name
、salary
和department_id
信息。如下代码所示:
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE department_id NOT IN(10, 20, 30);
查询结果:
2.5 LIKE
LIKE
是一个模糊查询,需要配合通配符%
使用。直接看例子;- 【例子1】查询员工表
employees
中名字last_name
中含有字符串'a'
的员工的employee_id
、last_name
信息。如下代码所示:
SELECT employee_id, last_name
FROM employees
WHERE last_name LIKE('%a%');
查询结果:
第 3 行代码中的 ‘%a%’
的意思是,last_name
中在字符串 ‘a’
前后包含不确定个数的字符串。这个“不确定”是包括 0 个的,具体参考上图查询结果中的第3行数据 Austin
,其第一个字母就是 ‘a’ ,它前面包含0个字符串,但也符合条件。
修改以下需求:【例子2】查询员工表 employees
中名字 last_name
中首字母为字符串 'a'
的员工的 employee_id
、last_name
信息。如下代码所示:
SELECT employee_id, last_name
FROM employees
WHERE last_name LIKE('a%');
查询结果:
【例子3】查询员工表 employees
中名字 last_name
中包含字符串 'a'
且包含字符串 'e'
的员工的 employee_id
、last_name
信息。如下代码所示:
# 写法一
SELECT employee_id, last_name
FROM employees
WHERE last_name LIKE '%a%' AND last_name LIKE '%e%';
# 写法二
SELECT employee_id, last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';
写法二
中之所以要写两个,是因为前者是字母 ‘a’ 在前, ‘e’ 在后的情况;而后者是字母 ‘e’ 在前, ‘a’ 在后的情况。两种情况都要考虑。
查询结果:
【例子4】查询员工表 employees
中名字 last_name
中第2个字符串是 'a'
的员工的 employee_id
、last_name
信息。
- 这一题通配符
%
就搞不定了,需要配合一个新的符号下划线_
。一个下划线_
就代表一个不确定的字符。如下代码所示:
SELECT employee_id, last_name
FROM employees
WHERE last_name LIKE '_a%';
查询结果:
【例子5】我们来提出一个更加变态的需求。查询员工表 employees
中名字 last_name
中第2个字符串是下划线 _
且第3个字符是 'a'
的员工的 employee_id
、last_name
信息。
- 这一题需要使用转义字符
\
,转义字符\
后面紧跟的_
就表示纯粹的下划线_
的意思,而不是代表一个不确定的字符的意思。如下代码所示:
SELECT employee_id, last_name
FROM employees
WHERE last_name LIKE '_\_a%';
查询结果:
- 【补充】也可以用关键字
ESCAPE
自定义转义字符。如下代码所示:
SELECT employee_id, last_name
FROM employees
WHERE last_name LIKE '_$_a%' ESCAPE '$';
2.6 REGEXP / RLIKE: 正则表达式
- 正则表达式是一种独立于SQL之外的一种规则。其体系过于庞大,碍于篇幅不在此详细介绍。在此仅介绍简单的几种正则表达式作为了了解,感兴趣的朋友可以留言点赞,我会之后专门更新一期正则表达式的文章。
REGEXP
用来匹配字符串,语法格式为expr REGEXP 匹配条件
。如果expr
满足匹配条件,返回1;如果不满足,则返回0;若expr
或匹配条件任意一个为 NULL ,则结果为 NULL。
常用的几种正则表达式 |
---|
(1) ‘^’ 匹配以该字符后面的字符开头的字符串。 |
(2) ‘$’ 匹配以该字符前面的字符结尾的字符串。 |
(3) ‘ . ’ 匹配任何一个单字符。 |
(4) “[…]” 匹配在方括号内的任何字符。例如,”[abc]” 匹配 “a” 或 “b” 或 “c” 。为了命名字符的范围,使用一个‘-’ 。“[a-z]” 匹配任何字母,而 “[0-9]” 匹配任何数字。 |
(5) ‘*’ 匹配零个或多个在它前面的字符。例如,“x*” 匹配任何数量的 ‘x’ 字符,“[0-9]*” 匹配任何数量的数字,而 “*” 匹配任何数量的任何字符。 |
【巧记】‘^’ 小荷才露尖尖角,尖角向上,因此是匹配字符串开头的字符串;‘$’ 美元硬币扔进池塘里会沉底,因此是匹配字符串结尾的字符串。
【例子1】
SELECT 'xiesihang' REGEXP '^x', 'xiesihang' REGEXP 'g$', 'xiesihang' REGEXP 'ie'
FROM DUAL;
查询结果:
【例子2】
SELECT 'atguigu' REGEXP 'gu.gu', 'atguigu' REGEXP '[ab]'
FROM DUAL;
查询结果:
以上就是MySQL中比较运算符的使用介绍,希望对你有帮助。