欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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
相关标签: mysql 记录