SQL必知必会--过滤数据

where子句：

 select [vend_name],[vend_address]  FROM [Test].[dbo].[Vendors] where [vend_name] = 'hanMa'

注：当有order by 和 where 时，where 在order by 前面

where子句操作符：

 

单个值检查：

 select [vend_name],[vend_address],[vend_zip] FROM [Test].[dbo].[Vendors] where [vend_zip] < 11111111

不匹配检查：

 select [vend_name],[vend_address],[vend_zip] FROM [Test].[dbo].[Vendors] where [vend_name] != 'defu'

范围值检查 ：

select [vend_name],[vend_address],[vend_zip]  FROM [Test].[dbo].[Vendors] where [vend_name] between 'hanma' and 'zhongshihua'

空值检查：null 它与空格，或者空字符串不同。

select [vend_name],[vend_address],[vend_zip] FROM [Test].[dbo].[Vendors] where [vend_city] is null

AND操作符：

select [vend_name],[vend_address],[vend_zip] FROM [Test].[dbo].[Vendors] where [vend_city] is not null and [vend_name] > 'defu'

OR操作符： 

select [vend_name],[vend_address],[vend_zip],[vend_city] FROM [Test].[dbo].[Vendors] where [vend_city] is  null or  [vend_name] <= 'defu'

注：子句在处理or操作符之前会优先处理and操作符

IN操作符:是用来指定条件范围的，范围中的每一个条件都可以匹配。

 select [vend_name],[vend_address],[vend_zip],[vend_city]FROM [Test].[dbo].[Vendors] where [vend_city] in ('bolin','haidian‘)

NOT操作符：

 select [vend_name],[vend_address],[vend_zip],[vend_city] FROM [Test].[dbo].[Vendors] where not [vend_city] in ('bolin','haidian')