SQL 模糊匹配 -- 通配符 -- 高级过滤

发表于 更新于 分类于 阅读次数： 1 本文字数： 2.1k 阅读时长 ≈ 8 分钟

SQL 查漏补缺：like 模糊匹配、通配符、高级过滤。

tips：图片资源可能被防盗链（寄）了，可以右键属性复制地址在地址栏查看哈。

SQL 查漏补缺

个人感觉对实际工作作用比较大的一些 SQL 关键字总结。

通配符过滤数据

通配符：用来匹配值的一部分的特殊字符。

搜索模式：由字面值、通配符或者两者组合构成的搜索条件。

百分号 % 通配符

作用：个人在工作中，最常用通配符是百分号（%）。在搜索字符中，% 表示任何字符出现任意次数。

例如：在使用 show 查询 MySQL 自带一些参数时，配合 like 去模糊匹配，并且使用通配符百分号 %。

查询当前 session 所有统计记录，如果直接在字符命令界面去查询，共有 175 条记录

1
2
3
4
5
6
7
8
9
show status LIKE 'com_%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Com_commit                          | 0     |
| Com_rollback                        | 0     |
+-------------------------------------+-------+
...
175 rows in set (0.00 sec)

img

Com_xx 部分参数作用说明：

  1. Com_xx：代表某某语句执行次数，一般我们关心的是 CURD 操作（select、insert、update、delete）。
  2. Com_select：执行 select 操作次数，每次累加 1 次。
  3. Com_insert：执行 insert 操作次数，对于批量执行插入的 insert 操作只累加 1 次。
  4. Com_update：执行 update 操作次数。
  5. Com_delete：执行 delete 操作次数。

有几个参数便于用户了解数据库情况：

1
2
3
show status LIKE 'conn%';
show status LIKE 'upti%';
show status LIKE 'slow_q%';

img

  • Connections：试图连接 MySQL 服务器次数。
  • Uptime：服务器工作时间。
  • Slow_queries：慢查询次数。

下划线（_）通配符

作用：这同样是一个比较有用的通配符。下划线用途和 % 一样，但有限制，只匹配单个字符。

示例：在 MySQL 中创建数据库、表以及插入演示数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 1.创建数据库test
create database test;
use test;

