MySQL BETWEEN 操作符详解@慕课网原创_慕课网

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_expr, otherwise 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 SELECT, INSERT, UPDATE 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

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

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)

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.