程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> 關於Oracle數據庫 >> Oracle學習筆記(五)

Oracle學習筆記(五)

編輯:關於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;
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved