MySQL之函数

admin • • 数据库
  •   备战2022春招或暑期实习，本专栏会持续输出MySQL系列文章，祝大家每天进步亿点点！文末私信作者，我们一起去大厂。
  • 本篇总结的是 《MySQL之函数》，后续会每日更新~
  • 关于《Redis入门到精通》、《并发编程》、《Java全面入门》、《鸿蒙开发》等知识点可以参考我的往期博客
  • 相信自己，越活越坚强，活着就该逢山开路，遇水架桥！生活，你给我压力，我还你奇迹！

目录

1、简介

2、正文

2.1 文本处理函数

2.2 日期处理函数

2.3 数值处理函数

2.4 聚集函数

1、简介

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

聚集函数：

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

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

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

不过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

 ?? 关注公众号 我们一起进大厂??   

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
THE END
分享
二维码
< <上一篇

)">
下一篇>>