vlambda博客
学习文章列表

MySql系列:正则表达式和数据过滤

引言

鼓励有时比压力要重要!

在学习的过程中,很多时候我们听到的都是,你要怎样,怎样,你瞧瞧谁谁谁,哪怕今天听不到这样的声音了,但因为曾经反复听到过而导致内心抗拒。虽然也知道自己要去学,但是很难坚持,学着学着就没有了方向,看到还有那么多不会的就更慌了,以至于最后心态崩了,更不愿意学。其实程序员的压力并不小,想成长几乎是需要一直的学习,就像似乎再也不敢说精通java了一样,知识量实在是随着学习的深入,越来越深,越来越广。所以需要,开心学习,快乐成长!

我们都知道到MySQL可以通过 LIKE ...% 来进行模糊匹配。

MySQL 同样也支持其他正则表达式的匹配, MySQL 中使用 REGEXP 操作符来进行正则表达式匹配。

以下所写内容均与以前的文章有联系可以前往博文查看,陈永佳的博客


✨表达式

正则表达式的作用是匹配文本,将一个模式(正则表达式)与一个文本串进行比较。MySQL用 WHERE 子句对正则表达式提供了初步的支持,允许你指定正则表达式,过滤 SELECT 检索出的数据。

下表中的正则模式可应用于 REGEXP 操作符中


模式 描述
^ 匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性,^ 也匹配 '\n' 或 '\r' 之后的位置。
$ 匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性,$ 也匹配 '\n' 或 '\r' 之前的位置。
. 匹配除 "\n" 之外的任何单个字符。要匹配包括 '\n' 在内的任何字符,请使用象 '[.\n]'
[...] 字符集合。匹配所包含的任意一个字符。例如, '[abc]' 可以匹配 "plain" 中的 'a'。
[^...] 负值字符集合。匹配未包含的任意字符。例如, '[^abc]' 可以匹配 "plain" 中的'p'。
p1 p2 p3 匹配 p1 或 p2 或 p3。例如,'z
  • |匹配前面的子表达式零次或多次。例如,zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。

+|匹配前面的子表达式一次或多次。例如,'zo+' 能匹配 "zo" 以及 "zoo",但不能匹配 "z"。+ 等价于 {1,}。{n}|n 是一个非负整数。匹配确定的 n 次。例如,'o{2}' 不能匹配 "Bob" 中的 'o',但是能匹配 "food" 中的两个 o。{n,m}|m 和 n 均为非负整数,其中n <= m。最少匹配 n 次且最多匹配 m 次。

基本字符匹配:

REGEXP关键字来匹配正则

SELECT name FROM tab1 WHERE name REGEXP 'abc'; //REGEXP后所跟的东西作为正则表达式

  • '.'是以匹配任意一个字符

  • 'a|b'可以匹配了两个a或者

  • '[abc]'可以匹配abc中的某一个字符

  • '[^a]'匹配除a以外的数据

  • '[1-9]'匹配1到9中的任何一个数字,[a-z]和[A-Z]同[1-9]

  • ''是相当于转义字符

  • SELECT name FROM tab1 WHERE name REGEXP 'a|b';

匹配字符类:

  • [:alnum:]  任意字母和数字(同[a-zA-Z0-9])

  • [:alpha:]  任意字符(同[a-zA-Z])

  • [:blank:]  空格和制表(同[\t])

  • [:cntrl:]  ASCII控制字符(ASCII 0到31和127)

  • [:digit:]  任意数字(同[0-9])

  • [:graph:]  与[:print:]相同,但不包括空格

  • [:lower:]  任意小写字母(同[a-z])

  • [:print:]  任意可打印字符

  • [:punct:]  既不在[:alnum:]又不在[:cntrl:]中的任意字符

  • [:space:]  包括空格在内的任意空白字符(同[\f\n\r\t\v])

  • [:upper:]  任意大写字母(同[A-Z])

  • [:xdigit:] 任意十六进制数字(同[a-fA-F0-9])

匹配多个字符:

0个或多个匹配

