Oracle Select SQL语句总结

alter user scott accountunlock;    conn scott/tiger

select * from dual;

select sysdate from dual;

select ename,sal*12 annual_sal from emp;--(annual_sal →ANNUAL_SAL)

select ename,sal*12 "annualsal" from emp;--(双引号保持原来格式：小写)

--含有空值的数学表达式计算后都是空值。

select ename||sal from emp;

select ename||'abc''def' from emp;--(两个单引号替换成一个单引号)

select distinct deptno from emp;

select distinct deptno,job from emp;--(deptno,job重复组合的值去掉)

WHERE

select ename,sal from emp where sal between 800 and 1500;
select ename,sal from emp where sal >= 800 and sal<= 1500;

select ename,sal,comm from emp where comm is null;

select ename,sal,comm from emp where comm is not null;

select ename,sal,comm from emp where sal in (800,1500,2000);

select ename,sal,comm from emp where ename in('SMITH','KING','ABC');

select ename,sal,hiredate from emp where hiredate >'20-2月-81';
select ename,sal,hiredate from emp where hiredate >'20-2月-1981';--(特定的格式)

select ename,sal from emp where sal not in (800,1500,2000);

select ename from emp where ename like '%ALL%';--(%:0个或者多个)

select ename from emp where ename like '_A%';--(_:一个字母)

select ename from emp where ename like '%\%%';--(默认转义字符)
select ename from emp where ename like '%$%%' escape '$';--(自定义转义字符)

Function

select lower(ename) from emp;

select substr(ename,2,3) from emp;--(从第二个字符开始截，一共截3个字符)

select chr(65) from dual;--(ASCII码:A)

select ascii('A') from dual;

select round(23.652) from dual;--(四舍五入)

select round(23.652,2) from dual;--(四舍五入：23.65)

select round(23.652,-1) from dual;--(四舍五入：20)

select to_char(sal,'$99,999.9999')from emp;--(9:一位数字)

select to_char(sal,'L99,999.9999')from emp;--(L:本地货币)

select to_char(sal,'L00,000.0000') from emp;--(0:强制显示)

select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;

select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;

select ename,hiredate from emp
  where hiredate > to_date('1981-2-20 12:34:56','YYYY-MM-DDHH24:MI:SS');

select sal from emp where sal > to_number('$1,250.00','$9,999.99');

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

GruopFunction 重要(一个都不能忘)

组函数(多个记录产生一个输出)

select max(sal) from emp;

select min(sal) from emp;

select avg(sal) from emp;

select round(avg(sal),2) fromemp;

select sum(sal) from emp;

select count(*) from emp;

select count(comm) from emp;(非空值)

select count(distinct deptno) from emp;

Group by

select deptno,avg(sal) from emp group by deptno;

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

select字段必须在组函数里面或group by后面。

where是对单条数据进行过滤，having是对分组进行限制。

select avg(sal),deptno from emp group by deptno having avg(sal)> 2000;

执行顺序

select * from emp
  where sal > 1000
  group by deptno
  having
  order by;

select avg(sal)
from emp
where sal > 1200
group by deptno
having avg(sal) > 1500
order by avg(sal) desc;

把子查询当作一张表

select ename,sal from emp where sal = (select max(sal) from emp);

select ename,sal from emp where sal > (select avg(sal) from emp);

--select ename,sal,deptno from emp where sal in (select max(sal) from emp group by deptno);

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

select ename,sal from emp
join (select max(sal) max_sal,deptno from emp group by deptno)t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);

select avg(sal),deptno from emp group by deptno;

select grade,t.deptno from salgrade
join (select avg(sal) avg_sal,deptno from emp group by deptno)t
on (t.avg_sal between salgrade.losal and salgrade.hisal);

self table connection

select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr =e2.empno;

SQL1999

select ename,dname from emp,dept;
select ename,dname from emp cross join dept;--(交叉链接，笛卡尔乘积)

on:链接条件  where:过滤条件

select ename,dname from emp,dept where emp.deptno =dept.deptno;
select ename,dname from emp join dept on (emp.deptno =dept.deptno);
select ename,dname from emp join dept using(deptno);--(等值链接，不推荐使用)

select ename,grade from emp e join salgrade s on(e.sal betweens.losal and s.hisal);

select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr =e2.empno);
select e1.ename,e2.ename from emp e1 left (outter) join emp e2 on(e1.mgr = e2.empno);
--左外链接会把左边这张表不能连接的数据拿出来

select ename,dname from emp e right (outter) join dept d on(e.deptno = d.deptno)
--右外链接会把右边这张表不能连接的数据拿出来

select ename,dname from emp e full join dept d on (e.deptno =d.deptno);

练习

--求部门平均薪水的等级
select deptno,avg_sal,grade from
(select deptno,avg(sal) avg_sal from emp group by deptno) t
join salgrade s on (t.avg_sal between s.losal and s.hisal);

--求部门平均的薪水等级
select deptno,avg(grade) from
(select deptno,ename,grade from emp join salgrade s on (emp.salbetween s.losal and s.hisal)) t
group by deptno;

--雇员中有哪些人是经理
select distinct e2.ename from emp e1, emp e2 where e1.mgr =e2.empno;
select ename from emp where empno in (select distinct mgr fromemp);

--不用组函数求最高薪水
select ename,sal from emp where sal = (select max(sal) fromemp);
select ename,sal from emp where sal = (select sal from emp);

select sal from emp where sal not in
(select distinct e1.sal from emp e1 join emp e2 on (e1.sal< e2.sal));

--平均薪水最高的部门编号(组函数嵌套 embedded group function)
select deptno,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal =
(select max(avg(sal)) from emp group by deptno);

VIEW
视图就是一个子查询或是一张表

create view v$_dept_avg_sal as
select avg(sal) avg_sal,deptno from emp group by deptno;

select * from v$_dept_avg_sal;