oracle培训第二天

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

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

select ename,sal,comm,sal||comm from emp

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)

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 * 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)

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 )

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;

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,键值）

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);

create index emp1_comp_index on emp1(job,deptno)

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';

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;

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;

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;

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动作轻 ，融合

alter index index_t_id invisible;//优化器不可见,索引正常更新
select * from user_indexes//visibility是invisible
alter index index_t_id unusable;
select * from user_indexes //status是unusable

+ 关注