数据库实验指导书2015 (1)

《数据库概论》实验指导书

中北大学 软件学院 2015/3/31

《数据库概论》实验

实验类别： 课内实验 实验课程名称： 数据库概论 实验室名称：软件工程实验室 实验课程编号： 总学时： 56 学 分： 适用专业： 软件工程

先修课程： 离散数学、计算机导论、数据结构与算法 实验在教学培养计划中地位、作用：

《数据库概论》是一门理论与实践相结合的课程，上机实验环节是本课程的重要组成部分。实验的目的是为了配合课堂教学，进一步强化对数据库原理的理解。实验的任务是要结合数据库原理和 SQL Server 2005/2008数据库系统，熟练掌握和深入理解课堂教学内容，实现从理论到实践的统一。

实验一 SQL的数据定义与单表查询（2学时）

1、实验目的

(1) 掌握DBMS 的数据定义功能 (2) 掌握SQL 语言的数据定义语句 (3) 掌握RDBMS 的数据单表查询功能 (4) 掌握SQL 语言的数据单表查询语句

2、实验内容

(1) 创建、删除表

(2) 查看、修改表的定义 (3) 理解索引的特点 (4) 创建和删除索引

(5)SELECT语句的基本用法

(6)使用WHERE 子句进行有条件的查询

(7)使用IN ，NOT IN，BETWEEN AND等谓词查询 (8)利用LIKE 子句实现模糊查询 (9)利用ORDER BY子句为结果排序

(10)用SQL Server的聚集函数进行统计计算 (11)用GR0UP BY子句实现分组查询的方法

3、实验要求

(1) 熟练掌握SQL 的数据定义语句CREATE 、ALTER 、DROP (2) 熟练掌握SQL 的数据查询语句SELECT (3) 写出实验报告

4、实验步骤

设有一个学生-课程数据库，包括学生关系Student 、课程关系Course 和选修关系SC ：学生表：Student(Sno，Sname ，Ssex ，Sage ，Sdept) 课程表：Course(Cno，Cname ，Cpno ，Ccredit) 学生选课表：SC(Sno，Cno ，Grade)

(1) 用查询分析器创建、删除表，例如： Create Database S_T1; CREATE TABLE Student

(Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20) UNIQUE, Ssex CHAR(1) , Sage INT,

Sdept CHAR(15))

create table Course

(Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT,

FOREIGN KEY (Cpno) REFERENCES Course(Cno) );

CREATE TABLE SC( Sno CHAR(5), Cno CHAR(3), Grade int,

Primary key (Sno, Cno));

DROP TABLE Student

(2) 查看、修改表的定义，例如：

ALTER TABLE Student ADD Scome DATETIME

ALTER TABLE Student ALTER COLUMN Sage SMALLINT (3) 创建和删除索引

CREATE UNIQUE INDEX Stusno ON Student(Sno)； CREATE UNIQUE INDEX Coucno ON Course(Cno)；

CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC)； DROP INDEX Stusno (4) 删除表

DROP TABLE SC;

DROP TABLE STUDENT; DROP TABLE COURSE;

(5)利用SQL Server集成管理器（简称SSMS ）交互式创建数据库S_T2； (6)将S_T设为当前数据库

例如：use S_T2; (7)创建3个表

利用SQL 语句中的Create Table命令创建表 create table Student

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

/*表Student 的主码为Sno ，属性列Sname 取唯一值*/ create table Course

(Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT,

FOREIGN KEY (Cpno) REFERENCES Course(Cno) ); go

/*表Course 的主码为Cno ，属性列Cpno(先修课) 为外码，被参照表为Course ，被参照列是Cno*/

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) ); go

/*表SC 的主码为(Sno, Cno), Sno 和Cno 均为外码，被参照表分别为Student 和Course ，被参照列分别为Student.Sno 和Course.Cno*/

(8)在3个表中添加示例数据（任选一种数据添加方法）

① 用SQL 语句中的更新语句（Insert 语句、Update 语句和Delete 语句）往3个表输入示例数据。

use S_T;/*将S_T设为当前数据库*/

insert into student values('200215121','李勇',' 男',20,'CS'); insert into student values('200215122','刘晨',' 女',19,'CS'); insert into student values('200215123','王敏',' 女',18,'MA'); insert into student values('200215125','张立',' 男',19,'IS'); go

/*为表Student 添加数据*/

insert into course values('1', '数据库', NULL,4); insert into course values('2', '数学', NULL,2); insert into course values('3', '信息系统', NULL,4); insert into course values('4', '操作系统', NULL,3); insert into course values('5', '数据结构', NULL,4); insert into course values('6', '数据处理', NULL, 2); insert into course values('7', 'java', NULL,4); go

update Course set Cpno = '5' where Cno = '1'; update Course set Cpno = '1' where Cno = '3'; update Course set Cpno = '6' where Cno = '4'; update Course set Cpno = '7' where Cno = '5'; update Course set Cpno = '6' where Cno = '7'; /*为表Course 添加数据*/

go

insert into SC values('200215121', '1',92); insert into SC values('200215121', '2',85); insert into SC values('200215121', '3',88); insert into SC values('200215122', '2',90); insert into SC values('200215122', '3',80); /*为表SC 添加数据*/ go ② 利用SQL Server集成管理器（简称SSMS ）交互式输入数据。

(9) 对学生关系Student 、课程关系Course 和选修关系SC 进行查询。

基本练习

1、SELECT 语句的基本用法

例如：查询全体学生的详细记录。 SELECT Sno,Sname,Ssex,Sage,Sdept FROM Student

