MYSQL_语法 7 -----正则表达式 （9）

REGEXP：正则表达式查询

查询 dept_name 字段以字母“C”开头的记录

mysql> SELECT *
    -> FROM tb_departments
    -> WHERE dept_name REGEXP '^C';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_type | dept_call |
+---------+-----------+-----------+-----------+
|       1 | Computer  | A         | 11111     |
|       2 | Chinese   | A         | 33333     |
|       4 | Computer  | B         | 11111     |
|       5 | Computer  | B         | 22222     |
|       7 | Computer  | B         | 33333     |
|       9 | Chinese   | B         | 11111     |
+---------+-----------+-----------+-----------+
6 rows in set (0.02 sec)

查询 dept_name 字段以“Ch”开头的记录

mysql> SELECT *
    -> FROM tb_departments
    -> WHERE dept_name REGEXP '^Ch';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_type | dept_call |
+---------+-----------+-----------+-----------+
|       2 | Chinese   | A         | 33333     |
|       9 | Chinese   | B         | 11111     |
+---------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

查询 dept_name 字段以字母“y”结尾的记录

mysql> SELECT *
    -> FROM tb_departments
    -> WHERE dept_name REGEXP 'y$';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_type | dept_call |
+---------+-----------+-----------+-----------+
|       8 | Economy   | B         | 44444     |
|      10 | History   | B         | 55555     |
+---------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

查询 dept_name 字段值包含字母“o”与字母“y”，且两个字母之间只有一个字母的记录，
用符号“.”代替字符串中的任意一个字符

mysql> SELECT *
    -> FROM tb_departments
    ->  WHERE dept_name REGEXP 'o.y';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_type | dept_call |
+---------+-----------+-----------+-----------+
|       8 | Economy   | B         | 44444     |
|      10 | History   | B         | 55555     |
+---------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

使用“”和“+”来匹配多个字符*
星号“”匹配前面的字符任意多次，包括 0 次。加号“+”匹配前面的字符至少一次。*

mysql> SELECT *
    -> FROM tb_departments
    -> WHERE dept_name REGEXP '^Ch*';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_type | dept_call |
+---------+-----------+-----------+-----------+
|       1 | Computer  | A         | 11111     |
|       2 | Chinese   | A         | 33333     |
|       4 | Computer  | B         | 11111     |
|       5 | Computer  | B         | 22222     |
|       7 | Computer  | B         | 33333     |
|       9 | Chinese   | B         | 11111     |
+---------+-----------+-----------+-----------+
6 rows in set (0.01 sec)

mysql> SELECT *
    ->  FROM tb_departments
    ->  WHERE dept_name REGEXP '^Ch+';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_type | dept_call |
+---------+-----------+-----------+-----------+
|       2 | Chinese   | A         | 33333     |
|       9 | Chinese   | B         | 11111     |
+---------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

匹配指定字符串
正则表达式可以匹配指定字符串，只要这个字符串在查询文本中即可，若要匹配多个字符串，则多个字符串之间使用分隔符“|”隔开。

mysql> SELECT *
    -> FROM tb_departments
    ->  WHERE dept_name REGEXP 'in';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_type | dept_call |
+---------+-----------+-----------+-----------+
|       2 | Chinese   | A         | 33333     |
|       9 | Chinese   | B         | 11111     |
+---------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

mysql> SELECT *
    -> FROM tb_departments
    -> WHERE dept_name REGEXP 'in|on';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_type | dept_call |
+---------+-----------+-----------+-----------+
|       2 | Chinese   | A         | 33333     |
|       8 | Economy   | B         | 44444     |
|       9 | Chinese   | B         | 11111     |
+---------+-----------+-----------+-----------+
3 rows in set (0.00 sec)

匹配指定字符串中的任意一个
方括号“[]”指定一个字符集合，只匹配其中任何一个字符，即为所查找的文本。

mysql> SELECT *
    ->  FROM tb_departments
    ->  WHERE dept_name REGEXP '[io]';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_type | dept_call |
+---------+-----------+-----------+-----------+
|       1 | Computer  | A         | 11111     |
|       2 | Chinese   | A         | 33333     |
|       4 | Computer  | B         | 11111     |
|       5 | Computer  | B         | 22222     |
|       7 | Computer  | B         | 33333     |
|       8 | Economy   | B         | 44444     |
|       9 | Chinese   | B         | 11111     |
|      10 | History   | B         | 55555     |
+---------+-----------+-----------+-----------+
8 rows in set (0.00 sec)

匹配指定字符以外的字符

mysql> SELECT *
    -> FROM tb_departments
    ->  WHERE dept_name REGEXP '[^a-t]';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_type | dept_call |
+---------+-----------+-----------+-----------+
|       1 | Computer  | A         | 11111     |
|       4 | Computer  | B         | 11111     |
|       5 | Computer  | B         | 22222     |
|       7 | Computer  | B         | 33333     |
|       8 | Economy   | B         | 44444     |
|      10 | History   | B         | 55555     |
+---------+-----------+-----------+-----------+
6 rows in set (0.00 sec)