轻松上手,快乐学习!

SQL BETWEEN 运算符


SQL BETWEEN 运算符

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

BETWEEN运算符是包含的:包括开始值和结束值。

在语法之间

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

演示数据库

以下是Northwind示例数据库中“Products”表的选择:

ProductID ProductName SupplierID CategoryID Unit Price
1 Chais 1 1 10 boxes x 20 bags 18
2 Chang 1 1 24 - 12 oz bottles 19
3 Aniseed Syrup 1 2 12 - 550 ml bottles 10
4 Chef Anton's Cajun Seasoning 1 2 48 - 6 oz jars 22
5 Chef Anton's Gumbo Mix 1 2 36 boxes 21.35

BETWEEN 例子

以下SQL语句选择BETWEEN 10和20之间的所有产品:

实例

SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;

运行实例»


NOT BETWEEN 实例

要显示上一实例范围之外的产品,请使用NOT BETWEEN:

实例

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;

运行实例»


与 IN 实例之间的关系

以下SQL语句选择价格介于10和20之间的所有产品同时不要显示CategoryID为1,2或3的产品:

实例

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

运行实例»


BETWEEN 文本值实例

以下SQL语句选择所有具有ProductName 在 Carnarvon Tigers和Mozzarella di Giovanni 之间的产品:

实例

SELECT * FROM Products
WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella di Giovanni'
ORDER BY ProductName;

运行实例»

以下SQL语句选择所有具有ProductName在 Carnarvon Tigers和Chef Anton's Cajun Seasoning之间的产品:

实例

SELECT * FROM Products
WHERE ProductName BETWEEN "Carnarvon Tigers" AND "Chef Anton's Cajun Seasoning"
ORDER BY ProductName;

运行实例»


NOT BETWEEN 文本值范围

以下SQL语句选择所有带有ProductName 不在 Carnarvon Tigers和Mozzarella di Giovanni之间的产品:

实例

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

运行实例»


样本表

以下是Northwind示例数据库中“Orders”表的选择:

OrderID CustomerID EmployeeID OrderDate ShipperID
10248 90 5 7/4/1996 3
10249 81 6 7/5/1996 1
10250 34 4 7/8/1996 2
10251 84 3 7/9/1996 1
10252 76 4 7/10/1996 2

在日期之间的例子

以下SQL语句选择OrderDate在 '01 -July-1996'和'31 -July-1996'之间的所有订单:

实例

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

运行实例»

或者:

实例

SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';

运行实例»