22-Oracle入门之总结

重要知识点：

基本语法

select a, b, c
from tab1, tab2

where col = 10
where col > 10 
where col between 1000 and 2000
where col in (10, 20, null)
wehre col not in (10, 20, null)
order by  col1 asc  , col desc
order by  别名 表达   3 
order by nulls last
group by a, b, c 
having ....


字符串-->数字
select '111' + 222 from dual ;  ===>333
数字--->字符串
select '111' || 222 from dual ; ===> 111222

字符串-->日期




日期--->字符串


select * 
from emp
where to_char(hiredate, 'yyyy-mm-dd') > '1981-01-01'


select * 
from emp
where hiredate >  to_date('1981-01-01','yyyy-mm-dd' ) ;


select * 
from emp
where hiredate >   '1-1月 -81'

--查询员工的薪水:两位小数 本地货币代码 千位符 


select ename, to_char(sal, 'L9,999.99')
from emp ;

￥1,600.00 
select to_number('￥1,600.00 ', 'L9,999.99')  from dual 
 
 
-----外连接 eg:按部门统计员工人数: 部门号 部门名称 各部门人数


1 10 20 30 分组
2 多表
3 count() 组函数


步骤1
select d.deptno, d.dname, count(e.empno)
from dept d, emp e
where d.deptno = e.deptno 
group by d.deptno , d.dname

步骤2
select d.deptno, d.dname, count(e.empno)
from dept d, emp e
where  e.deptno (+) = d.deptno
group by d.deptno , d.dname
order by 1

常见操作

1 求所有部门的平均奖金
select avg(nvl( comm, 0) ) from emp;


select avg(sal) from emp;




2 求各部门的平均薪水


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




====>错误案例
select deptno, ename, avg(sal)
from emp
group by deptno
/




select d
from emp
group by a, b, c





3 求各部门每个工种的平均薪水, 并显示部门编号、工种、平均薪水


select deptno, job, avg(sal)
from emp
group by deptno, job  -- 先按deptno、job分组，出来n组数据，在这个基础之上，再进行计算



4 求各部门每个工种大于2000的薪水
select sal
from emp
where sal>2000


4-1 求各部门每个工种，平均薪水大于2000的薪水


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




5 求10号部门的平均工资(2种写法)
select deptno, avg(sal)
from emp
where deptno=10
group by deptno


select deptno, avg(sal)
from emp
group by deptno
having deptno = 10

6 创建一个学生表
sid
sname
email
sex
age
7 并向表中插入一条数据


create table student
(
sid number,
sname varchar2(20),
email varchar2(64),
sex number,
age number
)
insert into student(sid, sname, email, sex, age) values(1, 'tom11', '[e-mail]', 1, 20) 






//只显示大于4个人的部门信息


select deptno, count(*)
from emp
group by deptno
having count(deptno) > 4
/








-- 创建表 


-- 1 查询 员工号 姓名 月薪 年薪 年收入 部门名称


select e.deptno, e.ename, e.sal, e.sal*12 yearsal, (e.sal*12+nvl(e.comm,0)) incoming , d.dname
from emp e, dept d
where e.deptno = d.deptno 


====>




---2 创建一个表, 把第一步骤查询的信息,存放在另外一张表中
create table s1
as 
(
select e.deptno, e.ename, e.sal, e.sal*12 yearsal, (e.sal*12+nvl(e.comm,0)) incoming , d.dname
from emp e, dept d
where e.deptno = d.deptno 
)


create view myview1
as 
(
select e.deptno, e.ename, e.sal, e.sal*12 yearsal, (e.sal*12+nvl(e.comm,0)) incoming , d.dname
from emp e, dept d
where e.deptno = d.deptno 
)



create table empincome
as


select e.empno, e.ename, e.sal, e.sal*12 annalsal, sal*12+nvl(comm, 0) income, d.dname
from emp e, dept d
where e.deptno = d.deptno;


--创建表的同时copy数据

常见面试题：

1
select empno, ename, sal
from emp
order by sal desc


2
select rownum, empno, ename, sal
from emp


   ROWNUM      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
        1       7369 SMITH             800
        2       7499 ALLEN            1600
        3       7521 WARD             1250
        4       7566 JONES            2975
        5       7654 MARTIN           1250
        6       7698 BLAKE            2850
        7       7782 CLARK            2450
        8       7788 SCOTT            3000
        9       7839 KING             5000
       10       7844 TURNER           1500
       11       7876 ADAMS            1100
       
       12       7900 JAMES             950
       13       7902 FORD             3000
       14       7934 MILLER           1300


已选择14行。


select rownum, empno, ename, sal
from emp
order by sal desc

select rownum, empno, ename, sal
from emp
where rownum <= 3
order by sal desc


oracle的ROWNUM机制
1  ROWNUM就是这么设计 rownum是属于服务器返回的结果集的固有属性
2 where rownum<=3 不能使用  where rownum>=3
生成结果集是1行1行的生成的. 没有第1行 就没有第2行
没有第2行 就没有第3行  没有第3行 就没有第4行

=====>Top-N


select * from
(select empno, ename, sal
from emp
order by sal desc)
/


select rownum, empno,  ename, sal from
(select empno, ename, sal
from emp
order by sal desc)
where rownum<=3




=======分页问题


1
select rownum, empno,  ename, sal from
(select empno, ename, sal
from emp
order by sal desc)
where rownum>=5 and rownum<=8




select   r, empno, ename, sal
from 


(
select rownum r, empno,  ename, sal from
(select empno, ename, sal
from emp
order by sal desc)
where rownum<=8
)


where r>=5


解决分页的思路
总的原则: 内层:排序 外层选...
（内层排序 外层选，需要三层查询） 
内：排序。
中：使用rownum选择前n条；并给rownum指定一个别名，以供最外层过滤使用。
外：去掉前m条结果。





查找 员工表中 薪水 大于 本部门平均薪水 的员工。


第一种方法 多表查询
分析1 10 20 30 要分组
select deptno, avg(sal) avgsal
from emp
group by deptno
分析2 可以把部门平均薪水的数据 看成一个表   多表查询


分析3 组函数

分析4 连接条件

分析5 别名

select e.empno, e.ename, e.sal , d.avgsal
from emp e , 
(select deptno, avg(sal) avgsal
from emp
group by deptno) d

where e.deptno=d.deptno and e.sal > d.avgsal  


第二种方法  相关子查询 
子查询分为2种 一般子查询 
相关子查询 

找到员工表中 薪水 大于 本部门平均薪水 的员工。


1
select empno, ename, sal, (员工所在部门的平均工资) avgsal
from emp
where  sal >  (员工所在部门的平均工资) avgsal


员工所在部门的平均工资==>
select avg(sal) avgsal from emp where deptno = 10 

2


select e.empno, e.ename, e.sal, ( select avg(sal) avgsal from emp where deptno = e.deptno) avgsal
from emp e
where  sal >  (select avg(sal) avgsal from emp where deptno =  e.deptno )

===总结 主查询 通过别名  把主查询的值 传递给 子查询 ..