Mysql基礎教程 mysql進階

Mysql正則表達式查詢


MySQL中使用正則表達式查詢



REGEXP '匹配方式'



常用匹配方式


模式字符

含義

^

匹配字符開始部分

$

匹配字符結尾的部分

.

代表字符串中的任意一個字符,包括回車和換行

[字符集合]

匹配字符集合中的任和一個字符

[^字符集合]

匹配除了字符集合以外的任何一個字符

S1|S2|S3

匹配S1、S2、S3中的任意一個字符串

*

代表0個1或者多個其前的字符

+

代表1個或者多個其前的字符

String{N}

字符串出現N次

字符串{M,N}

字符串至少出現M次,最多N次


查詢用戶表的記錄


mysql> SELECT * FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 張三  | zhangsan | [email protected] | 1419811708 | user.jpg |     1 |   21 | 男     |
|  2 | 張三豐 | zhangsanfeng | [email protected] | 1419812708 | user.jpg | 2 |   31 | 女     |
|  3 | 章子怡  | zhangsan | [email protected] | 1419813708 | user.jpg |  3 |   43 | 男     |
|  4 | long      | long  | [email protected] | 1419814708 | user.jpg |     4 |   41 | 女     |
|  5 | ring      | ring | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男     |
|  6 | queen  | queen  | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女     |
|  8 | blek  | blek   | [email protected] | 1419818708 | user.jpg |     1 |   85 | 女     |
|  9 | rose  | rose  | [email protected] | 1419821708 | user.jpg |     2 |    9 | 男     |
| 10 | lily      | lily   | [email protected] | 1419831708 | user.jpg |     2 |   39 | 女     |
| 11 | john      | john | [email protected] | 1419841708 | user.jpg |     2 |   72 | 保密   |
| 12 | test1     | test1        | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2     | TEST2        | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
12 rows in set (0.04 sec)


^匹配字符開始的部分


查詢用戶以t開始的用戶(不區分大小寫)


mysql> SELECT * FROM cms_user WHERE username REGEXP '^t';
+----+----------+----------+-------------+------------+----------+-------+------+--------+
| id | username | password | email       | regTime    | face     | proId | age  | sex    |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
| 12 | test1    | test1    | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2    | TEST2    | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
2 rows in set (0.04 sec)


$匹配字符串結尾的部分


查詢用戶以g結尾的用戶(不區分大小寫)


mysql> SELECT * FROM cms_user WHERE username REGEXP 'g$';
+----+----------+----------+-------------+------------+----------+-------+------+------+
| id | username | password | email       | regTime    | face     | proId | age  | sex  |
+----+----------+----------+-------------+------------+----------+-------+------+------+
|  4 | long     | long     | [email protected] | 1419814708 | user.jpg |     4 |   41 | 女   |
|  5 | ring     | ring     | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男   |
+----+----------+----------+-------------+------------+----------+-------+------+------+
2 rows in set (0.00 sec)


.代表任意字符(像寫了一個%,和沒寫效果一樣)


mysql> SELECT * FROM cms_user WHERE username REGEXP '.';
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password| email | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 張三  | zhangsan | [email protected] | 1419811708 | user.jpg |     1 |   21 | 男     |
|  2 | 張三豐| zhangsanfeng | [email protected] | 1419812708 | user.jpg |  2 | 31 | 女     |
|  3 | 章子怡    | zhangsan| [email protected] | 1419813708 | user.jpg | 3 |   43 | 男     |
|  4 | long   | long| [email protected] | 1419814708 | user.jpg |     4 |   41 | 女     |
|  5 | ring | ring   03| [email protected] | 1419815708 | user.jpg |     2 |    9 | 男     |
|  6 | queen | queen  | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女     |
|  8 | blek      | blek  | [email protected] | 1419818708 | user.jpg |     1 |   85 | 女     |
|  9 | rose      | rose  | [email protected] | 1419821708 | user.jpg |     2 |    9 | 男     |
| 10 | lily      | lily   | [email protected] | 1419831708 | user.jpg |     2 |   39 | 女     |
| 11 | john      | joh  | [email protected] | 1419841708 | user.jpg |     2 |   72 | 保密   |
| 12 | test1     | test1  | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2 | TEST2  | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
12 rows in set (0.00 sec)


