mysql数据库添加视图数据语句 mysql数据库视图创建

转载

mob64ca14085c24 2023-12-25 10:13:43

文章标签 mysql数据库添加视图数据语句数据 SQL 插入数据 文章分类 MySQL 数据库

实验5 视图的创建及应用

1、实验目的

（1）掌握使用T-SQL语言创建、修改视图；

（2）掌握使用T-SQL语句删除、重命名视图；

（3）掌握使用T-SQL语句，通过视图对基本表进行数据操作；

（4）掌握使用界面操作的方式创建、修改、删除和重命名视图，以及通过视图对基本表进行数据操作的方法。

2、实验类型

验证型、设计型。

3、相关知识

视图是一种数据库对象，是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。视图是从一个或者多个数据表或视图中导出的虚表，视图的结构和数据是对数据表进行查询的结果。

（1）使用T-SQL语句创建视图的语法格式

CREATE VIEW [ < owner > .] view_name [ ( column_name [ ,...n ] ) ]

[WITH ENCRYPTION]

select_statement

FROM table_name WHERE search_condition

[WITH CHECK OPTION]

其中：

n view_name：为新创建的视图指定的名字，视图名称必须符合标识符规则。

n column_name：在视图中包含的列名,也可以在SELECT 语句中指定列名。

n table_name：视图基表的名字。

n select_statement：选择哪些列进入视图的SELECT语句。

n WHERE search_condition：基表数据进入视图所应满足的条件

n WITH CHECK OPTION：迫使通过视图执行的所有数据修改语句必须符合视图定义中设置的条件。

n WITH ENCRYPTION：对视图的定义进行加密。

创建视图时的注意事项：

在CREATE VIEW语句中，不能包括ORDER BY、COMPUTE或者COMPUTE BY 子句，也不能出现INTO关键字。
创建视图所参考基表的列数最多为1024列。
创建视图不能参考临时表。
在一个批处理语句中，CREATE VIEW语句不能和其他Transact-SQL语句混合使用。
尽量避免使用外连接创建视图。

（2）使用T-SQL语句管理视图

n 使用系统存储过程查看视图信息：

SP_HELP 数据库对象名称
SP_HELPTEXT 视图（触发器、存储过程）
SP_DEPENDS 数据库对象名称

n 用DROP VIEW语句删除视图

DROP VIEW view_namel,view_name2,…

n 使用系统存储过程重命名视图

SP_RENAME old_view_name,new_view_name

（3）使用T-SQL语句修改视图

ALTER VIEW view_name

[（column[,...n]）]

[WITH ENCRYPTION]

AS
select_statement
[ WITH CHECK OPTION ]

其中：

n view_name：被修改的视图的名字。

n column_name：在视图中包含的列名。

n WITH CHECK OPTION：迫使通过视图进行数据修改的所有语句必须符合视图定义中设置的条件。

n table_name：视图基表的名字。

n WITH ENCRYPTION：对包含创建视图的SQL脚本进行加密。

（4）使用T-SQL语句对视图数据的查询、插入、修改与删除

用T-SQL语句对视图数据的查询、插入、修改与删除的语法格式和对表中数据的查询、插入、修改与删除的操作几乎一样。

修改视图数据的限制：

n 无论是视图的创建、修改、删除还是视图数据的查询、插入、更新、删除都必须由具有权限的用户进行。

n 对由多个表连接成的视图修改数据时，不能同时影响一个以上的基础表，也不允许删除视图中的数据。

n 对视图上的某些列不能进行修改。这些列是：计算值、内置函数和行集合函数。

n 对具有NOT NULL的列进行修改时可能会出错。在通过视图修改或插入数据时，必须保证未显示的具有NOT NULL属性的列有值，可以是缺省、IDENTITY等，否则不能向视图中插入数据行。

n 如果某些列因为规则或者约束的限制而不能接受从视图插入数据的时候，则插入数据可能会失败。

n 删除基础表并不删除视图。建议采用与表明显不同的名字命名视图。

