MySQL 学习实践笔记（三）

最新推荐文章于 2022-04-13 22:44:02 发布

0xE8551CCB

最新推荐文章于 2022-04-13 22:44:02 发布

阅读量574

点赞数

分类专栏：【06 MySQL】文章标签： MySQL 正则函数过滤

本文链接：https://blog.csdn.net/xE8551CCB/article/details/52421375

版权

【06 MySQL】专栏收录该内容

3 篇文章 0 订阅

订阅专栏

引言

前面一节，我们重点实践了如何使用 SELECT 语句进行检索，以及如何进行排序、过滤等操作。但是前面介绍的过滤方法（简单匹配、比较、通配符匹配等）只能完成相对简单的过滤工作，但一般也就够用了。本节将会涉及如何在 SQL 语句中使用正则表达式来进行更加复杂的过滤操作；此外，我们将会学习如何拼接字段、如何使用函数等知识。

正则过滤

在 MySQL 中，我们可以使用 WHERE 子句并结合关键字 REGEXP 对 SELECT 的结果进行匹配过滤操作。需要注意的是，MySQL 只是对正则表达式操作的子集提供了支持。正则表达式的一些基础就不再多言，下面重点看看如何使用正则表达式。

字符匹配

默认匹配时，字母是不区分大小写的：
- 语句：SELECT cust_id, cust_name FROM customers WHERE cust_name REGEXP 'Yosemite';
- 说明：表示使用正则表达式模式匹配出记录中含有 Yosemite 子串的行，并返回结果。
- 输出：

我们可以使用关键字 BINARY 实现大小写区分匹配：


# 有两条匹配的记录被输出

SELECT vend_id, prod_id, prod_name FROM products WHERE prod_name REGEXP 'jet';


# 使用 BINARY 后，由于区分大小写，故不再有满足要求的记录了

SELECT vend_id, prod_id, prod_name FROM products WHERE prod_name REGEXP BINARY 'jet';

或匹配

语句：


# 句法一

SELECT vend_id, prod_id, prod_name FROM products WHERE vend_id REGEXP '1001|1003|1005';


# 句法二：

SELECT vend_id, prod_id, prod_name FROM products WHERE vend_id REGEXP '100[135]';

说明：用以匹配 vend_id 为 1001 或 1003 或 1005 的所有记录。类似于 OR 逻辑操作符。
输出：

匹配若干个字符

匹配若干指定字符：
- 语句：SELECT vend_id, prod_id, prod_name FROM products WHERE prod_name REGEXP '[12] ton' ORDER BY vend_id;
- 说明：[12] 表示一组字符，可以匹配 1 或 2。因此上述语句执行后将会返回包含 1 或者 2 的产品名称等记录，结果按照 vend_id 排序。
- 输出：
排除集合中指定的字符：
- 语句：SELECT vend_id, prod_id, prod_name FROM products WHERE prod_name REGEXP '[^12] ton' ORDER BY vend_id;
- 说明：所有不包含 1 或 2的名称，并将检索结果按照 vend_id 排序。
- 输出：

匹配范围

数字：[0-9]；
字母：[a-zA-Z]
字母数字：[0-9a-zA-Z]
使用示例：
- 语句：SELECT vend_id, prod_id, prod_name FROM products WHERE prod_id REGEXP 'ANV0[0-9]' ORDER BY vend_id;
- 说明：表示匹配产品 id 含有 ANV0 再加一个数字的所有记录，检索结果按照 vend_id 排序。
- 输出：

匹配特殊字符

