一、常用函数分类
- 数学函数
- 字符串函数
- 日期和时间函数
- 流程控制函数
- 其他函数
二、数学函数
1、四舍五入函数
round(x,y):
- y=0:round(x),四舍五入为整数
- y>0:四舍五入到小数点右边的第y位
- y<0:从小数点左边的第|y|位进行四舍五入
示例:
select round(455.55),round(455.55,0),round(455.55,1),round(455.55,-1),round(455.55,-3),round(555.55,-3) from dual;
将小数123456.488965四舍五入到百位
select round(123456.488965,-2) from dual;
2、截断函数
truncate(x,y):
- y=0:截断为整数
- y>0 :截断到小数点右边的第y位
- y<0:从小数点左边的第|y|位进行截断
示例:
select truncate(455.55,0),truncate(455.55,1),truncate(455.55,-1),truncate(455.55,-3) from dual;
note:此处truncate(455.55,0),0不可省略,与四舍五入函数不同
3、求余
mod(x,y):
select mod(3,2) from dual;
select mod(3,0) from dual;
练习1
1.写一个查询,分别计算100.456 四舍五入到小数点后第2位,第1位,整数位的值。
select round(100.456,2),round(100.456,1),round(100.456,0) from dual;
2.写一个查询,分别计算100.456 从小数点后第2位,第1位,整数位截断的值。
select truncate(100.456,2),truncate(100.456,1),truncate(100.456,0) from dual;
附:
- abs(x):返回x的绝对值;
- sqrt(x):返回非负数x的平方根; PI():返回圆周率;
- mod(x,y)或%:返回x被y除的余数;
- ceil(x)、ceiling(x):返回大于或者等于x的最小整数值;
- floor(x):返回小于或者等于x的最大整数值;
- round(x,y):返回保留小数点后面y位,四舍五入的整数;
- truncate(x,y):返回被舍弃的小数点后y位的数字x;
- rand():每次产生不同的随机数; sign(x):返回参数的符号;
- pow(x,y)和power(x,y): 返回x的y次乘方的结果值;
- exp(x):返回以e为底的x乘方后的值;
- log(x):返回x的自然对数,x相对于基数e的对数;
- log10(x):返回x的基数为10的对数;
- radians(x):将参数x由角度转化为弧度;
- degrees(x):将参数x由弧度转化为度。
- sin(x):返回x正弦,其中x为弧度值;
- asin(x)返回x的反正弦,即正弦为x的值;
- cos(x):返回x的余弦;
- acos(x):返回x反余弦
- tan(x):返回x的正切;
- atan(x)返回x的反正切;
三、字符串函数
1、length(str):求字符串的长度
select length('abcabcabcabc') from dual;
查询员工的姓名长度为5的员工姓名、工资
select ename,sal from emp where length(ename)=5;
或者
select ename,sal from emp where ename like '_____';
其中 '_____'为5个占位符。
2、转大写,转小写
select lower(ename),upper(ename) from emp;
查询员工姓名为smith的员工信息
select * from emp where lower(ename)='smith';
select lower('ksdkDHKFLmdmmkKLJNJj'),upper('JJKKKhxjkcjjsJkkMm') from dual;
3、补足函数
lpad(str,len,str1):判断str的长度是否等于len,如果小于len,在str左边补充str1使其长度等于len为止。如果大于len,将str截断使其长度等于len
rpad(str,len,str1):判断str的长度是否等于len,如果小于len,在str右边补充str1使其长度等于len为止。如果大于len,将str截断使其长度等于len
select lpad('hello',20,'abc$') from dual;
select lpad('hello world',10,'abc') from dual;
select rpad('hello',20,'abc$') from dual;
select rpad('hello world',10,'abc') from dual;
4、trim()
去空格
select length(trim(' abc '));
去除字符串左右的某个字符
select trim('a' from 'abcdefg'),trim('a' from 'aaaaaaaaabcdefg') from dual;
select trim('tt' from 'ttttttttestttttttt') from dual;
select trim('tt' from 'tttttttttestttttttt') from dual;
select trim('tt' from 'tttttttttesttttttttt') from dual;
5、替换函数
replace(s,s1,s2):将s中的s1替换为s2
将员工姓名中的A替换为a
select ename,replace(ename,'A','a') from emp;
select replace('abc aaa abc bbb abc ccc','abc','###'),replace('abc aaa abc bbb abc ccc','abc','#') from dual;
6、截取子串
substring(str,start,length)
截取到字符串的尾部,省略第3个参数
select substring('hello world',3) from dual;
如果要从字符串的第1位开始截取,起始位置必须为1,不能为0
select substring('hello world',1,5) from dual;
起始位置必须为1,不能为0,结果是截取为空串
select substring('hello world',0,5) from dual;
练习2
1.显示所有员工姓名的前三个字符
select substring(ename,1,3) from emp;
2.显示正好为5个字符的员工的姓名,工资,部门号
select ename,salary,department_id from emp where length(ename)=5;
练习3
1.写一个查询,用首字母大写,其它字母小写显示雇员的 ename,显示名字的长度,并给每列一个适当的标签,条件是满足所有雇员名字的开始字母是J、A 或 M 的雇员,并对查询结果按雇员的ename升序排序。(提示:使用length、substr)
select concat(upper(substring(ename,1,1)),lower(substring(ename,2))) 名称,length(ename) 长度
from emp
where upper(substring(ename,1,1)) in ('J','A','M') order by ename;
或者
select concat(upper(left(ename,1)),lower(right(ename,length(ename)-1))) 名称,length(ename) 长度
from emp
where upper(left(ename,1)) in ('J','A','M') order by ename
2.查询员工姓名中包含大写或小写字母A的员工姓名。
select ename from emp where locate('A',upper(ename))>0
3.显示所有员工的姓名,用a替换所有"A"
select replace(ename,'A','a') from emp
4.查询部门编号为10或20,入职日期在81年5月1日之后,并且姓名中包含大写字母A的员工姓名,员工姓名长度
select ename,length(ename) from emp where deptno in(10,20) and hiredate>'1981-05-01' and locate('A',ename)>0
5.查询每个职工的编号,姓名,工资
要求将查询到的数据按照一定的格式合并成一个字符串.
前10位:编号,不足部分用*填充,左对齐
中间10位:姓名,不足部分用*填充,左对齐
后10位:工资,不足部分用*填充,右对齐
select concat(rpad(empno,10,'*'),rpad(ename,10,'*'),lpad(sal,10,'*')) from emp
附:
- char_length(str):返回字符串str的所包含字符个数;
- length(str):返回字符串str的长度;
- concat(s1,s2,...): 字符串连接;
- concat_ws(x,s1,s2,…):字符串连接, x是其它参数的分隔符;
- insert(s1,x,len,s2) :返回字符串s1,s1中插入字符串s2;
- lower (str) | lcase (str):将字符串全部转换成小写字母;
- upper(str) | ucase(str):将字符串全部转换成大写字母;
- left(s,n):返回最左边指定长度的字符;
- right(s,n):返回最右边指定长度的字符;
- lpad(s1,len,s2) | rpad(s1,len,s2) :填充字符串函数;
- trim(s1 from s) | ltrim(s) | rtrim(s):删除空格函数;
- repeat(s,n):重复生成字符串函数;
- space(n):返回一个由n个空格组成的字符串;
- replace(s,s1,s2):字符串替换函数;
- strcmp(s1,s2):比较字符串大小函数;
- substring(s,n,len):获取子串函数;
- locate(str1,str) | position(str1in str) | instr(str, str1):匹配子串开始位置函数;
- reverse(s):将字符串s反转;
- elt(N,字符串1,字符串2,字符串3,…):返回指定位置函数;
四、日期函数
1、查询当前日期
now():
select now(),curdate(),curtime() from dual;
2、两个日期之间相差的天数
select datediff(now(),'1990-02-03') from dual;
3、获取日期中的部分元素值
select extract(year from now()) from dual;
select extract(month from now()) from dual;
select extract(day from now()) from dual;
查询在前半年入职的员工信息
select * from emp where extract(month from hiredate) <=6;
4、date? + interval? expr? unit
date? - interval? expr? unit
select now() - interval 1 day from dual;
练习四
1.查询服务器当前时间
select now() from dual;
2.查询部门10,20的员工截止到2000年1月1日,工作了多少个月,入职的月份。
select ceil(datediff('2000-01-01',hiredate)/30),monthname(hiredate)from emp where deptno in(10,20);
3.如果员工试用期6个月,查询职位不是MANAGER的员工姓名,入职日期,转正日期,入职日期是第多少月,第多少周
select ename,hiredate,date_format(date_add(hiredate,interval 6 month),'%y-%m-%d')转正期,month(hiredate),weekofyear(hiredate) from emp where job<>'MANAGER';
或者
select ename,hiredate,date_format(hiredate+interval 6 month,'%y-%m-%d') 转正日期,month(hiredate),weekofyear(hiredate)
from emp where job<>'MANAGER';
附:
CURDATE()和CURRENT_DATE() :获取当前日期函数;
NOW():返回服务器的当前日期和时间;
CURTIME():返回当前时间,只包含时分秒;
UTC_DATE():返回世界标准时间日期函数;
UTC_TIME():返回世界标准时间函数;
TIMEDIFF(expr1, expr2):返回两个日期相减相差的时间数;
DATEDIFF(expr1, expr2):返回两个日期相减相差的天数;
DATE_ADD(date,INTERVAL expr type):日期加上一个时间间隔值;
DATE_SUB(date,INTERVAL expr type):日期减去一个时间间隔值;
DATE(date)、TIME(date)、YEAR(date):选取日期时间的各个部分:
EXTRACT(unit FROM date):从日期中抽取出某个单独的部分或组合;
DAYOFWEEK(date) 、DAYOFMONTH(date) 、DAYOFYEAR(date):返回日期在一周、一月、一年中是第几天
DAYNAME、MONTHNAME:返回日期的星期和月份名称;
DATE_FORMAT(date,format):格式化日期;
根据format 指定的格式显示日期或者时间值。 date 参数是合法的日期 format 规定日期/时间的输出格式
TIME_FORMATE(time,formate):格式化时间;
其中 可使用的格式:
五、流程控制函数
六、其他函数