MySQL Where between


The BETWEEN operator is used in a WHERE expression to select a range of data between two values. BETWEEN together with the use of AND, the syntax is as follows:

WHERE column BETWEEN value1 AND value2
WHERE column NOT BETWEEN value1 AND value2

Normally value1 should be less than value2. When BETWEEN preceded by NOT operator, that with the opposite BETWEEN mean, that is selected outside this range of values.

example

Select user data with uid between 2 and 5:

SELECT * FROM user WHERE uid BETWEEN 2 AND 5

The results are as follows:

uid username password email regdate
2 John a193686a53e4de85ee3f2ff0576adf01 john@gmail.com 1278063917
3 Jack 0193686a35e4de85ee3f2ff0567adf490 jack@gmail.com 1278061380
4 Marry e10adc3949ba59abbe56e057f20f883e marry@gmail.com 1289632955
5 admin a193686a53e4de85ee3f2ff0576adf01 admin@gmail.com 1291107029

Besides numeric types, BETWEEN also supports string ranges.
For example, selecting all users with a username between a and j (and including the single letter k / K) as follows:

SELECT * FROM user WHERE username BETWEEN 'a' AND 'k'

MySQL BETWEEN The date and time

BETWEEN AND commonly used to retrieve the contents of the time or date

Example
-- int Timestamp format, query 2008-08-08 20:00:00 to 2009-01-01 Data before the zero point
SELECT * FROM table WHERE column_time BETWEEN 1218196800 AND 1230739199

-- DATE format, query 2008-08-08 to 2009-01-01 before the zero data
SELECT * FROM table WHERE column_time BETWEEN '2008-08-08' AND '2009-01-01'

-- DATETIME format, query 2008-08-08 20:00:00 to 2009-01-01 data before the zero point 
SELECT * FROM table WHERE column_time BETWEEN '2008-08-08 20:00:00' AND '2008-12 -31 23:59:59 '

But for the query to the current time data, it is recommended to use >= operator:

-- DATETIME format, query 2008-08-08 20:00:00 to the current time of the data 
SELECT * FROM table WHERE column_time >= '2008-08-08 20:00:00'

MySQL BETWEEN data comparison

BETWEEN also has a data comparison function, the syntax is as follows:

Expr BETWEEN min AND max

BETWEEN returns 1 if the value of the expr expression is greater than or equal to min and less than or equal to max, and 0 otherwise. Using this function, you can determine whether an expression or value is in a range:

--  Returns 0
SELECT 1 BETWEEN 2 AND 3
--  Returns 1
SELECT 'b' BETWEEN 'a' AND 'c'
--  Determine the date range 
SELECT 20080808 BETWEEN 20080101 AND 20090101



Copyright 2014-2017 by DeveloperTut.com. All Rights Reserved.
TwitterGoogle+Facebook