【MySQL数据库设计与应用(四)】视图

MySQL 专栏收录该内容
8 篇文章 0 订阅

1 视图概述、创建视图

1.1 基本概念

视图是一个虚拟表,是 sql 的查询结果,其内容由查询定义。同真实的表一样,视图包含一系列带有名称的列和行数据,在使用视图时动态生成。视图的数据变化会影响到基表,基表的数据变化也会影响到视图。

在这里插入图片描述

1.2 创建视图

使用 create view 语句创建视图语法为:

create [or replace]								# 可替换已有的视图
[algorithm ={ undefined|merge|temptable }]		# 视图算法的选择
view view_name[(column_list)]					# 视图名,后可自定义视图列表名
as select_statement								# 视图的定义
[with[ cascaded|local] check option ] ;			# 权限设置

说明:
create: 表示创建新的视图;
or replace:表示如果该视图已经存在,则用新视图代替它,若视图不存在则创建新视图

algorithm 子句:表示为视图选择算法,有三个选项,一般情况下我们不显示给出,使用第一个参数 “undefined”,表示 MySQL 自动选择算法;

view_name:为视图名。默认情况下,在当前数据库下创建视图,若想给其他数据创建视图,必须在视图名前制定目标数据库,db_name.view_name,视图名不能与表名相同;

column_list:可以给视图列取名称,多个列用逗号隔开;

select_statement:用来创建视图的 select 语句,给出了视图所需内容。默认情况下,select 语句检索的列名就是视图的列名称。若想给列名取别的名字,可使用 column_list 字句,但注意两者数目必须相等。

With check option :用于视图更新时,保证在视图的权限范围内;

  • 要求具有针对视图的 create view 语句权限,以及针对有 select 语句选择列上的某些权限。
    cascaded | local:可选参数;

  • lcascade:默认值,更新视图时要满足所有相关视图和表的条件;

  • local :表示更新视图时满足该视图本身的定义即可。

视图不仅可以从一个基表导出,还可以从多张基表导出来;并且还可以在已有的视图基础上导出新的视图;因此可以将视图的创建分为三种情况:单表中创建视图,在多表中创建视图,在已有视图中创建新的视图

(1)在单表中创建视图

【例】在 course 数据库中,基于 student 表创建一个 A 老师可以查看的视图 A,取名:stu_viewA,要求查询输出 student_id,student_name,phone,并将字段名改为:学号、姓名、电话。

create or replace
view stu_viewA(学号, 姓名, 电话)
as
select student_id, student_name, phone
from student;

【例】在 course 数据库中,基于 student 表创建一个 B 老师可以查看的视图 B,取名:stu_viewB,要求查询输出家庭地址为“上海”的学生详细信息。

create or replace
view stu_viewB
as
select * from student where home_address = N'上海' # 中文查不出来,前面N
with check option;

(2)在多表中创建视图

【例】在 course 数据库中,使用 teacher 表、department 表创建一个名为 tech_view1 的视图,视图中可查询教工号 Teacher_id、教师姓名 Teacher_name、性别 Gender、学院名称 Department_name、专业 Major、职称信息 profesional。

create view tech_view1
as
select Teacher_id, Teacher_name, Gender, Department_name, Major, profesional
from teacher join department
on teacher.Department_id= department. Department_id;

(3)在视图中创建新视图

【例】利用上例中的 tech_view1 视图,创建一个名为 tech_view2 新视图,要求统计出“信息学院”,职称为“教授”、“副教授”的教师的以下信息:Teacher_id,Teacher_name,Major,并将视图的字段名设成教工号、姓名和专业。

create view tech_view2(教工号, 姓名, 专业)
as
select Teacher_id, Teacher_Name, Major
from tech_view1
where profesional like N'%教授' and Department_name = N'信息学院';

备注:
查询条件是中文查不出来时,查询时加入 N,如:select * form table where city=N’上海’

总结:

  • 视图是从一个或者多个表、或其他视图中通过 select 语句导出的虚拟表;
  • 数据库中只存放了视图的定义,而并没有存放视图中的数据;
  • 浏览视图时产生的数据均来自定义视图查询所引用的基表,并且在引用视图时动态生成;
  • 通过视图可以实现对基表数据的查询或修改。

视图作用:

  • 简化数据查询和处理:视图可以为用户集中多个表中的数据,使用户可以将注意力集中在他们关心的数据上,简化用户对数据的查询和处理;
  • 屏蔽数据库的复杂性:数据库表的更改不影响用户对数据库的使用,用户也不必了解复杂的数据库中的表结构。例如,那些定义了若干张表连接的视图,就将表与表之间的连接操作对用户隐蔽起来了;
  • 安全性:如果想要使用户只能查询或修改用户有权限访问的数据,也可以只授予用户访问视图的权限,而不授予访问表的权限,这样就提高了数据库的安全性。

