MySQL-函数（字符串函数、数值函数、日期函数、流程函数）-优快云博客

本文链接：https://blog.youkuaiyun.com/ZRSAI/article/details/131032464

文章介绍了MySQL中的四种主要函数类型：字符串函数如concat、lpad和rpad，数值函数包括ceil、floor和rand，日期函数如curdate、date_add以及datediff，以及流程控制函数if、ifnull和case语句在数据库操作中的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

函数是指一段可以直接被另一段程序调用的程序或代码。

MySQL中的函数主要分为以下四类： 字符串函数、数值函数、日期函数、流程函数。

字符串函数

select concat("hello", "mysql");
select lower("HHHHH");
select upper("hhhhh");
select lpad("01", 5, "-");
select rpad("01", 50, "abc");
select trim(" helo  word ");
select substring("hello word", 1, 5);

update emp set workno = lpad(workno, 10, "0");

数值函数

select ceil(1.5);
select floor(1.9);
select mod(7, 4);
select rand();
select round(2.345, 2);

select lpad(round(rand()*1000000, 0), 6, "0");

日期函数

select curdate();
select curtime();
select now();
select year(now());
select month(now());
select day(now());
select date_add(now(), interval 70 year );
select datediff("2021-12-01", "2021-10-01");

select name,datediff(curdate(),entrydate) as "entrydays" from emp order by entrydays desc ;

流程函数

select if(true, "ok", "error");
select ifnull(null, "default");

select
    name,
    case workaddress when "北京" then "一线城市" else "二线城市" end
from emp;

select * from score;
select
    name,
    case when math >= 85 then "优秀" when math >= 65 and math < 85 then "及格" else "不及格" end as "数学",
    case when english >= 85 then "优秀" when math >= 65 and math < 85 then "及格" else "不及格" end as "英语",
    case when chinese >= 85 then "优秀" when math >= 65 and math < 85 then "及格" else "不及格" end as "语文"

from score;