MySQL 从零开始：07 数据搜索与搜索
数据库表中包含了很多数据，一般我们不会检索表中的所有行。通常会根据特定的条件来提取出表的子集，此时我们需要指定搜索条件（search criteria），搜索条件也叫作过滤条件（filter condition）。
1、WHERE 子句
在 SELECT 语句中，使用 WHERE 子句指定搜索条件，其语法为：
SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....- 查询语句可以使用一个或者多个表，表之间使用逗号分隔；
- 可以在WHERE 子句中指定任何条件；
- 可以使用 AND 或者 OR 指定一个或多个条件；
- WHERE 子句也可以用于 DELETE 或 UPDATE 命令；
1.1 WHERE 子句操作符
操作符 | 说明 |
|---|---|
= | 等于 |
<>或!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 大于 |
>= | 大于等于 |
BETWEEN | 在指定的两个值之间 |
1.2 检查单值
假如我们想要查看 李四 的所有科目的成绩，那么我们就以 name = '李四' 作为筛选条件：
mysql> SELECT name, math_score, chinese_score, english_score
-> FROM score
-> WHERE name = '李四';
+------+------------+---------------+---------------+
| name | math_score | chinese_score | english_score |
+------+------------+---------------+---------------+
| 李四 | 74 | 85 | 97 |
+------+------------+---------------+---------------+
1 row in set (0.00 sec)查看数学成绩高于（包含）90分的同学名单，以 math_score >= 90 作为筛选条件：
mysql> SELECT name, math_score
-> FROM score
-> WHERE math_score >= 90;
+------+------------+
| name | math_score |
+------+------------+
| 吴七 | 94 |
| 陈十 | 96 |
+------+------------+
2 rows in set (0.00 sec)1.3 检查不匹配值
假如我们已经知道李四的成绩了，我们想要查看其它同学的所有成绩，那么以 name <> '李四' 或者 name != '李四' 作为筛选条件：
mysql> SELECT name, math_score, chinese_score, english_score
-> FROM score
-> WHERE name <> '李四';
+------+------------+---------------+---------------+
| name | math_score | chinese_score | english_score |
+------+------------+---------------+---------------+
| 赵一 | 87 | 90 | 79 |
| 钱二 | 80 | 87 | 92 |
| 孙三 | 89 | 94 | 90 |
| 王五 | 79 | 71 | 91 |
| 周六 | 73 | 84 | 77 |
| 吴七 | 94 | 90 | 93 |
| 郑八 | 87 | 98 | 97 |
| 冯九 | 69 | 82 | 83 |
| 陈十 | 96 | 97 | 96 |
+------+------------+---------------+---------------+
9 rows in set (0.00 sec)
mysql> SELECT name, math_score, chinese_score, english_score
-> FROM score
-> WHERE name != '李四';
+------+------------+---------------+---------------+
| name | math_score | chinese_score | english_score |
+------+------------+---------------+---------------+
| 赵一 | 87 | 90 | 79 |
| 钱二 | 80 | 87 | 92 |
| 孙三 | 89 | 94 | 90 |
| 王五 | 79 | 71 | 91 |
| 周六 | 73 | 84 | 77 |
| 吴七 | 94 | 90 | 93 |
| 郑八 | 87 | 98 | 97 |
| 冯九 | 69 | 82 | 83 |
| 陈十 | 96 | 97 | 96 |
+------+------------+---------------+---------------+
9 rows in set (0.00 sec)1.4 检查范围值
BETWEEN 操作符可以检查某个范围的值，该子句需要两个值，即范围的开始值和结束值，并使用 AND 关键字分隔。例如：检索语文成绩在70~90 之间的同学：
mysql> SELECT name, math_score, chinese_score, english_score
-> FROM score
-> WHERE chinese_score BETWEEN 70 AND 90;
+------+------------+---------------+---------------+
| name | math_score | chinese_score | english_score |
+------+------------+---------------+---------------+
| 赵一 | 87 | 90 | 79 |
| 钱二 | 80 | 87 | 92 |
| 李四 | 74 | 85 | 97 |
| 王五 | 79 | 71 | 91 |
| 周六 | 73 | 84 | 77 |
| 吴七 | 94 | 90 | 93 |
| 冯九 | 69 | 82 | 83 |
+------+------------+---------------+---------------+
7 rows in set (0.00 sec)1.5 检查空值
在创建表时，设计人员可以指定某个字段是否可以为空值，在某字段不包含值时，称其为包含空值 NULL。子句 IS NULL 用来检查具有 NULL 值的列，语法为：
WHERE column_name IS NULL;1.6 组合 WHERE 子句
在进行数据检索时，允许使用多个 WHERE 子句，使用 AND 或 OR 连接。
AND 操作符表示检索同时满足多个条件的数据，比如想要检索语文成绩低于85，英语成绩高于90的同学：
mysql> SELECT name, math_score, chinese_score, english_score
-> FROM score
-> WHERE chinese_score < 85 AND english_score > 90;
+------+------------+---------------+---------------+
| name | math_score | chinese_score | english_score |
+------+------------+---------------+---------------+
| 王五 | 79 | 71 | 91 |
+------+------------+---------------+---------------+OR 操作符表示检索满足任一条件的数据，比如想要检索数学成绩高于90 或者语文成绩高于90分的同学：
mysql> SELECT name, math_score, chinese_score, english_score
-> FROM score
-> WHERE math_score > 90 OR chinese_score > 90;
+------+------------+---------------+---------------+
| name | math_score | chinese_score | english_score |
+------+------------+---------------+---------------+
| 孙三 | 89 | 94 | 90 |
| 吴七 | 94 | 90 | 93 |
| 郑八 | 87 | 98 | 97 |
| 陈十 | 96 | 97 | 96 |
+------+------------+---------------+---------------+
4 rows in set (0.00 sec)WHERE 可以包含任意数目的 AND 和 OR 操作符，可以组合出更高级的过滤。那么就会出现一个问题：条件的顺序如何？AND操作符 的 优先级要高于 OR 操作符，如果想要指定筛选顺序，可以使用小括号进行分组。例如，要检索数学成绩高于90，语文成绩低于80或高于90的同学，可以使用下列检索语句：
mysql> SELECT name, math_score, chinese_score, english_score
-> FROM score
-> WHERE math_score > 90 AND (chinese_score > 90 OR chinese_score < 80);
+------+------------+---------------+---------------+
| name | math_score | chinese_score | english_score |
+------+------------+---------------+---------------+
| 陈十 | 96 | 97 | 96 |
+------+------------+---------------+---------------+
1 row in set (0.00 sec)1.7 IN 操作符
IN 操作符可以指定列的值是否匹配列表中的值或子查询中的任何值。其语法为：
SELECT column1, column2,... FROM table_name
WHERE (expr|column_1) IN ('value1', 'value2', ...)- 列表中的值使用逗号分隔
假如想检索数学成绩为 79、80和94的同学：
mysql> SELECT name, math_score, chinese_score, english_score
-> FROM score
-> WHERE math_score IN (79, 80, 94);
+------+------------+---------------+---------------+
| name | math_score | chinese_score | english_score |
+------+------------+---------------+---------------+
| 钱二 | 80 | 87 | 92 |
| 王五 | 79 | 71 | 91 |
| 吴七 | 94 | 90 | 93 |
+------+------------+---------------+---------------+
3 rows in set (0.01 sec)IN 操作符的优点：
- 在使用长的合法选项清单时，IN 操作符的语法更清楚且更直观；
- 在使用 IN 时，计算的次序更容易管理（因为使用的操作符更少）；
- IN 操作符一般比 OR 操作符清单执行更快；
- IN 操作符的最大优点是可以包含其他 SELECT 语句，使得能够更动态地建立 WHERE 子句，后面会对此进行详细介绍。
1.8 NOT 操作符
NOT 操作符的功能是否定它之后所跟的任何条件。
假如想检索数学成绩不为 79、80和94的同学：
mysql> SELECT name, math_score, chinese_score, english_score
-> FROM score
-> WHERE math_score NOT IN (79, 80, 94);
+------+------------+---------------+---------------+
| name | math_score | chinese_score | english_score |
+------+------------+---------------+---------------+
| 赵一 | 87 | 90 | 79 |
| 孙三 | 89 | 94 | 90 |
| 李四 | 74 | 85 | 97 |
| 周六 | 73 | 84 | 77 |
| 郑八 | 87 | 98 | 97 |
| 冯九 | 69 | 82 | 83 |
| 陈十 | 96 | 97 | 96 |
+------+------------+---------------+---------------+
7 rows in set (0.01 sec)2、LIKE 子句
如果我们明确想要搜索的结果，可以使用=进行筛选，比如WHERE name = '冯九'。但是如果我们只记得该字段中含有某些字符，比如只记得同学姓冯，那要筛选该记录就需要使用 LIKE 子句了。
通配符（wildcard）是用来匹配值的一部分的特殊字符。
2.1 `%`通配符
SQL LIKE 子句中使用 % 字符来表示任意字符，如果没有使用 % 那么 LIKE 子句与 等号的效果一样。LIKE 子句语法为：
SELECT column1, column2,...
FROM table_name
WHERE column1 LIKE condition1 [AND [OR]] column2 = 'somevalue'- 可以使用 AND 或 OR 指定一个或多个条件
下面演示如何检索姓冯的同学成绩：
mysql> SELECT name, math_score, chinese_score, english_score
-> FROM score
-> WHERE name LIKE '冯%';
+------+------------+---------------+---------------+
| name | math_score | chinese_score | english_score |
+------+------------+---------------+---------------+
| 冯九 | 69 | 82 | 83 |
+------+------------+---------------+---------------+
1 row in set (0.00 sec)2.2 `_`通配符
下划线通配符，只匹配单个字符，不能匹配多个字符。
mysql> SELECT name, math_score, chinese_score, english_score
-> FROM score
-> WHERE name LIKE '冯_';
+------+------------+---------------+---------------+
| name | math_score | chinese_score | english_score |
+------+------------+---------------+---------------+
| 冯九 | 69 | 82 | 83 |
+------+------------+---------------+---------------+
1 row in set (0.00 sec)虽然通配符很有用，但这种功能是有代价的：通配符搜索的处理一般比其他搜索所花时间更长。注意以下几点：
- 不要过度使用通配符；
- 确实需要通配符时，除非绝对必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来最慢。
3、正则表达式检索
前面介绍了 LIKE 通配符进行模糊匹配，如果对于基本的过滤，这就够了。但是遇到复杂的过滤条件，通配符就显得有些力不从心了，正则表达式该登场了，正则表达式是用来匹配文本 的特殊的串（字符集合） 。如果想要从文本里提取电话号码、QQ号码或者URL时，正则表达式再适合不过了。MySQL 中使用 REGEXP 操作符来进行正则匹配。
注意：MySQL 仅支持多数正则表达式实现的一个很小的子集。下表中的正则模式可以用于 REGEXP 操作符中：
匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 '\n' 或 '\r' 之后的位置。本节内容我们采用 MySQL 8 自带的数据库（world）中的数据表（city）。
3.1 基本字符匹配
. 是正则表达式语言中的一个特殊字符，它表示匹配任意一个字符，下面筛选出名称中包含 alle 的城市：
mysql> use world;
Database changed
mysql> SELECT name, Population FROM city
-> WHERE name REGEXP '.alle';
+--------------------------------+------------+
| name | Population |
+--------------------------------+------------+
| The Valley | 595 |
| San Fernando del Valle de Cata | 134935 |
...
| McAllen | 106414 |
+--------------------------------+------------+
19 rows in set (0.01 sec)3.2 进行OR匹配
如果搜索条件为两个，使用 | 进行条件的连接，下面语句筛选名称中包含alle 或 ille 的城市：
mysql> SELECT name, Population FROM city
-> WHERE name REGEXP 'alle|ille';
+--------------------------------+------------+
| name | Population |
+--------------------------------+------------+
| Willemstad | 2345 |
| The Valley | 595 |
| San Fernando del Valle de Cata | 134935 |
| Townsville | 109914 |
...
| Gainesville | 92291 |
+--------------------------------+------------+
42 rows in set (0.01 sec)3.3 匹配几个字符之一
如果想匹配几个特定的字符，可通过指定一组用[ 和 ] 括起来的字符完成，下面语句同样筛选名称中包含 alle 或 ille 的城市：
mysql> SELECT name, Population FROM city
-> WHERE name REGEXP '[ai]lle';
+--------------------------------+------------+
| name | Population |
+--------------------------------+------------+
| Willemstad | 2345 |
| The Valley | 595 |
| San Fernando del Valle de Cata | 134935 |
| Townsville | 109914 |
...
| Gainesville | 92291 |
+--------------------------------+------------+
42 rows in set (0.01 sec)3.4 匹配范围
集合可用来定义要匹配的一个或多个字符，假如我们想要匹配数字0到9可以写成 [0123456789] ，还有一种简单的写法 [0-9] ，范围不一定是数值，也可以是字母： [a-z] 匹配任意字母字符。
下面语句筛选名称中包含 alle 、blle 、clle 、… 、ille 的城市：
mysql> SELECT name, Population FROM city
-> WHERE name REGEXP '[a-i]lle';
+--------------------------------+------------+
| name | Population |
+--------------------------------+------------+
| Willemstad | 2345 |
| The Valley | 595 |
| San Fernando del Valle de Cata | 134935 |
...
| Gainesville | 92291 |
+--------------------------------+------------+
44 rows in set (0.01 sec)3.5 匹配特殊字符
在各种编程语言中都有 转义字符 的概念，MySQL 的正则表达式中也由一些特殊字符比如：.、-、[ 和 ] 等，如果要匹配此类字符，必须用 \\ 作为前导， \\- 表示查找 -，\\[ 表示查找 [。
\\ 也用来引用元字符，如表所示：
换页3.6 匹配字符类
MySQL 预定义了字符集用来匹配数字或字母字符等，称为 字符类 （character class）。
任意的字母和数字（同[a-zA-Z0-9]）3.7 匹配多个实例
前面使用的正则表达式都只匹配单次出现。有时候我们需要对匹配的数量进行控制，此时需要* 、+、?、{n}、{n,}或{n,m}。下面语句筛选名称中包含连续的5个a-e的城市，[a-e]{5}也可以写为[a-e][a-e][a-e][a-e][a-e]：
mysql> SELECT name, Population FROM city
-> WHERE name REGEXP '[a-e]{5}';
+-------------------+------------+
| name | Population |
+-------------------+------------+
| Bacabal | 93121 |
| Sabadell | 184859 |
| Addis Abeba | 2495000 |
| Ahmedabad | 2876710 |
| Faridabad | 703592 |
| Moradabad | 429214 |
| Abadan | 206073 |
| Jacobabad | 137700 |
| M??nchengladbach | 263697 |
| Bergisch Gladbach | 106150 |
+-------------------+------------+
10 rows in set (0.00 sec)3.8 定位符
为了匹配特定位置的文本，需要使用如下定位符：
文本的开始如下语句匹配名称以z开头的城市：
mysql> SELECT name, Population FROM city
-> WHERE name REGEXP '^[z]';
+-----------------+------------+
| name | Population |
+-----------------+------------+
| Zaanstad | 135621 |
| Zoetermeer | 110214 |
| Zwolle | 105819 |
| Zenica | 96027 |
...
| Zeleznogorsk | 94000 |
+-----------------+------------+
59 rows in set (0.00 sec)本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2018-08-14，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL（TencentDB for MySQL）为用户提供安全可靠，性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性，包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL，可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理，不仅经济实惠，而且稳定可靠，易于运维。