SQL中的BETWEEN运算符用于筛选指定范围内的数据值,它是一个逻辑运算符,返回布尔值(TRUE/FALSE)。
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
-- 查找价格在10到20之间的产品
SELECT ProductName, Price
FROM Products
WHERE Price BETWEEN 10 AND 20;
-- 查找2023年1月1日到2023年12月31日之间的订单
SELECT OrderID, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';
-- 查找名字以A到M开头的客户
SELECT CustomerName
FROM Customers
WHERE CustomerName BETWEEN 'A' AND 'N';
BETWEEN 10 AND 20
有效,而 BETWEEN 20 AND 10
无效-- 查找价格不在10到20之间的产品
SELECT ProductName, Price
FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
BETWEEN运算符可以改写为使用>=和<=的组合:
SELECT ProductName, Price
FROM Products
WHERE Price >= 10 AND Price <= 20;
两者在功能上是等价的,但BETWEEN通常更易读。