[REGEXP] - regular expresion 正则表达式

SELECT *
FROM customers
WHERE last_name LIKE '%field%'

<=>

SELECT *
FROM customers
WHERE last_name REGEXP 'field'

'^ + 字符串' ---> 限定以某字符串开头

SELECT *
FROM customers
WHERE last_name REGEXP '^field'

‘字符串 + $’ ---> 限定以某字符串结尾

SELECT *
FROM customers
WHERE last_name REGEXP 'field$'

| 或运算， 中间有空格就搜不出来了

SELECT *
FROM customers
WHERE last_name REGEXP 'field|mac'

 

多个条件查询

SELECT *
FROM customers
WHERE last_name REGEXP 'field|mac|rose'

必须以field开头或者...或者...

少了一条记录 

SELECT *
FROM customers
WHERE last_name REGEXP '^field|mac|rose' 

可以搜寻到以file结尾的brushfield

SELECT *
FROM customers
WHERE last_name REGEXP 'field$|mac|rose' 

 

[   ],  或者， ge, ie, me  

SELECT *
FROM customers
WHERE last_name REGEXP '[gim]e'
 

 

[x -y], 区间检索, 中间不能空格, 两边都是闭区间

SELECT *
FROM customers
WHERE last_name REGEXP '[a-l]e'
 

 

 

summary:

^ beginning

$ end

l logical or

[abcd] any of them

[a-f] any of them in this range

• homework1

first name are ELKA or AMBUR

SELECT *
FROM customers
WHERE first_name REGEXP 'elka|ambur'
 

可惜这是错误的， 这个代表正则表达式搜寻包含ELKA或者AMBUR的用户

right1:

SELECT *
FROM customers
WHERE first_name REGEXP '^elka$|^ambur$'

right2:

SELECT *
FROM customers
WHERE first_name IN ('elka','ambur')
 

 

• homework2

last names end with EY or ON

SELECT *
FROM customers
WHERE last_name REGEXP 'ey$|on$'
 

• homework3

last names start with MY or contains SE

SELECT *
FROM customers
WHERE last_name REGEXP '^my|se'
 

 

 

homework4

last names contain B followed by R or U

SELECT *
FROM customers
WHERE last_name REGEXP 'b[ru]'