新SQL - SERVER实验练习答案

SQL-Server实验

答案

上海师范大学 计算机系

SQL-Server实验讲义

目 录

第一部分 企业管理器的使用 ......................................................................................................... 3

试验一 注册服务器 ............................................................................... 错误！未定义书签。 试验二 创建数据库 ............................................................................... 错误！未定义书签。 试验三 创建表 ....................................................................................... 错误！未定义书签。 实验四 数据输入 ................................................................................... 错误！未定义书签。 实验五 登录到数据库服务器 ............................................................... 错误！未定义书签。 第二部分 SQL语言 ........................................................................................................................ 3 第二部分 SQL语言 ........................................................................................................................ 3

试验一 数据库创建 ................................................................................................................. 3 试验二 创建表 ......................................................................................................................... 3 试验三 创建数据完整性 ......................................................................................................... 5 试验四 数据完整性试验 ......................................................................................................... 6 试验五 索引 ........................................................................................................................... 10 试验六 更新数据 ................................................................................................................... 11 试验七 Sql 查询语句 ........................................................................................................... 12 试验八 视图 ........................................................................................................................... 15 试验九 安全性控制实验 ....................................................................................................... 16 试验十 存储过程 ................................................................................................................... 17 试验十二 触发器 ................................................................................................................... 21 试验十二 恢复技术 ............................................................................................................... 25 试验十三 事务 ....................................................................................................................... 26 试验十四 锁 ........................................................................................................................... 27

2

SQL-Server实验讲义

第一部分 企业管理器的使用

第二部分 SQL语言

试验一 数据库创建

目的：1掌握利用SQL语言进行数据库的创建、维护。

2 sp_helpdb 命令

要求：1 创建数据库 2 修改数据库 3 删除数据库

一 建立school 数据库

1 使用查询分析器创建数据库 school Create DataBase school

2 使用 SP_helpdb 查询数据库 School 的信息

3 使用SQL-Server 的企业管理器查看数据库 school 的信息。 4 记录：

1）school 数据库文件所在的文件夹。 2）school 数据库的文件名

二 删除School数据库

1 使用查询分析器删除数据库 school DROP DATABASE school

2 使用SQL-Server 的企业管理器删除数据库 school 。

三 create Database 深入研究

1 建立school数据库,要求数据库存储在c:\\data文件夹下，初始大小为5MB ,增量为 1MB。 CREATE DATABASE school ON( Name = ‘school_dat’,

Filename = 'c:\\sqldata\\school.mdf', SIZE = 5,

FILEGROWTH = 1 )

2使用SQL-Server 的企业管理器，将数据库的每次增量改为20%。

试验二 创建表

目的：1 掌握利用SQL语言创建表的方法。

2 sp_help 命令

要求：1 创建表 2 修改表结构 3 删除表

一 写出使用 Create Table 语句创建表 student , sc,course 的SQL语句。

学生表、课程表、选课表属于数据库 School ，其各自得数据结构如下： 学生 Student (Sno,Sname,Ssex,Sage,Sdept)

序号 列名 含义 数据类型 长度 1 Sno 6 学号 字符型(char) 2 Sname 8 姓名 字符型(varchar) 3

SQL-Server实验讲义

3 4 5 Ssex Sage sdept 性别 年龄 系科 字符型(char) 整数 (smallint) 字符型(varchar) 2 15

课程表 course(Cno,Cname,Cpno,Ccredit)

序号 列名 含义 1 Cno 课程号 2 cname 课程名 3 Cpno 先修课 4 Ccredit 学分 学生选课 SC(Sno,Cno,Grade)

序号 列名 含义 1 Sno 学号 2 Cno 课程名 3 Grade 成绩

数据类型 字符型(char) 字符型(varchar) 字符型(char) 短整数 (tinyint) 长度 4 20 4 数据类型 字符型(char) 字符型(char) 小数(decimal) 长度 4 6 12,1 二 把创建表的sql 语句的脚本存储到文件 school.sql 。

create table Student ( Sno char(6) , Sname char(10) , Ssex char(2) , Sage smallint , Sdept char(10) , )

create table course( Cno char(4) , Cname char(16) , Cpno char(4) , Ccredit int, )

create table SC( Sno char(6), Cno char(4) , Grade int )

三 使用 SP_HELP 查看表 student 的表结构 利用企业管理器查看表 sc 的表结构 四 利用 sql 语句表结构修改

1 在student 表中添加列：

家庭地址 address 长度为 60 varchar 型 入学日期 inDate 日期型

ALTER TABLE student ADD address varchar(60) ALTER TABLE student ADD inDate datetime

完成后用sp_help 查看是否成功。

2 将家庭地址 address 长度为 50

4

SQL-Server实验讲义

ALTER TABLE student ALTER COLUMN varchar(50)

完成后用sp_help 查看是否成功。

3 删除 student 表的 inDate 列

ALTER TABLE student DROP COLUMN inDate

五 删除表

1 删除表 sc

2 删除表 student 3 删除表 course

试验三 创建数据完整性

