SQL BETWEEN 操作符

SQL BETWEEN 操作符用来选取在两个值范围之间的数据。

SQL BETWEEN 操作符语法

SELECT ColumnName1, ColumnName2, ColumnName3, ...
FROM 表名
WHERE ColumnName BETWEEN Value1 AND Value2

BETWEEN 操作符可以用于SQL SELECT, UPDATE, INSERT 或 DELETE 语句;Value1, Value2可以是数字,文本或日期。

SQL BETWEEN 操作符范例

数据表: Books

BookId BookName Price
203 Mazojys 26.00
204 Jozzh 14.00
205 Syllauu 37.00
206 Gecrrcc 8.20
302 Jssme 52.00
303 Dnnaao 28.00
304 Tyoysww 17.00

Example 1
我们想选择所有价格在$17.00和$28.00之间的书:

SELECT *
FROM Books
WHERE Price BETWEEN 17 AND 28

对于SQL Server, Oracle, mySQL, 结果会包含那些价格正好是$17.00 或者 $28.00的书,所有结果会类似于:

BookId BookName Price
203 Mazojys 26.00
303 Dnnaao 28.00
304 Tyoysww 17.00

Example 2
我们想选择那些价格范围不在$17.00和$28.00之间的书:

SELECT *
FROM Books
WHERE Price NOT BETWEEN 17 AND 28

结果会类似于:

BookId BookName Price
204 Jozzh 14.00
205 Syllauu 37.00
206 Gecrrcc 8.20
302 Jssme 52.00