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