定义:视图是封装了基表的select语句,不是真实存储数据。
创建表:
create table emploee
(
id int primary key,
name varchar(20),
gender char(1),
saraly decimal(10,2),
department_id int,
manger_man varchar(10)
)
create table departments
(dept_id int primary key,
dept_name varchar(20)
)
创建视图方式一:
create or replace view emp_v1
as
select id,name,saraly from emploee
创建视图方式二:
create or replace view emp_v2(id,name_v2,sal_v2)
as
select id,name,saraly from emploee
创建视图 :多表查询
create or replace view emp_v3
as
select e.id,e.name,e.saraly,d.dept_name from emploee e join departments d on
e.id=d.dept_id
查看视图:
desc emp_v3
视图插入数据:
insert into emp_v1
values(106,'jack',5000),
(107,'rose',8000)
视图修改数据:
update emp_v1 set saraly =5500 where id =101
视图删除数据:
delete from emp_v1 where id =101
视图查询数据:
select * from emp_v1
修改视图方式一:
create or replace view emp_v1
as
select id,name,saraly from emploee where saraly >1000
修改视图方式二:
alter view emp_v1
as
select id,name,saraly from emploee where saraly <9000
删除视图:
drop view emp_v1
修改失败情况:
create or replace view emp_v4(dept_id,max_salay)
as
select department_id ,max(saraly) from emploee4 group by department_id
insert into emp_v4 values(70,8000) #原表中没有最大薪资字段
注:1) 对视图修改数据,相当于对基表修改数据,视图按要求显示字段,可以用来控制权限
2)小的项目直接对表操作,复杂的项目才考虑视图, 而且如果表字段经常变更,维护成本很高