5、MySQL数据库-常用函数

五、常用函数

1.字符函数

函数名	说明
concat(S1,S2,…,Sn)	连接S1，S2…,Sn为一个字符串
concat(s,S1,S2,…,Sn)	同concat(s1,s2,…)函数，但是每个字符串之间要加上s
char_length(s)	返回字符串s的字符数
length(s)	返回字符串s的字节数，和字符集有关
insert(str,index,len,instr)	将字符串str从第index位置开始，len个字符长的子串替换为字符串instr
upper(s)或ucase(s)	将字符串s的所有字母转成大写字母
lower(s)或lcase(s)	将字符串s的所有字母转成小写字母
left(s,n)	返回字符串s最左边的n个字符
right(s,n)	返回字符串s最右边的n个字符
lpad(str,len,pad)	用字符串pad对str最左边进行填充，直到str的长度为len个字符
rpad(str,len,pad)	用字符串pad对str最右边进行填充，直到str的长度为len个字符
ltrim(s)	去掉字符串s左侧的空格
rtrim(s)	去掉字符串s右侧的空格
trim(s)	去掉字符串s开始与结尾的空格
trim([both] s1 from s)	去掉字符串s开始与结尾的s1
trim([leading] s1 from s)	去掉字符串s开始处的s1
trim([trailing]) s1 from s	去掉字符串s结尾处的s1
repeat(str,n)	返回str重复n次的结果
replace(str,a,b)	用字符串b替换字符串str中所有出现的字符串a
strcmp(s1,s2)	比较字符串s1,s2。s1<s2,则返回负数，s1>s2,则返回正数
substring(s,index,len)	返回从字符串s的index位置其len个字符

1.1 查询员工以姓名-工作显示

select concat(ename,'-',job) namejob from emp;

1.2 查询每个员工的姓名以及所占字节长度

select ename,length(ename) from emp;

1.3查询每个员工的姓名以及所占字符个数

select ename,char_length(ename) from emp;

1.4 将每个员工的部门编号第一个数字替换为8查询出来

select empno,insert(empno,1,1,8) from emp;

1.5 将所有的员工查询出来的名字为小写英文，方便查看

select lower(ename) from emp;

1.6 查询每个员工的入职年份

select hiredate,left(hiredate,4) from emp;

select hiredate,substring(hiredate,1,4) from emp;

1.7 查询员工编号并在最前面加上2020

select empno,lpad(empno,8,'2020') from emp;

2.数学函数

函数名	说明
abs(x)	返回x的绝对值
ceil(x)	返回大于x的最小整数值
floor(x)	返回大于x的最大整数值
mod(x,y)	返回x/y的模
round(x,y)	返回参数x的四舍五入的youy位的小数的值
truncate(x,y)	返回数字x截断为y位小数的结果
sqrt(x)	返回x的平方根
pow(x,y)	返回x的y次方

1.ABS绝对值
select abs(-2.4);    # 2.4

2.ceil向上取整 返回>=该参数的最小整数
select ceil(-1.09);  # -1
select ceil(0.09);   #  1
select ceil(1.00);   #  1

3.floor向下取整，返回<=该参数的最大整数
select floor(-1.09);  # -2
select floor(0.09);   #  0
select floor(1.00);   #  1

4.round 四舍五入
select round(1.8712345);   # 2
select round(1.8712345,2); # 1.87

5.truncate 截断
select truncate(1.8712345,1); # 1.8

6.mod 取余
select mod(-10,3);  # -1
select -10%3;       # -1
select  10%3;		#  1
select -10%-3;	    # -1
select  10%3;       #  1

7.rand返回 0-1 的随机数
select rand();

8.sqrt返回平方根
select sqrt(9);

9.pow返回次方根
select pow(2,3);

3.日期函数

3.1查看当前系统的日期和时间(三种方法)

(1)select concat(curdate(),' ',curtime())date;
(2)select now();
(3)SELECT CONCAT(YEAR(NOW()),'-',MONTH(NOW()),'-',DAY(NOW()),' ',
    HOUR(NOW()),':',MINUTE(NOW()),':',SECOND(NOW())) DATE ;

3.2 查看员工的入职年份

select hiredate,year(hiredate) from emp;

3.3 查看当前日期是一年中的第几周

select week(now());

3.4 查看当前日期是星期几

select dayname(now());

3.5 查询员工入职日期，显示X年X月X日显示

select date_format(hiredate,'%Y年%m月%d日')date from emp;

# 注意：y和Y不同。例如：1998，y显示98，Y显示1998。
# m,d显示数字，M,D显示英文

3.6 查询员工入职日期在 3/15/1981之前的员工信息

select * from emp where hiredate < STR_TO_DATE('3/15/1981','%m%d%Y')

4.流程控制函数

4.1 如果有奖金，则显示最终奖金，如果没有，则显示0

select if(comm is null,0,comm)comm from emp;

select ifnull(comm,0) comm from emp;

4.2 部门编号是30，工资显示为2倍；部门编号是20，工资显示为3倍；否则不变，显示 部门编号，新工资，旧工资

select deptno,sal,
case deptno
when 30 then sal*2
when 20 then sal*3
else sal
end newsal
from emp;

4.3 如果工资>4000,显示级别A；工资>3000,显示级别B；工资>2000,显示级别C；否则，显示D

select sal,
case 
when sal>4000 then 'A'
when sal>3000 then 'B'
when sal>2000 then 'C'
else 'D'
end as dengji
from emp;