‘rg’中間有兩位任意字符(使用點號)


mysql> SELECT * FROM cms_user WHERE username REGEXP 'r..g';
+----+----------+----------+-------------+------------+----------+-------+------+------+
| id | username | password | email       | regTime    | face     | proId | age  | sex  |
+----+----------+----------+-------------+------------+----------+-------+------+------+
|  5 | ring     | ring     | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男   |
+----+----------+----------+-------------+------------+----------+-------+------+------+
1 row in set (0.00 sec)


‘rg’中間有兩位任意字符(使用下劃線,錯誤的)


mysql> SELECT * FROM cms_user WHERE username REGEXP 'r__g';
Empty set (0.00 sec)


‘rg’中間有兩位任意字符(使用下劃線,通過like來實現)


mysql> SELECT * FROM cms_user WHERE username LIKE 'r__g';
+----+----------+----------+-------------+------------+----------+-------+------+------+
| id | username | password | email       | regTime    | face     | proId | age  | sex  |
+----+----------+----------+-------------+------------+----------+-------+------+------+
|  5 | ring     | ring     | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男   |
+----+----------+----------+-------------+------------+----------+-------+------+------+
1 row in set (0.02 sec)


[字符集合] [lto]


查詢用戶名包含“lto”


mysql> SELECT * FROM cms_user WHERE username REGEXP '[lto]';
+----+----------+----------+-------------+------------+----------+-------+------+--------+
| id | username | password | email       | regTime    | face     | proId | age  | sex    |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
|  4 | long | long     | [email protected] | 1419814708 | user.jpg |     4 |   41 | 女     |
|  8 | blek | blek     | [email protected] | 1419818708 | user.jpg |     1 |   85 | 女     |
|  9 | rose | rose     | [email protected] | 1419821708 | user.jpg |     2 |    9 | 男     |
| 10 | lily   lily     | [email protected] | 1419831708 | user.jpg |     2 |   39 | 女     |
| 11 | john| john     | [email protected] | 1419841708 | user.jpg |     2 |   72 | 保密   |
| 12 | test1| test1    | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2    | TEST2    | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
7 rows in set (0.00 sec)


[^字符集合] [^lto]


除了字符集合中的內容


mysql> SELECT * FROM cms_user WHERE username REGEXP '[^lto]';
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password     | email       | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 張三 | zhangsan | [email protected] | 1419811708 | user.jpg |     1 |   21 | 男     |
|  2 | 張三豐 | zhangsanfeng | [email protected] | 1419812708 | user.jpg | 2 |   31 | 女     |
|  3 | 章子怡 | zhangsan| [email protected] | 1419813708 | user.jpg | 3 |   43 | 男     |
|  4 | long   | long   | [email protected] | 1419814708 | user.jpg |   4 |   41 | 女     |
|  5 | ring  | ring   | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男     |
|  6 | queen | queen | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女     |
|  8 | blek | blek   | [email protected] | 1419818708 | user.jpg |     1 |   85 | 女     |
|  9 | rose  | rose   | [email protected] | 1419821708 | user.jpg |     2 |    9 | 男     |
| 10 | lily      | lily  | [email protected] | 1419831708 | user.jpg |     2 |   39 | 女     |
| 11 | john      | john  | [email protected] | 1419841708 | user.jpg |     2 |   72 | 保密   |
| 12 | test1     | test1 | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2  | TEST2  | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
12 rows in set (0.00 sec)


[^字符集合] [^l]


除了字符集合中的內容


