SQL(MySQL）--函数（5）

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');	Hello MySQL
lower:全部转小写	select lower('Hello');	hello
upper:全部转大写	select upper('Hello');	HELLO
lpad:左填充	select lpad('01', 5, '-');	---01
rpad:右填充	select rpad('01', 5, '-');	01---
trim:去除空格	select trim('       Hello MySQL       ');	Hello MySQL
substring:截取子字符串	select substring('Hello MySQL',1,4);	Hell

 E.g.企业员工的工号，统一为5位数，不足5位数的在前面补0。

如1号员工的工号应该为00001: update emp set workno = lpad(workno, 5, '0'); 

二.数值函数

数值函数	功能	例子
CEIL(x)	向上取整	ceil：向上取整 →select ceil(1.1);
FLOOR(x)	向下取整	floor：向下取整 →select floor(1.9);
MOD(x,y)	返回x/y的模	mod：取模 →select mod(7,4);
RAND()	返回0~1内的随机数	rand：获取随机数 →select rand();
ROUND(x,y)	求参数x的四舍五入的值，保留y位小数	round：四舍五入 →select round(2.344,2);

E.g.通过数据库的函数，生成一个六位数的随机验证码。

思路：获取随机数可以通过rand()函数，但是获取出来的随机数是在0-1之间的，所以可以在其基础上乘以1000000，然后舍弃小数部分，如果长度不足6位，补0

select 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之间的天数

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 YEAR );
datediff：获取两个日期相差的天数	select datediff('2021-10-01','2021-12-01');

E.g.查询所有员工的入职天数，并根据入职天数倒序排序。

思路： 入职天数，就是通过当前日期 - 入职日期，所以需要使用datediff函数来完成。

select name, datediff(curdate(), entrydate)

as 'entrydays' from emp order by entrydays desc;

四.流程函数 

流程函数	功能
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');	OK
	select if(false, 'Ok', 'Error');	Error
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;

存储的是入职日期，如 2000-01-01，如何快速计算出入职天数：datediff

存储的是学生的分数值，如98、75，如何快速判定分数的等级：case ... when ...