oracle dba 常用語句9(table,view,sequence)

##################### CREATE/ALTER TABLE #######################alter table table_name drop column column_name ;---drop columnalter table table_name set unused (col1,col2,...);---- 設定列無效，這個比較快。alter table table_name drop unused columns;--- 刪除被設為無效的列rename table_name1 to table_name2; --- 重新命名表comment on table table_name is 'comment message';---- 給表放入注釋資訊create table table_name(col1 int not null,col2 varchar2(20),col3 varchar2(20),constraint uk_test2_1 unique(col2,col3))); ----- 定義表中的約束條件alter table table_name add constraint pk_test2 primary key(col1,col2,...); ---- 建立主鍵/* 建立外鍵 */create table table_name (rid int,name varchar2(20),constraint fk_test3 foreign key(rid) references other_table_name(id));alter table table_name add constraint ck_test3 check(name like 'K%');alter table table_name drop constraint constraint_name;alter table table_name drop primary key cascade;---- 串聯刪除主鍵alter table table_name disable/enable constraint constraint_name;---- 使約束暫時無效/* 刪除列，並串聯刪除此列下的約束條件 */alter table table_name drop column column_name cascade constraint;select * from user_constraints/user_cons_columns;--- 約束條件相關視圖############## Create Views #####################CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[,alias]...)]AS subquery[WITH CHECK OPTION [CONSTRAINT constraint_name]][WITH READ ONLY [CONSTRAINT constraint_name]]; ------ 建立視圖的文法example: Create or replace view testview as select col1,col2,col3 from table_name; ------ 建立視圖/* 使用別名 */Create or replace view testview as select col1,sum(col2) col2_alias from table_name;/* 建立複雜視圖 */Create view view_name (alias1,alias2,alias3,alias4) as select d.col1,min(e.col1),max(e.col1),avg(e.col1) from table_name1 e,table_name2 d where e.col2=d.col2 group by d.col1;/* 當用 update 修改資料時，必須滿足視圖的 col1>10 的條件，不滿足則不能被改變 .*/Create or replace view view_name as select * from table_name where col1>10 with check option;/* 改變視圖的值 . 對於簡單視圖可以用 update 文法修改表資料，但複雜視圖則不一定能改。如使用了函數， group by ,distinct 等的列 */update view_name set col1=value1;/*TOP-N 分析 */select [column_list],rownum from (select [column_list] from table_name order by Top-N_column) where rownum<=N;/* 找出某列三條最大值的記錄 */example: select rownum as rank ,col1 ,col2 from (select col1 ,col2 from table_name order by col2 desc) where rownum<=3;############# Other database Object ###############CREATE SEQUENCE sequence_name [INCREMENT BY n][START WITH n][{MAXVALUE n | NOMAXVALUE}][{MINVALUE n | NOMINVALUE}][{CYCEL | NOCYCLE}][{CACHE n | NOCACHE}]; ----- 建立 SEQUENCEexample:CREATE SEQUENCE sequence_name INCREMENT BY 10START WITH 120MAXVALUE 9999NOCACHENOCYCLE;select * from user_sequences ;--- 目前使用者下記錄 sequence 的視圖select sequence_name.nextval,sequence_name.currval from dual;-----sequence 的引用alter sequence sequence_name INCREMENT BY 20MAXVALUE 999999NOCACHENOCYCLE; ----- 修改 sequence, 不能改變起始序號drop sequence sequence_name; ---- 刪除 sequenceCREATE [PUBLIC] SYNONYM synonym_name FOR object; ------ 建立同義字DROP [PUBLIC] SYNONYM synonym_name;---- 刪除同義字CREATE PUBLIC DATABASE LINK link_name USEING OBJECT;---- 建立 DBLINKselect * from object_name@link_name; ---- 訪問遠端資料庫中的對象/*union 操作，它將兩個集合的交集部分壓縮，並對資料排序 */select col1,col2,col3 from table1_name union select col1,col2,col3 from table2_name;/*union all 操作，兩個集合的交集部分不壓縮，且不對資料排序 */select col1,col2,col3 from table1_name union all select col1,col2,col3 from table2_name;/*intersect 操作，求兩個集合的交集 , 它將對重複資料進行壓縮，且排序 */select col1,col2,col3 from table1_name intersect select col1,col2,col3 from table2_name;/*minus 操作，集合減 , 它將壓縮兩個集合減後的重複記錄 , 且對資料排序 */select col1,col2,col3 from table1_name minus select col1,col2,col3 from table2_name;/*EXTRACT 抽取時間函數 . 此例是抽取當前日期中的年 */select EXTRACT(YEAR FROM SYSDATE) from dual;/*EXTRACT 抽取時間函數 . 此例是抽取當前日期中的月 */select EXTRACT(MONTH FROM SYSDATE) from dual;