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

       过滤数据所用到的表格数据，详见MySQL学习足迹记录02




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        | [e-mail]     |
|   10002 | Mouse House    | 333 Fromage Lane    | Columbus  | OH         | 43333    | USA          | Jerry Mouse  |NULL               |
|   10003 | Wascals        | 1 Sunny Place       | Muncie    | IN         | 42222    | USA          | Jim Jones    | [e-mail] |
|   10004 | Yosemite Place | 829 Riverside Drive | Phoenix   | AZ         | 88888    | USA          | Y Sam        | [e-mail]    |
|   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)