使用数据处理函数

最新推荐文章于 2023-03-22 21:17:08 发布

lemon_EM

最新推荐文章于 2023-03-22 21:17:08 发布

阅读量229

点赞数

分类专栏： MySQL必知必会文章标签： mysql

本文链接：https://blog.csdn.net/butbabuc/article/details/107970868

版权

MySQL必知必会专栏收录该内容

22 篇文章 0 订阅

订阅专栏

2019-08-06

MySQL必知必会（6）：常用文本字符串处理函数

大多数`SQL`实现支持一下类型的函数。

用于处理文本串（如删除或填充值，转换值为大写或小写）的文本函数。
用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
用于处理日期和时间值并从这些值中提取特定成分（例如，返回两个日期之差，检查日期有效性等）的日期和时间函数。
返回DBMS正使用的特殊信息（如返回用户登录信息，检查版本细节）的系统函数。

函数没有`SQL`的可移植性强

能运行在多个系统上的代码称为可移植的（portable）。相对来说，多数SQL语句是可移植的，在SQL实现之间有差异时，这些差异通常不那么难处理。而函数的可移植性却不强。几乎每种主要的DBMS的实现都支持其他实现不支持的函数，而且有时差异还很大。
为了代码的可移植性，许多SQL程序员不赞成使用特殊实现的功能。虽然这样做很有好处，但不总是利于应用程序的性能。如果决定使用函数，应该保证做好代码注释，以便以后能确切地知道所编写SQL代码的含义。

1. 文本处理函数

常见的文本处理函数

函数	说明
`Left()`	返回串左边的字符
`Right()`	返回串右边的字符
`Length()`	返回串的长度
`Locate()`	找出串的一个子串
`SubString()`	返回子串的字符
`Upper()`	将串转换为大写
`Lower()`	将串转换为小写
`LTrim()`	去掉串左边的空格
`RTrim()`	去掉串右边的空格
`Soundex()`	返回串的`SOUNDEX`值

表中的SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。虽然SOUNDEX不是SQL概念，但MySQL（就像多数DBMS一样）都提供对SOUNDEX的支持。
下面给出一个使用Soundex()函数的例子。customers表中有一个顾客Coyote Inc.，其联系名为Y.Lee。但如果这是输入错误，此联系名实际应该是Y.Lie，怎么办？显然，按正确的联系名搜索不会返回数据，如下所示：

mysql> SELECT cust_name, cust_contact
    -> FROM customers
    -> WHERE cust_contact = 'Y. Lie';
Empty set (0.00 sec)

mysql> SELECT cust_name, cust_contact
    -> FROM customers
    -> WHERE Soundex(cust_contact) = Soundex('Y Lie');
+-------------+--------------+
| cust_name   | cust_contact |
+-------------+--------------+
| Coyote Inc. | Y Lee        |
+-------------+--------------+
1 row in set (0.04 sec)

使用Soundex()函数进行搜索，它匹配所有发音类似于Y.Lie的联系名。因为Y.Lee和Y.Lie发音相似，所有它们的SOUNDEX值匹配，因此WHERE字句正确地过滤出了所需的数据。

mysql>  SELECT vend_name, Upper(vend_name) AS vend_name_upcase
    ->  FROM vendors
    ->  ORDER BY vend_name;
+----------------+------------------+
| vend_name      | vend_name_upcase |
+----------------+------------------+
| ACME           | ACME             |
| Anvils R Us    | ANVILS R US      |
| Furball Inc.   | FURBALL INC.     |
| Jet Set        | JET SET          |
| Jouets Et Ours | JOUETS ET OURS   |
| LT Supplies    | LT SUPPLIES      |
+----------------+------------------+
6 rows in set (0.04 sec)

LOCATE(substr,str):返回子串substr在字符串str第一个出现的位置(从 1 开始)，如果substr不是在str里面，返回0.

mysql> SELECT locate('bar', 'foobarbar');
+----------------------------+
| locate('bar', 'foobarbar') |
+----------------------------+
|                          4 |
+----------------------------+
1 row in set (0.06 sec)

mysql> SELECT locate('xbar', 'foobar');
+--------------------------+
| locate('xbar', 'foobar') |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

LEFT(str,len):返回字符串str的最左面len个字符。
RIGHT(str,len):返回字符串str的最右面len个字符。

mysql> SELECT left('foobarbar', 5);
+----------------------+
| left('foobarbar', 5) |
+----------------------+
| fooba                |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT right('foobarbar', 4);
+-----------------------+
| right('foobarbar', 4) |
+-----------------------+
| rbar                  |
+-----------------------+
1 row in set (0.00 sec)

