BETWEEN 操作符
操作符 BETWEEN ... AND 會選取介於兩個值之間的數據范圍。這
些值可以是數值、文本或者日期。
SQL BETWEEN 語法
SELECT <column_name> FROM <table_name>
WHERE (<column_name> BETWEEN value1 AND value2
mysql教程>
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',
'19960725', '20060725', 1234.56, 'Toronto',
'Programmer');
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',
'19760321', '19860221', 6661.78, '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',
'19781212', '19900315', 6544.78, '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,'Celia', 'Rice',
'19821024', '19990421', 2344.78, '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',
'19840115', '19980808', 2334.78,
'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',
'19870730', '19960104', 4322.78,'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',
'19901231', '19980212', 7897.78,'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',
'19960917', '20020415', 1232.78,'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
| 6661.78 | Vancouver | Tester |
| 3 | James | Smith | 1978-12-12 | 1990-03-15
| 6544.78 | Vancouver | Tester |
| 4 | Celia | Rice | 1982-10-24 | 1999-04-21
| 2344.78 | Vancouver | Manager |
| 5 | Robert | Black | 1984-01-15 | 1998-08-08
| 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
| 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
| 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+-----------
-+---------+-----------+-------------+
2 rows in set (0.02 sec)
在兩個字符串中間的記錄
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
| 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
| 1232.78 | Vancouver | Tester |
+------+------------+-----------+------------+-----------
-+---------+-----------+-------------+
如果要判斷不在兩者之間用not between x and y 就KO了，看個簡
單的查詢語句
mysql> SELECT Name, InStock, OnOrder, Reserved
-> FROM Topic
-> WHERE (InStock+OnOrder-Reserved) NOT BETWEEN 10 AND
20
-> ORDER BY Name;
重要事項：不同的數據庫教程對 BETWEEN...AND 操作符的處理方式是
有差異的。某些數據庫會列出介於 "Adams" 和 "Carter" 之間的
人，但不包括 "Adams" 和 "Carter" ；某些數據庫會列出介於
"Adams" 和 "Carter" 之間並包括 "Adams" 和 "Carter" 的人；
而另一些數據庫會列出介於 "Adams" 和 "Carter" 之間的人，包
括 "Adams" ，但不包括 "Carter"
