MySql official manual Study Notes 2-MySql fuzzy query and Regular Expression

MySql official manual Study Notes 2-MySql fuzzy query and regular expression links: MySql official manual Study Notes 1-MySql easy to use http://www.bkjia.com/database/201210/159522.html MySQL provides standard SQL mode matching and extended regular expression mode matching formats. SQL mode matching allows you to use "_" to match any single character, while "%" matches any number of characters (including zero characters ). In MySQL, the SQL mode is case-insensitive by default. The following are some examples. Note that = or! =; Instead, use the LIKE or not like comparison operator. Www.2cto.com to find the name starting with "B": mysql> SELECT * FROM pet WHERE name LIKE 'B % '; + -------- + --------- + ------ + ------------ + | name | owner | species | sex | birth | death | + -------- + --------- + ------ + ------------ + | buffy | Harold | dog | f | 1989-05-13 | NULL | bow.| Diane | dog | m | 1989-08-31 | 1995-07-29 | + -------- + --------- + ------ + ------------ + --- --------- + Name ending with "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 | + -------- + --------- + ------ + ------------ + ------- + to find out contains "w" Name: mysql> SELECT * FROM pet WHERE name LIKE '% w % '; + ---------- + ------- + --------- + ------ + ------------ + | name | owner | species | sex | birth | death | + ---------- + ------- + --------- + ------ + ------------ + | claws | Gwen | cat | m | 1994-03-17 | NULL | bow.| Diane | dog | m | 1989-08-31 | 1995-07-29 | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | + ---------- + ------- +- -------- + ------ + ------------ + Www.2cto.com to find the name that exactly contains five characters, use the "_" mode character: 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 | + ------- + ----- --- + --------- + ------ + ------------ + ------- + Other types of pattern matching provided by MySQL are extended regular expressions. When you perform a match test on this type of pattern, use the REGEXP and not regexp operators (or RLIKE and not rlike, which are synonyms ). Some Characters of the extended regular expression are: www.2cto.com · '.' to match any single character. · The character class "[...]" matches any character in square brackets. For example, "[abc]" matches "a", "B", or "c ". To name the character range, use a hyphen (-). "[A-z]" matches any letter, and "[0-9]" matches any number. · "*" Matches zero or multiple characters before it. For example, "x *" matches any number of "x" characters, "[0-9] *" matches any number, and ". * "matches any number of characters. If the REGEXP pattern matches any part of the tested value, the pattern matches (different from the LIKE pattern match. The pattern matches only the entire value ). To locate a pattern so that it must match the start or end of the tested value, use "^" at the start of the pattern or "$" at the end of the pattern ". To demonstrate how the extended regular expression works, use REGEXP to rewrite the LIKE Query shown above: to find the name starting with "B", use "^" to match the start of the name: mysql> SELECT * FROM pet WHERE name REGEXP '^ B '; + -------- + --------- + ------ + ------------ + | name | owner | species | sex | birth | death | + -------- + --------- + ------ + ------------ + | buffy | Harold | dog | f | 1989-05-13 | NULL | bow.| Diane | dog | m | 1989-08-31 | + -------- + -------- + --------- + ------ + ------------ + If you want to force the REGEXP to be case sensitive, use the BINARY keyword to convert one of the strings to a BINARY string. This query only matches the lowercase 'B' of the first letter of the name '. Www.2cto.com mysql> SELECT * FROM pet WHERE name regexp binary '^ B'; to find the name ending with "fy", use "$" to match the end Of the name: 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 | + -------- + --------- + ------ + ------------ + ------- + to find the name containing a "w", use the following query: mysql> SELECT * FROM pet WHERE name REGEXP 'W '; + ---------- + ------- + --------- + ------ + ------------ + | name | owner | species | sex | birth | death | + ---------- + ------- + --------- + ------ + ------------ + | claws | Gwen | cat | m | NULL | Boane | Diane | dog | m | 1995 -07-29 | Whistler | Gwen | bird | NULL | 1997-12-09 | NULL | + ---------- + ------- + --------- + ------ + ------------ + since a regular expression appears in the value anywhere, if the mode matches, you do not have to place a wildcard on both sides of the mode in the previous query to make it match the entire value, just as if you were using an SQL mode. To find a name that contains exactly five characters, use "^" and "$" to match the start and end of the name and five ". "The instance is between the two: www.2cto.com 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 | + ------- + -------- + --------- + ------ + ---------- + ------- + you also you can use the "{n}" "repeated n times" operator to rewrite the previous query: 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 | + ------- + -------- + --------- + ------ + ---------- + ------- + www.2cto.com