【MySQL必知必会】学习笔记Day3

【MySQL必知必会】学习笔记Day3&1.23&D8-9章&P46-61页

基本概念

• 通配符： 用来匹配值的一部分的特殊字符
• 搜索模式：由字面值、通配符或两者组合构成的搜索条件
• LIKE：为在搜索子句中使用通配符，必须使用LIKE操作符
• %通配符:在搜索串中，%表示任何字符出现任意次数
• _通配符：下划线只匹配单个字符而不是多个字符
• REGEXP：后所跟的东西作为正则表达式
• .：是正则表达式语言中一个特殊的字符。表示匹配任意一个字符
• |：为正则表达式的OR操作 符。它表示匹配其中之一
• [ ]：用来匹配特定的字符，[123]的意思是匹配1或2或3
• [^ ]：字符集合否定，[^123]可以匹配除这些字符123外的东西
• -：集合可用来定义要匹配的一个或多个字符，“-”来定义一个范围

6、用通配符进行过滤

（1）LIKE操作符

• 为在搜索子句中使用通配符，必须使用LIKE操作符。LIKE指示MySQL后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

（2）百分号(%)通配符

• 在搜索串中，%表示任何字符出现任意次数
• eg：找出所有以词jet起头的产品

SELECT  prod_id,prod_name
FROM  products
WHERE  prod_name  LIKE 'jet%';

注：

• 区分大小写:根据MySQL的配置方式，搜索可以是区分大小写的

• 通配符可在搜索模式中任意位置使用，并且可以使用多个通配符

• eg:匹配任何位置包含文本anvil的值，而不论它之前或之后出现什么字符

SELECT  prod_id, prod_name
FROM  products
WHERE  prod_name  LIKE '%anvil%';

• 通配符也可以出现在搜索模式的中间
• eg：找出以s起头以e结尾的所有产品

SELECT  prod_name
FROM  products
WHERE  prod_name  LIKE 's%e';

注：

• 除了一个或多个字符外，%还能匹配0个字符。% 代表搜索模式中给定位置的0个、1个或多个字符。
• 注意尾空格：尾空格可能会干扰通配符匹配。例如，在保存词 anvil时，如果它后面有一个或多个空格，则子句WHERE prod_name LIKE '%anvil'将不会匹配它们，因为在最后的l 后有多余的字符。解决这个问题的一个简单的办法是在搜索模式最后附加一个%。还一个更好的办法是使用函数去掉首尾空格。
• 注意NULL：虽然似乎%通配符可以匹配任何东西，但是不能匹配NULL。

（3）下划线(_)通配符

• 下划线只匹配单个字符而不是多个字符。

SELECT  prod_id，prod_name
FROM  products
WHERE  prod_name  LIKE '_ ton anvil';

（4）使用通配符的技巧

• 不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符
• 尽量不要在搜索模式的开始处用通配符，把通配符置于搜索模式的开始处，搜索起来是最慢的
• 仔细注意通配符的位置，如果放错地方，可能不会返回想要的数据

7、用正则表达式进行搜索

（1） 正则表达式介绍

• 正则表达式是用来匹配文本的特殊的串(字符集合)。例如从文本文件中提取电话号码，查找名字中间有数字的所有文件，在一个文本块中找到所有重复的单词， 替换一个页面中的所有URL为这些 URL的实际HTML链接等

（2）使用MySQL正则表达式

• MySQL仅支持多数正则表达式实现的一个很小的子集

（3）基本字符匹配

• REGEXP后所跟的东西作为正则表达式
• eg：检索列prod_name包含文本1000的所有行

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP '1000'
ORDER BY prod_name；

• .是正则表达式语言中一个特殊的字符,它表示匹配任意一个字符

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP '.000'
ORDER BY prod_name；

注:

• LIKE与REGEXP 在LIKE和REGEXP之间有一个重要的差别,如下，
  如果执行下面两条语句，会发现第一条语句不返回数据，而第二条语句返回一行。这是因为，LIKE匹配整个列。如果被匹配的文本在列值 中出现，LIKE将不会找到它，相应的行也不被返回(除非使用通配符)。而REGEXP在列值内进行匹配，如果被匹配的文本在 列值中出现，REGEXP将会找到它，相应的行将被返回。这是一 个非常重要的差别。

SELECT prod_name
FROM  products
WHERE  prod_name LIKE  '1000'
ORDER BY prod_name；

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP '1000'
ORDER BY prod_name；
 

注：

