MySQL学习足迹记录04--数据过滤--WHERE_MySQL

MySQL学习足迹记录04--数据过滤--WHERE

1.使用WHERE子句

 eg:  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 |+---------------+------------+

TIPs:

*在同时使用ORDER BY和WHERE子句时,ORDER BY位于WHERE之后，否则出错。

2.WHERE子句的操作符

等于：=

不等于：<>或！=

小于：<

小于等于：<=

大于：>

大于等于：>=

在指定的两个值之间 BETWEEN

3.检测单个值

  eg:      mysql> SELECT prod_name,prod_price FROM products WHERE prod_name='fuses';+-----------------+---------------+                                                          #和字符串比较需用单引号限定| prod_name | prod_price |+-----------------+---------------+| Fuses           |       3.42      |+-----------------+----------------+

4.小于限定

  eg:      mysql> SELECT prod_name,prod_price FROM products WHERE prod_price<10;+---------------+------------+| prod_name     | prod_price |+---------------+------------+| .5 ton anvil  |       5.99 || 1 ton anvil   |       9.99 || Carrots       |       2.50 || Fuses         |       3.42 || Oil can       |       8.99 || Sling         |       4.49 || TNT (1 stick) |       2.50 |+---------------+------------+7 rows in set (0.00 sec)

5.小于等于限定

  eg:    mysql> SELECT prod_name,prod_price FROM products WHERE prod_price<=10;+----------------+------------+| prod_name      | prod_price |+----------------+------------+| .5 ton anvil   |       5.99 || 1 ton anvil    |       9.99 || Bird seed      |      10.00 || Carrots        |       2.50 || Fuses          |       3.42 || Oil can        |       8.99 || Sling          |       4.49 || TNT (1 stick)  |       2.50 || TNT (5 sticks) |      10.00 |+----------------+------------+9 rows in set (0.00 sec)

6.不匹配检查

  eg:   mysql> SELECT vend_id,prod_name FROM products WHERE vend_id <>1003;                                     #等效于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.00 sec)

7.范围值检查（BETWEEN）

  eg:   mysql> SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5.99 AND 10.00;+----------------+------------+                #注意，BETWEEN两边的取值为闭区间| 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)

8.空值检查（IS NULL)

*NULL：无值（no value),并不等于0，空字符串或仅仅包含空

 eg:   先列出包含空值的表：customers;  mysql> SELECT * FROM customers;+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+| cust_id | cust_name      | cust_address        | cust_city | cust_state | cust_zip | cust_country | cust_contact | cust_email          |+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+|   10001 | Coyote Inc.    | 200 Maple Lane      | Detroit   | MI         | 44444    | USA          | Y Lee        | ylee@coyote.com     ||   10002 | Mouse House    | 333 Fromage Lane    | Columbus  | OH         | 43333    | USA          | Jerry Mouse  |NULL                ||   10003 | Wascals        | 1 Sunny Place       | Muncie    | IN         | 42222    | USA          | Jim Jones    | rabbit@wascally.com ||   10004 | Yosemite Place | 829 Riverside Drive | Phoenix   | AZ         | 88888    | USA          | Y Sam        | sam@yosemite.com    ||   10005 | E Fudd         | 4545 53rd Street    | Chicago   | IL         | 54545    | USA          | E Fudd       |NULL                |+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+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.00 sec)