总结MySQL中的数据过滤方法：WHERE的基本过滤，WHERE的组合过滤，通配符过滤，子查询，组合查询。

1. WHERE的初级过滤

只检索所需数据需要指定搜索条件（search criteria），搜索条件也称为过滤条件（filter condition）。

mysql> SELECT prod_name,prod_price
    -> FROM products
    -> WHERE prod_price=2.50;
+---------------+------------+
| prod_name     | prod_price |
+---------------+------------+
| Carrots       |       2.50 |
| TNT (1 stick) |       2.50 |
+---------------+------------+
2 rows in set (0.88 sec)

除了上面提到的相等操作，还有WHERE通过其他操作符还能实现许多功能。

1.1 检查单个值

上面举了一个利用相等条件检查单个值的情况，下面再看一个利用不等条件的例子。

mysql> SELECT prod_name,prod_price
    -> FROM products
    -> WHERE prod_price > 50;
+--------------+------------+
| prod_name    | prod_price |
+--------------+------------+
| JetPack 2000 |      55.00 |
+--------------+------------+
1 row in set (0.25 sec)

1.2 不匹配检查

列出不是由供应商1003制造的产品。

mysql> SELECT vend_id, prod_name
    -> FROM products
    -> WHERE vend_id <> 1003;
+---------+--------------+
| vend_id | prod_name    |
+---------+--------------+
|    1001 | .5 ton anvil |
|    1001 | 1 ton anvil  |
|    1001 | 2 ton anvil  |
|    1002 | Fuses        |
|    1005 | JetPack 1000 |
|    1005 | JetPack 2000 |
|    1002 | Oil can      |
+---------+--------------+
7 rows in set (0.24 sec)

既可以使用<>操作符，也可以使用!=操作符。

1.3 范围值检查

为了检查某个范围的值，可使用BETWEEN操作符。下面展示，BETWEEN操作符检索价格在5美元和10美元之间的商品。

mysql> SELECT prod_name,prod_price
    -> FROM products
    -> WHERE prod_price BETWEEN 5 and 10;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| .5 ton anvil   |       5.99 |
| 1 ton anvil    |       9.99 |
| Bird seed      |      10.00 |
| Oil can        |       8.99 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
5 rows in set (0.75 sec)

在使用BETWEEN时需要跟在WHERE后面，必须指定两个值 ——所需范围的低端值和高端值。这两个值必须用AND关键字分隔。

1.4 空值检查

SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列。这个WHERE子句就是IS NULL子句。

mysql> SELECT prod_name
    -> From products
    -> WHERE prod_price IS NULL;
Empty set (0.24 sec)

注意，不能使用相等条件来检查NULL，必须使用特殊语句。

2. WHERE的高级过滤

2.1 组合WHERE语句

使用AND操作符给WHERE子句附加条件。

mysql> SELECT prod_id,prod_price,vend_id
    -> FROM products
    -> WHERE vend_id=1003 AND prod_price<=10;
+---------+------------+---------+
| prod_id | prod_price | vend_id |
+---------+------------+---------+
| FB      |      10.00 |    1003 |
| FC      |       2.50 |    1003 |
| SLING   |       4.49 |    1003 |
| TNT1    |       2.50 |    1003 |
| TNT2    |      10.00 |    1003 |
+---------+------------+---------+
5 rows in set (0.24 sec)

OR操作符与AND操作符不同，它指示MySQL检索匹配任一条件的行。

mysql> SELECT prod_id,prod_price,vend_id
    -> FROM products
    -> WHERE prod_price<5 OR vend_id=1001;
+---------+------------+---------+
| prod_id | prod_price | vend_id |
+---------+------------+---------+
| ANV01   |       5.99 |    1001 |
| ANV02   |       9.99 |    1001 |
| ANV03   |      14.99 |    1001 |
| FC      |       2.50 |    1003 |
| FU1     |       3.42 |    1002 |
| SLING   |       4.49 |    1003 |
| TNT1    |       2.50 |    1003 |
+---------+------------+---------+
7 rows in set (0.25 sec)

同时使用AND和OR时要注意：SQL在处理OR操作符前，优先处理AND操作符。此问题的解决方法是使用圆括号明确地分组相应的操作符。

mysql> SELECT prod_id,prod_price,vend_id
    -> FROM products
    -> WHERE (vend_id=1003 OR vend_id=1002)AND prod_price<5;
