3.5 BETWEEN 运算符

在本教程中，您将学习如何使用 MySQL BETWEEN运算符，使用它来确定值是否在一个值范围内。

MySQL BETWEEN 运算符介绍

BETWEEN运算符允许指定要测试的值范围。 我们经常在 SELECT，INSERT，UPDATE 和 DELETE 语句的 WHERE 子句中使用BETWEEN运算符。

下面说明了BETWEEN运算符的语法：

expr [NOT] BETWEEN begin_expr AND end_expr;

expr是在由begin_expr和end_expr定义的范围内测试的表达式。

所有三个表达式：expr，begin_expr和end_expr必须具有相同的数据类型。

如果expr的值大于或等于(>=)begin_expr的值且小于等于(<=)end_expr的值，则BETWEEN运算符返回true，否则返回0。

如果expr的值小于(<)begin_expr的值或大于end_expr的值的值，则NOT BETWEEN将返回true，否则返回0。

如果任何表达式为NULL，则BETWEEN运算符返回NULL值。如果想指定一个不含边界值的范围，则使用大于(>)和小于(<)运算符。

MySQL BETWEEN示例

下面我们来练习一些使用BETWEEN运算符的例子。

MySQL BETWEEN与数字示例

请参见示例数据库(yiibaidb)中的以下产品(products)表，表的结构如下所示：

mysql> desc products;
+--------------------+---------------+------+-----+---------+-------+
| Field              | Type          | Null | Key | Default | Extra |
+--------------------+---------------+------+-----+---------+-------+
| productCode        | varchar(15)   | NO   | PRI | NULL    |       |
| productName        | varchar(70)   | NO   |     | NULL    |       |
| productLine        | varchar(50)   | NO   | MUL | NULL    |       |
| productScale       | varchar(10)   | NO   |     | NULL    |       |
| productVendor      | varchar(50)   | NO   |     | NULL    |       |
| productDescription | text          | NO   |     | NULL    |       |
| quantityInStock    | smallint(6)   | NO   |     | NULL    |       |
| buyPrice           | decimal(10,2) | NO   |     | NULL    |       |
| MSRP               | decimal(10,2) | NO   |     | NULL    |       |
+--------------------+---------------+------+-----+---------+-------+
9 rows in set

假设您想要查找价格在90和100(含90和100)元范围内的商品，可以使用BETWEEN运算符作为以下查询：

mysql> SELECT 
    productCode, productName, buyPrice
FROM
    products
WHERE
    buyPrice BETWEEN 90 AND 100;
+-------------+--------------------------------------+----------+
| productCode | productName                          | buyPrice |
+-------------+--------------------------------------+----------+
| S10_1949    | 1952 Alpine Renault 1300             | 98.58    |
| S10_4698    | 2003 Harley-Davidson Eagle Drag Bike | 91.02    |
| S12_1099    | 1968 Ford Mustang                    | 95.34    |
| S12_1108    | 2001 Ferrari Enzo                    | 95.59    |
| S18_1984    | 1995 Honda Civic                     | 93.89    |
| S18_4027    | 1970 Triumph Spitfire                | 91.92    |
| S24_3856    | 1956 Porsche 356A Coupe              | 98.3     |
+-------------+--------------------------------------+----------+
7 rows in set

也可以通过使用大于或等于(>=)和小于或等于(<=)运算符来实现相同的结果，如以下查询：

mysql> SELECT 
    productCode, productName, buyPrice
FROM
    products
WHERE
    buyPrice >= 90 AND buyPrice <= 100;
+-------------+--------------------------------------+----------+
| productCode | productName                          | buyPrice |
+-------------+--------------------------------------+----------+
| S10_1949    | 1952 Alpine Renault 1300             | 98.58    |
| S10_4698    | 2003 Harley-Davidson Eagle Drag Bike | 91.02    |
| S12_1099    | 1968 Ford Mustang                    | 95.34    |
| S12_1108    | 2001 Ferrari Enzo                    | 95.59    |
| S18_1984    | 1995 Honda Civic                     | 93.89    |
| S18_4027    | 1970 Triumph Spitfire                | 91.92    |
| S24_3856    | 1956 Porsche 356A Coupe              | 98.3     |
+-------------+--------------------------------------+----------+
7 rows in set

要查找购买价格不在20到100(含20到100)之间的产品，可将BETWEEN运算符与NOT运算符组合使用，如下：

mysql> SELECT 
    productCode, productName, buyPrice
FROM
    products
WHERE
    buyPrice NOT BETWEEN 20 AND 100;
+-------------+-------------------------------------+----------+
| productCode | productName                         | buyPrice |
+-------------+-------------------------------------+----------+
| S10_4962    | 1962 LanciaA Delta 16V              | 103.42   |
| S18_2238    | 1998 Chrysler Plymouth Prowler      | 101.51   |
| S24_2840    | 1958 Chevy Corvette Limited Edition | 15.91    |
| S24_2972    | 1982 Lamborghini Diablo             | 16.24    |
+-------------+-------------------------------------+----------+
4 rows in set

您也可以使用少于(>)，大于(>)和逻辑运算符(AND)重写上述查询，如下所示 -

SELECT 
    productCode, productName, buyPrice
FROM
    products
WHERE
    buyPrice < 20 OR buyPrice > 100;

执行上面查询语句，得到以下结果 -

mysql> SELECT 
    productCode, productName, buyPrice
FROM
    products
WHERE
    buyPrice < 20 OR buyPrice > 100;
+-------------+-------------------------------------+----------+
| productCode | productName                         | buyPrice |
+-------------+-------------------------------------+----------+
| S10_4962    | 1962 LanciaA Delta 16V              | 103.42   |
| S18_2238    | 1998 Chrysler Plymouth Prowler      | 101.51   |
| S24_2840    | 1958 Chevy Corvette Limited Edition | 15.91    |
| S24_2972    | 1982 Lamborghini Diablo             | 16.24    |
+-------------+-------------------------------------+----------+
4 rows in set

MySQL BETWEEN 与日期类型数据示例

当使用BETWEEN运算符与日期类型值时，要获得最佳结果，应该使用类型转换将列或表达式的类型显式转换为DATE类型。

例如，要查询获取所需日期(requiredDate)从2013-01-01到2013-01-31的所有订单，请使用以下查询：

SELECT orderNumber,
         requiredDate,
         status
FROM orders
WHERE requireddate
    BETWEEN CAST('2013-01-01' AS DATE)
        AND CAST('2013-01-31' AS DATE);

执行上面查询语句，得到以下结果 -

mysql> SELECT orderNumber,
         requiredDate,
         status
FROM orders
WHERE requireddate
    BETWEEN CAST('2013-01-01' AS DATE)
        AND CAST('2013-01-31' AS DATE);
+-------------+--------------+---------+
| orderNumber | requiredDate | status  |
+-------------+--------------+---------+
|       10100 | 2013-01-13   | Shipped |
|       10101 | 2013-01-18   | Shipped |
|       10102 | 2013-01-18   | Shipped |
+-------------+--------------+---------+
3 rows in set

因为requiredDate列的数据类型是DATE，所以我们使用转换运算符将文字字符串“2013-01-01”和“2013-12-31”转换为DATE数据类型。

在本教程中，您已经学会了如何使用BETWEEN运算符来测试值是否在值的范围内。