MySQL学习足迹记录05--数据过滤--AND,OR,NOT,IN

缺乏、安全感 2022-01-11 05:05 201阅读 0赞

***过滤数据所用到的表格数据，详见***[MySQL学习足迹记录02][MySQL_02]

**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_02]: http://blog.csdn.net/piniheaven/article/details/9275303