• 首页
  • css3教程
  • html5教程
  • jQuery手册
  • vue手册
  • php手册
  • MySQL手册
  • apache手册
  • redis手册
  • 模式匹配(like)

    MySQL提供了标准的SQL模式匹配以及基于扩展的正则表达式的模式匹配形式,该正则表达式类似于vigrepsed之类的Unix实用程序使用的扩展正则表达式。

    SQL模式匹配使您可以_用来匹配任何单个字符并%匹配任意数量的字符(包括零个字符)。在MySQL中,默认情况下,SQL模式不区分大小写。这里显示一些示例。请勿使用=<>使用SQL模式时。改用LIKENOT LIKE比较运算符。

    查找以b以下内容开头的名称:

    mysql> SELECT * FROM pet WHERE 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 pet WHERE 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 pet WHERE 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 pet WHERE name LIKE '_____';
    +-------	+--------	+---------	+------	+------------	+-------	+
    | name   | owner   | species  | sex   | birth       | death  |
    +-------	+--------	+---------	+------	+------------	+-------	+
    | Claws  | Gwen    | cat      | m     | 1994 -03 -17  | NULL   |
    | Buffy  | Harold  | dog      | f     | 1989 -05 -13  | NULL   |
    +-------	+--------	+---------	+------	+------------	+-------	+