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)
--转自
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2014-11-5 11:10:54 |
赞(
操作
