六度外博/歪博

//(0).全表记录
mysql> select * from computer_stu;
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    85 |
| 1002 | Tom  |    91 |
| 1003 | Jim  |    87 |
| 1004 | Aric |    77 |
| 1005 | Lucy |    65 |
| 1006 | Andy |    99 |
| 1007 | Ada  |    85 |
| 1008 | Jeck |    70 |
+------+------+-------+
8 rows in set (0.00 sec)
 
//(1).查询以特定字符或字符串开头的记录
mysql> select * from computer_stu where name regexp '^L';
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    85 |
| 1005 | Lucy |    65 |
+------+------+-------+
2 rows in set (0.00 sec)
 
//(2).查询以特定字符或字符串结尾的记录
mysql> select * from computer_stu where name regexp 'y$';
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    85 |
| 1005 | Lucy |    65 |
| 1006 | Andy |    99 |
+------+------+-------+
3 rows in set (0.00 sec)
 
//(3).用符号"."来代替字符串中的任意一个字符
mysql> select * from computer_stu where name regexp '^L..y$';
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    85 |
| 1005 | Lucy |    65 |
+------+------+-------+
2 rows in set (0.00 sec)
//其中^L表示以字母L开头，两个"."表示两个任意字符，y$表示以字母y结尾
 
//(4).匹配指定字符中的任意一个
mysql> select * from computer_stu where name regexp '[ci]';
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    85 |
| 1003 | Jim  |    87 |
| 1004 | Aric |    77 |
| 1005 | Lucy |    65 |
| 1008 | Jeck |    70 |
+------+------+-------+
5 rows in set (0.00 sec)
//使用方括号可以指定集合的区间。[a-z]表示a~z的所有字母(mysql字段大小写不敏感)，[0-9]表示从0~9的所有数字，[a-z0-9]表示包含所有的字母和数字。
 
//(5).匹配指定字符以外的字符
mysql> select * from computer_stu where name regexp '[^a-w0-9]';
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    85 |
| 1005 | Lucy |    65 |
| 1006 | Andy |    99 |
+------+------+-------+
3 rows in set (0.00 sec)
 
//(6).匹配指定字符串
mysql> select * from computer_stu where name regexp 'il|dy';
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    85 |
| 1006 | Andy |    99 |
+------+------+-------+
2 rows in set (0.00 sec)
 
//(7).使用"*"和"+"来匹配多个字符
mysql> select * from computer_stu where name regexp 'i*m';
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1002 | Tom  |    91 |
| 1003 | Jim  |    87 |
+------+------+-------+
2 rows in set (0.00 sec)
mysql> select * from computer_stu where name regexp 'i+m';
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1003 | Jim  |    87 |
+------+------+-------+
1 row in set (0.00 sec)
 
//(8).使用{M}或者{M,N}来制定字符串连续出现的次数
mysql> insert into computer_stu values(1009,'aaabb',85),(1010,'aabbc',79),(1011,'abccc',98);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> select * from computer_stu where name regexp 'a{3}';
+------+-------+-------+
| id   | name  | score |
+------+-------+-------+
| 1009 | aaabb |    85 |
+------+-------+-------+
1 row in set (0.00 sec)
mysql> select * from computer_stu where name regexp 'a{2,3}';
+------+-------+-------+
| id   | name  | score |
+------+-------+-------+
| 1009 | aaabb |    85 |
| 1010 | aabbc |    79 |
+------+-------+-------+
2 rows in set (0.00 sec)