零基础入门 SQL 系列之（三）过滤数据

在实际工作中，我们用到的数据库表一般都比较大，记录条数在百万级甚至千万级。对于记录数比较多的表，只有在极少数情况下，才需要查询表里的全部数据。当我们只需要查询表里的部分数据时，需要我们在查询数据的时候指定查询条件。我们可以使用 WHERE 子句指定查询条件来过滤数据。可以在 WHERE 子句中使用的操作符包括：=,>,<,>=,<=,<>,BETWEEN AND,LIKE等。

使用数值类型的列进行过滤

SELECT prod_name, prod_price 
FROM products 
WHERE prod_price = 9.49;

SELECT prod_name, prod_price 
FROM products 
WHERE prod_price > 9.49;

SELECT prod_name, prod_price 
FROM products 
WHERE prod_price < 9.49;

SELECT prod_name, prod_price 
FROM products 
WHERE prod_price >= 9.49;

SELECT prod_name, prod_price 
FROM products 
WHERE prod_price <= 9.49;

SELECT prod_name, prod_price 
FROM products 
WHERE prod_price <> 9.49;

SELECT prod_name, prod_price 
FROM products 
WHERE prod_price BETWEEN 5.99 AND 11.99;

SELECT prod_name, prod_price 
FROM products 
WHERE prod_price >= 5.99 AND prod_price <= 11.99;

使用字符类型的列进行过滤

如果列的类型为字符串，在和值进行比较时，需要给值加上单引号。

SELECT prod_name, prod_price, vend_id 
FROM products 
WHERE vend_id = 'DLL01';

SELECT prod_name, prod_price, vend_id 
FROM products 
WHERE vend_id <> 'DLL01'

对值为 NULL 的值进行过滤

在数据库里还有一类值叫 NULL，它表示没有任何值，它与字段包含 0、空字符串或仅仅包含空格不同。确定值是否为 NULL 不能简单的用 = NULL，要用 IS NULL。

SELECT cust_id, cust_name
FROM customers 
WHERE cust_email IS NULL;

使用 OR、AND 操作符进行过滤

刚才在过滤数据时使用的都是单一条件，可以使用 OR、AND 操作符将多个条件组合在一起。AND 用来指示查询满足所有给定条件的行。

SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE vend_id = 'BRS01' AND prod_price < 10;

OR 用来指示检索满足任一给定条件的行。

SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE vend_id = 'BRS01' OR prod_price < 10;

AND 和 OR 组合在一起。假设我们要查询由 DLL01 或 FNG01 制造，且价格大于 9 美元的商品。如果我们将 SQL 写成如下这样：

SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'FNG01' AND prod_price > 9;

我们发现结果是不对的，查询出来的有些商品的价格是不大于 9 美元的。这是因为 AND 操作符的运算优先级大于 OR，查询的条件变成了商品由供应商 DLL01 制造或者由供应商 FNG01 制造并且商品价格大于 9 美元。正确的 SQL 语句应该写成如下形式：

SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE (vend_id = 'DLL01' OR vend_id = 'FNG01') AND prod_price > 9;

建议：当过滤条件里包括 AND 和 OR 时，都应该使用括号明确地指定操作符的执行顺序，不要依赖操作符的默认求值顺序，即使默认求值顺序如希望的那样。使用括号没有什么坏处，它能够消除歧义。
假设我们要查询由 DLL01、FNG01 或 BRS01制造的商品。我们可以将 SQL 写成如下的形式：

SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'FNG01' OR vend_id = 'BRS01';

使用 IN 操作符进行过滤

像上面这种查询，我们还有一种比较简便的写法：

SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE vend_id IN ('DLL01', 'FNG01', 'BRS01');

IN 的用法同样适用于数值型字段，例如：

SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE prod_price IN (3.49, 11.99, 9.49);

IN 操作符后面还可以跟上 SQL 语句，例如：

SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE prod_price IN (SELECT DISTINCT prod_price
        FROM products
        WHERE prod_id = 'BR03' OR prod_id = 'BNBG01');

IN 操作符的优点

• IN 操作符的语法更清楚、更直观。
• 在与 AND 或 OR 组合使用时，求值顺序更容易管理。
• IN 操作符一般比一组 OR 操作符执行得更快。
• IN 的最大优点是可以包含其他 SELECT 语句。

使用 NOT 操作符进行过滤

NOT 操作符, 表示对后面的过滤条件取反

SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE NOT vend_id = 'DLL01';

和下面的语句是等价的。

SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE vend_id <> 'DLL01';

在这种简单的条件语句中，NOT 确实没有什么优势，但是当条件语句更复杂时，我们使用 NOT 可以非常方便地找出和条件不匹配的记录。

SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE prod_price NOT IN (3.49, 11.99, 9.49);

使用 LIKE 和 通配符进行过滤

前面介绍的过滤条件中使用的值都是已知的，但有时候需要过滤的值不是已知的，例如，我们检索产品名以 doll 为结尾的产品。这时候我们便用到 LIKE 操作符以及通配符。

SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE prod_name LIKE '%doll'; 

% 为通配符，表示任何字符出现任意次数。 通配符查询只能用于文本字段，非文本数据类型字段不能使用通配符搜索。 % 通配符可在搜索模式的任意位置使用。并且可以使用多个通配符。

SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE prod_name LIKE 'Fish%'; 

SELECT prod_id, prod_price, prod_name, vend_id
FROM products
WHERE prod_name LIKE '%teddy%';

通配符不可以匹配 NULL。另外一个通配符是下划线_。下划线的用途与 % 是一样的，但它只匹配单个字符，而不是多个字符。

SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name LIKE '_ inch teddy bear';

SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name LIKE '__ inch teddy bear';

• 与 % 能匹配0个字符不同，_总是刚好匹配一个字符。
• 通配符很好用，但是这种好用是有代价的，代价就是搜索要耗费更长的处理时间。
  *使用通配符的注意事项： *
  1.不用滥用通配符，如果其他操作符可以达到目的，就选用其他操作符。
  2.在确实需要使用通配符时，也不要把通配符放在搜索模式的开始处，把通配符置于开始处，搜索起来是最慢的。
  在过滤数据时，我们还可以适用正则匹配。在 MySQL 里使用 REGEXP 关键字。

使用正则匹配进行过滤

在过滤数据时，我们还可以适用正则匹配。在 MySQL 里使用 REGEXP 关键字。

REGEXP 匹配开头

SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP '^Fish';

REGEXP 匹配结尾

SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP 'doll$';

[]

SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP '^[123]';

SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP '^[1-8]';

SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP '^[^123]';

SELECT prod_id, prod_price, prod_name, prod_id
FROM products
WHERE prod_name REGEXP '^[^1-8]';

备注：示例中用到的表和数据可以通过链接（https://github.com/songw/sql）下载。

本系列目录：
零基础入门 SQL 系列之（一）查询数据
零基础入门 SQL 系列之（二）排序
零基础入门 SQL 系列之（三）过滤数据
零基础入门 SQL 系列之（四）内置函数
零基础入门 SQL 系列之（五）数据汇总
零基础入门 SQL 系列之（六）表连接
零基础入门 SQL 系列之（七）组合
零基础入门 SQL 系列之（八）插入、更新、删除
零基础入门 SQL 系列之（九）创建和操作表
零基础入门 SQL 系列之（十）视图