2、使用WHERE 子句进行有条件的查询

例如：查询选修2号课程且成绩在90分以上的所有学生的学号、姓名 SELECT Student.Sno, student.Sname FROM Student, SC

WHERE Student.Sno = SC.Sno AND SC.Cno= ' 2 ' AND SC.Grade > 90 3、 使用IN ，NOT IN，BETWEEN 等谓词查询

例如：查询信息系（IS ）、数学系（MA ）和计算机科学系（CS ）学生的姓名和性别。 SELECT Sname,Ssex FROM Student

WHERE Sdept IN ( 'IS','MA','CS' )

例如：查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。 SELECT Sname,Sdept,Sage FROM Student

WHERE Sage BETWEEN 20 AND 23 4、利用LIKE 子句实现模糊查询

例如：查询所有姓刘学生的姓名、学号和性别。 SELECT Sname,Sno,Ssex FROM Student

WHERE Sname LIKE '刘%' 5、利用ORDER 子句为结果排序

例如：查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。 SELECT Sno,Grade FROM SC

WHERE Cno= '3' ORDER BY Grade DESC

6、用SQL Server的统计函数进行统计计算 例如：计算1号课程的学生平均成绩。 SELECT AVG(Grade) FROM SC

WHERE Cno= '1'

7、用GR0UP BY子句实现分组查询的方法

例如：查询选修了3门以上课程的学生学号。 SELECT Sno FROM SC GROUP BY Sno

HAVING COUNT(*) >3

扩展练习（要求写出并执行SQL 语句来完成以下各种操作，记录查询结果）

（1）查询全体学生的学号、姓名和年龄； （2）查询所有计算机系学生的详细记录；

（3）找出考试成绩为优秀（90分及以上）或不及格的学生的学号、课程号及成绩； （4）查询年龄不在19~20岁之间的学生姓名、性别和年龄； （5）查询数学系（MA ）、信息系（IS) 的学生的姓名和所在系；

（6）查询名称中包含“数据”的所有课程的课程号、课程名及其学分； （7) 找出所有没有选修课成绩的学生学号和课程号；

（思考：如何查询所有没有选修课成绩的计算机系的学生学号和课程号？） （8）查询学生200215121选修课的最高分、最低分以及平均成绩；

（9）查询选修了2号课程的学生的学号及其成绩，查询结果按成绩升序排列； （10）查询每个系名及其学生的平均年龄。 （思考：如何查询学生平均年龄在19岁以下（含19岁）的系别及其学生的平均年龄？）

实验二 SQL 的多表数据查询（2学时）

1、实验目的

(1) 掌握RDBMS 的数据多表查询功能 (2) 掌握SQL 语言的数据多表查询语句

2、实验内容

(1) 等值连接查询（含自然连接查询）与非等值连接查询 (2) 自身连接查询 (3) 外连接查询

(4) 复合条件连接查询

(5) 嵌套查询（带有IN 谓词的子查询） (6) 嵌套查询（带有比较运算符的子查询） (7) 嵌套查询（带有ANY 或ALL 谓词的子查询） (8) 嵌套查询（带有EXISTS 谓词的子查询） (9) 集合查询

3、实验要求

(1) 熟练掌握SQL 的连接查询语句 (2) 熟练掌握SQL 的嵌套查询语句 (3) 掌握表名前缀、别名前缀的用法

(4) 掌握不相关子查询和相关子查询的区别和用法

(5) 掌握不同查询之间的等价替换方法（一题多解）及限制 (6) 记录实验结果，认真完成实验报告

4、实验步骤

4.1 建立示例数据库S_T（复习）

在SQL Server集成管理器的查询窗口中输入如下SQL 语句序列来创建示例数据库。 /* 创建示例数据库S_T，包括3个表，即学生表Student 、课程表Course 和选课表SC*/ create database S_T; go

use S_T; /*将S_T设为当前数据库*/ create table Student

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

/*表Student 的主码为Sno ，属性列Sname 取唯一值*/ create table Course

(Cno CHAR(4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT,

FOREIGN KEY (Cpno) REFERENCES Course(Cno) ); go

/*表Course 的主码为Cno ，属性列Cpno(先修课) 为外码，被参照表为Course ，被参照列是Cno*/ 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) ); go

/*表SC 的主码为(Sno, Cno), Sno和Cno 均为外码，被参照表分别为Student 和Course ，被参照列分别为Student.Sno 和Course.Cno*/

insert into student values('200215121','李勇',' 男',20,'CS'); insert into student values('200215122','刘晨',' 女',19,'CS'); insert into student values('200215123','王敏',' 女',18,'MA'); insert into student values('200215125','张立',' 男',19,'IS');

go

/*为表Student 添加数据*/

insert into course values('1', '数据库', NULL,4); insert into course values('2', '数学', NULL,2); insert into course values('3', '信息系统', NULL,4); insert into course values('4', '操作系统', NULL,3); insert into course values('5', '数据结构', NULL,4); insert into course values('6', '数据处理', NULL, 2); insert into course values('7', 'PASCAL语言', NULL,4); go

update Course set Cpno = '5' where Cno = '1'; update Course set Cpno = '1' where Cno = '3'; update Course set Cpno = '6' where Cno = '4'; update Course set Cpno = '7' where Cno = '5'; update Course set Cpno = '6' where Cno = '7'; /*为表Course 添加数据*/ go

insert into SC values('200215121', '1',92); insert into SC values('200215121', '2',85); insert into SC values('200215121', '3',88); insert into SC values('200215122', '2',90); insert into SC values('200215122', '3',80); /*为表SC 添加数据*/ go

