Oracle学习笔记(五)

组合集总计： 
group by with rollup/cube 
grouping sets 
子查询按执行方式分：标准子查询、关联子查询 
标准子查询：嵌套子查询 
标量值查询 
case with then 
使用exits查询 
select user_id,user_name from tb_001 tb 
where [not] exists (select 'x' 
from tb_001 
where manager_id=tb.user_id ) 
关联更新 
update table1 alias1 
set column=(select expression 
from table2 alias2 
where alias1.column=alias2.column); 
关联删除 
delete from table1 alias11 
where column operator 
(select expression 
from table2 alias2 
where alias1.column=alias2.column); 
分层结构 
分层检索 
select [level],column,expr... 
from table 
[where with condition(s)] 
[connect by prior condition(s)] 
connect by prior column1=column2 
top down: 
colum1=parentkey 
column2=childkey 
bottom up: 
column1=childkey 
column2=parentkey 

插入语句insert statement 
insert into table[(column[,column...])] 
values(value[,value...]) 
修改语句update statement 
update table 
set column=value[,column=value,...] 
[where condition]; 

多表插入语句(Multitable Insert Statements) 

insert [all] [conditional_insert_clause] 
[insert_into_cause values_clause](subquery) 
conditional_insert_clause 
[all][first] 
[when condition then][insert_into_clause values_clause] 
[else][insert_into+clause values_clause] 
列： 
intsert all 
into tb_user values(id,uid,upwd) 
into tb_user_info values(id,name,sex,age) 
select id,uid,upwd,name,sex,age 
from tb_u_10 
where id>10000; 
待条件的多行插入 
insert all 
when id>11000 then 
into tb_user values(id,uid,upwd) 
when age>40 then 
into tb_user_info values(id,name,sex,age) 
select id,uid,upwd,name,sex,age 
from tb_u_10 
where id>10000; 
insert first 
when conditional then 
into ... 
when conditional then 
into ... 
else 
into... 
select... 
创建索引 
cretate table t_10 
(id number(6) not null primary key using index (create index index1 on t_10(id)), 
name varchar2(50)); 
查询索引 
select * from user_indexes;