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
  • 点赞
  • 收藏
  • 关注作者

评论(0

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

全部回复

上滑加载中

设置昵称

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

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

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