oracle关闭parentkey,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;