4.过滤数据

使用SELECT语句的WHERE子句指定搜索条件
一、WHERE子句操作符
1、= 等于
2、<> 不等于
3、!= 不等于
4、< 小于
5、<= 小于等于
6、> 大于
7、>= 大于等于
8、BETWEEN AND 在指定的两个值之间

SELECT prod_name,prod_price FROM products WHERE prod_name = 'fuses';

mysql> SELECT prod_name,prod_price FROM products WHERE prod_name = 'fuses';
+-----------+------------+
| prod_name | prod_price |
+-----------+------------+
| Fuses     |       3.42 |
+-----------+------------+
1 row in set (0.00 sec)

BETWEEN操作符，匹配范围中的所有值，包括指定的开始值和结束值

mysql> SELECT prod_name,prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
+----------------+------------+
| 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)

二、空值检查

mysql> select cust_id,cust_email from customers;
+---------+---------------------+
| cust_id | cust_email          |
+---------+---------------------+
|   10001 | ylee@coyote.com     |
|   10002 | NULL                |
|   10003 | rabbit@wascally.com |
|   10004 | sam@yosemite.com    |
|   10005 | NULL                |
+---------+---------------------+
5 rows in set (0.00 sec)

mysql> select cust_id,cust_email from customers where cust_email IS NOT NULL;
+---------+---------------------+
| cust_id | cust_email          |
+---------+---------------------+
|   10001 | ylee@coyote.com     |
|   10003 | rabbit@wascally.com |
|   10004 | sam@yosemite.com    |
+---------+---------------------+
3 rows in set (0.00 sec)

mysql> select cust_id,cust_email from customers where cust_email IS NULL;
+---------+------------+
| cust_id | cust_email |
+---------+------------+
|   10002 | NULL       |
|   10005 | NULL       |
+---------+------------+
2 rows in set (0.00 sec)

三、组合WHERE子句
AND、OR操作符、IN操作符、NOT操作符
操作符：用来联结或改变WHERE子句中的子句的关键字。也称为逻辑操作符
1、AND 操作符
用来指示检索满足所有给定条件的行
SELECT prod_id,prod_price,prod_name FROM products WHERE vend_id = 1003 AND prod_price <=10;
2、OR操作符
用来指示检索匹配任一条件
SELECT prod_name,prod_price FROM products WHERE vend_id = 1003 OR vend_id =1002;
3、计算次序
AND操作符会优先处理，再处理OR操作符。可以使用()改变处理次序
4、IN操作符
IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配
SELECT prod_name,prod_price,vend_id FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;

mysql> SELECT prod_name,prod_price,vend_id FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;
+----------------+------------+---------+
| prod_name      | prod_price | vend_id |
+----------------+------------+---------+
| Bird seed      |      10.00 |    1003 |
| Carrots        |       2.50 |    1003 |
| Detonator      |      13.00 |    1003 |
| Fuses          |       3.42 |    1002 |
| Oil can        |       8.99 |    1002 |
| Safe           |      50.00 |    1003 |
| Sling          |       4.49 |    1003 |
| TNT (1 stick)  |       2.50 |    1003 |
| TNT (5 sticks) |      10.00 |    1003 |
+----------------+------------+---------+
9 rows in set (0.00 sec)

5、NOT操作符
WHERE子句中用来否定后跟条件的关键字
NOT可以对IN、BETWEEN、EXISTS子句取反
SELECT prod_name,prod_price,vend_id FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;

mysql> SELECT prod_name,prod_price,vend_id FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;
+--------------+------------+---------+
| prod_name    | prod_price | vend_id |
+--------------+------------+---------+
| .5 ton anvil |       5.99 |    1001 |
| 1 ton anvil  |       9.99 |    1001 |
| 2 ton anvil  |      14.99 |    1001 |
| JetPack 1000 |      35.00 |    1005 |
| JetPack 2000 |      55.00 |    1005 |
+--------------+------------+---------+
5 rows in set (0.00 sec)

四、用通配符进行过滤
1、LIKE操作符
LIKE操作符指示MySQL,后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较
通配符：用来匹配值的一部分的特殊字符
搜索模式：由字面值、通配符或两者组合构成的搜索条件
1>%通配符
在搜索串中，%表示任何字符出现任意次数
'jet%'检索以jet开头的词
SELECT prod_id,prod_name FROM products WHERE prod_name LIKE 'jet%'
'%anvil%'匹配任何位置包含文本anvil的值
's%e'匹配以s开头以e结尾的值
'%'不能匹配NULL
2> _ 通配符
下划线_的用途与%一样，但是下划线只匹配单个字符而不是多个字符