《MySQL必知必会》—— 6.过滤数据 7.数据过滤

最新推荐文章于 2024-09-13 11:47:46 发布

Harmin

最新推荐文章于 2024-09-13 11:47:46 发布

阅读量305

点赞数

分类专栏： mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/youshiwukong1524/article/details/105915069

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

六、过滤数据

使用WHERE字句

指定搜索条件（过滤条件）

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.02 sec)

ps. ORDER BY字句应该在WHERE字句的后面。

WHERE字句操作符

WHERE字句操作符
操作符	说明
=	等于
<>	不等于
!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN ... AND ...	在指定的两个值之间包括开始值和结束值
IS NULL	空值检查不是值为0，是没有值

ps. 限定字符串的时候需要加单引号.

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE prod_name = 'fuses';
+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| Fuses     |       3.42 |
+-----------+------------+
1 row in set (0.00 sec)

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE prod_price < 5;
+---------------+------------+
| prod_name     | prod_price |
+---------------+------------+
| Carrots       |       2.50 |
| Fuses         |       3.42 |
| Sling         |       4.49 |
| TNT (1 stick) |       2.50 |
+---------------+------------+
4 rows in set (0.00 sec)

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        |
|    1002 | Oil can      |
|    1005 | JetPack 1000 |
|    1005 | JetPack 2000 |
+---------+--------------+
7 rows in set (0.00 sec)

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.00 sec)

mysql> SELECT cust_id
    -> FROM customers
    -> WHERE cust_email IS NULL;
+---------+
| cust_id |
+---------+
|   10002 |
|   10005 |
+---------+
2 rows in set (0.01 sec)

mysql> SELECT cust_id
    -> FROM customers
    -> WHERE cust_email IS NOT NULL;
+---------+
| cust_id |
+---------+
|   10001 |
|   10003 |
|   10004 |
+---------+
3 rows in set (0.00 sec)

七、数据过滤

7.1 组合WHERE字句

多个WHERE字句用：AND 或者 OR的方式使用

AND操作符

AND：返回满足所有给定条件的行。当有多个过滤条件时，每添加一条使用一个AND。

检索：由供应商1003制造的价格小于等10美元的所有产品的名称和价格。

mysql> SELECT prod_id, prod_price, prod_name
    -> FROM products
    -> WHERE vend_id = 1003 AND prod_price <= 10;
+---------+------------+----------------+
| prod_id | prod_price | prod_name      |
+---------+------------+----------------+
| FB      |      10.00 | Bird seed      |
| FC      |       2.50 | Carrots        |
| SLING   |       4.49 | Sling          |
| TNT1    |       2.50 | TNT (1 stick)  |
| TNT2    |      10.00 | TNT (5 sticks) |
+---------+------------+----------------+
5 rows in set (0.01 sec)

OR操作符

OR：检索匹配任一条件的行，而不是所有条件。

检索：任何制造商是1002或1003中的产品名字和价格。如果写成AND，将没有数据返回。

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE vend_id = 1002 OR vend_id = 1003;
+----------------+------------+
| prod_name      | prod_price |
+----------------+------------+
| Fuses          |       3.42 |
| Oil can        |       8.99 |
| Detonator      |      13.00 |
| Bird seed      |      10.00 |
| Carrots        |       2.50 |
| Safe           |      50.00 |
| Sling          |       4.49 |
| TNT (1 stick)  |       2.50 |
| TNT (5 sticks) |      10.00 |
+----------------+------------+
9 rows in set (0.00 sec)

mysql> SELECT prod_name, prod_price
    -> FROM products
    -> WHERE vend_id = 1002 AND vend_id = 1003;
Empty set (0.00 sec)

计算次序

检索：价格是10美元（含）以上且由1002或1003制造的的所有产品。

加入不加括号来约束：出现了价格小于10的产品，因为AND的优先级大于OR的优先级；

所以该WHERE语句的实际结果是：制造商是1002 或者价格是10美元（含）以上且由1003制造的产品

mysql> SELECT prod_name, prod_price, vend_id
    -> FROM products
    -> WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;
+----------------+------------+---------+
| prod_name      | prod_price | vend_id |
+----------------+------------+---------+
| Fuses          |       3.42 |    1002 |
| Oil can        |       8.99 |    1002 |
| Detonator      |      13.00 |    1003 |
| Bird seed      |      10.00 |    1003 |
| Safe           |      50.00 |    1003 |
| TNT (5 sticks) |      10.00 |    1003 |
+----------------+------------+---------+
6 rows in set (0.00 sec)

正确结果如下：

mysql> SELECT prod_name, prod_price, vend_id
    -> FROM products
    -> WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;
+----------------+------------+---------+
| prod_name      | prod_price | vend_id |
+----------------+------------+---------+
| Detonator      |      13.00 |    1003 |
| Bird seed      |      10.00 |    1003 |
| Safe           |      50.00 |    1003 |
| TNT (5 sticks) |      10.00 |    1003 |
+----------------+------------+---------+
4 rows in set (0.00 sec)

ps: 任何时候使用具有AND和OR操作符的WHERE字句，都应该使用圆括号明确地分组。不要过分依赖默认计算顺序。

7.2 IN操作符

IN操作符用来指定条件范围，范围中每个条件都可以进行匹配。功能上等于OR，但是语法更直观、更快、可以包含其他SELECT语句。

IN取合法值的用逗号分割，包括在圆括号内。

mysql> SELECT prod_name, prod_price, vend_id
    -> FROM products
    -> WHERE vend_id IN (1002, 1003)
    -> ORDER BY prod_name;
+----------------+------------+---------+
| prod_name      | prod_price | vend_id |
+----------------+------------+---------+
| Bird seed      |      10.00 |    1003 |
| Carrots        |       2.50 |    1003 |
| Detonator      |      13.00 |    1003 |
| Fuses          |       3.42 |    1002 |
| Oil can        |       8.99 |    1002 |
| Safe           |      50.00 |    1003 |
| Sling          |       4.49 |    1003 |
| TNT (1 stick)  |       2.50 |    1003 |
| TNT (5 sticks) |      10.00 |    1003 |
+----------------+------------+---------+
9 rows in set (0.00 sec)

7.3 NOT 操作符

NOT：否定它之后的任何条件。和 IN 联合使用找出与条件列表不匹配的行。

MySQL中的NOT：支持使用NOT对IN，BETWEEN和EXISTS字句取反。这和其他多数DBMS允许使用NOT对各种条件取反不大一样。

检索：除了1002和1003之外的所有供应商的产品。

mysql> SELECT prod_name, prod_price, vend_id
    -> FROM products
    -> WHERE vend_id NOT IN (1002, 1003)
    -> ORDER BY prod_name;
+--------------+------------+---------+
| prod_name    | prod_price | vend_id |
+--------------+------------+---------+
| .5 ton anvil |       5.99 |    1001 |
| 1 ton anvil  |       9.99 |    1001 |
| 2 ton anvil  |      14.99 |    1001 |
| JetPack 1000 |      35.00 |    1005 |
| JetPack 2000 |      55.00 |    1005 |
+--------------+------------+---------+
5 rows in set (0.00 sec)