#数学函数#round 四舍五入SELECTROUND(1.78);#ceil向上取整SELECT CEIL(-1.02);#floor向下取整SELECT FLOOR(9.99);#truncate 截断SELECTTRUNCATE(1.6999,1);#mod取余SELECTMOD(-10,3);#三、日期函数#now 返回当前日期+时间SELECTNOW();#curdate返回当前日期SELECT CURDATE();#curtime返回当前时间SELECT CURTIME();#str_to_date 将字符通过指定格式转化为日期SELECT STR_TO_DATE('1998-6-1','%Y-%c-%d')AS out_put;SELECT STR_TO_DATE('6-1 1998','%c-%d %Y')AS out_put;#date_format 将日期转化为字符SELECT DATE_FORMAT(NOW(),'%y年%m月%d日')AS out_put;#四、其他函数SELECT VERSION();SELECTDATABASE();SELECTUSER();#五、流程控制函数SELECTIF(10<5,'大','小');#case结构/*
case 要判断的字段或者表达式
when 常量1 then 要显示的值1或语句
when 常量2 then 要显示的值2或语句
……
else 要显示的值n或语句n
end
*/SELECT salary 原始工资,department_id,CASE department_id
WHEN30THEN salary*1.1WHEN40THEN salary*2.1WHEN50THEN salary*3.1ELSE salary
ENDAS 新工资
FROM employees;/*
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
……
else 要显示的值n或语句n
end
*/SELECT salary ,CASEWHEN salary>20000THEN'A'WHEN salary>15000THEN'B'WHEN salary>10000THEN'C'ELSE'D'ENDAS gongzijibie
FROM employees;