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 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