函数
-1.概念
函数指一段可以直接被另一段程序调用的程序或代码。
-2.函数的种类
-
字符串函数
mysql内置了很多函数,但是最常见的函数如下;
函数 功能 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个长度的字符串 #字符串函数: -- concat:拼接字符 select concat('hello','mysql'); #hellomysql -- lower:全部转为小写 select lower('HELLO');#结果为hello -- upper:全部转为大写 select upper('hello'); #结果为HELLO -- lpad:向左填充 select rpad('01',5,'hello');#将hello对01向左填充,中间表示总个数,结果为:hel01 -- rpad:向右填充 select rpad('01',5,'hello');#结果为10hel -- trim:去除收尾两个地方的空格 select trim(' Hello mysql '); #结果为:Hello mysql -- substring:截取字符串 select substring('hello mysql',1,5);#注意索引是从1开始,结果为hello -- 练习:由于业务的需求变更,企业员工工号,统一为5位数,目前不足5位数的全部咋前面补0,比如:1号员工的工号应该是00001. 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位小数 #数值函数 -- ceil:向上取整 select ceil(1.5);#结果为2 select ceil(1.1);#结果为2 -- floor:向下取整 select floor(1.9);#结果为1 -- mod #取余操作 select mod(5,4);#结果为1 ---5%4==1 -- rand:得出一个0到之间的随机数 select rand(); -- round:四舍五入 select round(2.34,1);#结果为2.3,means:对2.34进行四舍五入保留小数1位,2.3 select round(2.35,1);#结果为2.4 #根据需求,通过数据库的函数生成一个6位数的随机验证码 -- 通过向下取整的方式 select floor(rand()*1000000); -- 通过四舍五入的方式 select round( rand()*1000000,0); -- 改进 select lpad(round(rand()*1000000,0),6,'0');#不足六位的话就进行补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之间的天数 #日期函数 -- 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 DAY);#当前时间往后推70天 select date_add(now(),INTERVAL 70 MONTH);#当前时间往后推70个月 select date_add(now(),INTERVAL 70 YEAR );#当前时间往后推70年 -- datediff;两个指定时间相差的天数求两个时间的差 select datediff('2021-12-21','2021-10-01'); #查询所有员工的入职天数,并根据入职天数倒序排序 select name, datediff(curdate(),entrydate) as 'entrydates' from emp order by entrydates desc;
-
流程函数
流程函数也是一类很常用的函数,可以在SQL语句中实现条件筛选,从而提高语句的查询效率
函数 | 功能 |
---|---|
IF(values,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]…ELES [default] END | 如果expr的值等于val1,返回res1,…否则返回default默认值 |
#流程控制函数
-- if
select if (true,'ok','Error');#结果
select if (false,'ok','Error');#结果为Error
select if(2>3,'正确','错误');#结果为错误
-- ifnull:如果不为空就返回原值,否则返回默认值
select ifnull('ok','default'); #结果为ok
select ifnull('','default');#结果什么都没有
select ifnull(null,'default')#结果为default
-- case when then else end
-- 需求:查询emp表的员工姓名和工作地址(厦门/福州 ---> 一线城市,其他---->二线城市)
select
name,
(case workaddress when '厦门' then '二线城市' when '福州' then '二线城市' else '三线城市' end) as '工作地址'
from emp;
-- 案例:统计各个学员的成绩,展示规则如下
-- >=85,展示优秀
-- >=60 展示及格
-- 否则,展示不及格
create table score(
id int comment 'ID',
name varchar(20) comment '姓名',
math int comment '数学0',
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,78,90);
select * from score;
-- 实现
select
id,
name,
(case when math >=85 then '优秀' when math>=60 then '及格' else '不及格'end) '数学',
(case when english >=85 then '优秀' when english>=60 then '及格' else '不及格'end) '英语',
(case when chinese >=85 then '优秀' when chinese>=60 then '及格' else '不及格'end) '语文'
from score;
-3.总结:
-
字符串函数
CONCAT,LOWER,UPPER,LPAD,RPAD,TRIM,SUBSRTING
-
数值函数
CEIL,FLOOR,RAND,ROUND
-
日期函数
CURDATE,CURTIME,NOW,YEAR,MONTH,DAY,DATE_ADD,DATEDIFF
-
流程函数
IF,IFNULL,CASE[...]WHEN...THEN....ELSE...END