1.1 视图的基本操作
视图(View)是一种虚拟存在的表。视图中的数据并不在数据库中实际存在,行和列数据来自定义视图的查询中使用的表,并且是在使用视图时动态生成的。
通俗的讲,视图只保存了查询的SQL逻辑,不保存查询结果。所以我们在创建视图的时候,主要的工作就落在创建这条SQL查询语句上
创建语法:
CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
#创建视图 CREATE OR REPLACE VIEW emp_v_1 AS SELECT * FROM emp WHERE sal <= 2000;
查询:
#查看创建视图语句: SHOW CREATE VIEW 视图名称; #查看视图数据: SELECT * FROM 视图名称 ...... ;
-- 查询视图 SHOW CREATE VIEW emp_v_1; SELECT * FROM emp_v_1; SELECT * FROM emp_v_1 WHERE sal < 1000;
修改:
--方式一: CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ] --方式二: ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]
-- 修改视图 CREATE OR REPLACE VIEW emp_v_1 AS SELECT empno,sal,ename FROM emp WHERE sal <= 2000; ALTER VIEW emp_v_1 AS SELECT * FROM emp WHERE sal <= 2000;
删除:
DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...
drop view if exists emp_v_1;
上述我们演示了,视图应该如何创建、查询、修改、删除,那么我们能不能通过视图来插入、更新数据呢? 接下来,做一个测试
CREATE OR REPLACE VIEW emp_v_2 AS SELECT empno,ename,sal FROM emp_back WHERE sal<=2000; SELECT * FROM emp_v_2; INSERT INTO emp_v_2 VALUES(8080,'TOM',1888); INSERT INTO emp_v_2 VALUES(8081,'MARY',2500);
执行上述的SQL,我们会发现,empno为8080和8081的数据都是可以成功插入的。 但是我们执行查询,查询出来的数据,却没有id为8081的记录。
因为我们在创建视图的时候,指定的条件为 sal<=2000, id为8081的数据,是不符合条件的,所以没有查询出来,但是这条数据确实是已经成功的插入到了基表中。
如果我们定义视图时,如果指定了条件,然后我们在插入、修改、删除数据时,是否可以做到必须满足条件才能操作,否则不能够操作呢? 答案是可以的,这就需要借助于视图的检查选项了。
1.2 检查选项
当使用WITH CHECK OPTION子句创建视图时,MySQL会通过视图检查正在更改的每个行,例如 插
入,更新,删除,以使其符合视图的定义。 MySQL允许基于另一个视图创建视图,它还会检查依赖视
图中的规则以保持一致性。为了确定检查的范围,mysql提供了两个选项: CASCADED 和 LOCAL
,默认值为 CASCADED 。
-
CASCADED 级联
比如,v2视图是基于v1视图的,如果在v2视图创建的时候指定了检查选项为 cascaded,但是v1视图创建时未指定检查选项。 则在执行检查时,不仅会检查v2,还会级联检查v2的关联视图v1。
CREATE OR REPLACE VIEW emp_v_1 AS SELECT empno,ename,sal FROM emp_back WHERE sal<=2000; CREATE OR REPLACE VIEW emp_v_2 AS SELECT empno,ename,sal FROM emp_v_1 WHERE sal>=1000 WITH CASCADED CHECK OPTION; INSERT INTO emp_v_2 VALUES(8080,'TOM',1888); INSERT INTO emp_v_2 VALUES(8081,'MARY',2500);
执行上述的SQL,我们会发现,empno为8080的数据都是可以成功插入的。 而8081是无法插入的,这是因为, 2500 满足了emp_v_2的检查条件,但是emp_v_2视图是基于emp_v_1视图创建的,而是使用的是CASCADED,尽管emp_v_1没有设置with check option,但是emp_v_2使用with cascaded check option,所以在执行插入,不仅要检查插件的数据满足emp_v_2的条件,而且还有满足emp_v_1的条件,而8081满足emp_v_2条件,但是不满足emp_v_1,所以也插入失败,
-
LOCAL 本地。
比如,v2视图是基于v1视图的,如果在v2视图创建的时候指定了检查选项为 local ,但是v1视图创建时未指定检查选项。 则在执行检查时,知会检查v2,不会检查v2的关联视图v1。
CREATE OR REPLACE VIEW emp_v_1 AS SELECT empno,ename,sal FROM emp_back WHERE sal<=2000; CREATE OR REPLACE VIEW emp_v_2 AS SELECT empno,ename,sal FROM emp_v_1 WHERE sal>=1000 WITH LOCAL CHECK OPTION; INSERT INTO emp_v_2 VALUES(8080,'TOM',1888); INSERT INTO emp_v_2 VALUES(8081,'MARY',2500);
执行上述的SQL,我们会发现,empno为8080和8081的数据都是可以成功插入的。
1.3 视图的更新
要使视图可更新,视图中的行与基础表中的行之间必须存在一对一的关系。如果视图包含以下任何一项,则该视图不可更新:
A. 聚合函数或窗口函数(SUM()、 MIN()、 MAX()、 COUNT()等)
B. DISTINCT
C. GROUP BY
D. HAVING
E. UNION 或者 UNION ALL
create or replace view emp_v_1 as select job,count(1) from emp_back ; INSERT INTO emp_v_1(job,sal) VALUES('市场',2000);
1.4 视图的作用
-
简单
视图不仅可以简化用户对数据的理解,也可以简化他们的操作。那些被经常使用的查询可以被定义为视图,从而使得用户不必为以后的操作每次指定全部的条件。
-
安全
数据库可以授权,但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据
-
数据独立
视图可帮助用户屏蔽真实表结构变化带来的影响。
案例
1). 为了保证数据库表的安全性,开发人员在操作tb_user表时,只能看到的用户的基本字段,屏蔽
手机号和邮箱两个字段
create view tb_user_view as select id,name,profession,age,gender,status,createtime from tb_user; select * from tb_user_view;
2). 查询每个学生所选修的课程(三张表联查),这个功能在很多的业务中都有使用到,为了简化操
作,定义一个视图
create view tb_stu_course_view as select s.name student_name , s.no student_no , c.name course_name from student s, student_course sc , course c where s.id = sc.studentid and sc.courseid = c.id; select * from tb_stu_course_view;