4、实验内容及指导

【实验5-1】：基于BOOK表创建一个视图BOOKVIEW1，输出bno、bname、bpc、author，然后通过视图查询图书信息。

USE JXGL

CREATE VIEW BOOKVIEW1

AS SELECT bno,bname,Bpc,author

FROM BOOK

SELECT * FROM BOOKVIEW1

【实验5-2】：基于BOOK表创建一个出版社为“清华大学出版社”的视图BOOKVIEW2，输出bno、bname、author、price，然后通过视图查询图书信息。

USE JXGL

CREATE VIEW BOOKVIEW2

AS SELECT bno, bname, author,price

FROM BOOK WHERE pbc=’清华大学出版社’

SELECT * FROM BOOKVIEW2

【实验5-3】: 基于BOOK表创建一个清华大学出版社出版，图书定价排在前五名的视图BOOKVIEW3，输出bno、bname、author，然后通过视图查询图书信息。

USE JXGL

CREATE VIEW BOOKVIEW3

AS SELECT TOP 5 bno,bname,author

FROM BOOK WHERE pbc=’清华大学出版社’

ORDER BY price DESC

SELECT * FROM BOOKVIEW3

【实验5-4】: 基于BOOK表创建一个显示出版社、图书平均定价及图书种类数的视图BOOKVIEW4，输出“出版社”、“平均定价”、“图书种类数”，然后通过视图查询“清华大学出版社”的数据信息。

USE JXGL

CREATE VIEW BOOKVIEW4

AS SELECT pbc 出版社, AVG(price) 平均定价, COUNT(bno) 图书种类数

FROM BOOK

GROUP BY pbc

SELECT *

FROM BOOKVIEW4

WHERE 出版社=‘清华大学出版社’

【实验5-5】：建立所有正在被选作为教材的图书信息的视图BOOKVIEW5，输出“课程号”、“课程名”、“学分”，“书名”、“作者”和“出版社”。

USE JXGL

Create view Bookview5 AS select cno 课程号, cname 课程名,

credit 学分,bname 书名,author 作者, pbc 出版社

from BOOK，C Where BOOK.bno=C.bno

【实验5-6】：基于【实验5-5】创建视图BOOKVIEW6,输出“课程名”和“书名”，并通过该视图查看课程教材选用信息。

USE JXGL

CREATE VIEW BOOKVIEW6

AS SELECT 课程名, 书名

FROM BOOKVIEW5

SELECT * FROM BOOKVIEW6

【实验5-7】：基于【实验5-5】创建的视图BOOKVIEW5进行修改，输出“课程名”、“书名”和“出版社”，并对视图加密。

USE JXGL

ALTER VIEW BOOKVIEW5 WITH ENCRYPTION

AS SELECT cname 课程名, bname 书名, pbc 出版社

FROM BOOK，C

WHERE BOOK.bno=C.bno

【实验5-8】：查看视图BOOKVIEW5的定义。

USE JXGL

SP_HELPTEXT BOOKVIEW5

【实验5-9】：将视图BOOKVIEW5重新命名为BVIEW5。

USE JXGL

SP_RENAME ‘BOOKVIEW5’, ‘BVIEW5’

【实验5-10】：基于【实验5-8】,将视图BVIEW5删除。

USE JXGL

DROP VIEW BVIEW5

【实验5-11】：通过【实验5-1】创建的视图BOOKVIEW1，向BOOK表插入一行数据信息（‘b101’,’数据库系统’,’高等教育出版社’,’丁宝康’）

USE JXGL

INSERT INTO BOOKVIEW1(bno,bname,bpc,author)

VALUES (‘b101’,’数据库系统’,’高等教育出版社’,’丁宝康’)

注：当视图只输出基本表的部分列时，通过视图插入数据可能会遇到问题：视图没有显示的列可能没有设置NULL特性，也没有设置缺省值，而通过视图无法对没有出现的列向基表插入数据，因而会导致数据插入失败；如果视图没有显示的列设置了NULL特性或设置了默认值，则可以通过视图向基表成功插入数据，如【实验5-11】。

