oracle培训第二天

  1. 云栖社区>
  2. 博客>
  3. 正文

oracle培训第二天

张振磊 2017-05-06 14:37:50 浏览351
展开阅读全文
1.空值
空值的数据行将对算数表达式返回空值
select ename,sal,comm,sal+comm from emp
select sum(sal),sum(sal+comm) from emp
比较表达式有空值时返回假
select ename,sal,comm from emp where sal >=comm

非空值与空值做||时,null转化为"",合并的数据为varchar2
select ename,sal,comm,sal||comm from emp 
外键值可以为null
空值在where子句里用is null 或 is not null
空值在update或insert时,直接使用null
update emp set comm =null where empno =7788

处理空值的几种函数方法
nvl(expr1,expr2)
select nvl(1,2) from dual;
select nvl(null,2) from dual;
nvl2(expr1,expr2,expr3)
当第一参数不为null时,取第二个参数的值,否则取第三个参数的值
select nvl2(1,2,3) from dual;
select nvl2(null,2,3) from dual;
select ename ,nvl2(comm,sal+comm,sal) from emp;
nullif(expr1,expr2)
当第一个参数和第二个参数一样时返回为空,当两个参数不一样时返回第一个参数,第一个参数不允许为空
select nullif(1,1) from dual
select nullif(1,2) from dual
coalesce(expr1,expr2,...)
返回从左起始,第一个不为空的值,如果所有参数都为空,则返回空值
select coalesce(null,null,1,1) from dual
select coalesce(null,null,null,null) from dual



2.多表连接技术

交叉连接(笛卡尔积)
create table L(id int ,name1 varchar(10));
create table U(id int,name2 varchar(10));
insert into L
values(1,'a');
insert into L
values(2,'b');
insert into L
values(2,'c');
insert into L
values(4,'d');
insert into U
values(1,'A');
insert into U
values(2,'B');
insert into U
values(3,'C');

select * from l cross join u--sql99
select * from l,u--oracle
非等值连接
select empno,ename,sal,grade,losal,hisal from emp,salgrade where sal between losal and hisal
等值连接
内连接
select * from l inner join u on l.id = u.id
select * from l, u where l.id = u.id
外连接(左外,右外,全连接)
select * from l left outer join u on l.id = u.id;
select * from l left join u on l.id = u.id;
select * from l,u where l.id = u.id(+);

select * from l right outer  join u on l.id = u.id;
select * from l right   join u on l.id = u.id;
select * from l,u where l.id(+) = u.id;

select * from l full  join u on l.id = u.id;
自连接
select * from l ,l
select * from l a,l  
自然连接(隐含连接条件,自动匹配连接字段)
select * from emp natural join dept
select * from emp  join dept using(deptno)

select * from l natural join u
alter table l add  A varchar(2);
alter table u add  A varchar(2);
select * from l natural join u

update l set a='A' where id =1;
update u set a='A' where id =1;
select * from l natural join u
select * from l  join u using(id)
select * from l  join u using(id,a)
select l.id from l  join u using(id)
select a from l  join u using(id)


集合运算
create table emp1 as select * from emp where rownum =1
insert into emp1
values(3030,'张振磊','CLERK',7788,sysdate,800,null,10)

union
select * from emp1
union
select * from emp
union all
select * from emp1
union all
select * from emp
intersect
select * from emp1
intersect
select * from emp
minus
select * from emp1
minus
select * from emp



select id,name1 from l
union all
select empno,ename from emp


select id,name1 from l order by name1
union all
select empno,ename from emp order by ename


select id,name1 from l 
union all
select empno,ename from emp order by ename


select id,name1 from l 
union all
select empno,ename from emp order by name1




3.子查询
单行单列子查询(>,<,=,<>,>=,<=)
select ename,sal from emp where sal >(select sal from emp where empno =7788)

