2021-08-08_小白的oracle 学习整理（五）常用函数及练习

oracle 学习整理（五）

1 常用的oracle函数

1.1 数字相关的函数

求绝对值		abs(n)
四舍五入		round(数字, 小数的保留精度)
数字的截取	trunc(数字, 小数的保留精度)
向上取整		ceil(数字)
向下取整		floor(数字)
取余数		mod(除数, 被除数)
幂运算		power(数字, 次方)

1.2 字符串相关的函数

转换小写				lower(s)	
转换大写				upper(s)
转换首字母大写		initcap(s)
替换字符串			replace(字符串, 旧的内容, 新的内容)
字符串的截取			
第一种用法：  substr(字符串, 开始截取的位置, 连续截取的长度)
开始位置是从1开始的
第二种用法：  substr(字符串, 开始截取的位置)      不写长度，默认是到字符串最末尾
第三种用法：  substr(字符串, 负的开始位置)          从后面开始数数

字符串的拼接			concat(s1, s2)    将两个字符串拼接成一个字符串
select concat(concat(concat('广东省','深圳市'), '宝安区'), '新安街道') from dual;
select '广东省'||'深圳市'||'宝安区'||'新安街道' from dual;

查找字符串			instr(字符串, 要查找的字符)	 返回被查找的字符在原字符串中的位置
查询字符串的长度		length(s)
在emp表中，查找名字刚好是4个字符的都有谁？
select * from scott.emp where length(ename)=4;
去除左右空格			trim(s)    ltrim(s)    rtrim(s)
填充左右字符			lpad(字符串，总长度，长度不够的时候用来填充的符号)
lpad()	rpad()
select lpad('hello',10,'X') from dual;
select rpad('hello',10,'X') from dual;

1.3 特殊的聚合函数wm_concat()

对字符串进行行列转换    wm_concat(列)	将这一列的字符串拼接成一行数据，可以和分组结合使用
默认逗号分隔

1.4 时间日期相关的函数

系统关键字：获取当前服务器的时间和日期		sysdate
查询某个日期所在月份的最后一天日期是多少        last_day(时间)
select last_day(date'2021-2-5') from dual;
月份的偏移								add_months(日期, 月份的数字)
select add_months(date'2021-2-5',-13) from dual;
月份间隔的查询							months_between(日期1, 日期2)
天数的偏移								日期 - 数字      日期 + 数字

trunc(日期)
select trunc(sysdate,'yyyy') from dual;	查询这个日期所在年的第一天
select trunc(sysdate,'mm') from dual;	查询这个日期所在的月的第一天
select trunc(sysdate,'dd') from dual;	截取掉时分秒的数据
select trunc(sysdate,'day') from dual;	查询这个日期所在的星期的第一天是哪天

1.5 转换相关的函数

将数字内容的字符串转换成数字：to_number()

将数字或者是日期转换成字符串：to_char()
将日期转换成字符串：
select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'day') from dual;

1.6 练习

查询出emp表格里面，所有5月份入职的员工信息
select * from scott.emp where to_char(hiredate,'mm')=5;

按照年份来分组，查询出emp表格里面，每一年入职的员工人数
select to_char(hiredate,'yyyy'),count(1) from scott.emp 
group by to_char(hiredate,'yyyy');

查询（编写出）出一个永远是明天早上8点的时间。
select to_date(concat(to_char(sysdate+1,'yyyy-mm-dd'),' 8:00:00'),'yyyy-mm-dd hh24:mi:ss')  from dual;

查询出入职的时间超过了38年的员工信息。
select * from scott.emp where months_between(sysdate,hiredate)/12>38;

2 最经典的题目

2.1查询每个员工，入职了多少年多少月多少日。

ename   year  month  day
SMITH   XX    yy     zz
ALLEN   xx1   yy2    zz3

select ename,
       floor(months_between(sysdate,hiredate)/12) year,
       floor(mod(months_between(sysdate,hiredate),12)) month,
       ceil(sysdate-add_months(hiredate,floor(months_between(sysdate,hiredate)))) day
 from scott.emp;

3 练习

作业：
1. 查询名字由J开头的员工中的最高工资是多少？
select max(sal) from scott.emp where ename like 'J%';

2. 查询CLERK和SALESMAN这两个岗位的平均工资是多少，保留两位小数，平均工资降序排列。
select job,round(avg(sal),2) from scott.emp where job in ('CLERK','SALESMAN')
 group by job order by avg(sal) desc;

3. 查询最低工资高于1500的工作岗位有哪几个？
select job from scott.emp group by job having min(sal)>1500;

4. 查询每个部门的工资总和，并且最后一行要显示所有部门的工资总和。
select deptno,sum(sal) from scott.emp group by rollup(deptno);

5. 查询部门平均工资大于2000并且部门里面有PRESIDENT这个岗位的部门编号是哪一个？
select deptno from scott.emp where job='PRESIDENT'
intersect
select deptno from scott.emp group by deptno
having avg(sal)>2000;

6. 查询所有在15号之前入职的员工。
select * from scott.emp where to_char(hiredate,'dd')<15;