mysql> SELECT * FROM cms_user WHERE username REGEXP '[^l]';
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password | email  | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 張三  | zhangsan  | [email protected] | 1419811708 | user.jpg |1 |   21 | 男     |
|  2 | 張三豐| zhangsanfeng | [email protected] | 1419812708 | user.jpg |2 |   31 | 女     |
|  3 | 章子怡    | zhangsan | [email protected] | 1419813708 | user.jpg | 3 |   43 | 男     |
|  4 | long   | long  | [email protected] | 1419814708 | user.jpg |     4 |   41 | 女     |
|  5 | ring | ring   | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男     |
|  6 | queen| queen| [email protected] | 1419861708 | user.jpg |     3 |   77 | 女     |
|  8 | blek   | blek   | [email protected] | 1419818708 | user.jpg |     1 |   85 | 女     |
|  9 | rose | rose    | [email protected] | 1419821708 | user.jpg |     2 |    9 | 男     |
| 10 | lily      | lily | [email protected] | 1419831708 | user.jpg |     2 |   39 | 女     |
| 11 | john  | john    | [email protected] | 1419841708 | user.jpg |     2 |   72 | 保密   |
| 12 | test1     | test1 | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2 | TEST2  | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
12 rows in set (0.00 sec)


給用戶表添加記錄


mysql> INSERT cms_user(username,password,regTime,proId)
    -> VALUES('lll','lll',138212349,2),
    -> ('ttt','lll',138212349,2),
    -> ('ooo','lll',138212349,2);
Query OK, 3 rows affected (0.13 sec)
Records: 3  Duplicates: 0  Warnings: 0


[^字符集合] [^l]


除了字符集合中的內容


mysql> SELECT * FROM cms_user WHERE username REGEXP '[^l]';
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password | email | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 張三 | zhangsan| [email protected] | 1419811708 | user.jpg |     1 |   21 | 男     |
|  2 | 張三豐  | zhangsanfeng | [email protected] | 1419812708 | user.jpg | 2 | 31 | 女     |
|  3 | 章子怡 | zhangsan  | [email protected] | 1419813708 | user.jpg |  3 |  43 | 男     |
|  4 | long      | long | [email protected] | 1419814708 | user.jpg | 4 |   41 | 女     |
|  5 | ring   | ring   | [email protected] | 1419815708 | user.jpg |  2 |    9 | 男     |
|  6 | queen   | queen  | [email protected] | 1419861708 | user.jpg |3 |   77 | 女     |
|  8 | blek      | blek  | [email protected] | 1419818708 | user.jpg |     1 |   85 | 女  |
|  9 | rose      | rose | [email protected] | 1419821708 | user.jpg |     2 |    9 | 男     |
| 10 | lily      | lily  | [email protected] | 1419831708 | user.jpg |     2 |   39 | 女     |
| 11 | john      | john  | [email protected] | 1419841708 | user.jpg |     2 |   72 | 保密   |
| 12 | test1     | test1 | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2     | TEST2 | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
| 15 | ttt       | lll | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
| 16 | ooo       | lll  | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
14 rows in set (0.00 sec)


[^字符集合] [^lto]


除了字符集合中的內容


mysql> SELECT * FROM cms_user WHERE username REGEXP '[^lto]';
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password | email       | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 張三  | zhangsan | [email protected] | 1419811708 | user.jpg |     1 |   21 | 男     |
|  2 | 張三豐| zhangsanfeng | [email protected] | 1419812708 | user.jpg |     2 |   31 | 女     |
|  3 | 章子怡| zhangsan| [email protected] | 1419813708 | user.jpg |     3 |   43 | 男     |
|  4 | long   | long | [email protected] | 1419814708 | user.jpg |     4 |   41 | 女     |
|  5 | ring | ring   | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男     |
|  6 | queen | queen | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女     |
|  8 | blek   | blek   | [email protected] | 1419818708 | user.jpg |     1 |   85 | 女     |
|  9 | rose | rose      | [email protected] | 1419821708 | user.jpg |     2 |    9 | 男     |
| 10 | lily      | lily| [email protected] | 1419831708 | user.jpg |     2 |   39 | 女     |
| 11 | john      | john  | [email protected] | 1419841708 | user.jpg |     2 |   72 | 保密   |
| 12 | test1     | test1 | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2     | TEST2 | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
12 rows in set (0.00 sec)


