MySQL學習足跡記錄04--數據過濾--WHERE

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)