1.视图的概念
视图是虚表,是从一个或几个基本表(或视图)中导出的表,在系统的数据字典中仅存放了视图的定义,不存放视图对应的数据,是一条SELECT语句执行后返回的结果集。
2.视图的特性
视图看上去非常像数据库的物理表,对它的操作同任何其它的表一样。当通过视图修改数据时,实际上是在改变基表中的数据;相反地,基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因,有些视图可以修改对应的基表,而有些则不能(仅仅能查询)。
3.视图的作用
方便操作,特别是查询操作,减少复杂的SQL语句,增强可读性;
更加安全,数据库授权命令不能限定到特定行和特定列,但是通过合理创建视图,可以把权限限定到行列级别。
4.使用场景
(1)多个地方用到同样的查询结果
(2)查询结果使用的SQL语句比较复杂
(3)关键信息来源于多个复杂关联表,可创建视图提取需要信息,简化操作
5.创建视图及查询数据操作
首先创建一些测试表和数据
#1.创建用户表
drop table if exists tb_user;
create table tb_user(
id int not null auto_increment,
username varchar(32),
relaname varchar(32),
primary key(id)
);
#2.创建角色表
drop table if exists tb_role;
create table tb_role(
id int not null auto_increment,
rolename varchar(32),
primary key(id)
);
#3.创建用户角色管理表
drop table if exists tb_user_role;
create table tb_user_role(
id int not null auto_increment,
userid int,
roleid int,
primary key(id)
);
#4.插入数据
insert into tb_user(id,username,relaname) values
(1,'小君','君奉天'),(2,'小墨','墨倾池');
insert into tb_role(id,rolename) values
(1,'管理员'),(2,'普通用户');
insert into tb_user_role(id,userid,roleid) values
(1,1,1),(2,2,2);
从上文的测试数据,如果我们要查询某个用户的角色名称,那么我们需要连表查询,这里创建的测试数据不算复杂,实际开发中比这复杂多了,这里理解应用视图的思想。查询语句如下:
select a.username,b.rolename
from tb_user a
left join tb_user_role c on c.userid = a.id
left join tb_role b on c.roleid = b.id
where a.username = '小墨'
我们可以创建视图来简化上面的操作,创建视图的标准语法:
CREATE [REPLACE] [ALGORITHM [视图算法]] VIEW view_name [column_list]
AS SELECT_statement
[WITH [权限范围] ]
ALGORITHM视图算法:
(1)undefined 自动选择算法
(2)merge 将使用的视图语句与视图定义合并起来,
使得视图定义的某一部分取代语句对应的部分
(3)temptable 将视图的结果存入临时表
WITH[权限范围]:权限范围有
(1)cascaded 默认值表示更新视图时要满足所有的相关视图和表条件
(2)local 表示更新视图时满足该视图本身定义的条件即可
创建我们需要的视图,如下:
drop view if exists view_user_role;
create view view_user_role as(
select a.id,a.username,b.rolename
from tb_user a
left join tb_user_role c on c.userid = a.id
left join tb_role b on c.roleid = b.id
);
创建好视图之后,我们需要查询某个用户的角色名称的时候直接用以下SQL语句在视图上查询小墨的角色名称,同样可以得到所需结果:
select username,rolename from view_user_role;
6.增删改数据操作
我们可以尝试在视图view_user_role上做增删改数据操作
update view_user_role set username='小墨2',rolename='普通用2' where id = 2;
遗憾的是操作失败,提示错误信息如上图所示,因为不能在一张由多张关联表连接而成的视图上做同时修改两张表的操作。可操作的情况是视图与表是一对一关系情况:如果没有其它约束(在基本表必填等),是可以进行增删改数据操作。
drop view if exists view_user;
create view view_user as(
select a.id,a.username
from tb_user a
);
进行增删改操作如下,操作成功(注意user表中的其它字段要允许为空,否则操作失败):
插入
insert into view_user(id,username) values(3,'小天');
查询基础表验证
select * from tb_user;
继续我们进行更新和删除操作
更新
update view_user set username='小天2' where id = 3;
删除
delete from view_user where id = 3;
可自行验证(哈哈)。