也可以将上述SQL 语句序列预先保存在S_T.sql文件中，在SSMS 中打开并执行该文件中的sql 语句序列。

4.2 对学生关系Student 、课程关系Course 和选修关系SC 进行多表查询 基本练习

（1）等值连接查询与自然连接查询

例如：查询每个学生及其选修课的情况。

SELECT Student.*, SC.* FROM Student, SC

WHERE Student.Sno = SC.Sno; /* 一般等值连接 */ 又如：查询每个学生及其选修课的情况（去掉重复列）。

SELECT Student.Sno, Sname, Ssex, Sage, Cno, Grade FROM Student, SC

WHERE Student.Sno = SC.Sno; /* 自然连接--特殊的等值连接 */ （2）自身连接查询

例如：查询每一门课的间接先修课。

SELECT FIRST.Cno, SECOND.Cpno FROM Course FIRST, Course SECOND WHERE FIRST.Cpno = SECOND.Cno; （3）外连接查询

例如：查询每个学生及其选修课的情况（要求输出所有学生--含未选修课程的学生的情况）

SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, Grade FROM Student LEFT OUTER JOIN SC ON(Student.Sno = SC.Sno); （4）复合条件连接查询

例如：查询选修了2号课程而且成绩在90以上的所有学生的学号和姓名。

SELECT Student.Sno, Sname FROM Student, SC

WHERE Student.Sno = SC.Sno AND

SC.Cno = ‘2' AND SC.Grade >= 90;

又如：查询每个学生的学号、姓名、选修的课程名及成绩。

SELECT Student.Sno, Sname, Cname, Grade FROM Student, SC, Course

WHERE Student.Sno = SC.Sno AND SC.Cno = Course.Cno; （5）嵌套查询（带有IN 谓词的子查询）

例如：查询与“刘晨”在同一个系学习的学生的学号、姓名和所在系。

SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN

(SELECT Sdept FROM Student

WHERE Sname = '刘晨'); /* 解法一*/ 可以将本查询中的IN 谓词用比较运算符‘=’来代替：

SELECT Sno, Sname, Sdept FROM Student WHERE Sdept =

(SELECT Sdept FROM Student

WHERE Sname = '刘晨'); /* 解法二*/ 也可以使用自身连接完成以上查询：

SELECT s1.Sno, s1.Sname, s1.Sdept FROM Student s1, Student s2 WHERE s1.Sdept = S2.Sdept AND

s2.Sname = '刘晨'; /* 解法三*/ 还可以使用EXISTS 谓词完成本查询：

SELECT Sno, Sname, Sdept FROM Student S1 WHERE EXISTS (SELECT *

FROM Student S2

WHERE S2.Sdept=S1.Sdept AND S2.Sname='刘晨'); /* 解法四*/

又如：查询选修了课程名为“信息系统”的学生号和姓名。

SELECT Sno, Sname FROM Student WHERE Sno IN

(SELECT Sno FROM SC

WHERE Cno IN

(SELECT Cno

FROM Course

WHERE Cname = '信息系统'

)

);

也可以使用连接查询来完成上述查询：

SELECT Student.Sno, Sname

FROM Student, SC, Course

WHERE Student.Sno = SC.Sno AND

SC.Cno = Course.Cno AND

Course.Cname = '信息系统';

（6）嵌套查询（带有比较运算符的子查询）

例如：找出每个学生超过他所选修课程平均成绩的课程号。

SELECT Sno, Cno

FROM SC x

WHERE Grade >= ( SELECT AVG(Grade)

FROM SC y

WHERE y.Sno = x.Sno);

（7）嵌套查询（带有ANY 或ALL 谓词的子查询）

例如：查询其他系中比计算机系某个学生年龄小的学生的姓名和年龄。

SELECT Sname, Sage

FROM Student

WHERE Sage

FROM Student

WHERE Sdept = 'CS')

AND Sdept 'CS';

本查询也可以使用聚集函数来实现：

SELECT Sname, Sage

FROM Student

WHERE Sage

FROM Student

WHERE Sdept = 'CS')

AND Sdept 'CS';

又如：查询其他系中比计算机系所有学生年龄都小的学生的姓名和年龄。

SELECT Sname, Sage

FROM Student

WHERE Sage

FROM Student

WHERE Sdept = 'CS')

AND Sdept 'CS';

也可以使用聚集函数来实现：

SELECT Sname, Sage

FROM Student

WHERE Sage

FROM Student

WHERE Sdept = 'CS')

AND Sdept 'CS';

（8）嵌套查询（带有EXISTS 谓词的子查询）

例如：查询所有选修了1号课程的学生姓名。

SELECT Sname

FROM Student

WHERE EXISTS

(SELECT *

FROM SC

WHERE Sno=Student.Sno AND Cno='1');

又如：查询所有未选修1号课程的学生姓名。

SELECT Sname

FROM Student

WHERE NOT EXISTS

(SELECT *

FROM SC

WHERE Sno=Student.Sno AND Cno='1');

可以使用带有EXISTS 谓词的子查询实现全称量词或蕴涵逻辑运算功能：

例如：查询选修了全部课程的学生姓名。

SELECT Sname

FROM Student

WHERE NOT EXISTS

(SELECT *

FROM Course

WHERE NOT EXISTS

(SELECT *

FROM SC

WHERE Sno=Student.Sno AND

Cno=Course.Cno));

又如：查询至少选修了学生200215122选修的全部课程的学生号码。

