MySQL笔记 - 函数

原创已于 2025-05-22 00:55:17 修改 · 231 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #笔记

于 2025-04-15 14:21:26 首次发布

分为四类函数：

一）字符串函数
二）数值函数
三）日期函数
四）流程函数

一）字符串函数

concat(s1,s2,…,sn) 连接
lower(str) 全部转换为小写
upper(str) 全部转换为大写
lpad(str,n,pad) 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
rpad(str,n,pad) 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度

-- concat(s1,s2,...,sn)  连接
select  concat("hello","mysql");

-- lower(str)  全部转换为小写
select lower("Hello");

-- upper(str)  全部转换为大写
select  upper("Hello");

-- lpad(str,n,pad)  左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
-- 执行结果为：---01
select lpad("01",5,'-');
-- 执行结果为：01www
select lpad("01wwwwwwwww",5,'-');

-- rpad(str,n,pad)  右填充，用字符串pad对str的右边进行填充，达到n个字符串长度,截断
-- 执行结果为：01---
select rpad("01",5,'-');
-- 执行结果为：01www
select rpad("01wwwwwwwww",5,'-');

-- trim(str) 去除字符串头部和尾部空格
select trim(' Hello mysql ');

-- substring(str,start,len)  返回字符串str从start位置起的len个长度的字符串，索引从1开始
select substring('Hello Mysql' ,1,5);

-- 案例、由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部再前面补0，比如：1号员工的工号应该为00001.
update test_2.emp set workno = lpad(workno,5,"0");

二）数值函数

ceil(x) 向上取整
floor(x) 向下取整
mod(x,y) 返回x/y的余数
rand() 返回0~1内的随机数
round(x,y) 求参数x的四舍五入的值，保留y位小数

-- ceil(x)      向上取整
select ceil(1.5);
select ceil(-1.5);

-- floor(x)     向下取整
select floor(1.5);
select floor(-1.5);

-- mod(x,y)   返回x/y的余数
select mod(7,4);

-- rand()      返回0~1内的随机数
select rand();

-- round(x,y)   求参数x的四舍五入的值，保留y位小数
select round(3.14159,3);

-- 案例：通过数据库的函数，生成一个六位数的随机验证码
select lpad(round(rand()*1000000,0),6,'0');

三）日期函数

curdate() 返回当前日期
curtimen() 返回当前时间
now() 返回当前日期和时间
year(date) 获取指定date的年份
month(date) 获取指定date的月份
day(date) 获取指定date的日期
date_add(date,interval expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1,date2) 返回起始时间date1 和结束时间date2 之间的天数

-- curdate()        返回当前日期
select curdate();

-- curtimen()       返回当前时间
select curtime();

-- now()            返回当前日期和时间
select now();

-- year(date)       获取指定date的年份
select year(now());

-- month(date)      获取指定date的月份
select month(now());

-- day(date)        获取指定date的日期
select day(now());

-- date_add(date,interval expr type)        返回一个日期/时间值加上一个时间间隔expr后的时间值
-- 返回现在往后推70天的日期
select date_add(now(),interval 70 day);
-- 返回现在往后推70个月的日期
select date_add(now(),interval 70 month );
-- 返回现在往后推70年的日期
select date_add(now(),interval 70 year );

-- datediff(date1,date2)         返回起始时间date1 和结束时间date2 之间的天数
-- date1 - date2
select datediff('2020-01-02','2022-01-02');

-- 案例：查询所有员工的入职天数，并根据入职天数倒序排序
select name,entrydate, datediff(curdate(),emp.entrydate) 入职天数
from emp
order by entrydate asc ;

四）流程函数

if(value,t,f) 如果value为true，则返回t，否则返回f
ifnull(value1,value2) 如果value1不为空，返回value1，否则返回value2
case when [val1] then [res1] … else [default] end 如果val1为true，则返回res1，… 否则返回default默认值
case [expr] when [val1] then [res1] … else [default] end 如果expr的值等于val1，返回res1，… 否则返回default默认值

-- if(value,t,f)        如果value为true，则返回t，否则返回f
select if(1,'Ok','Error');

-- ifnull(value1,value2)        如果value1不为空，返回value1，否则返回value2
select ifnull('Ok','Default');
select ifnull('','Default');
select ifnull(null,'Default');

-- case when [val1] then [res1] ... else [default] end      如果val1为true，则返回res1，... 否则返回default默认值
-- case [expr] when [val1] then [res1] ... else [default] end       如果expr的值等于val1，返回res1，... 否则返回default默认值
-- 案例：查询emp表的员工姓名和工作地址（北京/上海 --->一线城市，其他---->二线城市）
select emp.name,emp.city,
#       ( case when (emp.city = "北京" or emp.city = "上海") then "一线城市"
        (case city when ("北京" or "上海") then "一线城市"
        else "二线城市"
        end) "城市等级"
from emp;

-- 案例：统计班级各个学生的成绩，展示规则如下：
-- >= 85，优秀
-- >=60 及格
-- <60 不及格
create table score(
    id int comment "ID",
    name varchar(10) comment "姓名",
    math int comment "数学",
    chinese int comment "语文",
    english int comment "英语"
) comment "学生成绩表";
insert into score(id, name, math, chinese, english)
    value (1,"小a",60,60,61),
    (2,"小b",80,85,90),
    (3,"小c",66,88,33);

select *
from score;

select id, name,
    (case when score.math >= 85 then "优秀"  when math >= 60 then "及格" else "不及格" end) '数学',
    (case when score.chinese >= 85 then "优秀"  when chinese >= 60 then "及格" else "不及格" end) '语文',
    (case when score.english >= 85 then "优秀"  when english >= 60 then "及格" else "不及格" end) '英语'
from  score;