Oracle常用SQL查询

简单查询

select * from dept;
select deptno,dname,loc from dept;

执行算数计算

select e.name|| ‘A’,(e.sal-12)*2,nvl(e.comm,0)+2 from emp e;

处理空值

select e.ename,e.sal*12,e.comm from emp e where e.comm is null;

使用别名定义列

select e.ename 名字,e.sal * 12 year_sal from emp e;

连接多个列

select empno|| ‘+’ ||ename,job,sal from emp;

排序，默认升序asc,降序desc

select * from dept order by deptno [asc];
select * from dept order by deptno desc;
select emp.*,nvl(comm,0) from emp order by nvl(comm,0) desc;

nvl(E1,E2)，E1为null，返回E2,否则返回E1

select avg(comm),avg(nvl(comm,0)) from emp;

分组group by

select e.deptno,max(e.sal) from emp e group by e.deptno;

having子句

select e.deptno,sum(e.sal) from emp e group by e.deptno having sum(e.sal)>9000;

distinct、group by去重

select distinct deptno from emp;
select deptno from emp group by deptno;

case when

select case when sal <= 1000 then ‘一级’
when sal <= 2000 then ‘二级’
when sal <= 3000 then ‘三级’
else ‘四级’
end as grade,empno,ename,job,sal
from emp order by sal;

分页：每页展示pageSize条，当前页pageNumber

select * from
(select rownum,emp.* rown from emp where rownum <=pageSizepageNumber)
where rown >pageSize（pageNumber-1）

between

select * from emp where empno between ‘001’ and ‘002’;
select * from emp where hiredate between to_date(‘2020-01-02’,‘yyyy-mm-dd’) and to_date(‘2022-11-19’,‘yyyy-mm-dd’);

in

select * from emp where emp in(‘001’,‘002’);

not in

select * from emp where emp not in(‘001’,‘002’);

like

select * from emp where ename like ‘王%’;

not like

select * from emp where ename not like ‘王%’;

and

select * from emp where ename like ‘王%’ and ename like ‘李%’;

or

select * from emp where ename like ‘王%’ or ename like ‘李%’;

多表查询

笛卡尔积，很少用到
select * from emp e,dept d;

等值连接

select * from emp e,dept d where e.deptno=d.deptno;

非等值连接

select * from emp e,salgrade s where e.sal between s.losal and s.hisal;

外连接（左外连接，右外连接同理）

select * from emp e left jion dept d on e.deptno=t.deptno;
等价于：
select * from emp e,dept d where e.deptno=d.deptno(+);

自连接

select e.empno,e.ename,e.job from emp e,emp mgr where e.empno=mgr.mgr;

子查询

select * from emp e where e.deptno=(select deptno from dept where deptno=10);
select * from emp e where e.deptno in (select deptno from dept);
select deptno,avg(sal) from emp group by deptno having avg(sal) < (select avg(sal) from emp where deptno=‘10’);