一.视图
概念: 从一张或多张表中抽取的虚拟表,操作虚拟表的CRUD与原表完全一样
优点:
1.简单化,从多张表抽取后,只需要管理虚拟表即可
2.安全性,能看到的只是抽取到虚拟表的字段
3.逻辑独立性
缺点:
1.性能没有提升,简单表会变得复杂
2.修改不方便,有多种情况不能修改虚拟表的
创建视图:
create view myView as
select employee_id,first_name,last_name,salary from t_employees;
select * from myView;
update myView set salary=30000 where employee_id=100;
修改视图:
方式1:创建或修改
CREATE OR REPLACE VIEW myView AS
select employee_id,first_name,last_name from t_employees;
方式2:修改视图
alter VIEW myView AS
select employee_id,first_name,last_name,salary from t_employees;
删除视图
drop view myView;
视图的不可更改性:
create view myView as
select department_id,count(*) from t_employees group by department_id;
update myView set department_id=119 where department_id=10;