|
|
函数（MySQL内置函数）
函数 是指一段可以直接被另一段程序调用的程序或代码。
也就意味着，这一段程序或代码在MySQL中已经给我们提供了，我们要做的就是在合适的业务场景调用对应的函数完成对应的业务需求即可。
MySQL中的函数主要分为以下四类：
字符串函数
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');
#lower 全部转小写
select lower('Hellow');
#upper 全部转大写
select upper('Hellow');
#lpad 用字符串'-'对'01'进行左填充，达到5个字符长度
select lpad('01',5,'-')
#rpad 右填充
select rpad('01',5,'-');
#trim 去除空格
select trim(' Hellow MySQL ');
#substring 截取字符串
select substring('Hellow MySQL',1,5);
练习：
- 由于业务需求变更，企业员工的工号，统一为5位数，目前不足5位数的全部在前面补0，比如： 1号员工的工号应该为00001
变更前：
变更：
update emp set workno=lpad(workno,5,'0');
数值函数
MySQL常见的数值函数：
| 函数 | 功能 | | CEIL(x) | 向上取整 | | FLOOR(x) | 向下取整 | | MOD(x,y) | 返回x/y的模 | | RAND() | 返回0~1内的随机数 | | ROUND(x,y) | 求参数x的四舍五入的值，保留y位小数 |
演示：
#ceil 向上取整
select ceil(1.5);
#floor 向下取整
select floor(1.5);
#mod 取模
select mod(5/2);
#rand 获取随机数
select rand();
#round 四舍五入
select round(1.234,2);
练习：
通过数据库的函数，生成一个6位数的随机验证码
select rpad(round(rand()*1000000,0),6,'0');
#思路
#获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础上乘以1000000
select rand()*1000000;
#然后通过round()函数舍弃小数部分
select round(rand()*1000000,0)；
#考虑到0-1之间的随机数可能像0.00853234...这样乘于1000000不满足6位数，可以在此基础上可以向右(左)补零
select rpad(round(rand()*1000000,0),6,'0');
日期函数
MySQL常见的日期函数：
| 函数 | 功能 | | 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 当前年
select year(now());
#month 当前月
select month(now());
#day 当前日
select day(now());
#date_add 增加指定的时间间隔 当前时间往后推70天
select date_add(now(),interval 70 day);
#datediff 获取两个日期相差的天数
select datediff('2022-10-16','2021-10-1');
练习：
查询所有员工入职天数，并根据入职天数倒序排序
#思路：入职天数，就是通过当前日期curdate()减去入职日期，所以需要使用datediff函数来完成。
select name,datediff(curdate(),entrydate) as 'entrydays' from emp order by entrydays desc;
流程函数
流程控制函数也是很常用的一类函数，可以在SQL语句中实现条件筛选，从而提高语句效率
MySQL常用的流程函数：
| 函数 | 功能 | | 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默认值 |
演示：
#if
select if(true,'ok','error');
#ifnull
select ifnull('ok','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 '数学',
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,
(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;
 |
|
发表于 2022-9-23 12:58:40