mysql通配符和正则表达式过滤语句（LIKE和REGEXP）

1.检索任意以jet开头的词，%告诉mysql接受jet之后的任意字符，数量不限

SELECT prod_id,prod_name FROM table_name WHERE prod_name LIKE 'jet%';

2.匹配prod_name列上的任何包含anvil的词

SELECT prod_id,prod_name FROM table_name WHERE prod_name LIKE '%anvil%';

3.找出table_name表中prod_name列上以s开头以e结尾的所有行。

SELECT prod_name FROM table_name WHERE prod_name LIKE 's%e';

4._和%用途一样，不过它只能充当一个字符

SELECT prod_id,prod_name FROM table_name WHERE prod_name LIKE '_tom anvil';

5.匹配表中prod_name列包含1000的行。

SELECT prod_name FROM table_name WHERE prod_name REGEXP '1000' ORDER BY prod_name;

6. . 表示匹配任意一个字符，意味着这条语句可以匹配表中prod_name包含1000，2000，a000，…的行

SELECT prod_name FROM table_name WHERE prod_name REGEXP '.000' ORDER BY prod_name;

7.BINARY让正则表达式的匹配中区分大小写

SELECT prod_name FROM table_name WHERE prod_name REGEXP BINARY 'JetPack .000' ;

8.匹配包含1000或2000的词

SELECT prod_name FROM table_name WHERE prod_name REGEXP '1000|2000' ORDER BY prod_name;

9.[123]匹配1或2或3

SELECT prod_name FROM table_name WHERE prod_name REGEXP '[123] ton' ORDER BY prod_name;

10.[^123]匹配1和2和3之外的任何字符（包含多个）

SELECT prod_name FROM table_name WHERE prod_name REGEXP '[^123] ton' ORDER BY prod_name;

11.[0-9]匹配0到9之间的任意一个数。[a-z]匹配任意字母字符

SELECT prod_name FROM table_name WHERE prod_name REGEXP '[0-9] ton' ORDER BY prod_name;

12.匹配包含 . 的词

SELECT prod_name FROM table_name WHERE prod_name REGEXP '\\.' ORDER BY prod_name;

13.sticks?表示匹配stick和sticks

SELECT prod_name FROM table_name WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
 ORDER BY prod_name;

14.[[:digit:]]{4}匹配连在一起的任意4位数字

SELECT prod_name FROM table_name WHERE prod_name REGEXP '[[:digit:]]{4}'
 ORDER BY prod_name;

15.匹配文本的开始有数字或.的词。

SELECT prod_name FROM table_name WHERE prod_name REGEXP '^[0-9\\.]'
 ORDER BY prod_name;