MySQL之函数

举报
李子捌 发表于 2021/11/17 09:11:56 2021/11/17
【摘要】 本专栏会持续输出MySQL系列文章，祝大家每天进步亿点点！文末私信作者，我们一起去大厂。本篇总结的是 《MySQL之函数》，后续会每日更新~关于《Redis入门到精通》、《并发编程》、《Java全面入门》、《鸿蒙开发》等知识点可以参考我的往期博客相信自己，越活越坚强，活着就该逢山开路，遇水架桥！生活，你给我压力，我还你奇迹！1、简介MySQL内置了很多函数用于各种场景数据处理。函数往往能够处...
  • 本专栏会持续输出MySQL系列文章，祝大家每天进步亿点点！文末私信作者，我们一起去大厂。
  • 本篇总结的是 《MySQL之函数》，后续会每日更新~
  • 关于《Redis入门到精通》、《并发编程》、《Java全面入门》、《鸿蒙开发》等知识点可以参考我的往期博客
  • 相信自己，越活越坚强，活着就该逢山开路，遇水架桥！生活，你给我压力，我还你奇迹！

751a8ffe445fbc514b868f41fb1efccd.jpeg


1、简介

MySQL内置了很多函数用于各种场景数据处理。函数往往能够处理文本数据、日期数据、数值数据以及返回系统相关信息；除普通函数之外MySQL还内置了一些聚集函数，用于对表中数据进行汇总、求和等操作。


聚集函数：

指的是运行在行组上，计算和返回单个值的函数。


使用MySQL的函数有很多好处：

  1. SQL编写简易、美观
  2. 计算速度快于客户机
  1. 减少数据返回，节省网络带宽资源

不过MySQL函数不一定在其他数据库系统支持（语法不同），因此SQL的移植性比较差。



2、正文

2.1 文本处理函数

文本处理函数能够对文本进行大小写转换、组合、去除空格等操作。


文本函数表：

函数

函数说明

left()

返回左边的字符

right()

返回右边的字符

substring()

字符串截取

trim()

去除两边空格

rtrim()

去除右边空格

ltrim()

去除左边空格

length()

返回字符串长度

lower()

将字符串转换成小写

upper()

将字符串转换成大写

locate()

找出字符串的子串

soundex()

返回字符串的soundex值


举例：

转小写lower()

mysql> select lower('HUAWEI');
+-----------------+
| lower('HUAWEI') |
+-----------------+
| huawei          |
+-----------------+

转大写upper()

mysql> select upper('alibaba');
+------------------+
| upper('alibaba') |
+------------------+
| ALIBABA          |
+------------------+

返回字符串长度length()

mysql> select length('liziba');
+------------------+
| length('liziba') |
+------------------+
|                6 |
+------------------+

字符串截取substring()

mysql> select substring('I am Liziba', 6, 10);
+---------------------------------+
| substring('I am Liziba', 6, 10) |
+---------------------------------+
| Liziba                          |
+---------------------------------+

soundex()可以找到发音相似字符串（这个挺有意思的！）

mysql> select soundex('liziba') = soundex('leezibe');
+----------------------------------------+
| soundex('liziba') = soundex('leezibe') |
+----------------------------------------+
|                                      1 |
+----------------------------------------+

mysql> select soundex('liziba') = soundex('lizijiu');
+----------------------------------------+
| soundex('liziba') = soundex('lizijiu') |
+----------------------------------------+
|                                      0 |
+----------------------------------------+


2.2 日期处理函数

日期函数在MySQL中非常重要，因为我们经常需要对日期进行格式化、提取日期的年月日、计算日期的产值等等。


日期函数表：

函数

函数说明

CurDate()

返回当前日期

CurTime()

返回当前时间

AddDate()

增加一个日期的天、周等

AddTime()

增加一个时间的时、分等

Date()

返回日期的时间的部分

DateDiff()

计算两个日期之差

Date_Add()

日期运算函数

Date_Format()

日期格式化函数

Day()

返回日期天数部分

DayOfWeek()

返回日期属于星期几

Year()

返回一个日期的年份部分

Month()

返回一个日期的月份部分

Time()

返回一个日期的时间部分

Hour()

返回时间的小时部分

Minute()

返回时间的分钟部分

Second()

返回时间的秒钟部分

Now()

返回当前日期和时间

举例：

查询当前日期和时间

mysql> select now() as now;
+---------------------+
| now                 |
+---------------------+
| 2021-11-12 23:39:49 |
+---------------------+

查询当前年份

mysql> select year(now()) as year;
+------+
| year |
+------+
| 2021 |
+------+

查询当前星期几（注意MySQL中计算的星期几，返回的是工作日索引，星期日等于1，星期六等于7）

mysql> select dayOfWeek(now()) as week;
+------+
| week |
+------+
|    6 |
+------+

日期格式化

