实验 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; 

       

2.向work_info插入数据

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

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;

    

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

（1）查看基本结构：

DESC info_view;

    

（2）查看详细结构：

SHOW CREATE VIEW info_view;

    

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

SELECT * FROM info_view;

    

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;

    

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

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

    

8.删除info_view视图。

DROP VIEW info_view;

        

（二）设计性试验

在学生管理系统中，有学生信息表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; 

       

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;

       

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

SHOW CREATE VIEW college_view;

    

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

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

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

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

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

      

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

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

       

6.删除视图。 

DROP VIEW college_view ;

        

四、观察与思考

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参数表示更新视图时要满足所有相关视图和表的条件，是默认值。

五、实验要求

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

六、实验总结

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