MySQL过滤数据

使用where子句

检查单个值

select prod_price, prod_name 
from products 
where prod_name = 'fuses';

select prod_price, prod_name 
from products 
where prod_price <= 10;

不匹配检查

select prod_price, prod_name 
from products 
where prod_price != 10;

范围值检查

select prod_price, prod_name 
from products 
where prod_price between 5 and 10;

空值检查

select prod_price, prod_name 
from products 
where prod_price is null;

组合where子句

and操作符

select prod_price, prod_name 
from products 
where vend_id = 1003 and prod_price <= 10;

or操作符

select prod_price, prod_name 
from products 
where vend_id = 1002 or vend_id = 1003; 

计算次序

select prod_price, prod_name 
from products 
where (vend_id = 1002 or vend_id = 1003) and prod_price>=10; 

in操作符

select prod_price, prod_name 
from products 
where vend_id in (1002, 1003)  
order by prod_name;

not操作符

select prod_price, prod_name 
from products 
where vend_id not in (1002, 1003)  
order by prod_name;

like操作符

百分号通配符（匹配0~多个字符，NULL除外）

select prod_price, prod_name 
from products 
where prod_name like 'jet%';

下划线通配符（只匹配一个字符）

select prod_price, prod_name 
from products 
where prod_name like '_ ton anvil';

正则表达式

基本字符匹配：检索列prod_name包含文本1000的所有行

select prod_name 
from products 
where prod_name regexp '1000' 
order by prod_name;

进行or匹配：

select prod_name 
from products 
where prod_name regexp '1000|2000' 
order by prod_name;

匹配几个字符之一：[123]表示匹配1或2或3，即[1|2|3]

select prod_name 
from products 
where prod_name regexp '[123] Ton' 
order by prod_name;

匹配范围：[1-5]表示匹配1到5

select prod_name 
from products 
where prod_name regexp '[1-5] Ton' 
order by prod_name;

匹配特殊字符：.、|、[]等前面加\

select prod_name 
from products 
where prod_name regexp '\\.' 
order by prod_name;

匹配多个实例：匹配连在一起的4个数字

select prod_name 
from products 
where prod_name regexp '[[:digit:]]{4}' 
order by prod_name;

定位符：找出一个数（包括以小数点开始的数）开始的所有产品

select prod_name 
from products 
where prod_name regexp '^[0-9\\.]' 
order by prod_name;