目的：1掌握创建数据完整性约束的命令。

2 掌握完整性约束的修改、删除。

要求：1 能建立完整性约束 2 修改完整性约束 3 删除完整性约束

一 写出带有完整性约束的 Create Table 命令建立表student、course、sc 。要求：

1 Student表的主码：sno student 的约束：

? 姓名不可为空，且唯一

? 性别 不能为空且取值范围为{男，女} ? 年龄大于16岁

? sdept 默认为 ?JSJ? 系

2Course表的主码：cno course 的约束：

? Ccredit 取值范围{ 0 ,1,2,3,4,5 }

? 课程表的每一行的 Cno 与 cpno 不可相同

3 Sc表的主码：sno，cno 。主码名为 PK_SC

Sc的外码：

? 外码：SC 表的sno 参照表 student 的 sno ? 外码：sc 表的Cno 参照表 course 的 cno

4 把上述创建表的sql 语句的脚本存储到文件 createSchool.sql 。 create table Student ( Sno char(6) ,

Sname char(10) not null unique ,

Ssex char(2) check (ssex='男' or ssex='女') , Sage smallint check(sage>16) ,

Sdept char(10) not null default 'JSJ' ,

primary key (sno) )

create table course( Cno char(4) , Cname char(16) ,

5

SQL-Server实验讲义

Cpno char(4) ,

Ccredit int check (Ccredit >=0 and Ccredit<=5),

check( cno<>cpno) , --约束 primary key (cno) )

create table SC( Sno char(6), Cno char(4) ,

Grade int check(grade<=100) ,

constraint pk_sc primary key (sno,cno), foreign key (sno) references student(sno) , foreign key (cno) references course(cno) , )

二 使用 SP_HELP 查看表 student 的主码名，约束名，并记录。

使用 SP_HELP 查看表 sc 的主码名，外码名，并记录。

三 利用alter table 添加、删除完整性约束

1 删除 SC 的主码，sc表的主码名为 pk_sc ALTER TABLE sc DROP pk_sc

复习在 Sql-Server 企业管理器中如何完成。 2 删除 SC 表参照 course 表的外码。

如何知道 SC表参照 course 表的外码的名字。

1) SP_HELP course 找到外码名 ,假设为： fk_sc_cno_02938

2) ALTER TABLE sc DROP fk_sc_cno_02938

3 添加 SC 表的主码。主码名为 PK_SC

ALTER TABLE sc ADD CONSTRAINT pk_sc PRIMARY KEY (sno,cno)

4 添加 SC 表的Cno 的外码 ，参照表Course的Cno.

ALTER TABLE sc ADD FOREIGN KEY (cno) REFERENCES course(cno) 5加自定义约束：表 SC的成绩只能在 0 – 100分之间。 ALTER TABLE sc ADD check(grade>=0 and grade<=100)

四 使用 Sql-Server 企业管理器完成： 1 删除 SC 表参照 course 表的外码。

2 建立 SC 表的Cno 的外码 ，参照表Course的Cno. *使该外码具有级联修改的功能。

3 删除表 SC的成绩只能在 0 – 100分之间的约束.

3加自定义约束：表 SC的成绩只能在 0 – 100分之间。

五 使用

select * from student 查看信息 select * from course 查看信息 select * from sc 查看信息

试验四 数据完整性试验

6

SQL-Server实验讲义

目的：1理解实体完整性、参照完整性、用户自定义完整性的作用

2 特别掌握外码的作用。

要求：记录试验中遇到的问题，并写出原因。

实验前需要利用试验三完成的脚本文件 createSchool.sql，重新建立数据库 school 。

一 实体完整性

1 student 表数据输入 学号 姓名 性别 年龄 3001 20 赵达 男 3002 21 杨丽 女 3001 21 李寅 女 ? 输入上述数据，记录出现的问题，说明原因。 第三行不能输入。

Student 的主码为 sno , 因此 sno 列的值不能重复。 ? select * from student 查看你输入了几行数据。

2 course 表数据的输入 Cno Cname Cpno 1081 4 电子商务

3 SC 表数据的输入

Sno Cno Grade 3001 1081 90 3001 1081 79 输入上述数据，记录出现的问题，说明原因。

系科 SX JSJ SX Ccredit 二 用户自定义完整性约束

表student 有用户自定义约束：

性别 不能为空且取值范围为{男，女} 年龄大于16岁 表course 的自定义约束：

Ccredit 取值范围{ 0 ,1,2,3,4,5 }

课程表的每一行的 Cno 与 cpno 不可相同

1 student 表数据输入 学号 姓名 性别 年龄 3005 14 赵达 男 3006 21 杨丽 南 ? 输入上述数据，记录出现的问题，说明原因。

3005 学生不能输入，年龄问题，有约束 sage>16 3006学生不能输入，性别不对。

系科 SX JSJ 7

SQL-Server实验讲义

? select * from student 查看你输入了那些数据。

