第四章过滤数据

大家好！本讲我们来讲一下，如何对数据进行过滤。

在实际工作中，我们用到的数据库表一般都比较大，记录条数在百万级甚至千万级。对于记录数比较多的表，只有在极少数情况下，才需要查询表里的全部数据。当我们只需要查询表里的部分数据时，需要我们在查询数据的时候指定查询条件。

# 1.列的类型为数值的过滤

我们可以使用 WHERE 子句指定查询条件来过滤数据。可以在 WHERE 子句中使用的操作符包括：=,>,<,>=,<=,<>,BETWEEN AND,LIKE等。

下面我们分别演示下各个操作符的使用：

从 products 查询 prod_price 为9.49的数据

SELECT 
    prod_name, 
    prod_price 
FROM 
    products 
WHERE
    prod_price = 9.49;

1
2
3
4
5
6
7

运行结果如下：

从 products 查询 prod_price 大于9.49的数据

SELECT 
    prod_name, 
    prod_price 
FROM 
    products 
WHERE 
    prod_price > 9.49;

1
2
3
4
5
6
7

运行结果如下：

从 products 查询 prod_price 小于9.49的数据

SELECT 
    prod_name, 
    prod_price 
FROM 
    products 
WHERE 
    prod_price < 9.49;

1
2
3
4
5
6
7

运行结果如下：

从 products 查询 prod_price 大于等于9.49的数据

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

1
2
3
4
5
6
7

运行结果如下：

从 products 查询 prod_price 小于等于9.49的数据

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

1
2
3
4
5
6
7

运行结果如下：

从 products 查询 prod_price 不等于9.49的数据

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

1
2
3
4
5
6
7

运行结果如下：

从 products 查询 prod_price 介于5.99到11.99之间的数据

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

1
2
3
4
5
6
7

运行结果如下：

还有另一种写法：

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

1
2
3
4
5
6
7

运行结果如下：

可以观察到两种写法的结果都是一样的。

# 2.列的类型为字符串的过滤

刚才举的例子，列的类型为数值，如果列的类型为字符串，在和值进行比较时，需要给值加上单引号。例如：从 products 查询 vend_id 为 DLL01 的数据

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

1
2
3
4
5
6
7
8

运行结果如下：

从 products 查询 vend_id 不是 DLL01 的所有数据

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

1
2
3
4
5
6
7
8

运行结果如下：

# 3.NULL 值的过滤

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

SELECT 
    cust_id, 
    cust_name
FROM 
    customers 
WHERE 
    cust_email IS NULL;

1
2
3
4
5
6
7

运行结果如下：

# 4.多条件的过滤

刚才在过滤数据时使用的都是单一条件，可以使用OR、AND操作符将多个条件组合在一起。

AND 用来指示查询满足所有给定条件的行，我们来演示下 AND 的用法。

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

1
2
3
4
5
6
7
8
9
10

运行结果如下：

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

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

1
2
3
4
5
6
7
8
9
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;

1
2
3
4
5
6
7
8
9
10

运行结果如下：

我们发现结果是不对的，查询出来的有些商品的价格是不大于 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;

1
2
3
4
5
6
7
8
9
10

运行结果如下：

我们发现现在的结果符合我们的期望。

建议：当过滤条件里包括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';

1
2
3
4
5
6
7
8
9
10
11

运行结果如下：

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

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

1
2
3
4
5
6
7
8
9

运行结果如下：

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

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

1
2
3
4
5
6
7
8
9

运行结果如下：

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');

1
2
3
4
5
6
7
8
9
10
11
12
13
14

运行结果如下：

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

NOT 操作符, 表示对后面的过滤条件取反 例如：从 products 查询 vend_id 不是 DLL01 的所有数据

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

1
2
3
4
5
6
7
8
9

运行结果如下：

和下面的语句是等价的。

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

1
2
3
4
5
6
7
8
9

运行结果如下：

在这种简单的条件语句中，NOT确实没有什么优势，但是当条件语句更复杂时，我们使用NOT可以非常方便地找出和条件不匹配的记录。 例如：从 products 查询 prod_price不是 3.49, 11.99, 9.49 三者任意一个的所有数据

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

1
2
3
4
5
6
7
8
9

运行结果如下：

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

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

1
2
3
4
5
6
7
8
9

运行结果如下：

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

例如：从 products 查找 prod_name 前缀含有 Fish 的数据

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

1
2
3
4
5
6
7
8
9

运行结果如下：

从 products 查找 prod_name 中含有 teddy 的数据

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

1
2
3
4
5
6
7
8
9

运行结果如下：

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

例：从 products 查询 prod_name 后缀为inch teddy bear且前面只有一个字符的数据。

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

1
2
3
4
5
6
7
8
9

运行结果如下：

从 products 查询 prod_name 后缀为inch teddy bear且前面只有两个字符的数据。

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

1
2
3
4
5
6
7
8
9

运行结果如下：

与%能匹配若干个字符不同，_总是刚好匹配一个字符。通配符很好用，但是这种好用是有代价的，代价就是搜索要耗费更长的处理时间。使用通配符的注意事项： 1.不要滥用通配符，如果其他操作符可以达到目的，就选用其他操作符。 2.在确实需要使用通配符时，也不要把通配符放在搜索模式的开始处，把通配符置于开始处，搜索起来是最慢的。

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

REGEXP 匹配开头：从 products 查询 prod_name 前缀为 Fish 的数据。

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

1
2
3

运行结果如下：

匹配结尾：从 products 查询 prod_name 后缀为 doll的数据。

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

1
2
3

运行结果如下：

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

1
2
3

运行结果如下：

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

1
2
3

运行结果如下：

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

1
2
3

运行结果如下：

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

1
2
3

运行结果如下：

小结：

# 5.课后题

1.下列哪一个操作符可以过滤模糊值？

A. OR

B. LIKE

C. AND

D. IN

2.查询产品价格不等于5.99的有哪些商品？