函数 - 编程资料

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

字符串函数

常见字符串函数

函数	功能
concat(S1,S2,...Sn)	字符串拼接，将S1,S2,...Sn拼接成一个字符串
lower(str)	将字符串str全部转化为小写
upper(str)	将字符串str全部转化为大写
lpad(srt,n,pad)	左填充，用字符串pad对str的左边进行填充，达到n个字符串长度
rpad(str,n,pad)	右填充，用字符串pad对str的右边进行填充，达到n个字符串长度
trim(str)	去掉字符串头部和尾部的空格
substring(str,start,len)	返回从字符串str从start位置起的len个长度的字符串

字符串函数演示

-- A. concat : 字符串拼接
select concat('hello','mysql');

-- B. lower : 全部转小写
select lower('Hello');

-- C. upper : 全部转大写
select upper('Hello');

-- D. lpad : 左填充
select lpad('01',5,'-');

-- E. rpad : 右填充
select rpad('01',5,'-');

-- F. trim : 去除空格
select trim(' hello mysql  ',);

-- G. substring : 截取子字符串
select substring('Hello MySQL',1,5);

字符串函数案例

由于业务需求变更，企业员工的工号，统一为8位数，目前不足8位数的全部在前面补0。比如： 1号员

工的工号应该为00001。

update emp set workno = lpad(workno,8,'0');

数值函数

常见数值串函数

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

数值串函数演示

-- A. ceil：向上取整
select ceil(1.1);   -- 2

-- B. floor：向下取整
select floor(1.9);  -- 1

-- C. mod：取模(余数)
select mod(7,4);  -- 3
select mod(5,8);  -- 5
select mod(6,6);  -- 0

-- D. rand：获取随机数
select rand();

-- E. round：四舍五入
select round(2.344,2)  -- 2.34
select round(3.1415926,4)  -- 3.1416

数值串函数案例

通过数据库的函数，生成一个六位数的随机验证码。

-- 步骤1：获取随机数0-1之间随机数：rand()函数
select rand()
-- 步骤2：在步骤1基础上乘以1000000（小数点向右移动6位）
select rand()*1000000;
-- 步骤3：四舍五入去除小数点 round(x,0)
selcet round(rand()*1000000,0);
-- 步骤4：如果长度不足，在左侧或右侧补0, lpad或rpad
select lpad(round(rand()*1000000,0),6,'0');
select rpad(round(rand()*1000000,0),6,'0');

日期函数

常见日期函数

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

日期函数演示

-- A. curdate：当前日期
select curdate();

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

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

-- D. YEAR , MONTH , DAY：当前年、月、日
select year(now());
select month(now());
select day(now());

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

-- F. datediff：获取两个日期相差的天数(date1-date2)
select datediff(now(),'1999-04-21');

日期函数案例

查询所有员工的入职天数，并根据入职天数倒序排序。

select name as '姓名' , datediff(curdate(),entrydate) as '入职天数' from emp order by '入职天数'  desc ;

查询所有女员工的入职天数，并根据入职天数升序排序。

select name as '姓名' , datediff(curdate(),entrydate) as '入职天数' from emp where gender = '女' order by '入职天数'  desc ;

流程函数

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

常见流程函数

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

流程函数演示

-- A. if
select if(1=1,'ok','error');  -- 真：ok
select if(1+1=1,'ok','error'); -- 假：error

-- B. ifnull
select ifnull('ok','default');  --  非空：ok
select ifnull('','default');    --  非空（假空）：
select ifnull(null,'default');  -- 空：default

-- C. case when then else end
-- 需求: 查询emp表的员工姓名和工作地址 (北京/上海 ----> 一线城市 , 其他 ----> 二线城市)
select name '姓名', workaddress  '工作地址', ( case workaddress when '北京' then '一线城市' when  '上海' then '一线城市' else '二线城市' end ) '城市分级' from emp ;

流程函数案例

判断成绩：>=85 优秀； >=60 及格； <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(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95
), (2, 'Rose' , 23, 66, 90),(3, 'Jack', 56, 98, 76);

语句：

select 
	id   '编号', name '姓名' , 
	math '数学成绩' , 
	(case  when math >= 85 then '优秀'  when  math >= 60  then '及格' else '不及格' end) '数学成绩等级' , 
english '英语成绩' , 
	(case  when english >= 85 then '优秀'  when  english >= 60  then '及格' else '不及格' end) '英语成绩等级' ,
chinese '语文成绩' ,
	(case  when chinese >= 85 then '优秀'  when  chinese >= 60  then '及格' else '不及格' end) '语文成绩等级' 
	from score;

函数