MySQL learning footprint record 05 -- data filtering -- AND, OR, NOT, IN bitsCN.com
MySQL learning footprint record 05 -- data filtering -- AND, OR, NOT, IN
1. AND operator
* Search for rows matching all given conditions
eg: mysql> SELECT prod_id,prod_price,prod_name FROM products WHERE -> vend_id =1003 AND prod_price<=10;+---------+------------+----------------+| prod_id | prod_price | prod_name |+---------+------------+----------------+| FB | 10.00 | Bird seed || FC | 2.50 | Carrots || SLING | 4.49 | Sling || TNT1 | 2.50 | TNT (1 stick) || TNT2 | 10.00 | TNT (5 sticks) |+---------+------------+----------------+5 rows in set (0.00 sec)
2. OR operator
* Search for rows matching any given condition
eg: mysql> SELECT prod_name,prod_price FROM products -> WHERE vend_id=1002 or vend_id=1003;+----------------+------------+| prod_name | prod_price |+----------------+------------+| Detonator | 13.00 || Bird seed | 10.00 || Carrots | 2.50 || Fuses | 3.42 || Oil can | 8.99 || Safe | 50.00 || Sling | 4.49 || TNT (1 stick) | 2.50 || TNT (5 sticks) | 10.00 |+----------------+------------+9 rows in set (0.01 sec)
3. Computing order
* WHERE can contain any number of and or operators.
Eg: mysql> SELECT prod_name, prod_price FROM products-> WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price> = 10; # before processing the OR operator, the AND operator is preferentially processed + ---------------- + ------------ + | prod_name | prod_price | + ---------------- + ------------ + | Detonator | 13.00 | Bird seed | 10.00 | Fuses | 3.42 | Oil can | 8.99 | Safe | 50.00 | TNT (5 sticks) | 10.00 | + ---------------- + ------------ + 6 rows in set (0.00 sec)
4. use () to change the computing order
Eg: mysql> SELECT prod_name, prod_price FROM products-> WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price> = 10; # calculate OR first, re-calculate AND + ---------------- + ------------ + | prod_name | prod_price | + ---------------- + ------------ + | Detonator | 13.00 | Bird seed | 10.00 | Safe | 50.00 | TNT (5 sticks) | 10.00 | + ---------------- + ------------ + 4 rows in set (0.00 sec)
5. IN operator
* Used to specify the condition range
eg: mysql> SELECT prod_name,prod_price FROM products -> WHERE vend_id IN (1002,1003) -> ORDER BY prod_name;+----------------+------------+| prod_name | prod_price |+----------------+------------+| Bird seed | 10.00 || Carrots | 2.50 || Detonator | 13.00 || Fuses | 3.42 || Oil can | 8.99 || Safe | 50.00 || Sling | 4.49 || TNT (1 stick) | 2.50 || TNT (5 sticks) | 10.00 |+----------------+------------+9 rows in set (0.00 sec)
The preceding statement is equivalent:
mysql> SELECT prod_name,prod_price FROM products -> WHERE vend_id =1002 OR vend_id = 1003 -> ORDER BY prod_name;+----------------+------------+| prod_name | prod_price |+----------------+------------+| Bird seed | 10.00 || Carrots | 2.50 || Detonator | 13.00 || Fuses | 3.42 || Oil can | 8.99 || Safe | 50.00 || Sling | 4.49 || TNT (1 stick) | 2.50 || TNT (5 sticks) | 10.00 |+----------------+------------+9 rows in set (0.00 sec)
6. NOT operator
* Deny any conditions followed by it
eg: mysql> SELECT vend_id,prod_price FROM products -> WHERE vend_id NOT IN (1002,1003) -> ORDER BY prod_name;+---------+------------+| vend_id | prod_price |+---------+------------+| 1001 | 5.99 || 1001 | 9.99 || 1001 | 14.99 || 1005 | 35.00 || 1005 | 55.00 |+---------+------------+5 rows in set (0.00 sec)
BitsCN.com