3/7 SELECT语句：过滤（WHERE）

序号	说明	操作
1	使用WHERE子句	WHERE
2	WHERE结合ORDER BY	顺序：FROM—WHERE—ORDER BY—LIMIT
3	WHERE子句操作符	=、<>、！=、<、<=、>、>=、BETWEEN
4	不匹配检查	<>、！=
5	范围值检查	BETWEEN n1 AND n2
6	空值检查	IS NULL

以下为数据库SQLTEST中的三张表，其结构和内容如下：

1.使用WHERE子句

在SELECT语句中，数据根据WHERE子句中指定的搜索条件进行过滤；
WHERE子句在表名（FROM子句）之后给出。

mysql> SELECT Stu_id,Lesson_id,Score
    -> FROM t_score
    -> WHERE Lesson_id = 'L001';
+--------+-----------+-------+
| Stu_id | Lesson_id | Score |
+--------+-----------+-------+
|      1 | L001      |    90 |
|      2 | L001      |    84 |
+--------+-----------+-------+

2.WHERE结合ORDER BY

WHERE子句位置：FROM—WHERE—ORDER BY。

mysql> SELECT Stu_id,Lesson_id,Score
    -> FROM t_score
    -> WHERE Lesson_id = 'L001'
    -> ORDER BY Score DESC;
+--------+-----------+-------+
| Stu_id | Lesson_id | Score |
+--------+-----------+-------+
|      1 | L001      |    90 |
|      2 | L001      |    84 |
+--------+-----------+-------+

3.WHERE子句操作符

操作符	说明
=	等于
<>	不等于
！=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	在指定的两个值之间

4.不匹配检查

显示Lesson_id值不等于“L001”的数据

mysql> SELECT Stu_id,Lesson_id,Score
    -> FROM t_score
    -> WHERE Lesson_id <> 'L001';
+--------+-----------+-------+
| Stu_id | Lesson_id | Score |
+--------+-----------+-------+
|      1 | L002      |    86 |
|      2 | L004      |    75 |
|      3 | L003      |    85 |
|      4 | L005      |    98 |
+--------+-----------+-------+

5.范围值检查

BETWEEN n1 AND n2

mysql> SELECT Stu_id,Lesson_id,Score
    -> FROM t_score
    -> WHERE Score BETWEEN 70 AND 90
    -> ORDER BY Score DESC;
+--------+-----------+-------+
| Stu_id | Lesson_id | Score |
+--------+-----------+-------+
|      1 | L001      |    90 |
|      1 | L002      |    86 |
|      3 | L003      |    85 |
|      2 | L001      |    84 |
|      2 | L004      |    75 |
+--------+-----------+-------+

6.空值检查

SELECT语句有一个特殊的WHERE子句，可用来检查具有NULL值的列，即为IS NULL子句；
NULL与不匹配：在通过过滤选择出不具有特定值的行时，可能也希望返回具有NULL值的行。但是不行，因为未知具有特殊的含义，数据库不知道它们是否匹配，所以在匹配过滤或不匹配过滤时不返回它们。

mysql> INSERT t_score(Stu_id,Lesson_id) VALUES(5,'L001');

mysql> SELECT Stu_id,Lesson_id,Score
    -> FROM t_score
    -> WHERE Score IS NULL;
+--------+-----------+-------+
| Stu_id | Lesson_id | Score |
+--------+-----------+-------+
|      5 | L001      |  NULL |
+--------+-----------+-------+

IS NULL与=NULL不同。

# 插入1个空行
mysql> INSERT t_stu_profile VALUES
    -> ();

# 没删除掉空行
mysql> DELETE
    -> FROM t_stu_profile
    -> WHERE Stu_id = NULL;
Query OK, 0 rows affected (0.00 sec)

# 删除掉空行
mysql> DELETE
    -> FROM t_stu_profile
    -> WHERE Stu_Name IS NULL;
Query OK, 1 row affected (0.14 sec)