1).创建没有分组函数的视图?
create view empview
as
select employee_id,last_name,salary
from employees
where employee_id = 80
视图的数据的增删改查操作与表相同,并且视图的数据的增删改查会在基表中体现
2).创建包含分组函数的视图?
分组函数必须取名字
create or replace view empview3
as
select department_name,avg(salary) avg_sal
from employees e , departments d
where e.department_id = d.department_id
group by department_name
3).屏蔽视图的 DML 操作?
create or replace view empview2
as
select employee_id,last_name,department_name
from employees e,departments d
where e.department_id = d.department_id
with read only
4).如何修改视图?
修改前准备:
create view empview2
as
select employee_id,last_name,salary,department_name
from employees e,departments d
where e.department_id = d.department_id
修改视图:
create or replace view empview2
as
select employee_id,last_name,department_name
from employees e,departments d
where e.department_id = d.department_id
5).如何删除视图?
drop view empview3;
6).什么情况下使用视图?
1.不希望访问者获取整个表的信息,只暴露部分字段给访问者,所以就建一个虚表,就是视图。
2.查询的数据来源于不同的表,而查询者希望以统一的方式查询,这样也可以建立一个视图