SQL BETWEEN 操作符 #

BETWEEN 操作符用于选取介于两个值之间的数据范围内的值。

BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。

SQL BETWEEN 语法 #

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

BETWEEN 操作符实例 #

下面的 SQL 语句选取 id 介于 10 和 20 之间的所有记录：

MariaDB [learnsql]> select * from domains where id between 10 and 20;
+----+----------+---------------+-----------+-----------+
| ID | ICP      | DomainName    | RegDate   | EndDate   |
+----+----------+---------------+-----------+-----------+
| 12 | namesilo | blbl.dev      | 2020/1/17 | 2023/1/17 |
| 13 | namesilo | raspi.in      | 2014/2/27 | 2023/2/27 |
| 14 | namesilo | pornie.in     | 2016/3/10 | 2023/3/10 |
| 15 | namesilo | aosp.me       | 2016/3/17 | 2023/3/17 |
| 16 | namesilo | bailuyuan.org | 2017/3/23 | 2023/3/23 |
| 17 | wanwang  | linuxuc.com   | 2016/3/24 | 2023/3/24 |
| 18 | namesilo | ustv.xyz      | 2018/3/24 | 2023/3/25 |
| 19 | wanwang  | unixetc.com   | 2017/3/30 | 2023/3/30 |
| 20 | namesilo | ciux.org      | 2010/4/26 | 2023/4/26 |
+----+----------+---------------+-----------+-----------+
9 rows in set (0.007 sec)

NOT BETWEEN 操作符实例 #

如需显示不在上面实例范围内的网站，请使用 NOT BETWEEN：

MariaDB [learnsql]> select * from domains where id not between 10 and 40;
+----+-----------+--------------+------------+------------+
| ID | ICP       | DomainName   | RegDate    | EndDate    |
+----+-----------+--------------+------------+------------+
|  4 | namesilo  | getos.org    | 2016/10/9  | 2022/10/9  |
|  6 | wanwang   | dotbbq.com   | 2017/10/28 | 2022/10/28 |
|  7 | wanwang   | lyq.wiki     | 2018/12/27 | 2022/12/28 |
|  8 | namesilo  | les.moe      | 2015/12/31 | 2022/12/31 |
|  9 | namesilo  | regex.in     | 2020/1/3   | 2023/1/3   |
| 43 | dnspod.cn | zjq.xyz      | 2021-08-06 | 2022-10-24 |
| 44 | dnspod.cn | alair.cn     | 2021-08-06 | 2022-12-10 |
| 45 | dnspod.cn | ymqd.net     | 2022-01-28 | 2023-03-03 |
| 46 | dnspod.cn | nasplus.cn   | 2022-01-28 | 2023-03-08 |
| 47 | dnspod.cn | manjushri.cn | 2022-01-28 | 2023-03-11 |
| 48 | dnspod.cn | pushto.cn    | 2022-01-28 | 2023-03-12 |
| 49 | dnspod.cn | gfwlist.cn   | 2022-01-28 | 2023-03-23 |
| 50 | dnspod.cn | byhzg.cn     | 2022-01-28 | 2023-04-14 |
| 52 | gandi.net | mua.wiki     | 2022/7/19  | 2023/7/19  |
+----+-----------+--------------+------------+------------+
14 rows in set (0.000 sec)

带有 IN 的 BETWEEN 操作符实例 #

下面的 SQL 语句选取 id 介于 10 和 20 之间但 icp 不为 namesilo 和 google 的所有记录：

MariaDB [learnsql]> select * from domains where ( id between 10 and 20) and icp not in('google','namesilo');
+----+---------+-------------+-----------+-----------+
| ID | ICP     | DomainName  | RegDate   | EndDate   |
+----+---------+-------------+-----------+-----------+
| 17 | wanwang | linuxuc.com | 2016/3/24 | 2023/3/24 |
| 19 | wanwang | unixetc.com | 2017/3/30 | 2023/3/30 |
+----+---------+-------------+-----------+-----------+
2 rows in set (0.000 sec)