SELECT DISTINCT Sno

FROM SC SCX

WHERE NOT EXISTS

(SELECT *

FROM SC SCY

WHERE SCY.Sno='200215122' AND

NOT EXISTS

(SELECT *

FROM SC SCZ

WHERE SCZ.Sno=SCX.Sno AND

SCZ.Cno=SCY.Cno));

（9）集合查询

例如：查询计算机系的学生以及年龄不大于19岁的的学生。

SELECT *

FROM Student

WHERE Sdept='CS'

UNION /*并集运算*/

SELECT *

FROM Student

WHERE Sage

可以改用多重条件查询：

SELECT *

FROM Student

WHERE Sdept='CS' OR Sage

又如：查询既选修了课程1又选修了课程2的学生（交集运算）。

SELECT Sno

FROM SC

WHERE Cno='1'

INTERSECT /*交集运算*/

SELECT Sno

FROM SC

WHERE Cno='2';

可以使用嵌套查询：

SELECT Sno

FROM SC

WHERE Cno='1' AND Sno IN

(SELECT Sno

FROM SC

WHERE Cno='2');

思考：能不能改用多重条件查询？

SELECT Sno

FROM SC

WHERE Cno='1' AND Cno='2';

再如：查询计算机系的学生与年龄不大于19岁的学生的差集。

SELECT *

FROM Student

WHERE Sdept='CS'

EXCEPT /*差集运算*/

SELECT *

FROM Student

WHERE Sage

可以改用多重条件查询：

SELECT *

FROM Student

WHERE Sdept='CS' AND Sage>19;

扩展练习（要求写出并执行SQL 语句完成以下各种操作，记录查询结果）

（1）查询每门课程及其被选情况（输出所有课程中每门课的课程号、课程名称、选修该课程的学生学号及成绩--如果没有学生选择该课，则相应的学生学号及成绩为空值）。

（2）查询与“张立”同岁的学生的学号、姓名和年龄。（要求使用至少3种方法求解）

（3）查询选修了3号课程而且成绩为良好（80~89分）的所有学生的学号和姓名。

（4）查询学生200215122选修的课程的课程号、课程名

（思考：如何查询学生200215122选修的课程的课程号、课程名及成绩？）

（5）找出每个学生低于他所选修课程平均成绩5分以上的课程号。（输出学号和课程号）

（6）查询比所有男生年龄都小的女生的学号、姓名和年龄。

（7）查询所有选修了2号课程的学生姓名及所在系。

实验三SQL 的数据更新（2学时）

1、实验目的

(1) 掌握DBMS 的数据查询功能

(2) 掌握SQL 语言的数据更新功能

2、实验内容

(1) update 语句用于对表进行更新

(2) delete 语句用于对表进行删除

(3) insert 语句用于对表进行插入

3、实验要求

(1) 熟练掌握SQL 的数据更新语句INSERT 、UPDATE 、DELETE

(2) 写出实验报告

4、实验步骤

4.1 认真阅读S_T.sql，理解其中插入和修改语句的作用和用法；执行S_T.sql，建立示例数据库S_T。

4.2 对学生关系Student 、课程关系Course 和选修关系SC 进行数据插入、修改和删除元组（记录）的更新操作。

基本练习

（1）插入一个元组

练习1：将一个新学生元组（200215140，田刚，男，计算机系，19岁）插入Student 表中。 INSERT

INTO Student (Sno,Sname,Ssex,Sdept, Sage)

VALUES('200215140','田刚',' 男' ，'CS',19);

练习2：插入一条选课记录（‘200215140’, ‘3’）。

INSERT

INTO SC

VALUES('200215140','3',NULL);

也可以使用如下SQL 语句：

INSERT

INTO SC(Sno,Cno)

VALUES('200215140','3');

思考：这两种用法有什么区别？（但最终效果一样！）

（2）插入子查询结果

练习3：对每一个学生，求其平均成绩，并把结果存入新表Sno_grade。

/*首先创建新表Sno_grade：*/

CREATE TABLE Sno_grade

( Sno char (9) primary key ,

Avg_grade SMALLINT );

go

/*然后对SC 表按学号分组求平均成绩，再把学号和平均成绩存入新表中：*/

INSERT INTO Sno_grade(Sno , Avg_grade)

SELECT Sno, A VG (grade )

FROM SC

GROUP BY Sno;

（3）修改某一个元组的值

练习4：将学生200215131的姓名改为“张莉莉”

UPDATE Student

SET Sname='张莉莉'

WHERE Sno='200215131';

（4）修改多个元组的值

练习5：将所有计算机系学生的年龄统一增加1岁。

UPDATE Student

SET Sage=Sage+1

WHERE Sdept='CS';

（5）带子查询的修改语句

练习6：将计算机系全体学生的成绩统一增加2分。

UPDATE SC

SET Grade=Grade+2

WHERE 'CS'=

(SELECT Sdept

FROM Student

WHERE Student.Sno = SC.Sno);

（6）删除某一个元组的值

练习7：删除学号为200215133的学生记录。

DELETE

FROM Student

WHERE Sno='200215133';

（7）删除多个元组的值

练习8：删除表Sno_grade中平均成绩低于90分（不含90分）的记录。

DELETE

FROM Sno_grade

WHERE Avg_grade

练习9：删除表Sno_grade中全部记录。

DELETE

FROM Sno_grade

思考：删除一个表中的全部元组后，该表的定义是否被同时删除？

（8）带子查询的删除语句

练习10：删除计算机系所有学生的选课记录。