+---------+------------+---------+
| prod_id | prod_price | vend_id |
+---------+------------+---------+
| FC      |       2.50 |    1003 |
| FU1     |       3.42 |    1002 |
| SLING   |       4.49 |    1003 |
| TNT1    |       2.50 |    1003 |
+---------+------------+---------+
4 rows in set (0.24 sec)

2.2 IN操作符

圆括号在WHERE子句中还有另外一种用法。IN操作符用来指定条件。

mysql> SELECT prod_price,prod_name,vend_id
    -> From products
    -> WHERE vend_id IN(1002,1005);
+------------+--------------+---------+
| prod_price | prod_name    | vend_id |
+------------+--------------+---------+
|       3.42 | Fuses        |    1002 |
|      35.00 | JetPack 1000 |    1005 |
|      55.00 | JetPack 2000 |    1005 |
|       8.99 | Oil can      |    1002 |
+------------+--------------+---------+
4 rows in set (0.24 sec)

这并不表示范围，特别表示1002和1005这两个对象。相较于AND和OR更清晰直观。

2.3 NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件。

mysql> SELECT prod_price,prod_name,vend_id
    -> From products
    -> WHERE vend_id NOT IN(1002,1005);
+------------+----------------+---------+
| prod_price | prod_name      | vend_id |
+------------+----------------+---------+
|       5.99 | .5 ton anvil   |    1001 |
|       9.99 | 1 ton anvil    |    1001 |
|      14.99 | 2 ton anvil    |    1001 |
|      13.00 | Detonator      |    1003 |
|      10.00 | Bird seed      |    1003 |
|       2.50 | Carrots        |    1003 |
|      50.00 | Safe           |    1003 |
|       4.49 | Sling          |    1003 |
|       2.50 | TNT (1 stick)  |    1003 |
|      10.00 | TNT (5 sticks) |    1003 |
+------------+----------------+---------+
10 rows in set (0.24 sec)

3. 通配符过滤

前面提到：用SELECT搜索所有数据的时候，用到的*就是一个通配符除了这个符号还有两种比较常见的。

3.1 百分号%

%表示任何字符出现任意次数。例如，为了找出所有以词jet起头的产品，可使用以下SELECT 语句：

mysql> SELECT prod_price,prod_name,vend_id
    -> From products
    -> WHERE prod_name LIKE 'jet%';
+------------+--------------+---------+
| prod_price | prod_name    | vend_id |
+------------+--------------+---------+
|      35.00 | JetPack 1000 |    1005 |
|      55.00 | JetPack 2000 |    1005 |
+------------+--------------+---------+
2 rows in set (0.54 sec)

此例子使用了搜索模式'jet%'。在执行这条子句时，将检索任意以jet起头的词。%告诉MySQL接受jet之后的任意字符，不管它有多少字符。注意：按照MySQL的配置方式，搜索是区分大小写的。

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

mysql> SELECT prod_price,prod_name,vend_id
    -> From products
    -> WHERE prod_name LIKE '%anvil%';
+------------+--------------+---------+
| prod_price | prod_name    | vend_id |
+------------+--------------+---------+
|       5.99 | .5 ton anvil |    1001 |
|       9.99 | 1 ton anvil  |    1001 |
|      14.99 | 2 ton anvil  |    1001 |
+------------+--------------+---------+
3 rows in set (0.25 sec)

3.2 下划线 _

下划线只匹配单个字符而不是多个字符。如下所示.5 ton anvi的产品没有被匹配。

mysql> SELECT prod_price,prod_name,vend_id
    -> From products
    -> WHERE prod_name LIKE '_ ton anvil';
+------------+-------------+---------+
| prod_price | prod_name   | vend_id |
+------------+-------------+---------+
|       9.99 | 1 ton anvil |    1001 |
|      14.99 | 2 ton anvil |    1001 |
+------------+-------------+---------+
2 rows in set (0.24 sec)

如果输入__ ton anvil则只返回.5 ton anvil那个产品。

4. 分组过滤

4.1 数据分组

分组是在SELECT语句的GROUP BY子句中建立的。下面的例子中我们利用COUNT统计数据，分组。

mysql> SELECT vend_id, COUNT(*) AS num_probs
    -> FROM products
    -> GROUP BY vend_id;
+---------+-----------+
| vend_id | num_probs |
+---------+-----------+
|    1001 |         3 |
|    1002 |         2 |
|    1003 |         7 |
|    1005 |         2 |
+---------+-----------+
4 rows in set (0.55 sec)

GROUP BY子句有一些独特的地方

GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

4.1 过滤分组

为WHERE过滤指定的是行而不是分组，如果要对分组进行过滤，需要使用HAVING子句。

