《MySQL 必知必会》C9-C12

第九章 用正则表达式进行搜索

1 正则表达式介绍

正则表达式是用来匹配文本的特殊的串(字符集合)。如果想从一个文本文件中提取电话号码，可以使用正则表达式，替换一个页面中的URL为这些URL的实际HTML链接，也可以使用正则表达式等等

所有种类的程序设计语言，文本编辑器，操作系统都支持正则表达式，正则表达式用正则表达式语言来建立，正则表达式语言是用来完成上述所有工作以及更多工作的一种特殊指令

2 使用MySQL正则表达式

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

(1) 基本字符匹配

这条语句类似第8章的like语句，只是like被替换成了regexp，它告诉MySQL：regexp后面跟的是正则表达式(与文字正文1000匹配的一个正则表达式)处理

为什么要费力的使用正则表达式，上述的例子还可能降低性能，考虑下面的示例：

这里使用了正则表达式.000， . 是正则表达式语言中一个特殊的字符，它表示匹配任意一个字符，合理的使用正则表达式能极大的提高搜索效率和正确率，快速的得到预期的结果

MySQL中的正则表达式匹配不区分大小写，如果想要区分大小写，可以使用binary关键字

(2) 进行or匹配

为了搜索两个串之一(或者这个串或者另一个串)，可以使用 |

| 为正则表达式的or操作符，它表示匹配其中之一，也可以使用多个 | 来匹配多个字符串

(3) 匹配几个字符之一

匹配特定的字符，可以通过指定一组用[ ]括起来的字符完成

这里使用了正则表达式 [123] ton，[123]定义了一组字符，它的意思是匹配1或2或3，[ ]是另一种形式的or语句，[123] 与[1|2|3]同意，但需要用[ ]来定义or语句查找什么

对于上面的例子过滤的结果并不是我们所需要的，这是由于MySQL假定你的意思是 ‘1’ 或 ‘2’ 或 ‘3 ton’ ，必须把 字符| 括在一个集合中，否则它将应用于整个串

字符集合也可以被否定，即它们将匹配除指定字符外的任何东西，为了否定字符集，在这个字符集开始处加一个 ^ 即可，如[^123]，匹配除了字符1，2，3外的任何东西

(4) 匹配范围

集合可以用来定义要匹配的一个或多个字符，如[0123456789]可以使用集合简化为[0-9]，范围不限于完整的集合[1-3]和[6-9]也是合法的，范围也不一定是数值，[a-z]匹配任意字母字符

(5) 匹配特殊字符

正则表达式语言由具有特殊含义的特殊字符组成，除了 . | -等还有一些其它的字符，如果需要匹配这些字符，需要使用到 \

不使用 \特殊字符 的方式：

上述例子想要使用正则表达式匹配 . 特殊字符，但这样使用 . 的含义是匹配任意字符，所以得不到想要的结果

正确的使用方式：

这种处理就是所谓的转义，正则表达式内具有特殊意义的所有字符都必须以这种方式进行转义，包括 . | [ ]等特殊字符

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

\\f	换页
\\n	换行
\\r	回车
\\t	制表
\\v	纵向制表

为了匹配\ ，需要使用\\

(6) 匹配字符类

为了方便可以使用预定的字符类：

(7) 匹配多个实例

有时需要对匹配结果的数目进行严格的控制，如找一个数不管数中多少数字

*		0个或多个匹配
+ 		1个或多个匹配
？		0个或1个匹配
{n}		指定数目的匹配
{n,}	不少于指定数目的匹配
{n,m} 	匹配数目的范围

上述例子中SQL语句中sticks？，s后面的？使s可选，因为？匹配它前面的任何字符的0次或1次出现，没有?匹配stick和sticks会非常困难

(8) 定位符

定位符可以指定文本位置开始查找

^			文本的开始
$			文本的结束
[[:<:]]		词的开始
[[:>:]]		词的结束

