【SQL必知必会笔记(3)】SELECT语句的WHERE子句数据过滤操作

时间：2022-05-09 23:46:38

上个笔记主要介绍了利用SELECT语句检索单个/多个/所有列，并利用DISTINCT关键字检索具有唯一性的值、利用LIMIT/OFFSET子句限制结果；以及利用ORDER BY子句排序检索出的数据，主要有按照单个/多个列名/列位置/混合排序、用DESC关键字指定排序方向。

这一次我们来看一下如何使用SELECT语句的WHERE子句来指定搜索条件进行数据过滤，包括使用=、>、<等基础操作符的基础数据过滤；使用AND、OR、IN、NOT操作符的高级数据过滤；以及使用%、_、[]通配符的数据过滤操作，以及实际使用中的一些细节。

1.过滤数据(WHERE子句)

数据库表一般包含大量的数据，很少需要检索表中的所有行。通常只会根据特定操作或报告的需要提取表数据的子集。

只检索所需数据需要指定搜索条件(search criteria)，或称为过滤条件(filter condition)。

  • 在SELECT语句中，数据根据WHERE子句中指定的过滤条件进行过滤。
  • WHERE子句位置：在表名(FROM子句)之后给出。
  • 通过非选择列进行过滤。用非检索的列过滤数据也是是完全合法的。

1.1 WHERE子句操作符

【SQL必知必会笔记(3)】SELECT语句的WHERE子句数据过滤操作

  • 注意：操作符兼容。表中所列出的操作符是冗余的，有功能相同的操作符。具体DBMS支持的操作符不一样。
  • 经过尝试，MySQL中除不支持!<、!>这两个操作符外，其他都支持。
  • SQL过滤 or 在应用层过滤？通常最好选择SQL过滤。

1.2 过滤操作

1.2.1 检查单个值

  • 语句：
SELECT 列名1,列名2

FROM 表

WHERE 列名1 < 10;

1.2.2 不匹配检查(!=、<>操作符)

  • 语句：
SELECT 列名1,列名2

FROM 表

WHERE 列名1 <> 'DLL01';

提示：

  • 单引号用来限定字符串。若将值与字符串类型的列进行比较，就需要限定引号；若将值与数值列进行比较，则不用引号。

1.2.3 范围值检查(BETWEEN操作符)

  • 语句：
SELECT 列名1,列名2

FROM 表

WHERE 列名1 BETWEEN 5 AND 10;

提示：

  • 在使用BETWEEN操作符时，必须指定两个值：所需范围的低端值和高端值。(闭区间)
  • 并且这两个值必须用AND关键字分隔。

1.2.4 空值检查(IS NULL操作符)

  • 语句：
SELECT 列名1,列名2

FROM 表

WHERE 列名1 IS NULL;

分析：

  • 在创建表时，表设计人员可以指定其中的列能否不包含值。在一个列不包含值时，称其包含空值NULL。
  • NULL：无值(no value)，它与字段包含0、空字符串或仅仅包含空格不同。

提示：各DBMS 特有的操作符。

  • 许多DBMS 扩展了标准操作符集，提供了更高级的过滤选择，想了解可以参阅相应DBMS文档。

注意：NULL和非匹配。

  • 在进行匹配过滤或非匹配过滤时，不会返回含NULL值的行。
  • 因此过滤数据时，一定要验证被过滤列中含NULL值的行确实出现在返回的数据中。

2.高级数据过滤(组合WHERE子句)

在上一小节中，所有的WHERE子句在过滤数据时使用的都是单一的条件。为了进行更强的过滤控制，SQL允许给出多个WHERE子句。这些句子有两种使用方式：以AND子句或OR子句的方式使用。

  • 操作符(operator)：用来联结或改变 WHERE子句中的//子句//的关键字，也称为逻辑操作符(logical operator)。

下面我们就来分别介绍一下AND、OR、IN、NOT四种操作符。

2.1 AND操作符

要通过不止一个列进行过滤，可以使用AND操作符给WHERE子句附加条件。

  • 语句：
SELECT 列名1,列名2,列名3

