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
