SQL语句LIKE

LIKE操作符

LIKE操作符用于在WHERE中搜索列中的指定模式。

SQL LIKE操作符语句

SELECT COLUMN_NAME(S) FROM TABLE_NAME WHERE COLUMN_NAME LIKE PATTERN

---

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

---

LIKE操作符实例

• 例子
  我们希望从CAR表中选取去以L(VIN)开头的数据：
  使用下面SQL语句

SELECT * FROM CAR WHERE VIN LIKE 'L%'

提示: “%”可用于定义通配符(模式中缺少的字母)
结果

VIN	BRAND	MODEL	PRICE	PRODUCTION_YEAR
LJCPCBLCX14500264	Ford	Focus	8000	2005
LP859CCSRH5555963	Toyota	Avensis	12000.00	2009
LP859CCSRH5555962	Opel	Punto	13000.00	2012

---

• 例子 2
  我们希望从CAR表中选取出VIN以6结尾的数据

SELECT * FROM CAR WHERE VIN LIKE '%6'

结果

VIN	BRAND	MODEL	PRICE	PRODUCTION_YEAR
KLATF08Y1VB363636	Volkswagen	Golf	3000	1992

---

• 例子 3
  我们希望从CAR表中选出包含VIN“36”的数据

SELECT * FROM CAR WHERE VIN LIKE '%36%'

结果

VIN	BRAND	MODEL	PRICE	PRODUCTION_YEAR
KLATF08Y1VB363636	Volkswagen	Golf	3000	1992

---

• 例子4
  通过使用not关键字，我们可以从car表中选取不包含“36”的数据
  第一种SQL语句:

SELECT * FROM CAR WHERE VIN NOT LIKE '%36'

第二种SQL语句:

SELECT * FROM CAR WHERE VIN NOT IN (SELECT VIN FROM CAR WHERE VIN LIKE '%36%')

第三种SQL语句:

SELECT * FROM CAR A WHERE NOT EXISTS (SELECT 1 FROM CAR B WHERE B.VIN LIKE '%36%' AND A.VIN=B.VIN )

结果:

VIN	BRAND	MODEL	PRICE	PRODUCTION_YEAR
LJCPCBLCX14500264	Ford	Focus	8000	2005
WPOZZZ79ZTS372128	Ford	Fusion	10000	2008
JF1BR93D7BG498281	Toyota	Avensis	10000	1999
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

---