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

---