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

页面导航:首页 > 数据库 > Mysql > MySQL学习足迹记录05--数据过滤--AND,OR,NOT,IN

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

来源: 作者: 时间:2016-02-18 10:28 【

MySQL学习足迹记录05--数据过滤--AND,OR,NOT,IN1.AND操作符 * 检索匹配满足所有给定条件的行eg: mysql SELECT prod_id,prod_price,prod_name FROM products WHERE - vend_id =1003 AN...
MySQL学习足迹记录05--数据过滤--AND,OR,NOT,IN
 
1.AND操作符
   * 检索匹配满足所有给定条件的行
  
eg:
   > 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)

 

 
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<