sql 基础--mysql 5 （4）-优快云博客

本文详细介绍了MySQL中常用的文本处理函数如Upper和Soundex，以及数值处理函数如Sqrt的应用实例。通过具体示例展示了如何使用这些函数进行数据转换和计算。

9.数据处理函数

　　文本处理：Upper 大写

mysql> select uid,name,msg, Upper(name) as UpperName from pw_luck;
+-----+-----------+------+-----------+
| uid | name      | msg  | UpperName |
+-----+-----------+------+-----------+
|   0 | Wilson    |  100 | WILSON    |
|   1 | zhangsan  |  100 | ZHANGSAN  |
|   2 | lisi      | 1001 | LISI      |
|   3 | wang5     | 1001 | WANG5     |
|   7 | zhangsan7 | 1000 | ZHANGSAN7 |
|   8 | zhangsan8 | 1000 | ZHANGSAN8 |
|   9 | zhangsan9 | 1000 | ZHANGSAN9 |
|  10 | wang5     | 1000 | WANG5     |
|  11 | wang5%    | 2000 | WANG5%    |
|  12 | WANG5     | 1000 | WANG5     |
+-----+-----------+------+-----------+
10 rows in set (0.02 sec)

　　文本处理：Soundex匹配所有发音类似的项

mysql> select uid,name,msg, Upper(name) from pw_luck where Soundex(name)=Soundex('wangwu');
+-----+--------+------+-------------+
| uid | name   | msg  | Upper(name) |
+-----+--------+------+-------------+
|   3 | wang5  | 1001 | WANG5       |
|  10 | wang5  | 1000 | WANG5       |
|  11 | wang5% | 2000 | WANG5%      |
|  12 | WANG5  | 1000 | WANG5       |
+-----+--------+------+-------------+
4 rows in set (0.03 sec)

　　日期和时间处理函数

　　　　Data（） 2012-12-12

　　　　Year（） 2012

　　　　Mounth（） 2

　　数值处理： Abs绝对值 Cos余弦 Exp指数 Mod余数 Sqrt平方根

mysql> select name,msg,Sqrt(msg) from pw_luck;
+-----------+------+--------------------+
| name      | msg  | Sqrt(msg)          |
+-----------+------+--------------------+
| Wilson    |  100 |                 10 |
| zhangsan  |  100 |                 10 |
| lisi      | 1001 |  31.63858403911275 |
| wang5     | 1001 |  31.63858403911275 |
| zhangsan7 | 1000 | 31.622776601683793 |
| zhangsan8 | 1000 | 31.622776601683793 |
| zhangsan9 | 1000 | 31.622776601683793 |
| wang5     | 1000 | 31.622776601683793 |
| wang5%    | 2000 | 44.721359549995796 |
| WANG5     | 1000 | 31.622776601683793 |
+-----------+------+--------------------+
10 rows in set (0.02 sec)

转载于:https://www.cnblogs.com/wjw334/p/4288080.html