SQL函数

函数

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

字符串函数

MySQL中内置了很多字符串函数，常用的几个如下

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

示例代码

select concat('Hello','MySQL'); #返回值:HelloMySQL
​
select lower('Hello'); #返回值:hello
​
select upper('Hello'); #返回值:HELLO
​
select lpad('01',5,'-') #返回值:---01
​
select rpad('01',5,'-') #返回值:01---
​
select trim(' Hello NySQL '); #返回值:Hello NySQL
​
select substring('Hello MySQL',1,5); #返回值:Hello
​
#更新emp表中员工工号为5位数，用0填充
update emp 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(13,4); #返回值:1;取模运算
​
select rand(); #返回值:返回0~1内的随机数
​
select round(2.3456,2); #返回值:2.35

练习

#通过数据库的函数，生成一个六位数的随机验证码
#方式一
select round(rand()*899999+100000,0);
#方式二
select lpad(round(rand()*1000000,0),6,'0');

日期函数

常见的日期函数如下

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

示例代码

select curdate(); #2022-10-26
select curtime(); #14:56:47
select now(); #2022-10-26 14:56:47
​
select year(now());#2022
select month(now());#10
select day(now());#26
​
select date_add(now(),interval 70 day ); #2023-01-04 15:01:35
select datediff('2022-12-01','2022-10-01');#61

练习

#查询所有员工的入职天数，并根据入职天数倒叙排序
select name,datediff(now(),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 [val] then [res1]..else [default] end	如果expr的值等于val1，返回res1，...否则返回default默认值

示例代码

select if(true,'Ok','Error');#Ok
​
select ifnull('Ok','Default');#Ok
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) 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;