##################### CREATE/ALTER TABLE #######################
alter table table_name drop column column_name ;---drop column
alter 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}]; ----- 创建 SEQUENCE
example:
CREATE SEQUENCE sequence_name INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
select * from user_sequences ;--- 当前用户下记录 sequence 的视图
select sequence_name.nextval,sequence_name.currval from dual;-----sequence 的引用
alter sequence sequence_name INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE; ----- 修改 sequence, 不能改变起始序号
drop sequence sequence_name; ---- 删除 sequence
CREATE [PUBLIC] SYNONYM synonym_name FOR object; ------ 创建同义词
DROP [PUBLIC] SYNONYM synonym_name;---- 删除同义词
CREATE PUBLIC DATABASE LINK link_name USEING OBJECT;---- 创建 DBLINK
select * 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;