FROM 表

WHERE 列名1 = 值1 AND 列名2 <= 值2;

分析：

  • AND：用在WHERE子句中的关键字，用来表示//检索(v)//满足所有给定条件的行。
  • 这个例子中只有两个过滤条件。可以增加多个过滤条件，每个条件间都要使用AND关键字。

注意：

  • 例子中省略了ORDER BY子句。因此不同情况下做出来的输出，顺序可能不同。可以在WHERE子句之后加上一个ORDER BY子句。

2.2 OR操作符

  • 语句：
SELECT 列名1,列名2,列名3

FROM 表

WHERE 列名1 = 值1 OR 列名2 = 值2;

分析：

  • OR：用在WHERE子句中的关键字，用来表示//检索(v)//满足任一给定条件的行。(可以看出，OR的功能与AND正好相反。)

提示：

  • 许多DBMS在OR WHERE子句的第一个条件得到满足时，不管第二个条件是否满足，相应的行都将被检索出来。

2.3 求值顺序(AND与OR结合时)

WHERE子句可以包含任意数目的AND和OR操作符。允许两者结合进行复杂、高级的过滤。

提示：在结合 AND和OR时，要注意求值的顺序(优先级)：

  • SQL(像多数语言一样)在处理OR操作符前，优先处理AND操作符。
  • 优先级：圆括号 > AND > OR。在三者中圆括号的优先级最高。
  • 任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。

2.4 IN操作符

  • 语句：
SELECT 列名1,列名2,列名3

FROM 表

WHERE 列名1 IN (值1，值2)

ORDER BY 列名2;

分析：

  • IN后跟一组由逗号分隔，括在圆括号(必须)中的合法值。

  • IN操作符用来指定条件范围，范围中的//每个条件都可以进行匹配。功能与OR相当，下面看一个实例：

    语句1：
    
SELECT prod_name,prod_price
    
FROM Products
    
WHERE vend_id IN ('DLL01','BRS01')
    
ORDER BY prod_name;

语句2：
    
SELECT prod_name,prod_price
    
FROM Products
    
WHERE vend_id ='DLL01' OR vend_id = 'BRS01'
    
ORDER BY prod_name;

语句1与语句2输出完全一样。
    
注意：使用OR时，即使检索同一个列的不同值，也不可以直接：vend_id ='DLL01' OR 'BRS01'

那么，既然IN与OR功能相当，我们为什么还要使用IN操作符呢？IN操作符的优点如下：

  • 语法更清楚、直观；
  • 在与其他AND和OR操作符组合使用IN时，求值顺序更容易管理；
  • IN操作符一般比一组OR操作符执行得更快。
  • 最大优点：IN操作符可以包含其他SELECT语句，能够更动态地建立WHERE子句。(后续的笔记中，还会对此做详细介绍。)

2.5 NOT操作符

  • 语句：
SELECT 列名1

FROM 表

WHERE NOT 列名1 = 值1

ORDER BY 列名1;

上面这个例子也可以使用<>或!=操作符来完成。

分析：

  • NOT操作符：有且只有一个功能，即否定其后所跟的任何条件。
  • NOT 从不单独使用，所以它的语法与其他操作符有所不同。可以用在要过滤的列前 ，也可以用在要过滤的列后。

说明：

  • 大多数DBMS：允许使用NOT否定任何条件。
  • MariaDB：支持使用NOT否定IN、BETWEEN和EXISTS子句。

NOT的优点：

  • 对于上面例子中简单的WHERE子句，使用NOT确实没有什么优势。
  • 但是在更复杂的子句中，NOT是非常有用的。比如，在与IN操作符联合使用时，NOT可以非常简单地找出与条件列表不匹配的行。

3.用通配符进行过滤(LIKE + 通配符)

前面所介绍的所有操作符都是针对已知(完整)值进行过滤的。但是这种过滤方法并不是任何时候都好用。有些时候需要利用通配符，来创建(v)//用于比较(v)特定数据的//搜索模式。

  • 通配符(wildcard):用来匹配值的一部分的特殊字符。
  • 搜索模式(search pattern):由字面值(已知值)、通配符或两者组合构成的搜索条件。