[字符集合] [a-k]


查詢用戶名集合的范圍內的都可以查詢


mysql> SELECT * FROM cms_user WHERE username REGEXP '[a-k]';
+----+----------+----------+-------------+------------+----------+-------+------+--------+
| id | username | password | email       | regTime    | face     | proId | age  | sex    |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
|  4 | long     | long  | [email protected] | 1419814708 | user.jpg |     4 |   41 | 女     |
|  5 | ring     | ring | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男     |
|  6 | queen    | queen | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女     |
|  8 | blek     | blek  | [email protected] | 1419818708 | user.jpg |     1 |   85 | 女     |
|  9 | rose     | rose  | [email protected] | 1419821708 | user.jpg |     2 |    9 | 男     |
| 10 | lily     | lily  | [email protected] | 1419831708 | user.jpg |     2 |   39 | 女     |
| 11 | john     | john | [email protected] | 1419841708 | user.jpg |     2 |   72 | 保密   |
| 12 | test1    | test1 | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2    | TEST2 | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
9 rows in set (0.00 sec)


[^字符集合] [^a-k]


查詢用戶名不包含集合的范圍


mysql> SELECT * FROM cms_user WHERE username REGEXP '[^a-k]';
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password     | email | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 張三      | zhangsan| [email protected] | 1419811708 | user.jpg | 1 |   21 | 男     |
|  2 | 張三豐 | zhangsanfeng | [email protected] | 1419812708 | user.jpg | 2 |   31 | 女     |
|  3 | 章子怡| zhangsan | [email protected] | 1419813708 | user.jpg |     3 |   43 | 男     |
|  4 | long      | long| [email protected] | 1419814708 | user.jpg |     4 |   41 | 女     |
|  5 | ring      | ring | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男     |
|  6 | queen  | queen | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女     |
|  8 | blek      | blek  | [email protected] | 1419818708 | user.jpg |     1 |   85 | 女     |
|  9 | rose      | rose | [email protected] | 1419821708 | user.jpg |     2 |    9 | 男     |
| 10 | lily      | lily  | [email protected] | 1419831708 | user.jpg |     2 |   39 | 女     |
| 11 | john | john | [email protected] | 1419841708 | user.jpg |     2 |   72 | 保密   |
| 12 | test1   | test1 | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2 | TEST2  | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
| 14 | lll       | lll  | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
| 15 | ttt       | lll  | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
| 16 | ooo       | lll | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
15 rows in set (0.00 sec)


[^字符集合] [^a-m]


查詢用戶名不包含集合的范圍


mysql> SELECT * FROM cms_user WHERE username REGEXP '[^a-m]';
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password | email  | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 張三  | zhangsan | [email protected] | 1419811708 | user.jpg |     1 |   21 | 男     |
|  2 | 張三豐| zhangsanfeng | [email protected] | 1419812708 | user.jpg |     2 |   31 | 女     |
|  3 | 章子怡| zhangsan| [email protected] | 1419813708 | user.jpg |     3 |   43 | 男     |
|  4 | long      | long  | [email protected] | 1419814708 | user.jpg |     4 |   41 | 女     |
|  5 | ring  | ring   | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男     |
|  6 | queen  | queen  | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女     |
|  9 | rose      | rose  | [email protected] | 1419821708 | user.jpg |     2 |    9 | 男     |
| 10 | lily      | lily    | [email protected] | 1419831708 | user.jpg |     2 |   39 | 女     |
| 11 | john      | john  | [email protected] | 1419841708 | user.jpg |     2 |   72 | 保密   |
| 12 | test1     | test1  | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2     | TEST2 | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
| 15 | ttt       | lll    | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
| 16 | ooo       | lll   | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
13 rows in set (0.00 sec)


