MySQL进阶——视图

目录

1基本语法

1.1创建

1.2 查询

1.3 修改

1.4 删除

2 检查选项

2.1 级联CASCADED

2.2本地LOCAL

3 更新及作用

3.1视图的更新条件

3.2视图的作用

4视图案例

1基本语法

视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

通俗的讲，视图只保存了查询的SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

1.1创建

语法：

CREATE [OR REPLACE 替换] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION]

案例：创建包含id和name两个字段的视图且id<10

SQL代码：

create or replace view str_v_1 as select id,name from student where id <=10;

1.2 查询

因为视图（View）是一种虚拟存在的表。所以可以操作表一样操作视图。

（1）查看创建视图语句：SHOW CREATE VIEW 视图名称;

SQL代码：

show create view str_v_1;

（2）查看视图数据：SELECT * FROM 视图名称 ...... ;

SQL代码：select * from str_v_1;

查看视图也可以加条件：

SQL代码：select * from str_v_1 where id <3;

1.3 修改

（1）方式一：

CREATE [OR REPLACE] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

SQL代码：在原来是视图上加上no

create or replace view str_v_1 as select id,name,no from student where id <=10;

（2）方式二：ALTER VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

SQL代码：在原来是视图上删掉name

alter view str_v_1 as select id,no from student where id <=10;

1.4 删除

语法：

DROP VIEW [IF EXISTS] 视图名称 [,视图名称] ...

SQL代码：

drop view if exists str_v_1;

2 检查选项

当使用WITH CHECK OPTION子句创建视图时，MySQL会通过视图检查正在更改的每个行，例如 插入，更新，删除，以使其符合视图的定义。 MySQL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mysql提供了两个选项： CASCADED 和 LOCAL ，默认值为 CASCADED 。

2.1 级联CASCADED

CREATE [OR REPLACE 替换] VIEW 视图名称[(列名列表)] AS SELECT语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]（检查选项）

案例：

（1）当不加检查选项：在执行插入时，不管是id=6<20，id=30>20，都会插入成功，即不会对视图str_v_1的操作进行检查，这样就会出现插入成功，但是查询不出来的情况。

create or replace view str_v_1 as select id,name from student where id <=20;

（2）加说检查选项：在执行插入时，在执行id=31>20时，会被检查出来，而不被插入进去。

SQL代码：

create or replace view str_v_1 as select id,name from student where id <=20 with cascaded check option ;

select * from str_v_1;

insert into str_v_1 values(7,'Jan');

insert into str_v_1 values(31,'Tom');

（3）还会检查依赖视图中的规则以保持一致性：

执行id=26时就会失败，因为id=26<10虽然满足str_v_2的条件，但是加了检查选项with cascaded check option，所以也会检查是否满足str_v_1的条件id=26>20的，不满足，所以最终不会运行成功。即加了检查选项，所有语句中的视图条件都要满足。

create or replace view str_v_1 as select id,name from student where id <=20;

create or replace view str_v_2 as select id,name from str_v_1 where id >=10 with cascaded check option ;

select * from str_v_2;



insert into str_v_2 values(15,'Jan');

insert into str_v_2 values(16,'Jan');

insert into str_v_2 values(26,'Tom');

（4）str_v_3没有加检查选项，所以id <=15可以不用满足，但是依赖了str_v_2，str_v_2又依赖了str_v_1，所以要满足前面两个的条件。

create or replace view str_v_1 as select id,name from student where id <=20;


create or replace view str_v_2 as select id,name from str_v_1 where id >=10 with cascaded check option ;

select * from str_v_2;


create or replace view str_v_3 as select id,name from str_v_2 where id <=15;

select * from str_v_2;


insert into str_v_2 values(11,'Jan');

insert into str_v_2 values(17,'Jan');

insert into str_v_2 values(28,'Tom');

insert into str_v_2 values(9,'Tom');

因此10<=11,17<=20可以插入，28,9不能插入:

2.2本地LOCAL

v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创建时未指定检查选项。 则在执行检查时，只会检查v2，不会检查v2的关联视图v1

检查选项为 local只会检查当前的，不会选择依赖的。Id为26和29不满足v1和v3，但是他们自己没有检查，local不会管。

create or replace view str_v_1 as select id,name from student where id <=20;

create or replace view str_v_2 as select id,name from str_v_1 where id >=10 with local check option ;

create or replace view str_v_3 as select id,name from str_v_2 where id <=15;

select * from str_v_2;



insert into str_v_2 values(12,'Jan');

insert into str_v_2 values(26,'Jan');

insert into str_v_3 values(29,'Jan');

3 更新及作用

3.1视图的更新条件

要使视图可更新，视图中的行与基础表中的行之间必须存在一对一的关系。

如果视图包含以下任何一项，则该视图不可更新：

A. 聚合函数或窗口函数（SUM()、 MIN()、 MAX()、 COUNT()等）

B. DISTINCT

C. GROUP BY

D. HAVING

E. UNION 或者 UNION ALL

如下案例：

这个视图使用了COUNT()就不能进行插入操作

create or replace view stu_v as select count(*) from student;

select * from stu_v;

insert into stu_v values(10);

3.2视图的作用

1）简单

视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视 图，从而使得用户不必为以后的操作每次指定全部的条件。

2）安全

数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据（保证一些敏感数据的安全）。

如：就将学号no隐藏了没有给这个视图查看权利。

create or replace view str_v_1 as select id,name from student where id <=20;

3）数据独立

视图可帮助用户屏蔽真实表结构变化带来的影响（创建视图给字段起别名，这样基础表变化不会影响视图）。

如：这样name字段名字改变了，不会影响视图中的字段。

create or replace view str_v_1 as select id,name as stuname from student where id <=20;

select * from str_v_1;

4视图案例

案例要求：

（1）为了保证数据库表的安全性，开发人员在操作tb_user表时，只能看到的用户的基本字段，屏蔽 密码和姓名两个字段。

（2）查询每个学生所选修的课程（三张表联查），这个功能在很多的业务中都有使用到，为了简化操 作，定义一个视图。

SQL实现：

（1）视图中查询除开“密码和姓名”以为的字段

create view v_tb_user as select id,username,birthday,sex from tb_user;

select * from v_tb_user;

（2）三张表联查：通过sc将s和c表连接起来的，可以先把select写好。