【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。