|匹配任一字符'ng|qu'


mysql> SELECT * FROM cms_user WHERE username REGEXP 'ng|qu';
+----+----------+----------+-------------+------------+----------+-------+------+------+
| id | username | password | email       | regTime    | face     | proId | age  | sex  |
+----+----------+----------+-------------+------------+----------+-------+------+------+
|  4 | long     | long     | [email protected] | 1419814708 | user.jpg |     4 |   41 | 女   |
|  5 | ring     | ring     | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男   |
|  6 | queen    | queen  | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女   |
+----+----------+----------+-------------+------------+----------+-------+------+------+
3 rows in set (0.02 sec)


多個字符串以|來連接


mysql> SELECT * FROM cms_user WHERE username REGEXP 'ng|qu|te';
+----+----------+----------+-------------+------------+----------+-------+------+--------+
| id | username | password | email  | regTime    | face     | proId | age  | sex    |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
|  4 | long     | long | [email protected] | 1419814708 | user.jpg |     4 |   41 | 女     |
|  5 | ring     | ring | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男     |
|  6 | queen    | queen | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女     |
| 12 | test1    | test1  | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2    | TEST2  | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
5 rows in set (0.00 sec)


查看用戶表的記錄


mysql> SELECT * FROM cms_user;
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password | email | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 張三  | zhangsan | [email protected] | 1419811708 | user.jpg |     1 |   21 | 男     |
|  2 | 張三豐  | zhangsanfeng | [email protected] | 1419812708 | user.jpg |2 |   31 | 女     |
|  3 | 章子怡    | zhangsan | [email protected] | 1419813708 | user.jpg | 3 |   43 | 男     |
|  4 | long      | long  | [email protected] | 1419814708 | user.jpg |     4 |   41 | 女     |
|  5 | ring      | ring  | [email protected] | 1419815708 | user.jpg |     2 |    9 | 男     |
|  6 | queen | queen  | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女     |
|  8 | blek      | blek | [email protected] | 1419818708 | user.jpg |     1 |   85 | 女     |
|  9 | rose      | rose| [email protected] | 1419821708 | user.jpg |     2 |    9 | 男     |
| 10 | lily      | lily  | [email protected] | 1419831708 | user.jpg |     2 |   39 | 女     |
| 11 | john      | john  | [email protected] | 1419841708 | user.jpg |     2 |   72 | 保密   |
| 12 | test1     | test1 | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2 | TEST2  | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
| 14 | lll       | lll    | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
| 15 | ttt       | lll  | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
| 16 | ooo       | lll   | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
15 rows in set (0.00 sec)


*匹配前面出現多個字符


mysql> SELECT * FROM cms_user WHERE username REGEXP 'que*';
+----+----------+----------+-------------+------------+----------+-------+------+------+
| id | username | password | email       | regTime    | face     | proId | age  | sex  |
+----+----------+----------+-------------+------------+----------+-------+------+------+
|  6 | queen    | queen | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女   |
+----+----------+----------+-------------+------------+----------+-------+------+------+
1 row in set (0.00 sec)


*匹配前面出現多個字符


