SQL | 使用通配符进行过滤

6-使用通配符进行过滤

6.1-LIKE操作符

前面介绍的所有操作符都是通过已知的值进行过滤，或者检查某个范围的值。但是如果我们想要查找产品名字中含有bag的数据，就不能使用前面那种过滤情况。

利用通配符，可以创建比较特定数据的搜索模式。

搜索模式：由字面值、通配符或两者组合构成的搜索条件。

通配符实际上是SQL语句中where子句中具有特殊含义的字符，SQL支持几种通配符。在为搜索子句中使用通配符，必须使用like操作符。

谓词：操作符何时不是操作符？答案是，它作为谓词时。从技术上说，LIKE是谓词而不是操作符。

通配符搜索只能用于文本字段（字符串），非文本数据类型字段不能使用通配符搜索。

6.1.1 百分号（%）通配符

百分号是最常用的通配符，%表示任何字符出现任意次数。

select prod_id,prod_name
from products
where prod_name LIKE 'Fish%';

上述SQL语句的含义是，查找名称以‘Fish’开头的行。执行上述语句时，会检索所有以'Fish'开头的词，不管它有多少字符。

一般来说，根据DBMS不同的配置，可以是区分大小写，'Fish'和‘fish’相同。也可以是不区分大小写，'Fish'和‘fish’不同。

通配符可以在搜索模式任意位置使用，并且可以使用多个通配符。

select prod_id,prod_name
from products
where prod_name LIKE '%bean bag%';

 

上述语句检索的数据特征是：名字中含有bean bag这段字符串的数据，不管它之前或者之后出现什么样的字符串。

通配符还可以出现搜索模式的中间，虽然这么做不太有用。下面例子找出以F开头，以y结尾的数据。

select prod_id,prod_name
from products
where prod_name LIKE 'F%y';

 

有一种情况把通配符放到搜索模式的中间，是很有效果的，就是根据邮件地址的一部分查找邮件。例如WHERE email LIKE '[e-mail]'。

注意：除了能匹配一个或多个字符以外，%还可以匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符。

通配符%看起来像是可以匹配任何东西，但有个例外，这就是NULL。子句WHERE prod_name LIKE '%'不会匹配产品名称为NULL的行。

6.1.2 下划线（_）通配符

作用与%通配符相同，只不过，它只匹配单个字符，并不会匹配多个字符。

select prod_id,prod_name
from products
where prod_name LIKE '__ inch teddy bear';

上述代码，给出了两个下划线通配符，检索出的数据应该是开头两个字符的数据。因此，产品名为“8 inch teddy bear”的产品不会被检索到。

6.1.3 方括号（[ ]）通配符

方括号通配符是指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。

并不是所有DBMS都支持用来创建集合的[]。微软的SQL Server支持集合，但是MySQL，Oracle，DB2，SQLite都不支持。

SELECT cust_contact 
FROM Customers 
WHERE cust_contact LIKE '[JM]%' 
ORDER BY cust_contact; 

名字以J或M起头的联系人。

此语句的WHERE子句中的模式为'[JM]%'。这一搜索模式使用了两个不同的通配符。[JM]匹配方括号中任意一个字符，它也只能匹配单个字符。因此，任何多于一个字符的名字都不匹配。[JM]之后的%通配符匹配第一个字符之后的任意数目的字符，返回所需结果。

此通配符可以用前缀字符^（脱字号）来否定。例如，下面的查询匹配以J和M之外的任意字符起头的任意联系人名。

SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'
ORDER BY cust_contact;

也可以使用NOT操作符得出类似的结果。^的唯一优点是在使用多个WHERE子句时可以简化语法

SELECT cust_contact
FROM Customers
WHERE NOT cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

6.2-使用通配符技巧

SQL通配符很有用，但是这种功能也是有代价的，相较于其他检索来说，通配符更耗费时间。

技巧：

• 不要过度使用通配符。如果其他操作符能达到相同目的，应该以其他操作符为主。

• 在确实需要使用通配符的时候，也不要把通配符使用在开始处。把通配符置于开始处是速度最慢的。

• 注意通配符的位置，如果位置不对，则返回的结果可能不尽如人意。