为了账号安全,请及时绑定邮箱和手机立即绑定

MySQL BETWEEN 操作符详解

标签:
MySQL

Summary: in this tutorial, you will learn how to use MySQL BETWEEN operator to specify a range to test.

Introduction to MySQL BETWEEN Operator

The BETWEEN operator allows you to specify a range to test. The following illustrates the syntax of the BETWEENoperator:

expr (NOT) BETWEEN begin_expr AND end_expr

In the expression above:

  • All expressions: expr, begin_expr and end_expr must return values with the same data type.

  • The BETWEEN operator returns 1 if the value of the expr  is greater than or equal to (>=) the value of begin_expr and less than or equal to (<= ) the value of end_exprotherwise it returns 0.

  • The NOT BETWEEN returns 1 if the value of expr  is less than (<) the value of begin_expr or greater than the value of end_expr, otherwise it returns 0.

  • If any expression above is NULL, the BETWEEN returns NULL.

The BETWEEN operator is typically used in the WHERE clause of SELECTINSERTUPDATE and DELETE statements.

MySQL BETWEEN examples

Let’s practice with some examples of using the BETWEEN operator.

MySQL BETWEEN with number examples

Suppose you want to find product whose buy price within the range of $90 and $100, you can use the BETWEENoperator as the following query:

SELECT productCode,        productName,        buyPrice  FROM products WHERE buyPrice BETWEEN 90 AND 100

MySQL Between with numbers

You can achieve the same result by using the greater than or equal ( >=) and less than or equal ( <=) operators as the following query:

SELECT productCode,        productName,        buyPrice  FROM products WHERE buyPrice >= 90 AND buyPrice <= 100

To find the product whose buy price is out of the range of $20 and $100, you use combine the BETWEEN operator with the NOT operator as follows:

SELECT productCode,        productName,        buyPrice  FROM products WHERE buyPrice NOT BETWEEN 20 AND 100

MySQL BETWEEN with NOT operator

The query above is equivalent to the following query that uses the comparison operators, greater than operator ( >) and less than operator ( <) and a logical operator OR.

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

 

MySQL BETWEEN with dates example

When you use the BETWEEN operator with date values, to get the best result, you should use the CAST function to explicitly convert the type of column or expression to the DATE type. For example, to get the orders whose required date is from 01/01/2003 to 01/31/2003, you use the following query:

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

MySQL BETWEEN with Dates

In the query above, because the data type of the required date column is DATE so we used the CAST function to convert the literal strings ‘ 2003-01-01‘ and ‘ 2003-12-31‘ to the DATE data type.

In this tutorial, you have learned how to use the BETWEEN operator to test if a value falls within a range of values. You also learn how to combine the BETWEEN operator with the NOT operator to select data whose value that are not in a range of values.

Related Tutorials

原文链接:http://outofmemory.cn/mysql/mysql-between

点击查看更多内容
TA 点赞

若觉得本文不错,就分享一下吧!

评论

作者其他优质文章

正在加载中
  • 推荐
  • 评论
  • 收藏
  • 共同学习,写下你的评论
感谢您的支持,我会继续努力的~
扫码打赏,你说多少就多少
赞赏金额会直接到老师账户
支付方式
打开微信扫一扫,即可进行扫码打赏哦
今天注册有机会得

100积分直接送

付费专栏免费学

大额优惠券免费领

立即参与 放弃机会
意见反馈 帮助中心 APP下载
官方微信

举报

0/150
提交
取消