oracle的查询、视图、索引

所有表来自oracle的scott用户的默认表

一、查询

1.1分组查询

查询平均工资大于2000的部门
t.deptno 部门
t.sal 工资

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

1.2多表查询

1.2.1 内连接

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

1.2.2 外连接

--左外连接
select * from emp e left join dept d on e.deptno=d.deptno;
select * from emp e, dept d where e.deptno(+)=d.deptno;
--右外连接
select * from emp e right join dept d on e.deptno=d.deptno;
select * from emp e, dept d where e.deptno=d.deptno(+);

1.2.3 自连接

select e1.ename as 员工,e2.ename as 上级
from emp e1,emp e2
where e1.mgr=e2.empno;

1.2.4子查询

1.如果返回一个值则使用where ？=(sql)
2.返回一个列表则使用 where ? in (sql)
3.返回一张表

-- 查询部分最低工资，最低工资员工姓名，所在部门名字
select t.msal,e.ename,d.dname
from (select deptno,min(sal) msal
      from emp
      group by deptno) t,emp e,dept d
where t.msal=e.sal
and t.deptno=d.deptno;

1.2.5分页查询

oracle的分页查询依靠rownum,
select每查询一条记录就会加个rownum,
rownum初始为1，依次递增，不能跳着走；
当查询时加入order by的时候就可能导致rownum错乱
比如:

select rownum,e.* from emp e order by sal desc;

结果集：

select 先执行完了再排序的，所以，rownum乱了

分页查询模板：

select * from
      (select rownum rn,e1.* from
             (select e.* from emp e order by sal desc) e1 
      where rownum <10)
where rn>5;

二、视图

2.1创建视图

create view v_emp as select  ename,sal from emp;
--只读视图
create view v_emp as select  ename,sal from emp with read only;

2.2查询视图

select * from v_emp;

2.3修改视图内容

修改后对应表的值也会变

update v_emp set sal=1000.00 where ename='SMITH'

三、索引

索引就是在表的列上构建一个二叉树，达到大幅度提高查询效率的目的，但是索引影响增删改的效率

3.1单列索引

3.1.1创建单列索引

create index index_ename on emp(ename);

3.1.2触发单列索引

触发规则是，查询条件必须是索引中的原始值,比如模糊查询和单行函数都会影响索引触发；
而且在or语句中，如果不包含触发原始值的条件则不触发索引；

select * from emp where ename='SCOTT'; ---触发 表中有ename=SCOTT
select * from emp where ename='abc'; ---不触发 表中无ename=abc
select * from emp where ename='SCOTT' or sal=1000; ---不触发

3.2多列索引

3.2.1创建多列索引

create index index_ename_sal on emp(ename,job);

3.2.1触发多列索引

优先检索列：索引的第一列
触发规则:查询条件必须包含优先检索列中的原始值

select * from emp where ename='SCOTT' and sal=1234; --触发多列索引
select * from emp where ename='abc' and sal='asx'; --不触发多列索引  表中无ename=abc
--表中既有单列索引又有多列索引时
select * from emp where ename='SCOTT';  --触发单列索引