MySQL pattern matching: regexp and like usage

Like requires that the entire data be matched, and regexp only needs to be partially matched.
That is, with like, all the content of this field must satisfy the condition, and regexp only needs to have any one fragment to satisfy.

MySQL provides standard SQL pattern matching (like) and an extended regular expression pattern matching format (regexp) based on UNIX utilities such as VI, grep, and sed.
SQL pattern matching allows you to match any single character with "_", while "%" matches any number of characters (including 0 characters). In MySQL, the SQL schema defaults to ignoring the case. Some examples are shown below. Note that when you use SQL mode, you cannot use = or!= and use like or not to compare operators.

To find the name that begins with "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
+--------+--------+---------+------+------------+------------+
To find the 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 the name that contains a "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
+----------+-------+---------+------+------------+------------+
To find names that contain exactly 5 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
+-------+--------+---------+------+------------+-------+

Another type of match is based on regular expressions. When you match this pattern to a test, use the regexp and not regexp operators (or rlike and not rlike, which are synonyms).

“.” matches any single character.
A character class "[...]" Matches any character within the square brackets. For example, "[ABC]" matches "a", "B", or "C". To name a range of characters, use a "-". "[A-z]" matches any lowercase letter, and "[0-9]" matches any number.
"*" matches 0 or more things in front of it. For example, "x*" matches any number of "X" characters, "[0-9]*" matches any number of numbers, and ". *" matches any number of anything.
Regular expressions are case-sensitive, but if you want to, you can use a character class to match two ways of writing. For example, "[AA]" matches either lowercase or uppercase "a" and "[A-za-z]" matches any of the letters in both ways.
If it appears anywhere in the tested value, the pattern matches (as long as they match the entire value and the SQL pattern matches).
To locate a pattern so that it must match the start or end of the tested value, use "^" at the beginning of the pattern or at the end of the pattern with "$".
To illustrate how extended regular expressions work, the like query shown below uses RegExp overrides:
To find the name that begins with "B", use "^" to match the start of the first name and "[BB]" to match lowercase or uppercase "B":
Mysql> select * FROM pet WHERE name REGEXP "^[BB]";
+--------+--------+---------+------+------------+------------+
Name owner species sex birth death
+--------+--------+---------+------+------------+------------+
Buffy Harold Dog F 1989-05-13 NULL
Bowser Diane Dog M 1989-08-31 1995-07-29
+--------+--------+---------+------+------------+------------+
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 "[WW]" to match the lowercase or uppercase "W":
Mysql> SELECT * from pet WHERE name REGEXP "[WW]";
+----------+-------+---------+------+------------+------------+
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
+----------+-------+---------+------+------------+------------+