MySQL - 函数

1 什么是函数？

要想实现上面的这些效果，就得借助于MySQL当中的内置函数。

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

MySQL当中内置了很多的函数，根据其操作的数据类型，分为以下四类：

• 字符串函数 
• 数值函数
• 日期函数
• 流程控制函数

1.1 字符串函数

函数	功能
char_length(s)	返回字符串的字符长度
concat(s1，s2，... sn)	字符串拼接，将s1，s2，... sn拼接成一个字符串
concat_ws(sep，s1，s2，... sn)	字符串拼接，将s1，s2，... sn拼接成一个字符串，中间使用sep分隔
left(s，len)	返回字符串左侧的len个长度的字符串
right(s，len)	返回字符串右侧的len个长度的字符串
substring(str，start，len)	返回字符串str从start位置起的len个长度的字符串[注意：start从1开始]

-- MySQL函数:
-- 字符串函数
-- 1. char_length(s) 返回字符串的字符长度
select name,char_length(name) from tb_emp;

-- 2. concat(s1,s2,...sn) 字符串拼接,将s1,s2,...sn拼接成一个字符串
select concat(id,'-',name) from tb_emp;

-- 3. concat_ws(sep,s1,s2,...sn) 字符串拼接,将s1,s2,...sn拼接成一个字符串,中间使用sep分隔
select concat_ws(' - ',username,name) from tb_emp;

-- 4. left(s,len) 返回字符串左侧的len个长度的字符串
select left(username,5) from tb_emp;

-- 5. right(s,len) 返回字符串右侧的len个长度的字符串
select right(username,5) from tb_emp;

-- 6. substring(str,start,len) 返回字符串str从start位置起的len个长度的字符串
-- 比如:我想查询每一个员工的姓氏
select substring(name,1,1) from tb_emp;

1.2 数值函数  

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

-- 数值函数
-- select后面如果没有指定任何的from的话,代表我们不需要从表当中来查询
-- 1. ceil(x) 向上取整
select ceil(2.1); -- 3

-- 2. floor(x) 向下取整
select floor(2.9); -- 2

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

-- 4. round(x,y) 求参数x的四舍五入的值,保留y位小数
select round(2.453,2); -- 2.45

1.3 日期函数 

日期时间类型的函数 

函数	功能
now()	返回当前日期和时间
curdate()	返回当前日期
curtime()	返回当前时间
year(date)	获取指定date日期的年份
month(date)	获取指定date的月份
day(date)	获取指定date的日期
datediff(date1，date2)	返回起始时间date1和结束时间date2之间的天数
date_format(date，format)	日期时间格式化

-- 日期时间类型的函数
-- 1. now() 返回当前日期和时间
select now(); -- 2023-09-01 19:02:40

-- 2. curdate() 返回当前日期
select curdate(); -- 2023-09-01

-- 3. curtime() 返回当前时间
select curtime(); -- 19:02:59

-- 4. year(date) 获取指定date日期的年份
select year('2023-02-05 00:09:45'); -- 2023

-- 5. month(date) 获取指定date的月份
select month('2023-02-05 00:09:45'); -- 2

-- 6. day(date) 获取指定date的日期
select day('2023-02-05 00:09:45'); -- 5

-- 7. datediff(date1,date2) 返回起始时间date1和结束时间date2之间的天数
select datediff('2002-07-08 00:09:45','2023-08-14 00:09:45'); -- 7707

-- 8. date_format(date,format) 日期时间格式化
-- format函数多的一批,鼠标往上一悬浮,会自动提示
select date_format('2002-07-08 00:09:45','%Y'); -- 2022

-- 需求:获取2015年入职的员工
select * from tb_emp where year(entrydate) = '2015';

1.4 流程控制函数(最重要) 

函数	功能
if(value，t，f)	如果条件表达式value为true，则返回t，否则返回f
ifnull(value1，value2)	如果value1不为空，返回value1，否则返回value2
case when cond1 then res1 when cond2 then res2... [else result] end	如果cond1为true，返回res1，... 否则返回result默认值
case expr when val1 then res1 ... [else result] end	如果表达式expr的值等于val1，返回res1，... 否则返回result默认值，最后语法结束来一个end

-- 流程控制函数
-- 1. if(value,t,f)  如果条件表达式value为true,则返回t,否则返回f
-- 要求:按照性别进行分组,如果gender=1,显示男性员工;如果gender=2,显示女性员工
select if(gender = 1, '男性员工', '女性员工') as 性别, count(1) as 总数
from tb_emp
group by gender;

-- 2. ifnull(value1,value2) 如果value1不为空,返回value1;否则返回value2
-- 查询员工的姓名及职位,如果员工没有职位,展示'未分配职位'
select name as '姓名', ifnull(job, '未分配职位') as '职位'
from tb_emp;

-- 3. case when condition1 then res1 when cond2 then res2...else result end
-- gender性别转换:1转化为男性员工,2转化为女性员工
select name, (case when gender = 1 then '男性员工' when gender = 2 then '女性员工' end) as '性别'
from tb_emp;

-- 4. case expr when val1 then res1 when val2 then res2 ... else result end
-- job职位转换,说明: 1 班主任 ,2 讲师, 3 学工主管, 4 教研主管
select name                 as '姓名',
       (case job
            when 1 then '班主任'
            when 2 then '讲师'
            when 3 then '学工主管'
            when 4 then '教研主管'
            else
                '@失业@' end) as '职位'
from tb_emp;

2. MySQL函数-案例

-- MySQL函数-案例
-- 计算每个员工的入职天数  datediff(date1,date2) 返回起始时间date1和结束时间date2之间的天数
select name as 员工姓名, datediff(now(), entrydate) as '入职天数' from tb_emp;

-- 19.查询出来不是班主任的员工
select * from tb_emp where job != 1 or job is null;

-- 20.查询出入职人数最多的月份
select month(entrydate) as '月份', count(*) as '总数'
from tb_emp
group by month(entrydate)
order by count(*) desc
limit 1;

-- 21.查询出入职人数最多的年份
select year(entrydate) as '年份', count(*) as '总数'
from tb_emp
group by year(entrydate)
order by count(*) desc
limit 1;