SQL中的函数（字符串、数值、日期、流程函数）

employee表如下（下面演示操作会用到）：

1 字符串函数

（1）函数功能实现

函数	功能
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个长度的字符串

-- --------------------------- 函数演示 --------------------------------

-- ----------------------- 1.字符串函数 ----------------------------
-- concat：字符串拼接
select concat('Hello', ' MySQL!');-- Hello MySQL!

-- lower：全部转小写
select lower('Hello');-- hello

-- Upper：全部转大写
select upper('Hello');-- HELLO

-- lpad：左填充
select lpad('01',6,'X');-- XXXX01

-- rpad：右填充
select rpad('01',6,'X');-- 01XXXX

-- trim：去除首尾空格
select trim(' Hello MySQL! ');-- Hello MySQL!

-- substring：截取子字符串
select substring(' Hello MySQL! ',1,13);-- Hello MySQL!

（2）业务需求实现

-- 业务需求员工号统一为6位数 不足则在前面补0
update employee set workno = lpad(workno,6,'0');

2 数值函数

函数功能实现

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

-- ----------------------- 2.数值函数 ----------------------------
-- ceil(x)：向上取整
select ceil(1.1);-- 2

-- floor(x)：向下取整
select floor(1.1);-- 1

-- mod(x,y)：取模
select mod(6,5);-- 1

-- rand：获取0~1随机数
select rand();

-- round(x,y)：四舍五入 保留y位小数
select round(6.6);-- 7
select round(6.3);-- 6
select round(6.365,2);-- 6.37


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

3 日期函数

（1）函数功能实现

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

-- ----------------------- 3.日期函数 ----------------------------
-- curdate：当前日期
select curdate();

-- curtime：当前时间
select curtime();

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

-- year month day：当前年 月 日
select year(now());
select month(now());
select day(now());

-- date_add：增加指定的时间间隔
select date_add(now(),interval 70 year);

-- datediff：获取两个日期相差的天数
select datediff('2022-12-01','2021-12-11');
select datediff('2023-3-26','2022-10-16');

（2）业务需求实现

-- 案例：查询所有员工入职天数 并根据入职天数倒序排序
-- 思路：入职天数-->当前日期-入职日期 调用datediff函数完成
select name,datediff(curdate(),entrydate) as 'entrydays' from employee order by entrydays desc;

4 流程函数

（1）函数功能实现

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

-- ----------------------- 4.流程控制函数 ----------------------------
-- if(value,t,f) 如果value为true 返回t 否则返回f
select if(false,'OK','ERROR');

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

-- case when then else end
-- 需求：查询employee表的员工姓名和年龄（age<=19-->少年, 其他-->老年）
select
    name,
    (case when age<=19 then '少年' else '老年' end) as '类分'
from employee;

（2）业务需求实现

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

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