五、常用函数
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. ceil向上取整 返回>= 该参数的最小整数
select ceil( - 1.09 ) ;
select ceil( 0.09 ) ;
select ceil( 1.00 ) ;
3. floor向下取整,返回<= 该参数的最大整数
select floor( - 1.09 ) ;
select floor( 0.09 ) ;
select floor( 1.00 ) ;
4. round 四舍五入
select round ( 1.8712345 ) ;
select round ( 1.8712345 , 2 ) ;
5. truncate 截断
select truncate ( 1.8712345 , 1 ) ;
6. mod 取余
select mod ( - 10 , 3 ) ;
select - 10 % 3 ;
select 10 % 3 ;
select - 10 % - 3 ;
select 10 % 3 ;
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;
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;