第8章、带搜索条件的查询

第8章、带搜索条件的查询

标签： MySQL是怎样使用的新版

我们上边介绍的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
`<=`	`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>

查询学号在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>

查询学号不在20180102~20180104这个区间内的所有学生信息，可以这么写：

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>

匹配列表中的元素

有时候搜索条件中指定的匹配值并不是单个值，而是一个列表，只要匹配到列表中的某一项就算匹配成功，这种情况可以使用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个字符的记录，所以这么写是查不出东西的。
> 小贴士： LIKE或者NOT LIKE操作符只用于字符串匹配。另外，通配符不能代表NULL，如果需要匹配NULL的话，需要使用IS NULL或者IS NOT NULL。

转义通配符

如果待匹配的字符串中本身就包含普通字符'%'或者'_'该咋办，怎么区分它是一个通配符还是一个普通字符呢？

答：如果匹配字符串中需要普通字符'%'或者'_'的话，需要在它们前边加一个反斜杠\来和通配符区分开来，也就是说：

'\%'代表普通字符'%'
'\_'代表普通字符'_' 比方说这样：

mysql> SELECT number, name, idnumber, major FROM student_info WHERE name LIKE ‘范\‘; Empty set (0.00 sec)

mysql>

由于student_info表中没有叫范_的学生，所以查询结果为空。

8、带搜索条件的查询