一、视图-介绍及基本语法
视图(View)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的。
通俗的讲,视图只保存了查询的SQL逻辑,不保存查询结果。所以我们在创建视图的时候,主要的工作就落在创建这条SQL查询语句上。
1.1 创建视图
CREATE [ OR REPLACE ] VIEW 视图名称[(列名列表)] AS SELECT 语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
创建视图时,[ OR REPLACE ]可以不添加,但是修改时必须添加
例子:
create or replace view stu_v_1 as select id,name from student where id<=10;
1.2 查询视图
查看创建视图语句: SHOW CREATE VIEW
视图名称;
例子:
show create view stu_v_1;
查看视图数据:SELECT*FROM
视图名称;
例子:select *from stu_v _1 where id < 3;
1.3 修改视图
方式一:CREATE[OR REPLACE] VIEW 视图名称[(列名列表))] AS SELECT 语句[ WITH[ CASCADED | LOCAL ] CHECK OPTION ]
创建视图时,[ OR REPLACE ]可以不添加,但是修改时必须添加
例子:create or replace view stu_v_1 as select id , name , no from student where id <= 10;
方式二:ALTER VIEW 视图名称 [(列名列表)] AS SELECT语句 [WITH [CASCADED | LOCAL] CHECK OPTION]
例子:alter view stu_v_1 as select id , name from student where id <= 10;
1.4 删除视图
DROP VIEW [IF EXISTS] 视图名称 [,视图名称]
例子:drop view if exists stu_v_1;
二、视图的检查选项
2.1 检查选项(cascaded)
创建视图的语句:
CREATE [ OR REPLACE ] VIEW 视图名称[(列名列表)] AS SELECT 语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
当使用WITH CHECK QPTION子句创建视图时,MySQL会通过视图检查正在更改的每个行,例如插入,更新,删除,以使其符合视图的定义。MySQL允许基于另一个视图创建视图,它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围,mysql提供了两个选项:CASCADED 和 LOCAL ,默认值为 CASCADED。
CASCADED:级联,一旦选择了这个选项,除了会检查创建视图时候的条件,还会检查所依赖视图的条件。
eg:
比如下面的例子:创建stu_V_l 视图,id是小于等于 20的。
create or replace view stu_V_l as select id,name from student where id <=20;
再创建 stu_v_2 视图,由于加上了
with cascaded check option,所以
20 >= id >=10。create or replace view stu_v_2 as select id,name from stu_v_1 where id >=10 with cascaded check option;
再创建 stu_v_3 视图。
create or replace view stu_v_3 as select id,name from stu_v_2 where id<=15;
这条数据能够成功,stu_v_3 没有开检查选项所以不会 去判断 id 是否小于等于15, 直接检查 是否满足 stu_v_2。insert into stu_v_3 values(17,'Tom');
2.2 检查选项(local)
LOCAL:本地的,
一旦选择了这个选项,除了会检查创建视图时候的条件,还会检查所依赖视图带检查选项的条件。
本地的条件也会检查,还会向上检查。在向上找的时候,就要看是否上面开了检查选项,如果没开就不检查。和 CASCADED 的区别就是 CASCADED 不管上面开没开检查选项都会进行检查。
三、视图更新及作用
3.1 视图的更新
要使视图可更新,视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项,则该视图不可更新
- 聚合函数或窗口函数 ( SUM()、MIN()、MAX()、COUNT() 等 )
- DISTINCT
- GROUP BY
- HAVING
- UNION 或者UNION ALL
例子:
使用了聚合函数,插入会失败。 创建视图如下:
create view stu_v_count as select count(*) from student;
执行下面插入语句会失败:
insert into stu_v_count values(10);
3.2 视图的作用
总而言之 类似于给表加上了一个外壳,通过这个外壳访问表的时候,只能按照所设计的方式进行访问与更新。
四、案例
1.为了保证数据库表的安全性,开发人员在操作tb_user表时,只能看到的用户的基本字段,屏蔽手机号和邮箱两个字段
create view tb_user_view as select id,name , profession , age , gender , status from tb_user;
2.查询每个学生所选修的课程〈三张表联查),这个功能在很多的业务中都有使用到,为了简化操作,定义一个视图。
create view tb_stu_course_view as select s.name,s.no , c.name from student s,student_course sc, course c where s.id = sc.studentid and sc.courseid = c.id;
五、总结
MySQL视图是对一个或多个表的查询结果的虚拟表,它可以作为查询的基础或封装复杂查询逻辑。下面是MySQL视图的几个要点总结:
1. 定义视图:使用CREATE VIEW语句定义视图,指定视图的名称和查询语句,如:
CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;
2. 使用视图:使用SELECT语句查询视图的数据,就像查询普通表一样,例如:
SELECT * FROM view_name;
3. 更新视图:视图可以被更新,前提是满足一定的条件,如视图的SELECT语句不能包含GROUP BY或HAVING子句,以及视图的列不能是表达式、子查询等。
4. 视图的优点:
- 简化复杂查询:可以将复杂的查询逻辑封装到视图中,使查询更简单和易于维护。
- 安全性控制:可以通过视图限制对数据的访问权限,只暴露特定的列或行给用户。
- 逻辑独立性:修改视图的定义不会影响基础表的结构,提高了应用程序的灵活性。
- 数据聚合和重用:可以对多个表进行聚合操作或创建可重用的视图。
5. 视图的限制:
- 不可索引:视图本身不存储实际的数据,因此无法创建索引。
- 写操作限制:根据视图的定义,可能无法进行插入、更新或删除操作。
- 性能影响:复杂的视图查询可能会降低查询性能。
总之,MySQL视图是一种强大的数据库对象,可以简化查询、保护数据、提高灵活性,并提供数据聚合和重用的能力。