mysql> select date_format(now(), '%Y-%m-%d') as date;
+------------+
| date       |
+------------+
| 2021-11-12 |
+------------+

计算日期之差

mysql> select DateDiff('2021-11-12 11:00:00', '2000-12-12 11:00:00') as dateDiff;
+----------+
| dateDiff |
+----------+
|     7640 |
+----------+


2.3 数值处理函数

数值处理函数用于数值的处理；一般用于代数运算、三角运算、几何运算等。


数值函数表：

函数

函数说明

abs()

绝对值

cos()

余弦值

sin()

正弦值

tan()

正切

exp()

指数值

sqrt()

平方根

rand()

随机数

pi()

圆周率

mod()

除数的余数

举例：

计算绝对值

mysql> select abs(-1998) as abs;
+------+
| abs  |
+------+
| 1998 |
+------+

返回π的值

mysql> select pi() as pi;
+----------+
| pi       |
+----------+
| 3.141593 |
+----------+

返回一个小于1大于0随机数

mysql> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.7134191456375822 |
+--------------------+

计算除数的余数

mysql> select mod(8, 2) as remain;
+--------+
| remain |
+--------+
|      0 |
+--------+

mysql> select mod(8, 3) as remain;
+--------+
| remain |
+--------+
|      2 |
+--------+


2.4 聚集函数

有些时候我们不需要返回表数据中所有的列，而只需要对表中的数据进行汇总，或者对表行组数据执行相关计算；此时可以使用MySQL的聚集函数。


聚集函数表：

函数

函数说明

avg()

求平均值

count()

返回列的行数

max()

计算列的最大值

min()

计算列的最小值

sum()

计算列的值之和


准备一张产品表，如下所示：

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for product
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `product_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '产品名称',
  `price` decimal(10, 2) UNSIGNED NOT NULL COMMENT '产品价格',
  `number` int(11) NOT NULL COMMENT '产品数量',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES (1, 'Apple iPhone 13 (A2634)', 6799.00, 22);
INSERT INTO `product` VALUES (2, 'HUAWEI P50 Pro', 6488.00, 88);
INSERT INTO `product` VALUES (3, 'MIX4', 4999.00, 30);
INSERT INTO `product` VALUES (4, 'OPPO Find X3', 3999.00, 15);
INSERT INTO `product` VALUES (5, 'vivo X70 Pro+', 5999.00, 27);

SET FOREIGN_KEY_CHECKS = 1;


求单价平均数

mysql> select avg(price) as avg_price from product ;
+-------------+
| avg_price   |
+-------------+
| 5656.800000 |
+-------------+
1 row in set (0.00 sec)

求产品表行数（三种办法，大家可以自取，具体性能方面这是个需要深究的问题，这里不讨论）

mysql> select count(*) from product;
+----------+
| count(*) |
+----------+
|        5 |
+----------+

mysql> select count(price) from product;
+--------------+
| count(price) |
+--------------+
|            5 |
+--------------+

mysql> select count(1) from product;
+----------+
| count(1) |
+----------+
|        5 |
+----------+

求价格最大值

mysql> select max(price) max_price from product;
+-----------+
| max_price |
+-----------+
|   6799.00 |
+-----------+

单价求和

mysql> select sum(price) sum_price from product;
+-----------+
| sum_price |
+-----------+
|  28284.00 |
+-----------+

MySQL中的聚集函数可以组合使用，比如上面求平均值、求行数、求最大值、求和可以一起查询。

mysql> select avg(price) as avg_price, count(*) as row_size, max(price) as max_price, sum(price) as sum_price from product;
+-------------+----------+-----------+-----------+
| avg_price   | row_size | max_price | sum_price |
+-------------+----------+-----------+-----------+
| 5656.800000 |        5 |   6799.00 |  28284.00 |
+-------------+----------+-----------+-----------+


注意：上面有说到count()函数可以统计表的行数，但是如果我们希望统计的是针对某一列去重的行数，此时可以使用distinct关键字，但是它只能作用于指定字段，不能作用于*。


正确用法：

mysql> select count(distinct price) from product;
+-----------------------+
| count(distinct price) |
+-----------------------+
|                     5 |
+-----------------------+

错误用法：

mysql> select count(distinct *) from product;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) from product' at line 1


👇🏻 关注公众号 获取更多资料👇🏻

【版权声明】本文为华为云社区用户原创内容，转载时必须标注文章的来源（华为云社区）、文章链接、文章作者等基本信息， 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@huaweicloud.com
MySQL
  • 点赞
  • 收藏
  • 关注作者

评论（0）

0/1000
抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认 取消

加入云驻计划，成为创作者

  • 华为云周边好礼
  • 免费体验产品
  • 特殊身份标识
  • 线下官方门票
  • 内部专家零距离
  • 与10000+优质创作者共同成长
立即加入