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;
1
2

查询结果：

• 如果比较运算符的两边的字符串都是字母，则不会触发隐式转换机制。SQL将按字符的ASCII值来进行比较运算。如下代码所示：

SELECT 'a' = 'a', 'ab' = 'ab', 'a' = 'b'
FROM DUAL;
1
2

查询结果：

• 只要有 (NULL) 参与的运算，结果一定为 (NULL) 。

SELECT 1 = NULL, NULL = NULL
FROM DUAL;
1
2

查询结果：

1.2 安全等与运算符

安全等于运算符 <=> 是为了让空值 (NULL) 参与比较运算而生的。

上面提到， (NULL) 参与的任何运算都为 (NULL) 。如果有这样一个需求：希望能查询员工表 employees 中奖金比率 commission_pct 为 (NULL) 的员工。很多新手会编写下面这样错误的代码：

SELECT employee_id, last_name, commission_pct
FROM employees
WHERE commission_pct = NULL;
1
2
3

查询结果：

因为 (NULL) 参与的任何运算都为 (NULL) ，所以采用这样查询不会显示任何结果。为了解决 (NULL) 参与的任何运算都为 (NULL) 这个令人头疼的问题，安全等于运算符就应运而生了。

• <=> 安全等于运算符的作用与等于运算符一样，唯一的区别是，当有 (NULL) 参与运算时，其结果不会为 (NULL) 。因此，其可以用于判断字段是否为 (NULL) 。如下代码所示：

SELECT employee_id, last_name, commission_pct
FROM employees
WHERE commission_pct <=> NULL;
1
2
3

查询结果：

有了安全等于运算符 <=> 的帮助，现在我们就可以正确地查询员工表 employees 中奖金比率 commission_pct 为 (NULL) 的员工了。

【例子】

SELECT 1 <=> NULL, NULL <=> NULL
FROM DUAL;
1
2

查询结果：

2.关键字

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;
1
2
3

查询结果：

• IS NOT NULL 是 IS NULL 的逆运算，是查询字段不为 (NULL) 的元组。

SELECT employee_id, last_name, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL;
1
2
3

查询结果：

• ISNULL 和 IS NULL 一个空格之差，它们的用法就是不一样的。但是它们的功能是一样的。如果想把 ISNULL 作为关键字用来查询字段为 (NULL) 的元组。SQL就会报错，如下代码所示：

SELECT employee_id, last_name, commission_pct
FROM employees
WHERE commission_pct ISNULL;
1
2
3

查询结果：

【正确用法】ISNULL() 是一个函数，正确的用法是把要判断是否为空的字段名作为参数传入ISNULL() 函数中，如果传入的参数为 (NULL) ，就返回 1 。如下代码所示：

SELECT employee_id, last_name, commission_pct
FROM employees
WHERE ISNULL(commission_pct);
1
2
3

查询结果：

2.2 LEAST / GREATEST 的使用

• LEAST() 和 GREATEST() 也是函数。通过往括号中传入多个需要比较的参数来返回最小 (或最大) 值。如下代码所示：

SELECT LEAST('a', 'b', 'c'), GREATEST('a', 'b', 'z')
FROM DUAL;
1
2

查询结果：

可见是比较字符串的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;
1
2
3

查询结果：

从查询结果中可以看到是包含边界值的。即区间左闭右闭。

2.4 IN (set) / NOT IN(set)

• 上面的 BETWEEN…AND… 查询的是在连续值范围的元组。而 IN (set) / NOT IN(set) 就是查询离散值的。
• 例如，查询员工表 employees 中部门ID department_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);
1
2
3

查询结果：

• 相反，NOT IN 就是 IN 的逆运算。例如，查询员工表 employees 中部门ID department_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);
1
2
3

查询结果：

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%');
1
2
3

查询结果：

第 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%');
1
2
3

查询结果：

【例子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%';
1
2
3
4

# 写法二
SELECT employee_id, last_name
FROM employees
WHERE last_name LIKE '%a%e%' OR last_name LIKE '%e%a%';
1
2
3
4

写法二 中之所以要写两个，是因为前者是字母 ‘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%';
1
2
3

查询结果：

【例子5】我们来提出一个更加变态的需求。查询员工表 employees 中名字 last_name 中第2个字符串是下划线 _ 且第3个字符是 'a' 的员工的 employee_id 、last_name 信息。

• 这一题需要使用转义字符 \ ，转义字符 \ 后面紧跟的 _ 就表示纯粹的下划线 _ 的意思，而不是代表一个不确定的字符的意思。如下代码所示：

SELECT employee_id, last_name
FROM employees
WHERE last_name LIKE '_\_a%';
1
2
3

查询结果：

• 【补充】也可以用关键字 ESCAPE 自定义转义字符。如下代码所示：

SELECT employee_id, last_name
FROM employees
WHERE last_name LIKE '_$_a%' ESCAPE '$';
1
2
3

2.6 REGEXP / RLIKE: 正则表达式

• 正则表达式是一种独立于SQL之外的一种规则。其体系过于庞大，碍于篇幅不在此详细介绍。在此仅介绍简单的几种正则表达式作为了了解，感兴趣的朋友可以留言点赞，我会之后专门更新一期正则表达式的文章。
• REGEXP 用来匹配字符串，语法格式为 expr REGEXP 匹配条件 。如果 expr 满足匹配条件，返回1；如果不满足，则返回0；若 expr 或匹配条件任意一个为 NULL ，则结果为 NULL。

【巧记】‘^’ 小荷才露尖尖角，尖角向上，因此是匹配字符串开头的字符串；‘$’ 美元硬币扔进池塘里会沉底，因此是匹配字符串结尾的字符串。

【例子1】

SELECT 'xiesihang' REGEXP '^x', 'xiesihang' REGEXP 'g$', 'xiesihang' REGEXP 'ie'
FROM DUAL;
1
2

查询结果：

【例子2】

SELECT 'atguigu' REGEXP 'gu.gu', 'atguigu' REGEXP '[ab]'
FROM DUAL;
1
2

查询结果：

以上就是MySQL中比较运算符的使用介绍，希望对你有帮助。