mysql 常用函数

-- 绝对值 100
select abs(-100)
-- 四舍五入保留两位小数 4.6300
select round(4.6288*100)/100

-- 向下取整 9
select floor(9.9)

-- 向上取整 4
select CEIL(3.2)

-- 2的3次幂
SELECT power(2,3)

当前时间

now(), 2019-08-09 09:22:51

curdate(), 2019-08-09

curtime() 09:22:51

格式化日期

select DATE_FORMAT(now(),'%Y') 2019

DATE_FORMAT(now(),'%m') 08

DATE_FORMAT(now(),'%d') 09

DATE_FORMAT(now(),'%H') 09 小时(24)

DATE_FORMAT(now(),'%h') 09 小时(12)

DATE_FORMAT(now(),'%i') 47 分钟

DATE_FORMAT(now(),'%s') 20 秒

DATE_FORMAT(now(),'%w') 5 星期5

DATE_FORMAT(now(),'%W') Friday 星期5

DATE_FORMAT(now(),'%j') 本年第几天 221

DATE_FORMAT(now(),'%U') 本年第几周 31

DATE_FORMAT(now(),'%r') 时间(24) 09:48:01 AM

DATE_FORMAT(now(),'%T') 时间(12) 09:48:42

日期偏移计算

SELECT DATE_ADD(NOW(),INTERVAL 5 DAY) 2019-08-14 10:07:19 增加5天

SELECT DATE_ADD(NOW(),INTERVAL -5 DAY) 2019-08-04 10:08:24 减少5天

SELECT DATE_ADD(NOW(),INTERVAL -5 MINUTE) 减少5分钟

SELECT DATE_ADD(NOW(),INTERVAL -5 HOUR) 减少5小时

SELECT DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -5 HOUR) ,"%Y/%m/%d") 2019/08/09

计算两个时间相差天数

SELECT DATEDIFF('2019-7-9',NOw()) -31

字符函数

select LOWER('ERIC') eric 转小写

select upper('eric') ERIC 转大写

select LENGTH ('eric') 计算长度 4

select CONCAT('eric','love','beijing') 字符串拼接 ericlovebeijing

select INSERT('你好',1,0,'先生') 插入替换字符先生你好

select INSTR('eric','r') 2 字符出现的位置没有返回0

select REPLACE('你好先生','先生','女士') 你好女士字符替换

字符串截取

select SUBSTR('你好世界',3,4) 世界
select SUBSTRING('你好世界',3,1) 世

select LPAD(SUBSTRING('13312345678',8,4),11,"*") *******5678

select RPAD(SUBSTRING('13312345678',1,7),11,"*") 1331234****

select REPLACE('13312345678',SUBSTRING('13312345678',4,4), '****') 133****5678

select RPAD(SUBSTRING('李晓娜',1,1),3,'*') 李**

select TRIM(' Hello World ') 去除两边空格

条件判断表达式

select
e.ename,e.empno,d.dname,
if(d.dname = "SALES",'礼品A','礼品B')
from t_emp e
join t_dept d on d.deptno = e.deptno

SMITH 7369 RESEARCH 礼品B
ALLEN 7499 SALES 礼品A
WARD 7521 SALES 礼品A
JONES 7566 RESEARCH 礼品B
MARTIN 7654 SALES 礼品A
BLAKE 7698 SALES 礼品A

/**
SALES 去p1
ACCOUNTING 去p2
RESEARCH 去p3
没部门的去p4
*/

select
e.ename,e.empno,d.dname,
CASE
WHEN d.dname = "SALES" THEN 'P1'
WHEN d.dname = "ACCOUNTING" THEN 'P2'
WHEN d.dname = "RESEARCH" THEN 'P3'
ELSE 'P4'
END as place
from t_emp e
left join t_dept d on d.deptno = e.deptno

ADAMS 7876 RESEARCH P3
FORD 7902 RESEARCH P3
ALLEN 7499 SALES P1
WARD 7521 SALES P1
MARTIN 7654 SALES P1
BLAKE 7698 SALES P1
TURNER 7844 SALES P1
JAMES 7900 SALES P1
李四 6789 P4

/**
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 t.deptno = e.deptno
set e.sal = (
CASE
WHEN d.dname = 'SALES' AND DATEDIFF(NOW(),e.hiredate)/365 >= 20 THEN sal * 1.1
WHEN d.dname = 'SALES' AND DATEDIFF(NOW(),e.hiredate)/365 < 20 THEN sal * 1.05
WHEN d.dname = 'ACCOUNTING' THEN e.sal + 300
WHEN d.dname = 'RESEARCH' and e.sal < t.avg THEN e.sal + 200
WHEN e.deptno IS NULL THEN sal + 100
else e.sal
END
)