2 course 表数据的输入 Cno Cname Cpno Ccredit 1085 C++ 9 1086 语文 1086 3 ? 输入上述数据，记录出现的问题，说明原因。 1086 不能输入，因为有约束 check(cno<>cpno) ? select * from student 查看你输入了那些数据。

3 SC 表数据的输入 Sno Cno Grade 3002 1081 128

? 输入上述数据，记录出现的问题，说明原因。

3002 这条数据不能输入，因为 grade不能大于 100分 ? select * from student 查看你输入了那些数据。

8

SQL-Server实验讲义

三 参照完整性约束

? 掌握表之间建立外码后，对被参照表的如下操作会有何影响：

修改主码、插入新行、删除新行？

? 对参照表添加新行、删除行、修改外码值有何影响？ ? 掌握级联修改、级联删除的概念。

注意：

表SC的 Sno是外码，参照student的sno。 表SC的 Cno是外码，参照course的cno。

1 输入实验前的数据

学生表 Student Sno Sname 4001 赵尹 4002 杨开 课程表 course Cno Cname 1088 Java 1089 数学

学生选课 SC Sno Cno 4001 1088 4002 1088 Ssex 男 女 Sage 20 20 Sdept SX JSJ Cpno Ccredit 5 3 Grade 90 86 2 试验过程

1) 在SC表中添加新行： Sno Cno Grade 4001 1066 76 记录试验结果.,写出出现此结果的原因.

不能添加，因为在 cno是外码，参照course的 cno , 但在course 中没有 1066课程。

2) 在student表中添加新行 Sno Sname Ssex Sage Sdept 4003 21 SX 赵辉 男 记录试验结果.,写出出现此结果的原因. 可以输入

3) 删除student 表的 4001 ，4002学生 记录试验结果.,写出出现此结果的原因.

两个学生不能被删除，因为sc的外码 sno 参照student的sno, sc中已经有4001，4002学生的数据，因此不能删除。 思考：

? 删除SC表的记录有限制吗？

没有

9

SQL-Server实验讲义

? 采取什么技术能使不能成功执行的命令变得可以执行，且使数据库保持数据完整

性。 级联删除

4) 把 student 表的学号 4003 改为 4018 , 4001改为4021。 记录试验结果.,写出出现此结果的原因. 4003 可以改为 4018, 4001不能改为 4021

因为sc的外码 sno 参照student的sno, sc中已经有4001的数据，但没有 4003的选课数据。

思考：采取什么技术能使本题不能执行的命令可以执行，且使数据库保持数据完整性。 级联修改

5) 把sc表中的如下记录的学号从4001改为4011。 Sno Cno Grade 4001 1088 90 记录试验结果.,写出出现此结果的原因.

不能修改，因为sc的外码 sno 参照student的sno, 4011在 student中不存在。

如不成功，则可以采取什么方法来实现此要求。 需要在student表中添加 4011学生。

? 如不成功，那么把4001修改为4003，能成功吗？

能成功！

思考: 参照完整性规则中,外码可以为空, 但SC表中的外码可以为空吗?为什么?

举一个外码可以为空的例子。

不可以，因为 sc表的主码为 sno+cno, 即sno,cno为主属性，所以不能为空。

试验五 索引

目的：掌握索引的建立、删除的方法。

一 创建索引

1 建 student 的索引

为姓名建立索引，索引名：Ix_student_sname 为系科建立索引，索引名：Ix_student_sdept create index ix_student_sname ON student(sname) create index ix_student_sdept ON student(sdept)

2 SC 的索引

为课程号建立索引： ix_sc_cno create index ix_sc_cno ON sc(cno)

3 Course 的索引

为课程名建立唯一性索引 ：Ix_course_cname

create unique index ix_course_cname ON course( cname)

4 如何 SP_HELP 查看索引刚才建立的索引？ 如何在企业管理器中查看索引？

二 删除索引 course 表的索引 IX_course_cname

DROP INDEX course.ix_course_cname

10

SQL-Server实验讲义

三 思考：如何把索引 IX_student_sname 修改为唯一性索引？ 可以使用企业管理器

或先删除索引，再重新建立。 *四 思考建立索引的目的

1 输入下列存储过程，该程序生成大量数据供测试： create procedure usp_makedata as

declare @nCnt int , @sNo varchar(6) , @sname varchar(8) set @nCnt =12000 --计数器 while @nCnt<999999 begin

set @nCnt = @nCnt + 1

set @sNo = convert(varchar(6) ,@nCnt) set @sName = '张'+@sno

insert into student (sno,sname,ssex,sage) values ( @sno,@sname,'男',20)

end return

2 exec usp_makedata --生成测试数据 3 输入下述测试程序：

create procedure usp_test as declare @nCount int ,@data int set @nCount=0

while @nCount<100 begin

select @data=count(*) from student where sname <'张3800' or sname>'张8800' set @nCount =@nCount + 1

end

4 测试

1）建立姓名的索引，查看运行时间(8秒).

create index ix_student_sname on student(sname) --建立索引 exec usp_test

2) 删除姓名索引，查看运行时间（2分11秒），比较与1）的时间长短。

drop index student.ix_student_sname --删除索引

