组合集总计： 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;