mysql> SELECT cust_id, COUNT(*) AS orders
    -> FROM orders
    -> GROUP BY cust_id
    -> HAVING COUNT(*)>=2;
+---------+--------+
| cust_id | orders |
+---------+--------+
|   10001 |      2 |
+---------+--------+
1 row in set (0.24 sec)

HAVING也可以和WHERE共用：

mysql> SELECT vend_id,COUNT(*) AS num_prods
    -> FROM products
    -> WHERE prod_price>=10
    -> GROUP BY vend_id
    -> HAVING COUNT(*)>=2;
+---------+-----------+
| vend_id | num_prods |
+---------+-----------+
|    1003 |         4 |
|    1005 |         2 |
+---------+-----------+
2 rows in set (0.24 sec)

4.3 SELECT子句顺序

目前已经介绍了WHERE, GROUP BY, HAVING, ORDER BY, LIMIT等子句，这些字句都可以共用，但顺序需要注意。

子句	作用
SELECT	选择一些列或表达式
FROM	要检索的表
WHERE	行级过滤
GROUP BY	分组说明
HAVING	组级过滤
ORDER BY	排序
LIMIT	输出的行数

5. 子查询

所谓子查询就是在WHERE中再嵌套WHERE，用IN连接

mysql> SELECT cust_id FROM orders
    -> WHERE order_num IN
    -> (SELECT order_num FROM orderitems
    -> WHERE prod_id = 'TNT2');
+---------+
| cust_id |
+---------+
|   10001 |
|   10004 |
+---------+
2 rows in set (0.56 sec)

当然也可以使用计算字段来进行过滤查询。下面这个例子中查询了两个表：customers和orders，将orders表中查询到的各个客户的订单数量，汇总。

mysql> SELECT cust_name, cust_state,
    -> (SELECT COUNT(*) FROM orders
    -> WHERE orders.cust_id = customers.cust_id)
    -> AS orders
    -> FROM customers ORDER BY cust_name;
+----------------+------------+--------+
| cust_name      | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc.    | MI         |      2 |
| E Fudd         | IL         |      1 |
| Mouse House    | OH         |      0 |
| Wascals        | IN         |      1 |
| Yosemite Place | AZ         |      1 |
+----------------+------------+--------+
5 rows in set (0.25 sec)

6. 组合查询

前面提到WHERE可以使用OR组合查询条件，这里再提供另一种查询方法UNION

mysql> SELECT vend_id, prod_id, prod_price
    -> FROM products
    -> WHERE prod_price<=5
    -> UNION
    -> SELECT vend_id, prod_id, prod_price
    -> FROM products
    -> WHERE vend_id IN(1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1002 | OL1     |       8.99 |
+---------+---------+------------+
8 rows in set (0.24 sec)

他等价于WHERE和OR的组合：

mysql> SELECT vend_id, prod_id, prod_price
    -> FROM products
    -> WHERE prod_price<=5 OR vend_id IN(1001,1002);
+---------+---------+------------+
| vend_id | prod_id | prod_price |
+---------+---------+------------+
|    1001 | ANV01   |       5.99 |
|    1001 | ANV02   |       9.99 |
|    1001 | ANV03   |      14.99 |
|    1003 | FC      |       2.50 |
|    1002 | FU1     |       3.42 |
|    1002 | OL1     |       8.99 |
|    1003 | SLING   |       4.49 |
|    1003 | TNT1    |       2.50 |
+---------+---------+------------+
8 rows in set (0.24 sec)

MySQL总结3-数据过滤

2019-11-28
数据库

MySQL总结3-数据过滤

1. WHERE的初级过滤

1.1 检查单个值

1.2 不匹配检查

1.3 范围值检查

1.4 空值检查

2. WHERE的高级过滤

2.1 组合WHERE语句

2.2 IN操作符

2.3 NOT操作符

3. 通配符过滤

3.1 百分号%

3.2 下划线 _

4. 分组过滤

4.1 数据分组

4.1 过滤分组

4.3 SELECT子句顺序

5. 子查询

6. 组合查询

MySQL总结3-数据过滤

1. WHERE的初级过滤

1.1 检查单个值

1.2 不匹配检查

1.3 范围值检查

1.4 空值检查

2. WHERE的高级过滤

2.1 组合WHERE语句

2.2 IN操作符

2.3 NOT操作符

3. 通配符过滤

3.1 百分号%

3.2 下划线 _

4. 分组过滤

4.1 数据分组

4.1 过滤分组

4.3 SELECT子句顺序

5. 子查询

6. 组合查询

感谢支持！