特殊字符主要有 ., ‘(‘, ‘\’ 等等。我们需要使用转义的方式对这些特殊字符匹配。MySQL 要求使用两个 \\ 作为转义标记，MySQL 解释一个，另一个交给正则表达式库解析。
使用示例：
- 语句：SELECT vend_id, prod_id, prod_name FROM products WHERE prod_name REGEXP '\\.' ORDER BY vend_id;
- 说明：表示匹配名称中含有小数点的记录，结果按照 vend_id 排序。
- 输出：

匹配字符类

预定义的字符集，即字符类（character class）表示了一些常用的匹配模式。

[:alnum:]：等同 [a-zA-Z0-9]
[:alpha:]：等同[a-zA-Z]`
[:blank:]：空格和制表符，等同 [\\t]
[:cntrl:]：ASCII 控制字符（0-31 和 127）
[:digit:]：等同 [0-9]
[:graph:]：等同 [:print:]，但不含空格
[:lower:]：等同 [a-z]
[:upper:]：等同 [A-Z]
[:punct:]：不在 [:alnum:] 和 [:cntrl:] 中的任何字符
[:space:]：等同 [\\f\\n\\r\\t\\v]
[:xdigit:]：任意十六进制数，等同 [a-fA-F0-9]

定位符

^：文本开头
$：文本结尾
[[:<:]]：词开头
[[:>:]]：词结尾

简单正则表达式测试

我们可以不使用数据库表来测试正则表达式，通过返回值 0（不匹配）或 1（匹配）判断匹配情况。示例语句：SELECT 'hello, world! 123' REGEXP '[:digit:]';，结果如下：

关于 LIKE 与 REGEXP

我们需要注意的是，LIKE 匹配整串，而 REGEXP 则匹配字符串中的子串。当然，我们可以借助定位符 ^ 和 $ 实现和 LIKE 类似的操作。

# 没有输出结果
SELECT cust_id, cust_name FROM customers WHERE cust_name LIKE 'Yosemite';

# 得到预期结果
SELECT cust_id, cust_name FROM customers WHERE cust_name LIKE '%Yosemite%';

计算字段

什么是计算字段？如何创建并引用计算字段？这是本小节重点内容。

字段（filed）：基本上与列类似，不过数据库列一般称为列；而字段常用于计算字段的连接上。
计算字段是在运行 SELECT 语句时创建的，它并不存在于数据库中。我们有时候需要将检索的结果直接进行转换、计算或者格式化等操作（并不是把这些工作交给客户端程序完成），这时就需要计算字段来完成了。只有数据库知道 SELECT 语句中哪些列是实际的表列，哪些是计算字段，对于客户端而言是透明的，本质上会“一视同仁”。

拼接字段

在 MySQL 中，字段拼接需要使用函数 Concat() 完成，不同于其它数据库使用 +, || 实现。拼接（concatenate）的作用就是将值链接到一块，从而构成单一的值。

简单的拼接示例：
- 语句：SELECT Concat(cust_name, ' => ', cust_country) FROM customers ORDER BY cust_id;
- 说明：上述表达式会将客户名称和国家之间使用 => 连接在一起，构成一个新字符串，结果会按照 id 排序。实际上可以看到，它会创建并输出一个新的列（匿名）。
- 结果：
结合 Trim() 函数移除多余的空格：SELECT Concat(Trim(cust_name), ' => ', Trim(cust_country)) FROM customers ORDER BY cust_id;。
此外，还有 RTrim() 和 LTrim()。可以类比 Python 中的 strip(), lstrip() 和 rstrip() 函数。

使用别名

我们可以看到，上面给出的字段拼接语句执行的结果只是返回一个新的未命名列，即一个值。我们无法在客户端程序中引用它，为此，我们需要使用别名（alias）来实现。即，使用 AS 关键字新建一个字段名称。此外，别名还可以用来重命名原列表中含有不合规定字符的列名等。

别名，有时也叫作导出列（derived column）。

语句：SELECT Concat(Trim(cust_name), ' => ', Trim(cust_country)) AS cust_info FROM customers ORDER BY cust_id;
说明：上述表达式将计算字段的名称指定为 cust_info，这样我们就可以轻松地引用计算字段值了，就像引用一个实际存在的列一样。
结果：

执行算术运算

我们可以直接在 SQL 语句中进行算术运算，这样就不需要客户机处理了。我们可以直接将计算的结果返回，多么方便。

在实际使用时，我们需要注意计算优先级问题，必要时需要添加括号标记。MySQL 算术操作符支持：+, -, *, /。

示例用法：
- 语句：SELECT vend_id, prod_id, prod_price / 2.0 AS new_price FROM products;
- 说明：通过在 SQL 中使用算术运算符，可以得到一个新的计算字段列 new_price。
- 结果：

计算测试

类似于测试正则表达式，我们也可以测试函数与计算。使用示例如下：

SELECT Now();
SELECT 100 * 200 AS result;

数据处理函数

其实在上一小节就给出了 Concat() 函数和 Trim() 函数的使用方法，所以，还是比较简单的。本节主要总结一些常用的函数。

我们需要注意的是，SQL 语句的移植性要高于函数，实际上很多不同数据库管理系统都包含一些其它 DBMS 所没有的函数实现。所以，我们在编写含有函数的 SQL 语句时最好加上注释，方便以后查阅。

文本处理函数

下面列出的是一些常用的文本处理函数：
- Left()：返回左边的字符；
- Length()：返回字符串的长度；
- Locate()：搜索一个子串；
- Lower()：字符串转小写；
- LTrim()：去除左边空白字符；
- Right()：返回右边的字符；
- RTrim()：去除右边空白字符；
- Trim()：不解释了；
- Soundex()：返回 SOUNDEX 值；
- SubString()：提取字符串；
- Upper()：字符串转大写。
不知道函数用法：help function_name 就可以了！
示例： Upper()
- 语句：SELECT cust_id, Upper(cust_name) FROM customers ORDER BY cust_id;
- 结果：
示例：Left()
- 语句：SELECT cust_id, Left(cust_name, 4) AS temp_name FROM customers ORDER BY cust_id;
- 结果：

日期和时间处理函数

日期和时间采用的是与之相应的数据类型和特殊的格式进行存储，从而可以快速和有效地进行排序或过滤，同时节省物理存储空间。
常用的日期和时间处理函数列表：
- AddDate()：增加日期（天、周等）；
- AddTime()：增加时间（时、分等）；
- CurDate()：获取当前日期；
- CurTime()：获取当前时间；
- Date()：返回日期时间和日期部分；
- DateDiff()：计算日期之差；
- Date_Add()：灵活的日期运算函数；
- Date_Format()：格式化日期和时间；
- Day()：返回日期的天数；
- DayOfWeek()：返回星期几；
- Hour()：返回小时；
- Minute()：返回分钟；
- Month()：返回月份；
- Now()：返回当前日期和时间；
- Second()：返回时间的秒；
- Time()：返回日期时间的时间部分；
- Year()：返回日期的年份部分。
在 SQL 中使用日期时，格式应当为 yyyy-mm-dd，当然，年份也可以是两位数，不过推荐使用四位年份。
如果要使用日期，就请使用 Date() 函数。
简单使用示例：

# 得到一个由字符串转换的日期（包括时间）
SELECT Date('2016-09-02 14:00') AS date;

SELECT Time(Now());

SELECT DAYOFWEEK(NOW());

过滤指定日期：
- 语句：SELECT cust_id, order_num FROM orders WHERE Date(order_date) = '2005-09-12';
- 结果：

过滤日期范围示例：

语句：

SELECT cust_id, order_num FROM orders WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';


# 另外一种写法

SELECT cust_id, order_num FROM orders WHERE Year(order_date) = 2005 AND Month(order_date) = 9 ;

结果：

数值处理函数

常用的数值处理函数有：Abs(), Cos(), Exp(), Mod(), Pi(), Rand(), Sin(), Sqrt(), Tan()。
使用示例如下图：

汇总数据

MySQL 为我们提供了一些实用的聚集函数，用于分析数据和报表生成。也就是说，我们无需先把数据检索出来，交给应用程序进行相关的计算（这样做比较低效）。
聚集函数（aggregate function）：运行在组件上，计算和返回单个值的函数。
SQL 聚集函数列表（常用的）：
- AVG()：返回列均值；
- COUNT()：返回列行数；
- MAX()：返回：返回列最大值；
- MIN()：返回列最小值；
- SUM()：返回列之和。
AVG() 函数：
1. 它只能用于单列，如果需要对多列计算，需要使用多个该函数；
2. 它会忽略值为 NULL 的行；
3. 使用示例：
```
SELECT AVG(prod_price) FROM products;
SELECT AVG(prod_price) FROM products WHERE prod_id LIKE 'ANV%';
```
COUNT() 函数：
1. COUNT(*) 对整个表的行数进行计算，不管表列中包含的是空值（NULL）还是非空值；
2. COUNT(column) 则针对特定的列统计行数，但会忽略空值行（NULL）。
3. 使用示例：
```
SELECT COUNT(*) FROM products;
SELECT COUNT(vend_id) AS count FROM products;
```

MAX() 和 MIN() 函数：

对特定列进行计算，获取最值；
它们会忽略 NULL 行；

使用示例：


# 应用于数值

SELECT MAX(prod_price) AS max_price, MIN(prod_price) AS min_price FROM products;


# 应用于日期

SELECT MAX(order_date) AS max_date, MIN(order_date) AS min_date FROM orders;


# 应用于字符串

SELECT MAX(prod_name) AS max_name, MIN(prod_name) AS min_name FROM products;

SUM() 函数：
1. 可以应用于某一列进行求和计算，也可以用于合计计算值；
2. 它会忽略值为 NULL 的行；
3. 使用示例：
```
SELECT SUM(prod_price) AS total FROM products;
SELECT COUNT(*) AS count, SUM(item_price * quantity) AS total FROM orderitems;
```
聚集不同的值，聚集函数默认对整个列（ALL）执行计算，但是也可以指定 DISTINCT 关键字对不同的值计算。示例如下：
```
SELECT AVG(DISTINCT prod_price) AS price FROM products;
```

综合应用示例：

SELECT COUNT(*) AS total_lines,
AVG(DISTINCT prod_price) AS a_price,
MAX(prod_price) AS max_price,
MIN(prod_price) AS min_price,
SUM(prod_price) AS total_price
FROM products;