mysql 常用函数

一、数字函数

函数	功能	用例
ABS	绝对值	ABS(-100)
ROUND	四舍五入	ROUND(4.62)
FLOOR	强制舍位到最近的整数	FLOOR(9.9)
CEIL	强制进位到最近的整数	CEIL(3.2)
POWER	幂函数	POWER(2,3)
LOG	对数函数	LOG(7,3)
LN	对数函数	LN(10)

select abs(-100) #100
select round(4.6288*100)/100 #4.6300
select floor(9.9) # 9
select ceil(3.2)  #4
select log(7,3) #0.5645750
select power(2,3)#8
select ln(10) #2.30545857

函数	功能	用例
SQRT	开平方	SQRT(100)
PI	圆周率	PI()
SIN	三角函数	SIN(1)
COS	三角函数	COS(1)
TAN	三角函数	TAN(1)
COT	三角函数	COT(1)
RADIANS	角度转弧度	RADIANS(30)
DEGREES	弧度转角度	DEGREES(1)

其中三角函数中均为弧度

select SQRT(100) #10
select PI() #3.141593
select sin(radians(30)) # 0.4999999994
select cos(radians(45))  #0.707106
select tan(radians(30)) #0.57735
select cot(radians(45))#1.0000000002
select degrees(1) #57.89

二、获取系统时间函数

• NOW() 函数能获得系统日期和时间，格式yyyy-MM-dd hh:mm:ss
• CURDATE()函数能获得当前系统日期，格式yyyy-MM-dd
• CURTIME()函数能获得当前系统时间，格式hh:mm:ss

1.日期格式化函数

DATE_FORMAT()函数用于格式化日期，返回用户想要的日期格式。

DATE_FORMAT(日期,表达式)
select ename,DATE_FORMAT(hiredate,"%Y")
from t_emp;

函数	作用	占位符	作用
%Y	年	%m	月份
%d	日期	%w	星期(数字)
%W	星期(名称)	%j	本年第几天
%U	本年第几周	%H	小时(24)
%h	小时(24)	%i	分钟
%s	秒	%r	时间(12)
%T	时间(24)

select DATE_FORMAT("2022-07-07","%W") #Tursday

select count(*) from t_emp
where DATE_FORMAT(hiredate,"%Y")=1998
and DATE_FORMAT(hiredate,"%m")<6; #5

2.日期计算的注意事项

mysql数据库中，两个日期不能直接相加减，日期也不能与数字加减。

(1)日期偏移量计算

DATE_ADD() 函数可以实现日期偏移量的计算，而且时间单位很灵活。

DATE_ADD(日期,INTERVAL 偏移量 时间单位)

select DATE_ADD(NOW(),INTERVAL 100 DAY);
select DATE_FORMAT(DATE_ADD(DATE_ADD(NOW(),INTERVAL -6 MONTH),INTERVAL -3 DAY),"%Y/%m/%d");

(2)计算日期之间相隔天数

DATEDIFF() 函数用来计算两个日期之间相差的天数

DATEDIFF(日期,日期)

三、字符函数

函数	功能	用例
LOWER	转换小写字符	LOWER(ename)
UPPER	转换大写字符	UPPER(ename)
LENGTH	字符数量	LENGTH(ename)
CONCAT	连接字符串	CONCAT(sal,“$”)
INSTR	字符出现的位置	INSTR(ename,“A”)
INSERT	插入/替换字符	INSERT(“你好”,1,0,“先生”)
REPLACE	替换字符	REPLACE(“你好先生”,“先生”,“女士”)

select insert("你好",1,0,"先生");#0表示不替换;先生，你好
select replace("你好先生","先生","女士");#你好女士

函数	功能	用例
SUBSTR	截取字符串	SUBSTR(“你好世界”,3,4)
SUBSTRING	截取字符串	SUBSTRING(“你好世界”,3,2)
LPAD	左侧填充字符	LPAD(“Hello”,10,“*”)
RPAD	右侧填充字符	RPAD(“Hello”,10,“*”)
TRIM	去除收尾空格	TRIM(" 你好先生")

select SUBSTR("你好世界",3,4);#世界
select SUBSTRING("你好世界",3,2);#世界
select lpad(substring("13456785432",8,4),11,"*");
#********5432
select rpad(substring("李晓娜"),1,1),length("李晓娜")/3,"*");
#李********
select trim("  hello world  ");

四、条件函数

sql语句中可以利用条件函数来实现变成语言里的条件判断。

(1)

ifnull(表达式,值)
if(表达式,值1,值2)

#sales部门发放礼品a,其余部门发放礼品b,打印每名员工获得的礼品。
select e.empno,e.ename,d.dname,
if(d.dname="sales","礼品a","礼品b") 
from t_emp e join t_dept d on e.deptno=d.deptno;

(2)

复杂的条件判断可以用条件语句来实现，比if语句功能更强大

case
  when 表达式 then 值1
  when 表达式 then 值1
  .....
  else 值n
end

/*sales部门去p1地点
accounting 部门去p2地点
research 部门去p3地点
*/
select 
	e.empno,e.ename,
	case 
		when d.dname="sales" then "p1"
		when d.dname="accounting" then "p2"
		when d.dname="research" then "p3"
	end as place
from t_emp e join t_dept d on e.deptno=d.deptno;

/*
sales部门中工龄超过20年 涨幅10%
sales 部门中工龄不满20年 涨幅5%
accounting 部门 +300元
research 部门中低于部门平均底薪 +200
没有部门的员工 +100
*/
update t_emp e left join t_dept d on e.deptno=d.deptno
left join (select deptno,avg(sal) as avg from t_emp group by deptno) t
on e.deptno=t.deptno
set sal=(
  case 
     when d.dname="sales" and datediff(now(),e.hiredate)/365>=20
     then e.sal*1.1
     when d.dname="sales" and datediff(now(),e.hiredate)/365<20
     then e.sal*1.05
     when d.name="research" and e.sal<t.avg 
     then e.sal+200
     when e.deptno is null then e.sal+100
     ELSE e.sal
     END
     );