-- 2.创建一张表演示。
CREATE TABLE `bols` (
  `id` int NOT NULL AUTO_INCREMENT,
  `girl_names` varchar(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `cup_size` varchar(8) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8_bin

-- 3.插入数据,省略掉了字段，实际工作开发中建议加上字段。
insert into test.bols values(1001,'18 bols D','女','D');
insert into test.bols values(1005,'20 bols D','女','D');
insert into test.bols values(1006,'9 bols A','女','A');

-- 4.使用通配符下划线_匹配。
mysql> select * from test.bols where girl_names like  '__ bols D'; -- 两个占位符
+------+------------+-----+----------+
| id   | girl_names | sex | cup_size |
+------+------------+-----+----------+
| 1001 | 18 bols D  | 女  | D        |
| 1005 | 20 bols D  | 女  | D        |
+------+------------+-----+----------+
2 rows in set (0.00 sec)

mysql> select * from test.bols where girl_names like  '_ bols A'; -- 1个占位符
+------+------------+-----+----------+
| id   | girl_names | sex | cup_size |
+------+------------+-----+----------+
| 1006 | 9 bols A   | 女  | A        |
+------+------------+-----+----------+
1 row in set (0.00 sec)

tips：DB2 不支持下划线（_）通配符。

方括号 [ ] 通配符

作用：方括号 [ ] 通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符。

tips：与上述示范的通配符不一样，并不是所有 DBMS 都支持用来创建集合的 [ ]。微软的 SQL Server 支持集合，但是 MySQL，Oracle，DB2，SQLite 都不支持。验证你使用的 DBMS 是否支持集合，请参考各自厂商提供的官方文档。

例如：我想匹配含有 b 和 o 的老师姓名，在 SQL Server2012 中演示。

1
2
3
4
5
6
7
select * from dbo.girl where girl_name like  '[bo]%';
+------+------------+-----+----------+
| id   | girl_name  | sex | cup_size |
+------+------------+-----+----------+
| 1004 | bols D 18  | 女  |     D    |
| 1005 | bols D 20  | 女  |     D    |
+------+------------+-----+----------+

1.4 使用通配符技巧

  1. 不过度使用通配符。
  2. 尽量不要将通配符放搜索模式的最开始处。
  3. 注意通配符位置。

高级数据过滤

操作符：用来联结或改变 where 子句中的子句的关键字，也称为逻辑操作符（local operator）。

我们通常使用 where 子句进行过滤数据时，一般是单过滤条件。

组合 where 子句：为了进行更强的过滤控制。当然也可以使用多个 where 子句过滤，比如使用 and 子句或者 or 子句。

  1. and 操作符；
  2. or 操作符；
  3. in 操作符；
  4. not 操作符。

and 操作符

使用 and 操作符给 where 子句附加条件，多重过滤。

作用：用在 where 子句中的关键字，用来指示检索满足所有给定条件的行。通俗点讲，给定条件都要满足才能检索出数据行。

示例：接着上面通配符 bols 示例继续，查询尺寸为 D 并且 ID 小于 1005。

1
2
3
4
5
6
7
select * from test.bols where (cup_size='D' and id<1005);
+------+------------+-----+----------+
| id   | girl_names | sex | cup_size |
+------+------------+-----+----------+
| 1001 | 18 bols D  | 女  | D        |
| 1004 | 16 bols C  | 女  | D        |
+------+------------+-----+----------+

or 操作符

or 操作符与 and 操作符正好相反，指示 DBMS 满足任意一个条件即可检索出数据行。

实际上，许多 DBMS 在 or where 子句的第一个条件得到满足情况下，就不再计算第二个条件（即在第一个条件满足时，无论第二个条件是否满足，相应行都将被匹配出来）。

作用：where 子句中使用的关键字，用来表示检索匹配任意给定条件的行。

示例：查询尺寸为 D 或者尺寸为 C 的数据行。

1
2
3
4
5
6
7
8
9
10
select * from test.bols where (cup_size='D' or cup_size='C');
+------+------------+-----+----------+
| id   | girl_names | sex | cup_size |
+------+------------+-----+----------+
| 1001 | 18 bols D  | 女  | D        |
| 1002 | 18 jizels  | 女  | C        |
| 1003 | longls     | 女  | C        |
| 1004 | 16 bols C  | 女  | D        |
| 1005 | 20 bols D  | 女  | D        |
+------+------------+-----+----------+

分析：使用 or 操作符，满足 D 或者 C 任一条件，返回匹配行，5 条数据全部检索出。如果换成 and 操作符，则不会检索到匹配的数据行。

总结：任何时候使用 and 和 or 操作符的 where 子句，应该使用圆括号明确地分组操作符。不应过分依赖默认求值顺序，即使它明确是你希望得到的结果。使用圆括号并没啥坏处，相反可以养成好习惯。

in 操作符

in 操作符用来指定条件范围，范围中每个条件都可以进行匹配。in 取一组逗号分隔，圈在圆括号中的合法值。

作用：where 子句中用来指定要匹配值清单的关键字，功能与 or 相当。

示例：取姓名为 longls 和 jizels 两条数据行。

1
2
3
4
5
6
7
8
9
10
11
12
-- 1.示例，修改一条数据，便于演示
update test.bols set girl_names='jizels' where id=1002;
commit;-- 提交事务

-- 2.使用in操作符
select * from test.bols where girl_names in('longls','jizels');
+------+------------+-----+----------+
| id   | girl_names | sex | cup_size |
+------+------------+-----+----------+
| 1002 | jizels     | 女  | C        |
| 1003 | longls     | 女  | C        |
+------+------------+-----+----------+

in 操作符优点：

  1. 很多场景下，in 操作符语法更清楚，更直观。
  2. 与其它 and 和 or 操作符组合使用时，求职顺序更容易管理。
  3. 一般比一组 or 操作符执行更快。
  4. 最大优点可以包含其它 select 语句，动态建立 where 子句。

not 操作符

where 子句中的操作符有且只有一个功能，否定其后多跟的任何条件。因为 not 基本不单独使用，，往往与其它操作符组合使用。

作用：where 子句中用来否定其后条件的关键字。

示例：匹配出不包含 longls 和 jizels 的数据行，和 in 操作符组合使用，否定 in 条件中的两条数据行。

1
2
3
4
5
6
7
8
9
select * from test.bols where girl_names not in('longls','jizels');
+------+------------+-----+----------+
| id   | girl_names | sex | cup_size |
+------+------------+-----+----------+
| 1001 | 18 bols D  | 女  | D        |
| 1004 | 16 bols C  | 女  | D        |
| 1005 | 20 bols D  | 女  | D        |
| 1006 | 9 bols A   | 女  | A        |
+------+------------+-----+----------+

tips：MariaDB 中的 not 操作符。MariaDB 支持使用 not 否定 in、between 和 exists 子句。大多数 DBMS 允许 not 否定任何条件。

show 关键字（MySQL）

列举一些 MySQL 中常用 show 使用方式。

查询 MySQL 有哪些自带数据库

1
2
3
4
5
6
7
8
9
>mysql show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

tips：在 mysql 中可以使用系统帮助命令？show 查看用法。

SQL 优化，统计 CURD（增删改查）操作次数

从下面 Com_select 为 44，可以看出 select 查询次数已经使用了 44 次。

示例：同理可以查询 Com_insert、Com_delete 和 Com_update，一般情况会配合通配符使用哟。

1
2
3
4
5
6
7
8
9
10
mysql> show status like 'Com_se%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Com_select             | 44    |
| Com_set_option         | 2     |
| Com_set_password       | 0     |
| Com_set_resource_group | 0     |
| Com_set_role           | 0     |
+------------------------+-------+

注意：在 MySQL 中不同存储引擎查询结果可能略微有所不同。

参考资料：

莫问收获，但问耕耘

静下心来，才发现原来不会的还有很多。

一分耕耘，一分收获。

多总结，你会发现，自己的知识宝库越来越丰富。