組合集總計： 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;
