SQL between syntax and Data Query Method

BETWEEN Operator
The BETWEEN operator... AND selects a data range BETWEEN two values. This

Some values can be numerical values, text values, or dates.

SQL BETWEEN Syntax
SELECT <column_name> FROM <table_name>
WHERE (<column_name> BETWEEN value1 AND value2

Mysql tutorial>
Mysql> create table Employee (
-> Id int,
-> First_name VARCHAR (15 ),
-> Last_name VARCHAR (15 ),
-> Start_date DATE,
-> End_date DATE,
-> Salary FLOAT (8, 2 ),
-> City VARCHAR (10 ),
-> Description VARCHAR (15)
-> );
Query OK, 0 rows affected (0.05 sec)

Mysql>
Mysql>
Mysql> insert into Employee (id, first_name, last_name,

Start_date, end_Date, salary, City, Description)
-> Values (1, 'jason ', 'martin ',

'123', '123', 19960725, 'toronto ',

'Grammer ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name,

Start_date, end_Date, salary, City, Description)
-> Values (2, 'alison ', 'mathews ',

'123', '123', 19760321, 'vancouver ', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name,

Start_date, end_Date, salary, City, Description)
-> Values (3, 'James ', 'Smith ',

'123', '123', 19781212, 'vancouver ', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name,

Start_date, end_Date, salary, City, Description)
-> Values (4, 'cela', 'Rice ',

'123', '123', 19821024, 'vancouver ', 'manager ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name,

Start_date, end_Date, salary, City, Description)
-> Values (5, 'Robert ', 'black ',

'123', '123', 19840115,

'Www .zhutiai.com ', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name,

Start_date, end_Date, salary, City, Description)
-> Values (6, 'linda ', 'green ',

'123', '123', 19870730, 'New York ', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name,

Start_date, end_Date, salary, City, Description)
-> Values (7, 'David ', 'Larry ',

'123', '123', 19901231, 'New York ', 'manager ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> insert into Employee (id, first_name, last_name,

Start_date, end_Date, salary, City, Description)
-> Values (8, 'James ', 'cat ',

'123', '123', 19960917, 'vancouver ', 'tester ');
Query OK, 1 row affected (0.00 sec)

Mysql>
Mysql> select * from Employee;
+ ------ + ------------ + -----------

-+ --------- + ----------- + ------------- +
| Id | first_name | last_name | start_date | end_date

| Salary | city | description |
+ ------ + ------------ + -----------

-+ --------- + ----------- + ------------- +
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25

| 1234.56 | Toronto | Programmer |
| 2 | Alison | Mathews | 1976-03-21 | 1986-02-21

| 1, 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15

| 1, 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 2.16-04-21

| 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08

| 1, 2334.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04

| 4322.78 | New York | Tester |
| 7 | David | Larry | 1990-12-31 | 1998-02-12

| 7897.78 | New York | Manager |
| 8 | James | Cat | 1996-09-17 | 2002-04-15

| 1, 1232.78 | Vancouver | Tester |
+ ------ + ------------ + -----------

-+ --------- + ----------- + ------------- +
8 rows in set (0.00 sec)

Mysql>
Mysql>
Mysql>
Mysql> SELECT * FROM employee
-> WHERE (employee. salary BETWEEN 1000 AND 2000 );
+ ------ + ------------ + -----------

-+ --------- + ----------- + ------------- +
| Id | first_name | last_name | start_date | end_date

| Salary | city | description |
+ ------ + ------------ + -----------

-+ --------- + ----------- + ------------- +
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25

| 1234.56 | Toronto | Programmer |
| 8 | James | Cat | 1996-09-17 | 2002-04-15

| 1, 1232.78 | Vancouver | Tester |
+ ------ + ------------ + -----------

-+ --------- + ----------- + ------------- +
2 rows in set (0.02 sec)

Records Between two strings

Mysql> SELECT * FROM employee
-> WHERE (employee. first_name BETWEEN 'elvis 'AND

'Michael ');
+ ------ + ------------ + -----------

-+ --------- + ----------- + ------------- +
| Id | first_name | last_name | start_date | end_date

| Salary | city | description |
+ ------ + ------------ + -----------

-+ --------- + ----------- + ------------- +
| 1 | Jason | Martin | 1996-07-25 | 2006-07-25

| 1234.56 | Toronto | Programmer |
| 3 | James | Smith | 1978-12-12 | 1990-03-15

| 1, 6544.78 | Vancouver | Tester |
| 6 | Linda | Green | 1987-07-30 | 1996-01-04

| 4322.78 | New York | Tester |
| 8 | James | Cat | 1996-09-17 | 2002-04-15

| 1, 1232.78 | Vancouver | Tester |
+ ------ + ------------ + -----------

-+ --------- + ----------- + ------------- +

If you want to judge whether they are not between them, use not between x and y to get KO.

Single query statement

Mysql> SELECT Name, InStock, OnOrder, Reserved
-> FROM Topic
-> WHERE (InStock + OnOrder-Reserved) not between 10 AND

20
-> Order by Name;

Important: The Processing Method for The BETWEEN... AND operator in different database tutorials is

There are differences. Some databases list

People, but not "Adams" and "Carter"; some databases list

"Adams" and "Carter" include "Adams" and "Carter;

Other databases list people between "Adams" and "Carter ".

Including "Adams", but not "Carter"