多行单列子查询(in,not in,all,any)
select ename from emp where empno in(select mgr from emp  )
select ename from emp where empno not in(select mgr from emp  )
select ename from emp where empno not in(select nvl(mgr,0) from emp  )
select ename ,deptno,sal from emp where sal >all(select avg(sal) from emp group by deptno)
select ename ,deptno,sal from emp where sal >any(select avg(sal) from emp group by deptno)

多行多列子查询
drop table emp1;
create table emp1 as select * from emp;
update emp1 set sal=1600,comm=300 where deptno <>30 and empno =7369;
select * from emp1 where (sal,comm) in(select sal,comm from emp1 where deptno =30) and deptno <> 30



4.布尔运算符 not
select * from emp where empno =7788
select * from emp where not empno =7788
select * from emp where empno !=7788
select * from emp where not empno !=7788

select * from emp where ename like 'S%'
select * from emp where ename not like 'S%'
select * from emp where not ename like 'S%'
select * from emp where not ename not like 'S%'

select * from emp where deptno in(10)
select * from emp where deptno not in(10)
select * from emp where not deptno in(10)
select * from emp where not deptno not in(10)

select * from emp where sal between 1500 and 3000
select * from emp where sal not between 1500 and 3000
select * from emp where not sal between 1500 and 3000
select * from emp where not sal not between 1500 and 3000


select * from emp where comm is null
select * from emp where comm is not null
select * from emp where not comm is null
select * from emp where not comm is not null

select * from emp where exists(select * from dept where deptno = emp.deptno and dname ='SALES')
select * from emp where not exists(select * from dept where deptno = emp.deptno and dname ='SALES')


from 子句使用子查询(也叫内联视图)

select ename,sal,avgsal from emp,(select deptno,avg(sal) avgsal from emp group by deptno ) b
where emp.deptno = b.deptno and emp.sal > b.avgsal

关联子查询
select ename,sal from emp outer where outer.sal > (select  avg(sal) avgsal from emp inner where outer.deptno = inner.deptno )

关联子查询用于update语句
drop table emp1;
create table emp1 as select emp.*,loc from emp,dept where emp.deptno = dept.deptno(+);
select * from emp1;
update emp1 set loc = null;
update emp1 set deptno =99 ,loc ='aaaa' where empno =7788
select * from emp1;
update emp1 set loc =(select loc from dept where deptno = emp1.deptno)
select * from emp1;
update emp1 set loc = null;
update emp1 set deptno =99 ,loc ='aaaa' where empno =7788;
select * from emp1;
update emp1 set loc =(select loc from dept where deptno = emp1.deptno)
where exists(select 1 from dept where deptno = emp1.deptno)
select * from emp1;

关联子查询特殊形式 exists和not exists
select * from emp where exists(select * from dept where deptno = emp.deptno and dname ='SALES')
select * from emp where not exists(select * from dept where deptno = emp.deptno and dname ='SALES')



5.别名
表别名和列别名
select ename 姓名 from emp 员工
select ename 姓 名 from emp 员工
select ename "姓 名" from emp 员工
select * from emp where rownum >1
select * from (select ename,rownum rm from emp )  where rm >1

6.索引
B树索引 根节点块,分支节点块,叶子节点块(rowid,键值)
位图索引(离散度低,男女 1,2)
索引是与表相关的一个可选结构,在逻辑上和物理上都独立于表的数据,索引能优化查询,不能优化DML操作。oracle自动维护索引,频繁的DML操作反而会引起大量的索引维护


常用的B树索引类型
唯一或非唯一索引(unique or  nounique),唯一索引指键值不重复
drop table emp1;
create table emp1 as select * from emp;
update emp1 set empno = 7788 where empno =7900

create unique index emp1_empno_index on emp1(empno);
update emp1 set empno =7900 where empno =7788 and ename <>'SCOTT';
create unique index emp1_empno_index on emp1(empno);
update emp1 set empno = 7788 where empno =7900

或者
drop index emp1_empno_index;
create index emp1_empno_index on emp1(empno);


组合索引(composite)绑定了两个以上列的索引

create index emp1_comp_index on emp1(job,deptno)