DELETE

FROM SC

WHERE 'CS'=

(SELECT Sdept

FROM Student

WHERE Student.Sno = SC.Sno);

扩展练习

首先执行S_T.sql，重建示例数据库S_T。

然后写出并执行SQL 语句完成以下各种操作，记录查询结果。

（1）在Student 表中插入计算机系的两个新生元组：（学号：200215180；姓名：王新宇；性别：男；年龄：19岁），（学号：200215181；姓名：丛欣然；性别：女；年龄：18岁）。

（2）对每一门课，求其选课人数，并把结果（课程号，选课人数）存入新表Cno_Number。

（3）将计算机系CS 的所有学生的系别改为软件工程系SE 。

（4）将软件工程系SE 的全体学生的成绩统一减去2分。

（5）删除学号为200215180的学生记录。

（6）将信息系IS 所有学生的成绩置为空值（非0值）。

（7）将所有成绩为空值的选课记录删除掉。

实验四SQL 的数据视图（2学时）

1、实验目的

(1) 掌握DBMS 的数据查询功能

(2) 掌握SQL 语言的视图功能

2、实验内容

(1) 创建表的视图

(2) 利用视图完成表的查询

(3) 删除表的视图

3、实验要求

(1) 掌握视图的定义

(2) 掌握对视图的操作

(3) 写出实验报告

4、实验步骤

4.1 执行S_T.sql，建立示例数据库S_T。

4.2 对学生关系Student 、课程关系Course 和选修关系SC 进行视图的定义（创建）、查询、更新、删除等操作。

基本练习

（1）创建视图

练习1：创建信息系学生的视图。

CREATE VIEW IS_Student

AS

SELECT Sno,Sname,Ssex,Sage

FROM Student

WHERE Sdept='IS'

WITH CHECK OPTION;

思考：其中，WITH CHECK OPTION子句的作用是什么？

练习2：建立信息系选修了1号课程且成绩在88分以上的学生的视图。

解法1：

CREATE VIEW IS_S1_88(Sno , Sname , Grade )

AS

SELECT Student. Sno , Sname, Grade

FROM Student, SC

WHERE Sdept='IS' AND

Student . Sno =SC . Sno AND

Cno ='1' AND Grade>=88;

解法2：

CREATE VIEW IS_S1_88(Sno , Sname , Grade )

AS

SELECT IS_Student. Sno , Sname, Grade

FROM IS_Student, SC

WHERE IS_Student. Sno =SC . Sno AND

Cno ='1' AND Grade>=88;

练习3：将学生的学号及平均成绩定义为一个视图。

CREATE VIEW S_G(Sno , Gavg )

AS

SELECT Sno, A VG (Grade )

FROM SC

GROUP BY Sno;

（2）查询视图

练习4：在信息系学生的视图中找出年龄小于19的女生。

SELECT Sno, Sname , Ssex , Sage

FROM IS_Student

WHERE Sage

思考：试写出通过视图消解法转换后的查询语句。

练习5：利用IS_S1_88视图，查询信息系选修了1号课程且成绩在92分以上的学生的学号、姓名和成绩。

SELECT *

FROM IS_S1_88

WHERE Grade>=92;

思考：如何查询信息系选修了1号课程且成绩在85分以上的学生的学号、姓名、性别、年龄和成绩？

练习6：在S_G视图（参见练习3）中查询平均成绩在88分以上的学生学号和平均成绩。 SELECT *

FROM S_G

WHERE Gavg>=88;

思考：对于此视图查询语句，SQL Server2005能否正确完成视图消解？试根据实际执行结果的正确与否加以判断，并写出视图消解转换后的SQL 语句。（参考教材p122-123）

（3）更新视图

练习7：向信息系学生视图IS_Student中插入一个新的学生元组，其中学号为200215185，姓名为周捷轮，性别为男，年龄为23岁。

INSERT

INTO IS_Student

VALUES ('200215185','周捷轮',' 男',23);

思考：执行此语句会出现什么问题？如何修改？ 试试：

① 首先，使用如下语句新建一个信息系学生视图IS_Student2：

CREATE VIEW IS_Student2

AS

SELECT Sno, Sname , Ssex , Sage FROM Student

WHERE isnull(Sdept,'IS')= 'IS ' /*判断Sdept 是否为空，若为空则用'IS' 代替 */

WITH CHECK OPTION ;

思考：与练习1中建立的IS_Student视图有何区别？

② 然后，执行如下SQL 语句，对视图进行插入操作，观察视图和基本表中的结果是否正确？ INSERT

INTO IS_Student2

V ALUES ('200215185','周捷轮',' 男',23);

练习8：将信息系学生视图IS_Student中学号为200215132的学生姓名修改为樊虹宇。

UPDA TE IS_Student

SET Sname=' 樊虹宇'

WHERE Sno='200215132' ;

观察：视图IS_Student、IS_Student2以及基本表Student 中的数据的变化情况。

练习9：删除信息系学生视图IS_Student中学号为200215135的记录。

DELETE

FROM IS_Student

WHERE Sno='200215135' ;

观察：视图IS_Student、IS_Student2以及基本表Student 中的数据的变化情况。

（4）删除视图（即删除视图的定义）

练习8：删除视图S_G和视图IS_Student2。

DROP VIEW S_G;

DROP VIEW IS_Student2;

思考：如何删除视图IS_Student及其导出的其他视图？（提示：SQL Server的DROP VIEW 语句中不支持CASCADE ）

扩展练习

首先执行S_T.sql，重建示例数据库S_T。

