MySQL学习足迹记录04--数据过滤--WHERE

- 日理万妓 2022-01-11 02:23 295阅读 0赞

***过滤数据所用到的表格数据，详见[MySQL学习足迹记录02][MySQL_02]*  
  
  
  
  
1.使用WHERE子句**  
**eg:  
mysql> SELECT prod\_name,prod\_price FROM products WHERE prod\_price=2.50;**  
\+---------------+------------+  
| prod\_name | prod\_price |  
\+---------------+------------+  
| Carrots | 2.50 |  
| TNT (1 stick) | 2.50 |  
\+---------------+------------+  
  
  
 **TIPs:  
\*在同时使用ORDER BY和WHERE子句时,ORDER BY位于WHERE之后，否则出错。  
  
  
2.WHERE子句的操作符  
等于：=  
不等于：<>或！=  
小于：<  
小于等于：<=  
大于：>  
大于等于：>=  
在指定的两个值之间 BETWEEN  
  
  
3.检测单个值  
 eg:  
mysql> SELECT prod\_name,prod\_price FROM products WHERE prod\_name='fuses';**  
\+-----------------+---------------+    **\#和字符串比较需用单引号限定**  
| prod\_name | prod\_price |  
\+-----------------+---------------+  
| Fuses | 3.42 |  
\+-----------------+----------------+  
  
  
  
  
**4.小于限定  
 eg:  
 mysql> SELECT prod\_name,prod\_price FROM products WHERE prod\_price<10;**  
\+---------------+------------+  
| prod\_name | prod\_price |  
\+---------------+------------+  
| .5 ton anvil | 5.99 |  
| 1 ton anvil | 9.99 |  
| Carrots | 2.50 |  
| Fuses | 3.42 |  
| Oil can | 8.99 |  
| Sling | 4.49 |  
| TNT (1 stick) | 2.50 |  
\+---------------+------------+  
7 rows in set (0.00 sec)  
  
  
  
**5.小于等于限定  
 eg:  
mysql> SELECT prod\_name,prod\_price FROM products WHERE prod\_price<=10;**   
\+----------------+------------+  
| prod\_name | prod\_price |  
\+----------------+------------+  
| .5 ton anvil | 5.99 |  
| 1 ton anvil | 9.99 |  
| Bird seed | 10.00 |  
| Carrots | 2.50 |  
| Fuses | 3.42 |  
| Oil can | 8.99 |  
| Sling | 4.49 |  
| TNT (1 stick) | 2.50 |  
| TNT (5 sticks) | 10.00 |  
\+----------------+------------+  
9 rows in set (0.00 sec)  
  
  
  
  
**6.不匹配检查  
 eg:  
mysql> SELECT vend\_id,prod\_name FROM products WHERE vend\_id <>1003;  
\#等效于SELECT vend\_id,prod\_name FROM products  
\# WHERE vend\_id ！= 1003;**  
   
  
\+---------+--------------+  
| vend\_id | prod\_name |  
\+---------+--------------+  
| 1001 | .5 ton anvil |  
| 1001 | 1 ton anvil |  
| 1001 | 2 ton anvil |  
| 1002 | Fuses |  
| 1005 | JetPack 1000 |  
| 1005 | JetPack 2000 |  
| 1002 | Oil can |  
\+---------+--------------+  
7 rows in set (0.00 sec)  
  
  
  
  
**7.范围值检查（BETWEEN）  
 eg:  
mysql> SELECT prod\_name,prod\_price FROM products WHERE prod\_price BETWEEN 5.99 AND 10.00;  
\+----------------+------------+   \#注意，BETWEEN两边的取值为闭区间**   
| prod\_name | prod\_price |  
\+----------------+------------+  
| .5 ton anvil | 5.99 |  
| 1 ton anvil | 9.99 |  
| Bird seed | 10.00 |  
| Oil can | 8.99 |  
| TNT (5 sticks) | 10.00 |  
\+----------------+------------+  
5 rows in set (0.00 sec)  
  
  
  
  
**8.空值检查（IS NULL)  
\*NULL：无值（no value),并不等于0，空字符串或仅仅包含空  
 eg:  
先列出包含空值的表：customers;  
 mysql> SELECT \* FROM customers;**  
\+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+  
| cust\_id | cust\_name | cust\_address | cust\_city | cust\_state | cust\_zip | cust\_country | cust\_contact | cust\_email |  
\+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+  
| 10001 | Coyote Inc. | 200 Maple Lane | Detroit | MI | 44444 | USA | Y Lee | ylee@coyote.com |  
| 10002 | Mouse House | 333 Fromage Lane | Columbus | OH | 43333 | USA | Jerry Mouse | NULL   |  
| 10003 | Wascals | 1 Sunny Place | Muncie | IN | 42222 | USA | Jim Jones | rabbit@wascally.com |  
| 10004 | Yosemite Place | 829 Riverside Drive | Phoenix | AZ | 88888 | USA | Y Sam | sam@yosemite.com |  
| 10005 | E Fudd | 4545 53rd Street | Chicago | IL | 54545 | USA | E Fudd | NULL   |  
\+---------+----------------+---------------------+-----------+------------+----------+--------------+--------------+---------------------+  
5 rows in set (0.00 sec)  
  
  
**mysql> SELECT cust\_id FROM customers WHERE cust\_email IS NULL;**  
\+---------+  
| cust\_id |  
\+---------+  
| 10002 |  
| 10005 |  
\+---------+  
2 rows in set (0.00 sec)

[MySQL_02]: http://blog.csdn.net/piniheaven/article/details/9275303