exec usp_test

试验六 更新数据

目的：掌握insert,update ,delete 语句的使用。

一 insert

1 写出把下述学生的信息添加到student表中的命令。 学号 4001 4002 姓名 赵茵 杨华 性别 男 女 20 21 年龄 SX 系科 Insert into student (sno,sname,ssex,sage,sdept) values (?4001 ?,?赵茵?,?男?,20,?SX?) Insert into student (sno,sname,ssex,sage) values (?4002 ?,?杨华?,?女?,21)

2 批量插入数据

1） 建立一个新表 sc_name ，有属性 sno , sname , ssex , cno , grade 。 CREATE TABLE sc_name (

11

SQL-Server实验讲义

Sno char(6) ,

Sname varchar(20), Ssex char(2) , cno char(4) , grade int )

2） 把 SX 系学生的sno,sname,ssex, cno , grade 插入到表 sc_name 中。 Insert into sc_name (sno,sname,ssex,cno , grade)

select student.sno,sname , ssex,cno,grade from student,sc where student.sno=sc.sno and sdept=?SX?

3） 察看 sc_name 表的数据 select * from sc_name

二 Update

1 修改 0001 学生的系科为: JSJ

Update student set sdept=?JSJ? where sno=?0001?

2 把陈小明的年龄加1岁，性别改为女。

Update student set sage=sage+1 , ssex=?女? where sname=? 陈小明? 3 修改李文庆的1001课程的成绩为 93 分

update sc set grade=93 where cno=?1001? and sno in (

select sno from student where sname=? 李文庆?)

4 把“数据库原理”课的成绩减去1分

update sc set grade=grade - 1 where cno in (

select cno from course where cname=?数据库原理? )

三 Delete

1 删除所有 JSJ 系的男生 delete from student where sdept=?JSJ?

2 删除“数据库原理”的课的选课纪录

Delete from sc where cno in (select cno from course where cname=?数据库原理? )

思考：修改数据的命令与修改表结构的命令有何区别？

试验七 Sql 查询语句

目的: 掌握 Select 查询语句。

一 单表

1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。 select sno,sname,sage from student

where sage between 19 and 21 and ssex=’女’ order by sage desc 2查询姓名中第戎2个字为“明”字的学生学号、性别。

select sname ,ssex from student where sname like ‘_明%’ 3查询 1001课程没有成绩的学生学号、课程号

select sno,cno from sc where grade is null and cno=’1001’ 4查询JSJ 、SX、WL 系的学生学号,姓名，结果按系及学号排列

select sno,sname from student where sdept in (‘JSJ’,’SX’,’WL’)

12

SQL-Server实验讲义

order by sdept,sno

5按10分制查询学生的sno,cno,10分制成绩

（1-10分 为1 ，11-20分为2 ，30-39分为3，。。。90-100为10） select sno , cno , grade/10.0+1 as level from sc

6查询 student 表中的学生共分布在那几个系中。（distinct） select distinct sdept from student

7查询0001号学生1001，1002课程的成绩。

Select cno from sc where sno=’0001’ and (cno=’1001’ or cno=’1002’)

二 统计

1查询姓名中有“明”字的学生人数。

select count(*) from student where sname like ‘%明%’ 2计算‘JSJ’系的平均年龄及最大年龄。

Select avg(sage) , max(sage) from student Where sdept=’JSJ’ 3计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列 select cno,sum(grade),avg(grade),max(grade),min(grade) from sc group by cno

order by avg(grade) desc

4 计算 1001,1002 课程的平均分。

Select cno , avg(grade) from sc where cno in (‘1001’,’1002’) Group by cno

5 查询平均分大于80分的学生学号及平均分 select sc.sno , avg(grade) from sc group by sc.sno

having avg(grade)>80

6 统计选修课程超过 2 门的学生学号

select sno from sc group by sno having count(*)>2 7 统计有10位成绩大于85分以上的课程号。 Select cno from sc where grade>85

group by cno having count(*) =10 8 统计平均分不及格的学生学号

select sno from sc group by sno having avg(grade)<60

9 统计有大于两门课不及格的学生学号 select sno from sc where grade<60 group by sno having count(*) >2

三 连接

1查询 JSJ 系的学生选修的课程号

