MySQL函数深度剖析：聚合、日期与字符串函数详解
文章目录
- MySQL系列
- 引言
- 一、聚合函数
- 二、日期函数
- 三、字符串函数
- 四、数学函数
- 五、其他函数
引言
MySQL具备众多内置函数，可用于数据处理、计算执行以及格式转换等操作，本篇将会阐述MySQL中常用的一些函数。本文内容以实践为主
此处的函数较为基础，不再展开解释，若再赘述便有牵强之感
上篇文章：MySQL数据操作全流程：创建、读取、更新与删除实战
一、聚合函数
这部分函数较为基础
| 函数名 | 作用 | 示例 | 结果 |
|---|---|---|---|
SUM(col) |
求和 | SUM(amount) |
所有 amount 的总和 |
AVG(col) |
求平均值 | AVG(age) |
平均年龄 |
COUNT(col) |
统计非NULL值的数量 | COUNT(id) |
行数 |
COUNT(*) |
统计总行数（包含NULL） | COUNT(*) |
总行数 |
MAX(col) |
求最大值 | MAX(score) |
最高分数 |
MIN(col) |
求最小值 | MIN(price) |
最低价格 |
测试表
CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);
create table exam_result (
id int unsigned primary key auto_increment,
name varchar(20) not null comment '同学姓名',
chinese float default 0.0 comment '语文成绩',
math float default 0.0 comment '数学成绩',
english float default 0.0 comment '英语成绩'
);
表内容
本文主要以上述两表进行测试，上篇文章已创建，此处直接使用
1、统计班级共有多少同学
select count(*) from students;
2、统计班级有多少 qq 号
select count(qq) from students;
对比上表可知count函数对NULL值不进行统计。
3、统计本次考试的数学成绩分数个数
select count(math) from exam_result;
对比上表可知count函数对重复值不进行统计。
4、统计数学成绩不及格人数
select count(math) from exam_result where math<60;
count函数可配合其他语句使用。
5、统计平均总分
select avg(math+chinese+english) 平均总分 from exam_result ;
6、返回英语最高分
select max(english) from exam_result ;
7、返回 > 70 分以上的数学最低分
select min(math) from exam_result where math >70;
二、日期函数
1、获取当前年月日
select current_date();
2、获取当前时分秒
select current_time;
3、获取时间戳
select current_timestamp;
4、在时间中提取日期部分
select date(current_timestamp());
5、在日期的基础上加上日期
select date_add(current_date,interval 10 day);
获取当前日期，并在该日期基础上增加十天
interval后可根据需求使用不同单位（年、月、日、分、秒)
6、在日期的基础上减去日期
select date_sub(current_date,interval 10 day);
获取当前日期，并在该日期基础上减去十天
7、计算两个日期之间相差多少天
select datediff(current_date,'1949-10-01');
中国成立，距今多少天
8、获取当前日期和时间
select now();
9、测试
//创建一个留言表
create table msg (
id int primary key auto_increment,
content varchar(30) not null,
sendtime datetime
);
//向表中插入测试数据
insert into msg(content,sendtime) values('hello1', now());
insert into msg(content,sendtime) values('hello2', now());
select * from msg;
显示所有留言信息，发布日期只显示日期，不用显示时间：
查询在1分钟内发布的帖子：
可见日期可直接进行比较
三、字符串函数
函数可配合select操作对表中数据进行操作，此处仅对部分场景进行演示
1、查看字符串的字符集
select charset(string)；
2、要求显示exam_result表中的信息，显示格式：“XXX的语文分：XXX，数学分：XXX，英语分：XXX”
3、在字符串中查找字符串
select instr(string,substring);
在string中查找字符串substring出现的位置，找到返回下标（从1开始），未找到返回0。
当目标字符串重复出现时，返回第一次出现的下标
4、字符串转为大写
select ucase(strig);
5、字符串转为小写
select lcase(string);
6、从字符串左端提取len个字符
select left(string,len);
6、从字符串右端提取len个字符
select right(string,len);
7、求字符串占用的字节数
selecty
length（）函数在MySQL中计算字符串的字节长度，非字符个数，当前使用字符集汉字占三个字节。
8、在字符串中进行字符串的替换 replace
select replace(substring,string,str);
在substring中查找string，并将其替换为str
此替换方式不影响原表内容，未找到则不处理
9、字符串截取 substring
select substring(string,pos,len);
从字符串string的pos处开始，向后截取len个字符。
10、去除字符串中最开始和最后的空格 trim
- trime：去除字符串两端空格
- ltrim：去除字符串最左边的空格
- rtrim：去除字符串右边的
保存用户信息数据时，一般先对数据执行去除空格操作。因网络传输可能引入不可见空字符，若直接存储含此类字符的数据，后续用户登录时，如输入密码因存在空格匹配不上，会引发登录失败问题且排查困难。所以，要先过滤字符串中的空格，再将处理后数据存入数据库，规避因隐性空格导致的登录故障
四、数学函数
1、abs 取绝对值
select abs(N);
2、bin 转二进制
select bin(N);
可见对小数进行二进制转换时，会向下取整后操作。
3、hex 转十六进制
select hex(N);
4、 conv 进制转换
select conv(N,fromm_base,to_base);
将数字N从from_base进制转换成to_base进制.
5、format 格式化，保留小数
select format(N,D);
将N保留D位小数，小数部分遵循四舍五入，不够补0.
6 mod 取模
select mod(x,y);
mod返回x对y取模的值，负数取模方式可自行尝试。
7、rand生成随机数
select rand();
生成0.0~1.0的数，若要指定范围可直接10n（如10则为0~10）
8、ceiling 向上取整
select ceiling(N);
可见向上取整是去掉小数部分直接+1；
9、floor 向下取整
select floor(N);
