mysql中begin和between_MySQL 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的值小于(

如果任何表达式为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运算符来测试值是否在值的范围内。

¥ 我要打赏

纠错/补充

收藏

加QQ群啦，易百教程官方技术学习群

注意：建议每个人选自己的技术方向加群，同一个QQ最多限加 3 个群。