A2-02-08.DML-MySQL BETWEEN Operator Explained

转载自：http://www.mysqltutorial.org/mysql-between

Home / Basic MySQL Tutorial / MySQL BETWEEN Operator Explained

MySQL BETWEEN Operator Explained

 

Summary: in this tutorial, you will learn how to use MySQL BETWEEN operator to determine whether a value is in a range of values.

Introduction to MySQL BETWEEN Operator

The BETWEEN operator allows you to specify a range to test. We often use the BETWEEN operator in the WHERE clause of the SELECT, INSERT, UPDATE, and DELETE statements.

The following illustrates the syntax of the BETWEEN operator:

 

1	expr [NOT] BETWEEN begin_expr AND end_expr;

The expr is the expression to test in the range that is defined by begin_expr and end_expr.

All three expressions: expr, begin_expr, and end_expr must have the same data type.

The BETWEEN operator returns true 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 the end_expr otherwise it returns zero.

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

If any expression is NULL, the BETWEEN operator returns a NULL value.

In case you want to specify an exclusive range, you use the greater than (>) and less than (<) operators.

MySQL BETWEEN examples

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

MySQL BETWEEN with number examples

See the following products table in the sample database:

Suppose you want to find products whose buy prices are within the ranges of $90 and $100, you can use the BETWEEN operator as the following query:

 

1 2 3 4 5 6

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

Try It Out

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

 

1 2 3 4 5 6

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

Try It Out

To find the product whose buy price is not between $20 and $100, you combine the BETWEEN operator with the NOT operator as follows:

 

1 2 3 4 5 6

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

Try It Out

You can rewrite the query above using the less than (>), greater than (>), and logical operators (AND) as the following query.

 

1 2 3 4 5 6

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

Try It Out

MySQL BETWEEN with dates example

When you use the BETWEEN operator with date values, to get the best result, you should use the type cast to explicitly convert the type of column or expression to the DATE type.

For example, to get the orders whose required dates are from 01/01/2003 to 01/31/2003, you use the following query:

 

1 2 3 4 5 6 7

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

Try It Out

Because the data type of the required date column is DATE so we used the cast operator 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.

Related Tutorials

• Using MySQL DISTINCT to Eliminate Duplicates
• Using MySQL LIMIT to Constrain The Number of Rows Returned By SELECT Statement
• Using MySQL SELECT Statement to Query Data

转载于:https://www.cnblogs.com/zhuntidaoren/p/9511806.html