select cno from student,sc where student.sno=sc.sno and sdept=’JSJ’ 2查询选修1002 课程的学生的学生姓名 (不用嵌套及嵌套2种方法）

a: select sname from student,sc where student.sno = sc.sno and cno=’1002’ b: select sname from student where sno in (select sno from sc where cno=’1002’) 3查询数据库原理不及格的学生学号及成绩 select sno,grade from sc ,course

where sc.cno=course.cno and cname=’数据库原理’

13

SQL-Server实验讲义

4查询选修“数据库原理”课且成绩 80 以上的学生姓名(不用嵌套及嵌套2种方法） a: select sname from student , sc , course

where student.sno=sc.sno and sc.cno = course.cno and

grade>80 and cname=’数据库原理’

b: select sname from student where sno in ( select sno from sc where grade>80 and cno in ( select cno from course where cname=’数据库原理’) ) 5查询平均分不及格的学生的学号，姓名,平均分。

select sno, max(sname) , avg(grade) as avggrade from sc , student where student.sno=sc.sno group by student.sno having avg(grade) <60

6查询女学生平均分高于75分的学生姓名。

A: Select sname from student where ssex=’女’ and sno in ( Select sno from sc group by sno having avg(grade)>75)

B: Select max(sname ) from sc,student where student.sno=sc.sno and Ssex=’女’ Group by student.sno having avg(grade)>75

7查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出，不能遗漏)

select student.sno,sname,cno,grade from student left join sc ON student.sno=sc.sno and ssex=’男’

四 嵌套、相关及其他

1 查询平均分不及格的学生人数

select count(*) from student where sno in (

select sno from sc group by sno having avg(grade)<60 ) 2 查询没有选修1002 课程的学生的学生姓名 select sname from student where sno not in( select sno from sc where cno=’1002’) student 0001 aa X 0002 bb ?0003 cc X sc

0001 1001 0001 1002 0002 1001 0003 1002

select sname from student where not exists (

select * from sc where cno=’1002’ and sc.sno=student.sno)

3 查询平均分最高的学生学号及平均分 （2种方法 TOP , any , all）

a: select top 1 sno,avg(grade) from sc group by sno order by avg(grade) desc

B: select sno,avg(grade) from sc group by sno

having avg(grade) = (select top 1 avg(grade) from sc

group by sno order by avg(grade) desc )

c: select sno,avg(grade) from sc group by sno

having avg(grade) >=all ( select avg(grade) from sc group by sno )

14

SQL-Server实验讲义

*4 查询没有选修1001，1002课程的学生姓名。 Select sname from student where not exists (

Select * from course where cno in (‘1001’,’1002’) and

Not exists ( select * from sc where sno=student.sno and cno=course.cno ) )

5 查询1002课程第一名的学生学号（2种方法）

a: select top 1 sno from sc cno=’1002’ order by grade desc

b: select sno from sc where cno=’1002’ and

grade >=all (select grade from sc where cno=’1002’)

6 查询平均分前三名的学生学号

select top 3 sno from sc group by sno order by avg(grade) desc 7 查询 JSJ 系的学生与年龄不大于19岁的学生的差集

a: select * from student where sdept=’JSJ’ and sage>19 b: select * from student where sdept=’JSJ’ except select * from student where sage<19

8 查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名 select student.sno,sname from student,sc where cno=?1001? and grade>90 union

select sno,sname from student where sno in (

select sno from sc group by sno having avg(grade)>85 ) 9 查询每门课程成绩都高于该门课程平均分的学生学号 select sno from student where sno not in ( select sno from sc X where grade<(

select avg(grade) from sc Y where Y.sno=X.sno) )

select sno from student where sno not in (

select sno from sc X where grade < (

select avg(grade) from sc where cno=X.cno ) )

10 查询大于本系科平均年龄的学生姓名 select sname from student X where sage > (

select avg(sage) from student y where sdept=x.sdept)

试验八 视图

目的: 掌握视图的建立、使用。

1建立学生学号、姓名、性别、课程号、成绩的视图 v_sc 查看V_sc中的数据。

Create view v_sc (sno , sname,ssex , cno, grade ) as

Select student.sno , sname,ssex , cno , grade from student , sc Where student.sno=sc.sno

Select * from v_sc

1 建立学生学号、姓名、出生年月的视图 v_age 查看V_age中的数据。

Create view v_age (sno,sname, sbirth) as Select sno , sname , 2008 – sage from student

15

SQL-Server实验讲义

Select * from v_age

2 建立 ?JSJ? 系的学生学号、姓名、性别、年龄的视图 V_JSJ Create view v_jsj (sno,sname,ssex, sage) as

Select sno,sname,ssex,sage from student where sdept=?JSJ?

3 建立每门课程的平均分的视图 V_avggrade Create view v_avgGrade(cno, grade1 ) as

Select cno , avg(grade) from sc group by cno

4 将 视图 v_jsj 中 李文庆 的年龄改为21岁

Update v_jsj set sage=sage+1 where sname=?李文庆?

5 察看 student 中李文庆的年龄 查看 v_age 中李文庆的出生年月

Select * from student where sname= ?李文庆?

Select * from v_age where sname=?李文庆? 6 查询每门课程的及格率

Create view v1 (cno , cnt1) as

Select cno, count(*) from sc group by cno

Create view v2 (cno , cnt1) as

Select cno, count(*) from sc where grade>=60 group by cno

Select v1.cno , cnt2*1.0 / cnt1 from v1,v2 where v1.cno=v2.cno

思考: 1 利用 V_JSJ 视图,可以更新SX 的学生的年龄吗? 写出理由 如: update v_jsj set sage=25 where sno=? 0004? 0004 号学生为 SX 系.

试验九 安全性控制实验

目的:掌握Sql-server 的授权机制.

1）建立新用户 mary , 密码1234 Sp_addLogin ‘mary’, ‘1234’

2) 授予 mary 可以访问 School 数据库的权力 选择 school 数据库

