sql-between-operator

SQL - BETWEEN 操作符

1. SQL BETWEEN 操作符

BETWEEN 操作符是 SQL 中的一个逻辑操作符，用于选择指定范围内的值。该范围是包含的，意味着它包括起始值和结束值。它简化了原本需要两个独立的 AND 条件的查询。

WHERE column >= value1 AND column <= value2 等同于 WHERE column BETWEEN value1 AND value2。

语法

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

值可以是数字、文本或日期。

2. 数字、文本和日期实用示例

让我们为示例设置一个 Products 示例表。

CREATE TABLE Products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    stock_date DATE NOT NULL
);

INSERT INTO Products VALUES
    (1, 'Laptop', 1200.00, '2023-01-15'),
    (2, 'Mouse', 25.00, '2023-01-20'),
    (3, 'Keyboard', 75.00, '2023-02-10'),
    (4, 'Monitor', 300.00, '2023-02-25'),
    (5, 'Webcam', 50.00, '2023-03-05');

示例：数值范围

查找价格在 50 美元到 300 美元（含）之间的所有产品：

SELECT * FROM Products WHERE price BETWEEN 50.00 AND 300.00;

输出：

id	name	price	stock_date
3	Keyboard	75.00	2023-02-10
4	Monitor	300.00	2023-02-25
5	Webcam	50.00	2023-03-05

示例：文本范围

查找名称以字母“K”到“M”开头的产品。请注意，这是一个字典序比较。

SELECT * FROM Products WHERE name BETWEEN 'K' AND 'Mouse';

此查询将包含“Keyboard”、“Laptop”和“Monitor”，但也会包含“Mouse”，因为“Mouse”在字典序上介于“K”和它自身之间。一个常见的陷阱是 BETWEEN ‘K’ AND ‘M’ 不会包含“Monitor”或“Mouse”。要获取所有以 K、L 或 M 开头的名称，LIKE 条件通常更清晰：WHERE name LIKE ‘K%’ OR name LIKE ‘L%’ OR name LIKE ‘M%‘。

示例：日期范围

查找 2023 年 2 月入库的所有产品：

SELECT * FROM Products WHERE stock_date BETWEEN '2023-02-01' AND '2023-02-28';

输出：

id	name	price	stock_date
3	Keyboard	75.00	2023-02-10
4	Monitor	300.00	2023-02-25

3. NOT BETWEEN 操作符

NOT BETWEEN 操作符执行相反的操作：它选择指定范围之外的值。

SELECT * FROM Products WHERE price NOT BETWEEN 50.00 AND 300.00;

这等同于 WHERE price < 50.00 OR price > 300.00。

输出：

id	name	price	stock_date
1	Laptop	1200.00	2023-01-15
2	Mouse	25.00	2023-01-20

4. BETWEEN 与其他子句结合使用

结合 IN 操作符

你可以将 BETWEEN 与 IN 子句结合使用，进一步过滤结果。查找价格在 20 美元到 100 美元之间且是“Mouse”或“Keyboard”的产品。

SELECT * FROM Products
WHERE price BETWEEN 20.00 AND 100.00
AND name IN ('Mouse', 'Keyboard');

结合 UPDATE 语句

将所有 1 月入库产品的价格提高 10%。

UPDATE Products
SET price = price * 1.10
WHERE stock_date BETWEEN '2023-01-01' AND '2023-01-31';

结合 DELETE 语句

删除所有价格在 70 美元到 80 美元之间的产品。

DELETE FROM Products
WHERE price BETWEEN 70.00 AND 80.00;

5. 性能考量

对于大型表，要使 BETWEEN 高效，WHERE 子句中使用的列应该有索引。索引允许数据库快速定位范围的起始点，而无需扫描整个表。

-- 在 price 列上创建索引将加快价格范围查询。
CREATE INDEX idx_products_price ON Products(price);

在索引列上使用 BETWEEN 的查询通常非常快，并且是查询范围的首选方式。