SQL BETWEEN 语句

SQL BETWEEN 描述

BETWEEN 运算符选择给定范围内的值。值可以是数字、文本或日期。

BETWEEN 运算符选择包括开始值和结束值的数据。

BETWEEN 语法

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

演示数据库

以下是“Products”表中的示例：

SQL BETWEEN 示例

以下 SQL 语句选择价格在 10 到 20 之间的所有产品：

Run SQLSELECT * FROM Products 
WHERE Price BETWEEN 10 AND 20

NOT BETWEEN 示例

要显示上一个示例范围之外的产品，请使用 NOT BETWEEN：

Run SQLSELECT * FROM Products 
WHERE Price NOT BETWEEN 10 AND 20

BETWEEN 和 IN 示例

以下 SQL 语句选择价格在 10 到 20 之间的所有产品，同时不显示 CategoryID 1、2 或 3 的产品：

Run SQLSELECT * FROM Products 
WHERE Price BETWEEN 10 AND 20 
AND CategoryID NOT IN (1,2,3)

BETWEEN 带有文本值示例

以下 SQL 语句从 ProductName 中选择“Carnarvon Tigers”和“Mozzarella di Giovanni”之间的所有产品：

Run SQLSELECT * FROM Products 
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' 
ORDER BY ProductName

以下 SQL 语句从 ProductName 中选择“Carnarvon Tigers”和“Chef Anton's Cajun Seasoning”之间的所有产品：

Run SQLSELECT * FROM Products 
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Chartreuse verte' 
ORDER BY ProductName

NOT BETWEEN 带有文本值示例

以下 SQL 语句从 ProductName 选择所有产品，而不是在“Carnarvon Tigers”和“Mozzarella di Giovanni”之间：

Run SQLSELECT * FROM Products 
WHERE ProductName NOT BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni' 
ORDER BY ProductName

表样

以下是表“Orders”（“订单”）中的示例：

BETWEEN 与日期示例

以下 SQL 语句从 OrderDate 中选择“01-July-1996”和“31-July-1996”之间的所有订单 (Orders)：

SELECT * FROM Orders 
WHERE OrderDate BETWEEN #07/01/1996# AND #07/31/1996#

或者：

Run SQLSELECT * FROM Orders 
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31'