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)

 

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。