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 ###############
文章来源于领测软件测试网 https://www.ltesting.net/
