MySQL讲义第 35 讲——select 查询之正则表达式
文章目录
正则表达式用来描述或者匹配符合规则的字符串。它的用法和 LIKE 相似，能够实现一些特殊的规则匹配。在MySQL中， 使用 REGEXP 操作符来进行正则表达式匹配。
一、正则表达式匹配规则
正则表达式常用的字符匹配列表：
| 选项 | 说明 | 例子 | 匹配值示例 |
|---|---|---|---|
| ‘字符串A’ | 包含【字符串A】 | ‘银行’ | 中国银行,交通银行股份有限公司 |
| | | 或者（OR） | ||
| ‘字符串A|字符串B|字符串C’ | 包含【字符串A】 或 【字符串B】 或 【字符串C】 | ‘石油|石化’ | 中国石油，中国石化，大桥石化公司 |
| ‘^字符串A’ | 以【字符串A】开头 | ‘^中原’ | 中原银行，中原证券 |
| ‘字符串A$’ | 以【字符串A】结尾 | ‘石化$’ | 中国石化，上海石化 |
| ‘.’ | 匹配任何单个字符 | b.t | bit,bat,but |
| ‘[字符集合]’ | 匹配字符集合中的任何一个字符。【字符集合】可以用【-】表示范围，比如[1-4]、[a-k]等 | ‘中国银[行河]’ | 中国银行，中国银河 |
| ‘[^字符集合]’ | 匹配不在括号中的任何字符 | ‘60010[^1-4]’ | 600105,600108等 |
| ‘a*’ | 匹配 0 个或多个字符 | ‘fa*n’ | fn,fan,faan |
| ‘a+’ | 匹配一个或多个字符，不包括空 | ‘ba+’ | ba,bay,bare |
| ‘a?’ | 匹配一个或 0 个字符 | ‘ba?’ | by,bay,bare |
| ‘字符串{n}’ | 匹配前面的字符串 n 次 | ‘ba{2}y’ | baay |
| ‘字符串{n,}’ | 匹配前面的字符串至少 n 次 | ‘ba{2,}y’ | baay,baaay |
| ‘字符串{n,m}’ | 匹配前面的字符串至少n次，至多m次。 | ‘ba{2,4}y’ | baay,baaay,baaaay |
二、数据准备
数据表 company_information 保存了中国的部分上市公司信息，表结构如下：
mysql> desc company_information;
+----------------------+-----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+-----------+------+-----+---------+-------+
| company_id | char(6) | NO | PRI | NULL | |
| company_abbreviation | char(20) | YES | | NULL | |
| company_fullname | char(100) | YES | | NULL | |
| English_name | char(200) | YES | | NULL | |
| registration_address | char(200) | YES | | NULL | |
| web_address | char(200) | YES | | NULL | |
+----------------------+-----------+------+-----+---------+-------+
6 rows in set (0.00 sec)
mysql> show create table company_information\G
*************************** 1. row ***************************
Table: company_information
Create Table: CREATE TABLE `company_information` (
`company_id` char(6) NOT NULL,
`company_abbreviation` char(20) DEFAULT NULL,
`company_fullname` char(100) DEFAULT NULL,
`English_name` char(200) DEFAULT NULL,
`registration_address` char(200) DEFAULT NULL,
`web_address` char(200) DEFAULT NULL,
PRIMARY KEY (`company_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
三、查询包含某些字符的字段信息
匹配指定字符串，只要这个字符串在查询文本中即可，要匹配多个字符串，多个字符串之间使用分隔符【|】隔开。
1、查询【company_fullname】字段包含【银行】两个字的公司信息
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> company_fullname
-> FROM
-> company_information
-> WHERE
-> company_fullname REGEXP '银行';
+------------+----------------------+-----------------------------------------------------+
| company_id | company_abbreviation | company_fullname |
+------------+----------------------+-----------------------------------------------------+
| 000001 | 平安银行 | 平安银行股份有限公司 |
| 002142 | 宁波银行 | 宁波银行股份有限公司 |
| 002839 | 张家港行 | 江苏张家港农村商业银行股份有限公司 |
| 600000 | 浦发银行 | 上海浦东发展银行股份有限公司 |
| 600015 | 华夏银行 | 华夏银行股份有限公司 |
| 600016 | 民生银行 | 中国民生银行股份有限公司 |
| 600036 | 招商银行 | 招商银行股份有限公司 |
| 600908 | 无锡银行 | 无锡农村商业银行股份有限公司 |
| 600919 | 江苏银行 | 江苏银行股份有限公司 |
| 600926 | 杭州银行 | 杭州银行股份有限公司 |
| 601009 | 南京银行 | 南京银行股份有限公司 |
| 601128 | 常熟银行 | 江苏常熟农村商业银行股份有限公司 |
| 601166 | 兴业银行 | 兴业银行股份有限公司 |
| 601169 | 北京银行 | 北京银行股份有限公司 |
| 601229 | 上海银行 | 上海银行股份有限公司 |
| 601288 | 农业银行 | 中国农业银行股份有限公司 |
| 601328 | 交通银行 | 交通银行股份有限公司 |
| 601398 | 工商银行 | 中国工商银行股份有限公司 |
| 601818 | 光大银行 | 中国光大银行股份有限公司 |
| 601939 | 建设银行 | 中国建设银行股份有限公司 |
| 601988 | 中国银行 | 中国银行股份有限公司 |
| 601997 | 贵阳银行 | 贵阳银行股份有限公司 |
| 601998 | 中信银行 | 中信银行股份有限公司 |
| 603323 | 吴江银行 | 江苏吴江农村商业银行股份有限公司 |
+------------+----------------------+-----------------------------------------------------+
24 rows in set (0.00 sec)
2、查询【company_fullname】字段包含【石油】或【石化】的公司信息
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> company_fullname
-> FROM
-> company_information
-> WHERE
-> company_fullname REGEXP '石油|石化';
+------------+----------------------+-----------------------------------------------------+
| company_id | company_abbreviation | company_fullname |
+------------+----------------------+-----------------------------------------------------+
| 000053 | 深基地Ｂ | 深圳赤湾石油基地股份有限公司 |
| 000554 | 泰山石油 | 中国石化山东泰山石油股份有限公司 |
| 000637 | 茂化实华 | 茂名石化实华股份有限公司 |
| 000703 | 恒逸石化 | 恒逸石化股份有限公司 |
| 000819 | 岳阳兴长 | 岳阳兴长石化股份有限公司 |
| 000852 | 石化机械 | 中石化石油机械股份有限公司 |
| 002207 | 准油股份 | 新疆准东石油技术股份有限公司 |
| 002278 | 神开股份 | 上海神开石油化工装备股份有限公司 |
| 002353 | 杰瑞股份 | 烟台杰瑞石油服务集团股份有限公司 |
| 002469 | 三维工程 | 山东三维石化工程股份有限公司 |
| 002490 | 山东墨龙 | 山东墨龙石油机械股份有限公司 |
| 002493 | 荣盛石化 | 荣盛石化股份有限公司 |
| 002648 | 卫星石化 | 浙江卫星石化股份有限公司 |
| 300164 | 通源石油 | 西安通源石油科技股份有限公司 |
| 600028 | 中国石化 | 中国石油化工股份有限公司 |
| 600248 | 延长化建 | 陕西延长石油化建股份有限公司 |
| 600346 | 恒力股份 | 恒力石化股份有限公司 |
| 600583 | 海油工程 | 海洋石油工程股份有限公司 |
| 600688 | 上海石化 | 中国石化上海石油化工股份有限公司 |
| 600871 | 石化油服 | 中石化石油工程技术服务股份有限公司 |
| 601798 | 蓝科高新 | 甘肃蓝科石化高新装备股份有限公司 |
| 601857 | 中国石油 | 中国石油天然气股份有限公司 |
| 603036 | 如通股份 | 江苏如通石油机械股份有限公司 |
| 603637 | 镇海股份 | 镇海石化工程股份有限公司 |
+------------+----------------------+-----------------------------------------------------+
24 rows in set (0.01 sec)
3、查询【company_fullname】字段包含【石油】和【石化】的公司信息
格式：
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> company_fullname
-> FROM
-> company_information
-> WHERE
-> company_fullname REGEXP '石油' AND
-> company_fullname REGEXP '石化';
+------------+----------------------+-----------------------------------------------------+
| company_id | company_abbreviation | company_fullname |
+------------+----------------------+-----------------------------------------------------+
| 000554 | 泰山石油 | 中国石化山东泰山石油股份有限公司 |
| 000852 | 石化机械 | 中石化石油机械股份有限公司 |
| 600688 | 上海石化 | 中国石化上海石油化工股份有限公司 |
| 600871 | 石化油服 | 中石化石油工程技术服务股份有限公司 |
+------------+----------------------+-----------------------------------------------------+
4 rows in set (0.00 sec)
四、查询以某个字符串开头的记录
使用【^】字符可以匹配以某个字符串开头的文本。例如：查询【company_abbreviation】字段以【中原】开头的公司信息。
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> company_fullname
-> FROM
-> company_information
-> WHERE
-> company_abbreviation REGEXP '^中原';
+------------+----------------------+--------------------------------------------+
| company_id | company_abbreviation | company_fullname |
+------------+----------------------+--------------------------------------------+
| 000544 | 中原环保 | 中原环保股份有限公司 |
| 002448 | 中原内配 | 中原内配集团股份有限公司 |
| 600020 | 中原高速 | 河南中原高速公路股份有限公司 |
| 601375 | 中原证券 | 中原证券股份有限公司 |
+------------+----------------------+--------------------------------------------+
4 rows in set (0.00 sec)
五、查询以某个字符串结尾的记录
使用【$】字符可以匹配以某个字符串结尾的文本。例如：
1、查询【company_abbreviation】字段以【石化】结尾的公司信息。
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> company_fullname
-> FROM
-> company_information
-> WHERE
-> company_abbreviation REGEXP '石化$';
+------------+----------------------+--------------------------------------------------+
| company_id | company_abbreviation | company_fullname |
+------------+----------------------+--------------------------------------------------+
| 000703 | 恒逸石化 | 恒逸石化股份有限公司 |
| 002493 | 荣盛石化 | 荣盛石化股份有限公司 |
| 002648 | 卫星石化 | 浙江卫星石化股份有限公司 |
| 600028 | 中国石化 | 中国石油化工股份有限公司 |
| 600688 | 上海石化 | 中国石化上海石油化工股份有限公司 |
+------------+----------------------+--------------------------------------------------+
5 rows in set (0.01 sec)
2、查询【registration_address】字段以【北京】开头并且以【1室】结尾的公司信息。
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> registration_address
-> FROM
-> company_information
-> WHERE
-> registration_address REGEXP '^北京' AND
-> registration_address REGEXP '1室$';
+------------+----------------------+--------------------------------------------------------+
| company_id | company_abbreviation | registration_address |
+------------+----------------------+--------------------------------------------------------+
| 002383 | 合众思壮 | 北京市海淀区知春路118号知春大厦1501室 |
| 300002 | 神州泰岳 | 北京市海淀区万泉庄路28号万柳新贵大厦A座6层601室 |
| 300157 | 恒泰艾普 | 北京市海淀区丰秀中路3号院4号楼401室 |
| 300212 | 易华录 | 北京市石景山区阜石路165号院1号楼1001室 |
| 300231 | 银信科技 | 北京市海淀区苏州街29号维亚大厦12层071室 |
| 300309 | 吉艾科技 | 北京市丰台区海鹰路1号院2号楼2层201室 |
| 300392 | 腾信股份 | 北京市朝阳区光华路15号院2号楼601室 |
| 600860 | 京城股份 | 北京市朝阳区东三环中路59楼901室 |
| 603979 | 金诚信 | 北京市密云县经济开发区水源西路28号院1号楼101室 |
+------------+----------------------+-------------------------------------------------------+
9 rows in set (0.00 sec)
六、匹配字符串中的任意一个字符
1、使用字符【.】可以匹配任意一个字符
例如：查询在【web_address】字段中包含两个字母 t，并且两个字母 t 之间只有一个字符的记录。
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> web_address
-> FROM
-> company_information
-> WHERE
-> web_address REGEXP 't.t';
+------------+----------------------+-------------------------+
| company_id | company_abbreviation | web_address |
+------------+----------------------+-------------------------+
| 000150 | 宜华健康 | www.yihuarealestate.com |
| 002444 | 巨星科技 | www.greatstartools.com |
| 002531 | 天顺风能 | www.titanwind.com.cn |
| 002835 | 同为股份 | www.tvt.net.cn |
| 300134 | 大富科技 | www.tatfook.com |
| 300397 | 天和防务 | www.thtw.com.cn |
| 300430 | 诚益通 | www.ctntech.com |
| 300494 | 盛天网络 | www.stnts.com |
| 600100 | 同方股份 | httpwww.thtf.com.cn |
| 600182 | S佳通 | www.gititirecorp.com |
| 600281 | 太化股份 | httpth600281.cn |
| 600582 | 天地科技 | httpwww.tdtec.com |
| 600751 | 天海投资 | www.tjtmsc.com |
| 601500 | 通用股份 | www.tytyre.com |
+------------+----------------------+-------------------------+
14 rows in set (0.00 sec)
2、使用方括号（[字符集合]）匹配指定字符中的任意一个字符
方括号（[字符集合]）可以指定一个字符集合，只要匹配其中的任何一个字符，即为所查找的文本。例如：
（1）查找【company_abbreviation】字段中前三个字是【中国石】，最后一个字是【油】或【化】的公司信息。
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> web_address
-> FROM
-> company_information
-> WHERE
-> company_abbreviation REGEXP '中国石[油化]';
+------------+----------------------+-----------------------+
| company_id | company_abbreviation | web_address |
+------------+----------------------+-----------------------+
| 600028 | 中国石化 | httpwww.sinopec.com |
| 601857 | 中国石油 | www.petrochina.com.cn |
+------------+----------------------+-----------------------+
2 rows in set (0.00 sec)
（2）查询【company_id】字段中以【60116】开头的公司信息。
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> company_fullname
-> FROM
-> company_information
-> WHERE
-> company_id REGEXP '^60116';
+------------+----------------------+--------------------------------+
| company_id | company_abbreviation | company_fullname |
+------------+----------------------+--------------------------------+
| 601163 | 三角轮胎 | 三角轮胎股份有限公司 |
| 601166 | 兴业银行 | 兴业银行股份有限公司 |
| 601168 | 西部矿业 | 西部矿业股份有限公司 |
| 601169 | 北京银行 | 北京银行股份有限公司 |
+------------+----------------------+--------------------------------+
4 rows in set (0.00 sec)
（3）查询【company_id】字段中以【60116】开头、最后一个数字是【3、6、8】的公司信息。
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> web_address
-> FROM
-> company_information
-> WHERE
-> company_id REGEXP '60116[3-8]';
+------------+----------------------+-------------------------+
| company_id | company_abbreviation | web_address |
+------------+----------------------+-------------------------+
| 601163 | 三角轮胎 | httpwww.triangle.com.cn |
| 601166 | 兴业银行 | www.cib.com.cn |
| 601168 | 西部矿业 | www.westmining.com |
+------------+----------------------+-------------------------+
3 rows in set (0.00 sec)
3、使用（[^字符集合]）匹配指定字符以外的字符
使用（[^字符集合]）可以匹配不在指定集合中的任何字符。例如：
（1）查询【company_id】字段中以【60116】开头、最后一个数字【不是3、8和9】的公司信息。
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> company_fullname
-> FROM
-> company_information
-> WHERE
-> company_id REGEXP '60116[^389]';
+------------+----------------------+--------------------------------+
| company_id | company_abbreviation | company_fullname |
+------------+----------------------+--------------------------------+
| 601166 | 兴业银行 | 兴业银行股份有限公司 |
+------------+----------------------+--------------------------------+
1 row in set (0.00 sec)
（2）查询【company_id】字段中以【60116】开头、最后一个数字不是【6、8、9】的公司信息。
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> company_fullname
-> FROM
-> company_information
-> WHERE
-> company_id REGEXP '60116[^6-9]';
+------------+----------------------+--------------------------------+
| company_id | company_abbreviation | company_fullname |
+------------+----------------------+--------------------------------+
| 601163 | 三角轮胎 | 三角轮胎股份有限公司 |
+------------+----------------------+--------------------------------+
1 row in set (0.00 sec)
七、指定字符串连续出现的次数
‘字符串{n}’ 表示前面的字符连续出现 n 次，‘字符串{n,}’ 表示前面的字符至少连续出现 n 次，‘字符串{n,m}’ 表示前面的字符至少连续出现 n 次，至多连续出现 m次 。如果要把字符串当成一个整体进行匹配，用小括号括起来，即：【(字符串){n}】。例如：
1、查询【web_address】字段中包含【ccc】的公司信息
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> web_address
-> FROM
-> company_information
-> WHERE
-> web_address REGEXP 'c{3}';
+------------+----------------------+-------------------------+
| company_id | company_abbreviation | web_address |
+------------+----------------------+-------------------------+
| 600415 | 小商品城 | httpwww.cccgroup.com.cn |
| 601618 | 中国中冶 | www.mccchina.com |
| 601800 | 中国交建 | www.ccccltd.cn |
+------------+----------------------+-------------------------+
3 rows in set (0.00 sec)
2、查询【web_address】字段中包含【jiji】的公司信息
mysql> SELECT
-> company_id,
-> company_abbreviation,
-> web_address
-> FROM
-> company_information
-> WHERE
-> web_address REGEXP '(ji){2}';
+------------+----------------------+------------------+
| company_id | company_abbreviation | web_address |
+------------+----------------------+------------------+
| 000965 | 天保基建 | www.tbjijian.com |
+------------+----------------------+------------------+
1 row in set (0.00 sec)
3、使用下面的数据进行验证
mysql> select * from t1;
+---------+
| name |
+---------+
| acd |
| ad |
| accd |
| acccd |
| accccd |
| acccccd |
+---------+
6 rows in set (0.00 sec)
--########################## 查询 ad 之间包含 1 个 c 的记录 #####################
mysql> SELECT
-> *
-> FROM
-> t1
-> WHERE
-> name REGEXP 'ac{1}d';
+------+
| name |
+------+
| acd |
+------+
1 row in set (0.00 sec)
--########################## 查询 ad 之间包含 2 个 c 的记录 #####################
mysql> SELECT
-> *
-> FROM
-> t1
-> WHERE
-> name REGEXP 'ac{2}d';
+------+
| name |
+------+
| accd |
+------+
1 row in set (0.00 sec)
--########################## 查询 ad 之间至少包含 2 个 c 的记录 #####################
mysql> SELECT
-> *
-> FROM
-> t1
-> WHERE
-> name REGEXP 'ac{2,}d';
+---------+
| name |
+---------+
| accd |
| acccd |
| accccd |
| acccccd |
+---------+
4 rows in set (0.00 sec)
--################ 查询 ad 之间至少包含 2 个，最多包含 4 个 c 的记录 ###############
mysql> SELECT
-> *
-> FROM
-> t1
-> WHERE
-> name REGEXP 'ac{2,4}d';
+--------+
| name |
+--------+
| accd |
| acccd |
| accccd |
+--------+
3 rows in set (0.00 sec)
--##################### 查询 ad 之间包含 0 个或多个 c 的记录 #####################
mysql> SELECT
-> *
-> FROM
-> t1
-> WHERE
-> name REGEXP 'ac*d';
+---------+
| name |
+---------+
| acd |
| ad |
| accd |
| acccd |
| accccd |
| acccccd |
+---------+
6 rows in set (0.00 sec)
--##################### 查询 ad 之间包含 1 个或多个 c 的记录 #####################
mysql> SELECT
-> *
-> FROM
-> t1
-> WHERE
-> name REGEXP 'ac+d';
+---------+
| name |
+---------+
| acd |
| accd |
| acccd |
| accccd |
| acccccd |
+---------+
5 rows in set (0.00 sec)
--##################### 查询 ad 之间包含 0 个或 1 个 c 的记录 #####################
mysql> SELECT
-> *
-> FROM
-> t1
-> WHERE
-> name REGEXP 'ac?d';
+------+
| name |
+------+
| acd |
| ad |
+------+
2 rows in set (0.00 sec)
245
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