Sp_grantDBaccess mary

3) 以mary 登录 sql-server ,

执行 select * from student ,记录执行结果，说明原因。 无法查到数据，因为mary 没有查询 student 的权限。

4）将 course 的查询、更改权限授予 mary Grant select , update on course to mary

5）把查询 student 表和修改学生学号的权限授予用户 mary,且他能将此权限转授他人。 Grant select , update(sno) on student to mary with grant option

16

SQL-Server实验讲义

6) 把对 course 表的更改权限从mary 收回 Revoke update on course from mary

7) 把第5）小题授予mary的权限收回。

revoke select , update(sno) on student from mary cascade

8）mary 只能查询 ‘1001’ 号课程的学生成绩,请问如何授权 Create view v_sc1 (sno,cno,grade) as

Select sno, cno,grade from sc where cno=’0001’

Grant select on v_sc1 to mary

思考: 1 sp_addlogin , sp_grantdbaccess 语句的区别.

2 如有200个人需要授权，SQL-SERVER如何简化授权机制。

试验十 存储过程

目的: 掌握存储过程的概念、编程及使用

1 编写一个存储过程 usp_avgage , 向客户端返回每个系科的学生平均年龄。 系科 平均年龄 JSJ 21 SX 20 。。。

1) 编写存储过程的代码

Create procedure usp_avgage as

Select sdept,avg(sage) from student group by sdept

2）调试、运行该存储过程。 Usp_avgage

2编写一个存储过程 usp_sdept, 传入一个系科代码，返回该系的平均年龄，人数 Create procedure usp_sdept @dept char(10) as

Select avg(sage),count(*) from student where sdept=@dept

3 编写存储过程 usp_updateGrade , 传入参数为课程号,处理逻辑:

对传入的这门课,进行如下处理:

如某学生该门课成绩>80 , 则加 2 分 如某学生该门课成绩>60 , 则加 1 分

17

SQL-Server实验讲义

如某学生该门课成绩<=60 ,则减 1分

并且返回此门课的每个学生的最新成绩: 学号 成绩.

Create procedure usp_updateGrade @cno char(4) as

Update sc set grade=grade + 2 where cno=@cno and grade>80

Update sc set grade=grade + 1 where cno=@cno and grade between 60 and 80 Update sc set grade=grade -1 where cno=@cno and grade<=80

Select sno , grade from sc where cno=@cno return

5 编写存储过程 usp_comp_age , 比较0001，0002学生的年龄的高低，输出： XXXX学生的年龄大

注意： XXXX为学生的姓名

Create procedure usp_comp_age as declare @age1 int , @age2 int

declare @name1 char(10) , @name2 char(10) --临时存储两个人的姓名

select @age1=sage ,@name1 = sname from student where sno=’0001’ select @age2=sage, @name2 = sname from student where sno=’0002’

if @age1 > @age2

print @name1 + ‘学生的年龄大’ else

print @name2 + ‘学生的年龄大’ return

7 编写存储过程 usp_comp_age1 , 比较两个学生的年龄的高低，两个学生的学号有参数输入，最后输出： XXXX学生的年龄大。 注意： XXXX为学生的姓名

Create procedure usp_comp_age1 @no1 char(6),@no2 char(6) as declare @age1 int , @age2 int

declare @name1 char(10) , @name2 char(10) --临时存储两个人的姓名

select @age1=sage ,@name1 = sname from student where sno=@no1 select @age2=sage, @name2 = sname from student where sno=@no2

if @age1 > @age2

print @name1 + ‘学生的年龄大’ else

print @name2 + ‘学生的年龄大’ return

10 编写存储过程 usp_comp_age2 , 比较两个学生的年龄的高低，两个学生的学号有参数输入，最后把年龄大的学生的姓名、性别返回客户端。

Create procedure usp_comp_age1 @no2 char(6),@no2 char(6) as declare @age1 int , @age2 int

declare @name1 char(10) , @name2 char(10) --临时存储两个人的姓名

18

SQL-Server实验讲义

select @age1=sage ,@name1 = sname from student where sno=@no1 select @age2=sage, @name2 = sname from student where sno=@no2

if @age1 > @age2

select sname ,ssex from student where sno=@no1 else

select sname ,ssex from student where sno=@no2 return

12 编写存储过程 usp_t1，传入参数为学号,把该学号的课程1001的成绩减到58分。每次只能减1分，用循环完成。

create procedure usp_t1 @no char(6) as declare @age int set @age=100 while @age>58 BEGIN

SELECT @age = sage from student where sno=@no If @age>58

Update sage=sage -1 where sno=@no END RETURN

-- 以下不需要

4 编写存储过程 usp_disp , 传入参数为课程号,处理逻辑: 返回每个学生的成绩等级。 成绩>=90 为优, 成绩>=80为良,成绩>=70 为中,成绩>=60为及格 ,成绩<=60为不及格。 返回结果如下：