通配符本身实际上是SQL的WHERE子句中有特殊含义的字符，SQL支持几种通配符。

LIKE操作符：

  • 为在搜索句子中使用通配符，必须使用LIKE操作符。
  • LIKE指示DBMS，后跟的搜索模式利用通配符匹配，而不是简单的相等匹配进行比较。

注意：

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

谓词(predicate)(选看)：

  • 操作符何时不是操作符？答案是，它作为谓词时。
  • 从技术上说，LIKE是谓词而不是操作符。虽然最终的结果是相同的，但应对此术语有所了解，以免在SQL文献或手册中遇到此术语时不知所云。

3.1 百分号(%)通配符

语句：

SELECT 列名1,列名2,列名3

FROM 表

WHERE 列名1 LIKE 'Fish%';

分析：

  • %是最常用的通配符。
  • 在搜索串中，%表示任何字符(除NULL)出现任意次数(包括0次)。
  • 通配符(不止是%)可以在搜索模式中的任意位置使用，并且可以使用多个通配符。

说明：

  • Access通配符。如果使用的是Microsoft Access，需要使用*而不是%。
  • 区分大小写。根据DBMS的不同及其配置，搜索可以是区分大小写的。

注意：

  • 注意字符串后面所跟的空格，包括Access在内的许多DBMS都用空格来填补字段的内容。(经过测试，默认设置的MySQL中没有填补空格的机制。)

    例如，如果某列有50个字符，而存储的文本为Fish bean bag toy(17个字符)，则为填满该列需要在文本后附加33个空格。
    
这样做一般对数据及其使用没有影响，但是可能对某些SQL语句有负面影响。
    
例如，子句WHERE prod_name LIKE 'F%y'只匹配以F开头，以y结尾的prod_name。如果值后面跟空格，则不是以y结尾，那么最终检索结果就不是我们预想的。

- 解决办法：简单的，给搜索模式再增加一个%，即'F%y%'；
    
  				 更好的解决办法，用函数去掉空格(后面详细介绍)

  • 注意NULL。通配符%无法匹配NULL。

3.2 下划线(_)通配符

  • 语句：
SELECT 列名1,列名2

FROM 表

WHERE 列名1 LIKE '__ inch teddy bear';

分析：

  • -总是刚好匹配一个字符，不能多也不能少。
  • %可以匹配0个、1个、多个字符。

说明：

  • DB2不支持通配符_
  • Access通配符。如果使用的是Microsoft Access，需要使用?而不是_。

3.3 方括号([])通配符(MySQL不支持)

  • 语句：
SELECT 列名1

FROM 表

WHERE 列名1 LIKE '[JM]%'

ORDER BY 列名1;

分析：

  • []通配符用来指定一个字符集，它必须(也只能)匹配指定位置(通配符的位置)的一个字符。
  • 此通配符可以用前缀字符(脱字号^)来否定。也可以使用NOT操作符得到类似的结果

    ，^的唯一优点是在使用多个WHERE子句时可以简化语法。

说明：

  • 并不总是支持集合。与前面描述的通配符不一样，并不是所有DBMS都支持用来创建集合的[]。只有Access和SQL Server支持集合。(经过测试，MySQL确实不支持这个操作。)
  • Access中需要用！而不是^来否定一个集合。即，用[!JM]，而不是[ ^JM]。

3.4 使用通配符的技巧

正如本节我们介绍，SQL的通配符很拥有。但是这种功能是有代价的，即通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。

使用通配符的技巧：

  • 不要过度使用通配符。其他操作符能达到相同的目的，尽量使用其他操作符。
  • 确实需要使用通配符时，尽量不要把它们放在搜索模式的开始处。
  • 仔细注意通配符的位置。如果放错地方，可能不会返回我们想要的数据。

总之，通配符是一种极其重要和有用的搜索工具，以后我们经常会用到它。

参考资料：

1.《SQL必知必会》Ben Forta。

相关文章