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

最新推荐文章于 2022-03-20 18:07:19 发布

learn123_net

最新推荐文章于 2022-03-20 18:07:19 发布

阅读量780

点赞数

分类专栏： MySQL学习足迹记录文章标签： MySQL AND OR NOT IN

本文链接：https://blog.csdn.net/piniheaven/article/details/9283815

版权

MySQL学习足迹记录专栏收录该内容

14 篇文章 0 订阅

订阅专栏

过滤数据所用到的表格数据，详见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)