bitsCN.com
MySQL学习足迹记录04--数据过滤--WHERE
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)