【实验5-12】：通过【实验5-2】创建的视图BOOKVIEW2，把清华大学出版社的图书定价更新为原定价的八折，然后通过视图查询图书信息。

USE JXGL

UPDATE BOOKVIEW2

SET price=0.8*price

SELECT *

FROM BOOKVIEW2

【实验5-13】：通过【实验5-1】创建的视图BOOKVIEW1，把所有高等教育出版社的图书信息删除。

USE JXGL

DELETE FROM BOOKVIEW1

WHERE bpc=’高等教育出版社’

SELECT * FROM BOOKVIEW1

5、实验作业

（1）基于S表创建一个名为SVIEW1的视图，输出sno、sname、sex、birth、homadd，然后通过该视图查询学生信息；

（2）基于S表创建一个男同学的名为SVIEW2的视图，输出sno、sname、homadd，然后通过该视图查询男生信息；

（3）基于DEPT表创建一个学生人数排在前五名的视图DEPTVIEW1，输出dno、dname、dheader，然后通过视图查询系信息数据；

（4）基于S和SC表创建一个按系别分组显示，选修课程考试平均成绩的视图SVIEW3，输出“系号”、“平均成绩”，然后通过视图查询’04’系的数据信息；

（5）基于S、C和SC创建一个联合视图SVIEW4，要求输出“学号”、“姓名”、“课程名”、“学分”、“总学时”、“成绩”和“选修学期”；

（6）基于（5）中的视图SVIEW4创建视图SVIEW5，要求输出“姓名”、“课程名”和“成绩”；并通过视图SVIEW5查询“数据库原理”课程的选修信息；

（7）基于（5）中创建的视图SVIEW4进行修改，要求输出“姓名”、“课程名”和“成绩”；并对该视图进行加密；

（7）查看视图的定义；

（8）将视图SVIEW5重新命名为STUVIEW5；

（9）将视图STUVIEW5删除；

（10）通过（1）中创建的名为SVIEW1的视图，向S表中插入数据（’15043102’,’兰一飞’，’男’,’1993-11’,’北京市通州区焦王庄’）；

（11）通过SVIEW1将（10）中插入的学号为115043102的学生姓名更新为“兰飞”；

（12）通过（1）中创建的名为SVIEW1的视图，删除所有男同学的信息。

6、实验总结

（1）实验内容的完成情况；

（2）对重点实验结果进行分析；

（3）出现的问题；

（4）解决方案（列出遇到的问题和解决方法，列出没有解决的问题）；

（5）收获和体会。

作业

use JXGL;
go
create view SVIEW1 as select sno,sname,sex,birth,homadd
from S
go
select * from SVIEW1

go
create view SVIEW2 as select sno,sname,homadd
from S
where sex = '男'
go
select * from SVIEW2


go 
create view DEPTVIEW1 as select top 5 dno,dname,dheader
from DEPT
order by snum desc

select * from DEPTVIEW1

go
create view SVIEW3 as select dno,AVG(SC.grade) as avggrede
from S,SC
where dno='D04'
group by dno

select * from SVIEW3

go
create view SVIEW4 as select S.sno,S.sname,C.cname,C.credit,C.tperiod,SC.grade,SC.time
from S,SC,C
where S.sno = SC.sno and SC.cno = C.cno
group by S.sno,S.sname,C.cname,C.credit,C.tperiod,SC.grade,SC.time

select * from SVIEW4 

go
create view SVIEW5 as select sname,cname,grade
from SVIEW4

select * from SVIEW5
where cname='数据库原理'



go
alter view SVIEW4 with encryption as 
select sname,cname,grade

 from S,SC,C
where S.sno = SC.sno and SC.cno = C.cno

go 
SP_HELPTEXT  SVIEW5

go
SP_RENAME   'SVIEW5',  'STUVIEW5'


go 
drop view STUVIEW5

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。