模式匹配(like)
MySQL提供了标准的SQL模式匹配以及基于扩展的正则表达式的模式匹配形式,该正则表达式类似于vi,grep和sed之类的Unix实用程序使用的扩展正则表达式。
SQL模式匹配使您可以_
用来匹配任何单个字符并%
匹配任意数量的字符(包括零个字符)。在MySQL中,默认情况下,SQL模式不区分大小写。这里显示一些示例。请勿使用=
或<>
使用SQL模式时。改用LIKE
或NOT LIKE
比较运算符。
查找以b
以下内容开头的名称:
mysql>SELECT *FROM petWHERE name LIKE 'b%'; +-------- +-------- +--------- +------ +------------ +------------ + | name | owner | species | sex | birth | death | +-------- +-------- +--------- +------ +------------ +------------ + | Buffy | Harold | dog | f | 1989 -05 -13 | NULL | | Bowser | Diane | dog | m | 1989 -08 -31 | 1995 -07 -29 | +-------- +-------- +--------- +------ +------------ +------------ +
查找以结尾的名称fy
:
mysql>SELECT *FROM petWHERE name LIKE '%fy'; +-------- +-------- +--------- +------ +------------ +------- + | name | owner | species | sex | birth | death | +-------- +-------- +--------- +------ +------------ +------- + | Fluffy | Harold | cat | f | 1993 -02 -04 | NULL | | Buffy | Harold | dog | f | 1989 -05 -13 | NULL | +-------- +-------- +--------- +------ +------------ +------- +
查找包含的名称w
:
mysql>SELECT *FROM petWHERE name LIKE '%w%'; +---------- +------- +--------- +------ +------------ +------------ + | name | owner | species | sex | birth | death | +---------- +------- +--------- +------ +------------ +------------ + | Claws | Gwen | cat | m | 1994 -03 -17 | NULL | | Bowser | Diane | dog | m | 1989 -08 -31 | 1995 -07 -29 | | Whistler | Gwen | bird | NULL | 1997 -12 -09 | NULL | +---------- +------- +--------- +------ +------------ +------------ +
要查找正好包含五个字符的名称,请使用_
模式字符的五个实例:
mysql>SELECT *FROM petWHERE name LIKE '_____'; +------- +-------- +--------- +------ +------------ +------- + | name | owner | species | sex | birth | death | +------- +-------- +--------- +------ +------------ +------- + | Claws | Gwen | cat | m | 1994 -03 -17 | NULL | | Buffy | Harold | dog | f | 1989 -05 -13 | NULL | +------- +-------- +--------- +------ +------------ +------- +