MySQL基础：函数

 

  💎所属专栏：MySQL 

函数是指一段可以直接被另一段程序调用的程序或代码，在MySQL中也内置了许多函数供开发者去调用，例如之前提到的聚合函数，本节再去介绍一些其他常用的函数

字符串函数

函数	功能
CONCAT(S1,S2...Sn)	字符串拼接，将S1,S2...Sn拼接成一个字符串
LOWER(str)	将字符串str全部转换为小写
UPPER(str)	将字符串str全部转换为大写
LPAD(str,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符长度
RPAD(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符长度
TRIM(str)	去掉字符串头部和尾部的空格
SUBSTRING(str,start,len)	返回从字符串str从start起的len个长度的字符串

接下来分别对这些函数进行演示 

-- concat(S1,S2...Sn)字符串拼接，将S1,S2...Sn拼接成一个字符串
select concat('hello','mysql');
-- LOWER(str) 将字符串str全部转换为小写
select lower('Hello');
-- UPPER(str) 将字符串str全部转换为大写
select upper('Hello');
-- LPAD(str,n,pad)左填充，用字符串pad对str的左边进行填充，达到n个字符长度
select lpad('01',5,'-');
-- RPAD(str,n,pad)右填充，用字符串pad对str的右边进行填充，达到n个字符长度
select rpad('01',5,'-');
-- TRIM(str) 去掉字符串头部和尾部的空格
select trim(  'hell o' );
-- SUBSTRING(str,start,len) 返回从字符串str从start起的len个长度的字符串
select substring('hello',3,2);

trim()只会去除开头和结尾的空格，字符串中间的空格并不会被去除 

这些函数还可以和 update 操作搭配使用：

update student
set sn = lpad(sn, 6, '0');

数值函数 

函数	功能
CEIL(x)	向上取整
FLOOR(x)	向下取整
MOD(x,y)	返回x/y的模
RAND()	返回0~1的随机数
ROUND(x,y)	求参数x的四舍五入的值，保留y位小数

 向上取整时，只要小数部分不是 0 ，就会向上取整

保留小数位数时，采用的是四舍五入的方法

-- CEIL(x) 向上取整
select ceil(1.1);
-- floor(x) 向下取整
select floor(1.1);
-- MOD(x,y) 返回x/y的模
select mod(3, 5);
-- RAND() 返回0~1的随机数
select rand();
-- ROUND(x,y) 求参数x的四舍五入的值，保留y位小数
select round(2.345,2);

函数与函数之间可以进行嵌套，例如生成一个四位数的随机数就可以这样写

-- 生成一个四位数随机数
select round(rand()*10000,0);

但此时还有一个小问题，如果说生成出来的0~1的数是 0.002这样的数，那么最终结果就不是4位数了，就需要在前面补上0，所以还需要调用lpad()函数

select lpad(round(rand()*10000,0),4,'0');

 日期函数

 

函数	功能
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
YEAR(date)	获取指定date的年份
MONTH(date)	获取指定date的月份
DAY(date)	获取指定date的日期
DATE_ADD(date,INTERVAL expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date 2)	返回起始时间date1和结束时间date2之间的天数

 

-- 当前日期
select curdate();
-- 当前时间
select curtime();
-- 当前日期和时间
select now();

 

-- 分别获取year,month,day
select year(now());
select month(now());
select day(now());

 

 接下来看时间间隔相关的函数

-- 添加时间间隔
select date_add(now(), interval 10 day);
-- 求出时间间隔
select datediff('2024-07-01', '2024-08-01');

上面的10 day也可以换成其他的时间单位，例如year,minute等 

datediff() 函数只能计算相差的天数

 

流程函数

流程函数可以在SQL语句中实现条件筛选，从而提高语句的效率

函数	功能
IF(value,t,f)	如果value为true，就返回 t ，否则返回 f
IFNULL(value1,value2)	如果value1不为空，返回value1，否则返回value2
CASE WHEN[val 1] THEN[res1]...ELSE[default] END	如果val1为true，返回res1，否则返回default默认值
CASE[expr] WHEN[val 1] THEN[res1]...ELSE[default] END	如果expr的值等于val1,返回res1,否则返回default默认值

 这里判断null是依然和之前一样，"" 这样一个空的字符串并不是null

-- if和ifnull
select if(true, 'ok', 'no');
select ifnull('hello', 'ok');

-- case when then end
select (case name when '许仙' then '民间传说人物' when '白素贞' then '民间传说人物' else '其他' end)
from student;

-- null的判断
SELECT (CASE WHEN mail IS NULL THEN '无邮箱' ELSE '有邮箱' END) AS mail_status
FROM student;

这个是加入了expr的值 

select (case '许仙' when '许仙' then '民间传说人物' else '不存在' end);

聚合函数 

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的总和
AVG([DISTINCT] expr)	返回查询到的数据的平均值
MAX([DISTINCT] expr)	返回查询到的数据的最大值
MIN([DISTINCT] expr)	返回查询到的数据的最小值

💎5.1 COUNT() 统计所有行

-- 统计表中的行数
select count(*)
from student;
-- 也可以传入常量 1
select count(1)
from student;

星号（*）并不直接表示表中的任意一列，而是作为一个特殊的指示符，告诉数据库管理系统（DBMS）计算表中的行数，而不关心表中的列内容或是否有NULL值。

还可以指定某一列进行统计：

-- 指定列统计
select count(id)
from student;

select count(name)
from student;

💎5.2 SUM() 求和 

创建一张成绩表，计算语文的总成绩

create table exam
(
    id      bigint primary key auto_increment,
    name    varchar(20),
    chinese decimal(5, 2),
    math    decimal(5, 2)
);
insert into exam(id, name, chinese, math)
values (1, '张三', 98, 95),
       (2, '李四', 97, 99),
       (3, '王五', 96, 98),
       (4, '赵六', 97, 94);
-- 计算语文总成绩
select *
from exam;
select sum(chinese)
from exam;

 查询到的结果存储在了临时表中，不受字段中长度的约束（decimal(5, 2)）

如果说求和的那一列存在null的话，会是像之前表达式相加时，null加上任何值都是null的情况吗？

insert into exam values (5,'钱七',96,null);

select sum(math)
from exam;

可以看出，最终的值并没有加上null ，并且，如果是非数值类型求和是没有意义的

💎5.3 AVG() 求平均值

-- 求平均值
select avg(math)
from exam;

-- 参数里边可以包含表达式，结果可以使用别名
select avg(math + chinese) as 总分平均值
from exam;

 

💎5.4 MAX()和MIN()

求指定列中的最大值和最小值

-- 求最大值和最小值
select max(chinese) as 语文最大值,
       min(math) as 数学最小值
from exam;

  可以多个聚合函数使用，同时也可以使用别名