Oracle基础查询用到的一些函数

今天我们来复习一下oracle的基础查询,所有的查询都基于SCOTT用户下的emp表
1.去重

select distinct(job) FROM emp;

2.全年收入总和（工资加奖金）

select ename,sal*12+nvl(comm,0) from emp;

3.查询员工表员工编号，姓名，输出格式如下：编号：XXX，姓名：XXX

select concat(concat('编号：',empno),concat('姓名：',ename)) from emp;
or
select '编号：'||empno||',姓名：'||ename from emp;

4.查询1981-1-1到1981-12-31入职的员工

select * from emp where hiredate between to_date('1981-1-1','yyyy-MM-dd') and to_date('1981-12-31','yyyy-MM-dd');

5.查询雇员的奖金并按降序排列

select ename,comm from emp order by comm desc nulls last;
--nulls last空值在后，默认是nulls first

6.查询雇员的工资做降序排列，并且在其中奖金部分是升序排列

select ename,sal,comm from emp order by sal desc,comm asc;
--结果就是以工资为准，工资一样的，按奖金排

7.将smith转换成大写

select upper('smith') from dual;

8.将ename全转成小写

select lower(ename) from emp;

9.首字母大写

select initcap(ename) from emp;

10.'helloworld’截取成hello

select substr('helloworld',0,5) from dual;

11.将hello中l用x替换

select replace('hello','l','x') from dual;

12.数值函数
round函数

select round(15.66,-2) from dual;		--0
select round(15.66,-1) from dual;		--20
select round(15.66,0) from dual;		    --16
select round(15.66,1) from dual;			--15.7
select round(15.66,2) from dual;			--15.66

trunc函数

select trunc(15.66,-2) from dual;				--0
select trunc(15.66,-1) from dual;				--10
select trunc(15.66,0) from dual;				--15
select trunc(15.66,1) from dual;				--15.6
select trunc(15.66,2) from dual;				--15.66

对15/3求余

select mod(15,3) from dual;

13.日期函数
查询雇员进入公司的周数

select ename,(sysdate-hiredate)/7 from emp;

查询雇员进去公司的月数

select ename,months_between(sysdate,hiredate) from emp;

三个月后的日期

select ename,hiredate,add_months(hiredate,3) from emp;

15.转换函数
将系统日期显示为2020-03-18 02:38:33格式

select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

如果想要24小时制并去掉月份前面的0的话

select to_char(sysdate,'yyyyfm-mm-dd hh24:mi:ss') from dual;

将字符串’1982-1-1’转成日期

select to_date('1982-1-1','yyyy-MM-dd') from dual;

16.条件表达式
查询员工的job内容并转成中文显示

select ename,decode(job,'CLERK','柜员','其他') from emp;

或者用case when的方式

select ename,case job when 'CLERK' THEN '柜员'
when 'SALESMAN' then '销售'
else '其他'
end from emp;

17.分组函数
查询部门名称，部门编号，平均工资

select dname,emp.deptno,avg(sal) from dept,emp where dept.deptno=emp.deptno group by emp.deptno,dname;

查询出部门人数大于5的部门

select deptno,count(*) from emp group by deptno having count(*)>5;
--group by 之后再想筛选，必须用having

18.外连接
查询员工编号，姓名，领导编号，领导姓名，包括没有领导的

select e1.empno,e1.ename,e2.empno,e2.ename from emp e1 left join emp e2 on e1.mgr=e2.empno;
or
select e1.empno,e1.ename,e2.empno,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno(+)

查询出所有部门信息（包括没员工的部门）及部门下的员工信息

select * from emp,dept where emp.deptno(+)=dept.deptno;

找到员工表中工资最高的前三名

select rownum,e.* from (select emp.* from emp order by sal desc)e where rownum<=3;
--rownum不能做大于

分页公式

PageNo=1			--第一页
PageSize=3		--每页3条记录
select * from (select rownum r,e.* from (select * from 表名 order by 列名 desc)e)e1 where r>(PageNo-1)*PageSize and r<=PageNo*PageSize;

19.集合运算
查询工资大于1200并且job是SALESMAN(使用intersect)

select * from emp where sal>1200
intersect
select * from emp where job='SALESMAN'

查询工资大于1200或者job是SALESMAN(使用UNION)

select * from emp where sal>1200
union
select * from emp where job='SALESMAN'

求工资大于1200和job是SALESMAN的差集(minus)

select * from emp where sal>1200
minus
select * from emp where job='SALESMAN'

查询出有员工的部门(exists)

select * from dept where exists(select * from emp where dept.deptno=emp.deptno)
--当括号里面的语句为真，前面select才有结果