正则表达式

正则表达式

导语

正则表达式是用某种模式去匹配一类字符串的一个方式。

正则表达式的功能非常强大，使用正则表达式可以灵活地设置字符串匹配的条件，而且，Java语言、C#语言、PHP语言、数据库查询和Shell脚本语言都可以使用正则表达式。

一、重要模式字符

模式字符表

正则表达式的模式字符	含义
^word	待搜索的字符串(word)在行首或字符串开始的部分
word$	待搜索的字符串(word)在行尾或字符串结束的部分
.	代表字符串中任意一个字符，包括回车和换行
c*	重复0个或多个前一个重复字符(c)
c+	重复1个或多个前一个重复字符(c)
[字符集合]	匹配“字符集合”中的任何一个字符
[^字符集合]	不匹配“字符集合”中的任何一个字符
word{N}	字符串(word)出现N次
word{M,N}	字符串(word)出现至少M次，最多N次

注：在Shell脚本中使用正则表达式时，如输入{N}必须先输入转义符。如 \{N \}。

二、Shell部分实例

1.利用*来搜索字符串

bash-3.2# cat express.txt 
The wind gently murmured,
puffed and sometimes sighed like a summer breeze lifting the leaves along the livelong night.
The cat was curling up quiet in her bed.
The mouse was sleeping carefree in his gallery.
The dog was lying swwet on the sofa.
Even the earth itself was sleeping in tranquility,exept that some house door faintly creaked upon its hinge,
telling us a remote inward warmth.
Nature was at her midnight work with feathery flakes whirling down in the wind,
as if she was showering her silvery seeds over the fields.
Finally I woke up.The floor creaked under my feet as I moved towards the window.
The snow as warm as cotton was lying calmly upon the window sill and the stillness of the morning was extremely impressive.
I moved on,
treading briskly along the road,
the dry and crisped snow crunching under my feet.
bash-3.2# grep 'sle*p' express.txt 
The mouse was sleeping carefree in his gallery.
Even the earth itself was sleeping in tranquility,exept that some house door faintly creaked upon its hinge,

2.行首和行尾

利用^和$来搜索行首和行尾

bash-3.2# grep 'night.$' express.txt 
puffed and sometimes sighed like a summer breeze lifting the leaves along the livelong night.

bash-3.2# grep '^The dog' express.txt 
The dog was lying swwet on the sofa.

三、MySQL操作

SQL语句：

SELECT * FROM info WHERE name REGEXP '^word';

1.行首和行尾

通过^和$查询

mysql> SELECT * FROM work_info;
+----+--------+-----+------+-----------------+---------+
| id | name   | sex | age  | address         | tel     |
+----+--------+-----+------+-----------------+---------+
|  1 | 张三   | M   |   18 | 北京海淀区      | 1234567 |
|  2 | 李四   | F   |   18 | 北京昌平区      | 2345678 |
|  3 | 王五   | M   |   17 | 湖南永州市      | 2345679 |
|  4 | 赵六   | F   |   25 | 辽宁阜新市      | 4556780 |
+----+--------+-----+------+-----------------+---------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM work_info WHERE address REGEXP '^北京';
+----+--------+-----+------+-----------------+---------+
| id | name   | sex | age  | address         | tel     |
+----+--------+-----+------+-----------------+---------+
|  1 | 张三   | M   |   18 | 北京海淀区      | 1234567 |
|  2 | 李四   | F   |   18 | 北京昌平区      | 2345678 |
+----+--------+-----+------+-----------------+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM work_info WHERE age REGEXP '8$';
+----+--------+-----+------+-----------------+---------+
| id | name   | sex | age  | address         | tel     |
+----+--------+-----+------+-----------------+---------+
|  1 | 张三   | M   |   18 | 北京海淀区      | 1234567 |
|  2 | 李四   | F   |   18 | 北京昌平区      | 2345678 |
+----+--------+-----+------+-----------------+---------+
2 rows in set (0.00 sec)

2.利用字符集合[]来查询

查询出现在[]集合中和字符。如下，当tel中出现0或1时则打印

mysql> SELECT * FROM work_info WHERE tel REGEXP '[10]';
+----+--------+-----+------+-----------------+---------+
| id | name   | sex | age  | address         | tel     |
+----+--------+-----+------+-----------------+---------+
|  1 | 张三   | M   |   18 | 北京海淀区      | 1234567 |
|  4 | 赵六   | F   |   25 | 辽宁阜新市      | 4556780 |
+----+--------+-----+------+-----------------+---------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM work_info WHERE tel REGEXP '[10]'
-> AND address REGEXP '市';
+----+--------+-----+------+-----------------+---------+
| id | name   | sex | age  | address         | tel     |
+----+--------+-----+------+-----------------+---------+
|  4 | 赵六   | F   |   25 | 辽宁阜新市      | 4556780 |
+----+--------+-----+------+-----------------+---------+
1 row in set (0.00 sec)

---

参考：
鸟哥的私房菜
MySQL入门很简单