SUBSTRING(str,pos):返回字符串str从pos位置（包括）开始之后的子串。

mysql>  SELECT substring('Quadratically', 5);
+-------------------------------+
| substring('Quadratically', 5) |
+-------------------------------+
| ratically                     |
+-------------------------------+
1 row in set (0.00 sec)

2. 日期和时间处理函数

常用日期和时间处理函数

函数	说明
`AddDate()`	增加一个日期（天、周等）
`AddTime()`	增加一个时间（时、分等）
`CurDate()`	返回当前日期
`CurTime()`	返回当前时间
`Now()`	返回当前日期和时间
`Date()`	返回日期时间的日期部分
`Time()`	返回日期时间的时间部分
`DateDiff()`	计算两个日期之差
`Date_Add()`	高度灵活的日期运算函数
`Date_Format()`	返回一个格式化的日期或时间串
`DateOfWeek()`	对于一个日期，返回对应的星期几
`Hour()`	返回一个时间的小时部分
`Minute()`	返回一个时间的分钟部分
`Second()`	返回一个时间的秒部分
`Year()`	返回一个日期的年份部分
`Month()`	返回一个日期的月份部分
`Day()`	返回一个日期的天数部分

`MySQL`使用的日期格式

无论什么时候指定一个日期，不管是插入或更新表值还是用WHERE子句进行过滤，日期必须为格式yyyy-mm-dd。因此，2005年9月1日，给出为2005-09-01。虽然其他的日期格式可能也行，但这是首选的日期格式，因为它排除了多义性（如，04/05/06是2006年5月4日或2006年4月5日或2004年5月6日或…）。
应该总是使用4位数字的年份，MySQL支持2位数字的年份，MySQL处理00-69位2000-2069，处理70-99位1970-1999。虽然它们可能是打算要的年份，但使用完整的4位数字年份更可靠，因为MySQL不必作出任何假定。

mysql> SELECT cust_id, order_num
    -> FROM orders
    -> WHERE order_date = '2005-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+
1 row in set (0.11 sec)

此SELECT语句正常运行。但是，使用WHERE order_date = '2005-09-01'可靠吗？order_date的数据类型为datetime。这种类型存储日期及时间值。样例表中的值全部具有时间值00：00：00，但实际中很可能并不总是这样。如果用当前日期和时间存储订单日期（因此你不仅知道订单日期，还知道下订单当天的时间），怎么办？比如，存储的order_date值为2005-09-01 11:30:05，则WHERE order_date = '2005-09-01'失败。即使给出具有该日期的一行，也不会把它检索出来，因为WHERE匹配失败。
解决办法是指示MySQL仅将给出的日期与列中的日期部分进行比较，而不是将给出的日期整个列值进行比较。为此，必须使用Date()函数。此函数指示MySQL仅提取列的日期部分。如下：

mysql> SELECT cust_id, order_num
    -> FROM orders
    -> WHERE Date(order_date) = '2005-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+
1 row in set (0.07 sec)

如果你想要的仅是日期，则使用Date()是一个良好的习惯，即使你知道相应的列只包含日期也是如此。这样，如果由于某种原因表中以后有日期和时间值，你的SQL代码也不用改变。当然，也存在一个Time()函数，在你只想要时间时应该使用它。

如果想检索出2005年9月下的所有订单，怎么办？简单的相等测试不行，因为它也要匹配月份中的天数。

如下几种解决办法：

mysql> SELECT cust_id, order_num
    -> FROM orders
    -> WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
+---------+-----------+
3 rows in set (0.00 sec)

mysql># 不需要记住每个月中有多少天或不需要操心闰年2月的办法
mysql> SELECT cust_id, order_num
    -> FROM orders
    -> WHERE Year(order_date) = 2005 AND Month(order_date) = 9;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10003 |     20006 |
|   10004 |     20007 |
+---------+-----------+
3 rows in set (0.00 sec)

3.数值处理函数

常用数值处理函数

函数	说明
`Abs()`	返回一个数的绝对值
`Sin()`	返回一个角度的正弦
`Cos()`	返回一个角度的余弦
`Tan()`	返回一个角度的正切
`Exp()`	返回一个数的指数值
`Mod()`	返回除操作数的余数
`Pi()`	返回圆周率 $\pi$
`Rand()`	返回一个随机数
`Sqrt()`	返回一个数的平方根