过滤数据
我们上边介绍的student_info
、student_score
表中的记录都很少,但是实际应用中的表里可能存储几千万条,甚至上亿条记录。而且我们通常并不是对所有的记录都感兴趣,只是想查询到符合某些条件的那些记录。比如我们只想查询名字为范剑
的学生基本信息,或者计算机学院
的学生都有哪些什么的,这些条件也被称为搜索条件
或者过滤条件
,当某条记录符合搜索条件
时,它将被放入结果集中。
简单搜索条件
我们需要把搜索条件
放在WHERE
语句中,比如我们想查询student_info
表中名字是范剑
的学生的一些信息,可以这么写:
mysql> SELECT number, name, id_number, major FROM student_info WHERE name = '范剑';
+----------+--------+--------------------+-----------------+
| number | name | id_number | major |
+----------+--------+--------------------+-----------------+
| 20180105 | 范剑 | 181048199308156368 | 飞行器设计 |
+----------+--------+--------------------+-----------------+
1 row in set (0.01 sec)
mysql>
复制代码
这个例子中的搜索条件
就是name = '范剑'
,也就是当记录中的name
列的值是'范剑'
的时候,该条记录的number
、name
、id_number
、major
这些字段才可以被放入结果集。像name = '范剑'
这种搜索条件
称为精确匹配,=
称为条件操作符
。我们看MySQL
中都有哪些简单的条件操作符:
操作符 | 示例 | 描述 |
---|---|---|
= | a = b | 等于 |
<> 或者!= | a <> b | 不等于 |
< | a < b | 小于 |
<= | a <= b | 不大于 |
> | a > b | 大于 |
>= | a >= b | 不小于 |
BETWEEN | a BETWEEN b AND c | 满足 b <= a <= c |
NOT BETWEEN | a NOT BETWEEN b AND c | 不满足 b <= a <= c |
我们想查询学号大于20180103
的学生信息可以这么写:
mysql> SELECT number, name, id_number, major FROM student_info WHERE number > 20180103;
+----------+-----------+--------------------+-----------------+
| number | name | id_number | major |
+----------+-----------+--------------------+-----------------+
| 20180104 | 史珍香 | 141992199701078600 | 软件工程 |
| 20180105 | 范剑 | 181048199308156368 | 飞行器设计 |
| 20180106 | 朱逸群 | 197995199501078445 | 电子信息 |
+----------+-----------+--------------------+-----------------+
3 rows in set (0.01 sec)
mysql>
复制代码
查询专业不是计算机科学与工程
的一些学生信息可以这么写:
mysql> SELECT number, name, id_number, major FROM student_info WHERE major != '计算机科学与工程';
+----------+-----------+--------------------+-----------------+
| number | name | id_number | major |
+----------+-----------+--------------------+-----------------+
| 20180103 | 范统 | 17156319980116959X | 软件工程 |
| 20180104 | 史珍香 | 141992199701078600 | 软件工程 |
| 20180105 | 范剑 | 181048199308156368 | 飞行器设计 |
| 20180106 | 朱逸群 | 197995199501078445 | 电子信息 |
+----------+-----------+--------------------+-----------------+
4 rows in set (0.00 sec)
mysql>
复制代码
需要注意的是BETWEEN ... AND ...
操作符的使用,它表示一个范围,比方说我们想查找学号在20180102
~20180104
间的学生信息,可以这么写:
mysql> SELECT number, name, id_number, major FROM student_info WHERE number BETWEEN 20180102 AND 20180104;
+----------+-----------+--------------------+--------------------------+
| number | name | id_number | major |
+----------+-----------+--------------------+--------------------------+
| 20180102 | 杜琦燕 | 151008199801178529 | 计算机科学与工程 |
| 20180103 | 范统 | 17156319980116959X | 软件工程 |
| 20180104 | 史珍香 | 141992199701078600 | 软件工程 |
+----------+-----------+--------------------+--------------------------+
3 rows in set (0.00 sec)
mysql>
复制代码
如果想查询指定范围之外的数据记录,可以使用NOT BETWEEN ... AND ...
的语法,比如这样:
mysql> SELECT number, name, id_number, major FROM student_info WHERE number NOT BETWEEN 20180102 AND 20180104;
+----------+-----------+--------------------+--------------------------+
| number | name | id_number | major |
+----------+-----------+--------------------+--------------------------+
| 20180101 | 杜子腾 | 158177199901044792 | 计算机科学与工程 |
| 20180105 | 范剑 | 181048199308156368 | 飞行器设计 |
| 20180106 | 朱逸群 | 197995199501078445 | 电子信息 |
+----------+-----------+--------------------+--------------------------+
3 rows in set (0.00 sec)
mysql>
复制代码
这样就可以查出学号不在20180102
~20180104
这个区间内的所有学生信息。
多值匹配
有时候指定的匹配值并不是单个值,而是一个列表,只要匹配到列表中的某一项就算匹配成功,这种情况可以使用IN
操作符:
操作符 | 示例 | 描述 |
---|---|---|
IN | a IN (b1, b2, ...) | a是b1, b2, ... 中的某一个 |
NOT IN | a NOT IN (b1, b2, ...) | a不是b1, b2, ... 中的任意一个 |
比如我们想查询软件工程
和飞行器设计
专业的学生信息,可以这么写:
mysql> SELECT number, name, id_number, major FROM student_info WHERE major IN ('软件工程', '飞行器设计');
+----------+-----------+--------------------+-----------------+
| number | name | id_number | major |
+----------+-----------+--------------------+-----------------+
| 20180103 | 范统 | 17156319980116959X | 软件工程 |
| 20180104 | 史珍香 | 141992199701078600 | 软件工程 |
| 20180105 | 范剑 | 181048199308156368 | 飞行器设计 |
+----------+-----------+--------------------+-----------------+
3 rows in set (0.01 sec)
mysql>
复制代码
如果想查询不是这两个专业的学生的信息,可以这么写:
mysql> SELECT number, name, id_number, major FROM student_info WHERE major NOT IN ('软件工程', '飞行器设计');
+----------+-----------+--------------------+--------------------------+
| number | name | id_number | major |
+----------+-----------+--------------------+--------------------------+
| 20180101 | 杜子腾 | 158177199901044792 | 计算机科学与工程 |
| 20180102 | 杜琦燕 | 151008199801178529 | 计算机科学与工程 |
| 20180106 | 朱逸群 | 197995199501078445 | 电子信息 |
+----------+-----------+--------------------+--------------------------+
3 rows in set (0.00 sec)
mysql>
复制代码
NULL
值检查
我们前边说过,NULL
代表没有值,意味着你并不知道该列应该填入什么数据,在判断某一列是否为NULL
的时候并不能单纯的使用=
操作符,而是需要专业判断值是否是NULL
的操作符:
操作符 | 示例 | 描述 |
---|---|---|
IS NULL | a IS NULL | a的值是NULL |
IS NOT NULL | a IS NOT NULL | a的值不是NULL |
比如我们想看一下student_info
表的name
列是NULL
的学生记录有哪些,可以这么写:
mysql> SELECT number, name, id_number, major FROM student_info WHERE name IS NULL;
Empty set (0.00 sec)
mysql>
复制代码
由于所有记录的name
列都不是NULL
值,所以最后结果是空的,我们看一下查询name
列不是NULL
值的方式:
mysql> SELECT number, name, id_number, major FROM student_info WHERE name IS NOT NULL;
+----------+-----------+--------------------+--------------------------+
| number | name | id_number | major |
+----------+-----------+--------------------+--------------------------+
| 20180101 | 杜子腾 | 158177199901044792 | 计算机科学与工程 |
| 20180102 | 杜琦燕 | 151008199801178529 | 计算机科学与工程 |
| 20180103 | 范统 | 17156319980116959X | 软件工程 |
| 20180104 | 史珍香 | 141992199701078600 | 软件工程 |
| 20180105 | 范剑 | 181048199308156368 | 飞行器设计 |
| 20180106 | 朱逸群 | 197995199501078445 | 电子信息 |
+----------+-----------+--------------------+--------------------------+
6 rows in set (0.00 sec)
mysql>
复制代码
name
列不是NULL
值的记录就被查询出来啦!
再次强调一遍,不能直接使用普通的操作符来与NULL
值进行比较,必须使用IS NULL
或者IS NOT NULL
!
多个搜索条件的查询
上边介绍的都是指定单个的搜索条件的查询,我们也可以在一次查询中指定多个搜索条件。
AND操作符
在给定多个搜索条件的时候,我们有时需要某条记录只有在符合所有搜索条件的时候,这条记录才可以被加入到结果集当中,这种情况我们可以使用AND
操作符来连接多个搜索条件。比如我们想从student_score
表中找出科目为'母猪的产后护理'
并且成绩大于75
分的记录,可以这么写:
mysql> SELECT * FROM student_score WHERE subject = '母猪的产后护理' AND score > 75;
+----------+-----------------------+-------+
| number | subject | score |
+----------+-----------------------+-------+
| 20180101 | 母猪的产后护理 | 78 |
| 20180102 | 母猪的产后护理 | 100 |
+----------+-----------------------+-------+
2 rows in set (0.00 sec)
mysql>
复制代码
其中的subject = '母猪的产后护理'
和score > 75
是两个搜索条件,我们使用AND
操作符把这两个搜索条件连接起来表示只有当两个条件都满足的记录才能被加入到结果集。
OR操作符
在给定多个搜索条件的时候,我们有时需要某条记录在符合某一个搜索条件的时候,这条记录就可以被加入到结果集当中,这种情况我们可以使用OR
操作符来连接多个搜索条件。比如我们想从student_score
表中找出成绩大于95
分或者小于55
分的记录,可以这么写:
mysql> SELECT * FROM student_score WHERE score > 95 OR score < 55;
+----------+-----------------------------+-------+
| number | subject | score |
+----------+-----------------------------+-------+
| 20180102 | 母猪的产后护理 | 100 |
| 20180102 | 论萨达姆的战争准备 | 98 |
| 20180104 | 论萨达姆的战争准备 | 46 |
+----------+-----------------------------+-------+
3 rows in set (0.00 sec)
mysql>
复制代码
更复杂的搜索条件的组合
如果我们需要在某个查询中指定很多的搜索条件,比方说我们想从student_score
表中找出课程为'论萨达姆的战争准备'
,并且成绩大于95
分或者小于55
分的记录,那我们可能会这么写:
mysql> SELECT * FROM student_score WHERE score > 95 OR score < 55 AND subject = '论萨达姆的战争准备';
+----------+-----------------------------+-------+
| number | subject | score |
+----------+-----------------------------+-------+
| 20180102 | 母猪的产后护理 | 100 |
| 20180102 | 论萨达姆的战争准备 | 98 |
| 20180104 | 论萨达姆的战争准备 | 46 |
+----------+-----------------------------+-------+
3 rows in set (0.00 sec)
mysql>
复制代码
为什么结果中仍然会有'母猪的产后护理'
课程的记录呢?因为:AND操作符的优先级默认高于OR操作符,也就是说在判断某条记录是否符合条件时会先执行AND操作符两边的搜索条件。所以
score > 95 OR score < 55 AND subject = '论萨达姆的战争准备'
复制代码
可以被看作下边这两个条件中任一条件成立则整个式子成立:
-
score > 95
-
score < 55 AND subject = '论萨达姆的战争准备'
因为结果集中subject
是'母猪的产后护理'
的记录中score
值为100
,符合第1个条件,所以整条记录会被加到结果集中。为了避免这种尴尬,在一个查询中有多个搜索条件时最好使用小括号()
来显式的指定各个搜索条件的执行顺序,比如上边的例子可以写成下边这样:
mysql> SELECT * FROM student_score WHERE (score > 95 OR score < 55) AND subject = '论萨达姆的战争准备';
+----------+-----------------------------+-------+
| number | subject | score |
+----------+-----------------------------+-------+
| 20180102 | 论萨达姆的战争准备 | 98 |
| 20180104 | 论萨达姆的战争准备 | 46 |
+----------+-----------------------------+-------+
2 rows in set (0.00 sec)
mysql>
复制代码
通配符
有时候我们并不能精确的描述我们要查询的东西,比方说我们只是想看看姓'杜'
的学生信息,而不能精确的描述出这些姓'杜'
的同学的完整姓名,我们称这种查询为模糊查询
。MySQL
中使用下边这两个操作符来支持模糊查询
:
操作符 | 示例 | 描述 |
---|---|---|
LIKE | a LIKE b | a匹配b |
NOT LIKE | a NOT LIKE b | a不匹配b |
既然我们不能完整描述要查询的信息,那就用某个符号来替代这些模糊的信息,这个符号就被称为通配符
。MySQL
中支持下边这两个通配符
:
-
%
:代表任意一个字符串。比方说我们想查询
student_info
表中name
以'杜'
开头的记录,我们可以这样写:mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '杜%'; +----------+-----------+--------------------+--------------------------+ | number | name | id_number | major | +----------+-----------+--------------------+--------------------------+ | 20180101 | 杜子腾 | 158177199901044792 | 计算机科学与工程 | | 20180102 | 杜琦燕 | 151008199801178529 | 计算机科学与工程 | +----------+-----------+--------------------+--------------------------+ 2 rows in set (0.00 sec) mysql> 复制代码
或者我们只知道学生名字里边包含了一个
'香'
字,那我们可以这么查:mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '%香%'; +----------+-----------+--------------------+--------------+ | number | name | id_number | major | +----------+-----------+--------------------+--------------+ | 20180104 | 史珍香 | 141992199701078600 | 软件工程 | +----------+-----------+--------------------+--------------+ 1 row in set (0.00 sec) mysql> 复制代码
-
_
:代表任意一个字符。有的时候我们知道要查询的字符串中有多少个字符,而使用
%
时匹配的范围太大,我们就可以用_
来做通配符。就像是支付宝的万能福卡,一张万能福卡能且只能代表任意一张福卡(也就是它不能代表多张福卡)。比方说我们想查询姓
'范'
,并且姓名只有2个字符的记录,可以这么写:mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '范_'; +----------+--------+--------------------+-----------------+ | number | name | id_number | major | +----------+--------+--------------------+-----------------+ | 20180103 | 范统 | 17156319980116959X | 软件工程 | | 20180105 | 范剑 | 181048199308156368 | 飞行器设计 | +----------+--------+--------------------+-----------------+ 2 rows in set (0.00 sec) mysql> 复制代码
不过下边这个查询却什么都没有查到:
mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '杜_'; Empty set (0.00 sec) mysql> 复制代码
这是因为一个
_
只能代表一个字符(%
是代表任意一个字符串),并且student_info
表中并没有姓'杜'
并且姓名长度是2个字符的记录,所以这么写是查不出东西的。
转义通配符
如果我们匹配的字符串中就包含普通字符'%'
或者'_'
该咋办,怎么区分它是一个通配符还是一个普通字符呢?
答:如果匹配字符串中需要普通字符'%'
或者'_'
的话,需要在它们前边加一个反斜杠\
以和通配符区分开来:
'\%'
代表普通字符'%'
'\_'
代表普通字符'_'
比方说这样:
mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '范\_';
Empty set (0.00 sec)
mysql>
复制代码
由于student_info
表中没有叫范_
的学生,所以查询结果为空。
注意事项
使用通配符时需要特别注意一下,通配符不能代表NULL
,如果需要匹配NULL
的话,需要使用IS NULL
或者IS NOT NULL
!