SQL Server实验——数据库视图的定义与使用

学生选课数据库的创建

构建学生信息表student、课程信息表course、学生选课信息表sc，并插入相关信息。

创建学生信息表student

学生信息表的要求如下：

SQL语句：

CREATE TABLE student
(Sno CHAR(9) PRIMARY KEY,
 Sname CHAR(20) UNIQUE,
 Ssex CHAR(2),
 Sage SMALLINT,
 Sdept CHAR(20)
);

创建课程信息表course

课程信息表的要求如下：

SQL语句：

CREATE TABLE course
(Cno CHAR(4) PRIMARY KEY,
 Cname CHAR(40) NOT NULL,
 Cpno CHAR(4),
 Ccredit SMALLINT,
 FOREIGN KEY(Cpno) REFERENCES course(Cno)
);

创建学生选课信息表sc

学生选课信息表的要求如下：

SQL语句：

CREATE TABLE sc
(Sno CHAR(9),
 Cno CHAR(4),
 Grade SMALLINT,
 PRIMARY KEY(Sno,Cno),
 FOREIGN KEY (Sno) REFERENCES student(Sno),
 FOREIGN KEY (Cno) REFERENCES course(Cno)
);

插入相关信息

一、向student表插入以下数据

insert into student(sno, sname, ssex, sage, sdept) values('200215121', '李勇', '男', 20, 'CS'); 
insert into student(sno, sname, ssex, sage, sdept) values('200215122', '刘晨', '女', 19, 'IS'); 
insert into student(sno, sname, ssex, sage, sdept) values('200215123', '王敏', '女', 18, 'MA'); 
insert into student(sno, sname, ssex, sage, sdept) values('200215124', '张立', '男', 19, 'IS'); 
insert into student(sno, sname, ssex, sage, sdept) values('200215125', '刘云', '女', 18, 'CS');
insert into student(sno, sname, ssex, sage, sdept) values('200215126', '王楠', '女', 19, 'CS');
insert into student(sno, sname, ssex, sage, sdept) values('200215127', '王小志', '男', 17, 'IS');
insert into student(sno, sname, ssex, sage, sdept) values('200215128', '王皇明', '男', 16, 'IS');

二、向course表插入以下数据

说明：
因为course表的cpno是外键，它是参照course表中的主键cno进行取值，故插入数据时可以按照数据依赖的关系进行数据的插入。本题可以按照cno=2,6,4,7,5,1,3,8的顺序执行下列的插入命令。

SQL语句：

insert into course(cno, cname, cpno, ccredit) values('2', '数学', null, 6 );
insert into course(cno, cname, cpno, ccredit) values('6', '数据处理', null, 3 );
insert into course(cno, cname, cpno, ccredit) values('4', '操作系统', '6', 4 );
insert into course(cno, cname, cpno, ccredit) values('7', 'PASCAL语言', '6', 4 );
insert into course(cno, cname, cpno, ccredit) values('5', '数据结构', '7', 4 );
insert into course(cno, cname, cpno, ccredit) values('1', '数据库', '5', 4);
insert into course(cno, cname, cpno, ccredit) values('3', '信息系统', '1', 3 );
insert into course(cno, cname, cpno, ccredit) values('8', '数据库原理', '5', 4 );

三、向sc表插入以下数据

insert into sc(sno, cno, grade) values('200215121', '1', 92);
insert into sc(sno, cno, grade) values('200215121', '2', 85);
insert into sc(sno, cno, grade) values('200215121', '3', 88);
insert into sc(sno, cno, grade) values('200215122', '2', 90);
insert into sc(sno, cno, grade) values('200215122', '3', 80);
insert into sc(sno, cno, grade) values('200215123', '2', 85);
insert into sc(sno, cno, grade) values('200215124', '1', 58);
insert into sc(sno, cno, grade) values('200215124', '2', 85);
insert into sc(sno, cno, grade) values('200215128', '2', 89);
insert into sc(sno, cno, grade) values('200215128', '3', 77);
insert into sc(sno, cno, grade) values('200215128', '1', 56);
insert into sc(sno, cno, grade) values('200215127', '2', 100);
insert into sc(sno, cno, grade) values('200215127', '3', 97);
insert into sc(sno, cno, grade) values('200215127', '1', 93);

视图的创建

基本应用

一、创建信息系（sdept=’IS’）学生信息的视图IS_Student，包含sno，sname，sage和sdept四个属性，并查看该视图的内容

SQL语句：

go
create view IS_Student(sno,sname,sage,sdept)
as
select sno,sname,sage,sdept
from student
where sdept='IS';
go

select * from IS_Student;               //查看该视图的内容

说明：
create view语句为为批处理中仅有的语句，故其前后都需要添加go。

二、创建信息系选修了1号课程的学生的视图IS_S1（至少包含sno、cno、 grade这几个属性），并同时给视图字段设定新的中文字段名（例如sno命名为“学号”），最后用SELECT语句查看该视图的内容

SQL语句：

go
create view IS_S1(学号,课程号,分数)
as
select student.sno 学号,cno 课程号,grade 分数
from student,sc
where sdept='IS' and
      student.sno=sc.sno and
	  sc.cno='1';
go

select * from IS_S1;              

三、基于之前创建的视图IS_S1建立信息系选修了1号课程且成绩在90分以上的学生的视图IS_S2，并查看该视图的内容

SQL语句：

go
create view IS_S2
as
select 学号,课程号,分数
from IS_S1
where 分数>=90;
go

select * from IS_S2;             

