MySQL学习足迹记录05--数据过滤--AND,OR,NOT,IN_MySQL
程序员文章站
2022-06-05 23:02:28
...
bitsCN.com
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
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)
bitsCN.com