1个或多个匹配(等于{1,})

? 0个或1个匹配(等于{0,1})
{n} 指定数目的匹配
{n,} 不少于指定数目的匹配
{n,m} 匹配数目的范围(m不超过255)

将以上的字符放在你想匹配的字符的后面,即可匹配多个字符

eg:

SELECT 列名... FROM 表名 WHERE 列名 REGEXP 'a*'; 匹配0个和多个a
复制代码

定位符:

  • '^' 匹配文本的开始

  • '$' 匹配文本的结尾

  • [[:<:]] 匹配词的开始

  • [[:>:]] 匹配词的结尾

匹配特殊字符使用\进行转义:

  • \.能够匹配.

  • \f换页

  • \n换行

  • \r回车

  • \t制表

  • \纵向制表

  • 注意:为了匹配\本身,需要使用\\

示例:

查询找到所有的名字以'st'开头

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查询找到所有的名字以'ok'结尾

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查询找到所有的名字包函'mar'的字符串 mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查询找到所有名称以元音开始和'ok'结束 的 mysql> SELECT name FROM person_tbl WHERE nameREGEXP '^[aeiou]|ok$';

一个正则表达式中的可以使用以下保留字

^

所匹配的字符串以后面的字符串开头\

mysql> select "fonfo" REGEXP "^fo$"; -> 0(表示不匹配)
mysql> select "fofo" REGEXP "^fo"; -> 1(表示匹配)

$

所匹配的字符串以前面的字符串结尾
mysql> select "fono" REGEXP "^fono$"; -> 1(表示匹配)
mysql> select "fono" REGEXP "^fo$"; -> 0(表示不匹配)

.

匹配任何字符(包括新行)
mysql> select "fofo" REGEXP "^f.*"; -> 1(表示匹配)  
mysql> select "fonfo" REGEXP "^f.*"; -> 1(表示匹配)

a*

匹配任意多个a(包括空串)
mysql> select "Ban" REGEXP "^Ba*n"; -> 1(表示匹配)
mysql> select "Baaan" REGEXP "^Ba*n"; -> 1(表示匹配)
mysql> select "Bn" REGEXP "^Ba*n"; -> 1(表示匹配)

a+

匹配任意多个a(不包括空串) \mysql> select "Ban" REGEXP "^Ba+n"; -> 1(表示匹配)
mysql> select "Bn" REGEXP "^Ba+n"; -> 0(表示不匹配)

a?

匹配一个或零个a \
mysql> select "Bn" REGEXP "^Ba?n"; -> 1(表示匹配)
mysql> select "Ban" REGEXP "^Ba?n"; -> 1(表示匹配)
mysql> select "Baan" REGEXP "^Ba?n"; -> 0(表示不匹配)

de|abc

匹配de或abc
mysql> select "pi" REGEXP "pi|apa"; -> 1(表示匹配)
mysql> select "axe" REGEXP "pi|apa"; -> 0(表示不匹配)
mysql> select "apa" REGEXP "pi|apa"; -> 1(表示匹配)
mysql> select "apa" REGEXP "^(pi|apa)$"; ->> 1(表示匹配)
mysql> select "pi" REGEXP "^(pi|apa)$"; -> 1(表示匹配)
mysql> select "pix" REGEXP "^(pi|apa)$"; -> 0(表示不匹配)

(abc)*

匹配任意多个abc(包括空串)
mysql> select "pi" REGEXP "^(pi)*$"; -> 1(表示匹配)
mysql> select "pip" REGEXP "^(pi)*$"; -> 0(表示不匹配)
mysql> select "pipi" REGEXP "^(pi)*$"; -> 1(表示匹配)

[a-dX]

匹配“a”、“b”、“c”、“d”或“X”

[^a-dX]

匹配除 “a”、“b”、“c”、“d”、“X” 以外的任何字符。

