实际查询中,通常不会检索所有行,需要对数据进行筛选过滤,选出符合我们需要条件的数据。
sql中的数据过滤通过where子句中指定的搜索条件进行
select prod_name, prod_price
from products
where prod_price = 3.49;
select prod_name,prod_price
from products
where prod_price < 10;
select vend_id, prod_name
from products
where vend_id <> 'DLL01';
select vend_id, prod_name
from products
where vend_id != 'DLL01';
select prod_name,prod_price
from products
where prod_price between 5 and 10;
select prod_name
from products
where prod_name is null;
and or操作符
select prod_name, prod_price
from products
where vend_id = 'DLL01' OR vend_id = 'BRS01'
and prod_price >= 10;
select prod_name, prod_price
from products
where (vend_id = 'DLL01' OR vend_id = 'BRS01')
and prod_price >= 10;
in操作符
select prod_name, prod_price
from products
where vend_id in( 'DLL01' , 'BRS01');
not 操作符
select prod_name
from products
where not vend_id = 'DLL01'
order by prod_name;
使用like操作符进行通配搜索
select prod_id,prod_name
from products
where prod_name like 'Fish%';
select prod_id,prod_name
from products
where prod_name like '__ inch teddy bear';
select cust_contact
from customers
where cust_contact like '[JM]%';