如何使用SQL BETWEEN运算符选择指定范围内的值

本教程将演示如何使用SQL BETWEEN运算符选择指定范围内的值。

SQL BETWEEN运算符简介

BETWEEN运算符是逻辑运算符。 它返回true，false或unknown值。 BETWEEN运算符用于SELECT，UPDATE或DELETE语句中以查找范围内的值。

以下说明了BETWEEN运算符的语法：

expression BETWEEN low AND high;

在上面语法中，

• 表达式是在低和高定义的范围内测试的表达式。
• low和high可以是表达式或文字值，要求low的值小于high的值。

如果表达式大于或等于(>=)low值且小于或等于(<=)high值，则BETWEEN运算符返回true，这相当于以下条件：

expression >= low and expression <= high

如果要指定独占范围，则必须使用小于(<)和大于(>)的比较运算符。

要否定BETWEEN运算符的结果，可以添加NOT运算符：

expression NOT BETWEEN low AND high

如果表达式小于低于或大于(>)的值，则NOT BETWEEN返回true; 否则它返回：false。

可以使用以下条件重写NOT BETWEEN：

expression < low OR expression > high

2. SQL BETWEEN运算符示例

我们将使用示例数据库中的employees表来演示BETWEEN运算符的工作原理。更多教程请访问http://www.manongzj.com

mysql> DESC employees;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| employee_id   | int(11)      | NO   | PRI | NULL    | auto_increment |
| first_name    | varchar(20)  | YES  |     | NULL    |                |
| last_name     | varchar(25)  | NO   |     | NULL    |                |
| email         | varchar(100) | NO   |     | NULL    |                |
| phone_number  | varchar(20)  | YES  |     | NULL    |                |
| hire_date     | date         | NO   |     | NULL    |                |
| job_id        | int(11)      | NO   | MUL | NULL    |                |
| salary        | decimal(8,2) | NO   |     | NULL    |                |
| manager_id    | int(11)      | YES  | MUL | NULL    |                |
| department_id | int(11)      | YES  | MUL | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
10 rows in set

SQL BETWEEN与数字示例

以下语句使用BETWEEN运算符查找薪水在2500和3000之间的所有员工：

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary BETWEEN 2500 AND 3000;

执行上面查询语句，得到以下结果 -

请注意，结果集中包含薪水为2500和3000的员工。

以下查询使用大于或等于(>=)且小于或等于(<=)的比较运算符和逻辑运算符AND，返回相同的结果集：

SELECT
    employee_id,
    first_name,
    last_name,
    salary
FROM
    employees
WHERE
    salary >= 2500
AND salary <= 3000;

要查找薪水不在2500和10000范围内的所有员工，请在WHERE子句中使用NOT BETWEEN运算符，如下所示：

SELECT 
    employee_id, first_name, last_name, salary
FROM
    employees
WHERE
    salary NOT BETWEEN 2500 AND 10000
ORDER BY salary;

执行上面查询语句，得到以下结果 -

 

SQL BETWEEN日期示例

要查找在1999年1月1日到2000年12月31日之间加入公司的所有员工，查询雇用日期(hire_date)是否在以下范围内：

SELECT 
    employee_id, first_name, last_name, hire_date
FROM
    employees
WHERE
    hire_date BETWEEN '1999-01-01' AND '2000-12-31'
ORDER BY hire_date;

执行上面示例代码，得到以下结果 -

+-------------+------------+-----------+------------+
| employee_id | first_name | last_name | hire_date  |
+-------------+------------+-----------+------------+
|         107 | Diana      | Chen      | 1999-02-07 |
|         178 | Kimberely  | Yang      | 1999-05-24 |
|         119 | Karen      | Zhang     | 1999-08-10 |
|         113 | Min        | Su        | 1999-12-07 |
|         179 | Charles    | Yang      | 2000-01-04 |
+-------------+------------+-----------+------------+
5 rows in set

要检索不在1989年1月1日到1999年12月31日这个时间内加入公司的所有员工，请使用NOT BETWEEN运算符，如下所示：

SELECT 
    employee_id, first_name, last_name, hire_date
FROM
    employees
WHERE
    hire_date NOT BETWEEN '1989-01-01' AND '1999-01-30'
ORDER BY hire_date;

执行上面示例代码，得到以下结果 -

 

3. SQL BETWEEN运算符使用说明

1. 低和高的值

BETWEEN运算符需要低值和高值。 当从用户那里获得输入时，在将其传递给查询之前，应始终检查低值是否小于高值。 如果低值大于高值，将得到一个空的结果集。

以下查询不返回任何行，因为低值大于高值：

SELECT 
    first_name, last_name, salary
FROM
    employees
WHERE
    salary BETWEEN 5000 AND 2000;

2. 将BETWEEN运算符与DATETIME数据一起使用

考虑以下tb1表，结构和数据如下所示：

 

2019年9月23日至2019年9月30日期间有5行数据。如果使用BETWEEN运算符查询create_time值介于2019-09-23和2019-09-30之间的行记录，将获得所期望的内容。

SELECT 
    id, create_time
FROM
    tb1
WHERE
    create_time BETWEEN '20190920' AND '20190930';

执行上面查询语句，得到以下结果 -

 

结果显示只返回了4行。 这是因为当使用以下条件时：

create_time BETWEEN '20190920' AND '20190930';

数据库系统将其转换为：

create_time BETWEEN '20190920 00:00:00.000000' AND '20190930 00:00:00.000000';

因此，2019-09-30 14:29:55值的行记录未包含在结果集中。

在本教程中，我们学习示了如何使用SQL BETWEEN运算符根据范围选择数据，通过本教程，可以使用BETWEEN运算符来组合成为更复杂的查询语句。