赞
踩
视图的含义:虚拟表,从数据库中的一个或多个表中导出来的表,(面试题)对视图看到的数据进行修改时,相应的基本表的数据也会发生改变。基本表数据变化,也会反映到视图中。
视图的作用:简单、安全性高、逻辑数据独立、可以让视图显示的都是我们需要的数据。
语法:create [or replace] [algorithm = {undefined | merge | temptable}] VIEW view_name [(column_list)] as select_statement [with [cascaded | local] check option]
建表:create table t(quantity int,price int);
添加数据:insert into t values(3,50);
创建视图:create view view_t AS select quantity,price,quantity*price from t;
查看视图:select * from view_t;
例2:为查询出的结果列名取别名
create view view_t2(qty,price,total)AS select quantity,price,quantity*price from t;
create view view_t3(id,name,glass)AS
select student.id,student.name,stu_info.glass
from student,stu_info where student.id=stu_info.id;
基本语法:describe 视图名;
*describe可简写为desc
基本语法:show table status like '视图名';
基本语法:show create view 视图名;
使用select语句进行查看
如:用表2中的数据取代view_t视图中的数据
create or replace view view_t as select * from 表2;
如:用t表中的quantity列,取代view_t视图中原来的数据。
alter view view_t as select quantity from t;
INSERT UPDATA DELETE
如:给view_t2增加一条记录
insert into view_t2 values(3,5,15);
如:将quantity字段值都改为5
updata view_t2 set quantity=5;
如:将price=5的记录删除
delete from view_t2 where price=5;
语法drop view [if exists] view_name [,view_name···]
如:删除view_t1视图
drop view view_t1;
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。