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