MySQL正则表达式基础

最新推荐文章于 2022-11-20 11:39:14 发布

Backcanhave7

最新推荐文章于 2022-11-20 11:39:14 发布

阅读量261

点赞数

分类专栏： MySQL 文章标签： MySQL正则表达式正则表达式

本文链接：https://blog.csdn.net/qq_41080850/article/details/85028347

版权

MySQL 专栏收录该内容

38 篇文章 2 订阅

订阅专栏

# 创建产品信息表products：

CREATE TABLE products
(
  prod_id    char(10)      NOT NULL,
  vend_id    int           NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  text          NULL ,
  PRIMARY KEY(prod_id)
) ENGINE=InnoDB CHARSET=UTF8;

# 产品信息表字段说明：
# pro_id表示产品编号，vend_id表示供应商编号，prod_name表示产品名；
# prod_price表示产品价格，prod_desc表示产品描述。

# 向产品信息表products中插入数据：

INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV01', 1001, '.5 ton anvil', 5.99, '.5 ton anvil, black, complete with handy hook');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV02', 1001, '1 ton anvil', 9.99, '1 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('ANV03', 1001, '2 ton anvil', 14.99, '2 ton anvil, black, complete with handy hook and carrying case');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('OL1', 1002, 'Oil can', 8.99, 'Oil can, red');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FU1', 1002, 'Fuses', 3.42, '1 dozen, extra long');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SLING', 1003, 'Sling', 4.49, 'Sling, one size fits all');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT1', 1003, 'TNT (1 stick)', 2.50, 'TNT, red, single stick');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('TNT2', 1003, 'TNT (5 sticks)', 10, 'TNT, red, pack of 10 sticks');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FB', 1003, 'Bird seed', 10, 'Large bag (suitable for road runners)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('FC', 1003, 'Carrots', 2.50, 'Carrots (rabbit hunting season only)');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('SAFE', 1003, 'Safe', 50, 'Safe with combination lock');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('DTNTR', 1003, 'Detonator', 13, 'Detonator (plunger powered), fuses not included');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP1000', 1005, 'JetPack 1000', 35, 'JetPack 1000, intended for single use');
INSERT INTO products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('JP2000', 1005, 'JetPack 2000', 55, 'JetPack 2000, multi-use');


# 创建供应商信息表vendors：
CREATE TABLE vendors
(
  vend_id      int      NOT NULL AUTO_INCREMENT,
  vend_name    char(50) NOT NULL ,
  vend_address char(50) NULL ,
  vend_city    char(50) NULL ,
  vend_state   char(5)  NULL ,
  vend_zip     char(10) NULL ,
  vend_country char(50) NULL ,
  PRIMARY KEY (vend_id)
) ENGINE=InnoDB CHARSET=UTF8;

# 供应商信息表vendors字段说明：
# vend_id表示供应商编号，vend_name表示供应商名，vend_address表示供应商地址，vend_city表示供应商的城市；
# vend_state表示供应商的州，vend_zip表示供应商的邮编，vend_country表示供应商的国家。

# 向供应商信息表中插入数据：

INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1001,'Anvils R Us','123 Main Street','Southfield','MI','48075', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1002,'LT Supplies','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1003,'ACME','555 High Street','Los Angeles','CA','90046', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1004,'Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1005,'Jet Set','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES(1006,'Jouets Et Ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

基本字符匹配：

# 查看products表中的所有信息：

select * from products;

# 返回结果为：
+---------+---------+----------------+------------+----------------------------------------------------------------+
| prod_id | vend_id | prod_name      | prod_price | prod_desc                                                      |
+---------+---------+----------------+------------+----------------------------------------------------------------+
| ANV01   |    1001 | .5 ton anvil   |       5.99 | .5 ton anvil, black, complete with handy hook                  |
| ANV02   |    1001 | 1 ton anvil    |       9.99 | 1 ton anvil, black, complete with handy hook and carrying case |
| ANV03   |    1001 | 2 ton anvil    |      14.99 | 2 ton anvil, black, complete with handy hook and carrying case |
| DTNTR   |    1003 | Detonator      |      13.00 | Detonator (plunger powered), fuses not included                |
| FB      |    1003 | Bird seed      |      10.00 | Large bag (suitable for road runners)                          |
| FC      |    1003 | Carrots        |       2.50 | Carrots (rabbit hunting season only)                           |
| FU1     |    1002 | Fuses          |       3.42 | 1 dozen, extra long                                            |
| JP1000  |    1005 | JetPack 1000   |      35.00 | JetPack 1000, intended for single use                          |
| JP2000  |    1005 | JetPack 2000   |      55.00 | JetPack 2000, multi-use                                        |
| OL1     |    1002 | Oil can        |       8.99 | Oil can, red                                                   |
| SAFE    |    1003 | Safe           |      50.00 | Safe with combination lock                                     |
| SLING   |    1003 | Sling          |       4.49 | Sling, one size fits all                                       |
| TNT1    |    1003 | TNT (1 stick)  |       2.50 | TNT, red, single stick                                         |
| TNT2    |    1003 | TNT (5 sticks) |      10.00 | TNT, red, pack of 10 sticks                                    |
+---------+---------+----------------+------------+----------------------------------------------------------------+

