MySQL常见函数

函数

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

字符串函数

函数	功能
CONCAT(S1,S2,S3…SN)	字符串拼接，将S1,S2,S3。。。SN凭借成一个字符串
LOWER(STR)	将字符串str全部转换为小写
UPPER(STR)	将字符串全部转换为大写
LPAD(STR,N,PAD)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
RPAD(STR,N,PAD)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
TRIM(STR)	去掉字符串头部和尾部的空格
SUBSTRING(STR,START,LEN)	返回从字符串str从start位置起的len个长度的字符串
SELECT 函数（参数）

-- ----------------------函数演示----------------
-- CONCAT
SELECT CONCAT('Hello',' MySQL');
-- lower
select lower('Hello');
-- upper
select upper('hello');
-- lpad
select lpad('01',5,'-');
-- rpad
select rpad('01',5,'-');
-- trim
select trim(' hello mysql ')
-- substring
SELECT SUBSTRING('HELLO MYSQL',1,5)
-- 由于业务需求变更，企业员工的工号，统一位5位数，目前不足5位数的全部在前面补0。
update employee set worknum = lpad(worknum,5,'0');

数值函数

常见的数值函数如下：

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

-- ceil
select ceil(1.5);
-- floor
select floor(1.2);
-- mod
select mod(3,4);
select mod(5,3);
-- rand
select rand();
select rand()*1000000;
-- round
select round(2.34577,3);
-- 通过数据库的函数，生成一个六位数的随机验证码
select lpad( round(rand()*1000000,0),6,'0');

日期函数

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

-- ------------日期函数--------------
-- crudate()
select curdate();
-- crutime()
select curtime();
-- now()
select now();
-- year,month,day
select year(now());
select month(now());
select day(now());
-- date_add()
select date_add(curdate(),INTERVAL 70 day);
-- datediff
select datediff('2022-12-1','2021-11-9');
-- 查询员工的入职天数，并根据入职天数倒序排序
select name,datediff(now(),ENTRYDATE) as entrydays from employee order by entrydays desc;

流程函数

• 流程函数也是很常用的一类函数，可以在sql语句中实现条件筛选，从而提高语句的效率。

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

-- 流程控制函数
-- if
select if(true,'yes','no');
-- ifnull
select ifnull(null,2);
select ifnull(1,null);
-- case
-- 需求：查employee表的员工姓名和工作地址（北京/上海---->一线城市，其他---->二线城市)
select name,(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '小破地儿' end)as '工作地址' from employee ;

create table score(
    id int comment 'ID',
    name varchar(20) comment '姓名',
    math int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
)comment '学员成绩表';
insert into score(id, name, math, english, chinese) VALUES (1,'Tom',67,88,95),(2,'Rose',23,66,90),(3,'Jack',56,98,76);
-- 案例：统计班级各个学员的成绩，展示的规则如下
-- >= 85，展示优秀
-- >= 60，展示及格
-- 否则，显示不及格
select id,
       name,
       (case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end)as '数学',
       (case when english >= 85 then '优秀' when english >= 60 then '及格' else '不及格' end)as '英语',
       (case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' end)as '语文'
from score;