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 |
