数据库系统原理及MySQL应用教程实验六视图创建与管理
作者:mmseoamin日期:2023-12-13

实验 6 视图创建与管理

一、实验目的

1.理解视图的概念。

2.掌握创建、更改、删除视图的方法。

3.掌握使用视图来访问数据的方法。

二、实验内容

1.验证性实验:在job数据库中,有聘任人员信息表:Work_lnfo表对其视图的操作。

2.设计性试验:在学生管理系统中,有学生信息表studentinfo表对其视图的操作。

三、实验步骤与实验结果

(一)验证性实验

在job数据库中,有聘任人员信息表:Work_lnfo表,其表结构如下表所示:

字段名

字段描述

数据类型

主键

外键

非空

唯一

自增

Id

编号

INT(4)

Name

名称

VARCHAR(20)

Sex

性别

VARCHAR(4)

Age

年龄

INT(4)

Address

家庭地址

VARCHAR(50)

Tel

电话号码

VARCHAR(20)

1.创建work_info表

CREATE TABLE  work_info(
id INT(4) NOT NULL  UNIQUE  PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
age INT(4),
address VARCHAR(50),
tel VARCHAR(20)
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 

       数据库系统原理及MySQL应用教程实验六视图创建与管理,第1张

2.向work_info插入数据

INSERT INTO work_info
VALUES
(1001,'张明','男',19,'北京市朝阳区','1234567'),
(1002,'李广','男',21,'北京市昌平区','2345678'),
(1003,'王丹','女',18,'湖南省永州市','3456789'),
(1004,'赵一枚','女',24,'浙江宁波市','4567890');

数据库系统原理及MySQL应用教程实验六视图创建与管理,第2张

3.创建视图info_view,显示年龄大于20岁的聘任人员id,name,sex,address信息。

CREATE VIEW info_view(id,name,sex,address)AS
SELECT id,name,sex,address
FROM
work_info
WHERE
age>20 WITH LOCAL CHECK OPTION;

    数据库系统原理及MySQL应用教程实验六视图创建与管理,第3张

4.查看视图info_view的基本结构和详细结构。

(1)查看基本结构:

DESC info_view;

    数据库系统原理及MySQL应用教程实验六视图创建与管理,第4张

(2)查看详细结构:

SHOW CREATE VIEW info_view;

    数据库系统原理及MySQL应用教程实验六视图创建与管理,第5张

5.查看视图info_view的所有记录。

SELECT * FROM info_view;

    数据库系统原理及MySQL应用教程实验六视图创建与管理,第6张

6.修改视图info_view,满足年龄小于20岁的聘任人员id,name,sex,address信息。

ALTER VIEW info_view(id,name,sex,address) AS
SELECT id,name,sex,address  
FROM work_info
WHERE
age<20 WITH LOCAL CHECK OPTION;

    数据库系统原理及MySQL应用教程实验六视图创建与管理,第7张

7.更新视图,将id号为1001的聘任员的性别,由“男“改为“女”。

UPDATE info_view SET sex='女' WHERE id=1001;

    数据库系统原理及MySQL应用教程实验六视图创建与管理,第8张

8.删除info_view视图。

DROP VIEW info_view;

        数据库系统原理及MySQL应用教程实验六视图创建与管理,第9张

(二)设计性试验

在学生管理系统中,有学生信息表studentinfo表,其表结构如下:

字段名

字段描述

数据类型

主键

外键

非空

唯一

自增

Number

学号

INT(4)

Name

姓名

VARCHAR(20)

Major

专业

VARCHAR(20)

age

年龄

INT(4)

1.创建studentinfo表

CREATE TABLE  studentinfo(
number INT(4) NOT NULL  UNIQUE  PRIMARY KEY,
name VARCHAR(20) NOT NULL,
major VARCHAR(20),
age INT(4)
)ENGINE=InnoDB DEFAULT CHARSET=utf8; 

       数据库系统原理及MySQL应用教程实验六视图创建与管理,第10张

2.使用CREATE VIEW语句来创建视图college_view,显示studentinfo表中的number,name,age,major,并将字段名显示为:

student_num,student_name,student_age,department。

CREATE VIEW college_view
(student_num,student_name,student_age,department)
AS
SELECT number,name,age,major
FROM studentinfo;

       数据库系统原理及MySQL应用教程实验六视图创建与管理,第11张

3.执行SHOW CREATE VIEW语句来查看视图的详细结构

SHOW CREATE VIEW college_view;

    数据库系统原理及MySQL应用教程实验六视图创建与管理,第12张

4.更新视图。向视图中插入如下3条记录:

0901,'张三',20,'外语'

0902,'李四',22,'计算机'

0903,'王五',19,'计算机‘

INSERT INTO college_view VALUES
(0901,'张三',20,'外语'),
(0902,'李四',22,'计算机'),
(0903,'王五',19,'计算机');

      数据库系统原理及MySQL应用教程实验六视图创建与管理,第13张

5.修改视图,使视图中只显示专业为“计算机”的信息。

ALTER VIEW college_view
(student_num,student_name,student_age,department)
AS
SELECT number,name,age,major
FROM studentinfo
WHERE major='计算机';

       数据库系统原理及MySQL应用教程实验六视图创建与管理,第14张

6.删除视图。 

DROP VIEW college_view ;

        数据库系统原理及MySQL应用教程实验六视图创建与管理,第15张

四、观察与思考

1.通过视图中插入的数据能进入到基本表中去吗?

答:可以的,因为视图不保存数据,只保存数据来源的内容。

2.WITH CHECK OPTION能起什么作用?

答:可以防止在更新视图时不满足视图条件的数据插入到视图中。

3.修改基本表的数据会自动反映到相应的视图中去吗?

答:修改基本表视图也会发生变化。

4.哪些视图中的数据不可以增删改操作?

(1)视图中包含SUM、COUNT等聚集函数。

(2)视图中包含UNION、UNION ALL、DISTINCT、GROUP BY、HAVING等关键字。

(3)常量视图,比如:CREATE VIEW view_now AS SELECT NOW().

(4)视图中包含子查询。

(5)由不可更新的视图导出的视图。

(6)创建视图时ALGORITHM为TEMPTABLE类型。

(7)视图对应的表上存在没有默认值的列,而且该列没有包含在视图里。

(8)WITH CHECK OPTION 也决定视图是否可以更新,LOCAL参数表示更新视图时要满足该视图本身定义的条件即可;CASCADED参数表示更新视图时要满足所有相关视图和表的条件,是默认值。

五、实验要求

按要求完成,详细记录操作步骤,书写实验报告。所有实验环节均由每位学 生独立完成,认真记录操作过程,严禁抄袭他人实验结果。

六、实验总结

通过本次实验学习了对视图的增删改查的操作,了解了视图的优势,在统一数据库可以创建不同的视图,为用户分配不同的视图,可以实现不同用户只能查询或修改对应的数据,增加了数据的安全访问控制。