然后写出并执行SQL 语句完成以下各种操作，记录查询结果。

创建计算机系学生的视图CS_Student，包括学号、姓名、年龄、性别、系别等属性列。

CREATE VIEW CS_Student

AS

SELECT Sno, Sname , Ssex , Sage , Sdept

FROM Student

WHERE Sdept='CS'

WITH CHECK OPTION ;

通过视图CS_Student查询计算机系男生的详细信息。

通过视图CS_Student将计算机系的一个新生元组（学号为200215120，姓名为龙语嫣，性别为女，年龄17岁）插入表中。

INSERT

INTO CS_Student

V ALUES ('200215120' , ' 龙语嫣' , ' 女' , 17, 'CS' );

如果换成如下语句：（系别改为信息系）

INSERT

INTO CS_Student

V ALUES ('200215120' , ' 龙语嫣' , ' 女' , 17, 'IS' );

会出现什么问题？

通过视图CS_Student将计算机系某个男生（学号为200215121）的年龄修改为19岁。

将信息系所有女生的学号及她的平均成绩定义为一个视图S_F_G。

通过视图S_F_G查询信息系所有女生中平均成绩在85-95之间的学号及平均成绩。

（7）删除视图IS_Student和视图S_F_G。

创建表的视图

例如：建立信息系学生的视图。

CREATE VIEW IS_Student

AS

SELECT Sno，Sname ，Sage

FROM Student

WHERE Sdept= 'IS'

利用视图完成表的查询

例如：查询信息系选修了1号课程的学生

SELECT Sno ，Sname

FROM IS_Student，SC

WHERE IS_Student.Sno =SC.Sno AND

SC.Cno= '1'

删除表的视图

例如：DROP VIEW IS_Student

实验五SQL 的数据控制（2学时）

1、实验目的

（1）理解SQL Server 2005数据库的安全性相关的概念。

（2）掌握SQL Server 2005的验证模式、登录管理、用户管理、角色管理、权限管理等操作。

2、实验内容

（1）SQL Server的安全认证模式

（2）管理数据库用户

（3）管理数据库角色

（4）权限管理

3、实验要求

（1）掌握SQL Server的安全认证模式的概念及设置方法

（2）掌握使用SSMS 和T-SQL 两种方式进行SQL Server 登录账号、数据库用户、数据库

角色的添加、修改、删除等操作，以及对数据库用户、角色进行权限授予和回收等操

作。

（3）理解架构(Schema)、用户(User)、角色(Role)和登录(Login)等基本概念及其关系。

（4）认真记录实验过程，完成实验报告。

4、相关知识

4.1 数据库安全性控制基础

（1）用户标识与鉴别

通过用户名和口令来鉴别用户。只有在DBMS 成功注册了的人员才是该数据库的用户(拥有属于自己的惟一的标识符) ，才能访问数据库。任何数据库用户要访问数据库时，都须声明自己的用户标识符。由系统检查该用户标识符的用户是否存在，如果存在还要进一步鉴别该声明者是否确实是具有此用户标识符的那个用户。只有通过鉴别的人才能进入系统。鉴别的方法多种多样，口令是最广泛使用的用户鉴别方法。

（2）存取控制

由DBMS 授权给有资格的用户访问数据库的权限。存取控制机制有两部分组成：①定义用户权限，并将用户权限登记到数据字典中； ②合法性检查，当用户提出操作请求时，DBMS 查找数据字典，进行合法性检查，如果用户的操作请求超出了所定义的权限，系统将拒绝执行此操作。最常使用的存取控制方法是自主存取控制（DAC ），能够通过授权机制有效地控制其他用户对敏感数据的存取，而且用户对数据的存取权限是“自主”的，用户可以自由地将数据的存取权限授予他人、决定是否也将“授权”的权限再授予别人，而系统对此无法控制。自主存取控制主要通过SQL 的GRANT 和REVOKE 语句来实现。另一种存取控制方法是强制存取控制（MAC ），主要用于高安全级别的场合，如军事领域。

（3）视图机制

通过视图机制，可以为不同的用户定义不同的视图，使得要保密的数据对无权存取的用户隐藏起来，从而在一定程序上达到对数据的安全保护的目的。

（4）审计

通过审计可以把用户对数据库的所有操作自动记录下来放入审计日记中，DBA 可以利用审计跟踪的信息，重现导致数据库现有状况的一系列事件，找出非法存取数据的人、时间和内容。审计通常是很费时间和空间的，一般用于安全性较高的部门，而且可由DBA 决定是否采用、何时采用。

（5）数据加密

通过对敏感数据进行加密存储能有效地防止数据泄密。通常由用户决定是否选用数据加密功能。

4.2 SQL Server 2005实现数据库安全性

（1）一台计算机上可以安装多个SQL Server，使得不同的应用程序可以拥有自己的SQL Server。SQL Server 的每一个安装称为一个实例（instance ），每个实例必须有属于自己的唯一的名字。

（2）在SQL Server中，有很多种方法来保证安全性。可以通过Windows 验证来让Windows 自己设置安全性；可以通过视图来限制用户对敏感数据的访问；还可以它通过特定的创建登录名、用户、角色以及授权来明确访问的许可权限。

