数据库实验四-上机实验.doc

实验四：T-SQL语句及存储过程
徐瑞福 20082603 信管1班
1. 实验特点:
类型：验证 类别：专业基础 计划学时：4学时 每组人数：1人
2. 实验目的与要求：
1） 掌握 T-SQL语句的基本语法格式，熟练使用查询语句，熟练使用流程控制语句
2） 掌握在查询分析器中创建存储过程，掌握利用企业管理器创建用户存储过程
3. 主要仪器设备：安装SQL Server 2000中文版的微型计算机。
4. 实验内容提要:
1）变量的定义、赋值、输出
2) IF…..ELSE 语句
3） WHILE 语句
4） 常用系统标量函数和用户定义函数
5） 游标的使用
6） 存储过程的创建、调用、删除
7）触发器的创建、执行、删除
8）数据库完整性中默认值、规则对象的创建、绑定、删除
完成包含以上内容的课上例题及补充作业题
5. 上机操作要点：上机前要认真阅读教材的有关例题，要将上机操作的语句事先写在提纲上。
6. 注意事项：操作命令在查询分析器中完成，注意保存SQL文件，上机结束时一定要将数据库和表与系统有效“分离”后，才能存盘带走。

T-SQL查询、存储过程、触发器、完整性上机作业题
第一部分 ：T-SQL程序设计
（1）.如果3号课程的平均成绩在80分以上，则输出“3号课程成绩良好”，否则输出“3号成绩一般”
declare @avg float set @avg=(select avg(grade)from sc where cno='3')if @avg>80print'3号课程成绩良好'else print'3号成绩一般'

（2）计算并输出95003号学生的平均成绩，若无该生信息，则显示“该生未选课”,提示信息.
declare @avg float if(select count(*)from sc where sno='95003')=0 print '该生未选课' else begin select @avg=avg(grade)from sc where sno='95003' print'95003号学生平均成绩' print @avg end

（3）.如果有成绩在90分以上的学生,则显示他的学号,课程和成绩,否则显示“没有学生的课程成绩在90分以上”提示信息
declare @text char(10) if exists(select grade from SC where grade>90)select Sno,Cno,Grade from SC where Grade>90 else begin set @text='没有学生的课程成绩在90分以上' print @text end

(4).利用游标逐行显示student表中的记录。
declare stu cursor for select *from student open stu fetch next from stu while @@fetch_status=0 fetch next from stu close stu deallocate stu

(5).用自定义函数计算全体男生的平均年龄
create function avg_age(@sex char(2)) returns int
as begin declare @aver int select @aver=(select avg(Sage) from Student where Ssex=@sex )return @aver end
go
declare @aver1 int,@sex char(2) set @sex='男' select @aver1=dbo.avg_age(@sex)
select @aver1 as '全体男生的平均年龄'
go

(6).显示course表中课程名的前2个字符。
select substring(Cname,1,2) from Course

(7).在一列中显示student中各元组的学号中的年级，列名显示为“年级”；另一列中显示学号中的学生序列号，列名显示为“序号”。
select substring(Sno,1,2) 年级,substring(Sno,3,len(Sno)-1) 序号 from Student order by Sno

(8).在选课表中显示学号、课程号，并根据成绩：0-59显示“不合格”；60-79显示“合格”；80-89显示“良好”；90-100显示“优秀。”
select Sno as '学号',Cno as '课程号', grade =case when Grade<=59 then '不合格' when Grade>=60 and Grade<=79 then '合格' when Grade>=80 and Grade<=89 then '良好' else '优秀' end from SC

第二部分 ：存储过程
（1）创建一个为worker表添加职工记录的存储过程Addworker

go
if exists(select name from sysobjects where name='Addworker' and type='P') drop procedure Addworker
go
create proc Addworker @职工号 char(4),@姓名 char(8),@性别 char(2),@出生日期 datetime,@党员否 char(2),@参加工作 datetime,@部门号 char(4)
as
insert into worker(职工号,姓名,性别,出生日期,党员否,参加工作,部门号)values(@职工号,@姓名,@性别,@出生日期,@党员否,@参加工作,@部门号)
go
exec Addworker '16','王璐','女','1988-11-20','否','2010-08-21','11'

（2）创建一个存储过程Delworker删除worker表中指定职工号的记录
go
if exists(select name from sysobjects where name='Delworker' and type='P') drop procedure Delworker
go
create procedure Delworker @职工号 char(4) as delete from worker where 职工号=@职工号
go
exec Delworker '16'

（3）显示存储过程Delworker的定义信息。
Sp_helptext Delworker

