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

学生选课数据库的创建创建学生信息表student创建课程信息表course创建学生选课信息表sc插入相关信息视图的创建基本应用一、创建信息系（sdept=’IS’）学生信息的视图IS_Student，包含sno，sname，sage和sdept四个属性，并查看该视图的内容二、创建信息系选修了1号课程的学生的视图IS_S1（至少包含sno、cno、 grade这几个属性），并同时给视图字段设定新的中文字段名（例如sno命名为“学号”），最后用SELECT语句查看该视图的内容三、基于之前创建的视图IS_S1建立信息系选修了1号课程且成绩在90分以上的学生的视图IS_S2，并查看该视图的内容四、将student表中所有女生的记录定义为一个视图F_student，并查看该视图的内容五、将所有学生的学号和他所有选修课程的平均成绩定义为一个视图S_G，并查看该视图的内容利用函数创建视图视图的修改应用：将视图 F_student 修改为计算机系（sdept=’CS’）的所有女生的视图，并查看该视图的内容。视图的查询一、基于信息系的学生视图IS_Student中查询信息系年龄小于20岁的学生的信息二、基于信息系的学生视图IS_Student中查询信息系选修了1号课程的学生的学号和姓名三、将信息系学生视图 IS_Student 中学号为 ‘200215122’的学生姓名改为 ‘侯亮平’，并查看该视图的内容以及该视图所关联的基表student的内容四、向信息系学生视图IS_Student中插入一个新的学生记录，学号为 200215129，姓名为“沙瑞金”，年龄为54岁，所在系为“IS”。并查询IS_student 视图看是否包含该记录五、向信息系学生视图IS_Student中插入一个新的学生记录，学号为 200215139，姓名为“易学习”，年龄为48岁，所在系为“CS”，并查看视图IS_Student及其基表student中是否有该记录六、向视图S_G中插入一条新记录，学号为11339，平均分为88，是否能插入成功七、首先删除sc表；然后删除信息系学生视图 IS_Student 中学号为 200215139 的学生的记录，并查看操作后该视图的基表student中是否还包含该记录删除视图应用：删除视图 S_G查看视图使用如下SQL命令查看当前数据库中的所有用户定义的视图：select name '视图名称（当前数据库）' from sys.views调用系统存储过程sp_helptext（系统存储过程类似于系统的函数），实现查看视图 F_student的定义代码的功能参考文献

学生选课数据库的创建

构建学生信息表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语句：

gocreate view IS_Student(sno,sname,sage,sdept)asselect sno,sname,sage,sdeptfrom studentwhere sdept='IS';goselect * from IS_Student;//查看该视图的内容

说明：

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

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

SQL语句：

gocreate view IS_S1(学号,课程号,分数)asselect student.sno 学号,cno 课程号,grade 分数from student,scwhere sdept='IS' andstudent.sno=sc.sno o='1';goselect * from IS_S1;

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

SQL语句：

gocreate view IS_S2asselect 学号,课程号,分数from IS_S1where 分数>=90;goselect * from IS_S2;

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

SQL语句：

gocreate view F_studentasselect *from studentwhere ssex='女';goselect * from F_student;

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

SQL语句：

gocreate view S_G(sno,gavy)asselect sno,AVG(grade)from scgroup by sno;goselect * from S_G;

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

利用函数创建视图

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

SQL语句：

gocreate function myAgeFunView(@a float,@b float) returns tableasreturn(select *from studentwhere sage>=@a and sage<=@b);goselect * from myAgeFunView(17,19); //查询年龄在[17, 19]年龄段内的学生信息

视图的修改

视图修改的基本语法：

alter view [视图名]

as

新的select 语句

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

SQL语句：

goalter view F_studentasselect *from studentwhere ssex='女' and sdept='CS';goselect * 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 o='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中插入一条新记录，学号为11339，平均分为88，是否能插入成功

SQL语句：

insert into S_G values('11339',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_Studentwhere sno='15129';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、老师给的实验报告模板