（3）登录帐号与身份验证。如果用户准备建立与SQL Server的连接，就必须拥有相应的登录帐号。SQL Server 提供两种身份验证模式：一种是Windows 身份验证模式，由SQL Server 系统管理员将Windows 帐号或Windows 组定义为合法的SQL Server 登录帐号，只进行Windows 身份验证，用户不必提供SQL Server登录帐号的用户名和密码。另一种是混合模式，如果用户准备建立与SQL Server的连接，那么既可以使用Windows 身份验证，也可以使用SQL Server身份验证，对于后者，用户必须提供SQL Server登录帐号的用户名和密码（由SQL Server系统管理员预先定义登录帐号）。

（4）数据库用户帐号和角色。如果用户通过Windows 或SQL Server 身份验证，即可登录（连接）到SQL Server，但这并不意味着就能够访问SQL Server中的所有数据库，要想访问某个数据库，用户必须在该数据库中拥有用户帐号。数据库用户帐号和角色用来标识数据库中的用户，并控制数据库对象的所有权和执行语句的权限。角色允许将多个用户归类到某个单元中，之后便可对此单元实施一定的许可权限。SQL Server 为常用的管理功能提供了预定义（或固定）的服务器角色和数据库角色，固定服务器角色是指在服务器级别提供的管理特权的分组，与用户数据库无关。固定数据库角色是指在数据库级别提供管理特权的分组。用户还可以创建自定义的数据库角色，来表达组织内部分组的工作，这样用户就不必为其中的各个成员分别授予或废除许可权限。如果某角色的职能发生了改变，那么也能非常容易地改变角色的许可权限，这种变更将自动应用于每个角色成员。

（5）许可权限验证。在每个数据库内部，可以为用户帐号和角色分配执行（或限制）某种动作的许可权限。然后按如下步骤进行许可权限验证：在用户执行某个动作之后，客户端将发送T-SQL 语句给SQL Server；SQL Server接收到T-SQL 语句后将检查用户是否具有执行该语句的许可权限；如果是，SQL Server执行相应的操作；否则，SQL Server返回错误信息。

（6）SQL Server 2005中的几个相关概念：架构（Schema ）、用户（User ）、角色（Role ）和登录（Login ）。其中，架构是一个独立数据库用户的非重复命名空间，可以被视为数据库对象的容器。 （7）SQL Server 2005 中数据库对象的引用形式为：

[DatabaseServer ].[DatabaseName ].[DatabaseSchema ].[DatabaseObject ]

5、实验步骤

5.1 实验准备：通过执行S_T.sql，在SQL Server 2005中创建一个建立示例数据库S_T。

5.2 利用SQL Server Management Studio进行安全性控制

5.2.1 使用系统管理员账号（如sa ）设置SQL Server的安全认证模式，并添加SQL Server登录账户。

（1）设置SQL Server的安全认证模式。在对象资源管理器中展开服务器组，右击需要设

置的SQL 服务器，从弹出的快捷菜单中选择“属性”命令。在弹出的窗口中，单击左

上角“安全性”选项，右边显示安全性相关设置项目。选中“Windows 身份验证模式”

或“SQL Server和Windows 身份验证模式”单选按钮。设置改变后，必须停止并重新

启动SQL Server服务，新设置才能生效。

（2）添加SQL Server登录帐号。打开SQL 服务器，选择“安全性”下的“登录名”文件

夹，右击“登录名”文件夹，在弹出的快捷菜单中选择“新建登录名”命令，在出现

的窗口的“登录名”文本框中输入一个不带反斜杠的用户名（如tempuser ），选中“SQL

Server 身份验证”单选按钮，并在“密码”与“确认密码”文本框中输入相同的口令

（如qq ），默认数据库为master （或改为S_T），默认语言为English ，单击“确定”

按钮完成创建。

（3）修改登录账号的属性。即双击要修改属性的登录账号，在其属性对话框中修改SQL

Server 账号temp 的口令（如将口令修改为tempqq ）

（4）删除登录账号（如tempuser ）。（注意：在实际操作时，先不要执行此项操作！）

使用上述方法，创建4个登录帐号：tempuser,temp1,temp2,temp3; 密码分别为：tempqq,qq1,qq2,qq3。

5.2.2 管理数据库用户

（1）添加数据库用户。展开要添加用户的某数据库（如S_T数据库），展开安全性，右击用户目录，从弹出的快捷菜单中选择“新建数据库用户”命令；打开“数据库用户”对话框；单击“登录名”文本框右边的按钮来选择一个登录账号（如tempuser ）；在“用户名”文本框中输入用户名，默认情况下它被设置为登录账号名；若需要可以指定数据库用户拥有的架构、数据库角色成员身份等；单击“确定”按钮完成数据库用户的创建。

（2）删除数据库用户。

使用上述方法，为S_T数据库添加4个用户：tempuser,temp1,temp2,temp3，与SQL Server 登录帐号一一对应（映射）。

5.2.3 管理数据库角色

（1）添加数据库角色。展开要添加用户的某数据库（如S_T数据库），展开安全性，右击

角色目录，从弹出的快捷菜单中选择“新建数据库角色”命令；打开“数据库角色”

对话框；单击“所有者”文本框右边的按钮来选择一个数据库用户（如dbo ）；在“角

色名称”文本框中输入角色名（如db_operator），若需要可以指定数据库角色拥有的

架构、此数据库角色的成员信息等；再将数据库用户temp2，temp3添加到数据库角色

db_operator中。单击“确定”按钮完成数据库角色的创建。

（2）删除数据库角色。右击要删除的用户自定义角色，在跨界菜单中选择“删除”命令，

在提示对话框中确认，即可完成删除。注意：不能删除一个有成员的角色，在删除这

样的角色之前，应先删除其成员；只能删除用户自定义角色，不能删除系统的固定角

色！

5.2.4 权限管理