（4）删除存储过程Addworker和Delworker。
drop procedure Addworker, Delworker
（5）创建并执行以下存储过程：
a.从数据库表中查询，返回学生学号、姓名、课程名、成绩
use 徐瑞福200826031
go
if exists(select name from sysobjects where name='select_stu' and type='P') drop procedure select_stu
go
create procedure select_stu
as select SC.Sno,Sname,Cname,Grade from Student,SC,Course where SC.Sno=Student.Sno and SC.Cno=Course.Cno
go
exec select_stu

b.从数据库表中查询指定学号的学生学号，姓名，该存储过程接受与传递参数，精确匹配的值
use 徐瑞福200826031
go
if exists(select name from sysobjects where name='select_sno' and type='P') drop procedure select_sno
go
create procedure select_sno @Sno char(5)
as select Sno,Sname from Student where Sno=@Sno
go
exec select_sno '95002'

第三部分：触发器
(1) 在表depart上创建一个触发器 depart_update , 当更改部门号时同步更改 worker表中对应的部门号。
建立触发器命令：
If exists(select name from sysobjects where name='depart_update'and type='tr') drop trigger depart_update
go
Create trigger depart_update on depart for update
as
declare@no1 int,@no2 int
select@no1=部门号 ftom inserted
select@no2=部门号 from deleted
update worker set 部门号=@no1 where 部门号=@no2
运行命令：update depart set 部门号=103 部门号=101

(2)在表worker上创建一个触发器worker_delete,当删除职工记录时同步删除salary表中对应的职工记录。
创建触发器的命令：
go
If exists(select name from sysobjects where name='worker_delete'and type='tr') drop trigger worker_delete
go
create trigger worker_delete on worker for delete as delete from salary where salary.职工号=(select 职工号 from deleted)
更新命令：delete from worker where 职工号=2
If exists(select name from sysobjects where name='trig'and type='tr') drop trigger worker_delete
go create trigger trig on worker
for delete as
select’deleted表：’as deleted 表，*from deleted

(3) 删除触发器depart_update
If exists(select name from sysobjects where name=’depart_update’and type=’tr’)drop trigger depart_update
(4) 删除触发器worker_delete
If exists(select name from sysobjects where name=’worker_delete’and type=’tr’)drop trigger worker_delete
(5)在数据库中创建一个触发器，向选课表添加一条纪录时，检查该纪录的学号在学生表中是否存在，检查该纪录的课程号在课程表中是否存在，若其中有一项为否，则拒绝添加操作，并显示“违反数据一致性”提示信息。
例如：向选课表中添加一条记录('95009','5',89)，说明95009不在student表中
create trigger sc_insert on sc for insert as
declare @xuehao char(10),@kechenghao char(10),@chengji smallint,@a,@b
select @xuehao=sno,@kechenghao=cno from inserted
set @a= select sno from student where sno=@xuehao
set @b= select cno from course where cno=@kechenghao
if exists(@a and @b)
beign
insert into sc values(@xuehao,@kechenghao,@chengji)
end
rollback transaction print'违反数据一致性'
insert into sc values('95009','5',89)
第四部分：数据库完整性
1、实施worker表的“性别”字段默认值为“男”的约束
alter table worker
add constraint con1 default '男' for 性别
go
例如：
insert worker(职工号) values(111)
select * from worker

2 实施salary表的“工资”字段值在0~9999的约束
create table salary
(职工号 char(4),
姓名 char(8),
日期 datetime,
工资 decimal not null check(工资>=0 and 工资<=9999),
primary key(职工号,日期))
3 实施depart表的“部门号”字段值唯一的非聚集索引的约束
create table depart
(部门号 char(4) unique,
部门名 char(10))
Go
4、为worker表建立外键“部门号”，参考表depart的“部门号”列。
Create table worker
(职工号 char(4) primary key,
姓名 char(8),
性别 char(2),
出生日期 datetime,
党员否 char(2),
参加工作 datetime,
部门号 char(4),
Foreign key(部门号) references depart(部门号))
5、 建立一个规则 sex：@性别=’男’OR @性别=’女’,将其绑定到worker表的“性别”列上。
create rule worker_rule
as @性别 like '男' or @性别 like '女'
go
exec sp_bindrule 'worker_rule', 'worker.性别'
Go
6、删除1小题所建立的约束
Alter table worker drop constraint con1
7、 删除2小题所建立的约束。
Alter table salary drop 工资check
8、 删除3小题所建立的约束
Alter table depart drop unique
9、删除4小题所建立的约束
Alter table worker drop foreign key
10.解除5小题所建立的绑定并删除规则sex
if exists (select name from sysobjects
where name = 'worker_rule' and type = 'R')
begin
exec sp_unbindrule 'worker.性别'
drop rule worker_rule