03-函数

函数

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

字符串函数

常用的如下：

函数	功能
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开始)

select concat('Hello',' MySQL');
-- Hello MySQL

select upper('Hello');
-- HELLO

select lower('Hello');
-- hello

select lpad('01',5,'-');
-- ---01

select rpad('01',5,'-');
-- 01---

select trim('   Hello  MySQL   ');
-- Hello  MySQL

select substring('Hello MySQL',1,5)
-- Hello

Case

-- 将员工工号统一为5位数，目前不足5位数的全部在前面补0
-- 修改数据用到update，前补足用lpad
update 员工表 set workno = lpad(workno,5,'0');

数值函数

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

select ceil(1.1);
-- 2

select floor(1.9)
-- 1

select mod(3,4);
-- 3

select rand()
-- 0-1随机小数

select round(2.345,2);
-- 2.35
select round(2.344,2);
-- 2.34

Case

-- 生成6位随机数

alter table emp modify idpage char(6);
update emp set idpage=lpad(round(rand()*1000000,0),6,'0');

日期函数

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

select curdate();
--  2024-09-16

select curtime();
-- 20:02:57

select now();
-- 2024-09-16 20:03:14

select year(now());
--  2024

select month(now());
-- 9

select day(now());
-- 16

select date_add(now(),interval 70 year);
-- 2094-09-16 20:05:24

select datediff('2024-12-01','2024-10-01');
-- 61
select datediff('2024-10-01','2024-12-01');
-- -61

-- 查询所有员工入职天数，并按照入职天数倒序排序

select name,datediff(curdate(),entrydate) as 'entrydays' from emp 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为true，返回res1，...否则返回默认值default
case [expr] when [val1] then [res1] ... else [default] end	如果expr的值等于val1，返回res1，...否则返回默认值default

select if(true,'ok','error');
-- ok

select ifnull('ok','default');
-- ok
select ifnull('','default');
--  (有返回，为'')
select ifnull(null,'default');
-- default

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

-- 案例: 统计班级各个学员的成绩，展示的规则如下：
-- >= 85，展示优秀
-- >= 60，展示及格
-- 否则，展示不及格
select id,name,
    -> (case when math >=85 then '优秀' when math>=60 then '及格' else '不及格' end) '数学' from score;
    -- when then when then ... else end
    -- 牢记结构