oracle培训第二天

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