视图
视图是从一个表或多个表中导出来的表,它是一种虚拟表,不存放数据,数据是从真实表中获取的,表的结构和数据都依赖于基本表
视图的优点:简化查询语句,安全性,逻辑数据独立性
例:
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)