带有文本值的 BETWEEN 操作符实例 #

下面的 SQL 语句选取 dmainname 以介于 ‘h’ 和 ’n’ 之间字母开始的所有记录：

MariaDB [learnsql]> select * from domains where domainname between 'h' and 'n';
+----+-----------+--------------+------------+------------+
| ID | ICP       | DomainName   | RegDate    | EndDate    |
+----+-----------+--------------+------------+------------+
|  7 | wanwang   | lyq.wiki     | 2018/12/27 | 2022/12/28 |
|  8 | namesilo  | les.moe      | 2015/12/31 | 2022/12/31 |
| 17 | wanwang   | linuxuc.com  | 2016/3/24  | 2023/3/24  |
| 24 | namesilo  | ltecn.com    | 2014/7/26  | 2025/7/26  |
| 47 | dnspod.cn | manjushri.cn | 2022-01-28 | 2023-03-11 |
| 52 | gandi.net | mua.wiki     | 2022/7/19  | 2023/7/19  |
+----+-----------+--------------+------------+------------+
6 rows in set (0.000 sec)

带有文本值的 NOT BETWEEN 操作符实例 #

下面的 SQL 语句选取 domainname 不介于 ‘h’ 和 ‘p’ 之间字母开始的所有记录：

MariaDB [learnsql]> select * from domains where domainname not between 'h' and 'p';
+----+-----------+---------------+------------+------------+
| ID | ICP       | DomainName    | RegDate    | EndDate    |
+----+-----------+---------------+------------+------------+
|  4 | namesilo  | getos.org     | 2016/10/9  | 2022/10/9  |
|  6 | wanwang   | dotbbq.com    | 2017/10/28 | 2022/10/28 |
|  9 | namesilo  | regex.in      | 2020/1/3   | 2023/1/3   |
| 12 | namesilo  | blbl.dev      | 2020/1/17  | 2023/1/17  |
| 13 | namesilo  | raspi.in      | 2014/2/27  | 2023/2/27  |
| 14 | namesilo  | pornie.in     | 2016/3/10  | 2023/3/10  |
| 15 | namesilo  | aosp.me       | 2016/3/17  | 2023/3/17  |
| 16 | namesilo  | bailuyuan.org | 2017/3/23  | 2023/3/23  |
| 18 | namesilo  | ustv.xyz      | 2018/3/24  | 2023/3/25  |
| 19 | wanwang   | unixetc.com   | 2017/3/30  | 2023/3/30  |
| 20 | namesilo  | ciux.org      | 2010/4/26  | 2023/4/26  |
| 21 | namesilo  | avlist.top    | 2021/6/5   | 2023/6/5   |
| 22 | google    | suopo.net     | 2020/6/17  | 2023/6/17  |
| 25 | namesilo  | pornie.top    | 2016/3/10  | 2027/3/10  |
| 43 | dnspod.cn | zjq.xyz       | 2021-08-06 | 2022-10-24 |
| 44 | dnspod.cn | alair.cn      | 2021-08-06 | 2022-12-10 |
| 45 | dnspod.cn | ymqd.net      | 2022-01-28 | 2023-03-03 |
| 48 | dnspod.cn | pushto.cn     | 2022-01-28 | 2023-03-12 |
| 49 | dnspod.cn | gfwlist.cn    | 2022-01-28 | 2023-03-23 |
| 50 | dnspod.cn | byhzg.cn      | 2022-01-28 | 2023-04-14 |
+----+-----------+---------------+------------+------------+
20 rows in set (0.001 sec)

请注意，在不同的数据库中，BETWEEN 操作符会产生不同的结果！

在某些数据库中，BETWEEN 选取介于两个值之间但不包括两个测试值的字段。
某些数据库中，BETWEEN 选取介于两个值之间且包括两个测试值的字段。
某些数据库中，BETWEEN 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段。

因此，请检查您的数据库是如何处理 BETWEEN 操作符！