反向键索引(reverse)将字节倒置后组建键值,当使用序列产生主键索引时,可以防止叶节点出现热快现象
create index emp1_mgr_index on emp1(mgr) reverse

函数索引,以索引列值的函数值去组织索引

create index emp1_fun_index on emp1(lower(ename)) 
select * from emp1 where lower(ename)='scott';
压缩,重复键值只存储一次,重复的键值在页块中只存储一次,后面跟所有与之匹配的rowid字符串

create index emp1_sal_index on emp1(sal)  compress


升序或降序,叶节点中的键值排序默认是升序的
create index emp1_deptjob_index on emp1(deptno desc,job asc)

select * from user_indexes
select * from user_ind_columns


优化器使用索引的扫描方式
drop table emp1;
create table emp1 as select * from emp;

索引唯一扫描。通过唯一索引查找一个数值返回单个rowid。对于唯一组合索引,要在where的谓词“=”后包含所有列的“布尔与”
create unique index index_emp1_empno on emp1(empno);
select * from emp1 where empno =7788;
create index index_emp1_deptnoename on emp1(deptno,ename);
select * from emp1 where deptno =20 and ename ='SCOTT';
select * from emp1 where ename ='SCOTT' and deptno =10;
select * from emp1 where ename ='SCOTT';
select * from emp1 where deptno =20;

索引范围扫描。在非唯一索引上可能返回多行数据。所以在非唯一索引上都使用索引范围扫描。
a)在唯一索引列上使用了range操作符(>,<,>=,<=,between)
select * from emp1 where empno >7788;
select * from emp1 where empno <>7788;
b)在唯一组合索引上,对组合索引使用部分列进行查询(含引导列),导致查询出多行
select * from emp1 where deptno =20;
c)对非唯一索引列上进行的任何查询。不含 布尔或
create index index_emp1_sal on emp1(sal);
select * from emp1 where sal =800;

索引全扫描
对整个index进行扫描,并且顺序的读取其中的数据
select empno  from emp1;
alter table emp1 modify empno not null;
select empno  from emp1;
索引快速扫描
扫描索引中的所有数据块
drop table emp1;
create table emp1 as select * from emp;
alter table emp1 modify empno not null;
create index index_emp1_empno on emp1(empno);
insert into emp1 select * from emp1;
select empno from emp1;

show parameter multi;



索引的碎片问题
聚簇因子:堆表的表行物理的存储在数据块是无序的,这与插入一行记录首选空闲快的策略有关。而索引的键值又是有序的。当这两者差异越大,聚簇因子的值就越高。


由于对基表做dml操作,便导致对索引表块的自动更改操作,尤其是基表的delete操作会引起index的逻辑删除。只有当index块中的所有index entry都被删除了,这个块才能够被回收,
如果update基表索引列,则索引块会发生entry delete,再entry insert,这些动作都可能产生索引碎片。

create table t (id int);
create index index_t_id on t(id);
begin
  for i in 1..1000000 loop
      insert into t values(i);
      if mod(i,100) = 0 then
         commit;
       end if;
  end loop;
end;

analyze index index_t_id validate structure;
select name,height,pct_used,del_lf_rows/lf_rows from index_stats
delete from t where id < 700000;
analyze index index_t_id validate structure;
select name,height,pct_used,del_lf_rows/lf_rows from index_stats
当下列三种情形之一发生时,说明积累的碎片应该整理了
1.height >=4
2.pct_used < 50%
3 delete_lf_rows/lf_rows >0.2

alter index index_t_id rebuild;//重建
analyze index index_t_id validate structure;
select name,height,pct_used,del_lf_rows/lf_rows from index_stats;

alter index index_t_id coalesce//比rebuild动作轻 ,融合
索引不可见(invisible)
alter index index_t_id invisible;//优化器不可见,索引正常更新
select * from user_indexes//visibility是invisible
alter index index_t_id unusable;
select * from user_indexes //status是unusable

网友评论

登录后评论
0/500
评论
张振磊
+ 关注