# 创建产品信息表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:本文为博主原创文章,转载请注明出处。