学号 课程号 成绩 等第 0001 1001 91 优 0001 1002 78 中 ?????..

create procedure udp_disp @cno char(4) as --建立临时表存储结果

create table #tmp (sno char(4),cno char(4),grade int , level char(6) ) --建立某门课程的游标

declare cur1 cursor for select sno,grade from sc where cno=@cno declare @sno char(4) ,@nGrade int

declare @sLevel char(6) --临时存储某学生的成绩等级 open cur1

fetch next from cur1 into @sno , @nGrade --读出游标第一行数据 while @@fetch_status =0 begin

--处理一行数据 if @nGrade>=90

set @sLevel = ‘优’ else if @nGrade>=80 set @sLevel = ‘良’ else if @nGrade>=70 set @sLevel = ‘中’

19

SQL-Server实验讲义

else if @nGrade>=80

set @sLevel = ‘及格’ else

set @sLevel = ‘不及格’ --把结果写入临时表

insert into #tmp(sno,cno,grade,level) values (@sno,@cno,@nGrade,@sLevel)

fetch next from cur1 into @sno , @nGrade --读出游标下一行数据 end

close cur1

dealLocate cur1

select * from #tmp --返回结果给客户端 drop table #tmp --删除临时表 return

5 编写一个存储过程，传入参数为学号，执行后，把该学号的学生按如下格式输出成绩： （注意：只有一行）

学号 姓名 1001课程 1002课程 1003 课程 平均分

6 编写一个存储过程，传入参数为 系科，执行后，把该系科的学生按如下格式输出学生成绩：

学号 姓名 1001 课程 1002课程 1003 课程 平均分 create procedure usp_grade @dept char(15) as create table #tmp ( sno char(4) , sname char(10) , g1 int null, g2 int null , g3 int null , pj int null )

declare @no char(4) , @name char(10), @nG1 int ,@nG2 int ,@nG3 int

declare cur1 cursor for

select sno , sname from student where sdept = @dept --游标 某一个系的学生

open cur1

fetch next from cur1 into @no , @name

while @@fetch_status=0 begin

select @nG1=grade from sc where sno=@no and cno='1001' select @nG2=grade from sc where sno=@no and cno='1002' select @nG3=grade from sc where sno=@no and cno='1003'

insert into #tmp(sno,sname,g1,g2,g3,pj) values (@no,@name,@nG1,@nG2,@nG3,(@nG1+@nG2+@nG3)/3 )

20

SQL-Server实验讲义

fetch next from cur1 into @no , @name end

close cur1

dealLocate cur1 select * from #tmp drop table #tmp

--执行

usp_grade 'JSJ'

7 编写存储过程，统计男女生1001，1002，1003各自的选修人数，输出格式如下：

性别 1001人数 1002人数 1003人数 小计 男 3 5 2 10 女 2 4 1 7 合计 5 9 3 17

（数据为示意数据）

create procedure usp_tj as

create table #tmp (ssex char(2), rs1 int,rs2 int ,rs3 int ,xj int ) declare @nRs1 int , @nRs2 int, @nRs3 int

select @nRs1 = count(*) from student,sc where cno=’1001’and ssex=’男’ select @nRs2 = count(*) from student,sc where cno=’1002’and ssex=’男’ select @nRs3 = count(*) from student,sc where cno=’1003’and ssex=’男’ insert into #tmp(ssex,rs1,rs2,rs3,xj)

values (‘男’,@nRs1,@nRs2,@nRs3, @nRs1+@nRs2+@nRs3)

select @nRs1 = count(*) from student,sc where cno=’1001’and ssex=’女’ select @nRs2 = count(*) from student,sc where cno=’1002’and ssex=’女’ select @nRs3 = count(*) from student,sc where cno=’1003’and ssex=’女’ insert into #tmp(ssex,rs1,rs2,rs3,xj)

values (‘女’,@nRs1,@nRs2,@nRs3, @nRs1+@nRs2+@nRs3)

select * from #tmp drop table #tmp return

8 编写一个存储过程，利用存储过程的参数返回数据库服务器上的日期时间。

思考：何时需要存储过程？

试验十二 触发器

目的: 了解触发器的机制及编程设计、使用

一 建立学生表的触发器 usp_addstudent，当增加学生时，SX系的学生不能超过30岁。

21

SQL-Server实验讲义

1 写出触发器

2 执行下列语句块： begin tran

insert into student (sno,sname,ssex,sage,sdept)

values (‘0701’,’刘欢’,’男’,26,’SX’)

if @@error=0 commit else

rollback

end

观察该学生是否加入到 student

3执行下列语句块： begin tran

insert into student (sno,sname,ssex,sage,sdept) values (?0702?,?赵欢?,?男?,31,?SX?) if @@error=0 commit else

rollback

end

观察该学生是否加入到 student

二 实现下列触发器

1 不能删除年龄大于25岁的学生记录。 create trigger utr_student1 on student for delete as

declare @nCnt int --存储被删除的大于25岁的人数 select @nCnt = count(*) from deleted where sage>25 if @nCnt>0 begin

