MySQL 从零开始:07 数据搜索与搜索

1、WHERE 子句1.1 WHERE 子句操作符1.2 检查单值1.3 检查不匹配值1.4 检查范围值1.5 检查空值1.6 组合 WHERE 子句1.7 IN 操作符1.8 NOT 操作符2、LIKE 子句2.1 `%`通配符2.2 `_`通配符3、正则表达式检索3.1 基本字符匹配3.2 进行OR匹配3.3 匹配几个字符之一3.4 匹配范围3.5 匹配特殊字符3.6 匹配字符类3.7 匹配多个实例3.8 定位符

数据库表中包含了很多数据,一般我们不会检索表中的所有行。通常会根据特定的条件来提取出表的子集,此时我们需要指定搜索条件(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 子句,使用 ANDOR 连接。

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匹配

如果搜索条件为两个,使用 | 进行条件的连接,下面语句筛选名称中包含alleille 的城市:

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 匹配几个字符之一

如果想匹配几个特定的字符,可通过指定一组用[] 括起来的字符完成,下面语句同样筛选名称中包含 alleille 的城市:

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] 匹配任意字母字符。

下面语句筛选名称中包含 alleblleclle 、… 、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)

本文分享自微信公众号 - C与Python实战(CPythonPractice)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-08-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

oracle中的数组(第一篇)(r4笔记第9天)

数组在各种编程语言中都是很重要的数据结构实现,在oracle中也有自己的一席之地。自己简单做了几个实验,发现很多东西还是眼高手低,真实去做的时候,里面还是有不少...

33460
来自专栏Python、Flask、Django

Flask 项目系列 -- 基于Flask打造招聘网站(2017-12-07更新)

18330
来自专栏技术专栏

hive 中 统计某字段json数组中每个value出现的次数

需要将json数组里的qd_title都提取出来转换成hive中的array数组。下面介绍两种方法

1.4K30
来自专栏idba

order by 原理以及优化

一 简介 偏向于业务的(MySQL)DBA或者业务的开发者来说,order by 排序是一个常见的业务功能,将结果根据指定的字段排序,满足前端展示的需求。然而...

15030
来自专栏与神兽党一起成长

[MySQL]查询学生选课的情况(二)

上一篇文章使用了自定义的函数查找出来选择Math,或者选择Chinese或选择Japanese课程的学生。

19600
来自专栏Leetcode名企之路

【Leetcode】177. 第N高的薪水

编写一个 SQL 查询,获取 Employee 表中第 n 高的薪水(Salary)。

29420
来自专栏Python爬虫实战

MySQL从零开始:05 MySQL数据类型

距离上次更新 MySQL 从零开始系列,已经过去了十几天,时间隔得有点长,由于我选用的是 MySQL 的最新版本,网上的教程大多停留在 MySQL 5.x,所以...

17230
来自专栏程序猿DD

10分钟让你明白MySQL是如何利用索引的

一、前言 在MySQL中进行SQL优化的时候,经常会在一些情况下,对MySQL能否利用索引有一些迷惑。 譬如: MySQL 在遇到范围查询条件的时候就停止匹配了...

36990
来自专栏数据和云

深入剖析:认识Oracle 中的 NULL 值

杨廷琨,网名 yangtingkun 云和恩墨技术总监,Oracle ACE Director,ACOUG 核心专家 经常看到很多人提出和NULL有关的问题。N...

35650
来自专栏Java后端生活

MySQL(三)基础查询

15990

扫码关注云+社区

领取腾讯云代金券