MySQL基本操作6

视图

视图是从一个表或多个表中导出来的表，它是一种虚拟表，不存放数据，数据是从真实表中获取的，表的结构和数据都依赖于基本表
视图的优点：简化查询语句，安全性，逻辑数据独立性

例：

create table my_goods
(id int primary key,
name varchar(255),
age int
)charset utf8;
insert into my_goods values(1,'小明',18),(2,'小红','20')
select * from my_goods;

6.1.2 创建视图的语法格式

create view 名字 as select.... with check option；

例:create view view_goods as select * from my_goods;
select * from view_goods;

show tables;
创建视图要求用户具有create view权限，视图创建成功后就会在目录下创建一个视图名.frm文件

1.自定义列名称

create view 视图名(自定义字段名1,字段名2..., as select [字段列表] from 表名);
例：create view view_goods_1(id,姓名,年龄) as select * from my_goods;
select * from view_goods_1;

6.2.2 查看视图
1.查看视图的字段信息

desc 视图名;
例:desc view_goods_1;

2.查看视图状态信息

show table status like 视图名;
例:show table status like 'view_goods_1';

3.查看视图的创建语句

show create view 视图名;
例:show create view view_goods_1;

6.2.3 修改视图
1.替换已有的视图
create or replace view view_goods_1 as select * from my_goods;
select * from view_goods_1;
2.
alter view view_goods_1(姓名,年龄) as select name,age from my_goods;
desc view_goods_1;
select * from view_goods_1;
6.2.4 删除视图

drop view 视图名1，视图名2....;
例：drop view view_goods_1,view_goods;
6.3 视图数据操作
insert into view_goods_1 values(3,'小张',21),(4,'小李',21);
insert into view_goods_1 values(5,'小王',18)
select * from view_goods_1;
select * from my_goods;
6.3.2 修改数据
update view_goods_1 set 年龄=20 where id=1;
6.3.3 删除数据
delete from view_goods_1 where id=3;
6.3.4 视图检查条件
create view view_goods_2 as select * from my_goods where age>20 with check option;
insert into  view_goods_2 values(6,'小桃',21)