^匹配串的开始，因此正则表达式只在 . 或任意数字为串中第一个字符时才匹配它们

第十章 创建计算字段

1 计算字段

存储在数据库中的数据一般不是应用程序所需要的格式，如：
1，想在一个字段中既显示公司名，又显示公司的地址，但这两个信息一般在表中的不同列中
2，列数据是大小混合的，但报表程序需要把所有的数据按大写打印
3，需要根据表数据进行总数，平均数计算或其它计算

在上述的几个例子中，存储在表中的数据都不是应用程序所需要到的。我们需要直接从数据库中检索出转换，计算或格式化过的数据，而不是检索出数据，然后再在客户机应用程序中重新格式化

这就是计算字段发挥作用所在了，计算字段并不实际存在于数据库表中，计算字段是运行时在select语句内创建的

字段：基本上与列的意思相同，经常互换使用，不过数据库列一般称为列，而字段用于计算字段的连接上

注：只有数据库知道select语句中哪些列是实际的列，哪些是计算字段，从客户机来看，计算字段的数据是以与其它列的数据相同的方式返回的

2 拼接字段

vendors表中包含了供应商品和位置信息，假如要生成一个供应商报表，需要在供应商的名字中按照 name(location)这样的格式列出供应商的位置

(1) 拼接

拼接：将值联结到一起构成单个值

在MySQL中处理拼接可以使用 concat()函数来拼接两个列

concat()拼接串，即把多个串连接起来形成一个较长的串，concat()需要一个或多个指定的串，各个串之间用 , 分隔

可以使用MySQL中提供的rtrim()函数删除数据右侧多余的空格，ltrim()删除左边的空格及trim()去掉串左右两边的空格

(2) 使用别名

前面生成的新计算列并没有名字，只是一个值，如果仅在SQL查询工具中查看以下结果，这样并无大碍，但是一个未命名的列不能用于客户机应用中，因为客户机无法通过名字引用它

为了解决这个问题，SQL支持列别名，别名是一个字段或值得替换名，别名用as关键字赋予

as vend_title，它只是MySQL创建一个包含指定计算名为vend_title的计算字段，任何客户机都可以按名引用这个列，就像它是一个实际的表列一样

3 执行算术计算

计算字段另一常见用途是对检索出的数据进行算术计算，如orders表中包含所有的订单，orderitems表包含每个订单中各项物品，检索订单号为20005中的所有物品

现在可以使用计算字段来汇总价格

输出的expanded_price列为一个计算字段，此计算为quantity*item_price，客户机就可以使用这个列像使用其它列一样

第十一章 使用数据处理函数

1 函数

同大多数计算机语言一样，SQL支持利用函数来处理数据，函数一般是在数据上执行的，它给数据的转换和处理提供了方便

函数没有SQL的可移植性强：
能运行在多个系统上的代码称为可移植的，相对来说多数SQL语句就是可移植的，但函数的可移植性却不强，几乎每种主要的DBMS的实现都支持其它实现不支持的函数，而且有时差异还很大，如果你决定使用函数，保证做好代码注释

2 使用函数

大多数SQL实现支持以下类型的函数：
1，用于处理文本串(如删除，值填充，转换大小写)的文本函数
2，用于数据上的算术操作(返回值，进行代数运算)的数值函数
3，用于处理日期和时间值并从这些值中提取特定的成分(如返回两个日期之差，检查日期有效性等)的日期和时间函数
4，返回DBMS正使用的特殊信息(如返回用户登录信息，检查版本细节)的系统函数

(1) 文本处理函数

使用upper()将文本转换成大写：

常用文本处理函数：

left()			返回串左边的字符
length()		返回串的长度
locate()		找出串的一个子串
lower()			将串转换成小写
ltrim()			去掉左边空格
right()			返回串右边的字符
rtrim()			去掉串右边的空格
soundex()		返回串的soundex值
substring()		返回子串的字符
upper()			将串转换为大写