“[”、“]”必须成对使用 \ mysql> select "aXbc" REGEXP "[a-dXYZ]"; -> 1(表示匹配)
mysql> select "aXbc" REGEXP"^[a-dXYZ]$"; -> 0(表示不匹配)
mysql> select "aXbc" REGEXP "^[a-dXYZ]+$"; -> 1(表示匹配)
mysql> select "aXbc" REGEXP "^[^a-dXYZ]+$"; -> 0(表示不匹配)
mysql> select "gheis" REGEXP "^[^a-dXYZ]+$"; -> 1(表示匹配)
mysql> select "gheisa" REGEXP "^[^a-dXYZ]+$"; -> 0(表示不匹配)

🚀数据过滤


WHERE子句

在我们使用数据库时,通常只会根据特定条件提取表数据的子集。只检索所需数据需要指定搜索条件(search criteria),搜索条件也称为过滤条件(filtercondition)。

SELECT name, age FROM sys_user WHERE age=18;

返回age=18的行
复制代码
WHERE子句支持的操作符
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于

大于
= 大于等于
BETWEEN 在指定的两个值之间

eg:
//获取age大于20的数据
SELECT name,age FROM sys_user WHERE age>20;

//获取age在12到18之间的数据,包括12和18
SELECT name,age FROM sys_user WHERE age BETWEEN 12AND 18;
复制代码
另一个特殊子句
IS NULL用来返回为空的数据

SELECT name, age FROM sys_user WHERE name IS NULL;
复制代码
比较运算符
组合 WHERE 子句
使用AND或者OR子句,组合WHERE子句

//查询age=20并且name='cyj'的数据
SELECT name,age FROM sys_user WHERE age=20 AND name='cyj';

//查询age=20或者age=50的数据
SELECT name,age FROM sys_user WHERE age=20 OR age=50;

AND和OR可以同时使用,不过AND的优先级高于OR,所以会先处理AND操作符,
另外可以用括号分组,明确的显示优先级.
复制代码
IN 操作符

指定条件范围,范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单,全都括在圆括号中。

//查询age在18,19或20......的所有数据,功能与OR相似. SELECT name,age FROM sys_user WHERE age IN (18,19,20,21,22,23);

IN相比于OR的一些好处:

1)在使用长的合法选项清单时,IN操作符的语法更清楚且更直观。
2)在使用IN时,计算的次序更容易管理
3)IN操作符一般比OR操作符清单执行更快
4) IN的可以包含其他SELECT语句,使得能够更动态地建立WHERE子句
复制代码
NOT操作符

否定它之后所写的任何条件。

//查询age不在18,19或20的所有数据,功能与OR相似.
SELECT name,age FROM sys_user WHERE age NOT IN (18,19,20);
复制代码
BETWEEN 与日期类型(NOT BETWEEN 与之相反)

当使用BETWEEN运算符与日期类型值时,要获得最佳结果,应该使用类型转换将列或表达式的类型显式转换为DATE类型。

  要查询获取所需日期(requiredDate)从2013-01-01到2013-01-31的所有订单,但是这里要注意(查询的日期AND后面的默认为2019-5-11 00:00:00)也就是说无法查询出2019-05-11当天日期。

SELECT orderNumber,
requiredDate,
status
FROM jmccsm_orders
WHERE requireddate
BETWEEN CAST('2019-05-01' AS DATE)
AND CAST('2019-5-11' AS DATE);
复制代码

想知道怎么办么,请持续关注博主的博文(前台、后台、sql都可以处理哦)。

LIMIT

SELECT 语句中使用 LIMIT 子句来约束结果集中的行数。LIMIT 子句接受一个或两个参数。两个参数的值必须为零或正整数。

语法:

🐱‍🏍使用通配符过滤


LIKE操作符(NOT LIKE与之相反)
%通配符
%可以匹配表示任何字符出现任意次数

//可以匹配任何name以a开头的数据
SELECT name FROM sys_user WHERE name LINK 'cyj%';  
复制代码
通配符

下划线(_)。下划线只匹配单个字符,用法同%

注意
不要过度使用通配符。

如果其他操作符能达到相同的目的,应该使用其他操作符。

尽量不要把它们用在搜索模式的开始处,因为速度会很慢