MySQL learning footprint record 04 -- Data Filtering -- WHERE

MySQL learning footprint record 04 -- Data Filtering -- WHERE 1. Use the WHERE clause

 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: * when both order by and WHERE clauses are used, order by is placed after WHERE; otherwise, an error occurs. 2. The WHERE clause operator is equal to: = not equal to: <> or! = Less than: <less than or equal to: <= greater than:> greater than or equal to:> = BETWEEN two specified values 3. Detect a single value

Eg: mysql> SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses '; + ----------------- + ------------- + # single quotes are required for comparison with strings. | prod_name | prod_price | + ----------------- + ------------- + | Fuses | 3.42 | + ----------------- + ----------------

 

4. Smaller than the limit

  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. less than or equal to the limit

  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. mismatch check

Eg: mysql> SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003; # equivalent to SELECT vend_id, prod_name FROM products # WHERE vend_id! = 1003; + --------- + ------------ + | vend_id | prod_name | + --------- + -------------- + | 1001 |. 5 ton andevil | 1001 | 1 ton andevil | 1001 | 2 ton andevil | 1002 | Fuses | 1005 | JetPack 1000 | 1005 | JetPack 2000 | 1002 | Oil can | + --------- + -------------- + 7 rows in set (0.00 sec)

 

7. Check the range value ()

Eg: mysql> SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5.99 AND 10.00; + ---------------- + ------------ + # note, values on both sides of BETWEEN are closed intervals | prod_name | prod_price | + ---------------- + ---------- + |. 5 ton andevil | 5.99 | 1 ton andevil | 9.99 | Bird seed | 10.00 | Oil can | 8.99 | TNT (5 sticks) | 10.00 | + ---------------- + ------------ + 5 rows in set (0.00 sec)

 

8. NULL check (is null) * NULL: NULL (no value), not equal to 0, empty string or only contains NULL

Eg: List tables that contain null values: customers; mysql> SELECT * FROM customers; + --------- + response + ----------- + ---------- + response + | cust_id | cust_name | cust_address | response | cust_state | response | 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 MERs WHERE cust_email is null; + --------- + | cust_id | + --------- + | 10002 | 10005 | + --------- + 2 rows in set (0.00 sec)