educoder 数据库系统概论2022 视图的创建和使用

任务描述

本关任务：掌握视图的创建和使用。

相关知识

视图是什么

视图是一个虚拟表，其结构和内容是通过 SQL 查询获得的。用户可以通过 SQL 查询语句，像其他普通关系表一样，对视图中的数据进行查询。视图同样支持表的相关操作，并可以直接修改、添加、删除数据库中的真实数据。

视图通常用来集中、简化和自定义不同用户对数据库的不同认识，如视图可用作安全机制。方法是只允许用户由视图访问数据，而不授予用户直接访问数据表的权限。视图还可以用于提供向后兼容接口，来模拟曾经存在，但其架构已更改的表。

视图的优缺点

视图的优点

• 安全保密。通过视图，用户只能查询和修改他们所能见到的数据，对于数据库中的其他数据，则既看不见也取不到。通过视图，用户可以被限制在数据的不同子集上。

• 查询简单。视图能够从几个不同的关系表中提取数据，并且用一个单表，表示出来。利用视图，将多表查询转换成视图的单表查询。

• 结构简单。视图能够给用户一个“个性化”的数据库结构外观，用一组用户感兴趣的可见表，来代表这个数据库的内容。

• 隔离变化。视图能够代表一个一致的、非变化的数据。即使是在作为视图基础的数据表（基表），被分割、重新构造或者重命名的情况下，也是如此。

• 逻辑数据独立。视图可以使应用程序和数据库表在一定程度上独立。如果没有视图，应用一定是建立在表上。有了视图之后，程序可以建立在视图上，从而使程序独立于数据表。

视图的缺点

虽然视图存在上述优点，但在定义数据库对象时，不能不加选择地来定义视图，因为视图也存在一些缺点：

• 性能。 SQL Server 必须把视图查询转化成对基本表的查询，如果这个视图是由一个复杂的多表查询所定义，那么，即使是视图的一个简单查询， SQL Server 也要把它变成一个复杂的结合体，需要花费一定的时间。

• 修改限制。当用户试图修改视图的某些信息时，数据库必须把它转化为对基本表的某些信息的修改，对于简单的视图来说，这是很方便的，但对于比较复杂的视图，可能是不可修改的。

操作视图

创建视图

使用 T-SQL 命令，创建视图的基本语法如下：

--语法
CREATE VIEW view_name
AS
SELECT column_name(s) FROM table_name
WHERE condition

view_name ：指定创建视图的名称；
column_name ：指定视图中列名称；
table_name ：视图的基础表名称；
condition ： select 的限定条件。

下面是一个创建视图的实例：

--准备表和数据
create table studentTable(
id int identity(1,1) primary key,
name varchar(20),
gender char(2),
age int,
)

insert into studentTable (name,gender,age)
values
('刘备','男',28),
('张飞','男',24),
('关羽','男',26);

--删除已存在的视图student_view
if (exists (select * from sys.objects where name = 'student_view'))
drop view student_view
go

--创建视图student_view
create view student_view
as
select name,age from studentTable where age>24;

--查询视图
select * from student_view;

通过创建的视图查询的结果：

通过视图向基本表中插入数据

--通过视图向基本表中插入数据
create view stu_insert_view(编号,姓名,性别,年龄)
as
select id,name,gender,age from studentTable;
go

select * from studentTable;

---插入一条数据
insert into stu_insert_view values('曹操2','男',40);

----查看插入记录之后表中的内容。
select * from studentTable;

插入数据之前表中的数据如下：

通过视图插入数据后，表中的数据如下：

通过视图修改基本表的数据

--查看修改之前的数据
select * from studentTable;

--修改数据
update student_view set age=30 where name='刘备';

--查看修改后的数据
select * from studentTable;

修改视图之前表 studentTable 中的数据如下：

通过视图修改表 studentTable 中的数据后如下：

通过视图删除基本表的数据

通过视图删除基本表数据的语法如下：

delete view_name where condition;

--查看表中源数据
select * from studentTable;

--删除记录
delete student_view where name ='关羽';

--通过视图删除之后的数据
select * from studentTable;

删除之前的数据：

删除之后的数据：

修改视图

可以使用 ALTER VIEW 语句来对已有的视图进行修改。

ALTER VIEW view_name AS select_statement
语法说明如下：

view_name ：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。

select_statement ：指定创建视图的 SELECT 语句，可用于查询多个基表或源视图。

例如：


--修改视图
alter view student_view
as
select name,age from studentTable where age>30;

此时再执行select * from student_view会得到如下年龄大于 30 的记录： 

删除视图

可以使用 drop view 语句删除视图：

drop view view_name1,view_name2,......,view_nameN;
--该语句可以同时删除多个视图，只要在删除各视图名称之间用逗号分隔即可。
例如


drop view student_view;

编程要求

本次编程任务是，在右侧编辑区 create score_view_cdept 下的 Begin-End 区域补全代码，创建学生的成绩单视图，包括学号 sno 、姓名 name 、课程名 cname 和成绩 grade 字段。

涉及到的数据库和表都已经建立好了，并插入了相应的数据，具体如下：
Student 表结构如下：

sno	name	sex	birthday	discipline	school
s001	Tom	male	2019-06-01	computer	information
s002	Bob	male	2019-06-01	software	information
s003	Alice	female	2019-05-02	computer	information
Course 表结构如下：

cno	cname	description	credit	cinstitution
c001	network	computer network description	2	College Of Information Engineering
c002	Java	java description	2	College Of Information Engineering

Score 表结构如下：

sno	cno	grade
s001	c001	85
s002	c001	90

测试说明

平台将对你补充的代码进行评测，输出结果正确才可通关。

预期输出：

---

开始你的任务吧，祝你成功！

参考代码

 

USE studentdb
go

SET NOCOUNT ON 
go

--********** create score_view_cdept **********--
--********** Begin **********--
create view score_view_cdept
as
select Student.sno,name,cname,grade 
from Student,Course,Score
where Student.sno=Score.sno and Course.cno=Score.cno


--********** End **********--
go

这里我们学会在处理表内有相同属性名时，要指明该属性属于哪一个表。