MySQL學習足跡記錄05--數據過濾--AND,OR,NOT,IN 1.AND操作符 * 檢索匹配滿足所有給定條件的行
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操作符 *檢索匹配滿足給定任一條件的行
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.計算次序 *WHERE可以包含任意數目的AND和OR操作符。
eg:
mysql> SELECT prod_name,prod_price FROM products
-> WHERE vend_id=1002 OR vend_id=1003 AND prod_price>=10;
#在處理OR操作符之前，AND操作符會被優先處理
+----------------+------------+
| 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.用（）改變計算次序
eg:
mysql> SELECT prod_name,prod_price FROM products
-> WHERE (vend_id=1002 OR vend_id=1003) AND prod_price >= 10;
#先計算OR，再計算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操作符 *用來指定條件范圍
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)
上面的語句等效於：
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操作符 *否定它之後所跟的任何條件
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)