• 匹配不区分大小写： MySQL中的正则表达式匹配不区分大小写。为区分大小写，可使用BINARY关键字，如WHERE prod_name REGEXP BINARY 'JetPack .000'

（4）进行OR匹配

• 为搜索两个串之一(或者为这个串，或者为另一个串)，使用|。

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP '1000|2000'
ORDER BY prod_name；

（5）匹配几个字符之一

• 匹配任何单一字符，[ ]用来匹配特定的字符
• eg：[123]定义一组字符，它的意思是匹配1或2或3
• 其实，正则表达式[123]Ton 为[1|2|3]Ton的缩写，也可以使用后者。

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP '[123] Ton'
ORDER BY prod_name；

注：

• 如上的输出效果，可以用'[123]Ton' 或者 '[1|2|3]Ton'，但不能是'1|2|3 Ton'，因为MySQL假定'1|2|3 Ton'的意思是'1'或 '2'或'3 ton'
• 字符集合也可以被否定，即[^123]可以匹配除这些字符123外的东西

（6）匹配范围

• 集合可用来定义要匹配的一个或多个字符
• [0-9]：匹配数字0到9
• [a-z]：匹配任意字母字符
• eg：下面表达式意思是匹配1到5，因此返回3个匹配行，.5 ton，1 ton，2 ton，由于5 ton匹配， 所以返回.5 ton也返回。

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name；

（7）匹配特殊字符

• 为了匹配特殊字符，如.、|、[]等必须用\为前导，进行转义,

• eg:匹配包含“.”的：

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP ' \\. '
ORDER BY prod_name；

• \\也用来引用元字符(具有特殊含义的字符)

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

注：

• \或\\? ,多数正则表达式实现使用单个反斜杠转义特殊字符， 以便能使用这些字符本身。但MySQL要求两个反斜杠(MySQL 自己解释一个，正则表达式库解释另一个)。

（8）匹配字符类

• 存在找出你自己经常使用的数字、所有字母字符或所有数字字母字 符等的匹配。因此可以使用预定义的字符集，称为字符类：

类         说明
[: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])

（9）匹配多个实例

• 以上所有正则表达式都试图匹配单次出现。但有时需要对匹配的数目进行更强的控制。例如，可能需要寻找所有的数，不管数中包含多少数字，或者可能想寻找一个单词并且还能够适应一个尾随的s等等。此时，可以用正则表达式重复元字符来完成。

元字符      说明
*          0个或多个匹配
+          1个或多个匹配(等于{1,})
?          0个或1个匹配(等于{0,1}) 
{n}        指定数目的匹配
{n,}       不少于指定数目的匹配
{n,m}      匹配数目的范围(m不超过255)

• eg：想匹配 （1 stick）和 （5 sticks）
• 正则表达式\([0-9] sticks?\)。解说如下：\( 匹配（，[0-9]匹配任意数字，sticks?匹配stick 和sticks(s后的?使s可选，因为?匹配它前面的任何字符的0次或1次出现)，\)匹配)

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP ' \\([0-9] sticks?\\)'
ORDER BY prod_name；

• eg：想匹配连在一起的4位数字
• 解说如下： [:digit:]匹配任意数字，因而它为数字的一个集合。{4}确切地要求它前面的字符(任意数字)出现4次，所以 [[:digit:]]{4}匹配连在一起的任意4位数字。

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name；

上面的也可可以写成：

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP '[0-9][0-9][0-9][0-9]'
ORDER BY prod_name；

（10）定位符

• 以上所有例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本，需要使用下面的定位符：

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

• eg: 若想找以一个数(包括以小数点开始的数)开始的所有产品，简单搜索[0-9\\.] 或[[:digit:]\\.])不行，因为它将在文本内任意位置查找匹配。解决办法是使用^定位符。
• ^ 匹配串的开始。因此，^ [0-9\\.]只在.或任意数字开头时才匹配它们。没有^，则还要多检索出那些中间有数字的行

SELECT prod_name
FROM  products
WHERE  prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name；

注：

• ^的双重用途：
  • 在集合中，用 [ 和 ] 定义，用 ^ 来否定该集合
  • 否则，用来指字符串的开始处
• 使REGEXP起类似LIKE的作用：LIKE和REGEXP 的不同在于，LIKE匹配整个串而REGEXP匹配子串。利用定位符，通过用^开始每个表达式，用$结束每个表达式，可以使 REGEXP的作用与LIKE一样

【MySQL必知必会】系列笔记：
【MySQL必知必会1-4章】学习笔记Day1
【MySQL必知必会5-7章】学习笔记Day2