视图

#建表语句 财富表
create table t_wealthy(
 id int primary key auto_increment,
 wname varchar(20),
 wage int,
 wmoney int
);
#插入测试数据
insert into t_wealthy values(1,'张三',20,110);
insert into t_wealthy values(2,'李四',35,110);
insert into t_wealthy values(3,'王五',35,90);
insert into t_wealthy values(4,'赵六',20,90);
#创建视图v_age,查询所有人的姓名、年龄、身价
create view v_age
as
select * from t_wealthy;
#向视图中添加测试数据
insert into t_wealthy(wname,wage,wmoney) values ('23',25,100);
#修改视图v_age,查询年龄小于30的姓名、年龄、身价，并添加with check option
alter view v_age
as
select * from t_wealthy w
where w.wage<30 with check option;
#向视图中添加测试数据
insert into t_wealthy(wname,wage,wmoney) values ('23',25,100);
#将视图中赵六的金额改为99
update v_age set wmoney=99 where wname='赵六';
#在视图v_age的基础上创建v_money视图,要求身价大于100
create view v_money
as
select * from v_age where wmoney>100;
#将视图中张三的金额改为99
update v_age set wmoney=99 where wname='张三';
#将视图中张三的金额改为199
update v_age set wmoney=199 where wname='张三';
#删除所有视图  drop view 视图1，视图2;
drop view v_age;
drop view v_money;