对于soundex()函数，它可以将任何文本串转换为描述其语音表示的字母数字模式的算法，soundex考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较

如customers表中有一个顾客名为Y.Lee，但如果这是输入错误，此联系名实际为Y.Lie，按正确的联系名搜索不会返回数据，使用soundex()函数进行搜索，它匹配所有发音类似于Y.Lie的联系名

因为Y.Lee和Y.Lie发音类似，所以它们的soundex值匹配，因此where子句正确的过滤了需要的数据

(2) 日期和时间处理函数

日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效地排序和过滤，并且节省物理存储空间

一般而言，应用程序不该使用用来存储时间和日期的格式，因此日期和时间函数总是被用来读取，统计和处理这些值，由于这个原因，日期和时间函数在MySQL中具有重要的作用

日期的格式必须为yyyy-mm-dd，2020年10月24日必须写为2020-10-24

上述的检索方式并不总是正确的，因为order_date的数据类型都是datetime，这种类型存储日期以及时间值，样例中的值全部具有时间值00：00：00，但实际中却很可能不是这样，如存储的order_date值为2005-09-01 11：35：05
那么where order_date = '2005-09-01’会失败，解决办法是指示MySQL仅将给出的日期与列中的日期部分比较，而不是将给出的日期和整个列值比较，为此必须使用date()函数

想要确定一段日期的方法：

(3) 数值处理函数

数值处理函数仅仅处理数值数据，一般主要用于代数，三角或几何运算

abs()		返回一个数的绝对值
cos()		返回一个角度的余弦
exp()		返回一个数的指数值
mod()		返回除法操作的余数
pi()		返回圆周率
rand()		返回一个随机数
sin()		返回一个角度的正弦
sqrt()		返回一个数的平方根
tan()		返回一个角度的正切

第十二章 汇总数据

1 聚集函数

我们经常需要汇总数据而不是将它们实际检索出来，为此MySQL提供了专门的函数，使用这些函数，MySQL查询可用于检索数据，以便分析和生成报表

聚集函数：运行在行组上，计算和返回单个值的函数

avg()		返回某列平均值
count()		返回某列的行数
max()		返回某列的最大值
min()		返回某列的最小值
sum()		返回某列值之和

(1) avg()函数

avg()通过对表中行数计算并计算特定列值之和，求得该列的平均值，avg()可以返回所有列的平均值，也可以用来返回特定列或行的平均值

所有产品的均值：

编号为1003产品的均值：

avg()只能用来确定特定数值列的平均值，而且列名必须作为函数参数给出，为了获得多个列的平均值必须用多个avg()函数，avg()忽略列值为null的行

(2) count()函数

count()函数进行计数，可以用count()确定表中行的数目或符合特定条件的行的数目

count()有两种使用方式：
1，使用count(*)对表中行的数目进行计算，不管表列中包含的是空值还是非空值
2，使用count(column)对特定列中具有值的行进行计算，忽略null值

5个客户中只有3个有电子邮件地址

(3) max()函数

max()返回指定列中最大行，max()要求指定列名

max()忽略列值为null的行

(4) min()函数

min()忽略列值为null的行

(5) sum()函数

sum()用来返回指定列值的和(总计)

利用标准的算术操作符，所有聚集函数都可以用来执行多个列上的计算

sum()忽略列值为null的值

2 聚集不同值

对以上的5个聚集函数都可以使用：
1，对所有的行执行计算，指定all参数或不给参数(all是默认行为)
2，只包含不同的值，指定distinct参数

使用了distinct后，此例子中的arg_price比较高，由于多个物品具有相同的较低价格，被distinct排除了导致提升了平均价格

3 组合聚集函数

select语句可以根据需要包含多个聚集函数：

在指定别名时不要用实际表中的列名，使用唯一的名字使你的SQL语句更易于理解和使用