函数

 

（一）单行函数：只对某一行做运算

1.数学函数

round（）：四舍五入

truncate（）：只保留位数

#select salary ,round(salary,1) as salary_round,truncate(salary,1) as salary_truncate from employee

2.字符串函数

#select name,left(name,1) from employee

3.日期时间函数

#select now() 2020-06-21 07:11:53
#select SYSDATE() 2020-06-21 07:11:53
#select CURRENT_DATE() 2020-06-21
#select CURRENT_TIME() 07:12:21
#select YEAR(CURRENT_DATE()) 2020

#select id,birthday,YEAR(CURRENT_DATE())-YEAR(birthday) as birthday_new from birthday

#select id,DATEDIFF(CURRENT_DATE(),birthday) from birthday 天数

#select birthday,DATE_ADD(birthday,INTERVAL 10 day) from birthday
#select birthday,DATE_ADD(birthday,INTERVAL -10 day) from birthday

#select DATE_FORMAT(now(),'%y年%c月%e日')      20年6月21日
#SELECT STR_TO_DATE('20年3月25日','%y年%c月%e日')    2020-03-25

（二）分组函数：多行一起统计

     sum,max,min,agc,count

（三）流程控制函数

1.ifnull（x,value）

如果x是空值，就用value计算，否则还是用x计算

2.case  ---相当于if .....else

select name,salary,
CASE
when salary>=3000 then "A"
when salary>=2500 then "B"
else "C"
end as class
from employee

3.case 相当于switch......case

select name,
case gender
when '女' then 'female'
when '男' then 'male'
end as gender_new
from employee

（四）加密函数  

#select name,password,PASSWORD(password) as pwd from user
#select name,password,md5(password) as pwd from user
#select name,password from user where md5(password)='827ccb0eea8a706c4c34a16891f84e7b'