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写好。

  • 26
    点赞
  • 25
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值