raiserror('不能删除大于25岁的学生',16,10) rollback transaction end

--测试

insert into student values ('8701','aa1','男',27,'JSJ') --不能被删除 insert into student values ('8702','bb1','男',24,'JSJ') --能删除

select * from student where sno in ('8701','8702') delete from student where sno='8701'

select * from student where sno in ('8701','8702') delete from student where sno='8702'

2 建立触发器 usp_delcourse , 使课程表中1001，1002，1003 三门课不会被删除。 注意如何调试。

create trigger utr_deleteCourse on course for delete as declare @nCnt int

select @nCnt = count(*) from deleted where cno in (‘1001’,’1002’,’1003’) if @nCnt>0 begin

22

SQL-Server实验讲义

raiserror('不能删除',16,10) rollback transaction end return

调试：

Delete from course where cno=’1001’ --不会被删除

Delete from course where cno=’1006’ --能被删除

3 对学生表建立一触发器，使更改后的年龄只能比原值大

create trigger utr_student_update1 on student for update as if not update(sage) return

declare @nCnt int

select @nCnt = count(*) from inserted ,deleted

where deleted.sno=inserted.sno and inserted.sage0 begin

raiserror('更改后的年龄比原值小了',16,10) rollback transaction end

4对sc表建立触发器，使‘JSJ’系的学生不可选择 ‘1004’号课程 create trigger utr_choose on sc for insert as

declare @nCnt int --存储被删除的大于25岁的人数 select @nCnt = count(*) from inserted ,student

where student.sno=inserted.sno and sdept='JSJ' and inserted.cno='1004' --inserted 存储insert 命令添加的数据 如 0001,1004,90 if @nCnt>0 begin

raiserror('JSJ不可选择 1004',16,10) rollback transaction end

--测试

insert into student values ('8701','aa1','男',27,'JSJ')

insert into sc(sno,cno,grade) values ('8701','1001',90) --可以

insert into sc(sno,cno,grade) values ('8701','1004',90) --不可以

select * from sc where sno='8701'

5 对表 course 建触发器，实现级联删除的功能，但某课选修人数大于3则不能删除。

(先删除 sc 表对course 的外码)

*三 建立一个触发器，使对sc表成绩的修改自动记录修改日志。

日志文件表(tablog)记录如下：

用户名 学号 课程号 原成绩 修改后成绩 更改日期

23

SQL-Server实验讲义

四 在School数据库中建立一个试验用的发票表bill，然后为发票bill建立触发器 utr_money ,实现当输入单价和数量后，自动填写金额，即发票金额不输入，由单价、数量相乘后自动填写到金额中。

Create table bill(

billID char(8), --发票编号 date datetime, --开票日期 product char(10), --产品编号 price int , --单价 qty int , --数量 charge int , --金额 primary key (billid) )

思考: 触发器中 inserted , deleted 表的作用? 在触发器中如没有用到此两个表中的任何一个,你认为触发器还有意义吗?

24

SQL-Server实验讲义

试验十二 恢复技术

目的：1 掌握数据库的备份及恢复的方法。

2 了解备份方案的设定

一 完全备份的建立与恢复 1建立完全备份 USE school GO

BACKUP DATABASE school TO DISK=’C:\\schooldata.bak’

2查看备份文件中的信息

RESTORE FILELISTONLY FROM DISK=’c:\\schooldata.bak’ RESTORE HEADERONLY FROM DISK=’c:\\schooldata.bak’

3恢复完全备份

1) 先删除数据库 School USE Master GO

DROP DATABASE school 2) 然后恢复.

RESTORE DATABASE school from DISK=’c:\\schooldata.bak’ 3): 查看 school 的student 中的数据

二 建立差异备份 1 建立备份

1） 制作数据文件备份 schoolDiff.bak

2） 把学号 7001， 姓名：王海，性别：男，年龄为23 的学生加入student 3） 制作school 的差异备份 ，存入schoolDiff.bak

BACKUP DATABASE school TO DISK=?schoolDiff.bak? WITH DIFFERENTIAL

4) 把学号 7002， 姓名：赵燕，性别：女,年龄为22 的学生加入student 5) 制作school 的差异备份 ，存入schoolDiff.bak

BACKUP DATABASE school TO DISK=?schoolDiff.bak? WITH DIFFERENTIAL

2查看备份文件 schoolDiff.bak 中的信息 3 删除 school 数据库

4 恢复数据库 school 到第2步状态

RESTORE DATABASE school from DISK=’c:\\schoolDiff.bak’WITH file=1 NORECOVERY RESTORE DATABASE school from DISK=’c:\\schoolDiff.bak’WITH file=2

Select * from student 观察student 数据

5 恢复数据库 school 到最新状态

RESTORE DATABASE school from DISK=’c:\\schoolDiff.bak’WITH file=1 NORECOVERY RESTORE DATABASE school from DISK=’c:\\schoolDiff.bak’WITH file=3

Select * from student 观察student 数据

25

本文来源：https://www.bwwdw.com/article/54mo.html