mysql> SELECT * FROM cms_user WHERE username REGEXP 't*';
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
| id | username  | password | email | regTime    | face     | proId | age  | sex    |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
|  1 | 張三  | zhangsan  | [email protected] | 1419811708 | user.jpg | 1 |   21 | 男     |
|  2 | 張三豐 | zhangsanfeng | [email protected] | 1419812708 | user.jpg | 2 |   31 | 女     |
|  3 | 章子怡    | zhangsan | [email protected] | 1419813708 | user.jpg |3 |   43 | 男     |
|  4 | long      | long   | [email protected] | 1419814708 | user.jpg | 4 |   41 | 女     |
|  5 | ring      | ring   | [email protected] | 1419815708 | user.jpg |  2 |    9 | 男     |
|  6 | queen     | queen  | [email protected] | 1419861708 | user.jpg |  3 |   77 | 女     |
|  8 | blek      | blek   | [email protected] | 1419818708 | user.jpg | 1 |   85 | 女     |
|  9 | rose      | rose    | [email protected] | 1419821708 | user.jpg |  2 |    9 | 男     |
| 10 | lily      | lily   | [email protected] | 1419831708 | user.jpg | 2 |   39 | 女     |
| 11 | john      | john  | [email protected] | 1419841708 | user.jpg |   2 |   72 | 保密   |
| 12 | test1     | test1  | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2     | TEST2 | [email protected] | 1381203974 | user.jpg |    20 |   18 | NULL   |
| 14 | lll       | lll  | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
| 15 | ttt       | lll   | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
| 16 | ooo       | lll  | [email protected] |  138212349 | user.jpg |     2 |   18 | NULL   |
+----+-----------+--------------+-------------+------------+----------+-------+------+--------+
15 rows in set (0.00 sec)


+至少要出出現一個或者多個即前面的字符(必須出現t)


mysql> SELECT * FROM cms_user WHERE username REGEXP 't+';
+----+----------+----------+-------------+------------+----------+-------+------+--------+
| id | username | password | email  | regTime    | face     | proId | age  | sex    |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
| 12 | test1    | test1 | [email protected] | 1419811708 | user.jpg |     1 | NULL | 保密   |
| 13 | TEST2    | TEST2 | [email protected] | 1381203974 | user.jpg |  20 |   18 | NULL   |
| 15 | ttt      | lll  | [email protected] |  138212349 | user.jpg | 2 |   18 | NULL   |
+----+----------+----------+-------------+------------+----------+-------+------+--------+
3 rows in set (0.00 sec)


+至少要出出現一個或者多個即前面的字符


mysql> SELECT * FROM cms_user WHERE username REGEXP 'que+';
+----+----------+----------+-------------+------------+----------+-------+------+------+
| id | username | password | email  | regTime    | face     | proId | age  | sex  |
+----+----------+----------+-------------+------------+----------+-------+------+------+
|  6 | queen    | queen | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女   |
+----+----------+----------+-------------+------------+----------+-------+------+------+
1 row in set (0.00 sec)


至少出現兩次用花括號表現{2}


mysql> SELECT * FROM cms_user WHERE username REGEXP 'que{2}';
+----+----------+----------+-------------+------------+----------+-------+------+------+
| id | username | password | email  | regTime    | face     | proId | age  | sex  |
+----+----------+----------+-------------+------------+----------+-------+------+------+
|  6 | queen    | queen| [email protected] | 1419861708 | user.jpg |     3 |   77 | 女   |
+----+----------+----------+-------------+------------+----------+-------+------+------+
1 row in set (0.00 sec)


至少出現三次用花括號表現{3}


mysql> SELECT * FROM cms_user WHERE username REGEXP 'que{3}';
Empty set (0.00 sec)


(空的集合)


至少出現一次到三次用花括號表現{1,3}(集合表時)


mysql> SELECT * FROM cms_user WHERE username REGEXP 'que{1,3}';
+----+----------+----------+-------------+------------+----------+-------+------+------+
| id | username | password | email   | regTime    | face     | proId | age  | sex  |
+----+----------+----------+-------------+------------+----------+-------+------+------+
|  6 | queen    | queen  | [email protected] | 1419861708 | user.jpg |     3 |   77 | 女   |
+----+----------+----------+-------------+------------+----------+-------+------+------+
1 row in set (0.00 sec)



比較


正則表達式比模糊查詢實現的要多。



【本文由麥子學院獨家原創,轉載請注明出處并保留原文鏈接】

logo
? 2012-2016 www.jvbprd.live
蜀ICP備13014270號-4 Version 5.0.0 release20160127

免費領取價值1888元求職寶典!

客服熱線 400-862-8862

回到頂部

彩票安徽25选5