函数:是指一段可以直接被另一段程序调用的程序或代码
一、字符串函数--select 函数名(参数);
1、concat(S1,S2,...Sn)--字符串拼接,将S1,S2,...Sn拼接成一个字符串
select concat('Hello','MySQL');
2、lower(str)--将字符串str全部转为小写
select lower('Hello');
3、upper(str)--将字符串全部转为大写
select upper('Hello');
4、lpad(str,n,pad)--左填充,用字符串pad对str的左边进行填充,达到n个字符串长度
select lpad('01',5,'-');
5、rpad(str,n,pad)--右填充,用字符串pad对str的右边进行填充,达到n个字符串长度
select rpad('01',5,'-');
6、trim(str)--去掉字符串头部和尾部的空格,不包括中间的空格
select trim(' Hello MySQL ');
7、substring(str,start,len)--返回从字符串str从start位置起(注意:索引值从1开始)的len个长度的字符串
select substring('Hello MySQL',1,5);
练习:
由于业务需求,企业员工的工号,统一为5位数,目前不足5位数的全部在前面补0。比如:1号员工的工号应该为00001。
update emp set workno = lpad(workno, 5, 0);
二、数值函数
1、ceil(x) ---向上取整
select ceil(1.5);
2、floor(x) ---向下取整
select fllor(1.9);
3、mod(x) ---返回x/y的模,即是求余数
select mod(5,4);
4、rand() ---返回0~1内的随机数
select rand();
5、round(x) ---求参数x的四舍五入的值,保留y位小数
select round(2.345,2);
练习:
通过数据库的函数,生成一个6位数的随机验证码
select lpad(round(rand()*1000000,0),6,0);
三、日期函数
1、curdate() ---返回当前日期
select curdate();
2、curtime() ---返回当前时间
select curtime();
3、now() ---返回当前日期和时间
select now();
4、year(date) ---获取指定date的年份
select YEAR(now());
5、month() ---获取指定date的月份
select MONTH(now());
6、day() ---获取指定date的日期
select DAY(now());
7、date_add(date,interval expr type) ---返回一个日期/时间值加上上一个时间间隔expr后的时间值
select date_add(now(),INTERVAL 70 DAY);
-- 当前时间2021年10月11日往后加70天时间是什么时候
select date_add(now(),INTERVAL 70 MONTH);
-- 当前时间2021年10月11日往后加70个月时间是什么时候
8、datediff(date1,date2) ---返回起始时间date2和结束时间date1之间的天数(date1-date2)
select datediff('2021-12-01','2021-11-01');
练习:
查询所有员工的入职天数,并根据入职天数倒序排序
select name, datediff(curdate(),entrydate) as "entrydays" from emp
order by entrydays desc;
四、流程控制函数
1、if(value , t , f) —— 如果value为true,则返回t,否则返回f)
select if(true,'OK','Error');
2、ifnull(value1 ,value2) —— 如果value1不为空,则返回value1,否则返回value2)
select ifnull ('OK','Default'); --不为null,返回“OK”
select ifnull ('','Default'); --不为null,返回空串
select ifnull (null,'Default'); -- 为null,返回default
3、case when [val1] then [res1]...else [default ] end —— 如果val1为true,则返回res1,...否则返回default默认值)
-- 查询emp表的员工姓名和工作地址(如果为北京/上海=== 一线城市,而其它地点=== 二线城市)
select
name,
(case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end)
as '工作地址'
from emp;
4、case [expr] when [val1] then [res1] ... else [default] end —— 如果expr表达式的值为val1,则返回res1,...否则返回default默认值)
统计班级各个学员的成绩,展示的规则如下:
>=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;