任务描述
本关任务:掌握视图的创建和使用。
相关知识
视图是什么
视图是一个虚拟表,其结构和内容是通过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至create 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
测试说明
平台将对你编写的 SQL 进行评测:
预期输出:
开始你的任务吧,祝你成功!
USE studentdb
go
SET NOCOUNT ON
go
/********** Begin **********/
--********** create score_view_cdept **********--
create view score_view_cdept
as
select student.sno as sno,student.name as name,course.cname as cname,score.grade as grade
from student
join score on student.sno=score.sno
join course on score.cno=course.cno
--********** create end **********--
/********** End **********/
go
注意as,join语句的使用