四、将student表中所有女生的记录定义为一个视图F_student，并查看该视图的内容

SQL语句：

go
create view F_student
as
select *
from student
where ssex='女';
go

select * from F_student;         

五、将所有学生的学号和他所有选修课程的平均成绩定义为一个视图S_G，并查看该视图的内容

SQL语句：

go
create view S_G(sno,gavy)
as
select sno,AVG(grade)
from sc
group by sno;
go
select * from S_G;       

注：函数AVG（）会自动求平均值。

利用函数创建视图

利用函数创建一个“视图”myAgeFunView，要求能够显示年龄在某个指定年龄段内的学生的信息。年龄段由2个参数来定义。然后利用该创建好的“视图”，查询年龄在[17, 19]年龄段内的学生信息。

SQL语句：

go
create function myAgeFunView(@a float,@b float) returns table
as
return
(select *
       from student
	   where sage>=@a and sage<=@b
);
go

select * from myAgeFunView(17,19);   //查询年龄在[17, 19]年龄段内的学生信息

视图的修改

视图修改的基本语法：
alter view [视图名]
as
新的select 语句

应用：将视图 F_student 修改为计算机系（sdept=’CS’）的所有女生的视图，并查看该视图的内容。

SQL语句：

go
alter view F_student
as
select *
from student
where ssex='女' and sdept='CS';
go

select * from F_student;

说明: 引入视图结构修改的目的是为避免与视图相关的数据库对象的变换，如触发器，关联等。

视图的查询

一、基于信息系的学生视图IS_Student中查询信息系年龄小于20岁的学生的信息

SQL语句：

select * from IS_Student where sage<20;

二、基于信息系的学生视图IS_Student中查询信息系选修了1号课程的学生的学号和姓名

SQL语句：

select IS_Student.sno,sname 
from IS_Student,sc 
where IS_Student.sno=sc.sno and sc.cno='1';

三、将信息系学生视图 IS_Student 中学号为 ‘200215122’的学生姓名改为 ‘侯亮平’，并查看该视图的内容以及该视图所关联的基表student的内容

注意查看基表student中的学号为 ‘200215122’的学生姓名是否因为视图的数据更新操作而影响被改为‘侯亮平’？

SQL语句：

update IS_Student 
set sname='侯亮平'
where sno='200215122';
select * from IS_Student;
select * from student;

结果显示，基表student中的学号为 ‘200215122’的学生姓名也因为视图的数据更新操作而影响被改为‘侯亮平’。

四、向信息系学生视图IS_Student中插入一个新的学生记录，学号为 200215129，姓名为“沙瑞金”，年龄为54岁，所在系为“IS”。并查询IS_student 视图看是否包含该记录

SQL语句：

insert into IS_Student values('200215129','沙瑞金',54,'IS');
select * from IS_Student;

五、向信息系学生视图IS_Student中插入一个新的学生记录，学号为 200215139，姓名为“易学习”，年龄为48岁，所在系为“CS”，并查看视图IS_Student及其基表student中是否有该记录

SQL语句：

insert into IS_Student values('200215139','易学习',48,'CS');
select * from IS_Student;
select * from student;

说明：
插入失败。视图IS_Student中并无该记录，因为视图IS_Student在定义之初已选定sdept的值为IS。由于插入新的学生记录时，其sdept的值为CS，重新查看视图IS_Student时，该记录已被排除。故查看视图IS_Student后并无该记录。而查看视图IS_Student的基表student时，会有该记录。该记录的sdept值为CS。

六、向视图S_G中插入一条新记录，学号为201211339，平均分为88，是否能插入成功

SQL语句：

insert into S_G values('201211339',88);

说明：
结果显示“插入失败，因其包含派生域或常量域”。平均成绩是由student表中对元祖分组后计算平均值所得的结果，视图定义中含有group by子句，故该视图不允许更新。

注意：
（1）视图由两个以上基本表导出，则该视图不允许更新
（2）视图的字段来自字段表达式或常数，则不允许对此视图执行insert和update操作，但允许执行delete操作
（3）视图的字段来自聚集函数，则此视图不允许更新
（4）视图定义中含有group by子句，则该视图不允许更新
（5）视图定义中含有distinct短语，则该视图不允许更新
（6）视图定义中有嵌套查询，且内层查询的from子句中涉及的表也是导出该视图的基本表，则该视图不允许更新
（7）一个不允许更新的视图上定义的视图亦不允许更新

七、首先删除sc表；然后删除信息系学生视图 IS_Student 中学号为 200215139 的学生的记录，并查看操作后该视图的基表student中是否还包含该记录

SQL语句：

drop table sc;
delete 
from IS_Student
where sno='201215129';
select * from student;

结果显示，该视图的基表student中仍然包含该记录。

删除视图

应用：删除视图 S_G

SQL语句：

drop view S_G;

查看视图

使用如下SQL命令查看当前数据库中的所有用户定义的视图：select name ‘视图名称（当前数据库）’ from sys.views

当我们用CREATE VIEW语句创建视图时，有关该视图的信息将存储在系统的目录视图sys.views中，可以试着使用如下SQL命令查看当前数据库中的所有用户定义的视图。

select name ‘视图名称（当前数据库）’ from sys.views;

SQL语句：

select name 'db_xxx' from sys.views;

调用系统存储过程sp_helptext（系统存储过程类似于系统的函数），实现查看视图 F_student的定义代码的功能

SQL语句：

exec sp_helptext F_student;

参考文献

1、数据库系统概论（第5版）.王珊 萨师煊 著
2、老师给的实验报告模板