本文详细介绍了MySQL中的视图概念，包括视图的创建、查看、使用、修改和删除。视图是一种虚拟表，不存储数据，只基于基表结构。视图有助于简化复杂的查询，提高数据安全性，并便于权限控制。虽然视图可以进行数据操作，但多表视图不能新增或删除数据，而单表视图则可以在一定程度上进行这些操作。视图的算法包括undefined、temptable和merge，通常推荐使用merge以提高效率。
一、视图
1、视图：view。视图是一种有结构（有行有列的二维表结构）但没结果（结构中不真实存放数据）的虚拟表。虚拟表的结构来源不是自己定义的，而是从对应的基表中产生（视图的数据来源）
注：虚拟表的数据也不是从自己来的
二、创建视图
1、基本语法
-- 创建视图
-- select语句：可以是 普通查询|连接查询|联合查询|子查询
create view 视图名字 as select 语句;
（1）单表视图：基表只有一个
（2）多表视图：基表来源至少是两个
注：多表视图（基表有多张）的字段名不能重复。可以进行别名命名
-- 创建视图：单表视图
create view my_v1 as
select * from my_student;
-- 创建视图：多表视图
create view my_v2 as
select * from my_student as s left join my_class c on c.id = s.c_id;
-- 报错：Duplicate column name 'id'，即 id重复（表中不允许出现同名字段）
-- 多表视图（基表有多张）的字段名不能重复（可以进行别名命名）
create view my_v2 as
select s.*, c.name as c_name, c.room from my_student as s
left join my_class c on c.id = s.c_id;
2、视图一旦创建，系统会在视图对应的数据库文件夹下创建一个对应的结构文件xxx.frm（视图不影响基表的存在，视图只是基于基表出现的东西）
注：视图名和表名不能重复。因为产生的都是xxx.frm结构文件
三、查看视图
1、视图是一张虚拟表，表的所有查看方式都适用于视图
-- 查看所有视图/表
show tables;
show tables like '视图名的模糊查询';
show views; -- 报错
-- 查看视图结构
desc 视图名;
-- 查看视图创建语句
show create table 视图名 [\G];
show create view 视图名 [\G];
四、使用视图
1、使用视图主要是为了查询数据，将视图当做表一样查询即可（视图不保存结果，但可以从基表拿到数据）
-- 使用视图，查询数据
-- 视图不保存结果，但可以从基表拿到数据
select * from my_v1;
2、视图的执行，其本质就是执行封装的select语句
五、修改视图
1、视图本身不可修改，但视图的来源是可以修改的。修改视图，就是修改视图本身的来源语句（select语句）
2、基本语法
-- 修改视图
alter view 视图名 as 新的select语句;
-- 创建视图
create view my_v3 as
select * from my_student;
-- 查看视图结构
desc my_v3;
-- 修改视图
alter view my_v3 as
select * from my_class;
-- 查看视图结构
desc my_v3;
六、删除视图
1、基本语法
-- 删除视图
drop view 视图名字;
-- 删除视图
drop view my_v3;
drop table my_v3; -- 报错
-- 表可以存数据，视图不存数据
-- 所以，删除视图是只删除结构，而删除表table会找到table删除表中的数据
-- 但实际上没有my_v3这张表来存储数据
2、视图可以随意删除，因为视图没有数据。但建议不要这样做，因为创建视图的sql语句可能没有保存
七、视图的意义
1、视图可以节省sql语句。将一条复杂的查询语句使用视图进行保存，以后可以直接对视图进行操作
2、数据安全。视图操作主要是针对查询的，如果对视图结构进行处理（删除），不会影响基表数据，相对安全
3、视图往往是在大项目中使用，而且是多系统使用。可以对外提供有用的数据，但隐藏了关键（无用）的数据，数据安全
4、视图可以对外提供友好型。不同的视图提供不同的数据，对外好像专门设计
5、视图可以更好（容易）的进行权限控制
注：基表的数据往往要极力隐藏，甚至都不让别人知道表的名字和结构
八、视图数据操作
1、视图可以进行数据写操作，但是有很多限制。一般只会给查看视图的权限，不会给增删改视图的权限
2、对视图的操作会影响基表，基表的数据会发生改变
3、新增数据
（1）新增数据就是直接对视图进行数据新增
注：给视图新增数据意味着不知道基表，不知道基表就没办法操作基表。所以，所有的操作都是对视图进行的操作
（2）多表视图不能新增数据（不能给一个有连接的视图(多表视图)插入数据）
（3）可以向单表视图插入数据。但是视图中包含的字段必须有基表中所有不能为空（或者没有默认值）的字段
（4）视图是可以向基表插入数据的（对视图的操作会影响基表，基表的数据会发生改变）
-- 单表视图插入数据
-- 对视图的操作会影响基表，基表的数据会发生改变
insert into 视图名 values ('值1', '值2', ... );
-- 创建视图：单表视图
create view my_v4 as
select * from my_class;
-- 查看数据
select * from my_class;
select * from my_v4;
-- 单表视图插入数据
-- 视图中包含的字段必须有基表中所有不能为空（或者没有默认值）的字段
-- 对视图的操作影响了基表，基表数据发生了改变
insert into my_v4 values (666, '6班', 'B606');
-- 查看数据
select * from my_v4;
select * from my_class;
4、删除数据
（1）多表视图不能删除数据（此处的多表视图指的是连接） -- 试一下多表数据源（笛卡尔积）、联合 可不可以删除???
eg：学生表和班级表连成一个视图，如果删除学生时把班级也删除了，就会造成所有的学生都没有匹配的班级了 -- 数据不可控
-- 创建多表视图（连接）
create view my_v5 as
select s.*, c.name as c_name, c.room from my_student as s
left join my_class c on c.id = s.c_id;
-- 查看视图数据
select * from my_v5;
-- 多表视图删除数据
delete from my_v5 where id = 1;
-- 报错：Can not delete from join view 'mydatabase.my_v5'
（2）单表视图可以删除数据
-- 单表视图删除数据
-- 对视图的操作会影响基表，基表的数据会发生改变
delete from 视图名 [where条件];
-- 创建视图：单表视图
create view my_v6 as
select * from my_class;
-- 查看数据
select * from my_class;
select * from my_v6;
-- 单表视图删除数据
-- 对视图的操作影响了基表，基表数据发生了改变
delete from my_v6 where id = 1;
-- 查看数据
select * from my_v6;
select * from my_class;
5、更新数据
（1）理论上，不论单表视图还是多表视图，都可以更新数据
-- 单表视图|多表视图 更新数据
-- 对视图的操作会影响基表，基表的数据会发生改变
update 视图名 set 字段名 = 值 [where条件]
-- 创建多表视图
create view my_v7 as
select s.*, c.name as c_name, c.room from my_student as s
left join my_class c on c.id = s.c_id
-- 查看数据
select * from my_v7;
-- 多表视图更新数据
-- 对视图的操作影响了基表，基表数据发生了改变
update my_v7 set c_id = 333 where id = 3;
-- 查看数据
select * from my_v7;
select * from my_student;
select * from my_class;
（2）更新限制：with check option。在创建视图的时候，如果对某个字段有限制，那么在对视图进行数据更新操作时，系统会进行验证。要保证更新之后，数据依然可以被视图查询出来（更新后的数据值依旧满足创建视图时对字段的限制），否则不让更新
-- 更新限制（with check option）
-- 创建视图，使用更新限制
create view 视图名 as
select * / 字段列表 from 表名 where条件1 with check option;
-- 更新数据
-- 更新的字段值必须满足创建视图时设定是where条件1，否则报错
update 视图名 set 字段名 = 值 [where条件];
-- 更新限制（with check option）
-- 创建视图，使用更新限制
-- where age>30：视图的数据来源都是age>30的数据
-- with check option：视图更新的时候，不能将已经得到的age>30的数据改成age<=30的数据
create view my_v8 as
select * from my_student where age > 30 with check option;
-- 查看数据
select * from my_v8;
-- 将视图可以查到的数据改成age<30的数据
update my_v8 set age = 29 where id = 1;
-- 报错：CHECK OPTION failed 'mydatabase.my_v8'
-- 对视图的操作影响了基表，基表数据发生了改变
update my_v8 set age = 36 where id = 1; -- 修改成功
-- 针对视图中的数据进行更新。视图中没有id=111的数据，所以修改无效
update my_v8 set age = 36 where id = 111;
-- 查看数据
select * from my_v8;
6、单表视图和多表视图的操作区别
（1）单表视图：可以插入数据，可以删除数据，可以更新数据
（2）多表视图：不能插入数据，不能删除数据，可以更新数据
九、视图算法
1、视图算法：系统对视图以及外部查询视图的select语句的一种解析方式
2、视图算法分为三种
（1）undefined：未定义（默认）。这不是一种实际使用的算法，是一种推卸责任的算法。告诉系统，视图没有定义算法，系统自己看着办
（2）temptable：临时表算法。系统应该先执行视图的select语句，后执行外部查询语句（将第一次查询到的数据拿到内存中，再对内存进行操作，操作了两次。因为拿到内存的数据也不一定全部都是需要的数据）
（3）merge：合并算法。系统应该先将视图对应的select语句与外部查询视图的select语句进行合并，然后执行 -- 效率高（常态）
注：
a). 执行一次查询的效率比执行两次查询的效率高
b). 系统经常选择merge，undefined时也会经常选择merge
3、算法指定：在创建视图的时候
-- 算法指定：在创建视图的时候
create algorithm = 指定算法 view 视图名 as select 语句;
-- 获取每个班级中最高的一个学生
create view my_v9 as
select * from my_student order by height desc;
-- 查询数据
-- 下面两句sql效果一样，结果都是错误的
select * from my_v9 group by c_id;
select * from my_student group by c_id order by height desc;
-- 指定算法为临时表temptable
create algorithm = temptable view my_v10 as
select * from my_student order by height desc;
-- 查看视图创建语句
show create view my_v10\G;
-- 查看数据
-- group by c_id：group by本身也带有排序效果，默认是升序asc。此处默认按照c_id升序排序
select * from my_v10 group by c_id; -- 结果正确
-- 等价于
select * from (select * from my_student order by height desc) as a group by a.c_id;
4、视图算法选择：如果视图的select语句中会包含一个查询子句（5子句），而且很有可能顺序比外部的查询语句要靠后，此时一定要使用临时表算法temptable。其他情况可以不用指定，默认即可
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
