MySQL34--数学函数及运算符

数学函数及运算符

名称	描述
+、-、*、/、％	加、减、乘、除、取余
CEIL(X)	进一取整
FLOOR(X)	舍一取整
POW(X,Y)/POWER(X,Y)	幂运算
SQRT(X)	获取平方根
RAND()	产生0~1之间的随机数
ROUND(X[,D])	四舍五入(数值,位数)
TRUNCATE(X,Y)	截取函数(数值,位数)

SELECT ROUND(45.393,2);  -- 45.39
SELECT ROUND(45.393,1);  -- 45.4
SELECT ROUND(45.393,0);  -- 45
SELECT ROUND(45.693,0);  -- 46
SELECT ROUND(45.693,-1); -- 50
SELECT ROUND(45.693,-2); -- 0
SELECT ROUND(43.693,-1); -- 40
SELECT ROUND(55.693,-2); -- 100

-- 查询emp表中,员工编号(empno),姓名(ename),工资(sal),日薪(sal/30),年薪(sal*12)
SELECT empno,ename,sal,sal/30,sal*12 FROM emp;
SELECT empno,ename,sal,ROUND(sal/30,2),sal*12 FROM emp;
SELECT empno,ename,sal,ROUND(sal/30,2) AS day_sal,sal*12 AS YEAR_sal FROM emp;

SELECT TRUNCATE(45.393,2);  -- 45.39
SELECT TRUNCATE(45.393,1);  -- 45.3
SELECT TRUNCATE(45.393,0);  -- 45
SELECT TRUNCATE(45.693,0);  -- 45
SELECT TRUNCATE(45.693,-1); -- 40
SELECT TRUNCATE(45.693,-2); -- 0
SELECT TRUNCATE(65.693,-2); -- 0