12.mysql 基础之视图

定义视图是封装了基表的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_v1asselect id,name,saraly from emploee创建视图方式二create or replace view emp_v2(id,name_v2,sal_v2)asselect id,name,saraly from emploee创建视图 :多表查询create or replace view emp_v3asselect e.id,e.name,e.saraly,d.dept_name from emploee e join departments d one.idd.dept_id查看视图desc emp_v3视图插入数据insert into emp_v1values(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_v1asselect id,name,saraly from emploee where saraly 1000修改视图方式二alter view emp_v1asselect id,name,saraly from emploee where saraly 9000删除视图drop view emp_v1修改失败情况create or replace view emp_v4(dept_id,max_salay)asselect department_id ,max(saraly) from emploee4 group by department_idinsert into emp_v4 values(70,8000) #原表中没有最大薪资字段注1 对视图修改数据相当于对基表修改数据视图按要求显示字段可以用来控制权限2小的项目直接对表操作复杂的项目才考虑视图 而且如果表字段经常变更维护成本很高