从产品信息表products中查询列prod_name中包含文本1000的所有行：

select prod_name from products where prod_name regexp '1000';

# 返回结果为：
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+

从产品信息表products中查询列prod_name中包含文本000的所有行：

select prod_name from products where prod_name regexp '.000';

# 返回结果为：
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

# 这里使用了正则表达式.000，.是正则表达式语言中一个特殊的字符，它表示匹配任意一个字符，
# 因此，1000和2000都匹配且返回。

通配符lIke和正则表达式regexp之间有一个重要的差别：

select prod_name from products where prod_name like '1000';
# 这条语句的返回结果为空

select prod_name from products where prod_name regexp '1000';
# 返回结果为：
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+

# 之所以出现这种现象是因为like匹配的是整个列，也就是说只有当prod_name与正则表达式'1000'完全相同时，
# prod_name才会被返回；而regexp是在列值内匹配，即如果prod_name中包含文本'1000'，则prod_name被返回。

进行or匹配：

从产品信息表products中查询列prod_name包含文本1000或文本2000的所有行：

select prod_name from products where prod_name regexp '1000|2000';

# 返回结果为：
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

# 上述语句中|为正则表达式中的or操作符，它表示匹配其中之一，因此包含文本1000或包含文本2000的行都被匹配返回。

匹配几个字符之一：

如果只想匹配特定的字符，可以通过指定一组用方括号[]括起来的字符集完成。

select prod_name from products where prod_name regexp '[123] Ton';

# 返回结果为：
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+

# 这里使用了正则表达式[123] Ton。[123]定义一组字符，它的意思是匹配1、2、3这三个数字中的任意一个，
# 因此1 ton和2 ton都匹配且返回。
# 正如所见，[]是另一种形式的or语句，事实上，正则表达式[123] Ton为[1|2|3] Ton的缩写。
# 字符集也可以被否定，即，它们将匹配除指定字符外的任何东西。为否定一个字符集，在集合的开始处放置一个^即可。
# 因此，尽管[123]匹配数字1、2、3这三个数字中的任意一个，但[^123]却匹配除这些数字之外的任何东西。
# 上述语句的返回结果也说明了MySQL中的正则表达式匹配不区分大小写。

匹配范围：

集合可用来定义要匹配的一个或多个字符，例如集合[0123456789]将匹配数字0到9中的任意一个数字，为简化这种类型的集合，可以使用-来定义一个范围，集合[0123456789]就可以简化为[0-9]。范围不限于完整的集合，[1-3]和[6-9]也是合法的范围。此外，范围不一定只是数值的，集合[a-z]匹配任意字母字符。

select prod_name from products where prod_name regexp '[1-5] Ton';

# 返回结果为：
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+

# 这里使用正则表达式[1-5] Ton。[1-5]定义了一个范围，这个表达式的意思是匹配1到5这五个数字中的任意一个，因此返回三个匹配行。

匹配特殊字符：

正则表达式语言由具有特定含义的特殊字符构成，如.、[]、|和-等，如果想要匹配这些特定的字符，需要使用\\来转义这些字符。

# 查看供应商信息表vendors中的所有信息：

select * from vendors;

# 返回结果为：
+---------+----------------+-----------------+-------------+------------+----------+--------------+
| vend_id | vend_name      | vend_address    | vend_city   | vend_state | vend_zip | vend_country |
+---------+----------------+-----------------+-------------+------------+----------+--------------+
|    1001 | Anvils R Us    | 123 Main Street | Southfield  | MI         | 48075    | USA          |
|    1002 | LT Supplies    | 500 Park Street | Anytown     | OH         | 44333    | USA          |
|    1003 | ACME           | 555 High Street | Los Angeles | CA         | 90046    | USA          |
|    1004 | Furball Inc.   | 1000 5th Avenue | New York    | NY         | 11111    | USA          |
|    1005 | Jet Set        | 42 Galaxy Road  | London      | NULL       | N16 6PS  | England      |
|    1006 | Jouets Et Ours | 1 Rue Amusement | Paris       | NULL       | 45678    | France       |
+---------+----------------+-----------------+-------------+------------+----------+--------------+