（1）语句权限的管理。右击要修改用户语句权限的数据库（如S_T数据库），在快捷菜单

中选择“属性”命令，打开S_T数据的属性对话框；单击“权限”标签，打开对话框

中的“权限”选项卡；在“权限”选项卡中列出了数据库中所有的用户，以及所有的

语句权限，可以单击用户与权限交叉点上的方框来选择权限，单击“确定”按钮设置

生效。

按照如上方法，将创建表、创建视图的权限授予tempuser 。

（2）对象权限的管理。右击要修改用户（或角色）对象权限的数据库（如S_T数据库），

选择“用户”（或“角色”）目录，在右边用户（或角色）列表中双击某用户（角色），

打开该用户（或角色）的属性对话框；选择“安全对象”选项卡，添加安全对象并设

置各自的权限，单击“确定”按钮设置生效。

按照如上方法，将SELECT ，UPDATE(Sname,Sage,Ssex,Sdept) ON Student 的权限授予数据库用户temp1。将SELECT ON Student,Course,SC的权限授予数据库角色db_operator。

（3）收回权限。

检验效果：用创建的用户替换sa 系统用户，来尝试与数据库服务器的连接，对数据表中的数据进行存取操作，并记录与分析可能会遇到的问题。尝试通过授予更高的权限来解决问题。

5.3 利用T-SQL 进行安全性控制

5.3.1 使用系统管理员账户设置SQL Server的安全认证模式，并添加SQL Server登录账户。

（1）设置SQL Server的安全认证模式。(见4.2.1)

（2）添加SQL Server登录帐号。

EXEC sp_addlogin 'testuser','qq','S_T','ENGLISH'

/*创建一个SQL Server登录名testuser ，密码为qq ，数据库为S_T,默认语言为英语*/

EXEC sp_addlogin 'test1','qq1','S_T','ENGLISH'

EXEC sp_addlogin 'test2','qq2','S_T_1','ENGLISH'

EXEC sp_addlogin 'test3','qq3','S_T_1','ENGLISH'

（3）修改登录账号的属性。

EXEC sp_password 'qq','testqq','testuser'

（4）删除登录账号（如testuser ）。

EXEC sp_droplogin 'testuser'’

5.3.2 管理数据库用户

（1）添加数据库用户

/* sp_grantdbaccess为登录帐号在当前数据库中添加一个安全账户，即数据库用户。以下T-SQL 代码将登录帐号添加到S_T数据库用户中。*/

USE S_T

EXEC sp_grantdbaccess 'testuser','testuser'

EXEC sp_grantdbaccess 'test1','test1'

EXEC sp_grantdbaccess 'test2','test2'

EXEC sp_grantdbaccess 'test3','test3'

（2）删除数据库用户(如test3）

EXEC sp_revokedbaccess 'test3'

5.3.3 管理数据库角色

(1) 添加数据库角色

/* 在S_T数据库中创建一个新角色db_operator，并将数据库用户test2,test3添加到该角色中 */ USE S_T

EXEC sp_addrole 'db_operator'

EXEC sp_addrolemember 'db_operator', 'test2'

EXEC sp_addrolemember 'db_operator', 'test3'

(2) 删除数据库角色

/* 删除db_operator角色。 */

EXEC sp_droprole 'db_operator'

5.3.4 权限管理

（1）语句权限的管理

系统管理员授予用户testuser 如下权限：CREATE TABLE、CREATE VIEW。

USE S_T

GRANT CREATE TABLE, CREATE VIEW TO testuser

（2）对象权限的管理

GRANT SELECT,UPDATE(Sname,Sage,Ssex,Sdept) ON Student TO test1

GRANT SELECT ON Student,Course,SC TO db_operator

（3）收回权限

REVOKE CREATE TABLE, CREATE VIEW FROM testuser

REVOKE SELECT UPDATE(Sname,Ssex,Sdept)ON Student FROM test1

REVOKE SELECT ON Student FROM db_operator

检验效果：用创建的用户替换sa 系统用户，来尝试与数据库服务器的连接，对数据表中的数据进行存取操作，并记录与分析可能会遇到的问题。尝试通过授予更高的权限来解决问题。

附：实验内容提纲

利用S_T.sql建立示例数据库S_T。

设置SQL Server的安全认证模式：

(1) Windows身份验证模式;

(2) 混合模式（SQL Server和Windows 身份验证模式）

添加SQL Server登录帐号：4个

tempuser, temp1, temp2, temp3;

密码分别为：tempqq, qq1, qq2, qq3。

为S_T数据库添加(数据库) 用户：4个

tempuser, temp1, temp2, temp3

（与SQL Server登录帐号一一对应（映射））

为S_T数据库添加自定义(数据库) 角色: 1个，即db_operator。

将数据库用户temp2和temp3添加到db_operator角色中，成为其角色成员。

将在S_T数据库中创建表、创建视图的权限授予数据库用户tempuser 。

将SELECT ，UPDATE(Sname,Sage,Ssex,Sdept) ON Student的权限授予数据库用户temp1。 将SELECT ON Student,Course,SC的权限授予数据库角色db_operator。

检验结果：用创建的登录账号替换sa 系统账号，来尝试与数据库服务器建立连接，对数据表中的数据进行存取操作，并记录与分析可能会遇到的问题。尝试通过授予更高的权限来解决问题。 从数据库用户temp1回收UPDATE(Sname,Ssex,Sdept) ON Student 的权限，从数据库角色db_operator回收SELECT ON SC的权限，然后检验结果的正确性。