##################### 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;
