mysql数据库教程-MySQL BETWEEN运算符

跨零代码为大家提供高品质的解决方案,请大家多多来访,跨零不胜感激,在此谢过。

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

MySQL BETWEEN运算符介绍

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

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

expr [NOT] BETWEEN begin_expr AND end_expr; 

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

所有三个表达式:exprbegin_exprend_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 

假设您想要查找价格在90100(含90100)元范围内的商品,可以使用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 

要查找购买价格不在20100(含20100)之间的产品,可将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-012013-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运算符来测试值是否在值的范围内。

  

从零到一,创造未来!跨零综合IT问题解决服务站,欢迎你的到来。Oracle数据库教程 mysql数据库教程/a> 只为你绽放。

本文固定链接: http://kua0.com/2019/04/19/mysql数据库教程-mysql-between运算符/

为您推荐

发表评论

电子邮件地址不会被公开。 必填项已用*标注