MYSQL

来源：互联网发布：mac airplay显示关闭编辑：程序博客网时间：2024/06/16 22:34

MySQL提供标准的SQL模式匹配，可以使用"_"匹配单个字符，%匹配任意数量的字符(包括0个字符)。当使用模式匹配时，用LIKE和NOT 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    | 1979-08-31 | 1995-07-29 |+--------+--------+---------+------+------------+------------+2 rows in set (0.00 sec)mysql>

查询以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  |+--------+--------+---------+------+------------+-------+2 rows in set (0.00 sec)mysql>

查询名字中包含'w'的宠物

mysql> select * from pet where name like '%w%';+----------+-------+---------+------+------------+------------+| name     | owner | species | sex  | birth      | death      |+----------+-------+---------+------+------------+------------+| Whistler | Gwen  | bird    | NULL | 0000-00-00 | NULL       || Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       || Bowser   | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |+----------+-------+---------+------+------------+------------+3 rows in set (0.01 sec)mysql>

查询名字中精确包含5个字符的宠物，使用5个'_'匹配5个字符

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  |+-------+--------+---------+------+------------+-------+2 rows in set (0.00 sec)mysql>

MySQL提供的另一种类型的模式匹配用的是扩展的正则表达式。当使用这种类型的模式匹配时，用REGEXP和NOT REGEXP操作(或RLIKE和NOT RLIKE)

下面列表描述了扩展的正则表达式的一些使用

"."匹配任意的单个字符
"[...]"匹配在括号中的任意字符。如[abc]匹配“a", "b" 或"c"。匹配一个范围的字符，用一个连字符"-"，[a-z]匹配任意字母，而[0-9]匹配任意数字。
"*"匹配0或多个其之前的实例。如"x*"匹配任意数量的"x"字符，"[0-9]*"匹配任意数量的数字，而".*"匹配任意数量的字符。
如果被测值的任意位置能与模式匹配，则REGEXP模式匹配成功（LIKE类型的模式匹配则要求整个被测值都匹配模式成功）
为使一个模式从开头或结尾匹配，使用"^"在模式开头或使用"$"在模式结尾。

使用REGEXP重写之前的查询语句

查询以"b"开头的名字的宠物

mysql> select * from pet where name rlike '^b';+--------+--------+---------+------+------------+------------+| name   | owner  | species | sex  | birth      | death      |+--------+--------+---------+------+------------+------------+| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       || Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |+--------+--------+---------+------+------------+------------+2 rows in set (0.00 sec)mysql> select * from pet where name regexp '^b';+--------+--------+---------+------+------------+------------+| name   | owner  | species | sex  | birth      | death      |+--------+--------+---------+------+------------+------------+| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       || Bowser | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |+--------+--------+---------+------+------------+------------+2 rows in set (0.00 sec)mysql>

查询以fy结尾的名字的宠物

mysql> select * from pet where name rlike 'fy$';+--------+--------+---------+------+------------+-------+| name   | owner  | species | sex  | birth      | death |+--------+--------+---------+------+------------+-------+| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  || Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |+--------+--------+---------+------+------------+-------+2 rows in set (0.00 sec)mysql> select * from pet where name regexp 'fy$';+--------+--------+---------+------+------------+-------+| name   | owner  | species | sex  | birth      | death |+--------+--------+---------+------+------------+-------+| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  || Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |+--------+--------+---------+------+------------+-------+2 rows in set (0.00 sec)mysql>

如果希望REGEXP比较是大小写敏感的，使用BINARY关键字。以下查询仅匹配小写字母"b"在名字的开头。

mysql> select * from pet where name rlike binary '^b';Empty set (0.00 sec)mysql> select * from pet where name regexp binary '^b';Empty set (0.00 sec)mysql>

查询名字中包含'w'的宠物

mysql> select * from pet where name rlike 'w';+----------+-------+---------+------+------------+------------+| name     | owner | species | sex  | birth      | death      |+----------+-------+---------+------+------------+------------+| Whistler | Gwen  | bird    | NULL | 0000-00-00 | NULL       || Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       || Bowser   | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |+----------+-------+---------+------+------------+------------+3 rows in set (0.00 sec)mysql> select * from pet where name regexp 'w';+----------+-------+---------+------+------------+------------+| name     | owner | species | sex  | birth      | death      |+----------+-------+---------+------+------------+------------+| Whistler | Gwen  | bird    | NULL | 0000-00-00 | NULL       || Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       || Bowser   | Diane | dog     | m    | 1979-08-31 | 1995-07-29 |+----------+-------+---------+------+------------+------------+3 rows in set (0.00 sec)mysql>

查询名字中精确包含5个字符的宠物

mysql> select * from pet where name rlike '^.....$';+-------+--------+---------+------+------------+-------+| name  | owner  | species | sex  | birth      | death |+-------+--------+---------+------+------------+-------+| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  || Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |+-------+--------+---------+------+------------+-------+2 rows in set (0.00 sec)mysql> select * from pet where name regexp '^.....$';+-------+--------+---------+------+------------+-------+| name  | owner  | species | sex  | birth      | death |+-------+--------+---------+------+------------+-------+| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  || Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |+-------+--------+---------+------+------------+-------+2 rows in set (0.00 sec)mysql>

也可以使用{n}（重复n次）操作

mysql> select * from pet where name rlike '^.{5}$';+-------+--------+---------+------+------------+-------+| name  | owner  | species | sex  | birth      | death |+-------+--------+---------+------+------------+-------+| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  || Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |+-------+--------+---------+------+------------+-------+2 rows in set (0.00 sec)mysql> select * from pet where name regexp '^.{5}$';+-------+--------+---------+------+------------+-------+| name  | owner  | species | sex  | birth      | death |+-------+--------+---------+------+------------+-------+| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  || Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |+-------+--------+---------+------+------------+-------+2 rows in set (0.00 sec)mysql>

0 0