SQL 必知必会第六课 用通配符进行过滤
select prod_id, prod_name from Products where prod_name like 'fish%';
## %告诉DBMS接受fish之后的任意字符
select prod_id, prod_name from Products where prod_name like '%bean bag%';
## 匹配任何位置上包含文本bean bag的值
select prod_name from Products where prod_name like 'f%y%';
## 找出以f开头y结尾的产品名 y后面加ge%好处是若y后面是空格值也能匹配到alter
select prod_id, prod_name from Products where prod_name like '_ inch teddy bear';
select prod_id, prod_name from Products where prod_name like '% inch teddy bear';
## _ 匹配单个字符 %匹配多个字符
## challenges
select prod_name, prod_desc from Products where prod_desc like '%toy%'; -- 1
select prod_name, prod_desc from Products where prod_desc not like '%toy%'; -- 2
select prod_name, prod_desc from Products where prod_desc like '%toy%' and prod_desc like '%carrot%'; -- 3
select prod_name, prod_desc from Products where prod_desc like '%toy%carrots%'; -- 4
注意点
-
mysql workbench中通配符不区分大小写,但根据DBMS不同设置,搜索是可以区分大小写
-
%可以匹配任何值,null除外
-
[] 通配符中mysql无法应用
通配符使用技巧
- 不过度使用通配符,若其他操作有相同目的,使用其他操作
- 使用通配符不要放在搜索模式的开始处
- 仔细注意通配符的位置