《SQL必知必会》--第6课用通配符进行过滤及课后练习

部分示例代码

like操作符+%通配符：模糊查询

①从products表中查询 以fish开头的prod_name以及它们对应的prod_id。

select prod_id,prod_name from [dbo].[Products]
where prod_name like 'FIsh%';

②从products表中查询prod_name包含“bean bag”字符的prod_name和prod_id。

select prod_id,prod_name from [dbo].[Products]
where prod_name like '%bean bag%';

③从products表中查询以F开头y结尾的prod_name和prod_id。

select prod_name from [dbo].[Products]
where prod_name like 'F%y';

like操作符+ _通配符：一个下划线只匹配单个字符

④匹配一个字符的例子

select prod_id,prod_name from [dbo].[Products]
where prod_name like '_ inch teddy bear'

⑤两个_ _，匹配两个字符的例子

select prod_id,prod_name from [dbo].[Products]
where prod_name like '__ inch teddy bear';

⑥与④和⑤对比，下面例子使用%

select prod_id,prod_name from [dbo].[Products] 
where prod_name like '%inch teddy bear';

like操作符+[]通配符：用于指定字符集，匹配[]中的任意字符即可

⑦从customers表中查询以J或M开头的cust_contact。

select cust_contact from [dbo].[Customers]
where cust_contact like '[JM]%';

⑧ []与^搭配，取反。返回与⑦的查询相反的数据

select cust_contact from [dbo].[Customers]
where cust_contact like '[^JM]%';

课后练习

①从products表中返回prod_name，prod_desc，仅返回prod_desc中包含“toy”的数据。

select prod_name,prod_desc from [dbo].[Products]
where prod_desc like '%toy%';

②从products表中返回prod_name，prod_desc，仅返回prod_desc中不包含“toy”的数据。

select prod_name,prod_desc from [dbo].[Products]
where not prod_desc like '%toy%'
order by prod_name;

③从products表中返回prod_name，prod_desc，仅返回prod_desc中既包含“toy”又包含“carrots”的数据。

select prod_name,prod_desc from [dbo].[Products]
where prod_desc like '%toy%' and prod_desc like '%carrots%';

④从products表中返回prod_name，prod_desc，仅返回prod_desc中既包含“toy”又包含“carrots”的数据。（用三个%表示）

select prod_name,prod_desc from [dbo].[Products]
where prod_desc like '%toy%carrots%';