SQL语句BETWEEN

BETWEEN操作符

操作符BETWEEN…AND会选取介于两个值之间的数据范围。这些值可以是数值、文本或者日期。
SQL BETWEEN语法

SELECT COLUMN_NAME(S) FROM TBALE_NAME WHERE COLUMN_NAME BETWEEN VALUE1 AND VALUE2

---

CAR表:

VIN	BRAND	MODEL	PRICE	PRODUCTION_YEAR
LJCPCBLCX14500264	Ford	Focus	8000	2005
WPOZZZ79ZTS372128	Ford	Fusion	10000	2008
JF1BR93D7BG498281	Toyota	Avensis	10000	1999
KLATF08Y1VB363636	Volkswagen	Golf	3000	1992
1M8GDM9AXKP042788	Volkswagen	Golf	10000	2010
1HGCM82633A004352	Volkswagen	Jetta	6000	2003
1G1YZ23J9P5800003	Fiat	Punto	6000	1999
GS723HDSAK2399002	Opel	Corsa	89000.00	2007
LP859CCSRH5555963	Toyota	Avensis	12000.00	2009
LP859CCSRH5555962	Opel	Punto	13000.00	2012

---

BETWEEN 操作符实例
如需以字母顺序显示介于’Ford’（包含）和’Opel’（不包含）之间的人，

SELECT * FROM CAR WHERE BRAND BETWEEN 'Ford' AND 'Opel'

结果:

VIN	BRAND	MODEL	PRICE	PRODUCTION_YEAR
LJCPCBLCX14500264	Ford	Focus	8000	2005
WPOZZZ79ZTS372128	Ford	Fusion	10000	2008
GS723HDSAK2399002	Opel	Corsa	89000.00	2007
LP859CCSRH5555962	Opel	Punto	13000.00	2012

---

重要事项:不同的数据库对BETWEEN…AND 操作符的处理方式是有差异的。有的数据库会列出’Ford’ 和’Opel’之间的值不包含’Ford’ ，有的数据库会列出’Ford’ 和’Opel’之间的值不包含’Opel’，有数据库的不包含’Ford’和’Opel’。

---

• 实例2
  如需要使用上面的例子显示范围之外的人 请使用NOT操作符:

SELECT * FROM CAR WHERE BRAND NOT BETWEEN 'Ford' AND 'Opel'  

结果:

VIN	BRAND	MODEL	PRICE	PRODUCTION_YEAR
JF1BR93D7BG498281	Toyota	Avensis	10000	1999
KLATF08Y1VB363636	Volkswagen	Golf	3000	1992
1M8GDM9AXKP042788	Volkswagen	Golf	10000	2010
1HGCM82633A004352	Volkswagen	Jetta	6000	2003
LP859CCSRH5555963	Toyota	Avensis	12000.00	2009