2 查看视图、修改视图

2.1 查看视图

查看视图:查看数据库中已存在的视图的定义。可以查看视图的基本信息、详细信息;
1、查看视图基本信息的方法:describe 语句、show table status 语句;
2、查看视图详细信息的方法:show create view 语句、查询 information_schema 数据库下的 views 表等。

查看视图基本信息
(1)使用 describe 语句查询视图基本信息
describe 语句查看视图与查看表的形式一样,
语法:describe view_name;describe 可简写成 desc

(2)使用 show table status 语句查询视图基本信息:
语法:show table status like ‘view_name’;

example:

# 使用 describe 语句查询上例中建立的 tech_view2 视图的基本信息
describe tech_view2;

# 使用 show table status 语句查询 tech_view2 视图的基本信息
show table status like ‘tech_view2’;

查看视图详细信息
(1)使用 show create view 语句查询视图详细信息:
语法:show create view view_name;

(2)在 views 表中查看视图详细信息:MySQL 数据库中,所有视图的定义都存在 information_schema 数据库下的 views 表中。
即:查询 information_schema.views 表,可以查看到数据库中所有视图的详细信息。
代码如下:
select * from information_schema.views;

example:

# 使用show create view语句查询tech_view2视图的详细信息
show create view tech_view2;

select * from information_schema.views;

2.2 修改视图

修改视图:指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时,可以通过修改视图来保持视图和基本表之间一致。MySQL 中通过 create or replace view 语句和 alter 语句来修改视图。

(1)使用 create or replace view 语句来修改视图,与前面创建视图语法一模一样,语法格式如下:
create [or repalce] [algorithm = {undefined|merge|temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded|local]check option];

(2)使用 alter 语句来修改视图,语法格式如下:
alter [algorithm = {undefined|merge|temptable}]
view view_name [(column_list)]
as select_statement
[with [cascaded|local]check option];

example:

alter view tech_view2(教工号, 姓名, 职称)
as
select Teacher_id, Teacher_Name, Major
from tech_view1
where profesional like N'%教授' and (Department_name = N'信息学院' or Department_name = N'机械学院');

3 视图数据更新、删除

3.1 视图数据更新

更新视图:通过视图来插入、更新、删除表中的数据,因为视图是一个虚拟表,其中没有数据。视图的更新实际上转到基本表上进行更新,即对视图增加或者删除记录,实际上是对其基本表增加或者删除记录。

本节将介绍 3 种视图更新的方法:insert、update、delete;

(1)使用 insert 语句在视图中插入一条数据,其语法与向表中插入数据相同。
语法:

  • insert [into] view_name [(col_name,…)]
    values ({expr|default},…),(…),…

(2)使用update语句更新视图数据,其语法与表数据更新相同。
语法:

  • update view_name
    set col_name1= [,col_name2=expr2 …]
    [where子句]

(3)使用 delete 语句删除视图一条数据,其语法与向表数据删除相同。
语法:

  • delete
    from view_name
    [where子句]
# 使用insert语句,向视图stu_viewB中插入一条数据
insert into stu_viewb values
('M20177010', '张小明', '123456', '男', '102', '2005', '2000-01-01', '11000000003', '上海');

update stu_viewb
set passord = '123666' where student_id = 'M20177010';

update tech_view1
set Department_name = '信息学院' where Teacher_id = 'T0003';

update tech_view1
set profesional  = '副教授' where Teacher_id = 'T0003';

delete from stu_viewb
where student_id = 'M20177010';

结论 :
1 、视图若只依赖于一个基表 ,则可以直接通过视图来更新基本表数据;
2 、视图若依赖于多张基表 ,则一次只能修改一个基表的数据,不能同时修改多个基表的 数据。

注意:当视图中包含如下内容时,视图更新操作将不会被执行:

  1. 视图中不包含基本表中被定义为非空的列;
  2. 在定义视图的 select 语句后的字段列表中使用了数学表达式;
  3. 在定义视图的 select 语句后的字段列表中使用了聚合函数;
  4. 在定义视图的 select 语句引用不可更新视图;
  5. 在定义视图的 select 语句中使用了 distinct、union、top、group by、order by、having 字句。

3.2 删除视图

当视图不再需要时,可以将其删除。删除视图时,只能删除视图的定义,不会删除数据。MySQL 数据库中,用户必须拥有 drop 权限才能删除视图。使用 drop view 命令进行删除,可以删除一个或多个视图,各视图名之间用逗号分隔。

语法:

  • drop view [if exists]
    view_name [, view_name]
    [ restrict | cascade]

example:

# 删除视图stu_viewA
drop view stu_viewA;
  • 1
    点赞
  • 1
    评论
  • 3
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

评论 1 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页

打赏作者

-出发-

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值