04--MySQL函数的使用

1、SQL函数的使用

当我们学习编程语言的时候，经常会遇到函数。函数的好处是，它可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既提高了编写代码的效率，又提高了可维护性。在SQL中函数主要要对数据进行处理！

常用的SQL函数

数学函数、字符串函数、日期函数、转换函数、聚合函数/聚集函数/分组函数

1.1 数学函数

-- 向上取整
select ceil(12.1);
select ceil(-12.2);

-- 向下取整
select floor(12.9);
select floor(-12.9);

-- 四舍五入
-- 一个参数
select round(12.4);
select round(12.5);
select round(-12.4);
-- 两个参数
select round(12.341,2);
select round(12.256,2);
select round(25.1,-1);

-- 随机数
select rand();

-- 次幂power(x,y) x的y次幂
select power(4,2);

1.2 字符串函数

-- 拼接字符串
select concat('aaa','bbb');
select concat(first_name,last_name) '姓名' from stu;

-- 查看字符串字节长度  中文在utf8占3个字节，在gbk占2个字节
select length('王强');
select length('abcde');

-- 查看字符串字符长度
select char_length('王强');
select char_length('abcde');

-- 截取字符串 substring(str,startIndex,length)
select substring('abcdefg',1,3);

-- 替换字符串 replace(str,要替换的字符串，替换成的字符串)
select replace('mamaabcdefg','a','*');

-- 去前后留白
select trim('  scsecs  ');

1.3 日期函数

-- 获取当前系统时间
select sysdate();
select now();

-- 给日期加上或者减去几天、几月、几年
select adddate(sysdate(),interval 5 day);
select date_add(sysdate(),interval 5 day);
select date_add(sysdate(),interval 5 month);
select date_add(sysdate(),interval 5 year);

select date_sub(sysdate(),interval 5 day);
select date_add(sysdate(),interval -5 day);

-- 日期之间的减法
select datediff(sysdate(),date_sub(sysdate(),interval 5 day));		-- 相差的是天数

select timestampdiff(year,'2023-01-01','2024-01-01');		-- 1
select timestampdiff(month,'2023-01-01','2024-01-01');		-- 12
select timestampdiff(day,'2023-01-01','2024-01-01');		-- 365

-- 获取对应的年月日时分秒
select year('2024-01-01');
select year(now());
select month(now());
select day(now());
select hour(now());
select minute(now());
select second(now());

1.4 转换函数

--  解析 		日期字符串解析为日期
select str_to_date('2024-01-04','%Y-%m-%d');
select str_to_date('2024-01-04 14:33:33','%Y-%m-%d %H:%i:%s');

--  格式化	日期格式化为日期字符串
select date_format(now(),'%Y-%m-%d');
select date_format(now(),'%Y-%m-%d %H:%i:%s');

1.5 聚合函数/分组函数

聚合函数	说明
count(*)| count(主键)	计算表中的总记录数
max	计算最大值
min	计算最小值
sum	计算和
avg	计算平均值

聚合函数的计算，排除null值。

分组函数还有另一个名字：多行处理函数。

多行处理函数的特点：输入多行，最终输出的结果是1行。

分组函数自动忽略NULL。

分组函数不可直接使用在where子句当中

group by是在where执行之后才会执行的。

-- count()函数的使用
select count(*) from stu;		-- 102		查询所有记录
select count(id) from stu;	-- 102		查询主键记录
select count(chinese) from stu;		-- 101 		查询非主键字段，含有null，

-- max()函数的使用
-- 查询语文成绩最高的学生
select name,max(chinese) from stu;

-- min()函数的使用
-- 查询数学成绩最低的学生
select name,min(math) from stu;

-- sum()函数的使用
-- 查询英语成绩的总分
select sum(english) from stu;

-- avg()函数的使用
-- 查询班级学生的各科平均分
select round(avg(ifnull(chinese,0)),2) '语文',round(avg(math),2) '数学',round(avg(english),2) '英语' from stu;

注意：分组函数一般都会和group by联合使用，这也是为什么它被称为分组函数的原因。并且任何一个分组函数（count sum avg max min）都是在group by语句执行结束之后才会执行的。

当一条sql语句没有group by的话，整张表的数据会自成一组。

当一条语句中有group by的话，select后面只能跟分组函数和参与分组的字段。