例如，想要从供应商信息表vendors的vend_name列中查询出包含点字符.的所有行：

select vend_name from vendors where vend_name regexp '\\.';

# 返回结果为：
+--------------+
| vend_name    |
+--------------+
| Furball Inc. |
+--------------+

# 正则表达式'\\.'匹配.,所以只检索出一行。正则表达式内具有特殊意义的所有字符都必须以这种方式转义。
# 例如，为了匹配反斜杠\本身，需要使用\\\。

\\也用来引用元字符(具有特殊含义的字符),如下表所示：

空白元字符
元字符	说明
\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

匹配字符类：

存在找出我们经常使用的数字、所有字母字符或所有数字字符的匹配。为更方便工作，可以使用预定义的字符集，称为字符类。

下表给出了常用的字符类以及它们的含义：

字符类
类	说明
[:alnum:]	任意字母和数字，同[a-zA-Z0-9]
[:alpha:]	任意字符，同[a-zA-Z]
[:blank:]	空格和制表，同\\t
[:cntrl:]	ASCII控制字符（ASCII0到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]

匹配多个实例：

目前为止使用的所有正则表达式都是试图匹配单次出现。如果存在，则该行被检索出来，如果不存在，则检索不出任何行。但有时需要对匹配的数目进行更强的限制。例如，我们可能需要寻找所有的数，不管数中包含多少数字，或者我们可能想寻找一个单词并且还能适应一个尾随的s(如果存在)。这可以用正则表达式重复元字符来完成，正则表达式中的重复元字符如下表所示：

重复元字符
元字符	说明
*	0个或多个匹配
+	1个或多个匹配，同{1,}
?	0个或1个匹配
{n}	指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m}	指定匹配数目的范围(m<=255)

示例1：

select prod_name from products where prod_name regexp '\\([0-9] sticks?\\)';

# 返回结果为：
+----------------+
| prod_name      |
+----------------+
| TNT (1 stick)  |
| TNT (5 sticks) |
+----------------+

# 这里使用了正则表达式\\([0-9] sticks?\\)。\\(用于转义左括号，\\)用于转义右括号，[0-9]匹配任意数
字(这个例子中为1和5),sticks匹配stick或sticks(s后的?使s可选，因为?匹配它前面的任何字符的0次或1次
出现)

示例2：

select prod_name from products where prod_name regexp '[[:digit:]]{4}';

# 返回结果为：
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+

# 如前所述，[:digit:]是一个集合，它匹配任意数字，{4}确切地要求它前面的字符(0到9中的任意一个数字)出现4次，
# 所以，[[:digit:]]{4}匹配连在一起的任意四个数字。
# 正则表达式'[0-9][0-9][0-9][0-9]'可以实现与正则表达式'[[:digit:]]{4}'同样的匹配。

定位符：

目前为止的所有例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本，可以使用下表中的定位符：

定位元字符
元字符	说明
^	文本的开始
$	文本的结尾
[[:<:]]	词的开始
[[:>:]]	词的结尾

示例：想从产品信息表products的列prod_name中找出以一个数(包括以小数点开始的数)开始的所有产品。

select prod_name from products where prod_name regexp '^[0-9\\.]';

# 返回结果为：
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+

# ^匹配串的开始。因此，^[0-9\\.]只在.或任意数字为串中第一个字符时才匹配它们。

# ^有两种用法。一是在集合中(用方括号[]定义),用来否定该集合，而是用来指定串的开始。
# 前文说过，like和regexp的不同在于,like匹配整个串而regexp匹配子串。利用定位符，通过用^指定从串的
# 开始位置匹配，用$指定匹配到串的结束位置，则可以使regexp的作用和like相同。

参考：

《MySQL必知必会》——Ben·Forta

PS：本文为博主原创文章，转载请注明出处。

Backcanhave7

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL正则表达式基础

# 创建产品信息表products：CREATE TABLE products( prod_id char(10) NOT NULL, vend_id int NOT NULL , prod_name char(255) NOT NULL , prod_price decimal(8,2) NOT NULL , pr...
复制链接

扫一扫